Using ADO.Net and Com Interop to read, modify and output from Excel

Today I was asked to do what seemed like a fairly simple task:

Take an Excel 2003 file, add a couple of columns of data and save to a new Excel file programattically.

I haven’t done any coding Excel for AGES so this i what I did.  To read in the file and add the extra columm and data I did the following:

• Used OleDBConnection to grab the data from the sheet into a dataset
• Added some columns to the dataset
• did some processing and set the data in the dataset

with the following code:

private void loadExcelData()
{
    string filename = "c:\temp\myexcelfile.xls";
    string connectionString =
        string.Format(
        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};
    Extended Properties=\"Excel 8.0;HDR=NO\"",
        filename);

    OleDbConnection connection = new OleDbConnection(connectionString);
    string command = "SELECT * from [Sheet1$]";
    OleDbDataAdapter da = new OleDbDataAdapter(command, connection);
    DataSet ds = new DataSet();
    da.Fill(ds);

    if (ds.Tables.Count > 0)
    {
        // add new columna and do some processing
        ds.Tables[0].Columns.Add("MyNewColumn", typeof (string));
        setNewColumnData(ds.Tables[0]);

        createNewWorkSheet(ds.Tables[0]);
    }
}

I couldn’t find an easy way to then save that dataset to a new Excel file using ADO. I tried writing an xls file as xml but that added headers. I needed to keep the file the same…just extra columns. So then I turned to interop.

• Created a workbook
• created a sheet
• replaced the cells with the data in the dataset
• Saved the sheet to the new file

with the following code:

using Microsoft.Office.Interop.Excel;
using Application=Microsoft.Office.Interop.Excel.Application;

private void createNewWorkSheet(DataTable email)
{
    Application excelApp = new Application();
    Workbook book;
    Worksheet sheet;
    string file = saveFileTextBox.Text;

    excelApp.Visible = false;
    excelApp.DisplayAlerts = false;

    excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);

    book = excelApp.Workbooks[1];
    sheet = book.Worksheets[1] as Worksheet;
    if (sheet != null)
    {
        Range cells = sheet.Cells;

        dumpData(email, cells);

        book.Save();
        sheet.SaveAs(file, XlFileFormat.xlXMLSpreadsheet, null,
            null, null, null, null, null, null, null);
        book.Close(null, null, null);
    }
    excelApp.Quit();
}

private void dumpData(DataTable email, Range cells)
{
    DataRow dr;
    Object[] dataColumns;

    for (int row = 0; row < email.Rows.Count; row++)
    {
        dr = email.Rows[row];
        dataColumns = dr.ItemArray;

        for (int column = 0; column < dataColumns.Length; column++)
        {
            cells[row + 1, column + 1] = dataColumns[column].ToString();
            progressBar.PerformStep();
        }
    }
}

Not sure if this the best way to do it bust seemed to work.