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

问题描述

我正在处理与使用Java读取包含单选按钮和复选框的excel有关的任务，当我尝试读取包含Checkbox的单元格的数据时，我做了很多尝试，但是不能继续做下去返回一个空值.

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屋！