java - 如何获取从Excel工作表中选中的复选框值

Question

我正在执行一项与使用 Java 读取包含单选按钮和复选框的 excel 相关的任务，我已经尝试了很多但无法进一步推进，当我尝试读取包含复选框的单元格的数据时正在返回空值。

有人可以帮忙吗。

Answer 1

控件不包含在单元格中，而是作为形状悬停在工作表上的绘图层中，并且仅锚定到单元格。因此单元格可能为空(因为其中没有内容)，尽管形状悬停在它上面并锚定到它。

此外，还有两种不同的控制方式。有表单控件和 ActiveX 控件。 ActiveX 控件的状态存储在二进制代码部分activeX1.bin 中，因此很难获取它们的状态。

在早期的 Excel 版本中(例如 2007)，所有控件的定位信息仅存储在 /xl/drawings/vmlDrawing1.vml 中。更高版本将它们存储在默认绘图和

<controls>
 <mc:AlternateContent>
  ...
 </mc:AlternateContent>
</controls>

工作表 XML 中的部分也是如此。幸运的是，还有 /xl/drawings/vmlDrawing1.vml 用于向后兼容。

以下代码解析 /xl/drawings/vmlDrawing1.vml 以获取可能锚定到单元格的控件。如果找到，它会获取此控件，如果此控件是表单控件并且不是 ActiveX 控件，那么它也可以获得它的状态。对于 ActiveX 控件，它仅获取信息，即“Pict”锚定到此单元格。

Excel:

代码:

import java.io.*;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import org.apache.poi.POIXMLDocumentPart;

import org.apache.poi.util.Units;

import org.apache.xmlbeans.XmlCursor;
import org.apache.xmlbeans.XmlObject;

import javax.xml.namespace.QName;

class ReadExcelXSSFControls {

 public ReadExcelXSSFControls() throws Exception {
  XSSFWorkbook wb  = (XSSFWorkbook)WorkbookFactory.create(new FileInputStream("ExcelWithControls.xlsx"));

  Sheet sheet = wb.getSheetAt(0);
  for (Row row : sheet) {
   for (int c = 0; c < 2; c++) {
    Cell cell = row.getCell(c);
    if (row.getRowNum() == 0) {
     System.out.print(cell + "\t");
    } else {
     if (c == 0) {
      System.out.print(cell + "\t");
     } else if (c == 1) {
      if (cell == null) cell = row.createCell(c);
      Control contol = getControlAt((XSSFCell)cell);
      System.out.print(contol);     
     }
    }
   }
   System.out.println();
  }

  wb.close();
 }

