I have a xlsx file that looks like this:

By using Java, I want to retrieve the font color present on every cell. What I need is just the text and font color. Font style or size and background color are not necessary. Note that A5 cell has 2 words with different color, so I need to get 2 colors from a single cell.

I tried using POI API but I can't reach what I want. This is the code:

public static void main(String[] args) {
    try {
        File file = new File("C:\\Users\\xxxxx\\Desktop\\example.xlsx");
        FileInputStream fis = new FileInputStream(file);
        XSSFWorkbook wb = new XSSFWorkbook(fis);
        XSSFSheet sheet = wb.getSheetAt(0);

        Iterator<Row> itr = sheet.iterator();
        while (itr.hasNext()) {
            Row row = itr.next();
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    System.out.print(cell.getStringCellValue()   "\t\t\t");
                    System.out.println(
                            "<<<<<<<<<<< COLOR: "   ((XSSFFont) ((XSSFCellStyle) cell.getCellStyle()).getFont())
                                    .getXSSFColor().getARGBHex());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    System.out.print(cell.getNumericCellValue()   "\t\t\t");
                    break;
                default:
                }
            }
            
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}

The code is simple:

1. Get the excel file
2. Get the Sheet
3. Iterate over the cells
4. Print the cell content and try to get the color with XSSFCellStyle

But the output is not what I was expecting:

hello           <<<<<<<<<<< COLOR: FF000000
hello           <<<<<<<<<<< COLOR: FFFF0000
hello           <<<<<<<<<<< COLOR: FF70AD47
hello           <<<<<<<<<<< COLOR: FFCE88D0
hello world         <<<<<<<<<<< COLOR: FF000000
hello           <<<<<<<<<<< COLOR: FF000000

1. Cases 1, 2, 3, 4 and 6 are OK because it shows its actual color.
2. Case 5 is not OK because if there are 2 or more words with different color, it returns 000000, aka BLACK.

What I need in this last case is to have something like this:

hello <<<<<<<COLOR: FFEBCB46  world <<<<<<<<<<COLOR: FF70AD47

But I dont know how to scan color for every words in a single cell.

How can I do that? Is it impossible or should I use another API?

CodePudding user response：

I posted the same question on StackOverflow in Spanish and there is an answer:

Basically, there are 2 aproaches:

1. Get char style (char per char) of the entire cell

   for (int i = 0; i < richTextString.length(); i  ) {
   String nextColor = font.((XSSFColor) ((XSSFFont)cell.getRichStringCellValue().getFontAtIndex(i))
       .getXSSFColor()).getARGBHex();
   

   ...

2. Parse RichStringCellValue() string value, since it has xml-based information about the entire cell, where every main block represents word or words (in the correct order) that have the same style.