使用Apache POI获取单元格颜色 [英] Get Cell Colour with Apache POI

问题描述

我正在尝试学习如何在一个小项目中使用Apache POI.我想使用Excel通过使用颜色编码的单元格来创建房间布局"，并将数据加载到Java程序中.我想了解如何访问单元格的颜色属性，但是我要问的是:

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:

是否可以访问空白单元格的颜色(无数据或无值)，或者单元格是否需要数据才能使Apache POI读取它?

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?

我只对颜色感兴趣，因此，将垃圾数据放入单元格中，还是根据坐标进行遍历，是否更可取?我是Apache POI的新手，因此非常感谢您的帮助.

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.

推荐答案

您尝试了什么?请阅读繁忙的HSSF和XSSF功能开发人员指南.

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

假设以下工作簿:

然后，下面的代码在a.xls(HSSF)和a.xlsx(XSSF)上也应能正常工作.

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

---

以上代码在2016年9月的apache poi版本中可用.以下代码在2020年1月的当前apache poi版本中可用:

---

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();

 }
}

这篇关于使用Apache POI获取单元格颜色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！