java - 如何设置索引值以更改每个列标题的颜色以及如何从 API 请求中设置颜色？

Question

我想在 excel 文件中设置不同列标题的颜色，那么如何设置索引值来设置每个列标题的颜色，并假设我从 API 请求中获得 4 种十六进制（#FFF）格式的不同颜色，如何将这些颜色设置为 4 个不同的列标题。

这是我的代码：

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import org.apache.commons.codec.binary.Hex;

class CreateXSSFColor {

 public static void main(String[] args) throws Exception {

  try (Workbook workbook = new XSSFWorkbook(); 
       FileOutputStream fileout = new FileOutputStream("Excel.xlsx") ) {

   String rgbS = "FFF000";
   byte[] rgbB = Hex.decodeHex(rgbS); // get byte array from hex string
   XSSFColor color = new XSSFColor(rgbB, null); //IndexedColorMap has no usage until now. So it can be set null.

   XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
   cellStyle.setFillForegroundColor(color);
   cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

   Sheet sheet = workbook.createSheet(); 
   Row row = sheet.createRow(0);
   Cell cell = row.createCell(0);
   cell.setCellValue("yellow");
   cell.setCellStyle(cellStyle);

   workbook.write(fileout);
  }

 }
}

标签： javaexcelspringapache-poiexport-to-excel

Answer 1

您显示的代码XSSFColor从十六进制字符串创建一个，并创建一个XSSFCellStyle将其XSSFColor作为填充颜色。然后它将其XSSFCellStyle用作一个单元格的单元格样式。

如果您有 4 个十六进制字符串，它们应该是单元格的填充颜色，那么您需要XSSFColors在 4XSSFCellStyle秒内创建 4 个，每个字符串将 4 个中的XSSFCellStyle一个XSSFColors作为填充前景色。然后，您可以使用这 4 个不同XSSFCellStyle的 s 作为工作表中不同单元格的单元格样式。

当然，如果十六进制字符串是动态来自外部的，那么同一个十六进制字符串可以多次出现。那么你不应该一遍又一遍地创建相同的单元格样式。中的单元格样式数有限制Excel。请参阅Excel 规范和限制。

以下示例提供了一种方法，该方法setFillForegroundXSSFColor将给定设置为给定XSSFColor单元格样式中的填充前景色XSSFCell。它并不总是为此创建新的单元格样式。相反，它会检查工作簿中是否已经存在合适的单元格样式，它是单元格的当前单元格样式加上所需的填充前景色的组合。如果是这样，则使用该单元格样式。只有在没有的情况下，才会创建新的单元格样式。

它还提供了一种getRGBFromHexString用于org.apache.commons.codec.binary.Hex将十六进制字符串转换为字节数组的方法。这使用字符串操作来支持以下十六进制字符串类型RGB：FFFFFF、FFF和。#FFFFFF#FFF

示例代码：

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.xssf.usermodel.*;

import org.apache.commons.codec.binary.Hex;

class CreateXSSFCellStyles {

 static byte[] getRGBFromHexString(String rgbString) {
  byte[] rgb = new byte[3];
  if (rgbString.startsWith("#")) rgbString = rgbString.substring(1);
  if (rgbString.length() == 3)
   rgbString =  rgbString.substring(0,1) + rgbString.substring(0,1)
              + rgbString.substring(1,2) + rgbString.substring(1,2)
              + rgbString.substring(2,3) + rgbString.substring(2,3);
  System.out.println(rgbString);
  try {
   rgb = Hex.decodeHex(rgbString);
  } catch (Exception ex) {
   //decoder exception so byte[] rgb remains empty
  }
  return rgb;
 }

