CODEWITHSUNDEEP
javaexcelapache-poixssf
Song
Song

Reputation: 1

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

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

Upvotes: 0

Views: 2308

Answers (1)

Axel Richter
Axel Richter

Reputation: 61925

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) {
  }
 }
}

Upvotes: 1

Related Questions