C# 导出Excel NPOI 修改指定单元格的样式 或者行样式

参考文章：原文链接：https://blog.csdn.net/chensirbbk/article/details/52189985

#region 2.NPOI读取Excel 验证Excel数据的有效性(非空) 并修改指定单元格样式
IWorkbook workbook = null;
ISheet sheet = null;
ArrayList questionRowIndex = new ArrayList();/*收集出现问题行的索引*/


using (FileStream fs = System.IO.File.Open(readExcelPath, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite))
{
    #region 选择解析方式
    if (dataLog.ExcelName.IndexOf(".xlsx") > 0)
    {
        workbook = new XSSFWorkbook(fs);
    }
    else if (dataLog.ExcelName.IndexOf(".xls") > 0)
    {
        workbook = new HSSFWorkbook(fs);
    }
    #endregion


    #region 核验数值列数据是否为空 并记录为空索引行 修改Excel指定索引行后重新保存


    sheet = workbook.GetSheetAt(0);/*指定数据格式只读取索引值为0的第一个sheet*/
    IRow row = null;
    for (int j = 1; j < sheet.PhysicalNumberOfRows && sheet.GetRow(j) != null; j++)/*j=1 从索引的第一行开始过滤掉表头*/
    {
        row = sheet.GetRow(j);
        if (string.IsNullOrWhiteSpace(row.GetCell(5).ToString()))/*验证数值非空*/
        {
            questionRowIndex.Add(j);


            /*修改样式关键代码*/
            ICellStyle style = workbook.CreateCellStyle();
            style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
            style.FillPattern = FillPattern.SolidForeground;
            style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;

            /*修改指定单元格样式 如果要修改行样式则需要将row.Cells.Count循环出来，挨个设置！*/

            row.Cells[5].CellStyle = style;

            //for(int i=0;i<row.Cells.Count;i++)

            //{
            // row.Cells[i].CellStyle = style;

            //}



            /*重新修改文件指定单元格样式*/
            FileStream fs1 = 
             System.IO.File.OpenWrite(readExcelPath);
            workbook.Write(fs1);
            fs1.Close();
        }
    }
    #endregion
}

 

实践代码：

 private byte[] GetMonthBudgetInfoReport(List<MonthBudgetResponse> list)
        {
            try
            {

                string modelExlPath = Directory.GetCurrentDirectory() + "\\Template\\Equipment\\EquipmentMonthBudget.xlsx";
                if (System.IO.File.Exists(modelExlPath) == false)//模板不存在
                {
                    modelExlPath = AppContext.BaseDirectory + "Template\\Equipment\\EquipmentMonthBudget.xlsx";
                    if (System.IO.File.Exists(modelExlPath) == false)//模板不存在
                    {
                        throw new FriendlyException("未找到模板");
                    }
                }
                IWorkbook workBook = null;
                using (FileStream file = new FileStream(modelExlPath, FileMode.Open, FileAccess.Read))
                {
                    workBook = new XSSFWorkbook(file);
                    file.Close();
                }

                XSSFSheet sheet = (XSSFSheet)workBook.GetSheetAt(0);



                //特定单元格样式 右对齐
                ICellStyle cellstyle = workBook.CreateCellStyle();
                cellstyle.Alignment = HorizontalAlignment.Right;
                cellstyle.BorderTop = BorderStyle.Thin;
                cellstyle.BorderBottom = BorderStyle.Thin;
                cellstyle.BorderLeft = BorderStyle.Thin;
                cellstyle.BorderRight = BorderStyle.Thin;

                //设置所有单元格统一的样式  添加边框线
                ICellStyle allcellstyle = workBook.CreateCellStyle();
                allcellstyle.BorderTop = BorderStyle.Thin;
                allcellstyle.BorderBottom = BorderStyle.Thin;
                allcellstyle.BorderLeft = BorderStyle.Thin;
                allcellstyle.BorderRight = BorderStyle.Thin;



                var rowIndex = 1;
                var cellIndex = 0;
                if (list != null && list.Count > 0)
                {
                    list.ForEach(x =>
                    {
                        IRow row = sheet.CreateRow(rowIndex);
                        row.HeightInPoints = 10 * 2;
                        row.CreateCell(cellIndex++).SetCellValue(x.ProjectName);
                        row.CreateCell(cellIndex++).SetCellValue(x.BudgetMonth.Value.ToString("yyyy-MM"));
                        row.CreateCell(cellIndex++).SetCellValue(x.TotalBuildAera.ToString());
                        row.CreateCell(cellIndex++).SetCellValue(x.BuildAera.ToString());
                        row.CreateCell(cellIndex++).SetCellValue(String.Format("{0:N2}", x.TotalBudget.Value));
                        row.CreateCell(cellIndex++).SetCellValue(String.Format("{0:N2}", x.MonthBudget.Value));

                        rowIndex++;
                        cellIndex = 0;
                    });
                }

                #region 设置单元格样式 
                //行
                for (int i = 1; i <= list.Count; i++)
                {
                    //列
                    for (int j = 0; j < 6; j++)
                    {

                        sheet.GetRow(i).Cells[j].CellStyle = allcellstyle;
                    }
                }


                //设置单元格样式
                for (int i = 1; i <= list.Count; i++)
                {
                    sheet.GetRow(i).Cells[2].CellStyle = cellstyle;
                    sheet.GetRow(i).Cells[3].CellStyle = cellstyle;
                    sheet.GetRow(i).Cells[4].CellStyle = cellstyle;
                    sheet.GetRow(i).Cells[5].CellStyle = cellstyle;
                }

                #endregion

                MemoryStream ms = new MemoryStream();
                workBook.Write(ms);
                return ms.ToArray();

            }
            catch (Exception ex)
            {
                throw new FriendlyException("导出数据失败");
            }
        }

 https://blog.csdn.net/qq_39541254/article/details/107935095