记录NPOI导出Excel并使用不同颜色标注特定条件相同的行

一、环境概述

开发工具：Visual Studio 2022
.Net环境：.Net Core 6.0
相关依赖：NPOI

二、基本概述

需求要实现导出的数据里如果连续行满足某个条件就用颜色标记，但是标记的区间要用不同颜色区分开，用了双指针，但是是记录了起始位置和偏移量，封装比较屎，这里主要记录一下思想。

三、代码实现
1、核心代码

        /// <summary>
        /// 用于获取线性表中需要标记的坐标(起始位置+偏移量)
        /// </summary>
        /// <param name="WorkBook"></param>
        /// <param name="ExportData"></param>
        /// <param name="comparer"></param>
        public static Dictionary<int,int>  GetMarkPosition(DataTable ExportData, Func<DataRow, DataRow, bool> comparer) {
            Dictionary<int, int> PositionDic = new Dictionary<int, int>();
            if (ExportData == null || ExportData.Rows.Count == 0)
            {
                return PositionDic;
            }
            int RowCount = ExportData.Rows.Count;
            int StartRow = 0;//开始指针
            int CurrentLength = 0;//偏移量
            for (int i = 1; i < RowCount; i++)
            {
                if (comparer(ExportData.Rows[i], ExportData.Rows[i - 1]))
                {
                    CurrentLength++;
                }
                else
                {
                    if (CurrentLength > 0)
                    {
                        PositionDic.Add(StartRow, CurrentLength);
                    }
                    StartRow = i;
                    CurrentLength = 0;
                }
            }
            if (CurrentLength > 0)
            {
                PositionDic.Add(StartRow, CurrentLength);
            }
            return PositionDic;
        }

        /// <summary>
        /// 用于标记连续相等的行
        /// </summary>
        /// <param name="WorkBook">用于获取样式的</param>
        /// <param name="Sheet">标记操作作用的Excel表</param>
        /// <param name="ExportData">数据</param>
        /// <param name="comparer">自定义的比较器</param>
        /// <param name="ColorAction">自定义的标记操作</param>
        public static void MarkSameRow(IWorkbook WorkBook,ISheet Sheet,DataTable ExportData, Func<DataRow, DataRow, bool> comparer,Action<IWorkbook,ISheet, KeyValuePair<int, int>,bool> ColorAction) {
            Dictionary<int,int> PositionDic = GetMarkPosition(ExportData, comparer);
            if (PositionDic==null||PositionDic.Count()==0) {
                return;
            }
            bool change = false;
            foreach (var item in PositionDic) {
                change = !change;
                ColorAction(WorkBook,Sheet,item, change);
            }
        }

2、实际使用

      //自定义的行比较的逻辑，作为委托参数
      private bool CompareRowData(DataRow CurRow, DataRow PreRow)
        {
            bool IsEquals = false;
            if (CurRow == null || PreRow == null)
            {
                return IsEquals;
            }
            string CurValue1 = Convert.ToString(CurRow[0]) ?? string.Empty;
            string CurValue2 = Convert.ToString(CurRow[1]) ?? string.Empty;

            string PreValue1 = Convert.ToString(PreRow[0]) ?? string.Empty;
            string PreValue2 = Convert.ToString(PreRow[1]) ?? string.Empty;
            if (CurValue1.Equals(PreValue1) && CurValue2.Equals(PreValue2))
            {
                IsEquals = true;
                return IsEquals;
            }
            return IsEquals;
        }
        //自定义的填充颜色的逻辑，作为委托参数
        private void FillColor(IWorkbook WorkBook, ISheet Sheet, KeyValuePair<int, int> Position, bool IsChange)
        {
            if (WorkBook == null)
            {
                return;
            }
            XSSFColor Color1 = ExcelUtils.GetXSSFColorWithRGB(68, 179, 225);
            XSSFColor Color2 = ExcelUtils.GetXSSFColorWithRGB(166, 166, 166);

            XSSFCellStyle ColorStyle1 = ExcelUtils.GetBGColorStyle(WorkBook, Color1);
            XSSFCellStyle ColorStyle2 = ExcelUtils.GetBGColorStyle(WorkBook, Color2);
            if (Sheet == null)
            {
                return;
            }
            for (int i = Position.Key + 2; i < Position.Key + Position.Value + 3; i++)
            {
                for(int j = 0; j < 9; j++) {
                    ICell Cell = ExcelUtils.GetCell(Sheet, i, j);
                    Cell.CellStyle = IsChange ? ColorStyle1 : ColorStyle2;
                }
            }
        }
        //实际使用
        ExcelUtils.MarkSameRow(WorkBook, Sheet, ExportData, CompareRowData, FillColor);
        

三、效果展示