ExportToExcel() very slow.

[AL]

Hi,

I am puzzled: Why does exporting to excel takes such a looooooooong time?

Getting data from database into a DataTable takes 5 seconds. Then I bind RadGrid to this DataTable in server side (RadGrid visible = false). Now it takes more than a minute to export it to excel (until I get the save/open file prompt). Is there any way to directly export DataTable to Excel? If, not is there any way to make export to excel much faster.

Here's the code (RadGrid's visibility is set to false, just exporting data from user selected dropdowns to excel):

            RadGrid1.AutoGenerateColumns = true;
            RadGrid1.DataSource = myDataTable; //from database
            RadGrid1.DataBind();

            RadGrid1.ExportSettings.ExportOnlyData = true;
            RadGrid1.ExportSettings.Excel.Format = GridExcelExportFormat.ExcelML;           
            RadGrid1.MasterTableView.ExportToExcel();

Any help and/or suggestions is deeply appreciated!

Thanks

[AL]

Any suggestion from anyone to make export to excel faster?

[Daniel]

Hello AL,

There are two ways to improve the exporting speed:
- reduce the number of columns / rows
- switch to CSV format

Another option is to export your data manually (that is to avoid the built-in export). For that purpose you can use the ExcelML format or a third-party library like NPOI.
ExcelML structure / GridExcelBuilder
NPOI @ CodePlex

Best regards,
Daniel
the Telerik team

[AL]

Hi Daniel,

I tried Export to CSV. But I only get the column headers (data part was missing). Can you help with ExportToCSV() code?

Here is my code, but data does not get exported:

            RadGrid1.AutoGenerateColumns = true;
            RadGrid1.DataSource = myDataTable; //from database
            RadGrid1.DataBind();

            RadGrid1.ExportSettings.ExportOnlyData = false;
            RadGrid1.MasterTableView.ExportToCSV();

Thanks.

[Daniel]

Hello AL,

I suppose that you use GridTemplateColumns. If this is so, please post the declaration of one of those columns in this thread. This way I will be able to provide better suggestion for your scenario.

Best regards,
Daniel
the Telerik team

[AL]

Hi Daniel,

I am not using GridTemplateColumns. In fact I am not even displaying the grid (see grid visibility = false mentioned in original post).

Ok, this is what I have in aspx page:

<

 

 

telerik:RadGrid ID="RadGrid1" runat="server" Visible="false" ></telerik:RadGrid> 

AND this is what I have in code behind:
RadGrid1.AutoGenerateColumns = true;
            RadGrid1.DataSource = myDataTable; //from database
            RadGrid1.DataBind();

            RadGrid1.ExportSettings.ExportOnlyData = false;
            RadGrid1.MasterTableView.ExportToCSV();

I am simply populating RadGrid from codebehind and trying to export its contents to csv. Can you help?

Thanks again.

AL

[Daniel]

Hello AL,

Please modify the export method this way:

RadGrid1.AutoGenerateColumns = true;
RadGrid1.DataSource = myDataTable; //from database

RadGrid1.Visible = true;

RadGrid1.DataBind();
RadGrid1.ExportSettings.ExportOnlyData = false;
RadGrid1.MasterTableView.ExportToCSV();

Note how this modification affects the exported file only. This is because the output is redirected to the file and the page won't be changed at all.

By the way, this is listed as an unsupported scenario in our documentation:
CSV export topic

Regards,
Daniel
the Telerik team