Npoi to Excel 常用构造与读取

         之前记录过如何判断 excel，word 的笔记，这里用到了就顺便写一下怎么构造与读取 excel ，毕竟在工作中用到的还是挺多的，导出文件，下载模板，读取文件都时挺常用的。话不多说，直接开始。

1. 构造 Excel 文件，从下载Npoi 的包开始，这个时候主要就是 Sheet表，Row行，Cell 列 ，以及 Style 等相关参数进行设置

                //创建对象
                HSSFWorkbook workbook = new HSSFWorkbook();
                //创建电子表
                ISheet sheet = workbook.CreateSheet(yearPeriodList[ii].YearPeriodName + ii);//创建首行
                IRow rowA = sheet.CreateRow(0);
                //创建首行首列
                ICell cell = rowA.CreateCell(0);
                //设置单元格样式
                cell.CellStyle = ExcelHelper.SetFontAlign(workbook, "微软雅黑", 14);
                //设置单元格值
                cell.SetCellValue(yearPeriodList[ii].YearPeriodName + ii);
                //合并单元格 首行 末行  首列 末列
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 10));

               #region 写入数据到客户端，供下载

               MemoryStream ms = new MemoryStream();
               workbook.Write(ms);
               ms.Seek(0, SeekOrigin.Begin);
               DateTime dt = DateTime.Now;
               string dateTime = dt.ToString("yyyyMMddHHmmssfff");
               string fileName = "文档名称" + dateTime + ".xls";
               return File(ms, "application/vnd.ms-excel", fileName);

               #endregion

2. 设置内容，样式等。有时候需要对单元格的字体，背景，以及下拉选项进行赋值

 

public class ExcelHelper
    {
        //返回默认标题单元格样式
        public static ICellStyle GetTitleCellStyle(HSSFWorkbook workbook)
        {
            ICellStyle style = workbook.CreateCellStyle();
            IFont font = workbook.CreateFont(); //创建一个字体样式对象       
            font.FontName = "微软雅黑"; //和excel里面的字体对应 
            font.FontHeightInPoints = 14; //字体大小
            style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //上框线
            style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; //下框线
            style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;  //左框线
            style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;  //右框线
            style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
            style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
            style.SetFont(font);
            return style;
        }

        /// <summary>
        /// 设置字体颜色
        /// </summary>
        /// <param name="style"></param>
        /// <param name="color"></param>
        public static ICellStyle SetFontColor(HSSFWorkbook workbook,ICellStyle style,short color)
        {
            //创建一个字体颜色
            IFont font = workbook.CreateFont();
            //设置字体颜色
            font.Color = color;
            font.FontHeightInPoints = 12;
            style.BorderTop = BorderStyle.Thin;
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.SetFont(font);
            return style;
        }

        //设置单元格对齐方式,字体 
        public static ICellStyle SetFontAlign(HSSFWorkbook workbook, string fontStyle = "Arial", short fontSize = 10, HorizontalAlignment align0 = HorizontalAlignment.Center, VerticalAlignment align1 = VerticalAlignment.Center)
        {
            ICellStyle style = workbook.CreateCellStyle();
            IFont font = workbook.CreateFont(); //创建一个字体样式对象       
            font.FontName = fontStyle; //和excel里面的字体对应 
            font.FontHeightInPoints = fontSize;
            style.Alignment = align0;
            style.VerticalAlignment = align1;
            style.SetFont(font);
            style.IsLocked = true;
            return style;
        }

        //绑定下拉列表值--数据项较少
        public static void SetCellDropdownList(ISheet sheet, int firstrow, int lastrow, int firstcol, int lastcol, string[] vals,bool bol = false)
        {
            if (bol)
            {
                for (int i = firstrow; i < lastrow; i++)
                {
                    for (int j = firstcol; j < lastcol; j++)
                    {
                        sheet.CreateRow(i).CreateCell(j).CellStyle.IsLocked = false;
                    }
                }
            }

            //设置生成下拉框的行和列
            var cellRegions = new CellRangeAddressList(firstrow, lastrow, firstcol, lastcol);
            //设置 下拉框内容
            DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(vals);
            //绑定下拉框和作用区域，并设置错误提示信息
            HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
            dataValidate.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。");
            dataValidate.ShowPromptBox = true;
            sheet.AddValidationData(dataValidate);
        }


        //绑定下拉列表值--数据项较多 Edit by fanzhang in 20200920
        public static void SetCellDropdownListMul(HSSFWorkbook workbook, ISheet sheet, string name, int firstrow, int lastrow, int firstcol, int lastcol, string[] vals, int sheetindex = 1)
        {
            //先创建一个Sheet专门用于存储下拉项的值
            ISheet sheet2 = workbook.CreateSheet(name);
            //隐藏
            workbook.SetSheetHidden(sheetindex, true);
            int index = 0;
            foreach (var item in vals)
            {
                sheet2.CreateRow(index).CreateCell(0).SetCellValue(item);
                index++;
            }
            //创建的下拉项的区域：
            var rangeName = name + "Range";
            IName range = workbook.CreateName();
            range.RefersToFormula = name + "!$A$1:$A$" + index;
            range.NameName = rangeName;
            CellRangeAddressList regions = new CellRangeAddressList(firstrow, lastrow, firstcol, lastcol);
            //设置约束
            DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(rangeName);
            HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
            dataValidate.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。");
            dataValidate.ShowPromptBox = true;
            sheet.AddValidationData(dataValidate);
        }

    }

 设置单元格背景颜色，换行

                //创建换行内容（一般是首行备注）
                var remark = new StringBuilder();
                remark.Append("1.星期、课节、班级名称、科目名称不可编辑。\n");
                remark.Append("2.单双周和走班多个科目使用英文“/”符分割。\n");
                remark.Append("3.科目名称必须填写正确并保持完整，如 政治/地理 )。\n");
                remark.Append("4.教师名称必须填写正确。\n");
                remark.Append("5.禁止修改表结构 \n");

                ISheet sheet = workbook.CreateSheet("我是sheet名称");//创建首行
                IRow rowA = sheet.CreateRow(0);
                rowA.Height = 10 * 200;
                //创建首行首列
                ICell cell = rowA.CreateCell(0);
                //设置单元格样式
                cell.CellStyle = ExcelHelper.SetFontAlign(workbook, "微软雅黑", 14, HSSFColor.Yellow.Index, true, HorizontalAlignment.Left);
                //设置单元格值
                cell.SetCellValue(remark.ToStr());

        //设置单元格对齐方式,字体，背景颜色，多行
        public static ICellStyle SetFontAlign(HSSFWorkbook workbook, string fontStyle = "Arial", short fontSize = 10, short color=HSSFColor.White.Index,bool bol=false)
        {           
            ICellStyle style = workbook.CreateCellStyle();
            IFont font = workbook.CreateFont(); //创建一个字体样式对象       
            font.FontName = fontStyle; //和excel里面的字体对应 
            font.FontHeightInPoints = 12;
            font.FontHeightInPoints = fontSize;
            style.SetFont(font);
            //设置背景颜色
            style.FillForegroundColor = color;
            style.FillPattern = FillPattern.SolidForeground;
            //设置是否多行
            style.WrapText = bol;
            return style;
        }

