logo标签列表

将DataGrid导出为CSV或Excel文件

c#wpfexcelcsvdatagrid
3
我该如何将DataGrid信息导出为.csv或Excel文件？ 我使用的是带有.NET Framework 4.0的Microsoft Visual Studio 2010。
3个回答
6

尝试下面的示例

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();
        }
    }
}
我用我的类Mail替换了Employee，用List<Mail>替换了Employees，这样对吗？ 我收到了12个错误，错误信息是： 找不到类型或命名空间名称“Excel”（是否缺少using指令或程序集引用？） - Sakul
你的项目中是否已经包含了Excel DLL？如果是的话，请在你的命名空间中加入 using Excel = Microsoft.Interop.Office.Excel;。 - JSJ
在解决方案资源管理器中右键单击“引用”->添加引用->Microsoft.Office.Interop.Excel，然后智能感知建议我将Excel替换为Microsoft.Office.Interop.Excel。 现在没有错误了...我现在会尝试它 编辑：完美运行！非常感谢！！！ - Sakul
1
你可以使用以下代码将datatable转换为csv。该代码非常灵活，您可以更改列名并选择列数。
要导出到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();
    }
}
0

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

听起来很简单，但我不知道报告是如何工作的。目前看起来有点混乱。 - Sakul
它们非常简单，因为您有一个向导，使您能够创建报告的视觉外观。然后，您只需将 reportViewer 添加到应用程序并将报告分配给它即可。这是一个可能会对您有所帮助的链接 http://msdn.microsoft.com/en-us/library/ms252067(v=vs.80).aspx - NDraskovic
谢谢你的回答，但 Jodha 的解决方案更容易理解。 - Sakul
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接