将 WPF DataGrid 导出到 Excel

Export WPF DataGrid to Excel

提问by Simon

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

为什么将 DataGrid 导出到 Excel 时我的值会发生变化

string 351732051316944 becomes 3,51732E+14

字符串 351732051316944 变成 3,51732E+14

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

我的日期时间：2014-01-01 02:09:29.942 变成 41641,09035

and how do I make it stop?

以及如何让它停止？

The class I use to ExportToExcel

我用来 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

我不知道从哪里开始这个问题，但任何帮助表示赞赏

采纳答案by DavidG

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:

如果您必须像这样使用 Excel 自动化，那么您需要确保将正确的格式应用于单元格。因此，在设置值后，还要设置格式。例如，日期可能是这样的：

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";