时间戳

首页 > 解决方案 > Apache POI 未应用某些颜色索引

java - Apache POI 未应用某些颜色索引

问题描述

Java 8 和 Apache POI 4.1.x 在这里。我有一些 Java 代码可以将对象列表写入 Excel 文件，并且它工作得很好，除了我尝试应用的一些基于颜色的单元格样式：

public void applyPriceListDataCellStyle(PriceListItem priceListItem, Cell cell) {

    short colorIndex;
    switch(priceListItem.getChangeType()) {
        case ADDITION:
            colorIndex = IndexedColors.YELLOW.getIndex();
            break;
        case DELETION:
            XSSFColor purple = new XSSFColor(new java.awt.Color(120,81,169), new DefaultIndexedColorMap());
            colorIndex = purple.getIndex();
            break;
        case PRICE_ADJUSTMENT_INCREASE:
            colorIndex = IndexedColors.RED.getIndex();
            break;
        case PRICE_ADJUSTMENT_DECREASE:
            colorIndex = IndexedColors.GREEN.getIndex();
            break;
        default:
            // NO_CHANGE (leave unstyled)
            colorIndex = IndexedColors.WHITE.getIndex();
            break;
    }

    Map<String,Object> cellProps = new HashMap<>();
    cellProps.put(CellUtil.FILL_FOREGROUND_COLOR, colorIndex);
    cellProps.put(CellUtil.FILL_PATTERN, FillPatternType.SOLID_FOREGROUND);

    CellUtil.setCellStyleProperties(cell, cellProps);

}

上面，在从对象创建a之后applyPriceListDataCellStyle调用该方法。然后将该实例与我的bean（这是我写入 Excel 文件中每一行的数据）一起作为参数传递给此方法。CellRowcellPriceListItem

PriceListItembean 有一个ChangeType属性（枚举），它指示单元格在最终 Excel 文件中应显示的颜色。

在运行时，我在PriceListItems每个ChangeType值的 5 个不同行（因此 5 个不同）的单元格上调用此方法，我得到如下所示的输出：

在此处输入图像描述

所以：

在这些不同行的单元格上设置颜色时，我在哪里出错了？

标签： javaexcelapache-poistyling

解决方案

The apache poi CellUtil only works using org.apache.poi.ss.*. It cannot work using a XSSFColor because org.apache.poi.ss.usermodel.CellStyle has no method to get/set fill foreground color from a XSSFColor. It only works using short color indexes from IndexedColors. Hence the black color, because in your code purple.getIndex() always returns 0. So if CellUtil shall be used, which is to recommend, then choose a color from IndexedColors instead of creating a custom color. There is IndexedColors.VIOLET for example.

