How to get the checkbox value selected from excel

Question

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.

Can someone please help with this.

Answer 1

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.

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.

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>

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

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:

Code:

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;
  }
 }
}

Result:

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