Can I format number when export to excelML in radgrid?

[Bruce Lam]

Can I format number when export to excelML in radgrid? please tell me.

thank you telerik team.

[Daniel]

Hello Bruce,

You should assign a style on ExcelMLExportRowCreated and then set the appropriate number format for this style on ExcelMLExportStylesCreated:

protected void RadGrid1_ExcelMLExportRowCreated(object source, GridExportExcelMLRowCreatedArgs e)
{
   e.Row.Cells.GetCellByName("Name").StyleValue = "myCustomStyle";
}
protected void RadGrid1_ExcelMLExportStylesCreated(object source, GridExportExcelMLStyleCreatedArgs e)
{
   StyleElement myStyle = new StyleElement("myCustomStyle");
   myStyle.NumberFormat.FormatType = NumberFormatType.Currency;
   myStyle.FontStyle.Bold = true;
   e.Styles.Add(myStyle);
}

You can also try one of the following formats:
General, General Number, General Date, Long Date, Medium Date, Short Date, Long Time, Medium Time, Short Time, Currency, Euro Currency, Fixed, Standard, Percent, Scientific
 
Best regards,
Daniel
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Bruce Lam]

thank you Daniel,
But, DataSource of my RadGridExport is a DataTable, this table is created while runtime, I think number format style doesn't work on columns that is not BoundComlumn.
how do I solve this problem? please help me as possible as.
thanks

[Daniel]

Hello Bruce,

Please try the following demo locally and let me know what your findings are:

protected void Button1_Click(object sender, EventArgs e)
{
    RadGrid1.MasterTableView.ExportToExcel();
}
protected void RadGrid1_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
{
    DataTable table = new DataTable();
    table.Columns.Add("ID", typeof(int));
    table.Columns.Add("Item", typeof(double));
    table.Columns.Add("Date", typeof(DateTime));

    for (int i = 0; i < 10; i++)
        table.Rows.Add(i, i * 1.546190, DateTime.Now);

    RadGrid1.DataSource = table;
}
protected void RadGrid1_ExcelMLExportRowCreated(object source, Telerik.Web.UI.GridExcelBuilder.GridExportExcelMLRowCreatedArgs e)
{
    e.Row.Cells.GetCellByName("ID").StyleValue = "idSt";
    e.Row.Cells.GetCellByName("Item").StyleValue = "itemSt";
    e.Row.Cells.GetCellByName("Date").StyleValue = "dateSt";
}
protected void RadGrid1_ExcelMLExportStylesCreated(object source, GridExportExcelMLStyleCreatedArgs e)
{
    StyleElement style = new StyleElement("idSt");
    style.NumberFormat.FormatType = NumberFormatType.EuroCurrency;
    e.Styles.Add(style);

    StyleElement style1 = new StyleElement("itemSt");
    style1.NumberFormat.FormatType = NumberFormatType.Currency;
    e.Styles.Add(style1);

    StyleElement style2 = new StyleElement("dateSt");
    style2.NumberFormat.FormatType = NumberFormatType.LongTime;
    e.Styles.Add(style2);
}

<asp:Button ID="Button1" runat="server" Text="Excel" OnClick="Button1_Click" />
<telerik:RadGrid ID="RadGrid1" runat="server" GridLines="None" OnExcelMLExportRowCreated="RadGrid1_ExcelMLExportRowCreated"
    OnExcelMLExportStylesCreated="RadGrid1_ExcelMLExportStylesCreated" OnNeedDataSource="RadGrid1_NeedDataSource">
    <ExportSettings Excel-Format="ExcelML" ExportOnlyData="true" OpenInNewWindow="true" />
    <MasterTableView AutoGenerateColumns="false">
        <Columns>
            <telerik:GridNumericColumn DataField="ID" HeaderText="ID" />
            <telerik:GridBoundColumn DataField="Item" HeaderText="Item" />
            <telerik:GridDateTimeColumn DataField="Date" HeaderText="Date" />
        </Columns>
    </MasterTableView>
