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;
}
}
}
Comments are closed.