使用 Apache POI 获取单元格颜色

问题描述

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

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

我只对颜色感兴趣，所以最好将垃圾数据放在单元格中，或者根据坐标遍历它们?我是 Apache POI 的新手，非常感谢任何帮助.

解决方案

你尝试过什么?请阅读

那么下面的代码应该适用于 a.xls (HSSF) 和 a.xlsx (XSSF).

import org.apache.poi.ss.usermodel.*;导入 org.apache.poi.xssf.usermodel.*;导入 org.apache.poi.hssf.util.HSSFColor;导入 org.apache.poi.openxml4j.exceptions.InvalidFormatException;导入 java.io.*;类 ReadExcelEmptyColoredCells {公共静态无效主(字符串 [] args){尝试 {//Workbook workbook = WorkbookFactory.create(new File("a.xls"));工作簿工作簿 = WorkbookFactory.create(new File("a.xlsx"));Sheet sheet = workbook.getSheetAt(0);for(行行:工作表){对于(单元格单元格:行){if (!"".equals(String.valueOf(cell)))System.out.println(cell.getAddress() + ":" + String.valueOf(cell));CellStyle cellStyle = cell.getCellStyle();颜色颜色 = cellStyle.getFillForegroundColorColor();如果(颜色！= null){如果(XSSFColor 的颜色实例){System.out.println(cell.getAddress() + ":" + ((XSSFColor)color).getARGBHex());} else if(HSSFColor的颜色实例){if (! (HSSFColor.AUTOMATIC的颜色实例))System.out.println(cell.getAddress() + ":" + ((HSSFColor)color).getHexString());}}}}workbook.close();} catch (InvalidFormatException ifex) {} catch (FileNotFoundException fnfex) {} 捕捉(IOException ioex){}}}

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

import org.apache.poi.ss.usermodel.*;导入 org.apache.poi.xssf.usermodel.*;导入 org.apache.poi.hssf.util.HSSFColor;导入 java.io.*;类 ReadExcelEmptyColoredCells {public static void main(String[] args) 抛出异常 {//Workbook workbook = WorkbookFactory.create(new File("a.xls"));工作簿工作簿 = WorkbookFactory.create(new File("a.xlsx"));Sheet sheet = workbook.getSheetAt(0);for(行行:工作表){对于(单元格单元格:行){if (!"".equals(String.valueOf(cell)))System.out.println(cell.getAddress() + ":" + String.valueOf(cell));CellStyle cellStyle = cell.getCellStyle();颜色颜色 = cellStyle.getFillForegroundColorColor();如果(颜色！= null){如果(XSSFColor 的颜色实例){System.out.println(cell.getAddress() + ":" + ((XSSFColor)color).getARGBHex());} else if(HSSFColor的颜色实例){if (! (color.equals(HSSFColor.HSSFColorPredefined.AUTOMATIC.getColor())))System.out.println(cell.getAddress() + ":" + ((HSSFColor)color).getHexString());}}}}workbook.close();}}

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.

解决方案

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

Supposing following Workbook:

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

 }
}

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