JAVA poi设置单元格背景颜色

时间：2023-01-14 19:16:04
  1. import java.io.FileOutputStream;
  2. import java.io.IOException;
  3.  
  4. import org.apache.poi.ss.usermodel.Cell;
  5. import org.apache.poi.ss.usermodel.CellStyle;
  6. import org.apache.poi.ss.usermodel.IndexedColors;
  7. import org.apache.poi.ss.usermodel.Row;
  8. import org.apache.poi.ss.usermodel.Sheet;
  9. import org.apache.poi.ss.usermodel.Workbook;
  10. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  11.  
  12. public class POIFillAndColorExample {
  13. public static void main(String[] args) throws IOException {
  14. // Create a workbook object
  15. Workbook workbook = new XSSFWorkbook();
  16. // Create sheet
  17. Sheet sheet = workbook.createSheet();
  18.  
  19. // Create a row and put some cells in it.
  20. Row row = sheet.createRow((short) 1);
  21.  
  22. // Aqua background
  23. CellStyle style = workbook.createCellStyle();
  24. style.setFillForegroundColor(IndexedColors.AQUA.getIndex());
  25. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  26. Cell cell = row.createCell((short) 1);
  27. cell.setCellValue("X1");
  28. cell.setCellStyle(style);
  29.  
  30. // Orange "foreground", foreground being the fill foreground not the
  31. // font color.
  32. style = workbook.createCellStyle();
  33. style.setFillForegroundColor(IndexedColors.AUTOMATIC.getIndex());
  34. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  35. cell = row.createCell((short) 2);
  36. cell.setCellValue("X2");
  37. cell.setCellStyle(style);
  38.  
  39. style = workbook.createCellStyle();
  40. style.setFillForegroundColor(IndexedColors.BLUE.getIndex());
  41. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  42. cell = row.createCell((short) 3);
  43. cell.setCellValue("X3");
  44. cell.setCellStyle(style);
  45.  
  46. style = workbook.createCellStyle();
  47. style.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
  48. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  49. cell = row.createCell((short) 4);
  50. cell.setCellValue("X4");
  51. cell.setCellStyle(style);
  52.  
  53. style = workbook.createCellStyle();
  54. style.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.getIndex());
  55. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  56. cell = row.createCell((short) 5);
  57. cell.setCellValue("X5");
  58. cell.setCellStyle(style);
  59.  
  60. // Create a row and put some cells in it.
  61. Row row2 = sheet.createRow((short) 2);
  62.  
  63. style = workbook.createCellStyle();
  64. style.setFillForegroundColor(IndexedColors.BROWN.getIndex());
  65. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  66. cell = row2.createCell((short) 1);
  67. cell.setCellValue("X6");
  68. cell.setCellStyle(style);
  69.  
  70. style = workbook.createCellStyle();
  71. style.setFillForegroundColor(IndexedColors.CORAL.getIndex());
  72. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  73. cell = row2.createCell((short) 2);
  74. cell.setCellValue("X7");
  75. cell.setCellStyle(style);
  76.  
  77. style = workbook.createCellStyle();
  78. style.setFillForegroundColor(IndexedColors.CORNFLOWER_BLUE.getIndex());
  79. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  80. cell = row2.createCell((short) 3);
  81. cell.setCellValue("X8");
  82. cell.setCellStyle(style);
  83.  
  84. style = workbook.createCellStyle();
  85. style.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex());
  86. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  87. cell = row2.createCell((short) 4);
  88. cell.setCellValue("X9");
  89. cell.setCellStyle(style);
  90. style = workbook.createCellStyle();
  91. style.setFillForegroundColor(IndexedColors.DARK_GREEN.getIndex());
  92. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  93. cell = row2.createCell((short) 5);
  94. cell.setCellValue("X10");
  95. cell.setCellStyle(style);
  96.  
  97. // Create a row and put some cells in it.
  98. Row row3 = sheet.createRow((short) 3);
  99.  
  100. style = workbook.createCellStyle();
  101. style.setFillForegroundColor(IndexedColors.DARK_RED.getIndex());
  102. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  103. cell = row3.createCell((short) 1);
  104. cell.setCellValue("X11");
  105. cell.setCellStyle(style);
  106. style = workbook.createCellStyle();
  107. style.setFillForegroundColor(IndexedColors.DARK_TEAL.getIndex());
  108. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  109. cell = row3.createCell((short) 2);
  110. cell.setCellValue("X12");
  111. cell.setCellStyle(style);
  112.  
  113. style = workbook.createCellStyle();
  114. style.setFillForegroundColor(IndexedColors.DARK_YELLOW.getIndex());
  115. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  116. cell = row3.createCell((short) 3);
  117. cell.setCellValue("X13");
  118. cell.setCellStyle(style);
  119. style = workbook.createCellStyle();
  120. style.setFillForegroundColor(IndexedColors.GOLD.getIndex());
  121. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  122. cell = row3.createCell((short) 4);
  123. cell.setCellValue("X14");
  124. cell.setCellStyle(style);
  125.  
  126. style = workbook.createCellStyle();
  127. style.setFillForegroundColor(IndexedColors.GREEN.getIndex());
  128. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  129. cell = row3.createCell((short) 5);
  130. cell.setCellValue("X15");
  131. cell.setCellStyle(style);
  132.  
  133. // Create a row and put some cells in it.
  134. Row row4 = sheet.createRow((short) 4);
  135. style = workbook.createCellStyle();
  136. style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
  137. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  138. cell = row4.createCell((short) 1);
  139. cell.setCellValue("X16");
  140. cell.setCellStyle(style);
  141.  
  142. style = workbook.createCellStyle();
  143. style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
  144. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  145. cell = row4.createCell((short) 2);
  146. cell.setCellValue("X17");
  147. cell.setCellStyle(style);
  148. style = workbook.createCellStyle();
  149. style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
  150. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  151. cell = row4.createCell((short) 3);
  152. cell.setCellValue("X18");
  153. cell.setCellStyle(style);
  154.  
  155. style = workbook.createCellStyle();
  156. style.setFillForegroundColor(IndexedColors.GREY_80_PERCENT.getIndex());
  157. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  158. cell = row4.createCell((short) 4);
  159. cell.setCellValue("X19");
  160. cell.setCellStyle(style);
  161. style = workbook.createCellStyle();
  162. style.setFillForegroundColor(IndexedColors.INDIGO.getIndex());
  163. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  164. cell = row4.createCell((short) 5);
  165. cell.setCellValue("X20");
  166. cell.setCellStyle(style);
  167.  
  168. // Create a row and put some cells in it.
  169. Row row5 = sheet.createRow((short) 5);
  170.  
  171. style = workbook.createCellStyle();
  172. style.setFillForegroundColor(IndexedColors.LAVENDER.getIndex());
  173. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  174. cell = row5.createCell((short) 1);
  175. cell.setCellValue("X21");
  176. cell.setCellStyle(style);
  177.  
  178. style = workbook.createCellStyle();
  179. style.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());
  180. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  181. cell = row5.createCell((short) 2);
  182. cell.setCellValue("X22");
  183. cell.setCellStyle(style);
  184.  
  185. style = workbook.createCellStyle();
  186. style.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
  187. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  188. cell = row5.createCell((short) 3);
  189. cell.setCellValue("X23");
  190. cell.setCellStyle(style);
  191. style = workbook.createCellStyle();
  192. style.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());
  193. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  194. cell = row5.createCell((short) 4);
  195. cell.setCellValue("X24");
  196. cell.setCellStyle(style);
  197.  
  198. style = workbook.createCellStyle();
  199. style.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
  200. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  201. cell = row5.createCell((short) 5);
  202. cell.setCellValue("X25");
  203. cell.setCellStyle(style);
  204.  
  205. // Create a row and put some cells in it.
  206. Row row6 = sheet.createRow((short) 6);
  207. style = workbook.createCellStyle();
  208. style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE
  209. .getIndex());
  210. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  211. cell = row6.createCell((short) 1);
  212. cell.setCellValue("X26");
  213. cell.setCellStyle(style);
  214.  
  215. style = workbook.createCellStyle();
  216. style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
  217. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  218. cell = row6.createCell((short) 2);
  219. cell.setCellValue("X27");
  220. cell.setCellStyle(style);
  221. style = workbook.createCellStyle();
  222. style.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex());
  223. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  224. cell = row6.createCell((short) 3);
  225. cell.setCellValue("X28");
  226. cell.setCellStyle(style);
  227.  
  228. style = workbook.createCellStyle();
  229. style.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.getIndex());
  230. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  231. cell = row6.createCell((short) 4);
  232. cell.setCellValue("X29");
  233. cell.setCellStyle(style);
  234.  
  235. style = workbook.createCellStyle();
  236. style.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
  237. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  238. cell = row6.createCell((short) 5);
  239. cell.setCellValue("X30");
  240. cell.setCellStyle(style);
  241.  
  242. // Create a row and put some cells in it.
  243. Row row7 = sheet.createRow((short) 7);
  244. style = workbook.createCellStyle();
  245. style.setFillForegroundColor(IndexedColors.LIME.getIndex());
  246. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  247. cell = row7.createCell((short) 1);
  248. cell.setCellValue("X31");
  249. cell.setCellStyle(style);
  250. style = workbook.createCellStyle();
  251. style.setFillForegroundColor(IndexedColors.MAROON.getIndex());
  252. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  253. cell = row7.createCell((short) 2);
  254. cell.setCellValue("X32");
  255. cell.setCellStyle(style);
  256.  
  257. style = workbook.createCellStyle();
  258. style.setFillForegroundColor(IndexedColors.OLIVE_GREEN.getIndex());
  259. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  260. cell = row7.createCell((short) 3);
  261. cell.setCellValue("X33");
  262. cell.setCellStyle(style);
  263. style = workbook.createCellStyle();
  264. style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
  265. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  266. cell = row7.createCell((short) 4);
  267. cell.setCellValue("X34");
  268. cell.setCellStyle(style);
  269.  
  270. style = workbook.createCellStyle();
  271. style.setFillForegroundColor(IndexedColors.ORCHID.getIndex());
  272. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  273. cell = row7.createCell((short) 5);
  274. cell.setCellValue("X35");
  275. cell.setCellStyle(style);
  276.  
  277. // Create a row and put some cells in it.
  278. Row row8 = sheet.createRow((short) 8);
  279.  
  280. style = workbook.createCellStyle();
  281. style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
  282. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  283. cell = row8.createCell((short) 1);
  284. cell.setCellValue("X36");
  285. cell.setCellStyle(style);
  286.  
  287. style = workbook.createCellStyle();
  288. style.setFillForegroundColor(IndexedColors.PINK.getIndex());
  289. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  290. cell = row8.createCell((short) 2);
  291. cell.setCellValue("X37");
  292. cell.setCellStyle(style);
  293. style = workbook.createCellStyle();
  294. style.setFillForegroundColor(IndexedColors.PLUM.getIndex());
  295. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  296. cell = row8.createCell((short) 3);
  297. cell.setCellValue("X38");
  298. cell.setCellStyle(style);
  299.  
  300. style = workbook.createCellStyle();
  301. style.setFillForegroundColor(IndexedColors.RED.getIndex());
  302. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  303. cell = row8.createCell((short) 4);
  304. cell.setCellValue("X39");
  305. cell.setCellStyle(style);
  306. style = workbook.createCellStyle();
  307. style.setFillForegroundColor(IndexedColors.ROSE.getIndex());
  308. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  309. cell = row8.createCell((short) 5);
  310. cell.setCellValue("X40");
  311. cell.setCellStyle(style);
  312.  
  313. // Create a row and put some cells in it.
  314. Row row9 = sheet.createRow((short) 9);
  315.  
  316. style = workbook.createCellStyle();
  317. style.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());
  318. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  319. cell = row9.createCell((short) 1);
  320. cell.setCellValue("X41");
  321. cell.setCellStyle(style);
  322. style = workbook.createCellStyle();
  323. style.setFillForegroundColor(IndexedColors.SEA_GREEN.getIndex());
  324. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  325. cell = row9.createCell((short) 2);
  326. cell.setCellValue("X42");
  327. cell.setCellStyle(style);
  328.  
  329. style = workbook.createCellStyle();
  330. style.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
  331. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  332. cell = row9.createCell((short) 3);
  333. cell.setCellValue("X43");
  334. cell.setCellStyle(style);
  335. style = workbook.createCellStyle();
  336. style.setFillForegroundColor(IndexedColors.TAN.getIndex());
  337. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  338. cell = row9.createCell((short) 4);
  339. cell.setCellValue("X44");
  340. cell.setCellStyle(style);
  341.  
  342. style = workbook.createCellStyle();
  343. style.setFillForegroundColor(IndexedColors.TEAL.getIndex());
  344. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  345. cell = row9.createCell((short) 5);
  346. cell.setCellValue("X45");
  347. cell.setCellStyle(style);
  348.  
  349. // Create a row and put some cells in it.
  350. Row row10 = sheet.createRow((short) 10);
  351.  
  352. style = workbook.createCellStyle();
  353. style.setFillForegroundColor(IndexedColors.TURQUOISE.getIndex());
  354. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  355. cell = row10.createCell((short) 1);
  356. cell.setCellValue("X46");
  357. cell.setCellStyle(style);
  358.  
  359. style = workbook.createCellStyle();
  360. style.setFillForegroundColor(IndexedColors.VIOLET.getIndex());
  361. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  362. cell = row10.createCell((short) 2);
  363. cell.setCellValue("X47");
  364. cell.setCellStyle(style);
  365. style = workbook.createCellStyle();
  366. style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
  367. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  368. cell = row10.createCell((short) 3);
  369. cell.setCellValue("X48");
  370. cell.setCellStyle(style);
  371.  
  372. style = workbook.createCellStyle();
  373. style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
  374. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  375. cell = row10.createCell((short) 3);
  376. cell.setCellValue("X49");
  377. cell.setCellStyle(style);
  378.  
  379. // Write the output to a file
  380. FileOutputStream fileOut = new FileOutputStream(
  381. "POIFillAndColorExample.xlsx");
  382. workbook.write(fileOut);
  383. fileOut.close();
  384.  
  385. }
  386. }

The generated excel files looks like below images.

JAVA poi设置单元格背景颜色

相关文章