C#后端代码处理excel，按条件给单元格设置格式

有时候在一些自定义场景下我们需要给excel表格打上标签格式，比如说财务想给每个月花费最高和最低员工打上标记，在输出表格时内容就已经处理完毕。

准备工作

首先，下载Spire.xls功能类库 ，点击这里下载 ；

然后，在后端代码引入命名空间。

using Spire.Xls;
using Spire.Xls.Core.Spreadsheet.ConditionalFormatting;
using Spire.Xls.Core.Spreadsheet.Collections;
using Spire.Xls.Core;

按条件设置单元格样式

            Workbook workbook = new Workbook();            
            workbook.LoadFromFile(@"..\..\..\..\..\..\Data\ConditionalFormatting.xlsx");

             //按索引指定sheet表
            Worksheet sheet = workbook.Worksheets[0];

            sheet.AllocatedRange.RowHeight = 15;
            sheet.AllocatedRange.ColumnWidth = 16;

            //创建条件格式样式
            XlsConditionalFormats xcfs1 = sheet.ConditionalFormats.Add();
            xcfs1.AddRange(sheet.Range["A1:D1"]);
            IConditionalFormat cf1 = xcfs1.AddCondition();
            cf1.FormatType = ConditionalFormatType.CellValue;
            cf1.FirstFormula = "150";
            cf1.Operator = ComparisonOperatorType.Greater;
            cf1.FontColor = Color.Red;
            cf1.BackColor = Color.LightBlue;

            XlsConditionalFormats xcfs2 = sheet.ConditionalFormats.Add();
            xcfs2.AddRange(sheet.Range["A2:D2"]);
            IConditionalFormat cf2 = xcfs2.AddCondition();
            cf2.FormatType = ConditionalFormatType.CellValue;
            cf2.FirstFormula = "300";
            cf2.Operator = ComparisonOperatorType.Less;
            //设置边框
            cf2.LeftBorderColor = Color.Pink;
            cf2.RightBorderColor = Color.Pink;
            cf2.TopBorderColor = Color.DeepSkyBlue;
            cf2.BottomBorderColor = Color.DeepSkyBlue;
            cf2.LeftBorderStyle = LineStyleType.Medium;
            cf2.RightBorderStyle = LineStyleType.Thick;
            cf2.TopBorderStyle = LineStyleType.Double;
            cf2.BottomBorderStyle = LineStyleType.Double;

            //添加数据条
            XlsConditionalFormats xcfs3 = sheet.ConditionalFormats.Add();
            xcfs3.AddRange(sheet.Range["A3:D3"]);
            IConditionalFormat cf3 = xcfs3.AddCondition();
            cf3.FormatType = ConditionalFormatType.DataBar;
            cf3.DataBar.BarColor = Color.CadetBlue;

            //添加icon
            XlsConditionalFormats xcfs4 = sheet.ConditionalFormats.Add();
            xcfs4.AddRange(sheet.Range["A4:D4"]);
            IConditionalFormat cf4 = xcfs4.AddCondition();
            cf4.FormatType = ConditionalFormatType.IconSet;
            cf4.IconSet.IconSetType = IconSetType.ThreeTrafficLights1;

            //添加颜色
            XlsConditionalFormats xcfs5 = sheet.ConditionalFormats.Add();
            xcfs5.AddRange(sheet.Range["A5:D5"]);
            IConditionalFormat cf5 = xcfs5.AddCondition();
            cf5.FormatType = ConditionalFormatType.ColorScale;
            
            //在 "A7:D7 "范围内用BurlyWood颜色突出重复的数值
            XlsConditionalFormats xcfs6 = sheet.ConditionalFormats.Add();
            xcfs6.AddRange(sheet.Range["A6:D6"]);
            IConditionalFormat cf6 = xcfs6.AddCondition();
            cf6.FormatType = ConditionalFormatType.DuplicateValues;
            cf6.BackColor = Color.BurlyWood;

            //保存
            workbook.SaveToFile("Output.xlsx", ExcelVersion.Version2010);

以上内容设置，我们可以在应用程序中为数据贴标签后输出，效率会比输出后人工打标签更为快捷。