Export WPF DataGrid to Excel

Question

Why does my values change when I export my DataGrid to Excel

string 351732051316944 becomes 3,51732E+14

and my datetimes: 2014-01-01 02:09:29.942 becomes 41641,09035

and how do I make it stop?

The class I use to ExportToExcel

public class ExportToExcel<T>
{
    public List<T> dataToPrint;
    // Excel object references.
    private Microsoft.Office.Interop.Excel.Application excelApp = null;
    private Workbooks books = null;
    private Workbook book = null;
    private Sheets sheets = null;
    private Worksheet sheet = null;
    private Range range = null;
    private Font font = null;
    // Optional argument variable
    private object optionalValue = Missing.Value;


    /// Generate report and sub functions
    public void GenerateReport()
    {
        try
        {
            if (dataToPrint != null)
            {
                if (dataToPrint.Count != 0)
                {
                    Mouse.SetCursor(Cursors.Wait);
                    CreateExcelRef();
                    FillSheet();
                    OpenReport();
                    Mouse.SetCursor(Cursors.Arrow);
                }
            }
        }
        catch (Exception e)
        {
            MessageBox.Show("Error while generating Excel report");
        }
        finally
        {
            ReleaseObject(sheet);
            ReleaseObject(sheets);
            ReleaseObject(book);
            ReleaseObject(books);
            ReleaseObject(excelApp);
        }
    }
    /// Make Microsoft Excel application visible
    private void OpenReport()
    {
        excelApp.Visible = true;
    }
    /// Populate the Excel sheet
    private void FillSheet()
    {
        object[] header = CreateHeader();
        WriteData(header);
    }
    /// Write data into the Excel sheet
    private void WriteData(object[] header)
    {
        object[,] objData = new object[dataToPrint.Count, header.Length];

        for (int j = 0; j < dataToPrint.Count; j++)
        {
            var item = dataToPrint[j];
            for (int i = 0; i < header.Length; i++)
            {
                var y = typeof(T).InvokeMember
        (header[i].ToString(), BindingFlags.GetProperty, null, item, null);
                objData[j, i] = (y == null) ? "" : y.ToString();
            }
        }
        AddExcelRows("A2", dataToPrint.Count, header.Length, objData);
        AutoFitColumns("A1", dataToPrint.Count + 1, header.Length);
    }
    /// Method to make columns auto fit according to data
    private void AutoFitColumns(string startRange, int rowCount, int colCount)
    {
        range = sheet.get_Range(startRange, optionalValue);
        range = range.get_Resize(rowCount, colCount);
        range.Columns.AutoFit();
    }
    /// Create header from the properties
    private object[] CreateHeader()
    {
        PropertyInfo[] headerInfo = typeof(T).GetProperties();

        // Create an array for the headers and add it to the
        // worksheet starting at cell A1.
        List<object> objHeaders = new List<object>();
        for (int n = 0; n < headerInfo.Length; n++)
        {
            objHeaders.Add(headerInfo[n].Name);
        }

        var headerToAdd = objHeaders.ToArray();
        AddExcelRows("A1", 1, headerToAdd.Length, headerToAdd);
        SetHeaderStyle();

        return headerToAdd;
    }
    /// Set Header style as bold
    private void SetHeaderStyle()
    {
        font = range.Font;
        font.Bold = true;
    }
    /// Method to add an excel rows
    private void AddExcelRows(string startRange, int rowCount, int colCount, object values)
    {
        range = sheet.get_Range(startRange, optionalValue);
        range = range.get_Resize(rowCount, colCount);
        range.set_Value(optionalValue, values);
    }

    /// Create Excel application parameters instances
    private void CreateExcelRef()
    {
        excelApp = new Microsoft.Office.Interop.Excel.Application();
        books = (Workbooks)excelApp.Workbooks;
        book = (Workbook)(books.Add(optionalValue));
        sheets = (Sheets)book.Worksheets;
        sheet = (Worksheet)(sheets.get_Item(1));
    }

    /// Release unused COM objects
    private void ReleaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
            MessageBox.Show(ex.Message.ToString());
        }
        finally
        {
            GC.Collect();
        }
    }
}

I have no idea where to begin with this problem but any help is appreciated

Answer 1

If you have to use Excel automation like this then you need to ensure the correct formatting is applied to the cells. So, after you set the value, also set the format. For example, a date could be this:

range.set_Value(optionalValue, values);
range.NumberFormat = "dd-mmm-yyyy";

And to prevent numbers showing using the format you mention:

range.set_Value(optionalValue, values);
range.NumberFormat = "0";