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

12 posts, 0 answers
  1. FvLent
    FvLent avatar
    19 posts
    Member since:
    Apr 2009

    Posted 29 Nov 2012 Link to this post

    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
  2. Daniel
    Admin
    Daniel avatar
    4943 posts

    Posted 03 Dec 2012 Link to this post

    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
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. FvLent
    FvLent avatar
    19 posts
    Member since:
    Apr 2009

    Posted 03 Dec 2012 Link to this post

    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
  5. Kostadin
    Admin
    Kostadin avatar
    1708 posts

    Posted 06 Dec 2012 Link to this post

    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
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
  6. Tushar
    Tushar avatar
    1 posts
    Member since:
    Feb 2013

    Posted 19 Feb 2013 Link to this post

    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.
  7. Kostadin
    Admin
    Kostadin avatar
    1708 posts

    Posted 22 Feb 2013 Link to this post

    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
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
  8. Shawn Krivjansky
    Shawn Krivjansky avatar
    86 posts
    Member since:
    Jan 2010

    Posted 28 May 2013 Link to this post

    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.
  9. Shawn Krivjansky
    Shawn Krivjansky avatar
    86 posts
    Member since:
    Jan 2010

    Posted 28 May 2013 Link to this post

    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.
  10. Ricardo Pinto
    Ricardo Pinto avatar
    36 posts
    Member since:
    Sep 2008

    Posted 08 Jul 2013 Link to this post

    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
  11. Kostadin
    Admin
    Kostadin avatar
    1708 posts

    Posted 11 Jul 2013 Link to this post

    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
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to the blog feed now.
  12. Ricardo Pinto
    Ricardo Pinto avatar
    36 posts
    Member since:
    Sep 2008

    Posted 12 Jul 2013 Link to this post

    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.
  13. Kostadin
    Admin
    Kostadin avatar
    1708 posts

    Posted 17 Jul 2013 Link to this post

    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
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to the blog feed now.
Back to Top
UI for ASP.NET Ajax is Ready for VS 2017