public DataTable ReadExcel(string filepath, Func <string, int, Type> callback) { using (FileStream fs = new FileStream(filepath, FileMode.Open, FileAccess.Read)) { XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs); ISheet sheet = xssfworkbook.GetSheetAt(0); IRow header = sheet.GetRow(sheet.FirstRowNum); List <int> columns = new List <int>(); DataTable dt = new DataTable(); CalculateTool.ReadHeader(header, dt, columns, callback); CalculateTool.ReadBody(sheet, dt, columns); return(dt); } }
public static void ReadHeader(IRow header, DataTable dt, List <int> columns) { for (int i = 0; i < header.LastCellNum; i++) { var cellValue = CalculateTool.GetCellValueType(header.GetCell(i)); if (cellValue == null || cellValue.ToString() == string.Empty) { dt.Columns.Add(new DataColumn("Columns" + i)); } else { dt.Columns.Add(new DataColumn(cellValue.ToString())); } columns.Add(i); } }
public static void ReadHeader(IRow header, DataTable dt, List <int> columns, Func <string, int, Type> callback) { for (int i = 0; i < header.LastCellNum; i++) { var cellValue = CalculateTool.GetCellValueType(header.GetCell(i)); if (cellValue == null || cellValue.ToString() == string.Empty) { var type = callback(cellValue.ToString(), i); dt.Columns.Add(new DataColumn("Columns" + i, type)); } else { var type = callback(cellValue.ToString(), i); dt.Columns.Add(new DataColumn(cellValue.ToString(), type)); } columns.Add(i); } }
public void WriteTableNoTitle(DataTable dt, string filepath) { XSSFWorkbook xssfworkbook = new XSSFWorkbook(); ISheet sheet = xssfworkbook.CreateSheet(dt.TableName == "" ? FILE_NAME : dt.TableName); CalculateTool.WriteBody(sheet, dt); using (MemoryStream ms = new MemoryStream()) { xssfworkbook.Write(ms); var buf = ms.ToArray(); using (FileStream fs = new FileStream(filepath, FileMode.Create, FileAccess.Write)) { fs.Write(buf, 0, buf.Length); fs.Flush(); } } }
public override DataTable ReadExcel(string filepath) { if (string.IsNullOrEmpty(filepath)) { throw new ArgumentNullException(nameof(filepath)); } using (FileStream fs = new FileStream(_filepath, FileMode.Open, FileAccess.Read)) { IWorkbook workbook = WorkbookFactory.Create(fs); DataTable dt = new DataTable(); ISheet sheet = workbook.GetSheetAt(0); IRow header = sheet.GetRow(sheet.FirstRowNum); List <int> columns = new List <int>(); CalculateTool.ReadHeader(header, dt, columns); CalculateTool.ReadBody(sheet, dt, columns); return(dt); } }
public override void WriteTable(DataTable dt, string filepath) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(); ISheet sheet = hssfworkbook.CreateSheet(dt.TableName == "" ? FILE_NAME : dt.TableName); IRow header = sheet.CreateRow(0); CalculateTool.WriteHeader(header, dt); CalculateTool.WriteBody(sheet, dt); using (MemoryStream stream = new MemoryStream()) { hssfworkbook.Write(stream); var buf = stream.ToArray(); using (FileStream fs = new FileStream(filepath, FileMode.Create, FileAccess.Write)) { fs.Write(buf, 0, buf.Length); fs.Flush(); } } }
public static void ReadBody(ISheet sheet, DataTable dt, List <int> columns) { for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++) { DataRow dr = dt.NewRow(); bool hasValue = false; foreach (int j in columns) { dr[j] = CalculateTool.GetCellValueType(sheet.GetRow(i).GetCell(j)); if (dr[j] != null && dr[j].ToString() != string.Empty) { hasValue = true; } } if (hasValue) { dt.Rows.Add(dr); } } }