Excel Automation in .Net

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 worsheets.

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.

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.


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();