 static void setFillForegroundXSSFColor(XSSFCell cell, XSSFColor color, FillPatternType fillPatternType) {
  //get original cell style of cell
  XSSFCellStyle originalCellStyle = cell.getCellStyle();
  //is there a fitting cell style already in workbook?
  XSSFWorkbook workbook = cell.getSheet().getWorkbook();
  for (int i = 0; i < workbook.getNumCellStyles(); i++) {
   XSSFCellStyle cellStyle = workbook.getCellStyleAt(i);
   //cell style needs to fit requested fill foreground color and fill pattern type
   if (color.equals(cellStyle.getFillForegroundColorColor()) && cellStyle.getFillPattern() == fillPatternType) {
    //also cell style needs to fit all other cell style properties of original cell style
    if (
           cellStyle.getAlignment() == originalCellStyle.getAlignment()
        && cellStyle.getBorderLeft() == originalCellStyle.getBorderLeft()
        && cellStyle.getBorderTop() == originalCellStyle.getBorderTop()
        && cellStyle.getBorderRight() == originalCellStyle.getBorderRight()
        && cellStyle.getBorderBottom() == originalCellStyle.getBorderBottom()
        && cellStyle.getLeftBorderColor() == originalCellStyle.getLeftBorderColor()
        && cellStyle.getTopBorderColor() == originalCellStyle.getTopBorderColor()
        && cellStyle.getRightBorderColor() == originalCellStyle.getRightBorderColor()
        && cellStyle.getBottomBorderColor() == originalCellStyle.getBottomBorderColor()
        && cellStyle.getDataFormat() == originalCellStyle.getDataFormat()
        && cellStyle.getFillBackgroundColor() == originalCellStyle.getFillBackgroundColor()
        && cellStyle.getFont() == originalCellStyle.getFont()
        && cellStyle.getHidden() == originalCellStyle.getHidden()
        && cellStyle.getIndention() == originalCellStyle.getIndention()
        && cellStyle.getLocked() == originalCellStyle.getLocked()
        && cellStyle.getQuotePrefixed() == originalCellStyle.getQuotePrefixed()
        && cellStyle.getReadingOrder() == originalCellStyle.getReadingOrder()
        && cellStyle.getRotation() == originalCellStyle.getRotation()
        && cellStyle.getShrinkToFit() == originalCellStyle.getShrinkToFit()
        && cellStyle.getVerticalAlignment() == originalCellStyle.getVerticalAlignment()
        && cellStyle.getWrapText() == originalCellStyle.getWrapText()
        ) {   
     cell.setCellStyle(cellStyle);
     System.out.println("fitting cell style found");
     return;
    }
   }
  }
  //no fitting cell style found, so create a new one
  XSSFCellStyle cellStyle = workbook.createCellStyle();
  cellStyle.cloneStyleFrom(originalCellStyle);
  cellStyle.setFillForegroundColor(color);
  cellStyle.setFillPattern(fillPatternType);
  cell.setCellStyle(cellStyle);
  System.out.println("new cell style created");
 }

 public static void main(String[] args) throws Exception {

  try (XSSFWorkbook workbook = new XSSFWorkbook(); 
       FileOutputStream fileout = new FileOutputStream("Excel.xlsx") ) {

   XSSFCellStyle headerCellStyle = workbook.createCellStyle();
   headerCellStyle.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
   headerCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
   XSSFFont font = workbook.createFont();
   font.setColor(IndexedColors.WHITE.getIndex());
   headerCellStyle.setFont(font);
System.out.println(workbook.getNumCellStyles()); // 2 == default + header cell style

   XSSFSheet sheet = workbook.createSheet(); 
   XSSFRow row = sheet.createRow(0);
   XSSFCell cell;
   for (int i = 0; i < 8; i++) {
    cell = row.createCell(i);
    cell.setCellValue("Header " + (i+1));
    cell.setCellStyle(headerCellStyle);
   }

   String rgbS;
   byte[] rgbB;
   XSSFColor color;

   rgbS = "#FF0000";
   cell = sheet.getRow(0).getCell(1);
   rgbB = getRGBFromHexString(rgbS);
   color = new XSSFColor(rgbB, null);
   setFillForegroundXSSFColor(cell, color, FillPatternType.SOLID_FOREGROUND);
System.out.println(workbook.getNumCellStyles()); // 3 == default + header cell style + this one new created

   rgbS = "0000FF";
   cell = sheet.getRow(0).getCell(3);
   rgbB = getRGBFromHexString(rgbS);
   color = new XSSFColor(rgbB, null);
   setFillForegroundXSSFColor(cell, color, FillPatternType.SOLID_FOREGROUND);
System.out.println(workbook.getNumCellStyles()); // 4 == default + header cell style + one from above + this one new created

   rgbS = "#F00";
   cell = sheet.getRow(0).getCell(5);
   rgbB = getRGBFromHexString(rgbS);
   color = new XSSFColor(rgbB, null);
   setFillForegroundXSSFColor(cell, color, FillPatternType.SOLID_FOREGROUND);
System.out.println(workbook.getNumCellStyles()); // 4 == default + header cell style + two from above, none new created

   rgbS = "#0000FF";
   cell = sheet.getRow(0).getCell(7);
   rgbB = getRGBFromHexString(rgbS);
   color = new XSSFColor(rgbB, null);
   setFillForegroundXSSFColor(cell, color, FillPatternType.SOLID_FOREGROUND);
System.out.println(workbook.getNumCellStyles()); // 4 == default + header cell style + two from above, none new created

   workbook.write(fileout);
  }

 }
}