/// <summary>
/// Get a list of header values from an Excel file
/// </summary>
/// <param name="fileName">File to use</param>
/// <returns>list of header values</returns>
public static List <string> GetHeaderListFromExcel(string fileName)
{
List <string> headerList = new List <string>();
XLWorkbook wb = new XLWorkbook(fileName);
IXLWorksheet ws = wb.Worksheet(1);
var xlRange = ws.RangeUsed();
IXLTable compTable = xlRange.AsTable();
foreach (IXLRangeColumn hello in compTable.Columns())
{
headerList.Add(hello.FirstCell().Value.ToString());
}
return(headerList);
}
/// <summary>
/// Get an ADO.Net DataTable object from an Excel .xlsx file
/// </summary>
/// <param name="fileName">File to use</param>
/// <returns>ADO.Net DataTable</returns>
public static DataTable GetDataTableFromExcel(string fileName)
{
DataTable newDt = new DataTable();
XLWorkbook wb = new XLWorkbook(fileName);
IXLWorksheet ws = wb.Worksheet(1);
var xlRange = ws.RangeUsed();
IXLTable compTable = xlRange.AsTable();
foreach (IXLRangeColumn hello in compTable.Columns())
{
newDt.Columns.Add(hello.FirstCell().Value.ToString(), typeof(string));
}
bool headerFound = false;
foreach (IXLRangeRow row in compTable.Rows())
{
if (headerFound)
{
DataRow dr = newDt.NewRow();
int itemCt = 0;
foreach (IXLCell cell in row.Cells())
{
dr[itemCt] = row.Cell(itemCt + 1).Value.ToString();
itemCt++;
}
newDt.Rows.Add(dr);
}
else
{
headerFound = true;
}
}
return(newDt);
}