But the other wrong cases are not reproducible for me. The following Minimal, Reproducible Example works for me as expected. It needs a price-list-template.xlsx having at least one worksheet.

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellUtil;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class PoiColors {

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

        List<PriceListItem> priceList = new ArrayList<>();

        PriceListItem noChange = new PriceListItem();
        noChange.modelNumber = "123";
        noChange.price = BigDecimal.valueOf(1.99);
        noChange.changeType = ChangeType.NO_CHANGE;

        PriceListItem addition = new PriceListItem();
        addition.modelNumber = "456";
        addition.price = BigDecimal.valueOf(2.99);
        addition.changeType = ChangeType.ADDITION;

        PriceListItem deletion = new PriceListItem();
        deletion.modelNumber = "789";
        deletion.price = BigDecimal.valueOf(3.99);
        deletion.changeType = ChangeType.DELETION;

        PriceListItem increase = new PriceListItem();
        increase.modelNumber = "234";
        increase.price = BigDecimal.valueOf(4.99);
        increase.changeType = ChangeType.PRICE_ADJUSTMENT_INCREASE;

        PriceListItem decrease = new PriceListItem();
        decrease.modelNumber = "345";
        decrease.price = BigDecimal.valueOf(5.99);
        decrease.changeType = ChangeType.PRICE_ADJUSTMENT_DECREASE;

        priceList.add(noChange);
        priceList.add(addition);
        priceList.add(deletion);
        priceList.add(increase);
        priceList.add(decrease);

        new PoiColors().exportPriceList(priceList, "acme.xlsx");

    }

    private void exportPriceList(
            List<PriceListItem> priceList,
            String targetAbsPath) throws IOException {

        // set variables based on specified format
        String templateName = "price-list-template.xlsx";

        // load the template
        InputStream inp = this.getClass().getClassLoader().getResource(templateName).openStream();
        Workbook workbook = WorkbookFactory.create(inp);

        Sheet sheet = workbook.getSheetAt(0);
        workbook.setSheetName(workbook.getSheetIndex(sheet), "ACME");

        // plug in the header/metadata info and format some headers so they get autosized properly
        Row row2 = CellUtil.getRow(1, sheet);
        Cell c2 = CellUtil.getCell(row2, 2);
        c2.setCellValue("ACME");


        // create the data rows and apply styling
        // start at row #11 which is where data rows begin
        int rowNum = 11;

        // rip through the items and write them to the rows; apply styling as appropriate
        for (PriceListItem priceListItem : priceList) {

            Row nextRow = sheet.createRow(rowNum);

            Cell changeType = nextRow.createCell(0);
            changeType.setCellValue(priceListItem.changeType.name());
            applyPriceListDataCellStyle(priceListItem, changeType);

            Cell modelNumber = nextRow.createCell(1);
            modelNumber.setCellValue(priceListItem.modelNumber);
            applyPriceListDataCellStyle(priceListItem, modelNumber);

            Cell price = nextRow.createCell(2);
            price.setCellValue(priceListItem.price.doubleValue());
            applyPriceListDataCellStyle(priceListItem, price);

            rowNum++;

        }

        // resize the columns appropriately
        for (int c = 0; c < 3; c++) {
            sheet.autoSizeColumn(c);
        }


        // export to file system
        FileOutputStream fos = new FileOutputStream(targetAbsPath);
        workbook.write(fos);

        fos.close();
        inp.close();
        workbook.close();

    }

    private void applyPriceListDataCellStyle(PriceListItem priceListItem, Cell cell) {

        short colorIndex;
        switch(priceListItem.changeType) {
            case ADDITION:
                colorIndex = IndexedColors.YELLOW.getIndex();
                break;
            case DELETION:
                colorIndex = IndexedColors.VIOLET.getIndex();
                break;
            case PRICE_ADJUSTMENT_INCREASE:
                colorIndex = IndexedColors.RED.getIndex();
                break;
            case PRICE_ADJUSTMENT_DECREASE:
                colorIndex = IndexedColors.GREEN.getIndex();
                break;
            default:
                // NO_CHANGE (leave unstyled)
                colorIndex = IndexedColors.WHITE.getIndex();
                break;
        }

        Map<String,Object> cellProps = new HashMap<>();
        cellProps.put(CellUtil.FILL_FOREGROUND_COLOR, colorIndex);
        cellProps.put(CellUtil.FILL_PATTERN, FillPatternType.SOLID_FOREGROUND);

        CellUtil.setCellStyleProperties(cell, cellProps);

    }

}

class PriceListItem {
    public String modelNumber;
    public BigDecimal price;
    public ChangeType changeType;
}

enum ChangeType {
    NO_CHANGE,
    ADDITION,
    DELETION,
    PRICE_ADJUSTMENT_INCREASE,
    PRICE_ADJUSTMENT_DECREASE
}

Result is acme.xlsx which looks like so:

enter image description here

Using IndexedColors the colors are set in Office Open XML /xl/styles.xml as follows:

...
<fill>
 <patternFill patternType="solid">
  <fgColor indexed="13"/>
  <bgColor indexed="64"/>
 </patternFill>
</fill>
...

The indexed colors are not given by RGB but are taken from the default color palette. If you have the suspicion that your spreadsheet calculation application uses a different default color palette than Excel, then you can test this using the following code:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellUtil;

import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;

public class TestIndexedColors {

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

        String templateName = "price-list-template.xlsx";
        InputStream inp = TestIndexedColors.class.getClassLoader().getResource(templateName).openStream();
        Workbook workbook = WorkbookFactory.create(inp);
        Sheet sheet = workbook.getSheetAt(0);

        Row row; Cell cell; int r = 11;
        Map<String,Object> cellProps;
        for (IndexedColors color : IndexedColors.values()) {
            row = sheet.createRow(r++);
            cell = row.createCell(0); cell.setCellValue(color.getIndex());
            cell = row.createCell(1); cell.setCellValue(color.name());
            cell = row.createCell(2);
            cellProps = new HashMap<>();
            cellProps.put(CellUtil.FILL_FOREGROUND_COLOR, color.getIndex());
            cellProps.put(CellUtil.FILL_PATTERN, FillPatternType.SOLID_FOREGROUND);
            CellUtil.setCellStyleProperties(cell, cellProps);
        }

        FileOutputStream out = new FileOutputStream("acme.xlsx"); 
        workbook.write(out);
        out.close();
        workbook.close();
    }
}

It needs a price-list-template.xlsx having at least one worksheet. The result acme.xlsx shows indexes, names and colors of all possible indexed colors using the current default color palette.

推荐阅读