</telerik:RadGrid>

Best regards,
Daniel
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[siva]

 

<table border="0" class="formlayout" width="100%" cellspacing="0" cellpadding="0">

 

 

<tbody><tr>

 

 

<td>

 

 

<telerik:RadGrid ID="rgRebalDealImpact" runat="server" AutoGenerateColumns="False"

 

 

GridLines="None" Skin="Office2007" Height="200px" ShowFooter="True" ExportSettings-Excel-Format="ExcelML" OnExcelMLExportRowCreated="rgRebalDealImpact_ExcelMLExportRowCreated" ExportOnlyData="true" OnExcelMLExportStylesCreated="rgRebalDealImpact_ExcelMLExportStylesCreated"

 

 

Style="background-color: white" Width="97%">

 

 

<MasterTableView>

 

 

<Columns>

 

 

<telerik:GridTemplateColumn HeaderText="Security" UniqueName="Security">

 

 

<ItemTemplate>

 

 

<asp:Label ID="lblSecurity" runat="server" Text='<%# Bind("Security") %>'> </asp:Label>

 

 

</ItemTemplate>

 

 

<ItemStyle HorizontalAlign="Left" />

 

 

</telerik:GridTemplateColumn>

 

 

<telerik:GridTemplateColumn HeaderText="Security Name" UniqueName="Security_Name">

 

 

<ItemTemplate>

 

 

<asp:Label ID="lblSecurity_Name" runat="server" Text='<%# Bind("Security_Name") %>'> </asp:Label>

 

 

</ItemTemplate>

 

 

<ItemStyle HorizontalAlign="Left" />

 

 

</telerik:GridTemplateColumn>

am using above grid structure ,
now i need to export data into excel with numberformat plz help me , i tried above method but it doesn work, do i need to import any dll or using import

 

[Daniel]

Hello Siva,

I'm afraid that the provided information is not enough to draw a clear picture about the problem. Please attach a screenshot that illustrates the issue and if possible, post the code-behind part.

In the meantime you can examine our online resources:
[demo] Export to ExcelML
[doc] ExcelML basics

Regards,
Daniel
the Telerik team

[Dhamodharan]

Hi Daniel,

Can i get date format in this format "MM-dd-yyyy".

is this possible?. please let me know

Thanks,
Dhamu.

[Daniel]

Hello Dhamu,

You can use the following approach to set a custom number/date format:

style.NumberFormat.Attributes["ss:Format"] = "MM/dd";

For more information please refer to the help topics posted in my previous answer.

Regards,
Daniel
the Telerik team

[San]

Hi
i have written the below code to format the date column while exporting and it is working fine.
is there any other ways to format the columns while exporting to excel.i don't want user these events and i wanted to show only date instead of date with time..


Code:   
protected void gdDisplay_ExcelMLExportRowCreated(object sender, Telerik.Web.UI.GridExcelBuilder.GridExportExcelMLRowCreatedArgs e)
        {

            //e.Row.Cells.GetCellByName("Date").StyleValue = "DateStyleValue";
            e.Row.Cells.GetCellByName("InvoiceDate").StyleValue = "InvoiceDateStyleValue";
        }

        protected void gdDisplay_ExcelMLExportStylesCreated(object sender, GridExportExcelMLStyleCreatedArgs e)
        {
            StyleElement InvoiceStyle = new StyleElement("InvoiceDateStyleValue");
            InvoiceStyle.NumberFormat.FormatType = NumberFormatType.ShortDate;
            e.Styles.Add(InvoiceStyle);
        }

Thanks
Santosh

[Daniel]

Hello Santosh,

The date format can be set either automatically depending on your local settings or explicitly, by using the aforementioned events.

Kind regards,
Daniel
the Telerik team

[Matt]

