Apache POI not applying certain color indices

Question

Java 8 and Apache POI 4.1.x here. I have some Java code that writes a list of objects to an Excel file and its working perfectly fine, with the exception of some color-based cell styles I'm trying to apply:

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);

}

Above, the applyPriceListDataCellStyle method is called after a Cell is created from a Row object. The cell instance is then passed into this method as an argument, along with my PriceListItem bean (which is the data I'm writing to each row in the Excel file).

The PriceListItem bean has a ChangeType property (enum) that dictates what color the cell should appear as in the final Excel file.

At runtime, I am calling this method on the cells of 5 different rows (so 5 different PriceListItems) of each ChangeType value, and I get output that looks like:

So:

• The 1st row, which is a "NO_CHANGE" (which maps to IndexedColors.WHITE) works perfectly fine, as expected
• The 2nd row, which is an "ADDITION" (which maps to IndexedColors.YELLOW) works perfectly fine, as expected
• The 3rd row, which is a "DELETION" (which maps to my custom purple color) appears jet black instead -- wrong!
• The 4th row, which is a "PRICE_ADJUSTMENT_INCREASE" (which maps to IndexedColors.RED) appears gray -- wrong!
• The 5th row, which is a "PRICE_ADJUSTMENT_DECREASE" (which maps to IndexedColors.GREEN) appears a lighter shade of gray -- wrong!

Where am I going awry on setting the colors on the cells of these various rows?

Answer 1

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:

---

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.