Excel Automation in .Net

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

Add a referrence to the Microsoft Excel object library COM component.


Add the namespace Excel


Instantiate the class Excel.ApplicationClass as below

Excel.Application xl=new Excel.ApplicationClass();


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


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

To save a value in a cell

excelCell2.Cells.Value2 = "SampleText"; //Assign a value to the cell
wb.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.