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.

XL-file.JPG

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

    XL-Automation.JPG
  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.