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

Share

Tags: , , ,