Excel Writer Library, a step in an interesting direction

  • Article

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.