读取execl数据到表

读取execl数据到 表

将execl 内容读取到 DataTable （我这按照开始的列和结束的列并不是全部读取）然后再将 DataTable 中的内容映射到你的model中 也就是表中。 execl 行名称必须是和model对应 建议第一行中文注释，第二行表字段 从第二行开始读取

将DataTable 中的数据映射到model

/// <summary>
        /// 将DataRow赋值给model中同名属性
        /// </summary>
        /// <typeparam name="T">泛型：model的类型</typeparam>
        /// <param name="objmodel">model实例</param>
        /// <param name="dtRow">DataTable行数据</param>
        public T TableRowToModel<T>(T objmodel, DataRow dtRow)
        {
            //获取model的类型
            Type modelType = typeof(T);
            //获取model中的属性
            PropertyInfo[] modelpropertys = modelType.GetProperties();
            //遍历model每一个属性并赋值DataRow对应的列
            foreach (PropertyInfo pi in modelpropertys)
            {
                //获取属性名称
                String name = pi.Name;
                if (dtRow.Table.Columns.Contains(name))
                {

                    //非泛型
                    if (!pi.PropertyType.IsGenericType)
                    {
                        try
                        {
                            pi.SetValue(objmodel, string.IsNullOrEmpty(dtRow[name].ToString()) ? null : Convert.ChangeType(dtRow[name], pi.PropertyType), null);
                        }
                        catch (Exception ex)
                        {
                            _logger.Error(ex, ex.Message);
                            throw Error.Init(BandexErrorType.DNC_JSON_ERROR, new Exception($"第{dtRow.Table.Rows}行，列【{dtRow.Table.Columns.ToString()}】,单元格格式错误"));
                        }
                    }
                    //泛型Nullable<>
                    else
                    {
                        Type genericTypeDefinition = pi.PropertyType.GetGenericTypeDefinition();
                        //model属性是可为null类型，进行赋null值
                        if (genericTypeDefinition == typeof(Nullable<>))
                        {
                            //返回指定可以为 null 的类型的基础类型参数
                            pi.SetValue(objmodel, string.IsNullOrEmpty(dtRow[name].ToString()) ? null : Convert.ChangeType(dtRow[name], Nullable.GetUnderlyingType(pi.PropertyType)), null);
                        }
                    }
                }
            }
            return objmodel;
        }
        public DataTable ExcelImport(MemoryStream fileStream, string flieType, int ColumnsStart, int ColumnsEnd)
        {
            return ExcelImport(fileStream, flieType, 1, ColumnsStart,  ColumnsEnd);
        }

• 读取excel , 按照开始的列和结束的列读取

/// <summary>
        /// 读取excel ,默认第一行为标头
        /// 0行中文注释 1行数据库字段 2行开始是数据
        /// </summary>
        /// <param name="fileStream">文件数据流</param>
        /// <param name="headerRowNo">标题行号从1开始</param>
        /// <param name="ColumnsStart">从第几列开始</param>
        /// <param name="ColumnsEnd">从第几列结束</param>
        /// <returns></returns>
        public DataTable ExcelImport(MemoryStream fileStream, string flieType, int headerRowNo,int ColumnsStart,int ColumnsEnd)
        {
            //DataTable dt = new DataTable();
            ISheet sheet;
            if (flieType == ".xls")
            {
                HSSFWorkbook hssfworkbook = new HSSFWorkbook(fileStream);
                sheet = hssfworkbook.GetSheetAt(0);
            }
            else
            {
                XSSFWorkbook xssfworkbook = new XSSFWorkbook(fileStream);
                sheet = xssfworkbook.GetSheetAt(0);
            }
            return ReadSheetToDataTable(headerRowNo, sheet, ColumnsStart, ColumnsEnd);
        }
        /// <summary>
        /// 从sheet中读取数据到DataTable
        /// </summary>
        /// <param name="headerRowNo">标题行号（数据行号=标题行号+1）</param>
        /// <param name="sheet"></param>
        /// <param name="ColumnsStart">从第几列开始读取</param>
        /// <param name="ColumnsEnd">从第几列结束</param>
        /// <returns></returns>
        private DataTable ReadSheetToDataTable(int headerRowNo, ISheet sheet ,int ColumnsStart ,int ColumnsEnd)
        {
            var dt = new DataTable();
            IRow headerRow = sheet.GetRow(headerRowNo);
            //int cellCount = headerRow.LastCellNum;

            for (int j = ColumnsStart; j < ColumnsEnd+1; j++)
            {
                ICell cell = headerRow.GetCell(j);
                dt.Columns.Add(cell.ToString());
            }

            for (int i = (headerRowNo + 1); i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dataRow = dt.NewRow();

                for (int j = 0; j <= ColumnsEnd - ColumnsStart; j++)
                {
                    if (row.GetCell(j) == null)
                    {
                        continue;
                    }

                    ICell cell = row.GetCell(j);
                    if (cell.CellType == CellType.Error)
                    {
                        _logger.Info($"第{i + 1}行，列【{dt.Columns[j].ColumnName}】,单元格格式错误");
                        throw Error.Init(BandexErrorType.DNC_JSON_ERROR, new Exception($"第{i + 1}行，列【{dt.Columns[j].ColumnName}】,单元格格式错误"));
                    }
                    else if (cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell))
                    {
                        dataRow[j] = cell.DateCellValue;
                    }
                    else if (cell.CellType == CellType.Numeric)
                    {
                        dataRow[j] = cell.NumericCellValue;
                    }
                    else if (cell.CellType == CellType.Blank)
                    {
                        dataRow[j] = "";
                    }
                    else
                    {
                        dataRow[j] = cell.StringCellValue;
                    }

                    //dataRow[j] = row.GetCell(j).ToString();
                }

                bool existsValue = false;
                foreach (DataColumn column in dt.Columns)
                {
                    if (dataRow[column.ColumnName] == null || string.IsNullOrEmpty(dataRow[column.ColumnName].ToString()))
                    {
                        continue;
                    }

                    existsValue = true;
                    break;
                }
                if (existsValue)
                {
                    dt.Rows.Add(dataRow);
                }
            }
            return dt;
        }