Setting color of each table cell in a row

Question

I have a servlet that is taking in a set of data, processing it, and writing it either into an excel file or as a text page depending on the form submission. All the processing is occurring in a respective model using the Apache POI when processing Excel. I'm trying to modify this so that it will color code the rows based on the data contained, however, after applying the color to the row, when I write the workbook to the fileoutputstream, the colors are not there. I'm processing the data into the excel file as such:

MCVE

package mcve;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.LinkedHashMap;
import java.util.Set;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class MCVE {

/**
 * @param args the command line arguments
 */
public static void main(String[] args) throws FileNotFoundException, IOException {
    LinkedHashMap<Integer, String> testReport = new LinkedHashMap<>();

    testReport.put(0, "CPU");
    testReport.put(1, "App");
    testReport.put(2, "Other");
    testReport.put(3, "Memory");
    testReport.put(4, "Power");
    testReport.put(5, "Disk");

    File file = new File("C:/SplunkTesting/report.xlsx");
    FileOutputStream out = new FileOutputStream(file);
    writeToExcel(testReport).write(out);
    out.flush();
    out.close();
}

private static XSSFWorkbook writeToExcel(LinkedHashMap<Integer, String> formattedReport) {
    try {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet();
        int rowIndex = sheet.getLastRowNum();
        Set<Integer> keySet = formattedReport.keySet();
        for (Integer key : keySet) {
            XSSFRow row = sheet.createRow(rowIndex++);
            String line = formattedReport.get(key);
            String[] strArr = line.split(",");
            int cellCount = 0;
            for (String str : strArr) {
                XSSFCell cell = row.createCell(cellCount++);
                cell.setCellValue(str);
            }

            XSSFCellStyle style = workbook.createCellStyle();
            if (line.contains("App")) {
                style.setFillForegroundColor(IndexedColors.BLUE.getIndex());
            } else if (line.contains("CPU")) {
                style.setFillForegroundColor(IndexedColors.GREEN.getIndex());
            } else if (line.contains("Disk")) {
                style.setFillForegroundColor(IndexedColors.GOLD.getIndex());
            } else if (line.contains("Memory")) {
                style.setFillForegroundColor(IndexedColors.INDIGO.getIndex());
            } else if (line.contains("Network")) {
                style.setFillForegroundColor(IndexedColors.LAVENDER.getIndex());
            } else if (line.contains("Power")) {
                style.setFillForegroundColor(IndexedColors.MAROON.getIndex());
            } else {
                style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
            }
            style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
            row.setRowStyle(style);
        }

        return workbook;

    } catch (Exception e) {
        e.printStackTrace();
    }
    return null;
}
}

I get the excel file still, but the formatting isn't there. What am I doing wrong?

Did you try to debug this code? Also note that `setFillBackgroundColor` is a *faux ami* [you probably want `fillForegroundColor`](http://stackoverflow.com/questions/17243529/cant-set-fill-color-apache-poi-excel-workbook) — , Jan 27 '16 at 20:15
Yes, I stepped through it and it hits the style changes exactly as I want it to. But I'll try that suggestion right now. — Susannah Potts, Jan 27 '16 at 20:16
Good, try using a `fillForegroundColor` as illustrated in the linked question — , Jan 27 '16 at 20:17
I did, made the change to reflect it in the question. However, it's still all white. — Susannah Potts, Jan 27 '16 at 20:21
can you post a [MCVE](http://stackoverflow.com/help/mcve)? As a side note, maybe the issue is too much styles, you are creating them in a loop — , Jan 27 '16 at 20:23
I don't have one on hand but I'll write one. Do you want me to keep it as a servlet/model form or just a quick main class? Do you have a suggestion as to how I could apply this kind of specific style change in a non-loop? — Susannah Potts, Jan 27 '16 at 20:26
So the MCVE produces the colors...but it doesn't place it under the text. Even writing the text after applying the color doesn't work as it replaces the old formatting with the cell.... — Susannah Potts, Jan 27 '16 at 20:43

score 1 · Accepted Answer · answered Jan 27 '16 at 20:57

The solution for me was to set the style in the cell creation and value setting loop. Otherwise, each cell when created overwrites the row formatting which is why it was failing for me. This is the case even if you set row formatting after cell creation.

Setting color of each table cell in a row

1 Answers1