So in this article we are going to learn how you can read excel in c#
for this we need to install EPPlus from Nuget packages.
remember you need to give full path of excel location to read and saving.
public static void ReadExcel()
{
using (var pck = new OfficeOpenXml.ExcelPackage())
{
using (var streamRead = File.OpenRead(@"C:\Nawin\Excels\TestList.xlsx"))
{
pck.Load(streamRead);
}
var ws = pck.Workbook.Worksheets.First();
DataTable tbl = new DataTable();
foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
{
tbl.Columns.Add(firstRowCell.Text);
}
var startRow = 2;
for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
{
var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
DataRow row = tbl.NewRow();
foreach (var cell in wsRow)
{
row[cell.Start.Column - 1] = cell.Text;
}
tbl.Rows.Add(row);
}
var finalList = new List<string>();
string names = @"C:\Nawin\Excels\NewList" + ".csv";
StringBuilder workBook = new StringBuilder();
workBook.AppendLine("S.No. , Id, Name");
var counter = 0;
foreach (DataRow myDataRow in tbl.Rows)
{
Object[] cells = myDataRow.ItemArray;
string name = null != cells[0] ? cells[0].ToString().Trim() : "";
string test = null != cells[10] ? cells[10].ToString().Trim() : "";
var finalFound = TestIds.Any(mn => (name.ToLower().Contains(mn.ToLower())));
if (finalFound)
{
counter++;
finalList.Add(name);
workBook.AppendLine(counter + "," + name + "," + test);
}
}
Console.WriteLine(finalList.Count());
File.WriteAllText(names, workBook.ToString());
}
}
