static DataTable ReadExcelConvertToDataTable(string xlsxFileName)
{
DataTable dt = new DataTable();
try
{
IWorkbook book;
using (FileStream fs = new FileStream(xlsxFileName, FileMode.Open, FileAccess.Read))
{
book = new XSSFWorkbook(fs);
ISheet st = book.GetSheetAt(0);
int rowsCount = st.LastRowNum;
DataRow dtHeadersRow = dt.NewRow();
int colsCount = st.GetRow(0).Cells.Count();
var firstRow = st.GetRow(0);
for (int i = 0; i < colsCount; i++)
{
string colName = firstRow.GetCell(i).StringCellValue;
DataColumn dc = new DataColumn(colName);
dt.Columns.Add(colName);
}
dt.Rows.Add(dtHeadersRow);
for (int i = 0; i <= rowsCount; i++)
{
DataRow dr = dt.NewRow();
StringBuilder rowBuilder = new StringBuilder();
for (int j = 0; j < colsCount; j++)
{
ICell cell = st.GetRow(i).GetCell(j);
string cellValue = string.Empty;
if (cell != null)
{
switch (cell.CellType)
{
case CellType.String:
cellValue = cell.StringCellValue;
break;
case CellType.Numeric:
cellValue = cell.NumericCellValue.ToString();
break;
}
}
dr[j] = cellValue;
}
dt.Rows.Add(dr);
}
}
}
catch(Exception ex)
{
Console.WriteLine(ex.TargetSite);
return dt;
}
return dt;
}