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

Can I format number when export to excelML in radgrid?

14 Answers 373 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Bruce Lam
Top achievements
Rank 1
Bruce Lam asked on 10 Dec 2009, 07:02 AM
Can I format number when export to excelML in radgrid? please tell me.

thank you telerik team.

14 Answers, 1 is accepted

Sort by
0
Daniel
Telerik team
answered on 10 Dec 2009, 11:34 AM
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.
0
Bruce Lam
Top achievements
Rank 1
answered on 11 Dec 2009, 03:14 AM
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
0
Daniel
Telerik team
answered on 15 Dec 2009, 05:49 PM
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.
0
siva
Top achievements
Rank 1
answered on 18 Jan 2011, 12:52 PM

 

<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

 

0
Daniel
Telerik team
answered on 21 Jan 2011, 11:39 AM
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
Browse the vast support resources we have to jump start your development with RadControls for ASP.NET AJAX. See how to integrate our AJAX controls seamlessly in SharePoint 2007/2010 visiting our common SharePoint portal.
0
Dhamodharan
Top achievements
Rank 1
answered on 19 Apr 2011, 02:32 PM
Hi Daniel,

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

is this possible?. please let me know

Thanks,
Dhamu.
0
Daniel
Telerik team
answered on 22 Apr 2011, 12:25 PM
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

Browse the vast support resources we have to jump start your development with RadControls for ASP.NET AJAX. See how to integrate our AJAX controls seamlessly in SharePoint 2007/2010 visiting our common SharePoint portal.

0
San
Top achievements
Rank 1
answered on 20 Nov 2012, 01:32 PM
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
0
Daniel
Telerik team
answered on 20 Nov 2012, 04:39 PM
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
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.
0
Matt
Top achievements
Rank 1
answered on 12 Apr 2013, 01:26 PM
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);
}
0
Matt
Top achievements
Rank 1
answered on 12 Apr 2013, 01:38 PM
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";
            }
        }
0
Daniel
Telerik team
answered on 17 Apr 2013, 12:07 PM
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
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.
0
Vijender Reddy
Top achievements
Rank 1
answered on 04 Nov 2013, 02:44 PM
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.
0
Princy
Top achievements
Rank 2
answered on 06 Nov 2013, 12:50 PM
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
Tags
Grid
Asked by
Bruce Lam
Top achievements
Rank 1
Answers by
Daniel
Telerik team
Bruce Lam
Top achievements
Rank 1
siva
Top achievements
Rank 1
Dhamodharan
Top achievements
Rank 1
San
Top achievements
Rank 1
Matt
Top achievements
Rank 1
Vijender Reddy
Top achievements
Rank 1
Princy
Top achievements
Rank 2
Share this question
or