StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Take a look at this article, which explains how to export a DataTable to Excel using the OpenXML SDK.</p> <p><a href="http://lateral8.com/articles/2010/3/5/openxml-sdk-20-export-a-datatable-to-excel.aspx" rel="nofollow">http://lateral8.com/articles/2010/3/5/openxml-sdk-20-export-a-datatable-to-excel.aspx</a></p> <p>Here is the main content from the article:</p> <p>Create the ExcelExport Class</p> <p>Now create a new class file called ExcelExport.cs in your project. Add the following references to the beginning of the file:</p> <p>using System; using System.Data; using System.Linq; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet;</p> <p>Next, add the following method to the file:</p> <pre><code>public void ExportDataTable(DataTable table, string exportFile) { //create the empty spreadsheet template and save the file //using the class generated by the Productivity tool ExcelDocument excelDocument = new ExcelDocument(); excelDocument.CreatePackage(exportFile); //populate the data into the spreadsheet using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(exportFile, true)) { WorkbookPart workbook = spreadsheet.WorkbookPart; //create a reference to Sheet1 WorksheetPart worksheet = workbook.WorksheetParts.Last(); SheetData data = worksheet.Worksheet.GetFirstChild<SheetData>(); //add column names to the first row Row header = new Row(); header.RowIndex = (UInt32)1; foreach (DataColumn column in table.Columns) { Cell headerCell = createTextCell(table.Columns.IndexOf(column) + 1, 1, column.ColumnName); header.AppendChild(headerCell); } data.AppendChild(header); //loop through each data row DataRow contentRow; for (int i = 0;i < table.Rows.Count; i++) { contentRow = table.Rows[i]; data.AppendChild(createContentRow(contentRow, i + 2)); } } } </code></pre> <p>The method above starts by using the ExcelDocument class created earlier to create a new file, which is saved to the location specified in the exportFile argument. Once the file is created, there are two main loops that occur. The first loops iterates through the DataTable object's columns and creates a Cell object for each column name using the createTextCell method:</p> <pre><code>private Cell createTextCell(int columnIndex, int rowIndex, object cellValue) { Cell cell = new Cell(); cell.DataType = CellValues.InlineString; cell.CellReference = getColumnName(columnIndex) + rowIndex; InlineString inlineString = new InlineString(); Text t = new Text(); t.Text = cellValue.ToString(); inlineString.AppendChild(t); cell.AppendChild(inlineString); return cell; } </code></pre> <p>Next, each row is appended to the worksheet using the createContentRow method:</p> <pre><code>private Row createContentRow(DataRow dataRow, int rowIndex) { Row row = new Row {RowIndex = (UInt32)rowIndex }; for (int i = 0; i < dataRow.Table.Columns.Count; i++) { Cell dataCell = createTextCell(i + 1, rowIndex, dataRow[i]); row.AppendChild(dataCell); } return row; } </code></pre> <p>The createTextCell method uses one additional supporting method to do the job of mapping the rows and columns to the correct cell reference, the method getColumnName:</p> <pre><code>private string getColumnName(int columnIndex) { int dividend = columnIndex; string columnName = String.Empty; int modifier; while (dividend > 0) { modifier = (dividend - 1) % 26; columnName = Convert.ToChar(65 + modifier).ToString() + columnName; dividend = (int)((dividend - modifier) / 26); } return columnName; } </code></pre> <p>This method provides a quick an easy way to map a column index number to an Excel column name (A-Z). The Cell object in the OpenXML SDK requires a valid Excel cell reference (ex. A1, C2) to be specified, so this method is combined with a row index reference to create the cell reference. It is important to note that the indexes here are not zero-based.</p> <p>Finally, to implement the class above, use the following code:</p> <pre><code>//create DataTable from sample data DataSet sampleDataSet = new DataSet(); sampleDataSet.ReadXml(context.Server.MapPath("~/sampleData.xml")); DataTable productsTable = sampleDataSet.Tables[0]; string exportFile = context.Server.MapPath("~/excelExport.xslx"); ExcelExport export = new ExcelExport(); export.ExportDataTable(productsTable, exportFile); </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload