Export ASPxGridView to xlsx using ASPxGridViewExporter with formatted text

ChocapicSz picture ChocapicSz · Jan 13, 2015 · Viewed 8.8k times · Source

I have an ASPxGridView having a number of columns (of type GridViewDataComboBoxColumn or GridViewDataHyperLinkColumn). In the code behind in the grid's CustomColumnDisplayText event I format the text of the cells in the following way:

protected void MyGrid_CustomColumnDisplayText(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewColumnDisplayTextEventArgs e)
{
    if (e.Column is DevExpress.Web.ASPxGridView.GridViewDataColumn)
    {
        switch (e.Column.FieldName)
        {
            case "Energy":
                if (e.Value is float)
                {
                    // the FormatEnergy method here returns for example "323.32 kWh"
                    e.DisplayText = DataDisplayTools.Instance.FormatEnergy((float)e.Value);
                }
                break;

                // other cases

        }
    }
}

For exporting the grid view in .xlsx format I use an ASPxGridViewExporter:

protected void lnkXlsxExport_Click(object sender, EventArgs e)
{
    try
    {
        gridViewExporter.DataBind();
        gridViewExporter.WriteXlsxToResponse();
    }
    catch (Exception e1)
    {
        //
    }
}

My problem is that when I export the grid view to .xlsx, the values of the columns in the exported file are not formatted as they are in the grid view. For example the values in the grid view (they are formatted in the CustomColumnDisplayText event) are

  • 32,332 kWh
  • 311 kWh
  • 2,254 kWh

but these values in the xlsx file after exporting appear like

  • 32332
  • 311
  • 2254

enter image description here

My question is: Can export an ASPxGridView to an excel file having exactly the same texts in the excel file's cells as the grid view has, using an ASPxGridViewExporter?

Answer

Gosha_Fighten picture Gosha_Fighten · Jan 13, 2015

The ASPxGridViewExporter exports the grid content by values, not display text. Create an instance of the XlsxExportOptions class, set its TextExportMode property to Text, and use this instance during exporting. Here is a corresponding ticket on DevExpress Support Center: ASPxGridView - The CustomColumnDisplayText event doesn't not work when exporting to Excel after upgrading from v.9.2 to v.13.2.

You may also face a problem described in the ASPxGridView / MVC GridView Extension - Excel Data Aware Export FAQ ticket. This ticket provides a solution.