Click here to Skip to main content
15,860,859 members
Articles / Web Development / ASP.NET

Reusable Class to Generate Excel Files

Rate me:
Please Sign up or sign in to vote.
4.92/5 (11 votes)
20 Mar 2009CPOL3 min read 59.4K   740   62   17
A class that generates an XML string as an Excel file

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.   

C#
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: 

C#
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
Mexico Mexico
Sergio has been working as a software developer since 2002 in several industries such as Insurance, Health, and Oil. He is an MCAD and most likely will be looking to upgrade his certification soon.

He currently lives in Montreal, Canada.

Comments and Discussions

 
QuestionAddtions Pin
Petersi14-Jun-12 8:50
Petersi14-Jun-12 8:50 
GeneralMy vote of 5 Pin
Nazmul108924-Apr-11 1:20
Nazmul108924-Apr-11 1:20 
GeneralStyles Pin
jmvallejo11-Feb-10 11:30
jmvallejo11-Feb-10 11:30 
QuestionWhich versions of office is this format supposed to work with? Pin
popodeperro30-Jan-10 5:24
popodeperro30-Jan-10 5:24 
GeneralPerformance improvement Pin
Clutchplate10-Apr-09 7:02
Clutchplate10-Apr-09 7:02 
GeneralRe: Performance improvement Pin
mirage4566-May-09 4:35
mirage4566-May-09 4:35 
GeneralRe: Performance improvement Pin
jmvallejo12-Feb-10 3:24
jmvallejo12-Feb-10 3:24 
GeneralRe: Performance improvement Pin
AnandChavali30-Sep-12 9:36
AnandChavali30-Sep-12 9:36 
QuestionI want to display the MyExcelTest.xml in web page ? Pin
JLKEngine00820-Mar-09 17:07
JLKEngine00820-Mar-09 17:07 
AnswerRe: I want to display the MyExcelTest.xml in web page ? Pin
Sergio Romero22-Mar-09 4:53
Sergio Romero22-Mar-09 4:53 
Questioncan you help me? Pin
JLKEngine00820-Mar-09 17:07
JLKEngine00820-Mar-09 17:07 
AnswerRe: can you help me? Pin
CikaPero28-Feb-10 21:57
CikaPero28-Feb-10 21:57 
Questioncan you saving an Excel file with a chart as an XML file ? Pin
JLKEngine00820-Mar-09 17:01
JLKEngine00820-Mar-09 17:01 
AnswerRe: can you saving an Excel file with a chart as an XML file ? Pin
Sergio Romero22-Mar-09 4:51
Sergio Romero22-Mar-09 4:51 
GeneralNice Code Pin
obinna_eke18-Mar-09 3:24
obinna_eke18-Mar-09 3:24 
GeneralRe: Nice Code Pin
Sergio Romero18-Mar-09 14:50
Sergio Romero18-Mar-09 14:50 
GeneralRe: Nice Code Pin
Sergio Romero19-Mar-09 4:52
Sergio Romero19-Mar-09 4:52 
Hi,

I tried saving an Excel file with a chart as an XML file and, unfortunately, the chart section was taken out.

This is too bad because it would have been nice, now I'm sorry to say that if you need a chart in your file you'll have to use another solution.

Sorry.

Frown | :(

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.