Making Excel The CarlosAg Way

One question we see a lot on the Sitepoint ASP.NET forums is “Hey, my boss needs me to make an Excel spreadsheet from this here data set. Please help. You are my last hope before I get fired!”

Now, there are a number of ways to generate an excel spreadsheet. The obvious way it to use COM interop and Excel’s own object model to do the job. This is possibly the worst plan. First, it requires one install Excel on the web server to work. Second, Excel is designed to run in user mode, and will pop modial dialog boxes—effectively freezing your applications. I shall ignore the “fun” tasks associated with COM interop and the pure joy of dealing with the Excel object model. Nevermind scalability issues and the like.

The second set of obvious options can get a bit expensive—the various commercial excel generation libraries. Another option in this vein—and possibly the most useful one—is Sql Server 2005 Reporting Services. But only if you are already using it for other reporting tasks, implementing it for some excel export alone is a bit of a waste. The other old standby is to generate a html table and tell a browser "yes, by jove, this is excel." But this is a limited solution at best. What is a developer to do?

Enter CarlosAg

But there is one other option. Modern versions [2002/XP or later] of Excel support the Xml Spreasheet specification. And if .NET is good for just one thing, it would beXml. Enter CarlosAg’s ExcelXmlWriter library. I have used it on a few projects and I must say it is very slick. It is the only free library I have seen which allow a developer to create a nearly fully-featured spreadsheet—including formulas, heavily styled cells and pivot tables—without crossing the COM bridge or paying significant cash.

Now, its not without its issues. The most significant one is that the developer must be very careful to properly format data because is no type filtering at the library level. Just like in any export operation, one must test the output carefully to make certain it is parsable. Nor is there a native .NET 2.0 binary available as of this writing. But I have not yet ran into a show-stopping bug. It is definitely a solid library and clearly quite sweet for the price—free.

And now we get to the good part—code samples. Lets say you need to, oh, just dump a dataset into Carlos’ writer for posterity’s sake.

Well, first you need a class to deal with the dataset as a whole:


using System.Data;
using CarlosAg.ExcelXmlWriter;

namespace WWB.ExcelDatasetWriter
{
	/// <summary>
	/// Creates an excel-friendly Xml Document from a dataset using the CarlosAg.ExcelXmlWriter library.
	/// </summary>

	public class ExcelDatasetWriter
	{
		public ExcelDatasetWriter()
		{}


		public Workbook CreateWorkbook(DataSet data)
		{
			//ensure valid data
			if (data==null)
			{
				throw new ArgumentNullException("data", "Data cannot be null.");
			}
			ensureTables(data);

			//Variable declarations
			//our workbook
			Workbook wb=new Workbook(); 
			//Our worksheet container
			Worksheet ws; 
			//Our DataTableWriter
			ExcelDataTableWriter edtw=new ExcelDataTableWriter(); 
			//Our sheet name
			string wsName; 
			//Our counter
			int tCnt=0; 

			//Loop through datatables and create worksheets
			foreach (DataTable dt in data.Tables)
			{
				//set the name of the worksheet
				if (dt.TableName!=null && dt.TableName.Length>0 && dt.TableName!="Table")
				{
					wsName=dt.TableName;
				}
				else
				{
					//Go to generic Sheet1 . . . SheetN
					wsName="Sheet" + (tCnt+1).ToString();
				}
				//Instantiate the worksheet
				ws=wb.Worksheets.Add(wsName);
				//Populate the worksheet
				edtw.PopulateWorksheet(dt, ws);
				tCnt++;
			}
			return wb;
		}


		private void ensureTables(DataSet data)
		{
			if (data.Tables.Count==0)
			{
				throw new ArgumentOutOfRangeException("data", "DataSet does not contain any tables.");
			}
		}
	}
}

Then you need a class to deal with each of the DataTables


using System;
using System.Data;
using CarlosAg.ExcelXmlWriter;

namespace WWB.ExcelDatasetWriter
{
	/// <summary>
	/// Writes a datatable to a worksheet using the CarlosAG.ExcelXmlWriter library.
	/// </summary>

	public class ExcelDataTableWriter
	{
		public ExcelDataTableWriter()
		{}

		public void PopulateWorksheet(DataTable dt, Worksheet toPopulate)
		{
			PopulateWorksheet(dt, toPopulate, true);
		}

