A few questions about exporting RadGridView to Excel?

[Nicholas]

So what I have to do basically is create an Excel report of the selected rows of my RadGridView and create a header on the first few rows (above my RadGridView data) with a little information about the report so like the time it was created for example. For now, I'm using GridViewSpreadExport class and passing the RadGridView into the constructor and using the SpreadExportRenderer class. and calling RunExport(...). So I guess my questions are:

 

1.) I seen 3 different ways to export  RadGridView data to Excel: GridViewSpreadExport, GridViewSpreadStreamExport, and ExportToExcelML. Which one is best for what I'm trying to accomplish? 

 

2.) How can I just export the data of the selected rows of the RadGridView?

 

3.) Is there a way to export 2 RadGridView to the same Excel sheet? 

[Dess | Tech Support Engineer, Principal]

Hello, Nicholas, 

- ExportToExcelML offers excellent performance and does not require MS Office installation on user machines. The ExcelML format can be read by MS Excel 2002 (MS Office XP) and above. Direct export to the xlsx format is possible by utilizing our RadSpreadProcessing libraries in the GridViewSpreadExport. The GridViewSpreadStreamExport uses the RadSpreadStreamProcessing library which allows you to create big documents (without loading the entire document in the memory) and export them to the most common formats.

For the common case, I would recommend you to use the GridViewSpreadExport. The WorkbookCreated event is triggered on the SpreadExportRenderer object when the workbook is ready to be exported. You can introduce any final changes to the exported workbook. Additional information is available in the following help article: https://docs.telerik.com/devtools/winforms/gridview/exporting-data/spreadstream-export 

- In order to export only the selected rows in RadGridView, you can create a new RadGridView filled only with the selected data and then export this new grid. There is not a built-in mechanism for this.

- The FileExportMode property determines whether the data will be exported into an existing or a new file. If new is chosen and such exists it will be overridden. Available options are:
 NewSheetInExistingFile: This option will create a new sheet in an already existing file.
 CreateOrOverrideFile: Creates new or overrides an existing file.


I hope this information helps. If you need any further assistance please don't hesitate to contact me. 

Regards,
Dess | Tech Support Engineer, Sr.
Progress Telerik

[Nicholas]

Okay, thank you for your help. One more quick question: Using the GridViewSpreadExport way and WorkbookCreated event, how can I put a header row that takes up let's say the first 2 rows and first 2 columns of the sheet and shift all the GridView results down? I can get the header information in the right area, but it messes up the GridView data in those first 2 rows and columns? It doesn't shift all the GridView data down at once, just those first 2 rows and columns. Is there an easy way to do this?  

[Dess | Tech Support Engineer, Principal]

Hello, Nicholas, 

The following blogs are quite useful about manipulating the workbook and introducing any customizations:
https://www.telerik.com/blogs/getting-started-with-radspreadprocessing-volume-1
https://www.telerik.com/blogs/getting-started-with-radspreadprocessing-vol2
https://www.telerik.com/blogs/getting-started-with-radspreadprocessing-vol3

You can also refer to the online documentation as well which introduce several useful articles on this topic:
https://docs.telerik.com/devtools/document-processing/libraries/radspreadprocessing/working-with-rows-and-columns/insert-and-remove
https://docs.telerik.com/devtools/document-processing/libraries/radspreadprocessing/features/headers-and-footers

Should you have further questions please let me know.

Regards,
Dess | Tech Support Engineer, Sr.
Progress Telerik

[JeffSM]

I was getting this error: https://www.telerik.com/support/kb/aspnet-ajax/grid/details/we-found-a-problem-with-this-formula-when-exporting-to-xlsx
But when I did this, then the fields datetime get bad format, and over all, the column DateTime with empty value crashes. So I made this fix.

        protected void RadGrid1_InfrastructureExporting(object sender, GridInfrastructureExportingEventArgs e)
        {
            var table = e.ExportStructure.Tables[0];

            //set the string format for all cells in the sheet if you only have string data or you don't know where issues may come up
            for (var col = 0; col < (sender as RadGrid).MasterTableView.RenderColumns.Length; col++)
            {
                for (var row = 1; row <= table.Rows.Count; row++)
                {
                    var txt = table.Cells[col, row].Text;

                    if (string.IsNullOrEmpty(txt) ||
                        txt.StartsWith("-") ||
                        txt.StartsWith("=") ||
                        txt.StartsWith("(") ||
                        txt.StartsWith("+")
                        )
                        table.Cells[col, row].Format = "@";
                }
            }
        }

[Dess | Tech Support Engineer, Principal]

Hello, Jefferson,      
 
According to the provided code snippet and referred KB article, it seems that you are using the grid in ASP.NET AJAX. The InfrastructureExporting event is not relevant for the WinForms RadGridView. Have in mind that this forum is related to the Telerik UI for WinForms suite. Feel free to post any questions or sample solutions regarding the ASP.NET AJAX suite in the relevant forum: https://www.telerik.com/forums

As to the export options in RadGridView in the Telerik UI for WinForms note that each column in RadGridView has ExcelExportType property which can be used for explicitly setting the data type of the cells in the exported document. In order to change the format of the exported data, you can set the ExcelExportType property of the specific column to Custom and specify the ExcelExportFormatString property with the desired format. Additional information is available in the following help article: https://docs.telerik.com/devtools/winforms/controls/gridview/exporting-data/spread-export

I hope this information helps. If you need any further assistance please don't hesitate to contact me. 

Regards,
Dess | Tech Support Engineer, Sr.
Progress Telerik

[JeffSM]

Ups, my mistake, I was looking for asp.net! Sorry!