一、环境概述

开发工具：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);

3、注意

-- 如果你用的XLS结尾的Excel你需要用这个来设置背景颜色
HSSFPalette palette = ((HSSFWorkbook)WorkBook).GetCustomPalette();
IColor color= palette.FindSimilarColor(190, 231, 233);
HSSFCellStyle cellStyle = (HSSFCellStyle)WorkBook.CreateCellStyle();
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.FillForegroundColor = color.Indexed;
cellStyle.FillPattern = FillPattern.SolidForeground;

-- 如果你用的是XLSX结尾的Excel你需要使用这个来设置背景颜色
 XSSFColor XSSFColor = new XSSFColor();
 byte[] ColorRGB = { R, G, B };-- R,G,B是三个byte范围的数值，这个可以自定义的。
 XSSFColor.SetRgb(ColorRGB);
XSSFCellStyle ColorStyle = (XSSFCellStyle)WorkBook.CreateCellStyle();
ColorStyle.FillForegroundColorColor = XSSFColor ;
ColorStyle.FillPattern = FillPattern.SolidForeground;
ColorStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
ColorStyle.VerticalAlignment = VerticalAlignment.Center;
ColorStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
ColorStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
ColorStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
ColorStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
二者如果混用会报错

-- 还有一个坑就是你读取模板的时候如果有一行是空白的，你想删除这一行
比如我有读取一个XLS的模板想删除第一行（不知道制作模板的傻逼设置了什么，填不进去数据，所以直接删了），
你要使用RemoveRow只能删除内容，你要用Sheet.ShiftRows(4, RowCount + 5, -1);就是从第四行到最后一行所有行往上移动一行，然后这一行会放到最后去，贼鸡儿傻逼。

三、效果展示