Thread: C# основной форум/Excel Automation in .Net

Excel Automation in .Net
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


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.