 private Control getControlAt(XSSFCell cell) throws Exception {
  XSSFSheet sheet = cell.getSheet();
  Row row =  cell.getRow();
  int r = row.getRowNum();
  int c = cell.getColumnIndex();

  int drheight = (int)Math.round(sheet.getDefaultRowHeightInPoints() * Units.PIXEL_DPI / Units.POINT_DPI);
  int rheight = (int)Math.round(row.getHeightInPoints() * Units.PIXEL_DPI / Units.POINT_DPI);
  row = null;
  if(r > 0) row = sheet.getRow(r-1);
  int rheightbefore = (row!=null)?(int)Math.round(row.getHeightInPoints() * Units.PIXEL_DPI / Units.POINT_DPI):drheight;
  row = sheet.getRow(r+1);
  int rheightafter = (row!=null)?(int)Math.round(row.getHeightInPoints() * Units.PIXEL_DPI / Units.POINT_DPI):drheight;

  String name = null;
  String objectType = null;
  String checked = null;

  XmlCursor xmlcursor = null;
  if (sheet.getCTWorksheet().getLegacyDrawing() != null) {
   String legacyDrawingId = sheet.getCTWorksheet().getLegacyDrawing().getId();
   POIXMLDocumentPart part = sheet.getRelationById(legacyDrawingId);
   XmlObject xmlDrawing = XmlObject.Factory.parse(part.getPackagePart().getInputStream());
   xmlcursor = xmlDrawing.newCursor();
   QName qnameClientData = new QName("urn:schemas-microsoft-com:office:excel", "ClientData", "x");
   QName qnameAnchor = new QName("urn:schemas-microsoft-com:office:excel", "Anchor", "x");
   boolean controlFound = false;
   while (xmlcursor.hasNextToken()) {
    XmlCursor.TokenType tokentype = xmlcursor.toNextToken();
    if (tokentype.isStart()) {
     if (qnameClientData.equals(xmlcursor.getName())) {
      controlFound = true;
      XmlObject clientdata = xmlcursor.getObject();
      XmlObject[] xmlchecked = clientdata.selectPath("declare namespace x='urn:schemas-microsoft-com:office:excel' x:Checked");
      if (xmlchecked.length > 0) {
       checked = "Checked";
      } else {
       checked = "Not checked";
      }
      while (xmlcursor.hasNextToken()) {
       tokentype = xmlcursor.toNextToken(); 
       if (tokentype.isAttr()) {
        if (new QName("ObjectType").equals(xmlcursor.getName())) {
         objectType = xmlcursor.getTextValue();
         name = objectType + " in row " + (r+1);
        } 
       } else {
        break;
       }
      }
     } else if (qnameAnchor.equals(xmlcursor.getName()) && controlFound) {
      controlFound = false;
      String anchorContent = xmlcursor.getTextValue().trim();
      String[] anchorparts = anchorContent.split(",");
      int fromCol = Integer.parseInt(anchorparts[0].trim());
      int fromColDx = Integer.parseInt(anchorparts[1].trim());
      int fromRow = Integer.parseInt(anchorparts[2].trim());
      int fromRowDy = Integer.parseInt(anchorparts[3].trim());
      int toCol = Integer.parseInt(anchorparts[4].trim());
      int toColDx = Integer.parseInt(anchorparts[5].trim());
      int toRow = Integer.parseInt(anchorparts[6].trim());
      int toRowDy = Integer.parseInt(anchorparts[7].trim());

      if (fromCol == c /*needs only starting into the column*/
       && (fromRow == r || (fromRow == r-1 && fromRowDy > rheightbefore/2f)) 
       && (toRow == r || (toRow == r+1 && toRowDy < rheightafter/2f))) {
//System.out.print(fromCol + ":" +fromColDx + ":" + fromRow + ":" + fromRowDy + ":" + toCol + ":" + toColDx + ":" + toRow + ":" + toRowDy);
       break;
      }
     } 
    } 
   }
  }

  if (xmlcursor!=null && xmlcursor.hasNextToken()) 
   return new Control(name, objectType, checked, r, c);

  return new Control("Not found", "unknown", "undefined", r, c); 
 }

 public static void main(String[] args) throws Exception {
  ReadExcelXSSFControls o = new ReadExcelXSSFControls();
 }

 private class Control {
  private String name;
  private String objectType;
  private String checked;
  private int row;
  private int col;
  public Control(String name, String objectType, String checked, int row, int col) {
   this.name = name;
   this.objectType = objectType;
   this.checked = checked;
   this.row = row;
   this.col= col;
  }
  public String getName() {
   return this.name;
  }
  public String getObjectType() {
   return this.objectType;
  }
  public String getChecked() {
   return this.checked;
  }
  public int getRow() {
   return this.row;
  }
  public int getCol() {
   return this.col;
  }
  public String toString() {
   return this.name + ":r/c:" +row+ "/" + col + ":" + this.checked;
  }
 }
}

结果:

axel@arichter:~/Dokumente/JAVA/poi/poi-3.17$ java -cp .:./*:./lib/*:./ooxml-lib/* ReadExcelXSSFControls 
Product  Status 
a        Checkbox in row 2:r/c:1/1:Checked
b        Not found:r/c:2/1:undefined
c        Checkbox in row 4:r/c:3/1:Not checked
d        Checkbox in row 5:r/c:4/1:Checked
e        Radio in row 6:r/c:5/1:Checked
f        Not found:r/c:6/1:undefined
g        Not found:r/c:7/1:undefined
e        Checkbox in row 9:r/c:8/1:Checked
f        Not found:r/c:9/1:undefined
h        Radio in row 11:r/c:10/1:Not checked
ActiveX  Pict in row 14:r/c:13/1:Not checked