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

ExportCellFormatting event not getting fired

11 Answers 247 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Vikas
Top achievements
Rank 1
Vikas asked on 25 Jul 2014, 10:38 AM
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

11 Answers, 1 is accepted

Sort by
0
Shinu
Top achievements
Rank 1
answered on 29 Jul 2014, 04:11 AM
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
0
Vikas
Top achievements
Rank 1
answered on 29 Jul 2014, 04:33 AM
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
0
Kostadin
Telerik team
answered on 30 Jul 2014, 06:51 AM
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
 

Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

 
0
Vikas
Top achievements
Rank 1
answered on 30 Jul 2014, 12:55 PM
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
0
Kostadin
Telerik team
answered on 01 Aug 2014, 11:30 AM
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
 

Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

 
0
Vikas
Top achievements
Rank 1
answered on 04 Aug 2014, 06:04 AM
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
0
Daniel
Telerik team
answered on 07 Aug 2014, 08:21 AM
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
 

Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

 
0
Vikas
Top achievements
Rank 1
answered on 08 Aug 2014, 12:34 PM
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
0
Daniel
Telerik team
answered on 13 Aug 2014, 07:53 AM
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
 

Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

 
0
Vikas
Top achievements
Rank 1
answered on 13 Aug 2014, 08:56 AM
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
0
Daniel
Telerik team
answered on 18 Aug 2014, 08:55 AM
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 

Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

 
Tags
Grid
Asked by
Vikas
Top achievements
Rank 1
Answers by
Shinu
Top achievements
Rank 1
Vikas
Top achievements
Rank 1
Kostadin
Telerik team
Daniel
Telerik team
Share this question
or