Excel Export

[Matt]

I am trying to export a RadGridView to excel and running into a few hiccups. I've been able to to work around these but would like to know if there is a better approach I could/should take. 

1.) When exporting to excel the RadGridView seems to only support .xls extension rather than .xlsx. These means the first time the user opens the file in excel they get prompted to reformat and re-save. To work around this currently I am using excel interop to open the file in the back ground and re-save it as .xlsx.

    Is there a way to save it directly as a .xlsx?

2.) The export method of the RadGridView appears to only support saving directly to a file.

     Can the export be streamed so that it is opened with out having to create a temp file so that when saved the user is forced to select a save location and name (this may be more of an issue with excel rather than telerik)?

3.) I did not see an option to be able to export and preserve pinned columns and pinning the headers in the excel file, currently doing this using excel interop.

    Is there a way to export with pinned columns pinned in the excel file and pinning the header row?

 

Below is the approach I am currently taking to accomplish the above would like to be able to clean it up if there are any built in options.

public static void ExportToExcel(this RadGridView grid)
        {
            Microsoft.Office.Interop.Excel.Application excel = null;
            Workbooks wbs = null;
            Workbook wb = null;
            Sheets sheets = null;
            Worksheet sheet = null;
            Window activeWindow = null;

            var fileName = string.Format("{0}-{1}", "SoftproExtract", DateTime.Now.ToString("yyyyMMdd-hhmmss"));
            var extension = "xls";
            string tempPath = string.Format("{0}{1}.{2}", Path.GetTempPath(), fileName, extension);
            string userPath = string.Empty;

            try
            {
                var exporter = new ExportToExcelML(grid) { HiddenColumnOption = HiddenOption.DoNotExport, HiddenRowOption = HiddenOption.DoNotExport, ExportVisualSettings = true, SummariesExportOption = SummariesOption.DoNotExport };

                exporter.RunExport(tempPath);

                excel = new Microsoft.Office.Interop.Excel.Application { Visible = false, Interactive = false };
                excel.DefaultFilePath = "";
                wbs = excel.Workbooks;
                wb = wbs.Open(tempPath);
                sheets = wb.Sheets;
                sheet = sheets[1];


                sheet.Activate();
                activeWindow = excel.ActiveWindow;
                activeWindow.SplitRow = 1;
                activeWindow.SplitColumn = grid.Columns.Count(x => x.PinPosition == PinnedColumnPosition.Left);
                activeWindow.FreezePanes = true;

                extension = "xlsx";
                userPath = string.Format("{0}\\{1}.{2}", Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), fileName, extension);
                wb.SaveAs(userPath, XlFileFormat.xlWorkbookDefault);
            }
            catch (Exception ex)
            {
                ex.WriteLog();
                ex.Show();
            }
            finally
            {
                if(wb != null)
                    wb.Close();
                if(excel != null)
                    excel.Quit();

                Marshal.ReleaseComObject(activeWindow);
                Marshal.ReleaseComObject(sheet);
                Marshal.ReleaseComObject(sheets);
                Marshal.ReleaseComObject(wb);
                Marshal.ReleaseComObject(wbs);
                Marshal.ReleaseComObject(excel);

                if (File.Exists(tempPath))
                    File.Delete(tempPath);
            }

            if (File.Exists(userPath))
                Process.Start(userPath);
        }

[Ralitsa]

Hello Matt,

Thank you for contacting us. 

RadGridView`s data can be exported to excel files using one of our export providers: 

1. The ExportToExcelML exports data in the Excel XML format. This is an XML-based format that cannot be saved with xlsx file extension. The ExcelML format can be read by MS Excel 2002 and above.  

2. The GridViewSpreadExport utilizes our RadSpreadProcessing libraries to export the content to xlsx, csv, pdf and txt formats. The application should be with target framework .Net 4.0 or higher version. If you use that export provider, you can freeze pinned columns and rows. Here is the code snippet how to achieved it: 

GridViewSpreadExport spreadExporter = new GridViewSpreadExport(this.radGridView1);
spreadExporter.FreezeHeaderRow = true;
spreadExporter.FreezePinnedColumns = true;
spreadExporter.FreezePinnedRows = true;
spreadExporter.SheetMaxRows = ExcelMaxRows._1048576;
spreadExporter.ExportFormat = SpreadExportFormat.Xlsx;
spreadExporter.FileExportMode = FileExportMode.CreateOrOverrideFile;
spreadExporter.RunExport(@"..\..\exportedFile", new SpreadExportRenderer());

No matter of export provider, you can prompts the user to select a location for saving a file using SaveFileDialog. Please refer to the code snippet below: 

SaveFileDialog dialog = new SaveFileDialog();
dialog.FileName = "exportedFile";
dialog.Filter = "xlsx files (*.xlsx)|*.xlsx|All files (*.*)|*.*";

if (dialog.ShowDialog() == DialogResult.OK)
{
    ExportData(dialog.FileName);
}

In the attachments you can find a sample demo demonstrating the code snippets. 

I hope this will be informative. If you have further questions, feel free to write back.

Regards,
Ralitsa
Telerik

[Christian]

It does not work if I put the grid invisible. How can I solve that?

[Ralitsa]

Hi Christian,

Thank you for writing us. 

You can try to call the LoadElementTree method, so it will force loading RadGridView`s elements. Here is the code snippet: 

this.radGridView1.Visible = false;
this.radGridView1.LoadElementTree();

In the attachment, you can find the modified sample application. 

I hope this information helps. If you have any additional questions, please let me know.

Regards,
Ralitsa
Telerik by Progress

[Michael]

Is there a way to export 2 gridviews to the same sheet? Or is there a way to use a saved excel template for an export?

[Dess | Tech Support Engineer, Principal]

Hello, Michael,    

The exporting functionality that RadGridView offers allows adding a new sheet to an existing one. You can use the FileExportMode.NewSheetInExistingFile option in the GridViewSpreadExport. I have prepared a sample project for your reference. 

I hope this information helps. If you have any additional questions, please let me know.  

 

Regards,
Dess
Progress Telerik

[Frank]

I have an interesting tools. You can use it for best performance. ZetExcel. Just Try it.

[Dess | Tech Support Engineer, Principal]

Hello, Frank, 

Telerik UI for WinForms suite uses the Document Processing libraries for implementing the different export functionalities: https://docs.telerik.com/devtools/document-processing/getting-started/getting-started 

For better memory consumption and export performance, you can use the GridViewSpreadStreamExport which allows you to create big documents (without loading the entire document in the memory) and export them to the most common formats:  https://docs.telerik.com/devtools/winforms/controls/gridview/exporting-data/spreadstream-export 
 
Should you have further questions please let me know.

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