How to read/write an excel using Microsoft.Office.Interop.Excel?

Below code will allow to read/ write to an excel using Microsoft.Office.Interop.Excel. To start with we will use Microsoft.Office.Interop.Excel  . You can download the same via Nuget package manager to reference in your project.

 

If you are getting below error, try downloading “Microsoft.CSharp” via Nuget package manager to reference in your project.

using Excel = Microsoft.Office.Interop.Excel;

//Creating Excel application scope

Excel.Application excelApplication = new Excel.Application();

//Opening the excel file

Excel.Workbook CCWorkbook = excelApplication.Workbooks.Open(@"D:\Students1.xlsx");

//you can substitute the above line of code with below if you want to pass additional options while opening workbook

//excel.Workbook CCWorkbook = Customercreation.Workbooks.Open(@"D:\Students1.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, false, 1, 0);

//Getting hold of worksheet

Excel._Worksheet workSheet = (Excel._Worksheet)CCWorkbook.Sheets["Sheet1"];

//Getting used range the excel worksheet

Excel.Range range = workSheet.UsedRange;

//Arriving at rows and column count  for the used range

int rowCount = range.Rows.Count;
int colCount = range.Columns.Count;

//Iterating through the rows and columns in range

for (int i = 1; i <= rowCount; i++)
{
    for (int j = 1; j <= colCount; j++)
    {
        //Reading Cell value
        string temp = ((range.Cells[i, j]).Value2).ToString();

        //Writiing to third column

        workSheet.Cells[i, 3] = "Processed";
    }
}

//Saving Workbook

CCWorkbook.Save();

//Closing Workbook

CCWorkbook.Close(true);

//Quit excel application

excelApplication.Quit();

The final Result will be

Another way to read excel : How to read an excel using ExcelDataReader and Store result to a DataTable?