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

Different format

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

11 comments

Vidya says:

March 2, 2011 at 11:35 am

when i open output excel file it will have XML file contents with schema. can you please help to resolve

Amy Jonas says:

March 24, 2011 at 2:01 pm

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!

kalyan says:

September 28, 2011 at 12:15 am

Hi

If i specify the file name extension as .xlsx then your code doesn’t works..can you help me on this?

Regards,
Kalyan

admin says:

September 29, 2011 at 9:36 am

@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.

vijay kumar N says:

March 27, 2012 at 3:21 am

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….

admin says:

March 28, 2012 at 11:46 pm

@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.

Igor says:

September 28, 2012 at 7:57 pm

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);

admin says:

October 2, 2012 at 7:53 am

@Igor : Thanks for your feedbacks.

krishna says:

November 24, 2015 at 12:02 am

“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?

admin says:

December 5, 2015 at 1:03 pm

@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.

Jeremy Gore says:

September 10, 2018 at 3:12 pm

Hi, you can try ZetExcel

C# – Create Excel file from DataTable without using Excel Object