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

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

11 Answers 1587 Views

Grid

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

11 Answers, 1 is accepted

answered on 06 Dec 2012, 12:18 PM

BiifExportNumberFormat.zip

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.

answered on 19 Feb 2013, 10:08 AM

Date-field.JPG

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.

answered on 28 May 2013, 08:12 PM

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.

answered on 28 May 2013, 08:38 PM

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.

answered on 08 Jul 2013, 02:22 PM

ExcelBiffFormat.png

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 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

answered on 11 Jul 2013, 12:09 PM

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

answered on 17 Jul 2013, 07:12 AM

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