/// <summary> /// Excel转table 导入 Michaux 20160531 /// </summary> /// <param name="file"></param> /// <returns></returns> public DataTable ImportExcelToDataTable(HttpPostedFileBase file,Dictionary<string,ExcelFormatter> formatColumn=null) { var datatable = new DataTable(); if (file.FileName.IndexOf("xlsx") > -1) { NPOI.XSSF.UserModel.XSSFWorkbook Upfile = new NPOI.XSSF.UserModel.XSSFWorkbook(file.InputStream); var sheet = Upfile.GetSheetAt(0); var firstRow = sheet.GetRow(0); var buffer = new byte[file.ContentLength]; for (int cellIndex = firstRow.FirstCellNum; cellIndex < firstRow.LastCellNum; cellIndex++) { datatable.Columns.Add(firstRow.GetCell(cellIndex).StringCellValue, typeof(string)); } for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++) { DataRow datarow = datatable.NewRow(); var row = sheet.GetRow(i); if (row == null) { continue; } bool con = true; for (int j = row.FirstCellNum; j < row.LastCellNum; j++) { //if (formatColumn!=null && formatColumn.ContainsKey(firstRow.GetCell(j).StringCellValue)) //{ //} var cell = row.GetCell(j); if (cell == null) { datarow[j] = ""; continue; } switch (cell.CellType) { case CellType.Numeric: datarow[j] = cell.NumericCellValue; break; case CellType.String: datarow[j] = cell.StringCellValue; break; case CellType.Blank: datarow[j] = ""; break; case CellType.Formula: switch (row.GetCell(j).CachedFormulaResultType) { case CellType.String: string strFORMULA = row.GetCell(j).StringCellValue; if (strFORMULA != null && strFORMULA.Length > 0) { datarow[j] = strFORMULA.ToString(); } else { datarow[j] = null; } break; case CellType.Numeric: datarow[j] = Convert.ToString(row.GetCell(j).NumericCellValue); break; case CellType.Boolean: datarow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue); break; case CellType.Error: datarow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue); break; default: datarow[j] = ""; break; } break; default: con = false; break; } if (!con) { break; } } if (con) { datatable.Rows.Add(datarow); } } } else { NPOI.HSSF.UserModel.HSSFWorkbook Upfile = new NPOI.HSSF.UserModel.HSSFWorkbook(file.InputStream); var sheet = Upfile.GetSheetAt(0); var firstRow = sheet.GetRow(0); var buffer = new byte[file.ContentLength]; for (int cellIndex = firstRow.FirstCellNum; cellIndex < firstRow.LastCellNum; cellIndex++) { datatable.Columns.Add(firstRow.GetCell(cellIndex).StringCellValue, typeof(string)); } for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++) { DataRow datarow = datatable.NewRow(); var row = sheet.GetRow(i); if (row == null) { continue; } bool con = true; for (int j = row.FirstCellNum; j < row.LastCellNum; j++) { var cell = row.GetCell(j); if (cell == null) { datarow[j] = ""; continue; } switch (cell.CellType) { case CellType.Numeric: datarow[j] = cell.NumericCellValue; break; case CellType.String: datarow[j] = cell.StringCellValue; break; case CellType.Blank: datarow[j] = ""; break; case CellType.Formula: switch (row.GetCell(j).CachedFormulaResultType) { case CellType.String: string strFORMULA = row.GetCell(j).StringCellValue; if (strFORMULA != null && strFORMULA.Length > 0) { datarow[j] = strFORMULA.ToString(); } else { datarow[j] = null; } break; case CellType.Numeric: datarow[j] = Convert.ToString(row.GetCell(j).NumericCellValue); break; case CellType.Boolean: datarow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue); break; case CellType.Error: datarow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue); break; default: datarow[j] = ""; break; } break; default: con = false; break; } if (!con) { break; } } if (con) { datatable.Rows.Add(datarow); } } } #region 清除最后的空行 for (int i = datatable.Rows.Count - 1; i > 0; i--) { bool isnull = true; for (int j = 0; j < datatable.Columns.Count; j++) { if (datatable.Rows[i][j] != null) { if (datatable.Rows[i][j].ToString() != "") { isnull = false; break; } } } if (isnull) { datatable.Rows[i].Delete(); } } #endregion return datatable; }