Reusable Class to Generate Excel Files

• Download source - 8.65 KB

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 strings 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 strings 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, Datasets 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 strings, 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.