This is a migrated thread and some comments may be shown as answers.

export money values to Excel

8 Answers 279 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Rudy
Top achievements
Rank 1
Rudy asked on 04 Feb 2009, 09:56 AM
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
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

Sort by
0
Daniel
Telerik team
answered on 04 Feb 2009, 10:17 AM
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.
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>



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>



0
Daniel
Telerik team
answered on 09 Feb 2009, 09:12 AM
Hello Rudy,

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):
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 
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:
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:
OnExcelMLExportRowCreated="radGrid1_ExcelMLExportRowCreated"   
OnExcelMLExportStylesCreated="radGrid1_ExcelMLExportStylesCreated" 
I found them in one of the mini-projects you sent me, so thanks for that.  Now, the formatting code you shot me:
myStyle.NumberFormat.Attributes.Add( "ss:Format",  "$#" ) 
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.
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
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!
Tags
Grid
Asked by
Rudy
Top achievements
Rank 1
Answers by
Daniel
Telerik team
Rudy
Top achievements
Rank 1
Mark Galbreath
Top achievements
Rank 2
Share this question
or