Get Cell Colour with Apache POI

1
votes

I'm trying to learn how to use Apache POI with a small project. I want to use Excel to create 'room layouts' by using colour-coded cells, and load the data into a Java program. I think understand how to access the colour properties of a cell, but what I'm asking is:

Is it possible to access the colour of a blank cell (no data or value), or does a cell need to have data in order for Apache POI to read it?

I am only interested in the colour, so might it be preferable to put junk data in the cells, or possibly iterate through them based on coordinates? I'm brand new to Apache POI, so any help is greatly appreciated.

1
javaexcelcolorsapache-poi

1 Answers

5
votes

What have you tried? Please read Busy Developers' Guide to HSSF and XSSF Features.

Supposing following Workbook:

enter image description here

Then the following code should work as well with a.xls (HSSF) as with a.xlsx (XSSF).

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

import java.io.*;

class ReadExcelEmptyColoredCells {

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

   //Workbook workbook = WorkbookFactory.create(new File("a.xls"));
   Workbook workbook = WorkbookFactory.create(new File("a.xlsx"));

   Sheet sheet = workbook.getSheetAt(0);

   for (Row row : sheet) {
    for (Cell cell : row) {
     if (! "".equals(String.valueOf(cell)))
      System.out.println(cell.getAddress() + ": " + String.valueOf(cell));
     CellStyle cellStyle = cell.getCellStyle();
     Color color = cellStyle.getFillForegroundColorColor();
     if (color != null) {
      if (color instanceof XSSFColor) {
       System.out.println(cell.getAddress() + ": " + ((XSSFColor)color).getARGBHex());
      } else if (color instanceof HSSFColor) {
       if (! (color instanceof HSSFColor.AUTOMATIC))
        System.out.println(cell.getAddress() + ": " + ((HSSFColor)color).getHexString());
      }
     }
    }
   }

   workbook.close();

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

Above code was usable using apache poi's version in September 2016. Following code is usable using current apache poi versions of January 2020:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;

import java.io.*;

class ReadExcelEmptyColoredCells {

 public static void main(String[] args) throws Exception {

  //Workbook workbook = WorkbookFactory.create(new File("a.xls"));
  Workbook workbook = WorkbookFactory.create(new File("a.xlsx"));

  Sheet sheet = workbook.getSheetAt(0);

  for (Row row : sheet) {
   for (Cell cell : row) {
    if (! "".equals(String.valueOf(cell)))
     System.out.println(cell.getAddress() + ": " + String.valueOf(cell));
    CellStyle cellStyle = cell.getCellStyle();
    Color color = cellStyle.getFillForegroundColorColor();
    if (color != null) {
     if (color instanceof XSSFColor) {
      System.out.println(cell.getAddress() + ": " + ((XSSFColor)color).getARGBHex());
     } else if (color instanceof HSSFColor) {
      if (! (color.equals(HSSFColor.HSSFColorPredefined.AUTOMATIC.getColor())))
       System.out.println(cell.getAddress() + ": " + ((HSSFColor)color).getHexString());
     }
    }
   }
  }

  workbook.close();

 }
}