ExportCellFormatting event not getting fired

[Vikas]

Hi Team,

I'm trying to export my grid to excel and to set some number formats I've attached a handler to ExportCellFormatting event. This used to work when I was using telerik UI dll with version 2012.1.215.35.

Recently, I upgraded my telerik UI to version 2014.2.618.35 and after this the ExportCellFormatting event does not get fired. Please suggest.

Thanks

[Shinu]

Hi Vikas,

I was not able to replicate this issue at my end. Please note that the ExcelExportCellFormatting event (Excel-specific) is marked as obsolete as from UI for ASP.NET AJAX Q1 2011. If you want to set any styles you can use the ItemCreated event as shown here . If this doesn't help, provide your full code snippet.

Thanks,
Shinu

[Vikas]

Hi Shinu,

Thanks for your reply.
I'm using ExportCellFormatting event only to format my excel. Issue is that this event is not called. Please see the below code sample.

Default.aspx

<telerik:RadScriptManager runat="server" ID="RadScriptManager1" />
        <script type="text/javascript">
            function onRequestStart(sender, args) {
                if (args.get_eventTarget().indexOf("ExportTo") >= 0) {
                    args.set_enableAjax(false);
                }
            }
        </script>
        <telerik:RadAjaxManager ID="RadAjaxManager1" runat="server">
            <ClientEvents OnRequestStart="onRequestStart"></ClientEvents>
            <AjaxSettings>
                <telerik:AjaxSetting AjaxControlID="RadGrid1">
                    <UpdatedControls>
                        <telerik:AjaxUpdatedControl ControlID="RadGrid1"></telerik:AjaxUpdatedControl>
                    </UpdatedControls>
                </telerik:AjaxSetting>
            </AjaxSettings>
        </telerik:RadAjaxManager>
        <telerik:RadGrid ID="RadGrid1" AllowSorting="True"
            AutoGenerateColumns="false" AllowPaging="True" PageSize="10" runat="server" OnExportCellFormatting="RadGrid1_ExportCellFormatting">
            <ExportSettings HideStructureColumns="true">
            </ExportSettings>
            <MasterTableView ClientDataKeyNames="CustomerID" CommandItemDisplay="Top">
                <RowIndicatorColumn Visible="False">
                    <HeaderStyle Width="20px" />
                </RowIndicatorColumn>
                <ExpandCollapseColumn Resizable="False" Visible="False">
                    <HeaderStyle Width="20px" />
                </ExpandCollapseColumn>
                <Columns>
                    <telerik:GridBoundColumn DataField="CustomerID" HeaderText="Customer ID" ReadOnly="true">
                    </telerik:GridBoundColumn>
                    <telerik:GridBoundColumn DataField="CompanyName" HeaderText="Company Name" ColumnEditorID="GridTextBoxEditor">
                    </telerik:GridBoundColumn>
                    <telerik:GridBoundColumn DataField="ContactName" HeaderText="Contact Name" ColumnEditorID="GridTextBoxEditor">
                    </telerik:GridBoundColumn>
                    <telerik:GridBoundColumn DataField="ContactTitle" HeaderText="Contact Title" ColumnEditorID="GridTextBoxEditor">
                    </telerik:GridBoundColumn>
                    <telerik:GridClientDeleteColumn HeaderText="Delete" ButtonType="ImageButton">
                        <HeaderStyle Width="70px" />
                    </telerik:GridClientDeleteColumn>
                </Columns>
                <EditFormSettings>
                    <PopUpSettings ScrollBars="None" />
                </EditFormSettings>
                <CommandItemSettings ShowExportToExcelButton="true" />
            </MasterTableView>
            <ClientSettings>
                <DataBinding Location="http://localhost:59507/NorthwindDataService.svc/" ResponseType="JSONP">
                    <DataService TableName="Customers" Type="OData" />
                </DataBinding>
            </ClientSettings>
        </telerik:RadGrid>

Default.aspx.cs

protected void RadGrid1_ExportCellFormatting(object sender, Telerik.Web.UI.ExportCellFormattingEventArgs e)
    {
        if (e.FormattedColumn.ColumnType.IndexOf(GridCurrencyColumnName) > -1)
        {
            e.Cell.Style["mso-number-format"] = @"$0.00\;($0.00)";
        }
        else if (e.FormattedColumn.DataTypeName == "System.String")
        {
            e.Cell.Style["mso-number-format"] = @"\@";
            e.Cell.HorizontalAlign = HorizontalAlign.Left;
        }
        else if (e.FormattedColumn.DataTypeName == "System.DateTime")
        {
            e.Cell.Style["mso-number-format"] = @"\@";
            e.Cell.HorizontalAlign = HorizontalAlign.Left;
        }
    }

