Excel Automation - With Clean Exit/Quit

• Download source - 3.88 KB

Introduction

The whole idea of posting this article on Excel automation is to provide a set of Fn()s that would provide a better and cleaner way of releasing the memory. The article also contains other simple to use Fn()s which gives the developers ability to read and write (manipulate) excel data in a .NET based application project.

Motivation

The motivation to write this article came from a very basic problem, described on many forums that "Excel does not quit after automation" or "Office application does not quit after automation from Visual Studio .NET client". There are numerous articles which discuss this topic, so I thought of solving this with a simple and reusable solution.

Problem Statement

For effective management of memory in managed code environment, the CLR (Virtual Execution Engine) relays on garbage collector, but since the Runtime Callable Wrapper is itself a managed object, so lifetime of an Excel automation object is not guaranteed to end deterministically, as soon as we release reference to it. The Runtime Callable Wrapper is marked for garbage collection and is released when the GC wants to free more memory. This means that although the Excel application object is no longer used, still the application may remain loaded because the garbage collection has not yet freed the Runtime Callable Wrapper. For further reference on the problem statement, please feel free to go to Microsoft Knowledge Base: 317109.

Assumptions

The article assumes that the reader has basic understanding of the following:

1. Object Oriented programming concepts
2. Visual Studio .NET IDE
3. C# based application development
4. VSTO - Excel Automation

Using ExcelHelper class library

1. Start Visual Studio 2008 .NET.
2. Click on the File menu, click New and then click Project.
3. Under Project types: Visual C#, select "Windows Forms Application" and click OK. Form1 is created by default.
4. Add a reference by Clicking on the menu "Project -> Add Reference".
5. On the COM tab, locate "Microsoft Excel 9.0 Object Library" ver 1.3.0.0 and then click Select.
6. Add a reference by Clicking on the menu "Project -> Add Reference".
7. On the COM tab, locate "Microsoft Office 12.0 Object Library" ver 2.4.0.0 and then click Select.
8. Add a reference by Clicking on the menu "Project -> Add Reference".
9. On the .NET tab, locate "Microsoft.Office.Interop.Excel" ver 12.0.0.0 and then click Select.
10. Add a reference by clicking on the menu "Project -> Add Existing Item".
11. Then locate and Add ExcelHelper.cs file.
12. Press (Cltr+w,x) to populate ToolBox and Add a button to the Form1.cs[Design] page.
13. Double click on the button and add OfficeAutomation namespace to the Form1.cs file.
14. Create an object of the ExcelHelper class and start excessing the functions as shown in the code snippet below:

void FunctionName()
{
    ExcelHelper xlObj = new ExcelHelper("C:\\ExcelFile.xls");  	// Initialize object 
							// by calling Constructor 
    
    xlObj.Open();                                  	// Open the specific Excel file
                   
    // Perform operation from the available Fn()'s
     
    xlObj.Close();                                  	// Save and Close the open file
    
    xlObj.ReleaseObjects();                          	// Release Objects 
}

Mentioned below are the public functions of the ExcelHelper class that are used to perform operations on Excel file:

public ExcelHelper(string filePath)                         //Constructor
public ExcelHelper(string filePath, int workSheetNo)        //Constructor
public ExcelHelper(string filePath, string workSheetName)   //Constructor
public void Open() 
public int ReadFromCellInteger(int rowId, int colId)
public int ReadFromCellInteger(object indexLoc) 
public double ReadFromCellDouble(int rowId, int colId) 
public double ReadFromCellDouble(object indexLoc)
public string ReadFromCellString(int rowId, int colId)
public string ReadFromCellString(object indexLoc)
public void WriteToCell(int rowId, int colId, int cellValue)
public void WriteToCell(int rowId, int colId, double cellValue)
public void WriteToCell(int rowId, int colId, string cellValue)
public void WriteToCell(int rowId, int colId, DateTime cellValue)
public void WriteToCells(object startIndexLoc, object endIndexLoc, int[,] cellValues)
public void WriteToCells(object startIndexLoc, object endIndexLoc, double[,] cellValues)
public void WriteToCells(object startIndexLoc, object endIndexLoc, string[,] cellValues)
public void WriteToCells(object startIndexLoc, object endIndexLoc, DateTime[,] cellValues)
public DateTime ReadFromCellDateTime(int rowId, int colId)
public DateTime ReadFromCellDateTime(object indexLoc)
public object ReadFromCells(object startIndexLoc, object endIndexLoc)
public void DataTableToExcel(int startRowId, int startColId, 
	System.Data.DataTable cellValues)
