/// <summary> /// 获取sheet表对应的DataTable /// </summary> /// <param name="sheet">Excel工作表</param> /// <param name="strMsg"></param> /// <returns></returns> private static DataTable GetSheetDataTable(ISheet sheet, out string strMsg) { lock (filelock) { strMsg = ""; DataTable dt = new DataTable(); FDataHelper.CreateDtColumns(ref dt); string sheetName = sheet.SheetName; int startIndex = 0;// sheet.FirstRowNum; int lastIndex = sheet.LastRowNum; //最大列数 int cellCount = 0; IRow maxRow = sheet.GetRow(0); for (int i = startIndex; i <= lastIndex; i++) { IRow row = sheet.GetRow(i); if (row != null && cellCount < row.LastCellNum) { cellCount = row.LastCellNum; maxRow = row; } } //列名设置 try { for (int i = 5; i < maxRow.LastCellNum; i++)//maxRow.FirstCellNum { dt.Columns.Add("data" + (i - 4)); } } catch { strMsg = "工作表" + sheetName + "中无数据"; return(null); } //数据填充 for (int i = startIndex; i <= lastIndex; i++) { IRow row = sheet.GetRow(i); DataRow drNew = dt.NewRow(); if (row != null) { for (int j = row.FirstCellNum; j < row.LastCellNum; ++j) { if (row.GetCell(j) != null) { ICell cell = row.GetCell(j); switch (cell.CellType) { case CellType.Blank: drNew[j] = ""; break; case CellType.Numeric: short format = cell.CellStyle.DataFormat; //对时间格式(2015.12.5、2015/12/5、2015-12-5等)的处理 if (format == 14 || format == 31 || format == 57 || format == 58) { drNew[j] = cell.DateCellValue; } else { drNew[j] = cell.NumericCellValue; } if (cell.CellStyle.DataFormat == 177 || cell.CellStyle.DataFormat == 178 || cell.CellStyle.DataFormat == 188) { drNew[j] = cell.NumericCellValue.ToString("#0.00"); } break; case CellType.String: drNew[j] = cell.StringCellValue; break; case CellType.Formula: try { drNew[j] = cell.NumericCellValue; if (cell.CellStyle.DataFormat == 177 || cell.CellStyle.DataFormat == 178 || cell.CellStyle.DataFormat == 188) { drNew[j] = cell.NumericCellValue.ToString("#0.00"); } } catch { try { drNew[j] = cell.StringCellValue; } catch { } } break; default: drNew[j] = cell.StringCellValue; break; } } } } dt.Rows.Add(drNew); } return(dt); } }
/// <summary> /// Excel转换成DataTable(.xls) /// </summary> /// <param name="filePath">Excel文件路径</param> /// <returns></returns> public static DataTable ExcelToDataTable(string filePath) { lock (filelock) { var dt = new DataTable(); FDataHelper.CreateDtColumns(ref dt); using (var file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { var hssfworkbook = new HSSFWorkbook(file); var sheet = hssfworkbook.GetSheetAt(0); int lastcolumn = sheet.GetRow(0).LastCellNum; for (var j = 5; j < lastcolumn; j++) { dt.Columns.Add("data" + (j - 4)); } var rows = sheet.GetRowEnumerator(); while (rows.MoveNext()) { var row = (HSSFRow)rows.Current; var dr = dt.NewRow(); for (var i = 0; i < row.LastCellNum; i++) { var cell = row.GetCell(i); if (cell == null) { dr[i] = null; } else { switch (cell.CellType) { case CellType.Blank: dr[i] = "[null]"; break; case CellType.Boolean: dr[i] = cell.BooleanCellValue; break; case CellType.Numeric: dr[i] = cell.ToString(); break; case CellType.String: dr[i] = cell.StringCellValue; break; case CellType.Error: dr[i] = cell.ErrorCellValue; break; case CellType.Formula: try { dr[i] = cell.NumericCellValue; } catch { dr[i] = cell.StringCellValue; } break; default: dr[i] = "=" + cell.CellFormula; break; } } } dt.Rows.Add(dr); } } return(dt); } }