mso-number-format doesn't work when Excel Export=BIFF

[FvLent]

Hello,
I have a RadGrid with Export Settings - Excel Format=BIFF
When I export the grid, all my numbers are shown as Text in the created Excel sheet.
When I add the mso-number-format:\#\,\#\#0\.000 in the style sheet for the class I use, or I add it in the code-behind using code like in http://www.telerik.com/help/aspnet-ajax/grid-html-export.html this number format is ignored.
When I change the Excel Format to HTML, the numbers are shown as numbers in Excel, even without using the mso-number-format. I then get the warning that the XLS format is not recognized, but I can continue.
Telerik: Can you investigate/advise?
Best Regards,
Frank

[Daniel]

Hello Frank,

mso-number-format is Office HTML-specific and will not work for the other Excel formats. You can use the Format property of the Cell object when exporting using the BIFF format.
You can access the aforementioned objects in the BiffExporting event.

Kind regards,
Daniel
the Telerik team

[FvLent]

Hi Daniel,
thank you for answering.
Can you provide me with a short demo-code accessing the Cell object and assigning it an Excel-numeric-type using the BIFF export
Thanx in advance,
Frank

[Kostadin]

Hi Frank,

I prepared a small sample and attached it to this forum post.

Give it a try and let me know whether it proves helpful.

Regards,
Kostadin
the Telerik team

[Tushar]

hello,

i am facing similar type of  problem , while i'm exporting an excel i have a date-time field. When i see the field in my grid(UI) it is showing date - time in 12hour clock but when i export that excel to my local , after opening it the date is shown correctly while the time is shown in 24 hr clock i have user mso-number-format as - mso-number-format:\""mm\/dd\/yy\"". could u please guide me so that wen i export i can get the correct time also with the date.

[Kostadin]

Hi Tushar,

You have to use the following format in order to show your date as 24hours format.

cell.Format = "d/M/yyyy HH:mm:ss";

Kind regards,
Kostadin
the Telerik team

[Shawn Krivjansky]

Cell.Format
???

What object is "Format" a property of Cell ??

I would assume you would be talking about the RadGrid_ExportCellFormatting event where "e" is of ExportCellFormattingEventArgs type.
e.Cell does NOT have a "Format" property.

I have also tried:
e.Cell.Style("mso-number-format") = "mm/dd/yyyy hh:mm:ss AM/PM"
during the mentioned event (EXCEL/HTML) and excel is NOT showing seconds, AM/PM, or a 12HR clock.
The "data" is getting there correctly, but the format is basically m/d/yyyy hh:mm (in 24HR clock).

How can I get the seconds with AM/PM on a 12HR clock??  I've read (on other forum posts) that mso-number-format should take care of that... but, doesn't seem to work for me.

[Shawn Krivjansky]

Figured it out...
Coming from VB...not really used to escaping characters and such as I never have to do that sorta thing that C# folks have to do.
Given that, I figured I didn't need to escape the format here either and I never did see a good example of this in VB that would've given me a hint that I NEEDED to use it.

Anyway, the VB solution is:
e.Cell.Style("mso-number-format") = "mm\/dd\/yyyy\ hh\:mm\:ss\ AM\/PM"

Guess those escape chars need to get into the resulting xml that Excel uses...so, sorta makes sense, but not completely obvious when you are sitting in the VB code-behind.

[Ricardo Pinto]

Hello Kostadin!

I have a similar scenario as the one Frank described, but I couldn't make your solution work.

My grid declaration

