Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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:

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)); }
But this fragment to output DataTable slooooooooooooooooooooooowwwwwww...
How can I export DataTable to Excel fast and truly?
                Hmm... No, but open xml sdk fast read/write excel files. In my program i reading xlsx files, grabbing data to datagridview (using DataTable), recheck data. Firstly i used interop, but it need excel and very slow. My problem is only export. But, I would't want to rewrite a lot of code at this moment :)
– user1576474
                Aug 4, 2012 at 21:37
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.
Edit: To make this work it is necessary to add WindowsBase and DocumentFormat.OpenXml references to project.
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);
                I think workbook.WorkbookPart.Workbook = new... and workbook.WorkbookPart.Workbook.Sheets = new should be moved outside the foreach loop.  Otherwise, each iteration of the loop replaces the worksheets, causing the excel file to contain only the final DataTable.
– Brian
                Mar 13, 2013 at 18:16
                @Brian , thanks for pointing that out. It originally worked because I only tested with one table. I just fixed it, does it look ok now?
– eburgos
                Mar 13, 2013 at 21:05
                Yeah, it looks fine.  As an aside that doesn't matter much, calling .Max(s=>s.SheetId.Value) is cleaner than calling .Select(s=>s.SheetId.Value).Max() . Similarly, you don't need a List<String> columns since DataRow has a DataColumn indexer; the second foreach can iterate over table.Columns as well.
– Brian
                Mar 13, 2013 at 21:25
                I figured it out myself. To make this work it is necessary to add WindowsBase and DocumentFormat.OpenXml references to project. Also SpreadsheetDocument and WorksheetPart are in DocumentFormat.OpenXml.Packaging namespace
– AaA
                Dec 24, 2013 at 3:12
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.
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);
                could u include a version which return bytes and also a version whch takes just a datatable and not a dataset
– Seabizkit
                Aug 25, 2022 at 17:58
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.
Full source code, and a demo, can be downloaded here:
Export to Excel
It's dead easy to use.
Just pass it the filename you want to write to, and a DataTable, DataSet or List<>.
CreateExcelFile.CreateExcelDocument(myDataSet, "MyFilename.xlsx");
And if you're calling it from an ASP.Net application, pass it the HttpResponse to write the file out to.
CreateExcelFile.CreateExcelDocument(myDataSet, "MyFilename.xlsx", Response);
                @MikeGledhill any workaround doing it asynchronously? I get out of memory exception when trying to write to a huge file, I've read all the related posts pretty much seems like it is limited to available memory?
– afr0
                Jan 10, 2018 at 1:24
                @Afr0: This shouldn't be the case.  My libraries use OpenXmlWriter which write out the data as it's going along, rather than attempting to build up the entire Excel file in memory first.  If possible, drop me an email via my website, and I'll see if I can help.
– Mike Gledhill
                Jan 12, 2018 at 16:06
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
https://openxmlexporttoexcel.codeplex.com/
I hope it helps.
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/
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!
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.
workbookPart.Workbook.Save();
I have posted full code @ Export DataTable to Excel with Open XML in c#
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.
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.
How to export DataTable to Excel
				Export to Excel using OpenXML - Exception of type 'System.OutOfMemoryException' was thrown
                See more linked questions