Export DataTable to Excel with Open Xml SDK in c#
Solution 1
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);
}
}
}
}
Solution 2
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);
}
}
}
}
Solution 3
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:
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);
Solution 4
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.
Related videos on Youtube
user1576474
Updated on July 09, 2022Comments
-
user1576474 almost 2 years
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)); } z++; } } } }
But this fragment to output DataTable slooooooooooooooooooooooowwwwwww...
How can I export DataTable to Excel fast and truly?
-
KLIM8D almost 12 yearsDo you need to use the open xml sdk?
-
user1576474 almost 12 yearsHmm... 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 almost 12 yearsThanks! This It's an idea! I will try it only for export.
-
Brian about 11 yearsI think
workbook.WorkbookPart.Workbook = new...
andworkbook.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 finalDataTable
. -
eburgos about 11 years@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 about 11 yearsThanks, just saw Brian's comment and I did the same on mine.
-
Brian about 11 yearsYeah, 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 aList<String> columns
sinceDataRow
has aDataColumn
indexer; the secondforeach
can iterate overtable.Columns
as well. -
eburgos about 11 yearsoh well, this is for educational purposes only I guess :) thanks
-
Rahul Chowdhury almost 11 years@eburgos this code is amazing ..Can we export an image form our local drive and show it in excel cell .i need it badly
-
eburgos over 10 years@RahulChowdhury thanks. Yes you can, it is a bit different though because an image is an external resource that should be embedded in your excel file and included from within your worksheet. I see you wrote this comment 2 days ago (sorry for the late response), do you still need it?
-
AaA over 10 yearsI couldn't find references for
SpreadsheetDocument
andWorksheetPart
inDocumentFormat.OpenXml
reference. Would you please specify the references too? -
AaA over 10 yearsI figured it out myself. To make this work it is necessary to add
WindowsBase
andDocumentFormat.OpenXml
references to project. AlsoSpreadsheetDocument
andWorksheetPart
are inDocumentFormat.OpenXml.Packaging
namespace -
eburgos over 10 yearsah sorry I just noticed this comment now. I will add this to the answer. Glad you figured it out!
-
Mike Gledhill almost 8 yearsSorry...! It's alive and kicking again now.
-
afr0 over 6 years@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?
-
Mike Gledhill over 6 years@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.
-
afr0 over 6 yearsmay be there is something how the codebase I have got is written not quite sure.