<sas:RadGrid ID="RadGrid1" DataSourceID="SqlDataSource2" runat="server" AllowMultiRowSelection="true">
        <MasterTableView DataKeyNames="Training_Course_ID,Training_Module_ID,Training_Action_ID,Training_Module_Order"
            Width="100%" runat="server" CommandItemDisplay="Top" Name="Modules" AllowPaging="false">
            <CommandItemTemplate>
                <div style="line-height: 30px;">
                    <sas:LinkButton ID="lbReloadModules" runat="server" CommandName="ReloadModules" Style="float: left; font-weight: lighter;"
                        ImageUrl="24_refresh.png" Width="160px" OnClientClick="javascript:return confirm('Serão repostos os dados originais dos módulos da ação. Pretende continuar?')">Repor Módulos Originais</sas:LinkButton>
                </div>
            </CommandItemTemplate>
            <Columns>
                <telerik:GridTemplateColumn UniqueName="PlanEditColumn" AllowFiltering="false">
                    <ItemTemplate>
                        <sas:ImageButton ID="ModulePlan" runat="server" ImageUrl="24_priv_schedule_hover.png" ToolTip="Consultar Planos do Módulo" />
                    </ItemTemplate>
                    <HeaderStyle Width="35px" />
                    <ItemStyle Width="35px" />
                </telerik:GridTemplateColumn>
                <telerik:GridTemplateColumn SortExpression="" HeaderText="" DataField="" AllowFiltering="false"
                    HeaderStyle-Width="35px" ItemStyle-Width="35px">
                    <ItemTemplate>
                        <sas:ImageButton ID="imgInfo" runat="server" ImageUrl="24_info.png" OnClientClick="return false; void(0);" />
                        <sas:RadToolTip ID="RadToolTip1" runat="server" TargetControlID="imgInfo" Width="350px"
                            RelativeTo="Element" Position="MiddleRight" Animation="Fade">
                            <table cellpadding="0" cellspacing="0" width="350px" style="table-layout: fixed;">
                                <tr>
                                    <td class="headerTT">
                                        <b>Dados Originais</b>
                                    </td>
                                </tr>
                                <tr>
                                    <td style="width: 100%;">
                                        <table cellpadding="0" cellspacing="0" style="width: 100%; text-align: left; border: 0px;">
                                            <tr>
                                                <td style="border-width: 0px; font-weight: bold;">Designação
                                                </td>
                                                <td style="width: 100%;">
                                                    <%#DataBinder.Eval(Container, "DataItem.CMOT_Module_Name")%>
                                                </td>
                                            </tr>
                                            <tr>
                                                <td style="border-width: 0px; font-weight: bold;">Duração
                                                </td>
                                                <td style="width: 100%;">
                                                    <%#BasePage.getHour(DataBinder.Eval(Container, "DataItem.CCMT_Duration")) & "h" & BasePage.getMinute(DataBinder.Eval(Container, "DataItem.CCMT_Duration")) & "m"%>
                                                </td>
                                            </tr>
                                        </table>
                                    </td>
                                </tr>
                            </table>
                        </sas:RadToolTip>
                    </ItemTemplate>
                </telerik:GridTemplateColumn>
                <telerik:GridBoundColumn SortExpression="Training_Module_ID" HeaderText="ID" Display="false"
                    HeaderButtonType="LinkButton" DataField="Training_Module_ID" UniqueName="Training_Module_ID">
                </telerik:GridBoundColumn>
                <telerik:GridBoundColumn SortExpression="CCMT_Acronym" HeaderText="Sigla" HeaderButtonType="LinkButton"
                    DataField="CCMT_Acronym" ItemStyle-HorizontalAlign="Right" UniqueName="CCMT_Acronym"
                    HeaderStyle-Width="70px" ItemStyle-Width="70px">
                </telerik:GridBoundColumn>
                <telerik:GridBoundColumn DataField="CCMT_Domain" HeaderText="Domínio" ReadOnly="True"
                    SortExpression="CCMT_Domain" UniqueName="CCMT_Domain" Display="false">
                </telerik:GridBoundColumn>
                <telerik:GridTemplateColumn SortExpression="Training_Module_Name" HeaderText="Designação"
                    ItemStyle-HorizontalAlign="left"
                    HeaderStyle-HorizontalAlign="Left" UniqueName="Training_Module_Name">
                    <ItemTemplate>
                        <asp:Label ID="lblName" runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.Training_Module_Name")%>'></asp:Label>
                    </ItemTemplate>
                </telerik:GridTemplateColumn>
                <telerik:GridTemplateColumn SortExpression="Training_Module_Name" HeaderText="Designação"
                    ItemStyle-HorizontalAlign="left"
                    HeaderStyle-HorizontalAlign="Left" UniqueName="Training_Module_Name_Txt">
                    <ItemTemplate>
                        <sas:RadTextBox ID="txtModuleValue" runat="server" Style="text-align: left;" ReadOnly="false"
                            Width="99%" Text='<%# DataBinder.Eval(Container, "DataItem.Training_Module_Name")%>' ClientEvents-OnValueChanged="SelectRow">
                        </sas:RadTextBox>
                    </ItemTemplate>
                </telerik:GridTemplateColumn>
                <telerik:GridTemplateColumn SortExpression="Training_Module_Duration" HeaderText="Duração" DataType="System.Int32"
                    UniqueName="" ItemStyle-HorizontalAlign="Right" HeaderStyle-Width="100px" ItemStyle-Width="100px" FilterControlWidth="60px">
                    <ItemTemplate>
                        <sas:RadTextBox ID="lblHour" ClientEvents-OnValueChanged="SelectRow" runat="server" Width="30px" Text='<%# BasePage.GetHour(DataBinder.Eval(Container, "DataItem.Training_Module_Duration"))%>'></sas:RadTextBox>
                        <asp:Label ID="letterH" runat="server" Text=":"></asp:Label>
                        <sas:RadTextBox ID="lblMinute" ClientEvents-OnValueChanged="SelectRow" runat="server" Width="30px" Text='<%# BasePage.GetMinute(DataBinder.Eval(Container, "DataItem.Training_Module_Duration"))%>'></sas:RadTextBox>
                    </ItemTemplate>
                </telerik:GridTemplateColumn>
                <telerik:GridTemplateColumn SortExpression="Training_Module_Duration" HeaderText="Duração" Visible="false" DataType="System.Int32"
                    UniqueName="" ItemStyle-HorizontalAlign="Right" HeaderStyle-Width="100px" ItemStyle-Width="100px" FilterControlWidth="60px">
                    <ItemTemplate>
                        <asp:Label ID="lblDuration" runat="server" Text='<%# Common.MinutesToHours(DataBinder.Eval(Container, "DataItem.Training_Module_Duration"))%>'></asp:Label>
                    </ItemTemplate>
                </telerik:GridTemplateColumn>
                <telerik:GridTemplateColumn SortExpression="Training_Module_PS_Duration" HeaderText="Duração PS"
                    UniqueName="" ItemStyle-HorizontalAlign="Right" HeaderStyle-Width="100px" ItemStyle-Width="100px" FilterControlWidth="60px">
                    <ItemTemplate>
                        <sas:RadTextBox ID="lblHourPS" ClientEvents-OnValueChanged="SelectRow" runat="server" Width="30px" Text='<%# BasePage.GetHour(DataBinder.Eval(Container, "DataItem.Training_Module_PS_Duration"))%>'></sas:RadTextBox>
                        <asp:Label ID="letterHPS" runat="server" Text=":"></asp:Label>
                        <sas:RadTextBox ID="lblMinutePS" ClientEvents-OnValueChanged="SelectRow" runat="server" Width="30px" Text='<%# BasePage.GetMinute(DataBinder.Eval(Container, "DataItem.Training_Module_PS_Duration"))%>'></sas:RadTextBox>
                    </ItemTemplate>
                </telerik:GridTemplateColumn>
                <telerik:GridTemplateColumn DataField="Training_Module_Year" SortExpression="Training_Module_Year"
                    HeaderText="Período" GroupByExpression="Training_Module_Year Group By Training_Module_Year"
                    UniqueName="Training_Module_Year" ReadOnly="true" ItemStyle-Width="60" HeaderStyle-Width="60"
                    FilterControlWidth="20px">
                    <ItemTemplate>
                        <sas:RadComboBox ID="cbYear" runat="server" SelectedValue='<%# Bind("Training_Module_Year") %>' OnClientSelectedIndexChanged="SelectRow"
                            EmptyMessage=" - Indique o período - " Width="98%">
                            <Items>
                                <telerik:RadComboBoxItem Text="1º" Value="1" />
                                <telerik:RadComboBoxItem Text="2º" Value="2" />
                                <telerik:RadComboBoxItem Text="3º" Value="3" />
                            </Items>
                        </sas:RadComboBox>
                    </ItemTemplate>
                </telerik:GridTemplateColumn>
                <telerik:GridTemplateColumn SortExpression="Training_Module_Active" HeaderText="Activo"
                    AllowFiltering="false" UniqueName="chkActiveTemplate" ItemStyle-HorizontalAlign="Center" HeaderStyle-HorizontalAlign="Left"
                    HeaderStyle-Width="50px" ItemStyle-Width="50px">
                    <ItemTemplate>
                        <asp:CheckBox ID="chkActive" runat="server" Checked='<%# DataBinder.Eval(Container, "DataItem.Training_Module_Active")%>' />
                    </ItemTemplate>
                </telerik:GridTemplateColumn>
                <telerik:GridTemplateColumn SortExpression="Training_Module_Subcomponent_TIC" HeaderText="Duração TIC"
                    UniqueName="" ItemStyle-HorizontalAlign="Right" HeaderStyle-Width="100px" ItemStyle-Width="100px" FilterControlWidth="60px">
                    <ItemTemplate>
                        <sas:RadTextBox ID="lblHourTIC" ClientEvents-OnValueChanged="SelectRow" runat="server" Width="30px" Text='<%# BasePage.GetHour(DataBinder.Eval(Container, "DataItem.Training_Module_Subcomponent_TIC"))%>'></sas:RadTextBox>
                        <asp:Label ID="letterHTIC" runat="server" Text=":"></asp:Label>
                        <sas:RadTextBox ID="lblMinuteTIC" ClientEvents-OnValueChanged="SelectRow" runat="server" Width="30px" Text='<%# BasePage.GetMinute(DataBinder.Eval(Container, "DataItem.Training_Module_Subcomponent_TIC"))%>'></sas:RadTextBox>
                    </ItemTemplate>
                </telerik:GridTemplateColumn>
                <telerik:GridTemplateColumn SortExpression="Training_Module_Subcomponent_AMB" HeaderText="Duração AMB"
                    UniqueName="" ItemStyle-HorizontalAlign="Right" HeaderStyle-Width="100px" ItemStyle-Width="100px" FilterControlWidth="60px">
                    <ItemTemplate>
                        <sas:RadTextBox ID="lblHourAMB" ClientEvents-OnValueChanged="SelectRow" runat="server" Width="30px" Text='<%# BasePage.GetHour(DataBinder.Eval(Container, "DataItem.Training_Module_Subcomponent_AMB"))%>'></sas:RadTextBox>
                        <asp:Label ID="letterHAMB" runat="server" Text=":"></asp:Label>
                        <sas:RadTextBox ID="lblMinuteAMB" ClientEvents-OnValueChanged="SelectRow" runat="server" Width="30px" Text='<%# BasePage.GetMinute(DataBinder.Eval(Container, "DataItem.Training_Module_Subcomponent_AMB"))%>'></sas:RadTextBox>
                    </ItemTemplate>
                </telerik:GridTemplateColumn>
                <telerik:GridTemplateColumn SortExpression="Training_Module_Subcomponent_IO" HeaderText="Duração IO"
                    UniqueName="" ItemStyle-HorizontalAlign="Right" HeaderStyle-Width="100px" ItemStyle-Width="100px" FilterControlWidth="60px">
                    <ItemTemplate>
                        <sas:RadTextBox ID="lblHourIO" ClientEvents-OnValueChanged="SelectRow" runat="server" Width="30px" Text='<%# BasePage.GetHour(DataBinder.Eval(Container, "DataItem.Training_Module_Subcomponent_IO"))%>'></sas:RadTextBox>
                        <asp:Label ID="letterHIO" runat="server" Text=":"></asp:Label>
                        <sas:RadTextBox ID="lblMinuteIO" ClientEvents-OnValueChanged="SelectRow" runat="server" Width="30px" Text='<%# BasePage.GetMinute(DataBinder.Eval(Container, "DataItem.Training_Module_Subcomponent_IO"))%>'></sas:RadTextBox>
                    </ItemTemplate>
                </telerik:GridTemplateColumn>
                <telerik:GridTemplateColumn HeaderText="Agendado"
                    AllowFiltering="false" UniqueName="" ItemStyle-HorizontalAlign="left" HeaderStyle-HorizontalAlign="Left"
                    HeaderStyle-Width="70px" ItemStyle-Width="70px">
                    <ItemTemplate>
                        <asp:Label ID="lblAc" runat="server" Text='<%# GetAccByModule(DataBinder.Eval(Container, "DataItem.Training_Action_ID"), DataBinder.Eval(Container, "DataItem.Training_Module_ID")) & "/" & Common.MinutesToHHmm(DataBinder.Eval(Container, "DataItem.Training_Module_Duration")) %>'></asp:Label>
                    </ItemTemplate>
                </telerik:GridTemplateColumn>
                <telerik:GridTemplateColumn SortExpression="" HeaderText="Formador" HeaderStyle-Width="175px" ItemStyle-Width="175px"
                    AllowFiltering="false" UniqueName="Formador" ItemStyle-HorizontalAlign="Center" HeaderStyle-HorizontalAlign="Left">
                    <HeaderTemplate>Formador</HeaderTemplate>
                    <ItemTemplate>
                        <asp:Label ID="lblTrainer" runat="server" Text='<%# GetTrainerName(DataBinder.Eval(Container, "DataItem.Training_Action_ID").ToString(),DataBinder.Eval(Container, "DataItem.Training_Course_ID").ToString(),DataBinder.Eval(Container, "DataItem.Training_Module_ID").ToString()) %>' />
                    </ItemTemplate>
                </telerik:GridTemplateColumn>
                <telerik:GridTemplateColumn HeaderText="Horas Dadas" DataType="System.Int32"
                    AllowFiltering="false" UniqueName="HD" ItemStyle-HorizontalAlign="left" HeaderStyle-HorizontalAlign="Left"
                    HeaderStyle-Width="70px" ItemStyle-Width="70px" Display="false">
                    <ItemTemplate>
                        <asp:Label ID="lblHD" runat="server" Text='<%# GetModuleHoursBeforePeriod(DataBinder.Eval(Container, "DataItem.Training_Action_ID"), DataBinder.Eval(Container, "DataItem.Training_Module_ID"))%>'></asp:Label>
                    </ItemTemplate>
                </telerik:GridTemplateColumn>
                <telerik:GridTemplateColumn HeaderText="Horas no Período" DataType="System.Int32"
                    AllowFiltering="false" UniqueName="HÁ" ItemStyle-HorizontalAlign="left" HeaderStyle-HorizontalAlign="Left"
                    HeaderStyle-Width="70px" ItemStyle-Width="70px" Display="false">
                    <ItemTemplate>
                        <asp:Label ID="lblHP" runat="server" Text='<%# GetModuleHoursDuringPeriod(DataBinder.Eval(Container, "DataItem.Training_Action_ID"), DataBinder.Eval(Container, "DataItem.Training_Module_ID"))%>'></asp:Label>
                    </ItemTemplate>
                </telerik:GridTemplateColumn>
                <telerik:GridTemplateColumn HeaderText="Horas Futuras" DataType="System.Int32"
                    AllowFiltering="false" UniqueName="HF" ItemStyle-HorizontalAlign="left" HeaderStyle-HorizontalAlign="Left"
                    HeaderStyle-Width="70px" ItemStyle-Width="70px" Display="false">
                    <ItemTemplate>
                        <asp:Label ID="lblHF" runat="server" Text='<%# GetModuleHoursAfterPeriod(DataBinder.Eval(Container, "DataItem.Training_Action_ID"), DataBinder.Eval(Container, "DataItem.Training_Module_ID"))%>'></asp:Label>
                    </ItemTemplate>
                </telerik:GridTemplateColumn>
                <telerik:GridTemplateColumn UniqueName="TemplateEditColumn" AllowFiltering="false" HeaderText="Avaliações">
                    <ItemTemplate>
                        <sas:ImageButton ID="ModuleEvaluation" runat="server" ImageUrl="24_evaluation.png" ToolTip="Avaliações" />
                    </ItemTemplate>
                    <HeaderStyle Width="35px" />
                    <ItemStyle Width="35px" />
                </telerik:GridTemplateColumn>
                <telerik:GridBoundColumn DataField="TRMT_Scheduler_Color" HeaderText="Cor" ReadOnly="True"
                    SortExpression="TRMT_Scheduler_Color" UniqueName="TRMT_Scheduler_Color" Display="false">
                </telerik:GridBoundColumn>
                <telerik:GridClientSelectColumn UniqueName="clientselectcolumn" ItemStyle-Width="40" HeaderStyle-Width="40"></telerik:GridClientSelectColumn>
            </Columns>
            <SortExpressions>
                <telerik:GridSortExpression FieldName="Training_Module_Order"></telerik:GridSortExpression>
            </SortExpressions>
        </MasterTableView>
        <ClientSettings AllowColumnHide="true" AllowRowsDragDrop="false">
            <Selecting AllowRowSelect="true" UseClientSelectColumnOnly="true" />
        </ClientSettings>
    </sas:RadGrid>

