Scott Watermasysk, brainsource of the truly excellent .Text ASP.NET Weblog technolgy, boosted my ego by posting a comment to one of my recent blog entries about using XML to generate Excel workbooks. I wanted to call it out more clearly here, because he mentioned Carlos Aguilar's Excel Writer Library, a managed OM that lets you build Excel workbooks without using the Excel OM. It's not too hard to figure out, works pretty well, and it's fast. I would love to see him/our community add more to his library as I did run into some limitations a little early (datatypes for columns being very limited for example- no currency!). That said, there is A LOT that can be done with this library. Great work, Carlos!

I tried it out by messing around with it and copying a few lines from his help file. Here's the code:

   Workbook wbk=new Workbook();
   WorksheetStyle style = wbk.Styles.Add("HeaderStyle");
   style.Font.FontName = "Arial";
   style.Font.Size = 12;
   style.Font.Bold = true;
   style.Alignment.Horizontal = StyleHorizontalAlignment.Center;
   style.Font.Color = "White";
   style.Interior.Color = "Blue";
   Worksheet sht = wbk.Worksheets.Add("My Payroll");
   WorksheetRow row =  sht.Table.Rows.Add();
   row.Cells.Add(new WorksheetCell("Pay Date", "HeaderStyle"));
   row.Cells.Add(new WorksheetCell("Amount", "HeaderStyle"));
   row =  sht.Table.Rows.Add();
   row.Cells.Add(new WorksheetCell("1/31/2004",DataType.String));
   row.Cells.Add(new WorksheetCell("20,000.00",ExcelWriter.DataType.Number));
   row = sht.Table.Rows.Add();
   row.Cells.Add(new WorksheetCell("2/28/2004",DataType.String));
   row.Cells.Add(new WorksheetCell("20,000.00",ExcelWriter.DataType.Number));
   row = sht.Table.Rows.Add();
   WorksheetCell cell = row.Cells.Add("Total");  
   cell = row.Cells.Add();
   cell.Formula="SUM(R[-2]C:R[-1]C)";
   wbk.Save("c:\\testWorkbook.xls");

This built a spreadsheet with my true salary sum for January and February.  Cool.

 Rock thought for the day: Leadbelly. That's all that needs to be said.

Rock on.