如何获取从Excel工作表中选择的复选框值 [英] How to get the checkbox value selected from excel sheet

问题描述

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

I am working on a task related to reading an excel which contains radio buttons and checkbox using Java, I have tried a lot but can't move further on this, when I am trying to read the data of cell containing Checkbox it is returning a null value.

有人可以帮忙吗.

推荐答案

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

Controls are not contained in the cells but hovers in the drawing layer over the sheet as shapes and only are anchored to the cells. So the cell may be null (because of no content in it) although a shape hovers over it and is anchored to it.

此外，有两种不同类型的控件是可能的.有表单控件和ActiveX 控件.ActiveX 控件的状态存储在二进制代码部分 activeX1.bin 中，因此获取它们的状态非常困难.

Moreover there are two different kinds of controls possible. There are form controls and ActiveX controls. The states of ActiveX controls are stored in binary code parts activeX1.bin and thus getting the state of them is very hard.

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

In earlier Excel versions (2007 for example) the anchor informations of all controls are stored in /xl/drawings/vmlDrawing1.vml only. Later versions are storing them in default drawing and within

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

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

parts in the sheet XML too. Fortunately there is also /xl/drawings/vmlDrawing1.vml for backwards compatibility.

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

The following code does parsing the /xl/drawings/vmlDrawing1.vml to get the control which is possible anchored to a cell. If found, it gets this control and if this control is a form control and not a ActiveX control, then it also can get it's state. For ActiveX controls it gets only the information, that a "Pict" is anchored to this cell.

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

这篇关于如何获取从Excel工作表中选择的复选框值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！