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

posted in: Uncategorized | 0

BronwenWeeGo.jpgToday 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 =
        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};
    Extended Properties=\"Excel 8.0;HDR=NO\"",

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

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


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;


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

        dumpData(email, cells);

        sheet.SaveAs(file, XlFileFormat.xlXMLSpreadsheet, null,
            null, null, null, null, null, null, null);
        book.Close(null, null, null);

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();

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

kick it on