Npoi填写数据

C#excel表中填写数据

using DevieceSystem.Model.Dtos;
using Microsoft.AspNetCore.Hosting;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;

namespace DevieceSystem.Model.ExportExcel
{
    public class NpoiExcelOperationService
    {
#pragma warning disable CS8618 // 在退出构造函数时，不可为 null 的 字段“_environment”必须包含非 null 值。请考虑将 字段 声明为可以为 null。
        private static IHostingEnvironment _environment;
#pragma warning restore CS8618 // 在退出构造函数时，不可为 null 的 字段“_environment”必须包含非 null 值。请考虑将 字段 声明为可以为 null。

        public NpoiExcelOperationService(IHostingEnvironment iEnvironment)
        {
            _environment = iEnvironment;
        }

        public NpoiExcelOperationService()
        { }

        public static void ExportExcel(string exportPath, TaskInfoDto taskInfo)
        {
#pragma warning disable CS0168 // 声明了变量“e”，但从未使用过
            try
            {
                //首先创建Excel文件对象
                var workbook = new XSSFWorkbook();
                //创建工作表，也就是Excel中的sheet，给工作表赋一个名称(Excel底部名称)
                //sheet.DefaultColumnWidth = 20;//默认列宽
                //sheet.ForceFormulaRecalculation = true;//TODO:是否开始Excel导出后公式仍然有效（非必须）
                CreateSheet("测试结果", workbook, taskInfo);
                string excelPath = exportPath;
                var fileStream = new FileStream(excelPath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                workbook.Write(fileStream);
                fileStream.Close();
                //释放流所占用的资源
                fileStream.Dispose();
            }
            catch (Exception e)
            {
            }
#pragma warning restore CS0168 // 声明了变量“e”，但从未使用过
        }

        private static ISheet CreateSheet(string sheetName, XSSFWorkbook workbook, TaskInfoDto taskInfo)
        {
            if (taskInfo == null)
            {
#pragma warning disable CS8603 // 可能返回 null 引用。
                return null;
#pragma warning restore CS8603 // 可能返回 null 引用。
            }
            //创建工作表，也就是Excel中的sheet，给工作表赋一个名称(Excel底部名称)
            var sheet = workbook.CreateSheet(sheetName);
            //sheet.DefaultColumnWidth = 20;//默认列宽
            sheet.ForceFormulaRecalculation = true;//TODO:是否开始Excel导出后公式仍然有效（非必须）

            #region table 表格内容设置

            #region 标题样式

            //设置顶部大标题样式
            var cellStyleFont = NpoiExcelExportHelper._.CreateStyle(workbook, HorizontalAlignment.Center, VerticalAlignment.Center, 20, true, 700, "楷体", true, false, false, true, FillPattern.SolidForeground, HSSFColor.Blue.Index, HSSFColor.White.Index,
                FontUnderlineType.None, FontSuperScript.None, false);
            //第一行表单
            var row = NpoiExcelExportHelper._.CreateRow(sheet, 0, 28);
            var cell = row.CreateCell(0);
            CellRangeAddress region = new CellRangeAddress(0, 0, 0, 8);
            sheet.AddMergedRegion(region);
            cell.SetCellValue("HQ性能测试");//合并单元格后，只需对第一个位置赋值即可（TODO:顶部标题）
            cell.CellStyle = cellStyleFont;
            //二级标题列样式设置
            var headTopStyle = NpoiExcelExportHelper._.CreateStyle(workbook, HorizontalAlignment.Center, VerticalAlignment.Center, 15, true, 700, "楷体", true, false, false, true, FillPattern.SolidForeground, HSSFColor.Grey25Percent.Index, HSSFColor.Black.Index,
            FontUnderlineType.None, FontSuperScript.None, false);
            //表头名称
            var headerName = new[] { "序号", "任务Id", "任务名称", "用例Id", "用例名称", "标准值", "自动结果", "人工结果", "是否通过" };
            row = NpoiExcelExportHelper._.CreateRow(sheet, 1, 24);//第二行
            for (var i = 0; i < headerName.Length; i++)
            {
                cell = NpoiExcelExportHelper._.CreateCells(row, headTopStyle, i, headerName[i]);
                sheet.SetColumnWidth(i, 5000);
            }

            #endregion 标题样式

            #region 单元格内容信息

            //单元格边框样式
            var cellStyle = NpoiExcelExportHelper._.CreateStyle(workbook, HorizontalAlignment.Center, VerticalAlignment.Center, 10, true, 400);
            var cellStyleCaseRetOk = NpoiExcelExportHelper._.CreateStyle(workbook, HorizontalAlignment.Center, VerticalAlignment.Center, 10, true, 400,fontColor: HSSFColor.Blue.Index, cellBackgroundColor:HSSFColor.LightGreen.Index);
            var cellStyleCaseRetNg = NpoiExcelExportHelper._.CreateStyle(workbook, HorizontalAlignment.Center, VerticalAlignment.Center, 10, true, 400, fontColor: HSSFColor.Red.Index, cellBackgroundColor: HSSFColor.Yellow.Index);
            var currentDate = DateTime.Now;
            int number = 1;
            if (taskInfo.CaseInfos == null)
            {
                return null;
            }
            int rowIdx = 2;
           
            for (var i = 0; i < taskInfo.CaseInfos.Count; i++)
            {

                ICellStyle cell1 = taskInfo.CaseInfos[i].FinnallyResponTime <= taskInfo.CaseInfos[i].StandardResult && taskInfo.CaseInfos[i].FinnallyResponTime != 0 ? cellStyleCaseRetOk: cellStyleCaseRetNg;

                row = NpoiExcelExportHelper._.CreateRow(sheet, rowIdx, 20); //sheet.CreateRow(i+2);//在上面表头的基础上创建行
                cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 0, number.ToString());
                cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 1, taskInfo.Id.ToString());
                cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 2, taskInfo.TaskName.ToString());
                cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 3, taskInfo.CaseInfos[i].Id.ToString());
                cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 4, taskInfo.CaseInfos[i].CaseName.ToString());
                cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 5, taskInfo.CaseInfos[i].StandardResult.ToString());
                cell = NpoiExcelExportHelper._.CreateCells(row, cellStyleCaseRetOk, 6, taskInfo.CaseInfos[i].FinnallyResponTime.ToString());
                cell = NpoiExcelExportHelper._.CreateCells(row, cellStyleCaseRetOk, 7, taskInfo.CaseInfos[i].CheckResponTime.ToString());
                cell = NpoiExcelExportHelper._.CreateCells(row, cell1, 8, taskInfo.CaseInfos[i].FinnallyResponTime <= taskInfo.CaseInfos[i].StandardResult && taskInfo.CaseInfos[i].FinnallyResponTime != 0 ? "Success" : "Fail");
                if (taskInfo.CaseInfos[i].CaseResults!=null)
                {

#pragma warning disable CS8602 // 解引用可能出现空引用。
                    taskInfo.CaseInfos[i].CaseResults.ForEach((caseRet) =>
                    {
                        ICellStyle cell1Case = caseRet.ResponTime!=0 && caseRet.ResponTimeNew!= 0 && caseRet.ResponTimeNew< taskInfo.CaseInfos[i].StandardResult ? cellStyleCaseRetOk : cellStyleCaseRetNg;

                       IRow rowCaseRet = NpoiExcelExportHelper._.CreateRow(sheet, ++rowIdx, 20);
                       NpoiExcelExportHelper._.CreateCells(rowCaseRet, cellStyle, 0, "");
                       NpoiExcelExportHelper._.CreateCells(rowCaseRet, cellStyle, 1, "");
                       NpoiExcelExportHelper._.CreateCells(rowCaseRet, cellStyle, 2, "");
                       NpoiExcelExportHelper._.CreateCells(rowCaseRet, cellStyle, 3, "");
                       NpoiExcelExportHelper._.CreateCells(rowCaseRet, cellStyle, 4, "");
                       NpoiExcelExportHelper._.CreateCells(rowCaseRet, cellStyle, 5, "");
                       NpoiExcelExportHelper._.CreateCells(rowCaseRet, cell1Case, 6, caseRet.ResponTime.ToString());
                       NpoiExcelExportHelper._.CreateCells(rowCaseRet, cell1Case, 7, caseRet.ResponTimeNew.ToString());
                    });
#pragma warning restore CS8602 // 解引用可能出现空引用。
            }
               
                number++;
            }

            #endregion 单元格内容信息

            #endregion table 表格内容设置

            return sheet;
        }
    }
}