		public void PopulateWorksheet(DataTable dt, Worksheet toPopulate, bool makeHeader)
		{
			//check valid input
			if (toPopulate==null)
			{
				throw new ArgumentNullException("toPopulate", "Worksheet cannot be null.");
			}
			if (dt==null)
			{
				throw new ArgumentNullException("dt", "DataTable cannot be null");
			}
			//Parse the columns
			ColumnType[] colDesc=parseColumns(dt);
			//Create header row
			if (makeHeader)
			{
				toPopulate.Table.Rows.Insert(0, makeHeaderRow(colDesc));
			}
			//Create rows
			foreach (DataRow row in dt.Rows)
			{
				toPopulate.Table.Rows.Add(makeDataRow(colDesc, row));
			}
		}

		#region row + cell making
		
		private WorksheetRow makeHeaderRow(ColumnType[] cols)
		{
			WorksheetRow ret=new WorksheetRow();
			foreach(ColumnType ctd in cols)
			{
				ret.Cells.Add(ctd.GetHeaderCell());
			}
			return ret;
		}

		private WorksheetRow makeDataRow(ColumnType[] ctds, DataRow row)
		{
			WorksheetRow ret=new WorksheetRow();
			WorksheetCell tmp=null;
			for (int i=0; i<row.Table.Columns.Count; i++)
			{
				tmp=ctds[i].GetDataCell(row[i]);
				ret.Cells.Add(tmp);
			}
			return ret;
		}

		#endregion

		#region column parsing
		private ColumnType[] parseColumns(DataTable dt)
		{
			ColumnType[] ret=new ColumnType[dt.Columns.Count];
			ColumnType ctd=null;
			for (int i=0; i<dt.Columns.Count; i++)
			{
				ctd=new ColumnType();
				ctd.Name=dt.Columns[i].ColumnName;
				getDataType(dt.Columns[i], ctd);
				ret[i]=ctd;
			}
			return ret;
		}

		private void getDataType(DataColumn col, ColumnType desc)
		{
			if (col.DataType==typeof(DateTime))
			{
				desc.ExcelType=DataType.DateTime;
			}
			else if (col.DataType==typeof(string))
			{
				desc.ExcelType=DataType.String;
			}
			else if (col.DataType==typeof(sbyte) 
				|| col.DataType==typeof(byte) 
				|| col.DataType==typeof(short) 
				|| col.DataType==typeof(ushort)
				|| col.DataType==typeof(int)
				|| col.DataType==typeof(uint)
				|| col.DataType==typeof(long)
				|| col.DataType==typeof(ulong)
				|| col.DataType==typeof(float)
				|| col.DataType==typeof(double)
				|| col.DataType==typeof(decimal)
				)
			{
				desc.ExcelType=DataType.Number;
			}
			else
			{
				desc.ExcelType=DataType.String;
			}
		}
		#endregion
	}
}

And finally, this class rests on a class to deal with the individual column types.


using System;
using CarlosAg.ExcelXmlWriter;

namespace WWB.ExcelDatasetWriter
{
	/// <summary>
	/// Creates a Column for CarlosAg.ExcelXmlWriter
	/// </summary>

	internal class ColumnType
	{
		public ColumnType()
		{}
		private string name;
		public string Name
		{
			get {return name;}
			set {name=value;}
		}

		private DataType excelType;
		public DataType ExcelType
		{
			get {return excelType;}
			set {excelType=value;}
		}

		public WorksheetCell GetHeaderCell()
		{
			WorksheetCell head=new WorksheetCell(Name, DataType.String);
			return head;
		}

		private string getDataTypeFormatString()
		{
			if (ExcelType==DataType.DateTime)
			{
				return "s";
			}
			return null;
		}

		public WorksheetCell GetDataCell(object data)
		{
			WorksheetCell dc=new WorksheetCell();
			dc.Data.Type=ExcelType;
			if (ExcelType==DataType.DateTime && data is DateTime)
			{
				DateTime dt=(DateTime)data;
				dc.Data.Text=dt.ToString("s");
			}
			else
			{
				string dataString=data.ToString();
				if (dataString == null || dataString.Length==0)
				{
					dc.Data.Type=DataType.String;
					dc.Data.Text=string.Empty;
				}
				else
				{
					dc.Data.Text=dataString;
				}
			}
			return dc;
		}
	}
}

