Existing GridView to Spreadsheet Workbook (for export)

15 posts, 1 answers
  1. Adrian
    Adrian avatar
    7 posts
    Member since:
    Jan 2013

    Posted 19 Mar 2013 Link to this post

    Is there an easy way to convert an existing RadGridView to a spreadsheet workbook so that we can use the XlsxFormatProvider to export the data to a valid Xlsx file?
  2. Answer
    Vlad
    Admin
    Vlad avatar
    11100 posts

    Posted 20 Mar 2013 Link to this post

    Hi,

     The easiest way currently to achieve your goal is to export the grid to CSV and read the this CSV using CsvFormatProvider. For example:

            private static Workbook CreateWorkbook(RadGridView grid)
            {
                Workbook book = null;

                using (var stream = new MemoryStream())
                {
                    grid.Export(stream, new GridViewExportOptions()
                    {
                        Format = ExportFormat.Csv,
                        ShowColumnFooters = grid.ShowColumnFooters,
                        ShowColumnHeaders = grid.ShowColumnHeaders,
                        ShowGroupFooters = grid.ShowGroupFooters
                    });

                    stream.Position = 0;

                    book = new CsvFormatProvider().Import(stream);
                }

                return book;
            }

    When you have the Workbook you can use XlsxFormatProvider to export it:

    ...
    var dialog = new SaveFileDialog();

    dialog.DefaultExt = "*.xlsx";
    dialog.Filter = "Excel Workbook (*.xlsx)|*.xlsx";

    if (dialog.ShowDialog() == true)
    {
    var workbook = CreateWorkBook(grid, settings);

                    if (workbook != null)
                    {
                                var provider = new XlsxFormatProvider();
                                using (var output = dialog.OpenFile())
                                {
                                    provider.Export(workbook, output);
                                }
                    }
    }
    ...

    Regards,
    Vlad
    the Telerik team

    Explore the entire Telerik portfolio by downloading Telerik DevCraft Ultimate.

  3. DevCraft banner
  4. Justin
    Justin avatar
    2 posts
    Member since:
    Feb 2013

    Posted 25 Apr 2013 Link to this post

    Hello,

    I am using pretty much this exact code and I'm getting a "The given key was not present in the dictionaty." exception.
    <
    var dialog = new SaveFileDialog()
                {
                    DefaultExt = "*.xlsx",
                    Filter = "Excel Workbook (*.xlsx)|*.xlsx|All files (*.*)|*.*",
                    
                };
     
                string filename = string.Empty;
     
                if (dialog.ShowDialog() == true)
                {
                    filename = dialog.SafeFileName;
     
                    Telerik.Windows.Documents.Spreadsheet.Model.Workbook book = null;
     
                    using (var stream = new System.IO.MemoryStream())
                    {
                        ExportGrid.Export(stream, new GridViewExportOptions()
                        {
                            Format = ExportFormat.Csv,
                            ShowColumnFooters = ExportGrid.ShowColumnFooters,
                            ShowColumnHeaders = ExportGrid.ShowColumnHeaders,
                            ShowGroupFooters = ExportGrid.ShowGroupFooters
                        });
     
                        stream.Position = 0;
     
                        book = new Telerik.Windows.Documents.Spreadsheet.FormatProviders.TextBased.Csv.CsvFormatProvider().Import(stream);
                    }
                    var provider = new XlsxFormatProvider();
                    using (var output = dialog.OpenFile())
                    {
                        provider.Export(book, output);
     
                    }
     
                }
    >

    Are there things the Workbook will not export? What is the best way to get around this problem.
  5. Andrew
    Admin
    Andrew avatar
    140 posts

    Posted 26 Apr 2013 Link to this post

    Hi Justin,

    Make sure you registered .XLSX format provider before using it. You can register it like this:

    WorkbookFormatProvidersManager.RegisterFormatProvider(new XlsxFormatProvider());

    If this does not fix the problem please check on which line of the code the exception is thrown and get back to us with the details so that we can assist you further.

    Kind regards,
    Andrew
    the Telerik team

    Explore the entire Telerik portfolio by downloading Telerik DevCraft Ultimate.

  6. Justin
    Justin avatar
    2 posts
    Member since:
    Feb 2013

    Posted 26 Apr 2013 Link to this post

    Hello,

    It still throws the exception on the line :

    <
    provider.Export(book, output);
    
    >

    I actually have a colored square as one of my grid columns in the the GridView I am trying to export. When I commented that out it exported fine.

    Now I must find another way to get that data into a form that can be exported. Im thinking just move it to another grid incode and make the data exportable? Maybe just Hex numbers?
  7. Andrew
    Admin
    Andrew avatar
    140 posts

    Posted 01 May 2013 Link to this post

    Hello Justin,

    We could not reproduce this strange behavior. Can you open a support ticket and send us a demo project illustrating the issue. This way we can assist you further.

    Greetings,
    Andrew
    the Telerik team

    Explore the entire Telerik portfolio by downloading Telerik DevCraft Ultimate.

  8. Michael Hilgers
    Michael Hilgers avatar
    76 posts
    Member since:
    Apr 2010

    Posted 30 Sep 2013 Link to this post

    Hello,

    has there been any progress with exporting a radGridView to a radSpreedsheat without using a CSV format? Because with CSV all optical grid informations like coloured group headers etc. are lost.

    Kind Regards,
    Michael
  9. Boryana
    Admin
    Boryana avatar
    330 posts

    Posted 03 Oct 2013 Link to this post

    Hello Michael,

    Thank you for writing.

    We are currently working on a demo that exports RadGridView to xlsx without using the csv format. I will let you know as soon it is ready.

    Do not hesitate to write back if you have further queries. I will do my best to assist you.

    Regards,
    Boryana
    Telerik
    TRY TELERIK'S NEWEST PRODUCT - EQATEC APPLICATION ANALYTICS for SILVERLIGHT.
    Learn what features your users use (or don't use) in your application. Know your audience. Target it better. Develop wisely.
    Sign up for Free application insights >>
  10. Michael Hilgers
    Michael Hilgers avatar
    76 posts
    Member since:
    Apr 2010

    Posted 04 Oct 2013 Link to this post

    That would be great. Thank you very much!

    Regards,
    Michael
  11. Boryana
    Admin
    Boryana avatar
    330 posts

    Posted 09 Oct 2013 Link to this post

    Hi Michael,

    Attached is a demo project that constructs a Workbook based on the data of RadGridView and exports it to an xlsx file. Please note that the demo cannot transfer automatically the appearance information (colors, gradients etc.) from RadGridView to the new Workbook, since the grid itself does not expose such information at run time. However, the project allows you to specify the IFill values of header, data and group header rows, so you will be able to apply formatting to your data manually.

    Additionally, the demo is for WPF, but the code-base of the RadControls for WPF and Silverlight suites is the same so little to no change should be required in order to use the code in a Silverlight project.

    I hope you will find it helpful. Let me know if you have further queries. I will do my best to assist you.

    Regards,
    Boryana
    Telerik
    TRY TELERIK'S NEWEST PRODUCT - EQATEC APPLICATION ANALYTICS for SILVERLIGHT.
    Learn what features your users use (or don't use) in your application. Know your audience. Target it better. Develop wisely.
    Sign up for Free application insights >>
  12. Dan Ove
    Dan Ove avatar
    9 posts
    Member since:
    Feb 2012

    Posted 09 Oct 2013 Link to this post

    Hi

    I also tested the new demo. It seems to be working properly, but in my RadGridView some columns contain strings that start with - or =.

     

    When I use the worksheet.Cells[currentRowIndex, cuurentColumnIndex].SetValue(value.ToString()) method in AddDataRow the setValue metod will treat the value as a RadExpression since the value starts with - or =. (Ref: http://www.telerik.com/help/wpf/radspreadsheet-model-working-with-cells-cell-value-types.html#Formula_Cell_Value )

     

    How can I escape the value so SetValue store the value as text in the cell?

    Best regards

    Dan Ove

  13. Anna
    Admin
    Anna avatar
    99 posts

    Posted 09 Oct 2013 Link to this post

    Hi,

    What you can do for such cells is to set their format to text. You need code similar to this:
    if (value.StartsWith("=") || value.StartsWith("-"))
    {
        worksheet.Cells[currentRowIndex, currentColumnIndex].SetFormat(new CellValueFormat("@"));
    }
     
    worksheet.Cells[currentRowIndex, currentColumnIndex].SetValue(value.ToString());

    I hope this helps. Let us know if you have any further difficulties.


    Regards,
    Anna
    Telerik
    TRY TELERIK'S NEWEST PRODUCT - EQATEC APPLICATION ANALYTICS for SILVERLIGHT.
    Learn what features your users use (or don't use) in your application. Know your audience. Target it better. Develop wisely.
    Sign up for Free application insights >>
  14. Dan Ove
    Dan Ove avatar
    9 posts
    Member since:
    Feb 2012

    Posted 10 Oct 2013 Link to this post

    WooHoo. It works like it should now.
    But I think you should add a built-in support to export a grid to xlsx and pdf. :)


    Thanks!
  15. Doug
    Doug avatar
    22 posts
    Member since:
    Feb 2005

    Posted 08 Nov 2013 Link to this post

    I second that request!  The current CSV, ExcelML and HTML options are not very good options for Excel as users typically want to edit the exported files and often lose those changes because they don't bother to resave the file as xlsx or xls first.  I have been using RadSpreadsheet to basically do the exports now but is a hassle when the data is already in a RadGridVew, filtered/sorted etc the way a user wants.
  16. Dimitrina
    Admin
    Dimitrina avatar
    3769 posts

    Posted 12 Nov 2013 Link to this post

    Hello,

    I am afraid that the capabilities of the built-in Export are not huge.
    Still, you have all the items (filtered and the way they have been sorted) and you can access them from RadGridView.Items collection. Then you can export them.

    Regards,
    Didie
    Telerik
    TRY TELERIK'S NEWEST PRODUCT - EQATEC APPLICATION ANALYTICS for SILVERLIGHT.
    Learn what features your users use (or don't use) in your application. Know your audience. Target it better. Develop wisely.
    Sign up for Free application insights >>
Back to Top
DevCraft banner