Microsoft Excel is beloved tool to work with tables. Therefore it is really valuable for end user if an application can export its data into Excel. However today a lot of business application are web application which cause a problem with Excel because the Excel is not installed on web server. Export data to Excel with COM Remote Control of Excel Object Model does not always work fast and reliable.
1. ExcelCreator
Since Excel 2003 there is a very simple way to create a Excel document without installation of Excel, even with XML. XML is only text and can be created by simply appending strings together. In the following code below I would like to demonstrate how we can create a Excel document with XML technology.
DataTable dtCustomer = new DataTable("Customer"); dtCustomer.Columns.Add(new DataColumn("Name")); dtCustomer.Columns.Add(new DataColumn("Gender")); DataRow drItem = dtCustomer.NewRow(); drItem["Name"] = "rongchaua"; drItem["Gender"] = "Male"; dtCustomer.Rows.Add(drItem); drItem = dtCustomer.NewRow(); drItem["Name"] = "4nh7i3m"; drItem["Gender"] = "Male"; dtCustomer.Rows.Add(drItem); ExcelCreator.Create(dtCustomer,@"E:\Export.xls"); MessageBox.Show("Export done");
I declared a DataTable and then add some information into it. At last I call the static function Create to save the DataTable into a Excel file. The code of class ExcelCreator is shown below
class ExcelCreator { /// <summary> /// Create one Excel-XML-Document with SpreadsheetML from a DataTable /// </summary> /// <param name="dataSource">Datasource which would be exported in Excel</param> /// <param name="fileName">Name of exported file</param> public static void Create(DataTable dtSource, string strFileName) { // Create XMLWriter XmlTextWriter xtwWriter = new XmlTextWriter(strFileName, Encoding.UTF8); //Format the output file for reading easier xtwWriter.Formatting = Formatting.Indented; // <?xml version="1.0"?> xtwWriter.WriteStartDocument(); // <?mso-application progid="Excel.Sheet"?> xtwWriter.WriteProcessingInstruction("mso-application", "progid=\"Excel.Sheet\""); // <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet >" xtwWriter.WriteStartElement("Workbook", "urn:schemas-microsoft-com:office:spreadsheet"); //Write definition of namespace xtwWriter.WriteAttributeString("xmlns", "o", null, "urn:schemas-microsoft-com:office:office"); xtwWriter.WriteAttributeString("xmlns", "x", null, "urn:schemas-microsoft-com:office:excel"); xtwWriter.WriteAttributeString("xmlns", "ss", null, "urn:schemas-microsoft-com:office:spreadsheet"); xtwWriter.WriteAttributeString("xmlns", "html", null, "http://www.w3.org/TR/REC-html40"); // <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"> xtwWriter.WriteStartElement("DocumentProperties", "urn:schemas-microsoft-com:office:office"); // Write document properties xtwWriter.WriteElementString("Author", Environment.UserName); xtwWriter.WriteElementString("LastAuthor", Environment.UserName); xtwWriter.WriteElementString("Created", DateTime.Now.ToString("u") + "Z"); xtwWriter.WriteElementString("Company", "Unknown"); xtwWriter.WriteElementString("Version", "11.8122"); // </DocumentProperties> xtwWriter.WriteEndElement(); // <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"> xtwWriter.WriteStartElement("ExcelWorkbook", "urn:schemas-microsoft-com:office:excel"); // Write settings of workbook xtwWriter.WriteElementString("WindowHeight", "13170"); xtwWriter.WriteElementString("WindowWidth", "17580"); xtwWriter.WriteElementString("WindowTopX", "120"); xtwWriter.WriteElementString("WindowTopY", "60"); xtwWriter.WriteElementString("ProtectStructure", "False"); xtwWriter.WriteElementString("ProtectWindows", "False"); // </ExcelWorkbook> xtwWriter.WriteEndElement(); // <Styles> xtwWriter.WriteStartElement("Styles"); // <Style ss:ID="Default" ss:Name="Normal"> xtwWriter.WriteStartElement("Style"); xtwWriter.WriteAttributeString("ss", "ID", null, "Default"); xtwWriter.WriteAttributeString("ss", "Name", null, "Normal"); // <Alignment ss:Vertical="Bottom"/> xtwWriter.WriteStartElement("Alignment"); xtwWriter.WriteAttributeString("ss", "Vertical", null, "Bottom"); xtwWriter.WriteEndElement(); // Write null on the other properties xtwWriter.WriteElementString("Borders", null); xtwWriter.WriteElementString("Font", null); xtwWriter.WriteElementString("Interior", null); xtwWriter.WriteElementString("NumberFormat", null); xtwWriter.WriteElementString("Protection", null); // </Style> xtwWriter.WriteEndElement(); // </Styles> xtwWriter.WriteEndElement(); // <Worksheet ss:Name="xxx"> xtwWriter.WriteStartElement("Worksheet"); xtwWriter.WriteAttributeString("ss", "Name", null, dtSource.TableName); // <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="3" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="60"> xtwWriter.WriteStartElement("Table"); xtwWriter.WriteAttributeString("ss", "ExpandedColumnCount", null, dtSource.Columns.Count.ToString()); xtwWriter.WriteAttributeString("ss", "ExpandedRowCount", null, dtSource.Rows.Count.ToString()); xtwWriter.WriteAttributeString("x", "FullColumns", null, "1"); xtwWriter.WriteAttributeString("x", "FullRows", null, "1"); xtwWriter.WriteAttributeString("ss", "DefaultColumnWidth", null, "60"); // Run through all rows of data source foreach (DataRow row in dtSource.Rows) { // <Row> xtwWriter.WriteStartElement("Row"); // Run through all cell of current rows foreach (object cellValue in row.ItemArray) { // <Cell> xtwWriter.WriteStartElement("Cell"); // <Data ss:Type="String">xxx</Data> xtwWriter.WriteStartElement("Data"); xtwWriter.WriteAttributeString("ss", "Type", null, "String"); // Write content of cell xtwWriter.WriteValue(cellValue); // </Data> xtwWriter.WriteEndElement(); // </Cell> xtwWriter.WriteEndElement(); } // </Row> xtwWriter.WriteEndElement(); } // </Table> xtwWriter.WriteEndElement(); // <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> xtwWriter.WriteStartElement("WorksheetOptions", "urn:schemas-microsoft-com:office:excel"); // Write settings of page xtwWriter.WriteStartElement("PageSetup"); xtwWriter.WriteStartElement("Header"); xtwWriter.WriteAttributeString("x", "Margin", null, "0.4921259845"); xtwWriter.WriteEndElement(); xtwWriter.WriteStartElement("Footer"); xtwWriter.WriteAttributeString("x", "Margin", null, "0.4921259845"); xtwWriter.WriteEndElement(); xtwWriter.WriteStartElement("PageMargins"); xtwWriter.WriteAttributeString("x", "Bottom", null, "0.984251969"); xtwWriter.WriteAttributeString("x", "Left", null, "0.78740157499999996"); xtwWriter.WriteAttributeString("x", "Right", null, "0.78740157499999996"); xtwWriter.WriteAttributeString("x", "Top", null, "0.984251969"); xtwWriter.WriteEndElement(); xtwWriter.WriteEndElement(); // <Selected/> xtwWriter.WriteElementString("Selected", null); // <Panes> xtwWriter.WriteStartElement("Panes"); // <Pane> xtwWriter.WriteStartElement("Pane"); // Write settings of active field xtwWriter.WriteElementString("Number", "1"); xtwWriter.WriteElementString("ActiveRow", "1"); xtwWriter.WriteElementString("ActiveCol", "1"); // </Pane> xtwWriter.WriteEndElement(); // </Panes> xtwWriter.WriteEndElement(); // <ProtectObjects>False</ProtectObjects> xtwWriter.WriteElementString("ProtectObjects", "False"); // <ProtectScenarios>False</ProtectScenarios> xtwWriter.WriteElementString("ProtectScenarios", "False"); // </WorksheetOptions> xtwWriter.WriteEndElement(); // </Worksheet> xtwWriter.WriteEndElement(); // </Workbook> xtwWriter.WriteEndElement(); // Write file on hard disk xtwWriter.Flush(); xtwWriter.Close(); } }
The code listing above can create a Excel file (*.xls) from scratch. However if you open the created file, you’ll get a warning
Just ignore the warning and keep opening the file, Microsoft Excel can handle this format.
2. ClosedXML
ClosedXML makes it easier for developers to create Excel 2007/2010 files. It provides a nice object oriented way to manipulate the files (similar to VBA) without dealing with the hassles of XML Documents. It can be used by any .NET language like C# and Visual Basic (VB).
XLWorkbook xlWorkbook = new XLWorkbook(); xlWorkbook.Worksheets.Add(dtCustomer); xlWorkbook.SaveAs("ClosedXML.xlsx");
ClosedXML can’t create Excel file (*.xls) compatible with Excel 97/2000/2003 because it uses new standard format OpenXML of Microsoft Office. Therefore if you have no problem with using Microsoft Excel version later than 2007, then use this library for generating Excel file from DataTable.
2. Updates
2.1 Update 29.03.2012
– Add one option so that one can export with column header.
2.2 Update 05.12.2015
– Use ClosedXML to create Excel from DataTable.
3. Source code
Source code: https://bitbucket.org/hintdesk/dotnet-create-excel-file-from-datatable-without-using-excel
when i open output excel file it will have XML file contents with schema. can you please help to resolve
I’m using your code to write about 15,000 rows of data to a spreadsheet. While it only takes about 20 seconds for me to open the spreadsheet, my end user is saying that it takes him around 2 minutes. He claims that he opens other spreadsheets similar in size much more quickly.
Do you think there’s anything happening in this code that would cause this issue?
Do you feel this method is not a good choice for creating spreadsheets containing 15,000 records?
Thanks!
Hi
If i specify the file name extension as .xlsx then your code doesn’t works..can you help me on this?
Regards,
Kalyan
@kalyan: Use this library for xlsx file http://epplus.codeplex.com/ or use this one http://excelpackage.codeplex.com/ . There are now a lot of free libraries in Internet so that you can create a Excel worksheet without installing Excel.
Thanks it’s working for me..but one problem is i have to send the columns also but in your code there is no columns..to send the excel only rows are sending please give me replay…its an urgent thanks in advance….
@vijay: I’ve updated source code for exporting Header. The export function will have now one option more “writeHeader”. Set it to true if you want to export the column name with.
Valuable post, thanks a lot!
Even large excel files are created quickly and requires little memory.
P.S. My datatable contains some empty values. To avoid exception like this
“Xml type ‘List of xdt:untypedAtomic’ does not support a conversion from Clr type ‘DBNull’ to Clr type ‘String’.”
I had to replace the code snippet
xtwWriter.WriteValue(cellValue);
with
xtwWriter.WriteValue(cellValue is DBNull ? string.Empty : cellValue);
@Igor : Thanks for your feedbacks.
“The file you are trying to open, ‘test.xls’, is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now ?”
Can you please help me how to fix above issue?
@krishna: That’s a known problem of that code listing. I update the post with new library for generating Excel in later version 2007 format. If you don’t have any problem to use Microsoft Excel OpenXML (at least 2007 version), you can try that ClosedXML library.
Hi, you can try ZetExcel