Introduction
So far I haven't met any developer that has not, at some point in his/her career, had the need to export or display data in an Excel file. There are hundreds of samples of different ways to achieve this all over the web, including The Code Project. Unfortunately I've never seen anything about a class that can be reused in different scenarios.
Fortunately, starting with Excel 2003, we can create an XML file with particular formatting and it will be opened as an Excel file. There are several ways to create an XML document, you can use the System.Xml
namespace, concatenate string
s or use a string builder (I don't recommend it, but it's possible) but, now you can use LINQ to XML and creating XML becomes so easy it's almost not fun anymore.
Limitations of the Code
Like I said, this works starting with Excel 2003 and later so, if you still have Office XP then you're out of luck.
Also this code is restricted to Excel's limitations. That is, it cannot handle more than 256 columns or 65,000 rows.
Using the Code
This is just a single class called XmlExcelHelper
that contains all the methods to add Worksheets, Rows, Cells, and even Styles for string
s to the new file. You need the System.Xml
, System.Xml.Linq
, and System.Core
namespaces for it to work.
Almost every method, including the class' constructor have several overloads, I'll demonstrate how to use some of them and you can play with the rest. The class is fully documented and I think is very straightforward. Explaining the XML nodes that Excel needs is beyond the scope of this article.
The XmlExcelHelper
's constructors call a private
method called CreateExcelHeader
. This method adds the XML structure that makes the created file to be an Excel file and I would like to thank Ming_Lu whose article located here gave me everything that was needed to create this class.
Here is a small example of how we can use this class to feed data into the XML and create a nice Excel table. I'm creating a dummy DataTable
, but you can use custom objects, Dataset
s or anything that has data that you need to save as an Excel file.
private const string TABLE_HEADER_FORMAT_NAME = "greyBackground";
private const string SMALL_FONT_FORMAT = "smallFont";
private static DataTable CreateDataTable()
{
DataTable companies = new DataTable("Companies");
DataColumn column;
column = new DataColumn("CompanyName");
companies.Columns.Add(column);
column = new DataColumn("CompanyCountry");
companies.Columns.Add(column);
column = new DataColumn("YearSales");
companies.Columns.Add(column);
column = new DataColumn("GrossProfit");
companies.Columns.Add(column);
column = new DataColumn("CreationDate");
companies.Columns.Add(column);
companies.Rows.Add("Mexican Tequila", "Mexico", "10875600",
"4975200", "03/24/1995 2:35:00PM");
companies.Rows.Add("Canadian Food", "Canada", "308450870",
"103476200", "08/12/1983");
companies.Rows.Add("French Wines", "France", "285309567",
"81650000", "11/21/1990 17:40:00");
companies.Rows.Add("German Beer", "Germany", "285309567",
"81650000", "11/21/1990 17:40:00");
companies.Rows.Add("Russian Vodka", "Russia", "285309567",
"81650000", "11/21/1990 17:40:00");
companies.Rows.Add("Spanish Paella", "Spain",
"285309567", "81650000", "11/21/1990 17:40:00");
companies.Rows.Add("Argentinian Beef", "Argentina",
"285309567", "81650000", "11/21/1990 17:40:00");
companies.Rows.Add("Italian Pasta", "Italy",
"285309567", "81650000", "11/21/1990 17:40:00");
return companies;
}
public static void CreateReport()
{
DataTable table = CreateDataTable();
XmlExcelHelper helper = new XmlExcelHelper
(@"c:\MyExcelTest.xml", "Times New Roman", 11);
helper.AddStringStyle(TABLE_HEADER_FORMAT_NAME,
"Arial", 10, "#FFFFFF", "#C0C0C0", false);
helper.AddStringStyle(SMALL_FONT_FORMAT, "Arial", 8, "#000000", false);
helper.CreateSheet("International Foods");
helper.AddRow();
helper.AddCell(XmlExcelHelper.CellType.String, "Report Generated with LINQ to XML");
helper.AddRow();
helper.AddCell(XmlExcelHelper.CellType.String,
SMALL_FONT_FORMAT, "Report generated on " + DateTime.Now.ToLongDateString());
helper.AddRow();
foreach (DataColumn column in table.Columns)
{
helper.AddCell(XmlExcelHelper.CellType.String,
TABLE_HEADER_FORMAT_NAME, column.ColumnName);
}
foreach (DataRow row in table.Rows)
{
helper.AddRow();
helper.AddCell(XmlExcelHelper.CellType.String, row[0].ToString());
helper.AddCell(XmlExcelHelper.CellType.String, row[1].ToString());
helper.AddCell(XmlExcelHelper.CellType.Number,
XmlExcelHelper.DefaultStyles.Decimal, row[2].ToString());
helper.AddCell(XmlExcelHelper.CellType.Number,
XmlExcelHelper.DefaultStyles.Decimal, row[3].ToString());
helper.AddCell(XmlExcelHelper.CellType.String, row[4].ToString());
}
helper.SaveDocument();
}
As you can see, the class has a SaveDocument
method to save the XML file to disk. It also has an ExcelFileXml
property which holds the XML string
so, if you need to display the Excel file from a Web application in your web page, you would have to do something like this:
Response.Clear();
Response.AppendHeader("Content-Type", "application/vnd.ms-excel");
Response.AppendHeader("Content-disposition", "attachment; filename=" + helper.FileName);
Response.Write(helper.ExcelFileXml);
Response.Flush();
Response.End();
Points of Interest
Of course this class is just a functional way of creating an Excel file. It has a lot of functionality, you can add several worksheets, add different styles for string
s, merge several cells but it could do a lot more. If there's something in particular that you need, you just have to do what you want in Excel, save it as XML and open it to see how the XML looks and you can add it to the class.
Hopefully you'll find this useful. Have fun.