How to export the RadGridView with number columns?

[Yuxiao]

Hi, 

I tried to export the RadGridView to an Excel file, however, every column there seems to be string. How to export the RadGridView with number columns?

I tried to set the columns as double but not work,

    ((GridViewBoundColumnBase)grid.Columns[17]).DataType = typeof(double);

I also tried following and not work either,

    ((GridViewDataColumn)grid.Columns[17]).DataFormatString = "0.00";

The XML in the exported file is something like 

<Row>
    <Cell><Data ss:Type="String">Title</Data></Cell>
    <Cell><Data ss:Type="String">SE</Data></Cell>
    <Cell><Data ss:Type="String">Hour</Data></Cell>
</Row>

While the right format should be 

    <Cell><Data ss:Type="Number">Hour</Data></Cell>

To bind the fields one by one maybe an option, but I just want it to simple. 

Could anyone help? Thank you very much in advance!

Here are my code in the cs.

  private void Button_Export_Click_1(object sender, RoutedEventArgs e)
        {
            RadGridView grid = Grid_Records;
            ExportFormat format = ExportFormat.ExcelML;
            string extension = "xls";
            SaveFileDialog dialog = new SaveFileDialog();
            dialog.DefaultExt = extension;
            dialog.Filter = String.Format("{1} files (*.{0})|*.{0}|All files (*.*)|*.*", extension, "xls");
            dialog.FilterIndex = 1;

            if (dialog.ShowDialog() == true)
            {
                using (Stream stream = dialog.OpenFile())
                {
                    GridViewExportOptions exportOptions = new GridViewExportOptions();
                    exportOptions.Format = format;
                    exportOptions.ShowColumnFooters = true;
                    exportOptions.ShowColumnHeaders = true;
                    exportOptions.ShowGroupFooters = true;

                    ((GridViewBoundColumnBase)grid.Columns[17]).DataType = typeof(double);

                    grid.Export(stream, exportOptions);
                }
            }
        }

[Yuxiao]

By the way, in the xaml file, the code is simple,

<telerik:RadGridView x:Name="Grid_Records" AutoGenerateColumns="False" ShowInsertRow="False" RowIndicatorVisibility="Collapsed" ShowGroupPanel="False" IsReadOnly="True" Height="800" Width="1200" Margin="0,20,0,0" HorizontalAlignment="Left" SelectionMode="Extended" ClipboardCopyMode="All" >
</telerik:RadGridView>

[Dimitrina]

Hi,

You can subscribe for the ElementExporting event of RadGridView and parse the value to be exported to the desired format and set it as e.Value. You can also check our online documentation on Exporting.

Regards,
Dimitrina
Telerik

 

 

[Yuxiao]

Got it, thank you very much for the quick help !

I know how to do it now. And I am real sorry to post stupid questions like this, just don't have much time to search.

But here is one more, is the comments for the header cell needs to be done in the same way (using elementexporting)?

I added the column head in the following way, and would like to a have comment in the head cell 'stay'

 GridViewDataColumn stayColumn = new GridViewDataColumn()
                                                {
                                                    UniqueName = "stay",
                                                    Header = "stay",
                                                };
            Grid_Records.Columns.Add(stayColumn);

[Dimitrina]

Hi,

The Header ("stay") should be exported without any additional code, is it not the case?
You can also check the Export Custom Header troubleshooting article in our online documentation.

Regards,
Dimitrina
Telerik

 

 

[Yuxiao]

Yes, without any other codes. Thanks.

[Dimitrina]

Hi,

I am bit confused whether you have a problem in the default case or not. If the header is not exported (which should be working by default), then may I ask you to open a new support thread and send us a demo project illustrating the problematic behavior?

Regards,
Dimitrina
Telerik

 

 

[Yuxiao]

The header is exported without any problem. I just would like to have a comment in the header cell.

[Dimitrina]

Hello,

Basically, the way to modify the values to be exported is subscribing for the ElementExporting event of RadGridView and setting the value to be exported (e.Value) as you wish.
For example:

private void clubsGrid_ElementExporting(object sender, GridViewElementExportingEventArgs e)
{
    if (e.Element == ExportElement.HeaderCell)
    {
        var column = e.Context as GridViewDataColumn;
        if (column != null)
        {
            e.Value = "any value you wish to export";
        }
    }
}

If you would like to export an additional row, then you can subscribe for ElementExported event. This is also illustrated on our online demo on Exporting RowDetails

Regards,
Dimitrina
Telerik

 

 

[Yuxiao]

I have no problem with the value itself, I have the following problems,

1. How to format the value into a percentage format? Have a % in the cell for a numeric value.
2. Would like to add a comment to the head row to put some comments.

Thanks in advance!

  private void Grid_Records_ElementExporting(object sender, GridViewElementExportingEventArgs e)
        {
            if (e.Element == ExportElement.Cell)
            {
                if ((e.Context as GridViewDataColumn).Header.ToString().Contains("Avg over stay"))
                {
                    decimal oUsage;
                    try
                    {
                        var remove = decimal.TryParse(e.Value.ToString(), out oUsage);
                        if (remove)
                        {
                                    e.Value = oUsage;
                        }
                    }
                    catch(Exception exporterror)
                    {
                    }
                }}}

[Dimitrina]

Hello,

In order to format the values, you can do so on column level setting DataFormatString of your column.
For example:

<telerik:GridViewDataColumn DataMemberBinding="{Binding StadiumCapacity}" DataFormatString="{}{0:#\%}"/>

The formatted value will be exported to Excel.

As to your second question, RadGridView's export does not suggest a way of specifying custom comments to the exported data. The way to modify the values to be exported is subscribing for the ElementExporting and ElementExported events of RadGridView.

Regards,
Dimitrina
Telerik

 

 

[Kamran]

Hi,

 

i know this thread is kinda old, but I found it and it fits my request quite well.

I got a RadTreeListView and would like to export the content to excel. I found the documentation but I am not sure what to put instead of "gridViewExport" in the example

https://docs.telerik.com/devtools/wpf/controls/radgridview/export/export

 

I didn't write any of the code, neither have I worked with Telerik. I just want to export the table as I see it to Excel

 

Thanks in advance

[Vladimir Stoyanov]

Hello Kamran,

The "gridViewExport" in that case is value of the Name property of the RadGridView. So if you want to use the same example, you need to set the Name of the RadTreeListView in Xaml like so:

<telerik:RadTreeListView x:Name="gridViewExport" />

I hope this helps. Let me know if I can be of any further assistance.

Regards,
Vladimir Stoyanov
Progress Telerik