I can't get these methods to produce a value in percent format. If the RadGrid displays in the correct format, the export should automatically inherit the format.

protected void RG_QABSR_ExcelMLExportRowCreated(object sender, GridExportExcelMLRowCreatedArgs e)
{
    if (e.RowType == GridExportExcelMLRowType.DataRow)
    {
        e.Row.Cells.GetCellByName("PtoG1").StyleValue = "PtoGPercent";
        e.Row.Cells.GetCellByName("PtoG2").StyleValue = "PtoGPercent";
        e.Row.Cells.GetCellByName("PtoG3").StyleValue = "PtoGPercent";
        e.Row.Cells.GetCellByName("PtoG4").StyleValue = "PtoGPercent";
        e.Row.Cells.GetCellByName("PtoG5").StyleValue = "PtoGPercent";
        e.Row.Cells.GetCellByName("PtoG").StyleValue = "PtoGPercent";
        e.Row.Cells.GetCellByName("EPtoG").StyleValue = "PtoGPercent";
    }
}

protected void RG_QABSR_ExcelMLExportStylesCreated(object sender, GridExportExcelMLStyleCreatedArgs e)
{
    StyleElement styleElement = new StyleElement("PtoGPercent");
    styleElement.NumberFormat.FormatType = NumberFormatType.Percent;
    e.Styles.Add(styleElement);
}

[Matt]

BTW, this works.

http://www.telerik.com/community/forums/aspnet-ajax/grid/exporttoexcel-format.aspx

protected void RG_ExcelExportCellFormatting(object sender, ExcelExportCellFormattingEventArgs e)
        {
            if (e.FormattedColumn.UniqueName == "PtoG1" || e.FormattedColumn.UniqueName == "PtoG2" || e.FormattedColumn.UniqueName == "PtoG3" || e.FormattedColumn.UniqueName == "PtoG3" || e.FormattedColumn.UniqueName == "PtoG4" || e.FormattedColumn.UniqueName == "PtoG5" || e.FormattedColumn.UniqueName == "PtoG" || e.FormattedColumn.UniqueName == "EPtoG")
            {
                e.Cell.Style["mso-number-format"] = "Percent";
            }
        }

[Daniel]

Hello Matt,

The first snipped would work for the HTML format unlike the second which is ExcelML-specific.
Excel Format (HTML-Based)
ExcelML basics
Let me know if you need more information.

Best regards,
Daniel
the Telerik team

[Vijender Reddy]

Hello Daniel,
         Your Solutions for finding e.Row.Cells.GetCellByName("something") doesnot works if it is GridTemplateColumn. Have a look at it not matter how i try i dont find any possible soultion to find CellElement if it is GridTemplateColumn, however your solution works if i change to GridBoundColumn. I need to find the CellElement when I use GridTempalteColumn please provide me possible solution as early as you could.

Thank you,
Vijender.

[Princy]

Hi Vijender,

Please try the following code snippet to set the number format for GridTemplateColumn during export.

ASPX:

<telerik:GridTemplateColumn>
    <ItemTemplate>
        <asp:Label ID="Lab1" runat="server" Text='<%# Eval("Number") %>'></asp:Label>
    </ItemTemplate>
</telerik:GridTemplateColumn>

C#:

static string export = "false";
protected void RadGrid1_ItemCommand(object sender, Telerik.Web.UI.GridCommandEventArgs e)
{
    if (e.CommandName == RadGrid.ExportToExcelCommandName)
    {
        export = "true";
    }
}
protected void RadGrid1_PreRender(object sender, EventArgs e)
{
    if (export == "true")
    {
        foreach (GridDataItem ditem in RadGrid1.MasterTableView.Items)
        {
            Label lb = (Label)ditem.FindControl("Lab1");
            lb.Text = string.Format("{0:#.00}", Convert.ToDecimal(lb.Text) / 100);
        }
        export = "false";
    }
}

Thanks,
Princy