Now, the first two code samples are quite straight-forwards. But the final one deals with a few quirks of the library. The main issue is the WorksheetCell.Data.Text property is actually a string, not an object. It literally writes its value to the spreadsheet. And sometimes this can conflict with the DataType property, leading to interesting (and hard to pinpoint) excel errors. In any case, the GetCellData(object) method wraps reasonably effectively for general purpose use.

Now, to make excel open the spreadsheet, you just need to remember to stream it back to the browser with the appropriate content type:


	ExcelDatasetWriter edw=new ExcelDatasetWriter();
	Workbook wb=CreateWorksheet(MyDataSet);
	Response.ContentType = "application/vnd.ms-excel";
	wb.Save(Response.OutputStream);

So, to summarize:

  1. Writing excel is a common need.
  2. Using Excel object libraries on a web server is a bad idea.
  3. Writing Excel using managed code is a good idea.
  4. CarlosAg’s excel writer helps you generate excel without ever stepping into interop land.

Enjoy and happy coding.

Free book: Jump Start HTML5 Basics

Grab a free copy of one our latest ebooks! Packed with hints and tips on HTML5's most powerful new features.

  • http://www.sitepoint.com Matthew Magain

    This looks really neat Wyatt. I got into the trap on a large project once of manually exporting to CSV, then importing into Excel manually – I was told the transformation would be a one-off.

    Of course it’s never a one-off with clients, and we got into an ugly cycle of having to document the manual process to be followed every time, but even when the instructions were clear, someone would still screw it up, and we ended up writing a custom transform in the end anyway (not web-based though).

    Automate whenever possible, I say.

  • Anonymous

    I don’t know if this trick still works, but years ago you used to be able to output to Excel from a web application by writing out a big HTML table (complete with old-school presentational attributes like bgcolor) and sending it back to the browser with “application/vnd.ms-excel” as the Content-Type. It’s documented here so it probably still works.

  • wwb_99

    Yes, it still works. I even mention it above. But it is a hacky solution at best for several reasons. Mainly because this technique is not really based on a published standard and could easily be broken because you are relying upon excel to properly interpert your html. Furthermore you definitely cannot take advantage of advanced excel features, such as datatypes or formulas, by dumping an html table. Wheras using an Xml Spreadsheet based solution, such as the above, lets you do some pretty nifty things.

    Now, I should say that the HTML table route is about the only good option avaliable should you need to generate spreadsheets for excel 97 or 2000 clients. But those are getting fewer and farther between these days.

  • http://www.vitaleffect.com Gamermk

    “Hey, my boss needs me to make an Excel spreadsheet from this here data set. Please help. You are my last hope before I get fired!”

    And sometimes this can conflict with the DataType property, leading to interesting (and hard to pinpoint) excel errors. In any case, the GetCellData(object) method wraps reasonably effectively for general purpose use.

    Hmm… these two parts worry me.

  • wwb_99

    @Gamermk: I never claimed the library was perfect. But it is far preferable to any free excel generation utility I have come by, especially when you consider the feature set. If you have a better option, please do tell.

    If you want to be very safe, you can always set the data type as DataType.String and leave it at that.

  • http://www.jasonwong.net cmorbutts

    I had a go with this library a while ago. In my application, I had to export rows that ranged from 10 – 1000s and it sure took a long time to open in Excel (for heaps of rows). The library wasn’t slow in generating the xls file. It’s just that the extra formatting it throws it makes it heavy to download, and Excel takes a long time opening it.

    I’d suggest simple CSV for such purposes, and use this library for smaller spreadsheets that require Excel specific formatting.

  • wwb_99

    @cmorbutts [hehe, nice name]

    It definitely does result in a larger file size. Insofar as load times go, I don’t think it was inordinate. My test dataset was three tables from Northwind (Orders, Order Details & Products). It weighed in at 2mb, and had ~2000 total rows in all three sheets. Now, I could see where a 10000 row sheet might take some time to load. Of course, a standards 10000 row sheet takes some time to load as well.

    CSV is an interesting option at times, but passing around data in such loose formats gives me the heebie jeebies. I would rather deal with the overhead of xml and be certain that schemas are being properly followed.

  • chrisb

    If you’re worried about filesizes but dont fanct CSV, generating a custom XML structure and simply applying a transform once you’ve downloaded it is easy enough too – its what I do when running reports against our 2000~ server estate

  • wwb_99

    @ChrisB: that defintiely makes sense in controlled scenarios, such as yours. But when you are dealing with the general public, one needs to supply things in a format that they can use without issue. Xml Spreadsheet is an interesting option on this front.

  • wwb_99

    Update: Drinking a bit of me own kool aid and using this library yet again. I ran into one thing which I should have noted above–DO NOT to use the DataType.Integer enumeration value, it seems to blow everything up. DataType.Number works just fine though.

  • http://humble-high-school.ustur.org Jennifer

    You guys do a wonderful job! Keep up the good work!!!

  • Pingback: Technical Related Notes » Blog Archive » links for 2006-12-31

  • Adrian Malone

    Is there any way i could use this in reverse and read in values from an excel file. I’m using the following code to read in from an excel file but when i try and host the web site it dosn’t work. Here’s my code:
    Excel.Application excelApp = new Excel.ApplicationClass();
    Excel.Workbook newWorkbook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);

    string workbookPath = fuExcel.PostedFile.FileName;
    Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0, true, 5, “”, “”, true, Excel.XlPlatform.xlWindows, “”, false, false, 0, true, false, false);

    Excel.Sheets excelSheets = excelWorkbook.Worksheets;

    string currentSheet = “Contacts”;

    Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);

    int rows;
    rows = excelWorksheet.UsedRange.Rows.Count;

    Excel.Range excelCell = (Excel.Range)excelWorksheet.get_Range(“A1″, “CN” + rows);
    object[,] cells = (object[,])excelCell.Value2;

    Can i install excel on the server or is there some other way i can read in the values?

    Any help would be greatly appreciated.

    Thanks,

    Adrian

  • Chander

    Wyatt,

    Had a glance at your example,Is it possible to create worksheets with two different datasets in one shot?

    Thanks

    Regards

    Chander

  • wwb_99

    Sure, but the code above will probably serve as little more than a guide to creating the worksheet. The API is very flexible–I usually feed it an object graph rather than a dataset myself.

  • Matt

    We currently have to use the COM approach as when the user runs the excel document we want to generate charts on the fly.

    Is it possible to add macros and bind them to events using this method?

  • Chris

    Thanks for this! Ver comprehensive.

  • Emily

    Hi, really needs your help with it.

    I’m trying to use the CarlosAg ExcelXmlWriter dll (which helped me alot so far) to create a pivot table, but somehow I got stuck with it.

    Does anyone got a simple example for using his dll to export a data table to a pivot table?

    thanks,
    Emily.

  • Pingback: Leading Email Marketing

  • Jan

    Hi,

    is it possible to insert image into WorksheetCell
    using CarlosAg ExcelXmlWriter dll???

    Or additionally to add it into generated document?
    Without use of Excel automation, of course.

    thanks a lot for any advice.

    Jan
    hecht@seznam.cz

  • tmgleas

    Thanks for the example. It was very helpful. This .dll is very good as is the reverse engineering tool. Sadly, my needs require that reports be formatted with graphics (logos, charts). Given that the tool doesn’t support this, there remains a large gap between retail products like SoftArtisans and Carlos’ contribution. It would be perfect if you could open a template and stream the XML (just do this now with XML Writer object). This would be beside the point of not having Excel on server, I know.

    Great for just dumping data into Excel, a joy to be free of COM (and free of cost!!)…unless you then have to open the output with COM to format further. I think anyone who muddles into SpreadsheetML needs a hobby :)

  • Michelle

    I have a problem while using CarlosAg.ExcelXmlWriter to create excel file. I have installed the web application on the server. The application will create excel file on my machine and machine A without any problem. However, it does not work on machine B. I mean, no excel file will be created but only a xml file will be created. below is the xml code header part:

    -

    -

    Anyone has any ideas?

  • Anonymous

    People Still active here. I have a few questions?

  • clau

    I have a problem while using CarlosAg.ExcelXmlWriter when finished to create a file big excel .. (4300 reg aprox) the process was finished correctly (apparently).. but when i try to open the file(which create) only wait process and not open it Can you help me please?

  • Joy

    hey can i insert image by using CarlosAg.ExcelXmlWriter…. please help me want to insert some small logo on report….
    Thankx