wpf 将数据表导出到 Excel 文件 (.xlsx) 的最有效方法

Most efficient way to export DataTable to Excel File (.xlsx)

提问by Jose M Martin

I wanted to know what is the most efficient way to export a DataTable or DataSet to an .xlsx file in terms of speed.

我想知道在速度方面将 DataTable 或 DataSet 导出到 .xlsx 文件的最有效方法是什么。

I have tables of 200K rows and looping is useless, so I want to make like a bulk export or something like that.

我有 200K 行的表，循环没用，所以我想做批量导出或类似的东西。

Anything easy to implement answer my question?

有什么容易实现的回答我的问题吗？

SOLUTION:I finally used OpenXml by this way, if someone needs it. It exports 100k in about 1 minute:

解决方案：如果有人需要的话，我终于通过这种方式使用了 OpenXml。它在大约 1 分钟内导出 100k：

    private void ExportDataSet(DataSet ds, string destination)
    {
        using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
        {
            var workbookPart = workbook.AddWorkbookPart();

            workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();

            workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();

            foreach (System.Data.DataTable table in ds.Tables)
            {

                var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
                var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
                sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);

                DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
                string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);

                uint sheetId = 1;
                if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
                {
                    sheetId =
                        sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                }

                DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName };
                sheets.Append(sheet);

                DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();

                List<String> columns = new List<string>();
                foreach (System.Data.DataColumn column in table.Columns)
                {
                    columns.Add(column.ColumnName);

                    DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                    cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                    cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
                    headerRow.AppendChild(cell);
                }


                sheetData.AppendChild(headerRow);

                foreach (System.Data.DataRow dsrow in table.Rows)
                {
                    DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
                    foreach (String col in columns)
                    {
                        DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                        cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                        cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
                        newRow.AppendChild(cell);
                    }

                    sheetData.AppendChild(newRow);
                }

            }
        }
    }

回答by ASH

You must be looping through the records, and that's why it's so slow.

你一定是在循环记录，这就是为什么它这么慢。

Try something like this.

尝试这样的事情。

var lines = new List<string>();

string[] columnNames = dataTable.Columns.Cast<DataColumn>().
                                  Select(column => column.ColumnName).
                                  ToArray();

var header = string.Join(",", columnNames);
lines.Add(header);

var valueLines = dataTable.AsEnumerable()
                   .Select(row => string.Join(",", row.ItemArray));            
lines.AddRange(valueLines);

File.WriteAllLines("excel.csv",lines);

Or, turn the DataTable into an Excel file.

或者，将 DataTable 转换为 Excel 文件。

XLWorkbook wb = new XLWorkbook();
DataTable dt = GetDataTableOrWhatever();
wb.Worksheets.Add(dt,"WorksheetName");

回答by ECourant

Personally I prefer Syncfusion's Excel library just because it's pretty intuitive, well documented and free with the community license.

就我个人而言，我更喜欢 Syncfusion 的 Excel 库，因为它非常直观、有据可查并且免费获得社区许可。

You can download it here: XlsIO Product Page

您可以在这里下载：XlsIO 产品页面

And the documentation can be found here: XlsIO Documentation

文档可以在这里找到：XlsIO 文档

It's free to use if you claim the free community license.

如果您申请免费社区许可证，则可以免费使用。

Here is a code sample for writing a DataTable to an xlsx file. I don't think the size of your table should be a problem at all but I'm not 100% sure, the largest files I've written with this library have about 90k rows with 60 columns and I did not have any issues.

这是将 DataTable 写入 xlsx 文件的代码示例。我不认为你的表的大小应该是一个问题，但我不是 100% 肯定，我用这个库编写的最大文件有大约 90k 行和 60 列，我没有任何问题。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Syncfusion.XlsIO;
namespace ConsoleApp3
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable Table = new DataTable();
            Table.Columns.Add("Column1");
            Table.Columns.Add("Column2");
            Table.Columns.Add("Column3");
            Table.Rows.Add("Item1", "Item2", "Item3");

            ExcelEngine ExcelEngineObject = new Syncfusion.XlsIO.ExcelEngine();
            IApplication Application = ExcelEngineObject.Excel;
            Application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook Workbook = Application.Workbooks.Create(1);
            IWorksheet Worksheet = Workbook.Worksheets[0];
            Worksheet.ImportDataTable(Table, true, 1, 1);
            Workbook.SaveAs("YourExcelFile.xlsx");
            Workbook.Close();
            ExcelEngineObject.Dispose();

        }
    }
}