Excel Export

2 posts, 0 answers
  1. Matt
    Matt avatar
    3 posts
    Member since:
    Nov 2015

    Posted 28 Dec 2015 Link to this post

    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);
            }

  2. Ralitsa
    Admin
    Ralitsa avatar
    176 posts

    Posted 29 Dec 2015 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  3. UI for WinForms is Visual Studio 2017 Ready
Back to Top