예제 #1
0
        /// <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);
        }