1 usingSystem;2 usingSystem.Collections.Generic;3 usingSystem.Linq;4 usingSystem.Text;5 usingSystem.Threading.Tasks;6 usingSystem.ComponentModel;7 usingSystem.Data;8 usingSystem.Drawing;9 usingSystem.Collections;10 usingSystem.IO;11 usingNPOI.HSSF.UserModel;12 usingNPOI.HPSF;13 usingNPOI.HSSF.Util;14 usingNPOI.POIFS.FileSystem;15 usingNPOI.SS.UserModel;16 usingNPOI.XSSF.UserModel;17
18 namespaceExcelFillColor19 {20 ///
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 ///
27 public classWorkBook28 {29 public static void CreateExcelFile2007(ListtoFillColors)30 {31
32
33 var wb = newXSSFWorkbook();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 = newXSSFColor(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 = newFileStream(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(ListtoFillColors)78 {79
80
81 var wb = newHSSFWorkbook();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 = newFileStream(savePath, FileMode.OpenOrCreate, FileAccess.Write))126 {127 wb.Write(fs);128
129 fs.Close();130 }131
132
133 }134
135 }136
137 }