---

using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using HorizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment;
using VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment;

namespace DevieceSystem.Model.ExportExcel
{
    internal class NpoiExcelExportHelper
    {
#pragma warning disable CS8618 // 在退出构造函数时，不可为 null 的 字段“_exportHelper”必须包含非 null 值。请考虑将 字段 声明为可以为 null。
        private static NpoiExcelExportHelper _exportHelper;
#pragma warning restore CS8618 // 在退出构造函数时，不可为 null 的 字段“_exportHelper”必须包含非 null 值。请考虑将 字段 声明为可以为 null。

        public static NpoiExcelExportHelper _
        {
            get => _exportHelper ?? (_exportHelper = new NpoiExcelExportHelper());
            set => _exportHelper = value;
        }

        /// <summary>
        /// TODO:先创建行，然后在创建对应的列
        /// 创建Excel中指定的行
        /// </summary>
        /// <param name="sheet">Excel工作表对象</param>
        /// <param name="rowNum">创建第几行(从0开始)</param>
        /// <param name="rowHeight">行高</param>
        public IRow CreateRow(ISheet sheet, int rowNum, float rowHeight)
        {
            IRow row = sheet.CreateRow(rowNum); //创建行
            row.HeightInPoints = rowHeight; //设置列头行高
            return row;
        }

