NPOI操作EXECL帮助类
Esempio n. 1
0
        /// <summary>
        /// 拓展方法,生成EXECL
        /// </summary>
        /// <param name="info">EXECL相关信息</param>
        /// <param name="token">用户认证令牌</param>
        /// <returns>Execl路径</returns>
        public static MemoryStream ExportExeclStream(this ExcelInfo info)
        {
            //1.获取列表对应数据
            DataTable dt = GetGirdData(info);

            //2.创建Execl文档
            return(NPOIHelper.Export(dt, info.ColumnInfoList));
        }
Esempio n. 2
0
 /// <summary>
 ///从上传文件流中读取数据 保存为datatable
 /// </summary>
 /// <param name="ins">输入流</param>
 /// <param name="datasheet">数据得sheet表格</param>
 /// <returns>数据</returns>
 public virtual DataTable GetDataFromExcel(Stream ins, out ISheet datasheet)
 {
     return(NPOIHelper.GetDataFromExcel(ins, out datasheet, StartRowIndex));
 }
Esempio n. 3
0
        /// <summary>
        /// 校验数据是否正常
        /// </summary>
        /// <param name="dt">数据集</param>
        /// <param name="outputStream">输出流</param>
        /// <param name="sheet">数据sheet</param>
        /// <param name="userInfo">用户信息</param>
        /// <param name="fileName">文件名称</param>
        /// <param name="DictColumnFields">英文字段名到中文列名映射关系</param>
        /// <returns>ImportResult</returns>
        public virtual ImportResult Verify(DataTable dt, ISheet sheet, Dictionary <string, object> extraInfo, UserInfo userInfo, string fileName, Dictionary <string, ImportVerify> DictColumnFields)
        {
            IWorkbook    wb     = sheet.Workbook;
            ImportResult result = new ImportResult();

            string[]          arrErrorMsg    = null;
            string            errorMsg       = string.Empty;
            int               columnCount    = dt.Columns.Count;
            string            columnName     = string.Empty;
            ImportVerify      objVerify      = null;
            ImportVerifyParam objVerifyParam = new ImportVerifyParam {
                DTExcel = dt, CellValue = null, ColName = columnName, ColumnIndex = 0, RowIndex = 0
            };
            DataRow row       = null;
            object  objExtra  = null;
            bool    isCorrect = true;

            //错误数据行样式
            var   cellErrorStyle = NPOIHelper.GetErrorCellStyle(wb);
            ICell errorCell      = null;
            IRow  sheetRow       = null;

            for (int i = 0, rLength = dt.Rows.Count; i < rLength; i++)
            {
                row         = dt.Rows[i];
                arrErrorMsg = new string[columnCount];
                for (int j = 0; j < columnCount; j++)
                {
                    columnName = dt.Columns[j].ColumnName;
                    if (DictColumnFields.TryGetValue(columnName, out objVerify))
                    {
                        if (objVerify.VerifyFunc != null)
                        {
                            objVerifyParam.CellValue   = row[j];
                            objVerifyParam.ColumnIndex = j;
                            objVerifyParam.RowIndex    = i;
                            objVerifyParam.ColName     = objVerify.ColumnName;
                            if (extraInfo != null)
                            {
                                extraInfo.TryGetValue(columnName, out objExtra);
                            }
                            arrErrorMsg[j] = objVerify.VerifyFunc(objVerifyParam, objExtra);
                        }
                    }
                }
                errorMsg = string.Join(",", arrErrorMsg.Where(e => !string.IsNullOrEmpty(e)));
                if (!string.IsNullOrEmpty(errorMsg))
                {
                    isCorrect = false;
                    //设置错误信息
                    sheetRow  = sheet.GetRow(StartRowIndex + 1 + i);
                    errorCell = sheetRow.GetCell(columnCount);
                    if (errorCell == null)
                    {
                        errorCell = sheetRow.CreateCell(columnCount);
                    }
                    errorCell.CellStyle = cellErrorStyle;
                    errorCell.SetCellValue(errorMsg);
                }
            }

            //输出错误信息模版
            if (!isCorrect)
            {
                sheetRow  = sheet.GetRow(StartRowIndex);
                errorCell = sheetRow.GetCell(columnCount);
                if (errorCell == null)
                {
                    errorCell = sheetRow.CreateCell(columnCount);
                }
                ICellStyle copyStyle = sheetRow.GetCell(columnCount - 1).CellStyle;
                ICellStyle style     = NPOIHelper.GetErrorHeadCellStyle(wb);
                IFont      font      = style.GetFont(wb);
                IFont      copyfont  = copyStyle.GetFont(wb);
                font.FontHeight           = copyfont.FontHeight;
                font.FontName             = copyfont.FontName;
                style.FillForegroundColor = copyStyle.FillForegroundColor;
                style.BorderBottom        = copyStyle.BorderBottom;
                style.BorderLeft          = copyStyle.BorderLeft;
                style.BorderRight         = copyStyle.BorderRight;
                style.BorderTop           = copyStyle.BorderTop;
                errorCell.CellStyle       = style;
                errorCell.SetCellValue("错误信息");

                //自适应列宽度
                sheet.AutoSizeColumn(columnCount);
                int width = sheet.GetColumnWidth(columnCount) + 2560;
                sheet.SetColumnWidth(columnCount, width > NPOIHelper.MAX_COLUMN_WIDTH ? NPOIHelper.MAX_COLUMN_WIDTH : width);

                result.Message = ExcelImportHelper.GetErrorExcel(wb, fileName);
            }
            else
            {
                result.IsSuccess = true;
            }
            return(result);
        }