Excel Automation in .Net

Introduction

Excel Automation is a buzz word in both webapps and winapps. In the programming life (like Mr.Anderson neo) I guess almost every one could have come across this word. For others let me go further in detail. Excel Automation is automating some or all of the process involved in creating or updating excel worksheets.

The real life scenario of an excel automation can be a daily account maintenance where you have an excel sheet template with graphs and calculations already in place except the data. So you want the data to be pulled in from a database and written to the excel sheet. After this the template takes care of the data by manipulating for graph generation.

Analysis

For the above said scenario we can go for a simple console application in .Net. Why I didn't go for an ASP.Net web application? Because running a web application requires a browser to be opened and closed. This becomes tedious when you schedule the process using windows scheduler to occur in particular intervals. And running a console based application is quite easy.



Getting Started

Pardon me for beating around the bush. Now let us jump in to the good part (coding). For this automation process we need to follow the below steps

1. Add a referrence to the Microsoft Excel object library COM component.
   
   
2. Add the namespace Excel
   
   
3. Instantiate the class Excel.ApplicationClass as below
   
   Excel.Application xl=new Excel.ApplicationClass();
   
   
4. To open an excel file,
   
   Excel.Workbook wb=xl.Workbooks.Open(Environment.CurrentDirectory+"/SampleExcel.xls",0, false, 5, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value,true, false, System.Reflection.Missing.Value, false, false, false);//Open the excel sheet
   
   
5. To read cell(s) in the worksheet,
   
   Excel.Sheets xlsheets = wb.Sheets; //Get the sheets from workbook
   Excel.Worksheet excelWorksheet = (Excel.Worksheet)xlsheets[1]; //Select the first sheet
   Excel.Range excelCell = (Excel.Range)excelWorksheet.get_Range("B4:FZ4", Type.Missing); //Select a range of cells
   Excel.Range excelCell2 = (Excel.Range)excelWorksheet.get_Range("A5:A5", Type.Missing); //Select a single cell
   Console.WriteLine(excelCell2.Cells.Value2.ToString()); //Print the value of the cell for a single cell selection
   System.Array myvalues = (System.Array)excelCell.Cells.Value2; //Assign it to an array
   string[] strArray = ConvertToStringArray(myvalues); //Convert array into String array
   foreach (string str in strArray)
   Console.WriteLine(" Text in Cell " + str); //Loop through the array to print the values in the cell
   
   
   
   
6. To save a value in a cell
   
   excelCell2.Cells.Value2 = "SampleText"; //Assign a value to the cell
   wb.Save(); //Save the workbook
   
   
7. Finally Quit the Excel Application
   
   xl.Quit();

Conclusion

Excel is a great tool to work with. When it comes to automating, we need to consider many things. Always remember to quit the excel application in code before exiting. If not, the memory consumed by the excel application will not be freed up.