彩票走势图

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

原创|使用教程|编辑:何跃|2022-01-17 14:36:27.830|阅读 154 次

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

# 慧都年终大促·界面/图表报表/文档/IDE等千款热门软控件火热促销中 >>


准备工作

首先,下载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);

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


标签:

本站文章除注明转载外,均为本站原创或翻译。欢迎任何形式的转载,但请务必注明出处、不得修改原文相关链接,如果存在内容上的异议请邮件反馈至chenjj@pclwef.cn


为你推荐

  • 推荐视频
  • 推荐活动
  • 推荐产品
  • 推荐文章
  • 慧都慧问
扫码咨询


添加微信 立即咨询

电话咨询

客服热线
023-68661681

TOP