Existing GridView to Spreadsheet Workbook (for export)

[Adrian]

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?

[Vlad]

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

[Justin]

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.

[Andrew]

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

[Justin]

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?

[Andrew]

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

[Michael Hilgers]

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

[Boryana]

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

[Michael Hilgers]

That would be great. Thank you very much!

Regards,
Michael

[Boryana]

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

[Dan Ove]

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

[Anna]

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

[Dan Ove]

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!

[Doug]

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.

[Dimitrina]

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