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?

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

Below code will allow to read a/multiple worksheet from excel and store to a datatable. To start with we will use ExcelDataReader and ExcelDataReader.DataSet. you can download the same via Nuget package manager to reference in your project

using System.IO;

using ExcelDataReader;

using System.Data;

string fileName= "D:\\Students.xlsx";

//Open file and returns as Stream

FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read);

//Createopenxmlreader via ExcelReaderFactory

IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

//Return as DataSet and Set the First Row as Column Name

DataSet result = excelReader.AsDataSet(new ExcelDataSetConfiguration()
{
    ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
    {
        UseHeaderRow = true
    }
});

//Get all the Tables

DataTableCollection table = result.Tables;

//Store it in DataTable

DataTable resultTable = table["Sheet1"];

//Loop through the rows in datatable

foreach(DataRow dr in resultTable.Rows)
{
       var name = dr["Name"];
       var age = dr["Age"];
}

Another way to read /Write excel :How to read/write an excel using Microsoft.Office.Interop.Excel?

Removing hidden/special characters from strings dot net

There will be situation when we need to remove special or hidden characters from a string. Below code will help you to chive the same.

C#

string output = new string(input.Where(c => !char.IsControl(c)).ToArray());

or 

 
string output = new string(input.Where(c => char.IsLetter(c) || char.IsDigit(c)).ToArray());

//if which to take only letters and

//if which to take only letters and digits

VB

Dim cleanString As String = Regex.Replace(yourString, "[^A-Za-z0-9\-/]", "")