使用 C# 中的 Open Xml SDK 将数据表导出到 Excel

Export DataTable to Excel with Open Xml SDK in c#

提问by user1576474

Sorry for my english please. I'm new in open xml sdk. My program have ability to export some data and DataTable to Excel file (template) In the template I insert the data to some placeholders. It's works very good, but I need to insert a DataTable too... My sample code:

请原谅我的英语。我是 open xml sdk 的新手。我的程序能够将一些数据和 DataTable 导出到 Excel 文件（模板）在模板中，我将数据插入到一些占位符中。它工作得很好，但我也需要插入一个数据表......我的示例代码：

using (Stream OutStream = new MemoryStream())
{
    // read teamplate
    using (var fileStream = File.OpenRead(templatePath))
        fileStream.CopyTo(OutStream);

    // exporting
    Exporting(OutStream);

    // to start
    OutStream.Seek(0L, SeekOrigin.Begin);

    // out
    using (var resultFile = File.Create(resultPath))
        OutStream.CopyTo(resultFile);

Next method to exporting

导出的下一个方法

private void Exporting(Stream template)
{
    using (var workbook = SpreadsheetDocument.Open(template, true, new OpenSettings                          { AutoSave = true }))
    {
        // Replace shared strings
        SharedStringTablePart sharedStringsPart = workbook.WorkbookPart.SharedStringTablePart;
        IEnumerable<Text> sharedStringTextElements = sharedStringsPart.SharedStringTable.Descendants<Text>();

        DoReplace(sharedStringTextElements);
        // Replace inline strings
        IEnumerable<WorksheetPart> worksheetParts = workbook.GetPartsOfType<WorksheetPart>();

        foreach (var worksheet in worksheetParts)
        {
            DoReplace(worksheet.Worksheet.Descendants<Text>());
        }

        int z = 40;
        foreach (System.Data.DataRow row in ExcelWorkXLSX.ToOut.Rows)
        {
            for (int i = 0; i < row.ItemArray.Count(); i++)
            { 
                ExcelWorkXLSX.InsertText(workbook, row.ItemArray.ElementAt(i).ToString(), getColumnName(i), Convert.ToUInt32(z)); }
                z++;
            }
        } 

    }
}

But this fragment to output DataTable slooooooooooooooooooooooowwwwwww...

但是这个片段要输出DataTable slooooooooooooooooooooooowwwwwww...

How can I export DataTable to Excel fast and truly?

如何快速而真实地将DataTable导出到Excel？

采纳答案by eburgos

I wrote this quick example. It works for me. I only tested it with one dataset with one table inside, but I guess that may be enough for you.

我写了这个简单的例子。这个对我有用。我只用一个里面有一张表的数据集测试了它，但我想这对你来说可能已经足够了。

Take into consideration that I treated all cells as String (not even SharedStrings). If you want to use SharedStrings you might need to tweak my sample a bit.

考虑到我将所有单元格视为字符串（甚至不是 SharedStrings）。如果您想使用 SharedStrings，您可能需要稍微调整一下我的示例。

Edit: To make this work it is necessary to add WindowsBase and DocumentFormat.OpenXml references to project.

编辑：要完成这项工作，必须向项目添加 WindowsBase 和 DocumentFormat.OpenXml 引用。

Enjoy,

享受，

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 KLIM8D

You could try taking a look at this libary. I've used it for one of my projects and found it very easy to work with, reliable and fast (I only used it for exporting data).

你可以试试看一下这个库。我已经将它用于我的一个项目，发现它非常易于使用、可靠且快速（我仅将它用于导出数据）。

http://epplus.codeplex.com/

http://epplus.codeplex.com/

回答by esp

eburgos, I've modified your code slightly because when you have multiple datatables in your dataset it was just overwriting them in the spreadsheet so you were only left with one sheet in the workbook. I basically just moved the part where the workbook is created out of the loop. Here is the updated code.

eburgos，我稍微修改了您的代码，因为当您的数据集中有多个数据表时，它只是在电子表格中覆盖它们，因此您在工作簿中只剩下一张工作表。我基本上只是将创建工作簿的部分移出循环。这是更新的代码。

private void ExportDSToExcel(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();

        uint sheetId = 1;

        foreach (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);

            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 (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 (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 jamdagni86

You can have a look at my library here. Under the documentation section, you will find how to import a data table.

你可以在这里看看我的图书馆。在文档部分，您将找到如何导入数据表。

You just have to write

你只需要写

using (var doc = new SpreadsheetDocument(@"C:\OpenXmlPackaging.xlsx")) {
    Worksheet sheet1 = doc.Worksheets.Add("My Sheet");
    sheet1.ImportDataTable(ds.Tables[0], "A1", true);
}

Hope it helps!

希望能帮助到你！

回答by Steve

I wrote my own export to Excel writer because nothing else quite met my needs. It is fast and allows for substantial formatting of the cells. You can review it at

我写了自己的导出到 Excel 编写器，因为没有其他东西能完全满足我的需求。它速度很快，并允许对单元格进行大量格式化。你可以在

https://openxmlexporttoexcel.codeplex.com/

https://openxmlexporttoexcel.codeplex.com/

I hope it helps.

我希望它有帮助。

回答by Mike Gledhill

I also wrote a C#/VB.Net "Export to Excel" library, which uses OpenXML and (more importantly) also uses OpenXmlWriter, so you won't run out of memory when writing large files.

我还编写了一个 C#/VB.Net“导出到 Excel”库，它使用 OpenXML 并且（更重要的是）还使用 OpenXmlWriter，因此在编写大文件时不会耗尽内存。

Full source code, and a demo, can be downloaded here:

完整的源代码和演示，可以在这里下载：

Export to Excel

导出到 Excel

It's dead easy to use. Just pass it the filename you want to write to, and a DataTable, DataSetor List<>.

它非常容易使用。只需将您要写入的文件名和一个DataTable,DataSet或List<>.

CreateExcelFile.CreateExcelDocument(myDataSet, "MyFilename.xlsx");

And if you're calling it from an ASP.Net application, pass it the HttpResponseto write the file out to.

如果您从 ASP.Net 应用程序调用它，请将它传递HttpResponse给写入文件。

CreateExcelFile.CreateExcelDocument(myDataSet, "MyFilename.xlsx", Response);

回答by Saranga

I tried accepted answer and got message saying generated excel file is corrupted when trying to open. I was able to fix it by doing few modifications like adding below line end of the code.

我尝试了接受的答案，并收到消息说尝试打开时生成的 excel 文件已损坏。我能够通过做一些修改来修复它，比如在代码的下面添加行尾。

workbookPart.Workbook.Save();

workbookPart.Workbook.Save();

I have posted full code @ Export DataTable to Excel with Open XML in c#

我已经发布了完整的代码@Export DataTable to Excel with Open XML in c#

回答by WATYF

I wanted to add this answer because I used the primary answer from this question as my basis for exporting from a datatable to Excel using OpenXML but then transitioned to OpenXMLWriter when I found it to be much faster than the above method.

我想添加这个答案，因为我使用了这个问题的主要答案作为我使用 OpenXML 从数据表导出到 Excel 的基础，但是当我发现它比上述方法快得多时，然后转换到 OpenXMLWriter。

You can find the full details in my answer in the link below. My code is in VB.NET though, so you'll have to convert it.

您可以在下面的链接中的我的回答中找到完整的详细信息。我的代码在 VB.NET 中，所以你必须转换它。

How to export DataTable to Excel

如何将数据表导出到 Excel