public string ConvertInteger2Alphabet(int colId)
public void Close()
public void ReleaseObjects()

Mentioned below are the code snippets of all the functions and how they can be used in the application project.

//Constructor 
ExcelHelper xlObj = new ExcelHelper("C:\\ExcelFile.xls"); 	//Constructor takes FileName 
							//as a parameter 

//Constructor
//ExcelLib xlObj = new ExcelLib("C:\\ExcelFile.xls", 2);   //Constructor takes FileName 
				//and the WorkSheet no to be opened as a parameter

//Constructor
//ExcelLib xlObj = new ExcelLib("C:\\ExcelFile.xls", "Sheet3");  //Constructor takes 
			//FileName and the WorkSheet name to be opened as a parameter
               
//Open() function    
xlObj.Open();    	//Fn() opens the excel file whose path is passed in the constructor 
		//of the class

// WriteToCell() function has 4 overloads
xlObj.WriteToCell(3, 3, 1024);               	//Fn() writes integer value to the 3rd 
					//row and 3rd column of the Worksheet

// WriteToCells() function has 4 overloads
int[,] saNames = new int[2, 2]; 
saNames[0, 0] = 1; saNames[0, 1] = 2;  
saNames[1, 0] = 3; saNames[1, 1] = 4;
object obj_firstIndexLoc = "A7";
object obj_lastIndexLoc = "B8";
xlObj.WriteToCells(obj_firstIndexLoc, obj_lastIndexLoc, saNames); 	//Fn() writes int 
								//array to cells
       
// ReadFromCellInteger() function
int n_no = xlObj.ReadFromCellInteger(9, 2);   	//Fn() reads integer value from 
						//row 9th column 2nd

// ReadFromCellInteger() function
int n_no = xlObj.ReadFromCellInteger("I8");  		//Fn() reads integer value from  
						//row 8th column 9th (column: I)

// ReadFromCellDouble() function 
double d_no =  xlObj.ReadFromCellDouble(9, 2);   	//Fn() reads double value from 
						//row 9th column 2nd

// ReadFromCellDouble() function
double d_no =  xlObj.ReadFromCellDouble("C5");   	//Fn() reads double value from  
						//row 5th column 3rd (column: C)

// ReadFromCellString() function
string s_string = xlObj.ReadFromCellString(6, 5); 	//Fn() reads string value from
						//row 6th column 5th

// ReadFromCellString() function
string s_string = xlObj.ReadFromCellString("D4"); 	//Fn() reads string value from  
						//row 4th column 4rd (column: D)

// ReadFromCellDateTime() function
DateTime dt = xlObj.ReadFromCellDateTime(4, 7);   	//Fn() reads DateTime value from 
						//row 4th column 7th
 
// ReadFromCellDateTime() function
DateTime dt = xlObj.ReadFromCellDateTime("G4");   	//Fn() reads DateTime value from 
						//row 4th column 7th
 
// ReadFromCells() function 
object obj_values = xlObj.ReadFromCells("A7", "A9");  	//Fn() reads array of values
						//from the defined range of cells
 
// DataTableToExcel
DataTable myDataTable = new DataTable();
// Fill DataTable object with any values. 
xlObj.DataTableToExcel(3, 4, myDataTable);    // Fills the currently opened Worksheet with 
					//Datatable values, starting from 3rd row and 
					//4th column
 
// ConvertInteger2Alphabet() function
string s_str = xlObj.ConvertInteger2Alphabet(987); 	//Fn() converts a int into array 
						//of Alphabet, 
 
// Close() function
xlObj.Close();                   	//Fn() saves and closes the open workbook object.

// ReleaseObjects() function
xlObj.ReleaseObjects();          	//Fn() releases the Excel.WorkSheet, 
				//Excel.WorkBook and Excel.Application 
                                 	//object before calling the KillExcelProcessById Fn()

Approach Used to Clean Memory

