C#使用NPOI进行xlsx的读写（项目案例）

        /// <summary>
        /// 导出exec
        /// </summary>
        /// <returns></returns>
        public async Task<ActionResult> ExportExamRecord()
        {
            // 数据源
            var model = new ExportExamExecModel();
            // 方法
            var wk = EquipWordHelper.ExportExamRecordExec(model);
            using (MemoryStream ms = new MemoryStream())
            {
                wk.Write(ms);
                //文件名称
                string fileName = $"{model.ProjectName}-{model.Name}-{model.TitleName}-{ DateTime.Now.ToString("yyyy-MM-dd")}.xlsx";
                return File(ms.ToArray(), System.Net.Mime.MediaTypeNames.Application.Octet, fileName);
            }
        }

处理方法-（ExportExamRecordExec）注意：.xlsx----XSSFWorkbook    .xls-----HSSFWorkbook

      private static readonly string Folder = HttpContext.Current.Server.MapPath("~/File/Template/");

        public static HSSFWorkbook ExportExamRecordExec(ExportExamExecModel model)
        {
            var template = Folder + "Exam.xls";
            using (FileStream fs = new FileStream(template, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                HSSFWorkbook wk = new HSSFWorkbook(fs);
                FillContentExamRecord(model, wk);
                return wk;
            }

        }

 public static void FillContentExamRecord(ExportExamExecModel model, HSSFWorkbook wk)
        {
            var rowold = 5;
            var rownum = 4;
            ISheet sheet = wk.GetSheetAt(0);
            for (int rowIndex = 0; rowIndex <= 5; rowIndex++)
            {
                // 1.判断当前行是否空行，若空行就不在进行读取下一行操作，结束Excel读取操作
                IRow row = sheet.GetRow(rowIndex);
                if (row != null)
                {
                    if (rowIndex == 0)
                    {
                        var cell = row.GetCell(0);
                        cell.SetCellValue(model.TitleName);
                    }
                    if (rowIndex == 1)
                    {
                        row.GetCell(1).SetCellValue(model.ProjectName);//项目名称
                        row.GetCell(7).SetCellValue(model.EquipSupplierName);//供应商名称
                    }
                    else if (rowIndex == 2)
                    {
                        row.GetCell(1).SetCellValue(model.Name);//姓名
                        row.GetCell(7).SetCellValue(model.JobName);//工种
                    }
                    else if (rowIndex == 3)
                    {
                        row.GetCell(1).SetCellValue(model.CommitTimeStr);//考试时间
                        row.GetCell(7).SetCellValue(model.ExamScore);//得分
                    }
                    else if (rowIndex == 5)
                    {
                        sheet.ShiftRows(5, sheet.LastRowNum, model.ExamInfoList.Count() - 1, true, false);
                        var rowStyle = sheet.GetRow(4);//获取当前行样式
                        var xzlist = model.ExamInfoList.Where(x => x.TopicType == 1).OrderBy(x => x.Sort).ToList();
                        var xzsort = 1;
                        foreach (var item in xzlist)
                        {
                            rownum += 1;
                            // 添加行
                            var rowInsert = sheet.CreateRow(rownum);

                            rowInsert.HeightInPoints = 80; //设置列头行高

                            // 添加列
                            for (int i = 0; i <= 11; i++)
                            {
                                var sourceCell = rowStyle.GetCell(i);
                                var cell2 = rowInsert.CreateCell(i);
                                cell2.CellStyle = sourceCell.CellStyle;
                                cell2.SetCellType(sourceCell.CellType);
                            }
                            if (rownum != 4)
                            {
                                // 合并行/列
                                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rownum, rownum, 2, 5));
                                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rownum, rownum, 6, 8));
                            }

                            rowInsert.GetCell(0).SetCellValue(xzsort);//序号
                            xzsort += 1;
                            rowInsert.GetCell(1).SetCellValue(item.TopicTypeStr);//类型
                            rowInsert.GetCell(2).SetCellValue(item.TopicContent);//考试题目
                            var rqvalule = item.TopicXXContent.Replace("<br>", "\n");
                            rowInsert.GetCell(6).SetCellValue(rqvalule);//考试选项
                            rowInsert.GetCell(9).SetCellValue(item.TopicAnswer);//正确选项
                            rowInsert.GetCell(10).SetCellValue(item.CommitTopicAnswer);//考试人选项
                            rowInsert.GetCell(11).SetCellValue(item.TopiResult);//考试结果
                        }
                        // 合并选择项
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowold, rownum, 1, 1));

                        var pcrowold = rownum + 1;
                        var pclist = model.ExamInfoList.Where(x => x.TopicType == 2).OrderBy(x => x.Sort).ToList();
                        var pdsort = 1;
                        foreach (var item in pclist)
                        {
                            rownum += 1;
                            var rowInsert = sheet.CreateRow(rownum);

                            rowInsert.HeightInPoints = 80; //设置列头行高

                            for (int i = 0; i <= 11; i++)
                            {
                                var sourceCell = rowStyle.GetCell(i);
                                var cell2 = rowInsert.CreateCell(i);
                                cell2.CellStyle = sourceCell.CellStyle;
                                cell2.SetCellType(sourceCell.CellType);
                            }
                            if (rownum != 4)
                            {
                                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rownum, rownum, 2, 5));
                                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rownum, rownum, 6, 8));
                            }

                            rowInsert.GetCell(0).SetCellValue(pdsort);//序号
                            pdsort += 1;
                            rowInsert.GetCell(1).SetCellValue(item.TopicTypeStr);//类型
                            rowInsert.GetCell(2).SetCellValue(item.TopicContent);//考试题目
                            var rqvalule = item.TopicXXContent.Replace("<br>", "\n");
                            rowInsert.GetCell(6).SetCellValue(rqvalule);//考试选项
                            rowInsert.GetCell(9).SetCellValue(item.TopicAnswer);//正确选项
                            rowInsert.GetCell(10).SetCellValue(item.CommitTopicAnswer);//考试人选项
                            rowInsert.GetCell(11).SetCellValue(item.TopiResult);//考试结果
                        }
                        // 合并判断项
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(pcrowold, rownum, 1, 1));
                    }
                }
            }
        }

文件模板

 

导出结果

 

附加：增加字体颜色，增加背景颜色

  // 设置列的宽度
            ISheet sheet = workbook.GetSheet(sheetName);
            sheet.SetColumnWidth(rowoneCumMn, 20 * 256);

 // 已经知道是那一行了-设置指定列的背景颜色
                                var cell = dataRow.CreateCell(rows++);
                                ICellStyle style = workbook.CreateCellStyle();
                                style.FillForegroundColor = IndexedColors.Grey25Percent.Index;
                                style.FillPattern = FillPattern.SolidForeground;
                                cell.CellStyle = style;

   //  已经知道是那一行了--设置指定列字体颜色
                    var cqdc = dataRow.CreateCell(rows++);
                    cqdc.SetCellValue(jstimestr);
                    IFont font = workbook.CreateFont();
                    font.Color = IndexedColors.Red.Index;
                    ICellStyle style = workbook.CreateCellStyle();
                    style.SetFont(font);
                    cqdc.CellStyle = style;