使用Apache POI从Excel中的空白单元格读取颜色到Java

Question

背景：我正在尝试将Excel文件读入Java程序。我的Excel文件的意思是表示一个网格或栅格地图，所以我使每个单元格的高度和宽度为一英寸。我的想法是，我可以“绘制”一个地图或图像，通过阴影在每个单元格与一个颜色。然后，我可以将Excel文件读到一个我自己用“Pixel”对象创建的Java程序，并创建一个更文字化的图像。我是计算机科学的本科生，到目前为止我只上过四节计算机科学课。我懂OOP，能用Java编程。这不是为了一堂课；这是一个辅助项目。我正在使用XSSF（Microsoft 2007及以后版本）。

    try {
        FileInputStream file = new FileInputStream(new File("My FilePath"));
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook.getSheetAt(0);
        for(int i=0; i<5040; i++) {
            Row row = sheet.getRow(i);
        for(int j=0; j<10080; j++) {
            Cell cell = row.getCell(j, Row.MissingCellPolicy.RETURN_NULL_AND_BLANK);
            ExtendedColor color = (ExtendedColor) cell.getCellStyle().getFillForegroundColorColor(); 
            //NOTE: getFillBackgroundColorColor did not work! It only returns the color black.
            byte[] bytes = color.getRGB();
            RGBColor rgb = new RGBColor(bytes);
            String text = cell.getStringCellValue();
            Coordinate coordinate = new Coordinate(j, i);
            Tile tile = new Tile(rgb, text);
            map[j][i] = tile;
            // Coordinate and Tile are other objects I made myself. 
            // The map is a two-dimensional array of Tiles, declared previously. 
            // I left this code here because it works.
        }
    }
    workbook.close();
    file.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    }

RGBColor构造函数代码：

    public RGBColor(byte[] bytes) {
        if(bytes != null) {
        this.red = (int) bytes[0];
        this.green = (int) bytes[1];
        this.blue = (int) bytes[2];
        if(red<0) {red = red+256;}
        if(green<0) {green = green+256;}
        if(blue<0) {blue = blue+256;}
    }

结果:如果单元格中有文本，上面的代码将正确读取该单元格的颜色，并根据该颜色创建一个RGBColor对象。上面的代码还可以从单元格中读取文本。但是，一旦它到达没有文本的单元格，它就会在ExtendedColor行处导致NullPointerException（因此单元格为空）。当使用MissingCellPolicy CREATE_NULL_AS_BLANK代替时，它会在byte[]行（因此颜色为空）处导致nullPointerException。感谢任何帮助，即使不是我所要求的帮助，因为我是ApachePOI的新手！

Answer 1

单个彩色单元格永远不能为空。它必须存在。所以我们只能循环现有的单元格。CellStyle每个定义都不是-null。但是CellStyle.GetFillForeGroundColorColor可以在没有颜色的情况下返回null。所以我们需要检查一下。

假设下表：

代码：

import org.apache.poi.ss.usermodel.*;
import java.io.*;

import java.util.Arrays;

class ReadColorsFromExcel {

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

  InputStream inp = new FileInputStream("MyFile.xlsx");
  Workbook workbook = WorkbookFactory.create(inp);
  Sheet sheet = workbook.getSheetAt(0);
  for (Row row : sheet) {
   for (Cell cell : row) { // cell will always be not-null only existing cells are in loop
    CellStyle cellStyle = cell.getCellStyle(); // cellStyle is always not-null
    ExtendedColor extendedColor = (ExtendedColor)cellStyle.getFillForegroundColorColor(); // extendedColor may be null
    String color = "none";
    if (extendedColor != null) {
     byte[] bytes = extendedColor.getRGB();
     color = Arrays.toString(bytes);
    }
    System.out.println("Cell " + cell.getAddress() + " of type " + cell.getCellType() + " has color " + color);
   }
  }
 }
}

Cell A1 of type 1 has color none
Cell B1 of type 0 has color [-1, -1, 0]
Cell C1 of type 2 has color none
Cell B3 of type 0 has color none
Cell C3 of type 3 has color [-110, -48, 80]
Cell D4 of type 1 has color none
Cell B6 of type 3 has color [0, 112, -64]
Cell D7 of type 3 has color [-1, 0, 0]
Cell A9 of type 3 has color [-1, -64, 0]
Cell F12 of type 3 has color [0, -80, 80]

代码：

import org.apache.poi.ss.usermodel.*;
import java.io.*;

import java.util.Arrays;

class ReadColorsFromExcel {

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

  InputStream inp = new FileInputStream("MyFile.xlsx");
  Workbook workbook = WorkbookFactory.create(inp);
  Sheet sheet = workbook.getSheetAt(0);

  int lastRow = 12;
  int lastCol = 6;

  for (int rowNum = 0; rowNum < lastRow; rowNum++) {
   Row row = sheet.getRow(rowNum);
   if (row == null) {
    row = sheet.createRow(rowNum);
   }
   CellStyle rowStyle = row.getRowStyle(); // if the whole row has a style
   for (int colNum = 0; colNum < lastCol; colNum++) {
    CellStyle colStyle = sheet.getColumnStyle(colNum); // if the whole column has a style
    Cell cell = row.getCell(colNum, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
    CellStyle cellStyle = cell.getCellStyle(); // cellStyle is always not-null
    String color = "none";
    ExtendedColor extendedColor = (ExtendedColor)cellStyle.getFillForegroundColorColor(); // first we try cellStyle
    if (extendedColor == null && rowStyle != null) extendedColor = (ExtendedColor)rowStyle.getFillForegroundColorColor(); // now we try rowStyle
    if (extendedColor == null && colStyle != null) extendedColor = (ExtendedColor)colStyle.getFillForegroundColorColor(); // at last we try colStyle
    if (extendedColor != null) {
     byte[] bytes = extendedColor.getRGB();
     color = Arrays.toString(bytes);
    }
    System.out.println("Cell " + cell.getAddress() + " of type " + cell.getCellType() + " has color " + color);
   }
  }
 }
}

现在所有可能的样式都要考虑进去。