In order to perform complete cleanup, a function named GetExcelProcessIdsSnapshot() was created which takes a snapshot of all of the current Excel processes running in memory and then the same function would be called two times, once before opening the Excel file whose path is provided in the constructor and second time just after opening the Excel file and the results of both the Fn() calls would be stored in separate Generics List and finally the GetExcelProcessId() function would be passed the two Generics List and the alone standing Excel process (which was newly created as a result of the opening of Excel file) will be identified and later killed after releasing the Excel.WorkSheet, Excel.WorkBook and Excel.Application object. Following is the code snippet of Open(), GetExcelProcessIdsSnapshot() and GetExcelProcessId() functions.

public void Open() 
{ 
    try 
    {
        l_initialProcessIds = this.GetExcelProcessIdsSnapshot();
        xlAppObj = new Excel.ApplicationClass();
        xlWorkBookObj = xlAppObj.Workbooks.Open(s_filePath, 0, false, 5, 
        System.Reflection.Missing.Value, System.Reflection.Missing.Value,
       	false, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);
 
        if (n_workSheetNo != 0 && s_workSheetName == null)
        {
            xlWorkSheetObj = 
		(Excel.Worksheet)xlWorkBookObj.Worksheets.get_Item(n_workSheetNo); 
 
            l_finalProcessIds = this.GetExcelProcessIdsSnapshot();
            n_processId = this.GetExcelProcessId(l_initialProcessIds, l_finalProcessIds);
        }
 
        if (s_workSheetName != null && n_workSheetNo == 0)
        {
            bool b_worksheetNameExist = true;
            for (int n_loop = 1; n_loop <= xlWorkBookObj.Worksheets.Count; n_loop++)
            {
                xlWorkSheetObj = 
		(Excel.Worksheet)xlWorkBookObj.Worksheets.get_Item(n_loop); 
                if (xlWorkSheetObj.Name == s_workSheetName)
                {
                    b_worksheetNameExist = true; 
                    break; 
                }
                b_worksheetNameExist = false;
            }
            if (b_worksheetNameExist == true)
            {
                l_finalProcessIds = this.GetExcelProcessIdsSnapshot();
                n_processId = this.GetExcelProcessId
				(l_initialProcessIds, l_finalProcessIds);
            }
            else
            {
                // Exception is on its way........ ting 
            } 
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString(), "Error"); 
    } 
}  
 
private List<int> GetExcelProcessIdsSnapshot()
{
    List<int> ProcessIds = new List<int>();
    try
    {
        Process[] Processes = Process.GetProcessesByName("Excel");
        for (int n_loop = 0; n_loop < Processes.Count(); n_loop++)
             ProcessIds.Add(Processes.ElementAt(n_loop).Id);
     }
     catch (Exception ex)
     {
         MessageBox.Show("Process Snapshot not Successful " + ex.ToString(), "Error");
     }
 
    return ProcessIds;
}
  
private int GetExcelProcessId(List<int> l_initialProcessIds, List<int> l_finalProcessIds)
{
    try
    {
        for (int n_loop = 0; n_loop < l_initialProcessIds.Count; n_loop++)
        { 
            int n_PidInitialProcessList = l_initialProcessIds.ElementAt(n_loop);
            for (int n_innerloop = 0; n_innerloop < l_finalProcessIds.Count; n_innerloop++)
            {
                int n_PidFinalProcessList = l_finalProcessIds.ElementAt(n_innerloop);
                if (n_PidInitialProcessList == n_PidFinalProcessList)
                {
                    l_finalProcessIds.RemoveAt(n_innerloop);
                    break;
                }
            }
            l_initialProcessIds.RemoveAt(n_loop);
            n_loop--;
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show("GetExcelProcessId() unsuccessful" + ex.ToString(), "Error");
    }
 
    return l_finalProcessIds.ElementAt(0);
}
 
private void KillExcelProcessById(int n_processId)
{
    try
    {
        Process xlProcess = null;
        xlProcess = Process.GetProcessById(n_processId);
        xlProcess.Kill(); 
    } 
    catch (Exception ex)
    {
        MessageBox.Show("ProcessId " + n_processId.ToString() + 
		" could not be cleaned" + ex.ToString(), "Error");
    }
} 

Points of Interest

The basic idea of writing this class was to demonstrate the memory clean up technique, but the class has a lot of scope of improvement and there is no limit to the number of new functions that can be added for Excel manipulation ranging from password protection to Chart creation and so on...

History

• Initial Release 1.0