        /// <summary>
        /// 创建行内指定的单元格
        /// </summary>
        /// <param name="row">需要创建单元格的行</param>
        /// <param name="cellStyle">单元格样式</param>
        /// <param name="cellNum">创建第几个单元格(从0开始)</param>
        /// <param name="cellValue">给单元格赋值</param>
        /// <returns></returns>
        public ICell CreateCells(IRow row, ICellStyle cellStyle, int cellNum, string cellValue)
        {
            ICell cell = row.CreateCell(cellNum); //创建单元格
            cell.CellStyle = cellStyle; //将样式绑定到单元格
            if (!string.IsNullOrWhiteSpace(cellValue))
            {
                //单元格赋值
                cell.SetCellValue(cellValue);
            }

            return cell;
        }

        /// <summary>
        /// 行内单元格常用样式设置
        /// </summary>
        /// <param name="workbook">Excel文件对象</param>
        /// <param name="hAlignment">水平布局方式</param>
        /// <param name="vAlignment">垂直布局方式</param>
        /// <param name="fontHeightInPoints">字体大小</param>
        /// <param name="isAddBorder">是否需要边框</param>
        /// <param name="boldWeight">字体加粗 (None = 0,Normal = 400，Bold = 700</param>
        /// <param name="fontName">字体（仿宋，楷体，宋体，微软雅黑...与Excel主题字体相对应）</param>
        /// <param name="isAddBorderColor">是否增加边框颜色</param>
        /// <param name="isItalic">是否将文字变为斜体</param>
        /// <param name="isLineFeed">是否自动换行</param>
        /// <param name="isAddCellBackground">是否增加单元格背景颜色</param>
        /// <param name="fillPattern">填充图案样式(FineDots 细点，SolidForeground立体前景，isAddFillPattern=true时存在)</param>
        /// <param name="cellBackgroundColor">单元格背景颜色（当isAddCellBackground=true时存在）</param>
        /// <param name="fontColor">字体颜色</param>
        /// <param name="underlineStyle">下划线样式（无下划线[None],单下划线[Single],双下划线[Double],会计用单下划线[SingleAccounting],会计用双下划线[DoubleAccounting]）</param>
        /// <param name="typeOffset">字体上标下标(普通默认值[None],上标[Sub],下标[Super]),即字体在单元格内的上下偏移量</param>
        /// <param name="isStrikeout">是否显示删除线</param>
        /// <returns></returns>
        public ICellStyle CreateStyle(IWorkbook workbook, HorizontalAlignment hAlignment, VerticalAlignment vAlignment, short fontHeightInPoints, bool isAddBorder, short boldWeight, string fontName = "宋体", bool isAddBorderColor = true, bool isItalic = false, bool isLineFeed = false, bool isAddCellBackground = false, FillPattern fillPattern = FillPattern.NoFill, short cellBackgroundColor = HSSFColor.Yellow.Index, short fontColor = HSSFColor.Black.Index, FontUnderlineType underlineStyle =
            FontUnderlineType.None, FontSuperScript typeOffset = FontSuperScript.None, bool isStrikeout = false)
        {
            var cellStyle = workbook.CreateCellStyle(); //创建列头单元格实例样式
            cellStyle.Alignment = hAlignment; //水平居中
            cellStyle.VerticalAlignment = vAlignment; //垂直居中
            cellStyle.WrapText = isLineFeed;//自动换行

            //背景颜色，边框颜色，字体颜色都是使用 HSSFColor属性中的对应调色板索引，关于 HSSFColor 颜色索引对照表，详情参考：https://www.cnblogs.com/Brainpan/p/5804167.html

            //TODO：引用了NPOI后可通过ICellStyle 接口的 FillForegroundColor 属性实现 Excel 单元格的背景色设置，FillPattern 为单元格背景色的填充样式

            //TODO:十分注意，要设置单元格背景色必须是FillForegroundColor和FillPattern两个属性同时设置，否则是不会显示背景颜色
            if (isAddCellBackground)
            {
                cellStyle.FillForegroundColor = cellBackgroundColor;//单元格背景颜色
                cellStyle.FillPattern = fillPattern;//填充图案样式(FineDots 细点，SolidForeground立体前景)
            }

            //是否增加边框
            if (isAddBorder)
            {
                //常用的边框样式 None(没有),Thin(细边框，瘦的),Medium(中等),Dashed(虚线),Dotted(星罗棋布的),Thick(厚的),Double(双倍),Hair(头发)[上右下左顺序设置]
                cellStyle.BorderBottom = BorderStyle.Thin;
                cellStyle.BorderRight = BorderStyle.Thin;
                cellStyle.BorderTop = BorderStyle.Thin;
                cellStyle.BorderLeft = BorderStyle.Thin;
            }

            //是否设置边框颜色
            if (isAddBorderColor)
            {
                //边框颜色[上右下左顺序设置]
                cellStyle.TopBorderColor = HSSFColor.DarkGreen.Index;//DarkGreen(黑绿色)
                cellStyle.RightBorderColor = HSSFColor.DarkGreen.Index;
                cellStyle.BottomBorderColor = HSSFColor.DarkGreen.Index;
                cellStyle.LeftBorderColor = HSSFColor.DarkGreen.Index;
            }

            /**
             * 设置相关字体样式
             */
            var cellStyleFont = workbook.CreateFont(); //创建字体

            //假如字体大小只需要是粗体的话直接使用下面该属性即可
            //cellStyleFont.IsBold = true;

#pragma warning disable CS0618 // “IFont.Boldweight”已过时:“deprecated POI 3.15 beta 2. Use IsBold instead.”
            cellStyleFont.Boldweight = boldWeight; //字体加粗
#pragma warning restore CS0618 // “IFont.Boldweight”已过时:“deprecated POI 3.15 beta 2. Use IsBold instead.”
            cellStyleFont.FontHeightInPoints = fontHeightInPoints; //字体大小
            cellStyleFont.FontName = fontName;//字体（仿宋，楷体，宋体 ）
            cellStyleFont.Color = fontColor;//设置字体颜色
            cellStyleFont.IsItalic = isItalic;//是否将文字变为斜体
            cellStyleFont.Underline = underlineStyle;//字体下划线
            cellStyleFont.TypeOffset = typeOffset;//字体上标下标
            cellStyleFont.IsStrikeout = isStrikeout;//是否有删除线

            cellStyle.SetFont(cellStyleFont); //将字体绑定到样式
            return cellStyle;
        }
    }
}