public static DataTable Sheet_to_DT(IXLWorksheet sheet)
{
if (sheet == null)
{
return(null);
}
DataTable dt = new DataTable(sheet.Name);
int headerRowID = HssExcel.GetHeaderRowID(sheet);
//Console.WriteLine(sheet.Name + " header: " + headerRowID);
if (headerRowID < 0)
{
return(dt);
}
int rowID = 0, col_count = 0;
List <string> colName_list = null;
foreach (IXLRow xl_row in sheet.Rows())
{
if (rowID > headerRowID)
{
DataRow dt_row = dt.Rows.Add();
for (int i = 0; i < col_count; ++i)
{
dt_row[i] = xl_row.Cell(i + 1).Value;
}
}
else if (rowID == headerRowID)
{
colName_list = HssExcel.GetNames_from_cells(xl_row.Cells());
col_count = colName_list.Count;
foreach (string s in colName_list)
{
dt.Columns.Add(s);
}
}
++rowID;
}
return(dt);
}
public static DataSet Excel_to_DS(string filePath)
{
DataSet ds = new DataSet();
XLWorkbook workBook = null;
try { workBook = new XLWorkbook(filePath); }
catch (Exception ex)
{
Console.WriteLine("HssExcel error 0: " + ex.ToString());
return(ds);
}
foreach (IXLWorksheet sheet in workBook.Worksheets)
{
DataTable dt = HssExcel.Sheet_to_DT(sheet);
ds.Tables.Add(dt);
}
return(ds);
}