hello,
i have i radgrid with a column named "Price" and "Discount".
In the SQL database these values are of type: decimal(9,2)
in the radgrid they are like:
price discount
when i export them
to excel some results are correct presented in the column in the same way
but many has a complete different value, for example:
430,10 0,85
i notice that there is a "," instead of "."
when i export to CSV everything is correct.
has anybody else found the same bug?
thanks
Rudy - Italy
i have i radgrid with a column named "Price" and "Discount".
In the SQL database these values are of type: decimal(9,2)
in the radgrid they are like:
price discount
11060.00 | 6.50 |
when i export them
to excel some results are correct presented in the column in the same way
but many has a complete different value, for example:
430,10 0,85
i notice that there is a "," instead of "."
when i export to CSV everything is correct.
has anybody else found the same bug?
thanks
Rudy - Italy
8 Answers, 1 is accepted
0
Hello Rudy,
Please elaborate whether you export to Excel or ExcelML format. Also I suggest you open the file with a regular editor (like Wordpad) and check if your values are exported properly or not.
Kind regards,
Daniel
the Telerik team
Check out Telerik Trainer, the state of the art learning tool for Telerik products.
Please elaborate whether you export to Excel or ExcelML format. Also I suggest you open the file with a regular editor (like Wordpad) and check if your values are exported properly or not.
Kind regards,
Daniel
the Telerik team
Check out Telerik Trainer, the state of the art learning tool for Telerik products.
0
Rudy
Top achievements
Rank 1
answered on 04 Feb 2009, 10:48 AM
thanks,
what do you mean with:
Please elaborate whether you export to Excel or ExcelML format.
my export is:
protected void btnExportXLS_Click(object sender, EventArgs e)
{
_stand.GetAll();
FormatColumnsForExport();
gridBooth.Rebind();
gridBooth.ExportSettings.ExportOnlyData = false;
gridBooth.ExportSettings.IgnorePaging = true;
gridBooth.ExportSettings.FileName = "ExportGlobal.xcl";
gridBooth.ExportSettings.OpenInNewWindow = true;
gridBooth.MasterTableView.CommandItemDisplay = GridCommandItemDisplay.None;
gridBooth.MasterTableView.ExportToExcel();
}
columns:
<telerik:GridBoundColumn DataField="Price" EmptyDataText="0" HeaderText="Prezzo"
UniqueName="Price">
</telerik:GridBoundColumn>
<telerik:GridBoundColumn DataField="Discount" EmptyDataText="0" HeaderText="Sconto"
UniqueName="Discount">
</telerik:GridBoundColumn>
what do you mean with:
Please elaborate whether you export to Excel or ExcelML format.
my export is:
protected void btnExportXLS_Click(object sender, EventArgs e)
{
_stand.GetAll();
FormatColumnsForExport();
gridBooth.Rebind();
gridBooth.ExportSettings.ExportOnlyData = false;
gridBooth.ExportSettings.IgnorePaging = true;
gridBooth.ExportSettings.FileName = "ExportGlobal.xcl";
gridBooth.ExportSettings.OpenInNewWindow = true;
gridBooth.MasterTableView.CommandItemDisplay = GridCommandItemDisplay.None;
gridBooth.MasterTableView.ExportToExcel();
}
columns:
<telerik:GridBoundColumn DataField="Price" EmptyDataText="0" HeaderText="Prezzo"
UniqueName="Price">
</telerik:GridBoundColumn>
<telerik:GridBoundColumn DataField="Discount" EmptyDataText="0" HeaderText="Sconto"
UniqueName="Discount">
</telerik:GridBoundColumn>
0
Rudy
Top achievements
Rank 1
answered on 04 Feb 2009, 11:07 AM
thanks,
what do you mean with:
Please elaborate whether you export to Excel or ExcelML format.
as i said the CSV export is ok
my export is:
protected void btnExportXLS_Click(object sender, EventArgs e)
{
_stand.GetAll();
FormatColumnsForExport();
gridBooth.Rebind();
gridBooth.ExportSettings.ExportOnlyData = false;
gridBooth.ExportSettings.IgnorePaging = true;
gridBooth.ExportSettings.FileName = "ExportGlobal.xcl";
gridBooth.ExportSettings.OpenInNewWindow = true;
gridBooth.MasterTableView.CommandItemDisplay = GridCommandItemDisplay.None;
gridBooth.MasterTableView.ExportToExcel();
}
columns:
<telerik:GridBoundColumn DataField="Price" EmptyDataText="0" HeaderText="Prezzo"
UniqueName="Price">
</telerik:GridBoundColumn>
<telerik:GridBoundColumn DataField="Discount" EmptyDataText="0" HeaderText="Sconto"
UniqueName="Discount">
</telerik:GridBoundColumn>
what do you mean with:
Please elaborate whether you export to Excel or ExcelML format.
as i said the CSV export is ok
my export is:
protected void btnExportXLS_Click(object sender, EventArgs e)
{
_stand.GetAll();
FormatColumnsForExport();
gridBooth.Rebind();
gridBooth.ExportSettings.ExportOnlyData = false;
gridBooth.ExportSettings.IgnorePaging = true;
gridBooth.ExportSettings.FileName = "ExportGlobal.xcl";
gridBooth.ExportSettings.OpenInNewWindow = true;
gridBooth.MasterTableView.CommandItemDisplay = GridCommandItemDisplay.None;
gridBooth.MasterTableView.ExportToExcel();
}
columns:
<telerik:GridBoundColumn DataField="Price" EmptyDataText="0" HeaderText="Prezzo"
UniqueName="Price">
</telerik:GridBoundColumn>
<telerik:GridBoundColumn DataField="Discount" EmptyDataText="0" HeaderText="Sconto"
UniqueName="Discount">
</telerik:GridBoundColumn>
0
Hello Rudy,
I suppose the reason is that Excel parse the numbers as specified in your regional settings. You can try the following approach:
Export to Microsoft Excel/Word/PDF/CSV
mso-number-format
Best regards,
Daniel
the Telerik team
Check out Telerik Trainer, the state of the art learning tool for Telerik products.
I suppose the reason is that Excel parse the numbers as specified in your regional settings. You can try the following approach:
protected void RadGrid1_ExcelExportCellFormatting(object source, ExcelExportCellFormattingEventArgs e) |
{ |
if (e.FormattedColumn.UniqueName == "Price") |
e.Cell.Style["mso-number-format"] = @"\@"; |
} |
Export to Microsoft Excel/Word/PDF/CSV
mso-number-format
Best regards,
Daniel
the Telerik team
Check out Telerik Trainer, the state of the art learning tool for Telerik products.
0
Mark Galbreath
Top achievements
Rank 2
answered on 20 Nov 2009, 07:19 PM
What is this @"\@" syntax? This is an IDE error in VB. What is the difference between ExcelExportCellFormatting and ExcelMLExportStylesCreated?
The example you gave me, Daniel (transposed to VB):
does not work; the methods do not appear to be firing (a RadAjaxManger1.Alert( "test" ) fails) and no formatting of the resultant Excel worksheet is occurring.
My export settings are typical:
Cheers!
Mark
The example you gave me, Daniel (transposed to VB):
Protected Sub radGrid1_ExcelMLExportRowCreated( source As Object, e As GridExportExcelMLRowCreatedArgs ) |
e.Row.Cells.GetCellByName( "price" ).StyleValue = "myStyle" |
e.Row.Cells.GetCellByName( "max_cost" ).StyleValue = "myStyle" |
e.Row.Cells.GetCellByName( "min_cost").StyleValue = "myStyle" |
End Sub |
Protected Sub radGrid1_ExcelMLExportStylesCreated( source As Object, e As GridExportExcelMLStyleCreatedArgs ) |
Dim myStyle As StyleElement = New StyleElement( "myStyle" ) |
myStyle.NumberFormat.Attributes.Add( "ss:Format", "$#" ) |
e.Styles.Add( myStyle ) |
End Sub |
My export settings are typical:
Protected Sub btn_export_Click( sender As Object, e As EventArgs ) Handles btn_export.Click |
radGrid1.MasterTableView.UseAllDataFields = True |
radGrid1.ExportSettings.IgnorePaging = True |
radGrid1.ExportSettings.OpenInNewWindow = True |
radGrid1.ExportSettings.ExportOnlyData = True |
radGrid1.ExportSettings.Excel.Format = GridExcelExportFormat.ExcelML |
Me.radGrid1.MasterTableView.ExportToExcel |
End Sub |
Cheers!
Mark
0
Mark Galbreath
Top achievements
Rank 2
answered on 20 Nov 2009, 09:10 PM
Progress! Daniel, you didn't tell me that these attributes had to be in the markup to fire the code-behind methods:
I found them in one of the mini-projects you sent me, so thanks for that. Now, the formatting code you shot me:
is producing values in the Excel spreadsheet without commas, like "$123456" and where the value was 0, it is now merely "$." Is there a source for these formatting codes I can look up myself? Like, what is the syntax for comma-delimited groupings of 3 and how to keep the original 0 in the cell so it displays as "$0"?
Thanks, bud.
OnExcelMLExportRowCreated="radGrid1_ExcelMLExportRowCreated" |
OnExcelMLExportStylesCreated="radGrid1_ExcelMLExportStylesCreated" |
myStyle.NumberFormat.Attributes.Add( "ss:Format", "$#" ) |
Thanks, bud.
0
Mark Galbreath
Top achievements
Rank 2
answered on 20 Nov 2009, 09:28 PM
Okay, I got the grouping by trial and error: .Add( "ss:Format", "$###,###" ). This gives me what I need, which is a non-decimal currency, but it changes an amount of 0 (zero) to nothing; that is, from [0] to [$]. ( "ss:Format", "Currency" ) adds the .00 decimal and there does not seem to be a way to get rid of the decimal like you can in the markup for a RagGrid column by specifying a data format of "{0:c0}"
How to prepend a "$" to a 0?
Thanks,
Mark
How to prepend a "$" to a 0?
Thanks,
Mark
0
Mark Galbreath
Top achievements
Rank 2
answered on 20 Nov 2009, 11:05 PM
I found the answer in the custom cell formatting settings in Excel: ( "ss:Format", "$#,##0" ) .
Time for a beer!
Time for a beer!