Thursday, September 8, 2011

C# - Excel Workbook Wrapper

This is a nice little wrapper class around the Worksheet COM automation interface in Excel.  You'll need the Excel Office extensions installed.  I use it to open and modify excel worksheets that I've tagged with specific values in certain cells. It's by no means complete, and is a great starting point for building out your own Excel workbook utility class. It makes light work of modifying excel workbooks.
 public class ExcelWorkbook : IDisposable  
 {  
   private Application _app = null;  
   private Workbook _workbook = null;  
   private string _filename = null;  
   public Worksheet ActiveWorksheet  
   {  
     get { return _workbook.ActiveSheet as Worksheet; }  
   }  
   public ExcelWorkbook(string filename)  
   {  
     _app = new Application();  
     _app.Visible = false;  
     _filename = filename;  
     _workbook = _app.Workbooks.Open(filename);  
   }  
   public Range FindItem(string tag)  
   {  
     return FindItem(tag, true);  
   }  
   public void DeleteRow(Range range)  
   {  
     range.EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp);  
   }  
   public void DeleteColumn(Range range)  
   {  
     range.EntireColumn.Delete(XlDeleteShiftDirection.xlShiftToLeft);  
   }  
   public Range FindItem(string tag)  
   {  
     object missing = Type.Missing;  
     Range range =  
       ActiveWorksheet.Cells.Find(  
       tag, ActiveWorksheet.Cells[1, 1],  
       Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues,  
       Microsoft.Office.Interop.Excel.XlLookAt.xlPart,  
       missing,  
       Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext,  
       false, missing, missing);  
     return range;  
   }  
   public void Save()  
   {  
     _workbook.Save();  
   }  
   public void SaveAs(string filename)  
   {  
     _workbook.SaveAs(filename);  
   }  
   #region IDisposable Members  
   public void Dispose()  
   {  
     // nothing to do if there's no app object  
     if (_app == null) return;  
     // close app and wait for finalization  
     _app.Quit();  
     _app = null;  
     GC.Collect();  
     GC.WaitForPendingFinalizers();  
     GC.Collect();  
     GC.WaitForPendingFinalizers();  
   }  
   #endregion  
 }  

No comments: