/// <summary>
/// 读取上传文件中的Excel（返回表格，如果需要导入，还需要格式化表头）
/// </summary>
/// <param name="workbook">Excel工作本</param>
/// <param name="sheetIndex">第N个Sheet表格</param>
/// <param name="isMergedCellName">是否把合并列头的名称连接起来</param>
/// <param name="startIndex">开始索引</param>
/// <param name="headCrossRowNum">头部跨行数（为0时自动识别）</param>
/// <returns></returns>
private static MDataTable ReadExcel(IWorkbook workbook, Stream stream = null, int sheetIndex = 0, int startIndex = 0, int headCrossRowNum = 0, bool isMergedCellName = false)
{
MDataTable dt = new MDataTable();
try
{
if (workbook != null)
{
ISheet sheet = workbook.GetSheetAt(sheetIndex);
dt.TableName = sheet.SheetName;
dt.DynamicData = sheet;
IRow excelRow = sheet.GetRow(startIndex);
int dataRowStart = startIndex;
if (headCrossRowNum <= 0)
{
dataRowStart += 1;
#region 遍历、找出(头部跨行数)最大行。用最大行进行遍历列（如果为空，往上一级找）
int mIndex = 0;
for (int i = 0; i < excelRow.Cells.Count; i++)
{
ICell cell = excelRow.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK);// .Cells[i];
if (cell.IsMergedCell)
{
NPOI.SS.Util.CellRangeAddress range = sheet.GetCellRange(cell);//获取范围块。
if (range != null)
{
dataRowStart = Math.Max(dataRowStart, range.LastRow + 1);//设置数据的读取行数。
mIndex++;
i += range.LastColumn - range.FirstColumn;
}
}
}
#endregion
}
else
{
dataRowStart += headCrossRowNum;
}
//读取列头。
if (dataRowStart > 1)
{
excelRow = sheet.GetRow(dataRowStart - 1);
}
dt.RecordsAffected = dataRowStart;
dt.Columns.CheckDuplicate = false;
#region 读取列头
//if (excelRow.FirstCellNum > 0)
//{
// for (int i = 0; i < excelRow.FirstCellNum; i++)
// {
// string columnName = "该列头为空_" + i;
// dt.Columns.Add(columnName);
// }
//}
int emptyCellCount = 0;//兼容处理错误的Excel格式（读了256个空格列）
for (int i = 0; i < excelRow.Cells.Count; i++)
{
string columnName = string.Empty;
for (int j = dataRowStart; j > startIndex; j--)
{
#region MyRegion
IRow row = sheet.GetRow(j - 1);
ICell cell;
try
{
cell = row.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK);//不能用GetCell(i)，会多出一行导致下面错误位。
}
catch (Exception)
{
continue;
}
string name = cell.ToString().Trim(); // .StringCellValue.Trim();
if (!string.IsNullOrEmpty(name) && !columnName.Contains(name)) //
{
columnName += name + "_";
}
else if (j != dataRowStart && cell.IsMergedCell)
{
cell = sheet.GetMergedRegion(cell);//获取范围块。
if (cell != null)
{
name = cell.ToString().Trim();
if (!string.IsNullOrEmpty(name) && !columnName.Contains(name))
{
columnName += name + "_";
}
}
}
if (!isMergedCellName && !string.IsNullOrEmpty(columnName))
{
break;
}
#endregion
}
columnName = columnName.TrimEnd('_').Trim();
if (string.IsNullOrEmpty(columnName))
{
if (emptyCellCount > 30)//连续30次空格列
{
break;
}
emptyCellCount++;
columnName = "该列头为空_" + i;
}
else
{
emptyCellCount = 0;//只要一个正常，即回归索引
}
if (dt.Columns.Contains(columnName))
{
columnName += "_" + i;
}
dt.Columns.Add(columnName);
}
//移除空格列
if (emptyCellCount > 0)
{
dt.Columns.RemoveRange(dt.Columns.Count - emptyCellCount, emptyCellCount);
}
if (dt.Columns.Count > 0)
{
dt.Conn = dt.Columns.Count.ToString();//找个变量存储实际的列的长度，在SetError中使用。（dt可能在SetError前列被变更)
}
#endregion
ICell sheetCell;
int emptyCount = 0;
for (int i = dataRowStart; i <= sheet.LastRowNum; i++)
{
excelRow = sheet.GetRow(i);
if (excelRow == null)
{
break;
}
MDataRow tbRow = dt.NewRow();
bool isOk = false;
for (int j = 0; j < dt.Columns.Count; j++)
{
#region 读一行
sheetCell = excelRow.GetCell(j, MissingCellPolicy.RETURN_BLANK_AS_NULL);
if (sheetCell != null)
{
string value = string.Empty;
if (sheetCell.CellType == CellType.Numeric)
{
try
{
if (sheetCell.ToString().Split('/', '-').Length > 1)
{
value = sheetCell.DateCellValue.ToString();
}
else
{
value = sheetCell.NumericCellValue.ToString();
}
}
catch
{
value = sheetCell.ToString();
}
}
else if (sheetCell.CellType == CellType.Formula)
{
try
{
/*公式不一定是Numeric的取值，也有可能是=G4 这种，然而单元格G4不是数字;
* 公式单元格也有可能读取错误 #VALUE! #REF! 等*/
CellType resultType = sheetCell.CachedFormulaResultType;
switch (resultType)
{
case CellType.Boolean:
value = sheetCell.BooleanCellValue.ToString();
break;
case CellType.Numeric:
value = sheetCell.NumericCellValue.ToString();
break;
case CellType.Blank:
case CellType.Error:
case CellType.Unknown:
value = string.Empty;
break;
default:
value = sheetCell.StringCellValue;
break;
}
//value = sheetCell.NumericCellValue.ToString(); //由公式取值
}
catch
{
}
}
else
{
value = sheetCell.ToString();
}
value = value.Trim();
if (!isOk && !string.IsNullOrEmpty(value))
{
isOk = true;
}
if (!string.IsNullOrEmpty(value))//空值当Null值处理，避免字段有Check对空值的约束
{
tbRow.Set(sheetCell.ColumnIndex, value);
}
}
#endregion
}
if (isOk)//忽略空行数据。
{
dt.Rows.Add(tbRow);
}
else
{
if (dt.Rows.Count == 0)
{
dt.RecordsAffected++;
}
emptyCount++;
if (emptyCount > 1)//超过2次空格行，跳出。
{
break;
}
}
}
workbook.Close();//关闭了，dt.DynamicData带出了Sheet,还是可以后续使用（估计NPOI的没处理）
}
}
catch (Exception err)
{
Log.WriteLogToTxt(err);
}
return(dt);
}
