I wrote a little VBA the other day to save an Excel Worksheet as an ADODB.Recordset XML file. I decided to see how hard it would be to do the exact same thing in C#. The idea is that I might start using C# Script instead of JavaScript for little utilities in the future.

using ADODB;
using Excel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Reflection;
using System.Runtime.InteropServices;

namespace ExcelUtility
{
    public class ExcelExport
    {
        public const string EXCEL_PROG_ID = "Excel.Application";

        public static Excel.Application ExcelApplication
        {
            get
            {
                Excel.Application app = null;

                try
                {
                    // http://msmvps.com/blogs/pauldomag/archive/2006/03/15/86417.aspx
                    app = Marshal.GetActiveObject(EXCEL_PROG_ID) as Excel.Application;
                }
                catch (Exception ex)
                {
                    System.Diagnostics.Trace.WriteLine(ex.ToString());
                }

                if (app == null)
                {
                    try
                    {
                        app = new Excel.ApplicationClass();
                    }
                    catch (Exception ex)
                    {
                        System.Diagnostics.Trace.WriteLine(ex.ToString());
                    }
                }

                return app;
            }
        }

        public static System.Data.DataTable SaveWorksheetAsDataTable()
        {
            Excel.Application app = ExcelApplication;
            Excel.Workbook book = app.ActiveWorkbook as Excel.Workbook;
            Excel.Worksheet sheet = app.ActiveSheet as Excel.Worksheet;
            int col, colcount;
            int row, rowcount;

            if (sheet == null)
                return null;

            // Get the current sheet range
            Excel.Range range = sheet.Cells;
            colcount = sheet.UsedRange.Columns.Count;
            rowcount = sheet.UsedRange.Rows.Count;

            // Get the header row
            row = 1;
            System.Data.DataTable dt = new System.Data.DataTable(sheet.Name);
            for (col = 1; col <= colcount; ++col)
            {
                string text = (string)(range[row, col] as Excel.Range).Text;
                dt.Columns.Add(text, typeof(string));
            }

            // Get all of the data
            for (row = 2; row <= rowcount; ++row)
            {
                DataRow dr = dt.NewRow();
                for (col = 0; col < colcount; ++col)
                {
                    string text = (string)(range[row, col + 1] as Excel.Range).Text;
                    if (text.Length > 0)
                        dr[col] = text;
                }
                dt.Rows.Add(dr);
            }

            // Save the data
            dt.WriteXml(book.Path + "\\" + sheet.Name + ".xml");

            // Clean up
            app = null;
            return dt;
        }

        public static ADODB.Recordset SaveWorksheetAsRecordset()
        {
            Excel.Application app = ExcelApplication;
            Excel.Worksheet sheet = app.ActiveSheet as Excel.Worksheet;
            int col, colcount;
            int row, rowcount;

            if (sheet == null)
                return null;

            // Get the current sheet range
            Excel.Range range = sheet.Cells;
            colcount = sheet.UsedRange.Columns.Count;
            rowcount = sheet.UsedRange.Rows.Count;

            // Get the header row
            row = 1;
            ADODB.Recordset rst = new ADODB.RecordsetClass();
            for (col = 1; col <= colcount; ++col)
            {
                string text = (string)(range[row, col] as Excel.Range).Text;
                rst.Fields.Append(text, ADODB.DataTypeEnum.adVarChar, 255, ADODB.FieldAttributeEnum.adFldMayBeNull, null);
            }
            rst.Open(null, null, CursorTypeEnum.adOpenUnspecified, LockTypeEnum.adLockUnspecified, -1);

            ADODB.Field[] fields = new ADODB.Field[colcount];
            for (col = 0; col < colcount; ++col)
                fields[col] = rst.Fields[col];

            // Get all of the data
            for (row = 2; row <= rowcount; ++row)
            {
                rst.AddNew(null, null);
                for (col = 0; col < colcount; ++col)
                {
                    string text = (string)(range[row, col + 1] as Excel.Range).Text;
                    if (text.Length > 0)
                        fields[col].Value = text;
                }
                rst.Update(null, null);
            }

            // Save the data
            string filename = book.Path + "\\" + sheet.Name + ".xml";
            rst.Save(filename, PersistFormatEnum.adPersistXML);

            // Clean up
            app = null;
            return dt;
        }
    }
}