My Export Click

        Protected Sub RadToolbarFilter_ButtonClick(sender As Object, e As RadToolBarEventArgs)
            If e.Item.Value.ToUpper = "Excel".ToUpper Then
                isExporting = True
                RadGrid1.Rebind()
                ConfigureExport()
                RadGrid1.MasterTableView.ExportToExcel()
            End If

        End Sub

Public Sub ConfigureExport()
            RadGrid1.Columns(0).Visible = False
            RadGrid1.Columns(1).Visible = False
            RadGrid1.Columns(2).Visible = False
            RadGrid1.Columns(5).Visible = True
            RadGrid1.Columns(6).Visible = False
            RadGrid1.Columns(7).Visible = False
            RadGrid1.Columns(8).Visible = True
            RadGrid1.Columns(9).Visible = False
            RadGrid1.Columns(10).Visible = False
            RadGrid1.Columns(11).Visible = False
            RadGrid1.Columns(12).Visible = False
            RadGrid1.Columns(13).Visible = False
            RadGrid1.Columns(14).Visible = False
            RadGrid1.Columns(15).Visible = False
            RadGrid1.Columns(17).Display = True
            RadGrid1.Columns(18).Display = True
            RadGrid1.Columns(19).Display = True
            RadGrid1.Columns(20).Visible = False
            RadGrid1.Columns(21).Visible = False
            RadGrid1.Columns(22).Visible = False
            RadGrid1.ExportSettings.HideStructureColumns = True
            RadGrid1.AllowFilteringByColumn = False
            RadGrid1.ExportSettings.ExportOnlyData = True
            RadGrid1.ExportSettings.IgnorePaging = True
            RadGrid1.ExportSettings.OpenInNewWindow = True
            Dim rdpInitialDate As CoreReadyCTL.UI.RadDatePicker = CType(RadToolbarFilter.FindItemByValue("Dates").FindControl("rdpInitialDate"), CoreReadyCTL.UI.RadDatePicker)
            Dim rdpFinalDate As CoreReadyCTL.UI.RadDatePicker = CType(RadToolbarFilter.FindItemByValue("Dates").FindControl("rdpFinalDate"), CoreReadyCTL.UI.RadDatePicker)
            RadGrid1.ExportSettings.FileName = "Módulos da ação " & pActionName & " - " & rdpInitialDate.SelectedDate.Value.Date.ToShortDateString & " - " & rdpFinalDate.SelectedDate.Value.Date.ToShortDateString
            RadGrid1.ExportSettings.Excel.Format = GridExcelExportFormat.Biff
        End Sub

