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); }