Wiring Data Into ExcelXmlWriter

I’ve been checking out Carlos Mares’ killer tool ExcelXmlWriter.  His library lets you create classes to write out flexible, complex Excel files in Office’s XML format. (Office XP and above, only.)  It’s a great tool, but it took me a little while to understand how to go about throwing data into the classes you build. 

I thought there might be some way to pass XML from a DataSet into Mares’ API, but it doesn’t work that way.  You need to iterate through rows in a DataTable (or a table of a DataSet. Duh.) and create individual cells from that.  Below is an implementation pulling data from three tables in the ubiquitous Northwind database.

First, load up a DataTable:

public System.Data.DataTable GetAllEmployeesSalesHistory()

{

    string sel = @"select employees.lastname, orders.orderdate, " +

        "[order details].unitprice, [order details].quantity, " +

        "[order details].discount from employees, orders, [order details]" +

        "where [order details].orderid = orders.orderid AND " +

        "employees.employeeid = orders.employeeid "+

        "order by employees.lastname";

 

    DataTable history = new DataTable();

 

    try

    {

        SqlCommand cmd = new SqlCommand();

        SqlDataAdapter adapter = new SqlDataAdapter(sel, conn);

        adapter.Fill(history);

    }

    catch (SqlException e)

    {

        System.Console.WriteLine("Error: " + e.Message);

    }

 

    return history;

}

Now use that to load data into an existing sheet of a workbook. This example assumes you've created a sheet named "History."  Note I’m adding in a column containing a formula for figuring out the gains of the sale — ExcelXmlWriter supports all kinds of Excel shininess like formulas, pivot tables, sorting, filters, etc.

private void LoadData(Workbook book, DataTable data)

{

    //reps is a private member ArrayList holding sales reps' last names

    reps = new ArrayList();

    WorksheetRow sheetRow;

    WorksheetCell cell;

 

    foreach (DataRow tableRow in data.Rows)

    {

        //  create a new row

        sheetRow = book.Worksheets["History"].Table.Rows.Add();

 

        //last name

        cell = sheetRow.Cells.Add();

        cell.Data.Type = DataType.String;

        cell.Data.Text = tableRow[0].ToString();

 

        //add rep names in if they're not already

        if (! reps.Contains(tableRow[0]))

        {

            reps.Add(tableRow[0]);

        }

 

        //date of sale

        cell = sheetRow.Cells.Add();

        //Note this is type *string*.  DateTime type causes load errors in

        // Excel.  The column's DateTime type so everything out ducky.

        cell.Data.Type = DataType.String;

        DateTime date = Convert.ToDateTime(tableRow[1].ToString());

        cell.Data.Text = date.ToShortDateString();

 

        //unit price

        cell = sheetRow.Cells.Add();

        cell.Data.Type = DataType.Number;

        cell.Data.Text = tableRow[2].ToString();

 

        //quantity

        cell = sheetRow.Cells.Add();

        cell.Data.Type = DataType.Number;

        cell.Data.Text = tableRow[3].ToString();

 

        //discount

        cell = sheetRow.Cells.Add();

        cell.Data.Type = DataType.Number;

        cell.Data.Text = tableRow[4].ToString();

 

        //Net sales -- calculated

        //  Unit cost less any discount times quantity sold

        cell = sheetRow.Cells.Add();

        //Note R1C1 cell reference type!

        cell.Formula =

            "=IF(RC[-1] > 0,RC[-3] * ((1 - RC[-1])) * RC[-2], RC[-3]*RC[-2])";

    }

}

I also did a bit of fancy but not too complex trickery to create a summary of sales reports:

private void GenerateWorksheetSheet1(WorksheetCollection sheets)

{

    Worksheet sheet = sheets.Add("History");

 

    WorksheetRow headerRow = sheet.Table.Rows.Add();

    headerRow.Cells.Add("Sales Rep", DataType.String, "headerStyle");

    headerRow.Cells.Add("Order Date", DataType.String, "headerStyle");

    headerRow.Cells.Add("Unit Price", DataType.String, "headerStyle");

    headerRow.Cells.Add("Quantity", DataType.String, "headerStyle");

    headerRow.Cells.Add("Discount", DataType.String, "headerStyle");

    headerRow.Cells.Add("Net", DataType.String, "headerStyle");

 

    WorksheetColumn salesRep = sheet.Table.Columns.Add();

    salesRep.Width = 70;

    salesRep.StyleID = "defaultStyle";

 

    WorksheetColumn date = sheet.Table.Columns.Add();

    date.Width = 66;

    date.StyleID = "dateStyle";

 

    WorksheetColumn unitPrice = sheet.Table.Columns.Add();

    unitPrice.Width = 57;

    unitPrice.StyleID = "currencyStyle";

 

    WorksheetColumn quantity = sheet.Table.Columns.Add();

    quantity.StyleID = "quantityStyle";

 

    WorksheetColumn discount = sheet.Table.Columns.Add();

    discount.StyleID = "discountStyle";

 

    WorksheetColumn net = sheet.Table.Columns.Add();

    net.StyleID = "currencyStyle";

}

The result looks something like this

Pretty cool, no?  Mares has a great tool to help dealing with the non-trivial API: ExcelXmlGenerator, which reverse-engineers an existing Excel file.  So that makes the easy route to first create a spreadsheet like you want it to look, including formulae, styling, and some mock data.  Then run the Generator against it and Poof! you’ve got complete code as a starting point.

You’ll need to do some code clean up for simple readability, plus delete all the hardwired mock data you tossed in.

It took me some time to figure out how to deal with the different DataType values as they interract with formats of data coming out of the DataTable I used as a source.  Things went quickly once I finally got my head wrapped around that.

Now Playing: Morrissey — Your Arsenal.  Love this guy’s voice, love the guitars.