A C# class to control Excel – A core class used in TWE 2.0
Introduction
This class was developed from the basic framework Vahe Karamian created in 2005. It is a complete solution to control Microsoft Excel from C#.Net. It allows you to create an Excel object and control a lot of the Excel functionalities such as getting worksheet information, extracting data from the worksheet given a range, and manipulating data in many ways. You can use this class freely in your C# project. If you find and fix bugs please write comments to this post. Read Karamian’s original article to find out how to use the code. You can download all source code in a zip file through the link at the end of the post.
Source code
using System;
using System.IO;
using System.Collections;
using System.Threading;
using Excel;
using System.Diagnostics;
namespace TWE
{
/// <summary>
/// Project: A complete set of gadgets to control excel
/// Author: Vahe Karamian, Zhanshan Dong
/// Date: 01/16/2006
/// Modified: 09/13/2009
/// Version: 0.5
/// Description: To create a class that includes most useful methods to handle Excel
/// </summary>
public class ZDExcel
{
private const int columnLimit = 255;
private const int rowLimit = 65536;
private Excel.Application xlApp = null;
private Excel.Workbooks xlWorkbooks = null;
private Excel.Workbook xlWorkbook = null;
private Excel.Sheets xlSheets = null;
private Excel.Worksheet xlWorksheet = null;
private static object zd_missing = System.Reflection.Missing.Value;
private static object zd_visible = true;
private static object zd_false = false;
private static object zd_true = true;
private bool zd_app_visible = false;
private bool zd_app_running = false;
private object zd_filename;
#region OPEN WORKBOOK VARIABLES
private object zd_update_links = 0;
private object zd_read_only = zd_true;
private object zd_format = 1;
private object zd_password = zd_missing;
private object zd_write_res_password = zd_missing;
private object zd_ignore_read_only_recommend = zd_true;
private object zd_origin = zd_missing;
private object zd_delimiter = zd_missing;
private object zd_editable = zd_false;
private object zd_notify = zd_false;
private object zd_converter = zd_missing;
private object zd_add_to_mru = zd_false;
private object zd_local = zd_false;
private object zd_corrupt_load = zd_false;
#endregion
#region CLOSE WORKBOOK VARIABLES
private object zd_save_changes = zd_false;
private object zd_route_workbook = zd_false;
#endregion
/// <summary>
/// Vahe Karamian - 03/04/2005 - Excel Object Constructor.
/// </summary>
public ZDExcel() {}
/// <summary>
/// Vahe Karamian - 03/04/2005 - Excel Object Constructor
/// visible is a parameter, either TRUE or FALSE, of type object.
/// </summary>
/// <param name="visible">Visible parameter, true for visible, false for non-visible</param>
public ZDExcel(bool visible)
{
this.zd_app_visible = visible;
}
public int ColumnLimit
{
get
{
return columnLimit;
}
}
public int RowLimit
{
get
{
return rowLimit;
}
}
/// <summary>
/// Zhanshan Dong - 01/17/2006 - Return the Excel.application object
/// </summary>
#region EXCEL APPLICATION
public Excel.Application xlApplication
{
get
{
return xlApp;
}
}
#endregion
/// <summary>
/// Vahe Karamian - 03/04/2005 - Start Excel Application
/// Zhanshan Dong - 01/16/2006 - To avoid open more than one Excel window,
/// the method first search for any running Excel window
/// </summary>
#region START EXCEL
public void startExcel()
{
this.zd_app_running = false;
try
{
if (System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application") != null)
{
xlApp = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
this.zd_app_running = true;
// return "Old";
}
}
catch (Exception e)
{
//return e.Message;
}
finally
{
if (this.xlApp == null)
{
this.xlApp = new Excel.ApplicationClass();
}
}
// Make Excel Visible
this.xlApp.Visible = zd_app_visible;
//return "New";
}
#endregion
/// <summary>
/// Vahe Karamian - 03/23/2005 - Kill the current Excel Process
/// Zhanshan Dong - 01/16/2006 - Modify the declaration of the method
/// to assure the Excel windows closed completely
/// </summary>
#region STOP EXCEL
public void stopExcel()
{
if( (this.xlApp != null) && (!this.zd_app_running) )
{
bool Changed = false;
for (int i=0; i<this.xlApp.Workbooks.Count; i++)
{
Workbook mybook = this.xlApp.Workbooks.get_Item((object)(i+1));
if (! mybook.Saved)
{
Changed = true;
}
}
if (! Changed)
{
this.xlApp.Quit(); // this statement will make sure to close Excel window
Process[] pProcess;
pProcess = System.Diagnostics.Process.GetProcessesByName("Excel");
pProcess[0].Kill();
}
}
}
#endregion
/// <summary>
/// Vahe Karamian - 03/09/2005 - Open File function for Excel 2003
/// The following function will take in a filename, and a password
/// associated, if needed, to open the file.
/// </summary>
/// <param name="fileName"></param>
/// <param name="password"></param>
#region OPEN EXCEL FILE
public string OpenFile(string fileName, string password)
{
zd_filename = fileName;
if( password.Length > 0 )
{
zd_password = password;
}
try
{
// Open a workbook in Excel
this.xlWorkbook = this.xlApp.Workbooks.Open(
fileName, zd_update_links, zd_read_only, zd_format, zd_password,
zd_write_res_password, zd_ignore_read_only_recommend, zd_origin,
zd_delimiter, zd_editable, zd_notify, zd_converter, zd_add_to_mru);
/*
this.xlWorkbook = this.xlApp.Workbooks.Open(
fileName, zd_update_links, zd_read_only, zd_format, zd_password,
zd_write_res_password, zd_ignore_read_only_recommend, zd_origin,
zd_delimiter, zd_editable, zd_notify, zd_converter, zd_add_to_mru,
zd_local, zd_corrupt_load);
*/
}
catch(Exception e)
{
this.CloseFile();
return e.Message;
}
return "OK";
}
#endregion
/// <summary>
/// Vahe Karamian - 03/09/2005 - Close Excel file
/// </summary>
#region CLOSE EXCEL FILE
public void CloseFile()
{
xlWorkbook.Close( zd_save_changes, zd_filename, zd_route_workbook );
}
#endregion
/// <summary>
/// Zhanshan Dong - 01/16/2006 - Get Excel Workbooks
/// Get the collection of workbooks in the running Excel instance
/// </summary>
#region GET EXCEL WORKBOOKS
public void GetBooks()
{
if( this.xlApp != null )
{
xlWorkbooks = xlApp.Workbooks;
}
}
#endregion
/// <summary>
/// Zhanshan Dong - 01/16/2006 - Get an list of Excel workbook names
/// </summary>
#region GET A WORKBOOK LIST
public string[] WorkbookList()
{
string [] arrayS = new string[xlWorkbooks.Count];
if( this.xlWorkbooks != null )
{
for (int i =0; i<xlWorkbooks.Count; i++)
{
object j = i+1;
Excel.Workbook mybook = ((Excel.Workbook)xlWorkbooks.get_Item(j));
arrayS[i] = mybook.Name;
}
}
return arrayS;
}
#endregion
/// <summary>
/// Zhanshan Dong - 01/16/2006 - Return a workbook object
/// </summary>
#region GET A WORKBOOK
public Excel.Workbook GetABook(int Index)
{
object myIndex = new object();
myIndex = (object)Index;
// return ((Excel.Workbook)xlApp.Workbooks.get_Item((object)Index));
return ((Excel.Workbook)xlApp.Workbooks.get_Item(myIndex));
}
#endregion
/// <summary>
/// Zhanshan Dong - 01/17/2006 - Find a workbook and return it if found
/// </summary>
/// <returns>Excel.Workbook</returns>
#region FIND A WORKBOOK
public Excel.Workbook FindWorkbook(string workbookName)
{
Excel.Workbook ABook = null;
if( this.xlWorkbooks != null )
{
// Step thru the workbook collection and see if a sheet is available
for( int i=1; i<=this.xlWorkbooks.Count; i++ )
{
ABook = (Excel.Workbook)xlWorkbooks.get_Item((object)i);
if( ABook.Name.Equals(workbookName) )
{
return ABook;
}
}
}
return null;
}
#endregion
/// <summary>
/// Vahe Karamian - 03/20/2005 - Get Excel Sheets
/// Get the collection of sheets in the workbook
/// </summary>
#region GET EXCEL SHEETS
public void GetSheets()
{
if( this.xlWorkbook != null )
{
xlSheets = xlWorkbook.Worksheets;
}
}
#endregion
/// <summary>
/// Zhanshan Dong - 01/16/2006 - Get a list of Excel worksheet name
/// </summary>
#region GET A WORKSHEET LIST
public string[] SheetList()
{
string [] arrayS = new string[xlSheets.Count];
if( this.xlSheets != null )
{
for (int i=0; i<xlSheets.Count; i++)
{
Excel.Worksheet mysheet = ((Excel.Worksheet)xlSheets.get_Item((object)(i+1)));
arrayS[i] = mysheet.Name;
}
}
return arrayS;
}
#endregion
/// <summary>
/// Zhanshan Dong - 01/16/2006 - Return a worksheet object
/// </summary>
#region GET A WORKSHEET
public Excel.Worksheet GetASheet(int Index)
{
return ((Excel.Worksheet)xlSheets.get_Item((object)Index));
}
#endregion
/// <summary>
/// Vahe Karamian - 03/21/2005 - Find a worksheet, return TRUE if found
/// </summary>
/// <returns>bool</returns>
#region ACTIVATE A WORKSHEET
public bool ActivateWorksheet(string worksheetName)
{
bool SHEET_FOUND = false;
if( this.xlSheets != null )
{
// Step thru the worksheet collection and see if ATP sheet is
// available. If found return true;
for( int i=1; i<=this.xlSheets.Count; i++ )
{
this.xlWorksheet = (Excel.Worksheet)xlSheets.get_Item((object)i);
if( this.xlWorksheet.Name.Equals(worksheetName) )
{
this.xlWorksheet.Activate();
SHEET_FOUND = true;
return SHEET_FOUND;
}
}
}
return SHEET_FOUND;
}
#endregion
/// <summary>
/// Zhanshan Dong - 01/17/2006 - Find a worksheet and return it if found
/// </summary>
/// <returns>Excel.Worksheet</returns>
#region FIND A WORKSHEET
public Excel.Worksheet FindWorksheet(string worksheetName)
{
Excel.Worksheet ASheet = null;
if( this.xlSheets != null )
{
// Step thru the worksheet collection and see if a sheet is available
for( int i=1; i<=this.xlSheets.Count; i++ )
{
ASheet = (Excel.Worksheet)xlSheets.get_Item((object)i);
if( ASheet.Name.Equals(worksheetName) )
{
return ASheet;
}
}
}
return null;
}
#endregion
/// <summary>
/// Vahe Karamian - 03/22/2005 - Get Range from Worksheet
/// Return content of range from the selected range
/// </summary>
/// <param name="range">Range parameter: Example, GetRange("A1:D10")</param>
#region GET RANGE
public string[] GetRange(string range)
{
Excel.Range workingRangeCells = xlWorksheet.get_Range(range,Type.Missing);
//workingRangeCells.Select();
System.Array array = (System.Array)workingRangeCells.Cells.Value2;
string[] arrayS = this.ConvertToStringArray(array);
return arrayS;
}
#endregion
/// <summary>
/// Vahe Karamian - 03/22/2005 - Convert To String Array
/// Convert System.Array into string[]
/// </summary>
/// <param name="values">Values from range object</param>
/// <returns>String[]</returns>
#region CONVERT TO STRING ARRAY
private string[] ConvertToStringArray(System.Array values)
{
string[] newArray = new string[values.Length];
int index = 0;
for ( int i = values.GetLowerBound(0); i <= values.GetUpperBound(0); i++ )
{
for ( int j = values.GetLowerBound(1); j <= values.GetUpperBound(1); j++ )
{
if(values.GetValue(i,j)==null)
{
newArray[index]="";
}
else
{
newArray[index]=(string)values.GetValue(i,j).ToString();
}
index++;
}
}
return newArray;
}
#endregion
/// <summary>
/// Zhanshan Dong - 01/20/2006 - Get the dimension of the used range in a given sheet
/// </summary>
/// <param name="mySheet">A given worksheet</param>
/// <returns>int []</returns>
#region GET THE DIMENSION OF USED RAGNE
public int[] GetUsedRangeDim(Excel.Worksheet mySheet)
{
int [] myDim = new int[4];
// myDim[0] =(mySheet.UsedRange.Cells.Row>1)?
// mySheet.UsedRange.Cells.Row-1:mySheet.UsedRange.Cells.Row;
// myDim[1] = (mySheet.UsedRange.Cells.Column>1)?
// mySheet.UsedRange.Cells.Column-1:mySheet.UsedRange.Cells.Column;
myDim[0] = mySheet.UsedRange.Cells.Row;
myDim[1] = mySheet.UsedRange.Cells.Column;
myDim[2] = mySheet.UsedRange.Rows.Count;
myDim[3] = mySheet.UsedRange.Columns.Count;
return myDim;
}
#endregion
/// <summary>
/// Zhanshan Dong - 04/15/2006 - Get the dimension of a selected range in a given sheet
/// </summary>
/// <param name="mySheet">A given worksheet</param>
/// <returns>int []</returns>
#region GET THE DIMENSION OF THE SELECTED RANGE
public int[] GetSelectedRangeDim(Excel.Worksheet mySheet)
{
Excel.Worksheet tWksht = (Excel.Worksheet)xlApplication.ActiveSheet;
mySheet.Activate();
Excel.Range mySelection = (Excel.Range)mySheet.Application.ActiveWindow.RangeSelection;
int [] myDim = new int[4];
int [] myUsedDim = new int[4];
myUsedDim = GetUsedRangeDim(mySheet);
int lastRow = (myUsedDim[0]+myUsedDim[2]-1);
int lastColumn = (myUsedDim[1]+myUsedDim[3]-1);
myDim[0] = mySelection.Cells.Row;
myDim[1] = mySelection.Cells.Column;
myDim[2] = mySelection.Rows.Count;
myDim[3] = mySelection.Columns.Count;
tWksht.Activate();
// use used range dimension to check the boundaray of selected range
// check if the selected cells is not in the used range
// used range can be like D10:J20
if (myDim[0]<myUsedDim[0])
{
if (myDim[0]+myDim[2]-1>myUsedDim[0])
{
myDim[0] = myUsedDim[0];
}
else
{
myDim[0] = -1;
}
}
if (myDim[0]>lastRow)
{
myDim[0] = -1;
}
if (myDim[1]<myUsedDim[1])
{
if (myDim[1]+myDim[3]-1>myUsedDim[1])
{
myDim[1] = myUsedDim[1];
}
else
{
myDim[1] = -1;
}
}
if (myDim[1]>lastColumn)
{
myDim[1] = -1;
}
// if selected number of rows is greater than the possible rows of the used range
// let the number of selected rows equals to the number of rows in the used range
if ((lastRow - myDim[0] + 1>0) && (lastRow - myDim[0] + 1 < mySelection.Rows.Count))
{
myDim[2] = lastRow - myDim[0] + 1;
}
// if selected number of columns is greater than the possible columns of the used range
// let the number of selected columns equals to the number of columns in the used range
if ((lastColumn - myDim[1]+1>0)&&(lastColumn - myDim[1]+1 < mySelection.Columns.Count))
{
myDim[3] = lastColumn - myDim[1]+1;
}
return myDim;
}
#endregion
/// <summary>
/// Zhanshan Dong - 04/17/2006 - Convert a number to letters for column head
/// </summary>
/// <param name="mySheet">A number</param>
/// <returns>A string</returns>
#region GET THE HEAD LETTERS OF A GIVEN COLUMN
public string GetHeadString(int aNumber)
{
string myChars = "";
int tens = aNumber / 26;
int ones = aNumber % 26;
if (ones==0)
{
tens--;
ones = 26;
}
if (tens<1)
{
myChars = ((char)(64 + aNumber)).ToString();
}
else
{
myChars = ((char)(64 + tens)).ToString() + ((char)(64 + ones)).ToString();
}
return myChars;
}
#endregion
/// <summary>
/// Zhanshan Dong - 04/29/2006 - chech a sheet existing or not
/// </summary>
/// <param name="myBook">A workbook</param>
/// <param name="mySheetName">A worksheet name</param>
/// <returns>A boolean value</returns>
#region Check if a sheet exists in the the given workbook
public bool existingSheet(Excel.Workbook myBook, string mySheetName)
{
for (int i=0; i<myBook.Worksheets.Count; i++)
{
Excel.Worksheet mySheet = ((Excel.Worksheet)myBook.Worksheets.get_Item((object)(i+1)));
if (String.Compare(mySheet.Name, mySheetName, true) == 0)
{
return true;
}
}
return false;
}
#endregion
}
}
Reference
How to automate Microsoft Excel from Microsoft Visual C# .NET
Automating MS Excel Using Visual Studio .NET
Table with Excel – TWE 2.0
Download the source code – zdexcel.zip
Interesting point on data extraction, For data extraction i use python for simple things, but for anything slightly hard i have used extracting data for getting data from documents, files, or the web