Here in this article, we are going to guide you to create a program in C# to automate MS Excel workbook. To do this you need to add reference to the dynamic link library of Excel called Microsoft.Office.Interop.Excel.dll.
This sample C# code example will open the excel file available on some path and then will write data to specified cells and then saves it with some other name on the same path.
Follow the below given steps.
1: In Solution Explorer window, right click the project then click Add Reference.
2: In Reference Manager window, search Excel and then select Microsoft.Office.Interop.Excel component and then click OK.
3: Now check that Microsoft.Office.Interop.Excel has been added under the references.
4: Add the Excel reference to your Class by placing this code in the list of namespaces.
using excel = Microsoft.Office.Interop.Excel;
5: Define the function with the name you desire. Here we created with the name workOnExcel.
public void workOnExcel()
{
}
6: Write the code inside the function as given below. Comments have been added above each code line to help you understand the functionality.
public void workOnExcel()
{
// creates excel application object
excel.Application objExcel = new excel.Application();
// creates new excel workbook object
excel.Workbook excelworkBook;
// creates new excel sheet object
excel.Worksheet excelSheet;
string excelPath = "D:/Test Excel";
// opens excel workbook on the specified path
objExcel.Workbooks.Open(excelPath);
// set the Visible property of excel to false to make excel invisible
objExcel.Visible = false;
// set the DisplayAlerts property of excel to disable all display alerts
objExcel.DisplayAlerts = false;
// assigns the workbook to excel application
excelworkBook = objExcel.Workbooks[1];
// specifies the sheet to work on
excelSheet = (excel.Worksheet)excelworkBook.Sheets.get_Item("Sheet1");
// writes data in specified cell of specified sheet
excelSheet.Cells[1, 1] = "First Name";
excelSheet.Cells[1, 2] = "Last Name";
excelSheet.Cells[2, 1] = "James";
excelSheet.Cells[2, 2] = "Thompson";
excelSheet.Cells[3, 1] = "Eric";
excelSheet.Cells[3, 2] = "Morgan";
excelSheet.Cells[4, 1] = "Tim";
excelSheet.Cells[4, 2] = "Smith";
excelSheet.Cells[5, 1] = "John";
excelSheet.Cells[5, 2] = "Kivell";
excelSheet.Cells[6, 1] = "Andy";
excelSheet.Cells[6, 2] = "Jardine";
// saves the excel on specified path
excelworkBook.SaveAs("D:\\Data Excel.xlsx");
// closes all workbooks
objExcel.Workbooks.Close();
// quits the excel application object
objExcel.Quit();
}
7: Check on the specified path. You will see the new excel workbook with name “Data Excel.xlsx”. Open this excel and you will get the programmatically written data inside this excel workbook.
