How to convert XSSF color to Java.awt.color format. because XSSF colour could not make a compare

Question

I have posted the code clealy . I want get the color of cell in excel, and i post my excel file and there are only two colors ,green and yellow. if I use XSSFColor ,the print out is XSSFColor@8b21b8fa and XSSFColor@dfcdb1. therefore, and I could not make a compare whether it is green or yellow. I hope to print out 1 if it is yellow, and 0 if it is green. thanks for any help!!!

for(Row row : sheet)
{
    for(Cell cell : row)
    {
        switch(formulaEvaluator.evaluateInCell(cell).getCellType())
        {
            case Cell.CELL_TYPE_BLANK:
               Color cellColor= cell.getCellStyle().getFillForegroundColorColor();
               if(cellColor==Color.GREEN)
               {
                   System.out.print(0+",");
               }
               else if(cellColor==Color.YELLOW)
               {
                   System.out.print(1+",");
               }
        }
    }
    System.out.println();
}

this is my excel file shows a madarin character

Would something like [XSSFColor.getARGBHex()](https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/ExtendedColor.html#getARGBHex%28%29) not do you - that'll give you a string like `00FFFF00` for your colour — Gagravarr, Apr 09 '16 at 10:29

Axel Richter · Answer 1 · 2016-04-09T16:29:36.980

While it is possible to create a XSSFColor from a java.awt.Color, there is no simple possibility to get java.awt.Color from a XSSFColor.

We could compare the ARGBHex of the XSSFColor out of the cell with the ARGBHex of a new created XSSFColor from a java.awt.Color.

Example:

import java.io.*;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

import org.apache.poi.xssf.usermodel.XSSFColor;

class ColorTest {

 public static void main(String[] args) {
  try {

   InputStream inp = new FileInputStream("ColorTest.xlsx");
   Workbook wb = WorkbookFactory.create(inp);

   Sheet sheet = wb.getSheetAt(0);

   for (Row row : sheet) {

    for(Cell cell : row) {

     switch(cell.getCellType()) {
      case Cell.CELL_TYPE_BLANK:
       Color cellColor= cell.getCellStyle().getFillForegroundColorColor();
       if (cellColor instanceof XSSFColor) {
        XSSFColor xssfCellColor = (XSSFColor) cellColor;
        if(xssfCellColor.getARGBHex().equals(new XSSFColor(java.awt.Color.GREEN).getARGBHex())) {
         System.out.print(0+",");
        } else if(xssfCellColor.getARGBHex().equals(new XSSFColor(java.awt.Color.YELLOW).getARGBHex())) {
         System.out.print(1+",");
        }
       }
      break; 

     }
    }
   }
   System.out.println();
  } catch (InvalidFormatException ifex) {
  } catch (FileNotFoundException fnfex) {
  } catch (IOException ioex) {
  }
 }
}

But in your provided picture the green color seems not to be really green with RGB 00FF00 but a muddy mixture green. So the comparision with java.awt.Color.GREEN will not match, since java.awt.Color.GREEN is exactly RGB 00FF00.

Example for both XSSF and HSSF:

import java.io.*;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.hssf.util.HSSFColor;

class ColorTest {

 public static void main(String[] args) {
  try {

   //InputStream inp = new FileInputStream("ColorTest.xlsx");
   InputStream inp = new FileInputStream("ColorTest.xls");

   Workbook wb = WorkbookFactory.create(inp);

   Sheet sheet = wb.getSheetAt(0);

   for (Row row : sheet) {

    for(Cell cell : row) {

     switch(cell.getCellType()) {
      case Cell.CELL_TYPE_BLANK:
       Color cellColor= cell.getCellStyle().getFillForegroundColorColor();
       if (cellColor instanceof XSSFColor) {
        XSSFColor xssfCellColor = (XSSFColor) cellColor;

        System.out.println(xssfCellColor.getARGBHex()); 

        if(xssfCellColor.getARGBHex().equals(new XSSFColor(java.awt.Color.GREEN).getARGBHex())) {
         System.out.println(cell.getAddress() + " is green");
        } else if(xssfCellColor.getARGBHex().equals(new XSSFColor(java.awt.Color.YELLOW).getARGBHex())) {
         System.out.println(cell.getAddress() + " is yellow");
        }
       } else if (cellColor instanceof HSSFColor) {
        HSSFColor hssfCellColor = (HSSFColor) cellColor;

        System.out.println(hssfCellColor.getHexString()); 

        if(hssfCellColor.getHexString().equals("0:FFFF:0")) {
         System.out.println(cell.getAddress() + " is green");
        } else if(hssfCellColor.getHexString().equals("FFFF:FFFF:0")) {
         System.out.println(cell.getAddress() + " is yellow");
        }
       }
      break; 

     }

    }
   }

  } catch (InvalidFormatException ifex) {
  } catch (FileNotFoundException fnfex) {
  } catch (IOException ioex) {
  }
 }
}

Thank u Axel ! I got your point. but I think there are some problems in switch statement, did u test your code? — Song, Apr 09 '16 at 11:08
It also works for me. if (cellColor instanceof XSSFColor) {XSSFColor xssfCellColor = (XSSFColor) cellColor; could u explain this two piece of code? — Song, Apr 09 '16 at 12:08
Until this I'm working with `org.apache.poi.ss.usermodel.*` only. This is reliable for both `HSSF` and `XSSF`. But now I need `XSSFColor` which is `org.apache.poi.xssf.usermodel`. So I must check if I really have a `XSSFColor` (`if (cellColor instanceof XSSFColor)`) and if so, cast the `cellColor` to `XSSFColor`. If `HSSF` (*.xls) will also be possible, then `} else if (cellColor instanceof HSSFColor) { HSSFColor hssfCellColor = (HSSFColor) cellColor;...}` will be necessary. — Axel Richter, Apr 09 '16 at 12:21
So I must check if I really have a XSSFColor (if (cellColor instanceof XSSFColor). can i understand this like that----whether cellColor is a instance of XSSFColor? — Song, Apr 09 '16 at 12:29
OK, did u find that there is a null string.after print out a character. but after taht , there is no null string which i need. but I could not find what is the problem. I want represent a character and have a null string and then repensent another character — Song, Apr 09 '16 at 13:32
I have figured it out. Thanks for all your assistance. Axel ! — Song, Apr 09 '16 at 14:02
You could re-write the `XSSFColor` check to `ExtendedColor` to stick with only HSSF and SS classes if you wanted - `XSSFColor` implements that — Gagravarr, Apr 09 '16 at 15:17
@Gagravarr: No. The aim was to compare the `org.apache.poi.ss.usermodel.Color`with `java.awt.Color`. This is not possible using only `ExtendedColor`. Furthermore with `HSSF` the `Color cellColor= cell.getCellStyle().getFillForegroundColorColor();` is not instance of `ExtendedColor`. So both `XSSFColor` and `HSSFColor` are needed. But with `HSSF` seems not be possible to compare with `java.awt.Color`. See my supplements. — Axel Richter, Apr 09 '16 at 16:29
HSSF cell style colours are not ExtendedColors. However, if you want to write you code with only `org.apache.poi.ss` classes, the check for XSSF-like colours should be with `ExtendedColor` — Gagravarr, Apr 09 '16 at 16:31
@Gagravarr: How will you compare the `ExtendedColor` with `java.awt.Color.GREEN` then without using `XSSFColor`? — Axel Richter, Apr 09 '16 at 16:38
Take your current code, replace `XSSFColor` with `ExtendedColor` and you're done! `XSSFColor` extends from `ExtendedColor`, but the former isn't in the core POI jar while the latter is — Gagravarr, Apr 09 '16 at 16:39

How to convert XSSF color to Java.awt.color format. because XSSF colour could not make a compare

1 Answers1