C# Utility to export Excel Worksheet As ADO Recordset XML

<< Export Excel Worksheet to ADO Recordset | Home | Still trying to syntax highlight in Blog >>

C# Utility to export Excel Worksheet As ADO Recordset XML

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 System;
using System.Collections.Generic;
using System.Data;
using System.Reflection;
using System.Runtime.InteropServices;

using ADODB;
using Excel;

namespace ExcelUtility
{
	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;
		}
	}
}

Print | posted on Friday, January 19, 2007 7:52 AM | Filed Under [ C# Excel ]

Feedback

# The profile is unchanged from the all-too-familiar JS Wings model <a href="http://www.adidaswingshoes.com/"><strong>Jeremy Scott

The profile is unchanged from the all-too-familiar JS Wings model Jeremy Scott Adidas
2/19/2012 8:33 PM | Adidas Wing Shoes

Post Comment

Title

Name

Email

Url

Comment

Remember Me?

Please add 5 and 6 and type the answer here:
Enter the code shown above:

Nimble Coder

My Links

Tag Cloud

Archives

Post Categories

Blog roll

C# Utility to export Excel Worksheet As ADO Recordset XML

Feedback

# The profile is unchanged from the all-too-familiar JS Wings model <a href="http://www.adidaswingshoes.com/"><strong>Jeremy Scott

Post Comment