出口WPF的DataGrid到Excel [英] Export WPF DataGrid to Excel

问题描述

为什么我的价值观，当我我的DataGrid导出到Excel更改

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

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

string 351732051316944 becomes 3,51732E+14

和我的日期时间：
2014年1月1日02：09：29.942成为41641,09035

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

和我怎么让它停止

我用ExportToExcel类

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

推荐答案

如果您需要使用Excel的自动化是这样，那么你需要确保正确的格式应用于该单元格。所以，你设置的值后，还设置了格式。例如，日期可能是这样的：

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

这篇关于出口WPF的DataGrid到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！