export money values to Excel

9 posts, 0 answers
  1. Rudy
    Rudy avatar
    14 posts
    Member since:
    Nov 2008

    Posted 04 Feb 2009 Link to this post

    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


  2. Daniel
    Admin
    Daniel avatar
    4946 posts

    Posted 04 Feb 2009 Link to this post

    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.
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Rudy
    Rudy avatar
    14 posts
    Member since:
    Nov 2008

    Posted 04 Feb 2009 Link to this post

    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>



  5. Rudy
    Rudy avatar
    14 posts
    Member since:
    Nov 2008

    Posted 04 Feb 2009 Link to this post

    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>



  6. Daniel
    Admin
    Daniel avatar
    4946 posts

    Posted 09 Feb 2009 Link to this post

    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.
  7. Mark Galbreath
    Mark Galbreath avatar
    171 posts
    Member since:
    Jul 2009

    Posted 20 Nov 2009 Link to this post

    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
  8. Mark Galbreath
    Mark Galbreath avatar
    171 posts
    Member since:
    Jul 2009

    Posted 20 Nov 2009 Link to this post

    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.
  9. Mark Galbreath
    Mark Galbreath avatar
    171 posts
    Member since:
    Jul 2009

    Posted 20 Nov 2009 Link to this post

    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
  10. Mark Galbreath
    Mark Galbreath avatar
    171 posts
    Member since:
    Jul 2009

    Posted 20 Nov 2009 Link to this post

    I found the answer in the custom cell formatting settings in Excel: ( "ss:Format", "$#,##0" ) .

    Time for a beer!
Back to Top
UI for ASP.NET Ajax is Ready for VS 2017