Exporting to Excel problems

[Martin Roussel]

Hi everyone,

Im facing consistent problems when exporting a GridView to Excel (im using MS Excel 2003 and Telerik version 2010.3.1314.1040). Ive also tested two exporting methods for each problems which ill refer below:

Method1:

Dim dlgSave As New SaveFileDialog()

dlgSave.Filter = "Excel Files (*.xls)|*.xls"
dlgSave.DefaultExt = "xls"

If dlgSave.ShowDialog() = True Then

    AddHandler grdData.Exporting, AddressOf grd_Exporting

    Dim Content As String
    Content = grdData.ToHtml(True)

    RemoveHandler grdData.Exporting, AddressOf grd_Exporting

    Using stream As Stream = dlgSave.OpenFile()
        Dim bytes As [Byte]() = Encoding.UTF8.GetBytes(Content)
        stream.Write(bytes, 0, bytes.Length)
        stream.Close()
    End Using
End If

Method2:

Dim extension As String = ""
Dim format As ExportFormat = ExportFormat.Html

extension = "xls"
format = ExportFormat.Html

Dim dialog As New SaveFileDialog()
dialog.DefaultExt = extension
dialog.Filter = [String].Format("{1} files (*.{0})|*.{0}|All files (*.*)|*.*", extension, "Excel")
dialog.FilterIndex = 1

If dialog.ShowDialog() = True Then
    Using stream As Stream = dialog.OpenFile()
        Dim exportOptions As New GridViewExportOptions()
        exportOptions.Format = format
        exportOptions.ShowColumnFooters = True
        exportOptions.ShowColumnHeaders = True
        exportOptions.ShowGroupFooters = True

        grdData.Export(stream, exportOptions)
    End Using
End If

1-Aggregate/Grouping:

When im using header grouping and aggregate functions to summarized my grid. I would like to have that summarized view in my Excel. The problem is I have too much data displayed (the reason im using groupings) and I end up having all the grid rows exported...which I dont want. The worse are the aggregate totals for each group (sum, avg etc.) that are not exported. The ideal would be to export only the data that are visible....(not those folded and hidden by the groupings). With Method1, I get all the rows with no aggregate at all. With Method2, I get the same + the aggregate footer of the grid, but I also get weird code such as  "[QCVG: Key=CM550FB240; ItemCount=2; HasSubgroups=False; ParentGroup=[QCVG: Key=Root; ItemCount=2; HasSubgroups=True; ParentGroup=null];];" in all columns of the last row.

2-Localizations

When im using French Canadian localization ("fr-CA"), im always getting weird characters for "special" letters (like "é", etc.) For instance the word "Équipement" becomes "Équipement". I also noted that when using {0:N4} as DataFormatString for particular numeric type columns (ex: 7,68), the export gives 76,80. Im forced to use default DataFormatString which use "." instead of "," but I lose the 4 digits limit.

Ive also attached a screenshot of the gridview (grouped and expanded) + exported .xls for each method (the first one is using french localization to demonstrate problem #2)


Can you help please?

Thanks in advance

[Dimitrina]

Hello Martin Roussel,

Regarding your questions:

1. As your requirement is very custom, you will need to do a kind of manual exporting by creating the exported file. I have attached a sample project that shows how you can create a spreadsheet and fill it with your grouped data. You can see how the summary exporting is implemented . 

2. I have tested your issue and I have found out that when the export format is Html, then the data is exported right, but it is visualized wrong when it is opened in Excel.
May you please export your GridView with ExportFormat.ExcelML? With this format the symbols shown in the excel file should be fine.

Kind regards,
Didie
the Telerik team

[Helmut]

Hello,

This example works fine when i group with one column.
(285752_266984-RadGridView-SL4-AR-10.zip)

Is it also possible to have this work with more then one column?

Thanks a lot,

   Helmut

[Dimitrina]

Hi Helmut,

When you group on more than one column, then the 'group' (one of the playersGrid.Items.Groups) will have a Subgroups in group.Subgroups.

Then you could use the Subgroups data and export it as you would like to. 

foreach (QueryableCollectionViewGroup group in this.playersGrid.Items.Groups)
{                      ...

foreach (IGroup subGroup in group.Subgroups)

{

QueryableCollectionViewGroup subGroup1 = (subGroup as QueryableCollectionViewGroup);

                             

for (int index = 0; index < subGroup1.AggregateResults.Count; index++)

{

if (subGroup1.AggregateResults[index].FormattedValue != null)

{

                // generate how the data to be exported

}

}
...

}

}

Greetings,
Didie
the Telerik team