Introduction
I have written this article to address requests I have been getting through e-mail on how to automate MS Excel. Most people who are looking at this article might also be interested at the following articles for automating MS Word documents. Here is the link for Automating MS Word using C#, and here is the link for Automating MS Word using C++.
Back to the main subject, there are many spreadsheets in the business world, and more and more of them are being created and sometimes used in ways that simply Excel was not designed for, for instance storing sensitive and crucial data for laboratories and etc�
To start with, this article will not get into the very advanced automations available in Excel, but it will give a framework that hopefully can be used by others to improve on and make it more expansible. The framework will allow you to create an Excel object and control some of the basic functionalities such as getting worksheet information and extracting data from the worksheet given a range.
The program that I had to develop has a larger scope, I will just be concentrating on the Excel portion. But there are a few neat classes which I developed for file system navigation using threads. If there is enough request for such an article, or if I get a chance to do it, I will go ahead and post it. In the meantime I hope that the following article benefits you.
Background
Having enough understanding of OOP and familiarity with the C# language.
Using the code
I will provide the Excel wrapper class that can be used in your project. The code will be discussed below. I will not get too much into the Excel object model, first because it is a huge task, and second because there is already documentation done by Microsoft. Before we start, here is a quick start for beginners who need to know how to setup an Office Automation project:
Create a new project, for simplicity, create a Windows application, go ahead and right click on References in the Solution Explorer, and select Add Reference� When the Add Reference window comes up, select the COM tab. This will list all Component names which are available on your machine. Since we are going to use MS Excel, you will scroll down until you find: Microsoft Excel 11.0 Object Library.
Note: Yours might be a different version depending on the version of Office installed on your machine. This is for MS Excel 2003.
using System;
using System.IO;
using System.Collections;
using System.Threading;
using Office = Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
using System.Diagnostics;
namespace ATPMain
{
public class VkExcel
{
private Excel.Application excelApp = null;
private Excel.Workbook excelWorkbook = null;
private Excel.Sheets excelSheets = null;
private Excel.Worksheet excelWorksheet = null;
...using Office = Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
You will need to include these two so you can use the Excel object in your code. So we need to have an Excel.Application object, Excel.Workbook object, Excel.Sheets object, and Excel.Worksheet object. These object will be used to control and extract data from Excel. So we declare the following variables to represent the mentioned objects: excelApp, excelWorkbook, excelSheets, and excelWorksheet.
....
private static object vk_missing = System.Reflection.Missing.Value;
private static object vk_visible = true;
private static object vk_false = false;
private static object vk_true = true;
private bool vk_app_visible = false;
private object vk_filename;
#region OPEN WORKBOOK VARIABLES
private object vk_update_links = 0;
private object vk_read_only = vk_true;
private object vk_format = 1;
private object vk_password = vk_missing;
private object vk_write_res_password = vk_missing;
private object vk_ignore_read_only_recommend = vk_true;
private object vk_origin = vk_missing;
private object vk_delimiter = vk_missing;
private object vk_editable = vk_false;
private object vk_notify = vk_false;
private object vk_converter = vk_missing;
private object vk_add_to_mru = vk_false;
private object vk_local = vk_false;
private object vk_corrupt_load = vk_false;
#endregion
#region CLOSE WORKBOOK VARIABLES
private object vk_save_changes = vk_false;
private object vk_route_workbook = vk_false;
#endregion
public VkExcel()
{
this.startExcel();
}
public VkExcel(bool visible)
{
this.vk_app_visible = visible;
this.startExcel();
}
...
In the above block, we have predefined some constants that will be used to open a given Excel file. To find out more about what each parameter represents or does, you should look into the documentation that comes with Excel.
We have two constructors: VkExcel() which by default will start Excel hidden, and the other VkExcel(bool visible) which gives you the option to specify if you would like to see the Excel application or not.
...
#region START EXCEL
private void startExcel()
{
if( this.excelApp == null )
{
this.excelApp = new Excel.ApplicationClass();
}
this.excelApp.Visible = this.vk_app_visible;
}
#endregion
#region STOP EXCEL
public void stopExcel()
{
if( this.excelApp != null )
{
Process[] pProcess;
pProcess = System.Diagnostics.Process.GetProcessesByName("Excel");
pProcess[0].Kill();
}
}
#endregion
...
The above code starts and stops the Excel Application. startExcel() checks to see if the excelApp object is initialized or not, if it is then just make sure its visibility is set to the visible property. If not, it goes ahead and initializes the object for us. stopExcel() also checks to see if the object is currently in use, and if it is then it will go ahead and kill the process.
Note: pProcess[0].Kill() will make sure that Excel is gone for good! Some people that do Excel automation always complain that after they quit the application, Excel disappears but the Excel process is still in the task monitor, this code will take care of that for you!
...
#region OPEN FILE FOR EXCEL
public string OpenFile(string fileName, string password)
{
vk_filename = fileName;
if( password.Length > 0 )
{
vk_password = password;
}
try
{
this.excelWorkbook = this.excelApp.Workbooks.Open(
fileName, vk_update_links, vk_read_only,
vk_format, vk_password,
vk_write_res_password,
vk_ignore_read_only_recommend, vk_origin,
vk_delimiter, vk_editable, vk_notify,
vk_converter, vk_add_to_mru,
vk_local, vk_corrupt_load);
}
catch(Exception e)
{
this.CloseFile();
return e.Message;
}
return "OK";
}
#endregion
public void CloseFile()
{
excelWorkbook.Close( vk_save_changes,
vk_filename, vk_route_workbook );
}
...
Alright, so the above code allows you to open Excel files. OpenFile(string fileName, string password) takes two parameters, the filename, or FULLNAME which is the path + filename, and a password parameter, which is used for protected sheets. Notice that the open function takes a bunch of parameters, which we have defined in the class. CloseFile() will goes ahead and closes the file.
Note: The code provided is for MS Excel 2003. For earlier versions, the parameters are a little different, you will need to check the documentation. If you need help on that send me an e-mail and I will try to help you out.
...
#region GET EXCEL SHEETS
public void GetExcelSheets()
{
if( this.excelWorkbook != null )
{
excelSheets = excelWorkbook.Worksheets;
}
}
#endregion
#region FIND EXCEL ATP WORKSHEET
public bool FindExcelATPWorksheet(string worksheetName)
{
bool ATP_SHEET_FOUND = false;
if( this.excelSheets != null )
{
for( int i=1; i<=this.excelSheets.Count; i++ )
{
this.excelWorksheet =
(Excel.Worksheet)excelSheets.get_Item((object)i);
if( this.excelWorksheet.Name.Equals(worksheetName) )
{
this.excelWorksheet.Activate();
ATP_SHEET_FOUND = true;
return ATP_SHEET_FOUND;
}
}
}
return ATP_SHEET_FOUND;
}
#endregion
...
The above code demonstrates how to get all worksheets belonging to a workbook and getting a specific worksheet to extract data from. GetExcelSheets() gets all the sheets. FindExcelATPWorkSheet(string worksheetName) searches for worksheets with the name worksheetName.
...
#region GET RANGE
public string[] GetRange(string range)
{
Excel.Range workingRangeCells =
excelWorksheet.get_Range(range,Type.Missing);
System.Array array = (System.Array)workingRangeCells.Cells.Value2;
string[] arrayS = this.ConvertToStringArray(array);
return arrayS;
}
#endregion
...
GetRange(string range) is the function that actually retrieves the data from the Excel sheet and we convert the returned values into a string[]. This is done by the next function call: this.ConvertToStringArray(array). Then the string[] is passed back to the caller who can consume it in any way they want.
...
#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
}
And the final code portion: ConvertToStringArray(System.Array values) will take the array passed from GetRange(...) to put it into a string array and pass it back.
We have reached the end of our object. As you can see, it is a very simple object with very minimum functionality, but it is a good starting point for anyone who needs to get started quickly, and you can very easily expand it into a more complex object.
I have not included a demo project. The reason is that it is extremely easy to use the object. You will just need to follow these steps to initialize and use the VkExcel object.
- Create an object of type
VkExcel: VkExcel excel = new VkExcel(false);. Remember that VkExcel(...) takes a parameter for visibility of the application.
- Open an Excel file:
string status = excel.OpenFile( filename, password );, pass in the filename or fullname if you are using a OpenFileDialog. If the file is not password protected, set password to null.
- Check to see if the file was opened successfully:
if( status.equal("OK")) ...
- Retrieve Excel sheets:
excel.GetExcelSheets(); will get all sheets internally in the object.
- Search for specific sheet:
excel.FindExcelWorksheet(worksheetName); will look for any sheet in a given file. Pass in the worksheetName as parameter.
- Retrieve data given a range:
string[] A4D4 = excel.GetRange("A4:D4"); this will return values in the range in a string[].
That's all there is to it!
