NPOI 设置Excel2003/2007+填充色（自定义的Html 格式的色值）

调用示范：

https://gitee.com/henduanqiushui/ExcelFillColor

 

 

代码核心：

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Text;
  5 using System.Threading.Tasks;
  6 using System.ComponentModel;
  7 using System.Data;
  8 using System.Drawing;
  9 using System.Collections;
 10 using System.IO;
 11 using NPOI.HSSF.UserModel;
 12 using NPOI.HPSF;
 13 using NPOI.HSSF.Util;
 14 using NPOI.POIFS.FileSystem;
 15 using NPOI.SS.UserModel;
 16 using NPOI.XSSF.UserModel;
 17 
 18 namespace ExcelFillColor
 19 {
 20     /// <summary>
 21     /// NPOI 设置Excel 填充色（自定义的Html 格式的色值）
 22     /// 参考文献：https://www.codota.com/code/java/methods/org.apache.poi.hssf.usermodel.HSSFWorkbook/getCustomPalette
 23     /// https://stackoverflow.com/questions/10528516/poi-setting-cell-background-to-a-custom-color
 24     /// https://www.cnblogs.com/mq0036/p/9835975.html
 25     /// https://stackoverflow.com/questions/22687901/custom-color-for-icellstyle-fillforegroundcolor-than-provided-named-colors
 26     /// </summary>
 27     public class WorkBook
 28     {
 29         public static void CreateExcelFile2007(List<string> toFillColors)
 30         {
 31 
 32 
 33             var wb = new XSSFWorkbook();
 34 
 35             var sheet = wb.CreateSheet("第一页");
 36 
 37 
 38 
 39             for (int i = 0; i < toFillColors.Count; i++)
 40             {
 41 
 42                 var row = sheet.CreateRow(i);
 43 
 44                 var cell = row.CreateCell(0);
 45                 cell.SetCellValue("第一页第一行");
 46 
 47                 //设置单元格样式
 48                 XSSFCellStyle cellStyle = (XSSFCellStyle)wb.CreateCellStyle();
 49 
 50                 var itemColorStr = toFillColors[i];
 51                 Color co = System.Drawing.ColorTranslator.FromHtml(itemColorStr);
 52 
 53                 XSSFColor xssfColor = new XSSFColor(co);
 54                 cellStyle.SetFillForegroundColor(xssfColor);
 55                 cellStyle.FillPattern = FillPattern.SolidForeground;
 56 
 57                 cell.CellStyle = cellStyle;
 58             }
 59 
 60 
 61 
 62             //save
 63 
 64             string savePath = $"Demo-{DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff")}.xlsx";
 65 
 66             using (FileStream fs = new FileStream(savePath, FileMode.OpenOrCreate, FileAccess.Write))
 67             {
 68                 wb.Write(fs);
 69 
 70                 fs.Close();
 71             }
 72 
 73 
 74             int a = 0;
 75         }
 76 
 77         public static void CreateExcelFile2003(List<string> toFillColors)
 78         {
 79 
 80 
 81             var wb = new HSSFWorkbook();
 82             HSSFPalette palette = wb.GetCustomPalette();
 83             var sheet = wb.CreateSheet("第一页");
 84 
 85 
 86 
 87             for (int i = 0; i < toFillColors.Count; i++)
 88             {
 89 
 90                 var row = sheet.CreateRow(i);
 91 
 92                 var cell = row.CreateCell(0);
 93                 cell.SetCellValue("第一页第一行");
 94 
 95                 //设置单元格样式
 96                 ICellStyle cellStyle = wb.CreateCellStyle();
 97              
 98                 var itemColorStr = toFillColors[i];
 99                 Color co = System.Drawing.ColorTranslator.FromHtml(itemColorStr);
100                 HSSFColor myColor = palette.FindColor(co.R, co.G, co.B);
101                 if (null== myColor)
102                 {
103                     //最多支持 56个设置区间
104                     //参考：https://www.cnblogs.com/yxhblog/p/6225018.html
105                     short idx = (short)(8 + i);
106                     if (idx >= 64)
107                     {
108                         throw new Exception("colors  max  size is : 56 ");
109                     }
110 
111                     palette.SetColorAtIndex(idx, co.R, co.G, co.B);
112                     myColor = palette.FindColor(co.R, co.G, co.B);
113                 }
114 
115                 cellStyle.FillForegroundColor = myColor.Indexed;
116                 cellStyle.FillPattern = FillPattern.SolidForeground;
117 
118 
119                 cell.CellStyle = cellStyle;
120             }
121 
122             //save
123             string savePath = $"Demo-{DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff")}.xls";
124 
125             using (FileStream fs = new FileStream(savePath, FileMode.OpenOrCreate, FileAccess.Write))
126             {
127                 wb.Write(fs);
128 
129                 fs.Close();
130             }
131 
132 
133         }
134 
135     }
136 
137 }

 

posted @ 2020-06-09 20:17  特洛伊-Micro  阅读(781)  评论(0)  编辑  收藏  举报