Im using isExporting flag and Rebind before exporting in order to change some styles on itemDatabound.
To check if this was not interfering with the formatting issue I also tried to run without these two lines, but with no difference.

My GridItemDatabound

Protected Sub RadGrid1_ItemDataBound(ByVal sender As Object, ByVal e As Telerik.Web.UI.GridItemEventArgs) Handles RadGrid1.ItemDataBound

            If isExporting Then
                If e.Item.ItemType = GridItemType.Header Then
                    e.Item.BackColor = System.Drawing.Color.DarkGray
                    e.Item.ForeColor = System.Drawing.Color.Black
                    e.Item.BorderStyle = BorderStyle.Solid
                    e.Item.BorderWidth = 2
                    e.Item.BorderColor = Drawing.Color.Black
                    e.Item.Font.Bold = True
                    e.Item.Height = Unit.Point(20)
                    e.Item.Font.Size = 12
                    e.Item.Font.Name = "Arial"
                ElseIf e.Item.ItemType = GridItemType.Item Then
                    Dim dataitem As GridDataItem = CType(e.Item, GridDataItem)
                    e.Item.BackColor = SetColor(dataitem("TRMT_Scheduler_Color").Text)
                End If
            End If

        End Sub

The excel first row is styled (GridItemType.Header section) but the data rows are not shown with the expected backColor (GridItemType.Item section).

