Export DataTable to Excel with Open Xml SDK in c

Question

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   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 lt Text gt  sharedStringTextElements   sharedStringsPart SharedStringTable Descendants lt Text gt              DoReplace sharedStringTextElements              Replace inline strings         IEnumerable lt WorksheetPart gt  worksheetParts   workbook GetPartsOfType lt WorksheetPart gt              foreach  var worksheet in worksheetParts                        DoReplace worksheet Worksheet Descendants lt Text gt                         int z   40          foreach  System Data DataRow row in ExcelWorkXLSX ToOut Rows                        for  int i   0  i  lt  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     How can I export DataTable to Excel fast and truly

User · Answer

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

User · Answer

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 lt  gt    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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 lt WorksheetPart gt                         var sheetData   new DocumentFormat OpenXml Spreadsheet SheetData                        sheetPart Worksheet   new DocumentFormat OpenXml Spreadsheet Worksheet sheetData                        DocumentFormat OpenXml Spreadsheet Sheets sheets   workbook WorkbookPart Workbook GetFirstChild lt DocumentFormat OpenXml Spreadsheet Sheets gt                         string relationshipId   workbook WorkbookPart GetIdOfPart sheetPart                        uint sheetId   1                      if  sheets Elements lt DocumentFormat OpenXml Spreadsheet Sheet gt    Count    gt  0                                                sheetId                               sheets Elements lt DocumentFormat OpenXml Spreadsheet Sheet gt    Select s   gt  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 lt String gt  columns   new List lt string gt                         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

User · Answer

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 lt WorksheetPart gt                 var sheetData   new DocumentFormat OpenXml Spreadsheet SheetData                sheetPart Worksheet   new DocumentFormat OpenXml Spreadsheet Worksheet sheetData                                DocumentFormat OpenXml Spreadsheet Sheets sheets   workbook WorkbookPart Workbook GetFirstChild lt DocumentFormat OpenXml Spreadsheet Sheets gt                 string relationshipId   workbook WorkbookPart GetIdOfPart sheetPart                if  sheets Elements lt DocumentFormat OpenXml Spreadsheet Sheet gt    Count    gt  0                                sheetId                       sheets Elements lt DocumentFormat OpenXml Spreadsheet Sheet gt    Select s   gt  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 lt String gt  columns   new List lt string gt                 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

User · Answer

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

[c#] Export DataTable to Excel with Open Xml SDK in c#

Examples related to c#

Examples related to excel

Examples related to openxml

Examples related to openxml-sdk