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.