My BiffExporting

Protected Sub RadGrid1_BiffExporting(sender As Object, e As GridBiffExportingEventArgs) Handles RadGrid1.BiffExporting
            Dim tbl As Table = e.ExportStructure.Tables(0)

            tbl.Cells(1, 1).Value = "CF"
            tbl.Columns(1).Width = 10
            tbl.Cells(2, 1).Value = "SIGLA"
            tbl.Columns(2).Width = 10

            tbl.Cells(3, 1).Value = "DESIGNAÇÃO"
            tbl.Columns(3).Width = 75
            tbl.Cells(4, 1).Value = ""
            For Each cell In tbl.Columns(4).Cells
                If Not cell.Text = String.Empty AndAlso IsNumeric(cell.Text) Then
                    'cell.Format = String.Format("{0:0}", Int32.Parse(cell.Text))
                    cell.Format = String.Format("{0:0.000}", Decimal.Parse(cell.Text))
                End If
            Next
            tbl.Columns(4).Width = 10
            tbl.Cells(5, 1).Value = "FORMADORES"
            tbl.Columns(5).Width = 50
            tbl.Cells(6, 1).Value = "HD"
            For Each cell In tbl.Columns(6).Cells
                If Not cell.Text = String.Empty AndAlso IsNumeric(cell.Text) Then
                    cell.Format = String.Format("{0:0}", Int32.Parse(cell.Text))
                End If
            Next
            tbl.Columns(6).Width = 10
            tbl.Cells(7, 1).Value = "HÁ"
            For Each cell In tbl.Columns(7).Cells
                If Not cell.Text = String.Empty AndAlso IsNumeric(cell.Text) Then
                    cell.Format = String.Format("{0:0}", Int32.Parse(cell.Text))
                End If
            Next
            tbl.Columns(7).Width = 10
            tbl.Cells(8, 1).Value = "HF"
            For Each cell In tbl.Columns(8).Cells
                If Not cell.Text = String.Empty AndAlso IsNumeric(cell.Text) Then
                    cell.Format = String.Format("{0:0}", Int32.Parse(cell.Text))
                End If
            Next
            tbl.Columns(8).Width = 10

        End Sub

