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

Excel.Sheets xlsheets = wb.Sheets; //Get the sheets from workbookExcel.Worksheet excelWorksheet = (Excel.Worksheet)xlsheets[1]; //Select the first sheetExcel.Range excelCell = (Excel.Range)excelWorksheet.get_Range("B4:FZ4", Type.Missing); //Select a range of cellsExcel.Range excelCell2 = (Excel.Range)excelWorksheet.get_Range("A5:A5", Type.Missing); //Select a single cellConsole.WriteLine(excelCell2.Cells.Value2.ToString()); //Print the value of the cell for a single cell selectionSystem.Array myvalues = (System.Array)excelCell.Cells.Value2; //Assign it to an arraystring[] strArray = ConvertToStringArray(myvalues); //Convert array into String arrayforeach (string str in strArray)Console.WriteLine(" Text in Cell " + str); //Loop through the array to print the values in the cell

To save a value in a cell

excelCell2.Cells.Value2 = "SampleText"; //Assign a value to the cellwb.Save(); //Save the workbook

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.

Thiagu is living in Bangalore, India. His native is Madurai, a historic city in south India. He loves to code in C#. He frequents c# corner articles when he is not coding. To view his blog - http://csharpnet.blogspo... Read more