wpf 将 DataGrid 导出为 CSV 或 Excel

Export DataGrid into CSV or Excel

提问by Sakul

How can i export my DataGrid information into an .csv or excel file? I'm using Microsoft Visual Studio 2010 with .NET Framework 4.0.

如何将我的 DataGrid 信息导出到 .csv 或 excel 文件中？我将 Microsoft Visual Studio 2010 与 .NET Framework 4.0 一起使用。

回答by JSJ

try below sample

尝试以下示例

private void btnexport_Click(object sender, RoutedEventArgs e)
{
    ExportToExcel<Employee, Employees> s = new ExportToExcel<Employee, Employees>();
    s.dataToPrint = (Employees)dgEmployee.ItemsSource;
    s.GenerateReport();
}






/// <summary>
/// Class for generator of Excel file
/// </summary>
/// <typeparam name="T"></typeparam>
/// <typeparam name="U"></typeparam>
public class ExportToExcel<T, U>
    where T : class
    where U : List<T>
{
    public List<T> dataToPrint;
    // Excel object references.
    private Excel.Application _excelApp = null;
    private Excel.Workbooks _books = null;
    private Excel._Workbook _book = null;
    private Excel.Sheets _sheets = null;
    private Excel._Worksheet _sheet = null;
    private Excel.Range _range = null;
    private Excel.Font _font = null;
    // Optional argument variable
    private object _optionalValue = Missing.Value;

    /// <summary>
    /// Generate report and sub functions
    /// </summary>
    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);
        }
    }
    /// <summary>
    /// Make Microsoft Excel application visible
    /// </summary>
    private void OpenReport()
    {
        _excelApp.Visible = true;
    }
    /// <summary>
    /// Populate the Excel sheet
    /// </summary>
    private void FillSheet()
    {
        object[] header = CreateHeader();
        WriteData(header);
    }
    /// <summary>
    /// Write data into the Excel sheet
    /// </summary>
    /// <param name="header"></param>
    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);
    }
    /// <summary>
    /// Method to make columns auto fit according to data
    /// </summary>
    /// <param name="startRange"></param>
    /// <param name="rowCount"></param>
    /// <param name="colCount"></param>
    private void AutoFitColumns(string startRange, int rowCount, int colCount)
    {
        _range = _sheet.get_Range(startRange, _optionalValue);
        _range = _range.get_Resize(rowCount, colCount);
        _range.Columns.AutoFit();
    }
    /// <summary>
    /// Create header from the properties
    /// </summary>
    /// <returns></returns>
    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;
    }
    /// <summary>
    /// Set Header style as bold
    /// </summary>
    private void SetHeaderStyle()
    {
        _font = _range.Font;
        _font.Bold = true;
    }
    /// <summary>
    /// Method to add an excel rows
    /// </summary>
    /// <param name="startRange"></param>
    /// <param name="rowCount"></param>
    /// <param name="colCount"></param>
    /// <param name="values"></param>
    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);
    }       
    /// <summary>
    /// Create Excel application parameters instances
    /// </summary>
    private void CreateExcelRef()
    {
        _excelApp = new Excel.Application();
        _books = (Excel.Workbooks)_excelApp.Workbooks;
        _book = (Excel._Workbook)(_books.Add(_optionalValue));
        _sheets = (Excel.Sheets)_book.Worksheets;
        _sheet = (Excel._Worksheet)(_sheets.get_Item(1));
    }
    /// <summary>
    /// Release unused COM objects
    /// </summary>
    /// <param name="obj"></param>
    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();
        }
    }
}

回答by Debajit Mukhopadhyay

You can use the following code which converts datatable to csv. The code is very flexible like you can change the column name and can select number of columns.

您可以使用以下代码将数据表转换为 csv。代码非常灵活，就像您可以更改列名称和选择列数一样。

To export to csv you need to call this

要导出到 csv，您需要调用它

ExportFile.ExportCSV(dt, "id,name", "Product ID,Name","order.csv");

here is the code below:

这是下面的代码：

public class ExporFile
{
    /// <summary>
    /// Export to CSV
    /// </summary>
    /// <param name="exportTable">Export table</param>
    /// <param name="showColumns">Columns needs to show in CSV</param>
    /// <param name="changedColumnName">Changed Column Names in CSV</param>
    /// <param name="fileName">File Name</param>
    public static void ExportCSV(DataTable exportTable, string showColumns, string changedColumnName, string fileName)
    {
        DataTable filterTable = FilterColumn(exportTable, showColumns, changedColumnName);
        string dataCSV = DataTable2CSV(filterTable, "\t", "\"");
        dataCSV = System.Web.HttpContext.Current.Server.HtmlDecode(dataCSV);
        System.Web.HttpContext.Current.Response.Charset = "";
        System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Unicode;
        System.Web.HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" + fileName);
        System.Web.HttpContext.Current.Response.Write(dataCSV);
        System.Web.HttpContext.Current.Response.Flush();
        try
        {
            System.Web.HttpContext.Current.Response.End();
        }
        catch { };
    }

    /// <summary>
    /// Filter Columns
    /// </summary>
    /// <param name="exportTable"></param>
    /// <param name="showColumns"></param>
    /// <param name="changedColumnName"></param>
    /// <returns></returns>
    private static DataTable FilterColumn(DataTable exportTable, string showColumns, string changedColumnName)
    {
        DataView filterDataView = exportTable.DefaultView;
        //filterDataView.Sort = "AutoID";
        DataTable filterTable = filterDataView.ToTable(false, showColumns.Split(','));
        return ChangedExportDataColumnName(filterTable, changedColumnName);
    }

    /// <summary>
    /// Changed Column Datatable
    /// </summary>
    /// <param name="filterTable"></param>
    /// <param name="changedColumnName"></param>
    /// <returns></returns>
    private static DataTable ChangedExportDataColumnName(DataTable filterTable, string changedColumnName)
    {
        string[] changedNames = changedColumnName.Split(',');

        for (int i = 0; i < changedNames.Length; i++)
        {
            if (!String.IsNullOrEmpty(changedNames[i]))
            {
                filterTable.Columns[i].ColumnName = changedNames[i];
            }
        }
        return filterTable;
    }

    /// <summary>
    /// Returns a CSV string corresponding to a datatable. However the separator can be defined and hence it can be any string separated value and not only csv.
    /// </summary>
    /// <param name="table">The Datatable</param>
    /// <param name="separator">The value separator</param>
    /// <param name="circumfix">The circumfix to be used to enclose values</param>
    /// <returns></returns>
    private static String DataTable2CSV(DataTable table, string separator, string circumfix)
    {

        StringBuilder builder = new StringBuilder(Convert.ToString((char)65279));
        foreach (DataColumn col in table.Columns)
        {
            builder.Append(col.ColumnName).Append(separator);
        }
        builder.Remove((builder.Length - separator.Length), separator.Length);
        builder.Append(Environment.NewLine);

        foreach (DataRow row in table.Rows)
        {
            foreach (DataColumn col in table.Columns)
            {
                builder.Append(circumfix).Append(row[col.ColumnName].ToString().Replace("\"", "\"\"")).Append(circumfix).Append(separator);
            }
            builder.Remove((builder.Length - separator.Length), separator.Length);
            builder.Append(Environment.NewLine);
        }
        return builder.ToString();
    }
}

回答by NDraskovic

I think that the best and fastest option is to create a report and use reportViewer control to do the exporting for you (it has the option of exporting data into Excel, PDF and Word documents)

我认为最好和最快的选择是创建一个报告并使用reportViewer控件为您进行导出（它具有将数据导出为Excel、PDF和Word文档的选项）