I tried with decimal or Int32, but with no success!

Can you please help me with this formatting issue (number format and color)?
Thanks in advance, 

Ricardo Pinto.

Ps: I've attached the exported excel file

[Kostadin]

Hi Ricardo,

A possible solution is to parse the cell's text to integer and set the number as value of this cell. Check out the following code snippet.

Protected Sub RadGrid1_BiffExporting(sender As Object, e As Telerik.Web.UI.GridBiffExportingEventArgs)
    Dim table As xls.Table = e.ExportStructure.Tables(0)
    For Each cell As var In table.Columns(4).Cells
        Dim result As Integer
        Dim isInteger As Boolean = Int32.TryParse(cell.Text, result)
        If isInteger Then
            cell.Value = result
        End If
    Next
End Sub

Regards,
Kostadin
Telerik

[Ricardo Pinto]

Hello Kostadin!

Thanks a lot for your feedback.
Your solution worked as intended :)

Concerning the color issue, I can see here en example where all the rows in the first column are exported with a different back color.
But in that example the color is set directly in the column itemStyle, while in my case the color is set on itemdatabind based on a datasource field.

is there any other options to explore?

Best regards,

Ricardo.

[Kostadin]

Hi Ricardo,

You could also set the background color directly to the cell on BiffExporting event handler. For instance:

Protected Sub RadGrid1_BiffExporting(sender As Object, e As Telerik.Web.UI.GridBiffExportingEventArgs)
    Dim table As xls.Table = e.ExportStructure.Tables(0)
    For Each cell As var In table.Columns(4).Cells
        Dim result As Integer
        Dim isInteger As Boolean = Int32.TryParse(cell.Text, result)
        If isInteger Then
            cell.Value = result
            cell.Style.BackColor = Drawing.Color.AliceBlue
        End If
    Next
End Sub

Additional information about styling the exported file could be found at the following help article.

Regards,
Kostadin
Telerik