About Excel exporting in CarlosAG way..

I was looking for managed code component for excel exporting and happened to test a freeware component named CarlosAG Excel XML Writer Library. The following contains my thoughts about the pros and cons..

What does it do?

“This library allows you to generate Excel Workbooks using XML, it is built 100% in C# and does not requires Excel installed at all to generate the files. It exposes a simple object model to generate the XML Workbooks.
It supports several features for generating Excel Workbooks including:

• Formatting
• Alignment
• Formulas
• Pivot Tables
• and more... “ (From carlosag.net)

This is nice, I suppose, but these keywords are not good enough for me. I want to try it out and see what the developer sees and what exactly will end up in the file.

what do I need ?

The excel functionality I want is quite simple actually:

• to support data types
• to support unicode

I’ll also try out fancier things which I foresee myself using at some point:

• basic formatting (background colors, bold, borders would be nice)
• formulas – it can avoid tracking values across rows/cols in code
• cell merge
• setting column width

For testing I’ll create a excel file programmatically, trying to use these features..

What did I get ?

I could get my needs fulfilled with some minor struggling. The output file is xml, schema seems similar to the one described in:
http://en.wikipedia.org/wiki/Microsoft_Office_2003_XML_formats.

This is the output in excel with no modifications:

How did I do it?

This is the code I used. The chosen methods may not be optimal but it does indicate the API you could end up using..

using System;
using System.Globalization;
using CarlosAg.ExcelXmlWriter;


public class CarlosAGTest
{
    private const int DummyRows = 15;
    private const string HeaderStyleName = "headerStyle";
    private const string DateStyleName = "dateStyle";

    public static void Build()
    {
        //prepare sheet
        Workbook book = new Workbook();
        Worksheet sheet = book.Worksheets.Add("Sample");

        //delcare header style
        WorksheetStyle style = book.Styles.Add(HeaderStyleName);
        style.Font.Bold = true;

        WorksheetStyle dateStyle = book.Styles.Add(DateStyleName);
        dateStyle.NumberFormat = "Short Date";

        //create data                
        sheet.Table.Rows.Add(BuildMergedRow());
        sheet.Table.Rows.Add(BuildHeaderRow());
        for(int i = 1; i <= DummyRows; i++)
        {
            sheet.Table.Rows.Add(BuildDataRow(i));
        }
        sheet.Table.Rows.Add(BuildSummaryRow());

        //set column width:
        sheet.Table.Columns.Add().Width = 5;
        sheet.Table.Columns.Add();
        sheet.Table.Columns.Add().Width = 150;

        //output result
        book.Save("CarlosAG_result.xls");
    }

    private static WorksheetRow BuildMergedRow()
    {
        WorksheetRow row = new WorksheetRow();
        var lastCell = row.Cells.Add("demonstrate merge");
        lastCell.MergeAcross = 3;
        return row;
    }

    private static WorksheetRow BuildHeaderRow()
    {
        WorksheetRow row = new WorksheetRow();
        //demonstrate overloads of adding cell contents.
        row.Cells.Add(new WorksheetCell("index") { StyleID = HeaderStyleName });
        row.Cells.Add("(1/index)", DataType.String, HeaderStyleName);
        row.Cells.Add(new WorksheetCell("name", DataType.String, HeaderStyleName));
        row.Cells.Add("dateTime", DataType.String, HeaderStyleName);
        return row;
    }

    private static WorksheetRow BuildDataRow(int rowIndex)
    {
        //generate dummy data row using rowIndex for values:
        WorksheetRow row = new WorksheetRow();
        row.Cells.Add(rowIndex.ToString(CultureInfo.InvariantCulture), DataType.Number, null);
        row.Cells.Add((1.0M/rowIndex).ToString(CultureInfo.InvariantCulture), DataType.Number, null);
        row.Cells.Add("Ilus õõvaöö nr " + rowIndex.ToString(CultureInfo.InvariantCulture), DataType.String, null);
        row.Cells.Add(DateTime.Today.AddDays(rowIndex).ToString("s"), DataType.DateTime, DateStyleName);
        return row;
    }

    private static WorksheetRow BuildSummaryRow()
    {
        WorksheetRow row = new WorksheetRow();
        var averageCell = row.Cells.Add();
        averageCell.Formula = "=AVERAGE(R[-" + DummyRows + "]C:R[-1]C)";

        var sumcell = row.Cells.Add();
        sumcell.Formula = "=SUM(R[-" + DummyRows + "]C:R[-1]C)";

        row.Cells.Add();

        var plusOneCell = row.Cells.Add(null, DataType.DateTime, DateStyleName);
        plusOneCell.Formula = "= R[-1]C + 1";
        return row;
    }
}

A few subjective numbers:

• The file was generated with approx 40-50 ms on E8300 @2.83GHz, 4GB RAM.
• 88 lines of code (including empty lines, using-statements, etc).

What do I think ?

If you are low budget then CarlosAG is certainly a good tool to use. Far from ideal, though.

The good:

• It did everything I wanted
• Free. no cost, use as you want.
• Fully managed code, requires just .net FW 1.1
• Simple deplopyment  - just a single ~100k dll.
• Reasonably simple API.

The bad:

• no source code
• just the one from reflector
• no documentation
• no xmldoc file for dll, chm in website was broken and failed to show a single page of it.
• no automatic value formatting
• cell values have to be set as strings, the of API user has to manually format contents to be saved into XML. What format is expected is not obvious.For example: Datetimes, decimal separators, etc. This could lead to hard to debug problems.. and it did.
• Probable lack of support
• Developer seems to have made this library for fun, this could mean loss of quality and support. For example, last realease was in 2005. It is foreseeable that problems have to be solved by yourself, digging knee-deep into reflector-generated code. For code written in .net FW 1.1, this could be ugly.

• API lacks some comfort-features

• You have to create lots of rows and cells and columns. To add something to n-th row you have to ensure all rows up andincluding n-th row are created. same for cells, and columns.

• No native xls/xlsx output.

• just xml with <?mso-application progid='Excel.Sheet'?>

• The formula input language in unknown to me
• I'd prefer to write exactly what I would write in excel. No doubt the given langugage could be more helpful but it adds to the learning curve. For example I’m using "=SUM(R[-30] C:R[-1]C)" instead of “=SUM(A1:A30)”.

Links

Official site:
http://www.carlosag.net/Tools/ExcelXmlWriter/

Sample for exporting dataset contents:
http://www.sitepoint.com/blogs/2006/08/22/making-excel-the-carlosag-way/