This is a migrated thread and some comments may be shown as answers.

Existing GridView to Spreadsheet Workbook (for export)

14 Answers 270 Views
Spreadsheet
This is a migrated thread and some comments may be shown as answers.
Adrian
Top achievements
Rank 1
Adrian asked on 19 Mar 2013, 03:45 PM
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?

14 Answers, 1 is accepted

Sort by
0
Accepted
Vlad
Telerik team
answered on 20 Mar 2013, 07:51 AM
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.

0
Justin
Top achievements
Rank 1
answered on 25 Apr 2013, 07:39 PM
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.
0
Andrew
Telerik team
answered on 26 Apr 2013, 09:04 AM
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.

0
Justin
Top achievements
Rank 1
answered on 26 Apr 2013, 01:55 PM
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?
0
Andrew
Telerik team
answered on 01 May 2013, 02:48 PM
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.

0
Michael Hilgers
Top achievements
Rank 1
answered on 30 Sep 2013, 06:39 AM
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
0
Boryana
Telerik team
answered on 03 Oct 2013, 08:14 AM
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 >>
0
Michael Hilgers
Top achievements
Rank 1
answered on 04 Oct 2013, 06:27 AM
That would be great. Thank you very much!

Regards,
Michael
0
Boryana
Telerik team
answered on 09 Oct 2013, 08:20 AM
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 >>
0
Dan Ove
Top achievements
Rank 1
answered on 09 Oct 2013, 10:38 AM

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

0
Anna
Telerik team
answered on 09 Oct 2013, 02:12 PM
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 >>
0
Dan Ove
Top achievements
Rank 1
answered on 10 Oct 2013, 07:03 AM
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!
0
Doug
Top achievements
Rank 1
Veteran
answered on 08 Nov 2013, 06:02 PM
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.
0
Dimitrina
Telerik team
answered on 12 Nov 2013, 08:22 AM
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 >>
Tags
Spreadsheet
Asked by
Adrian
Top achievements
Rank 1
Answers by
Vlad
Telerik team
Justin
Top achievements
Rank 1
Andrew
Telerik team
Michael Hilgers
Top achievements
Rank 1
Boryana
Telerik team
Dan Ove
Top achievements
Rank 1
Anna
Telerik team
Doug
Top achievements
Rank 1
Veteran
Dimitrina
Telerik team
Share this question
or