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(@"............DataConditionalFormatting.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);

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

标签：

来源：慧都