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

[Solved] Format decimals on ExcelML export

9 Answers 825 Views

Grid

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

9 Answers, 1 is accepted

answered on 05 Jan 2009, 11:55 AM

Hello Daniel,

Please try to set the NumberStyle in the RadGrid1_ExcelMLExportStylesCreated event handler.

    
            styleElement.NumberFormat.FormatType = Telerik.Web.UI.GridExcelBuilder.NumberFormatType.Fixed;

    
            styleElement.NumberFormat.FormatType = Telerik.Web.UI.GridExcelBuilder.NumberFormatType.Currency;

Export to Microsoft Excel/Word/PDF/CSV

Regards,
Daniel
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

answered on 05 Jan 2009, 12:22 PM

Hey guys,

unfortunately none of the suggestions work.

Princy, I can add a custom style to the selection, but how do i set *specific* columns to use that style?

Daniel, check my original request. I cannot set the number format for the style (so effectively for each row). I have to operate based on columns as some colums are plain numbers (quantity) others are dates and others are currencies.

Why isn't the DataFormatString I have set for the column just used for the export automatically? The UI display is OK.

Any other ideas?

Daniel

answered on 05 Jan 2009, 02:27 PM

Daniel,

You can set the style to the column of your choice:

    
            protected void radGrid_ExcelMLExportStylesCreated(object source, Telerik.Web.UI.GridExcelBuilder.GridExportExcelMLStyleCreatedArgs e) 
        
            { 
        
                StyleElement numberStyle = new StyleElement("MyNumberStyle"); 
        
                numberStyle.NumberFormat.FormatType = Telerik.Web.UI.GridExcelBuilder.NumberFormatType.Fixed; 
        
                e.Styles.Add(numberStyle); 
        
            } 
        
            protected void radGrid_ExcelMLExportRowCreated(object source, GridExportExcelMLRowCreatedArgs e) 
        
            { 
        
                if (e.RowType == Telerik.Web.UI.GridExcelBuilder.GridExportExcelMLRowType.DataRow) 
        
                    e.Row.Cells.GetCellByName("myLongNumberColumn").StyleValue = "MyNumberStyle"; 
        
            }

I hope this helps.

Regards,
Daniel
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

answered on 05 Jan 2009, 03:33 PM

OK, nearly there.

I have

    
            Private Sub grdPoStat_ExcelMLExportStylesCreated(ByVal source As Object, ByVal e As Telerik.Web.UI.GridExcelBuilder.GridExportExcelMLStyleCreatedArgs) Handles grdPoStat.ExcelMLExportStylesCreated 
        
                    Dim style As New StyleElement("MyNumberStyle") 
        
                    style.NumberFormat.FormatType = Telerik.Web.UI.GridExcelBuilder.NumberFormatType.Fixed 
        
                    style.NumberFormat.Attributes.Add("ss:Format", "#.0000") 
        
                    e.Styles.Add(style) 
        
                End Sub

and

    
            Private Sub grdPoStat_ExcelMLExportRowCreated(ByVal source As Object, ByVal e As Telerik.Web.UI.GridExcelBuilder.GridExportExcelMLRowCreatedArgs) Handles grdPoStat.ExcelMLExportRowCreated 
        
                    If e.RowType = GridExportExcelMLRowType.DataRow Then 
        
                        e.Row.Cells.GetCellByName("VolumeOfSales").StyleValue = "MyNumberStyle" 
        
                    End If 
        
            End Sub

I do not see scientific notation now but rather long numeric values. Setting the number format attribute or leaving that out does not change anything. However the column now is formatted as a number with to decimal digits.

Setting a breakpoint on the GetCellByName code line I can inspect the cell value. I see a value of "2000D", a 2000 decimal value in the quick watch. This ends up in Excel as 200000000000,00 (german number format). A cell value of 2.81D is exported as 281000000,00. I need that to be 2000,00 or 2,81 but I cannot find a ss:Format value that takes care of that.

Daniel

answered on 08 Jan 2009, 02:55 PM

Hello Daniel,

It seems that Excel itself doesn't convert the value according to the current culture.
When the decimal separator in the exported file is comma, it is improperly parsed to a group separator regardless of the current culture settings.
This can be easily verified if you open the output file with a regular editor (Notepad, Wordpad).
The number is let's say 1,23, but in Excel it's looking as 123. Fortunately we found a way to overcome this issue:

    
            Protected Overloads Overrides Sub InitializeCulture() 
        
                MyBase.InitializeCulture() 
        
                Thread.CurrentThread.CurrentCulture = TryCast(CultureInfo.CurrentCulture.Clone(), CultureInfo) 
        
                Thread.CurrentThread.CurrentCulture.NumberFormat.NumberDecimalSeparator = "." 
        
            End Sub

More information about XML spreadsheets is available below:
XML Spreadsheet Reference

Regards,
Daniel
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.