/// <summary> /// 读取sheet数据 /// </summary> /// <param name="dt"></param> /// <param name="sheet"></param> /// <param name="NullOrEmptyIgnore"></param> private void ReadSheetData(DataTable dt, ISheet sheet, int curIndex, int sheetCount, Action <int> reportProcessValue = null) { #region//读取数据 for (int i = 0; i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); //读取当前行数据 if (row == null) { continue; } DataRow newrow = dt.NewRow(); bool IsNullOrEmpty = true; for (int j = 0; j < row.LastCellNum; j++) { ICell cell = row.GetCell(j); //当前表格 string cellvalue = ""; if (cell != null) { switch (cell.CellType) { case CellType.Blank: //空数据类型处理 { cellvalue = ""; break; } case CellType.Numeric: { //特殊处理吧,木办法 string DataFormat = cell.CellStyle.GetDataFormatString(); if (DataFormat == "yyyymm") // { //DateTime date = cell.DateCellValue; //if (string.IsNullOrEmpty(DataFormat)) //{ // cellvalue = date.ToString("yyyy-MM-dd HH:mm:ss"); //} //else //{ // cellvalue = date.ToString(DataFormat); //} //cellvalue = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss"); if (DataFormat == "yyyymm") { DataFormat = "yyyyMM"; } cellvalue = cell.DateCellValue.ToString(DataFormat); } else if (HSSFDateUtil.IsCellInternalDateFormatted(cell)) { cellvalue = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss"); } else { cellvalue = cell.NumericCellValue.ToString(); } break; } case CellType.String: { cellvalue = cell.StringCellValue; break; } case CellType.Formula: { if (cell.CachedFormulaResultType == CellType.Numeric) { cellvalue = cell.NumericCellValue.ToString(); } else { cellvalue = cell.StringCellValue; } break; } default: { cellvalue = cell.ToString(); break; } } } if (!string.IsNullOrEmpty(cellvalue)) { IsNullOrEmpty = false; } newrow[j] = cellvalue; } if (reportProcessValue != null) { double dblHaveReadSheetPercent = curIndex * 1.0 / sheetCount; double dblCurSheetPercent = 1 / sheetCount; double dblCurReadRowCountPercent = (i + 1) * 1.0 / sheet.LastRowNum; if (i >= sheet.LastRowNum / 2) { } double dblCurProcess = dblHaveReadSheetPercent + dblCurReadRowCountPercent / dblCurSheetPercent; reportProcessValue((int)(dblCurProcess * 100)); } if (IsNullOrEmpty)//如果是空行 { continue; } dt.Rows.Add(newrow); } #endregion }
/// <summary> /// HSSFWorkbook To DataTable /// </summary> /// <returns></returns> public DataTable GetDataTable(int?userId, string filePath, out string message) { message = string.Empty; bool exists = IsExists(filePath, out message); if (!exists) { return(null); } string fileName = System.IO.Path.GetFileName(filePath); try { IWorkbook workbook = null; ISheet sheet = null; IRow row = null; FileStream streamfile = new FileStream(filePath, FileMode.Open, FileAccess.Read); // 2007版本 if (filePath.IndexOf(".xlsx") > 0) { workbook = new XSSFWorkbook(streamfile); } // 2003版本 else if (filePath.IndexOf(".xls") > 0) { workbook = new HSSFWorkbook(streamfile); } sheet = workbook.GetSheetAt(0); DataTable table = new DataTable(); IRow headerRow = sheet.GetRow(0); //第一行为标题行 int cellCount = headerRow.LastCellNum; //总列 int rowCount = sheet.LastRowNum; //总行 for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(column); } DataColumn colAdder = new DataColumn("Adder"); DataColumn colAddTime = new DataColumn("AddTime"); DataColumn colFileName = new DataColumn("FileName"); table.Columns.Add(colAdder); table.Columns.Add(colAddTime); table.Columns.Add(colFileName); DateTime addTime = DateTime.Now; for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++) { row = sheet.GetRow(i); DataRow dataRow = table.NewRow(); if (row != null) { dataRow["Adder"] = userId; dataRow["AddTime"] = addTime; dataRow["FileName"] = fileName; for (int j = row.FirstCellNum; j < cellCount; j++) { ICell cell = row.Cells[j]; switch (cell.CellType) { case CellType.Blank: dataRow[j] = string.Empty; break; case CellType.String: dataRow[j] = cell.StringCellValue; break; case CellType.Numeric: if (HSSFDateUtil.IsCellDateFormatted(cell)) { dataRow[j] = cell.DateCellValue; } else if (HSSFDateUtil.IsCellInternalDateFormatted(cell)) { dataRow[j] = cell.DateCellValue; } else { dataRow[j] = cell.NumericCellValue; } break; case CellType.Boolean: dataRow[j] = cell.BooleanCellValue; break; case CellType.Error: dataRow[j] = cell.ErrorCellValue.ToString(); break; case CellType.Formula: HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(workbook); dataRow[i] = eva.Evaluate(cell).StringValue; break; default: dataRow[j] = cell.ToString(); break; } } } table.Rows.Add(dataRow); } return(table); } catch (Exception ex) { message += ex.Message; return(null); } }