Please note that if I use "<asp:SqlDataSource>" as my data source rather than the wcf data service, this event works fine.

Thanks

[Kostadin]

Hello Vikas,

Note that export feature works only with a server side binding. Please make sure you are using one in order to export the grid to Excel.

Regards,
Kostadin
Telerik

 

 

[Vikas]

Hi Kostadin,

I figured it out. The issue is when I set the excel format as BIFF, the ExportCellFormatting event does not get fired. And therefore, the numbers in my excel comes formatted as text. Please suggest how can I handle this formatting.

Thanks,
Vikas

[Kostadin]

Hi Vikas,

Note that ExportCellFormatting event is firing only when HTML Based format is used. If you want to use a Biff format there is a separate event named OnBiffExporting. You could apply Format the of each cell as demonstrated below.

protected void RadGrid2_BiffExporting(object sender, GridBiffExportingEventArgs e)
{
    Telerik.Web.UI.ExportInfrastructure.Cell cell1 = e.ExportStructure.Tables[0].Cells["B2"];
    cell1.Format = "$0.00";
}

Regards,
Kostadin
Telerik

 

 

[Vikas]

Hi Kostadin,

I've attached the handler to the OnBiffExporting event and my export functionality is working fine now. However, there is a considerable hit in the performance. As the BiffExporting event fires for each exporting cell, I'm checking the cell's content for numeric or currency every time and depending upon the content, I'm setting its format. This takes lot of processing time especially when I've tens of thousands of rows. Please suggest if these is some alternate way to implement this to help improve the performance.

Thanks,
Vikas

[Daniel]

Hello Vikas,

BiffExporting event fires only one time, for the whole control and not for each and every cell. If you have set a DataFormatString for your columns this may cause a problem when exporting. In this case, I would recommend that you enable the SuppressColumnDataFormatStrings property in ExportSettings.

Regards,
Daniel
Telerik

 

 

[Vikas]

Hi Daniel,

I've attached the BiffExporting event in my code and put a breakpoint in it. I've noticed that the breakpoint hits once for the grid but in the arguments I'm not getting any column information. Therefore, I've to format each and every cell independently. I was thinking about any option so that I could get some column information and format that column depending upon its data type. That  column formatting then will be inherited by all the cells in that column. Below is the code I've used. Its not causing any problem in the output excel but it is considerably slow when exporting thousands of rows.

static void grid_BiffExporting(object sender, GridBiffExportingEventArgs e)
        {
            int numericData;
            Regex moneyRegex = new Regex(@"\$(\d{1,3},)*\d+\.\d{2}");
            foreach (Telerik.Web.UI.ExportInfrastructure.Cell cell in e.ExportStructure.Tables[0].Cells)
            {
                if (int.TryParse(cell.Text, out numericData))
                {
                    cell.Value = numericData;
                }
                else if (moneyRegex.IsMatch(cell.Text))
                {
                    cell.Format = @"$#,##0.00_);($#,##0.00)";
                    cell.Value = decimal.Parse(cell.Text, NumberStyles.Currency);
                }
            }
        }

Thanks,
Vikas

[Daniel]

Hello Vikas,

If you want to apply custom format to a specific column only you can get that column by index.

e.ExportStructure.Tables[0].Columns[columnIndex].Cells...

In this case you don't have to traverse all cells in the worksheet.

Regards,
Daniel
Telerik

 

 

[Vikas]

Hi Daniel,

In my application, I've a common Radgrid implementation which I use through out the application. At different pages, there are different grids and all these grids use the same Radgrid definition. Therefore, I can not identify a column based on its index. I need to identify a column depending upon its data type.
For example, in one of my grid the amount column is at 4th index whereas in some other grid this column is at 8th index.

Please suggest how can I identify a column by its data type. Also can I set a format for the whole column in BiffExporting event rather than traversing all cells of that column.

Thanks,
Vikas

[Daniel]

Hello Vikas,

I think that you should be able to get the column index this way:

protected void RadGrid1_BiffExporting(object sender, GridBiffExportingEventArgs e)
{
    for (int colNum = 0; colNum < RadGrid1.MasterTableView.RenderColumns.Length; colNum++)
    {
        if(RadGrid1.MasterTableView.RenderColumns[colNum].DataType...)
            e.ExportStructure.Tables[0].Columns[colNum]....
    }
}

Please note that this is a dummy code (example only).

Regards,
Daniel
Telerik