Saveen Reddy's blog

C# Code Sample: Creating an Excel 2007 XML File from a DataTable object

Recently I’ve been playing with System.Data.DataTable and needed to generate an Excel Spreadsheet from from records stored in a DataTable (CSV format wasn’t sufficient for my needs). The sample below uses the ExcelXmlWriter library from Carlos Aguilar Mares which makes this very straightforward.

The ExportToExcelXml method creates the Xml File.

The ExportToExcel method will create a temporary file and launch Excel to open the XML file.

//using System;
//using System.Collections.Generic;
//using System.Data;
//using System.Linq;

public void ExportToExcelXML(DataTable data, string filename)
{
    var dic = new Dictionary<System.Type, CarlosAg.ExcelXmlWriter.DataType>
                  {
                      {typeof (int), CarlosAg.ExcelXmlWriter.DataType.Number},
                      {typeof (string), CarlosAg.ExcelXmlWriter.DataType.String},
                      {typeof (System.DateTime), CarlosAg.ExcelXmlWriter.DataType.DateTime},
                      {typeof (System.Double), CarlosAg.ExcelXmlWriter.DataType.Number},
                      {typeof (float), CarlosAg.ExcelXmlWriter.DataType.Number},
                      {typeof (decimal), CarlosAg.ExcelXmlWriter.DataType.Number}
                  };

    var celltypes = new List<CarlosAg.ExcelXmlWriter.DataType>();
    var wb = new CarlosAg.ExcelXmlWriter.Workbook();
    var ws = wb.Worksheets.Add("Sheet1");
    foreach (System.Data.DataColumn col in data.Columns)
    {
        var c = new CarlosAg.ExcelXmlWriter.WorksheetColumn();
        ws.Table.Columns.Add(c);
        if (dic.ContainsKey(col.DataType))
        {
            celltypes.Add(dic[col.DataType]);
        }
        else
        {
            celltypes.Add(CarlosAg.ExcelXmlWriter.DataType.String);
        }
    }

    foreach (System.Data.DataRow row in data.Rows)
    {
        var nrow = new CarlosAg.ExcelXmlWriter.WorksheetRow();

        int i = 0;
        foreach (var item in row.ItemArray)
        {
            var cell = nrow.Cells.Add(item.ToString());
            cell.Data.Type = celltypes[i];
            i++;
        }

        ws.Table.Rows.Add(nrow);
    }

    wb.Save(filename);
}

public void ExportToExcel(DataTable data)
{
    string temp_xml_fname = System.IO.Path.Combine(System.IO.Path.GetTempPath(), "temp.xml");
    this.ExportToExcelXML(data, temp_xml_fname);

    System.Diagnostics.Process.Start(temp_xml_fname);
}