excelwpfradgridview

Telerik RadGridView.ExportToXlsx - format cells as number

I need the Excel export of a RadGridView to have a cell format of "Number" in Excel, with a format style of "{0:#,##0.00}".

I achieved that using .Export, handling the ElementExporting event:

grid.ElementExporting += Grid_ElementExporting;
grid.Export(stream, new GridViewExportOptions()
{
  Format = ExportFormat.ExcelML,
  ShowColumnHeaders = true,
  ShowColumnFooters = true
});

private void Grid_ElementExporting(object sender, GridViewElementExportingEventArgs e)
        {
            if (e.Element == ExportElement.Cell)
            {
                var column = e.Context as GridViewDataColumn;
                if (column?.DataType?.Name == "Decimal")
                {
                    e.Value = string.Format(@"{0:#,##0.00}", e.Value);
                }
            }
        }

However I receive an error on opening in Excel "The file format and extension of X don't match.", despite it definitely being of .xls extension. I can click past that and it loads correctly.

Reading up on it more, it sounds like I should update to use the .ExportToXlsx instead, and getting the files in .xlsx would be a perk anyway.

I change .Export to .ExportToXlxs, and the ElementExporting to ElementExportingToDocument, and the formatting is working, but all cells are back to being of format "General" in Excel, whereas I need them as "Number".

There's documentation on applying visual styles: https://docs.telerik.com/devtools/wpf/controls/radgridview/export/how-to/style-exported-documents

But not to change the underlying format that I can find.

Any suggestions?

Solution

  • You should use a CellValueFormat as explained in the official docs.

    The CellValueFormat class is defined in Telerik.Windows.Documents.Spreadsheet.dll so you need to reference this assembly