对于excel 的构造下载，以上可以满足常用需求，当然还有就是如何读取excel 数据,  逻辑是先将上传的文件保存到本地，然后对文件进行操作，当然也可以将文件直接放到内存中进行读取，如下

public JsonResult UpStuLoadFile(HttpPostedFileBase[] uploadFiles)
        {
                //验证上传的excel
                if (uploadFiles == null || uploadFiles.All(x => x == null))
                {
                    return Json(new { Code = "-1", Desc = "请上传excel格式文件" });
                }
                string directoryPath = string.Format("/UploadFiles/Template/{0}/{1}/", DateTime.Now.ToString("yyyyMM"), "Kiaser");
                var directoryServerPath = Server.MapPath(directoryPath);
                //创建文件路径 
                if (!Directory.Exists(directoryServerPath)){
                    Directory.CreateDirectory(directoryServerPath);}
                var errorSuffix = new[] { ".exe" };
                //验证文件后缀
                var fileFullName = uploadFiles[0].FileName;
                var fileNameArr = fileFullName.Split('.');
                if (fileNameArr.Length != 2)
                {
                    return Json(new { Code = "-1", Desc = "请上传excel格式文件" });
                }
                var fileName = fileNameArr[0] + DateTime.Now.Ticks;
                var fileSuffix = string.Format(".{0}", fileNameArr[1]);
                if (errorSuffix.Contains(fileSuffix))
                {
                    return Json(new { Code = "-1", Desc = "请上传excel格式文件" });
                }
                //构造excel完整路径
                var filePath = string.Format("{0}/{1}{2}", directoryPath, fileName, fileSuffix);
                filePath = Server.MapPath(filePath);
                //将excel 保存到本地
                uploadFiles[0].SaveAs(filePath);
                //加载读取 Excel数据
                var dtStudent = ImportStuExcelFile(filePath, gosubjects);
                //....

解析Excel 文件数据

        //解析Excel文件
        public List<DataTable> ImportExcelFile(string filePath)
        {
            //构造表集合
            List<DataTable> dtSubjests = new List<DataTable>();
            IWorkbook workbook = null;
            try
            {
                using (FileStream fs = System.IO.File.OpenRead(filePath))
                {
                    workbook = WorkbookFactory.Create(fs);
                }

                //遍历工作表Sheet
                for (int i = 0; i < workbook.NumberOfSheets; i++)
                {
                    #region 工作表相关数据
                    //当前工作表
                    var currSheet = workbook.GetSheetAt(i);
                    //获取总行数
                    var allRowNum = currSheet.LastRowNum;
                    //获取总列数
                    var allCellNum = currSheet.GetRow(2).LastCellNum; 
                    #endregion

                    #region 构造表列
                    DataTable dt = new DataTable();
                    dt.Columns.Add("Week");   //周
                    dt.Columns.Add("Section");  //课节
                    dt.Columns.Add("ClassName");  //班级
                    dt.Columns.Add("ClassID");  //班级Id
                    dt.Columns.Add("SubjectName");  //科目
                    #endregion
                    
                    //遍历sheet表，读取行，列数据
                    ....
                    //构造行
                    DataRow DRow = dt.NewRow();
                    //构造行中的列
                    DRow[0] = w.ToStr();  //周
                    DRow[1] = s.ToStr();  //课节
                    DRow[2] = "";
                    DRow[3] = "";
                    DRow[4] = "";
                    //表格添加列
                    dt.Rows.Add(DRow);
                }
                return dtSubjests;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }

 

关于Excel 的常用构造，和读取，相关介绍就到这里，以后若碰见额外特别的需求，在做进一步记录.