Completed
Last Updated: 12 Feb 2018 14:57 by ADMIN
ADMIN
Hristo
Created on: 22 Jan 2018 14:00
Category: GridView
Type: Bug Report
1
FIX. RadGridView - DateTime values prior to 1/1/1900 should be exported to Excel as text value
Excel does not support dates prior to 1/1/1900, however,  we could export such dates as simple strings: https://support.office.com/en-us/article/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3

How to reproduce:
 public partial class Form1 : RadForm
 {
     public Form1()
     {
         InitializeComponent();

         this.radGridView1.DataSource = this.GetData();
         this.radGridView1.AutoSizeColumnsMode = Telerik.WinControls.UI.GridViewAutoSizeColumnsMode.Fill;
     }

     private object GetData()
     {
         DataTable dt = new DataTable();
         dt.Columns.Add("Id", typeof(int));
         dt.Columns.Add("Name", typeof(string));
         dt.Columns.Add("Bool", typeof(bool));
         dt.Columns.Add("Date", typeof(DateTime));

         DateTime date = new DateTime(1850, 1, 1);
         for (int i = 0; i < 100; i++)
         {
             dt.Rows.Add(i, "Name " + i, i % 2 == 0, date.AddYears(i));
         }

         return dt;
     }
     
     FieldInfo fi;
     private void radButton1_Click(object sender, EventArgs e)
     {
         //Old Export using the ExcelML format
         this.fi = typeof(Telerik.WinControls.UI.Export.ExcelML.CellElement).GetField("_dataElement", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic);
         ExportToExcelML exporter = new ExportToExcelML(this.radGridView1);
         string fileName = @"..\..\data.xls";
         exporter.RunExport(fileName);

         //New export utilizing the SpreadProcessing libraries
         GridViewSpreadExport spreadExporter = new GridViewSpreadExport(this.radGridView1);

         SpreadExportRenderer exportRenderer = new SpreadExportRenderer();
         spreadExporter.RunExport(@"..\..\data.xlsx", exportRenderer);
     }
 }


Workaround: handle the CellFormatting event
public partial class Form1 : RadForm
{
    public Form1()
    {
        InitializeComponent();

        this.radGridView1.DataSource = this.GetData();
        this.radGridView1.AutoSizeColumnsMode = Telerik.WinControls.UI.GridViewAutoSizeColumnsMode.Fill;
    }

    private object GetData()
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("Id", typeof(int));
        dt.Columns.Add("Name", typeof(string));
        dt.Columns.Add("Bool", typeof(bool));
        dt.Columns.Add("Date", typeof(DateTime));

        DateTime date = new DateTime(1850, 1, 1);
        for (int i = 0; i < 100; i++)
        {
            dt.Rows.Add(i, "Name " + i, i % 2 == 0, date.AddYears(i));
        }

        return dt;
    }
    
    FieldInfo fi;
    private void radButton1_Click(object sender, EventArgs e)
    {
        //Old Export using the ExcelML format
        this.fi = typeof(Telerik.WinControls.UI.Export.ExcelML.CellElement).GetField("_dataElement", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic);
        ExportToExcelML exporter = new ExportToExcelML(this.radGridView1);
        exporter.ExcelCellFormatting += Exporter_ExcelCellFormatting;
        string fileName = @"..\..\data.xls";
        exporter.RunExport(fileName);

        //New export utilizing the SpreadProcessing libraries
        GridViewSpreadExport spreadExporter = new GridViewSpreadExport(this.radGridView1);
         spreadExporter.CellFormatting += SpreadExporter_CellFormatting;

        SpreadExportRenderer exportRenderer = new SpreadExportRenderer();
        spreadExporter.RunExport(@"..\..\data.xlsx", exportRenderer);
    }

    private void SpreadExporter_CellFormatting(object sender, Telerik.WinControls.Export.CellFormattingEventArgs e)
    {
        if (e.GridRowIndex >= 1 && e.GridCellInfo.ColumnInfo is GridViewDateTimeColumn && ((DateTime)e.GridCellInfo.Value).Year < 1900)
        {
            Telerik.Windows.Documents.Spreadsheet.Model.CellSelection cell = e.CellSelection as Telerik.Windows.Documents.Spreadsheet.Model.CellSelection;
            cell.SetValue(e.GridCellInfo.Value.ToString());
        }
    }

    private void Exporter_ExcelCellFormatting(object sender, Telerik.WinControls.UI.Export.ExcelML.ExcelCellFormattingEventArgs e)
    {
        if (e.GridRowIndex > -1 && e.GridCellInfo.ColumnInfo is GridViewDateTimeColumn && ((DateTime)e.GridCellInfo.Value).Year < 1900)
        {
            DataElement data = new DataElement();
            data.DataItem = e.GridCellInfo.Value.ToString();
            this.fi.SetValue(e.ExcelCellElement, data);
        }
    }
}

(Total attached files size should be smaller than 20mb. Allowed extensions: .zip, .rar, .jpg, .png, .gif)
0 comments