2

I'm reading a xlsx file in Java and I need to check some checkboxes. Right now I am using Apache POI for this, but I can't see how I can access these checkboxes (They are inserted in excel using the Developer tab -> Insert -> Form Control).

For xls files I managed to access them with the hint from: Reading Excel checkbox values in Java Apache POI

In the "XSSF and SAX (Event API)" section from the poi how-to page (https://poi.apache.org/spreadsheet/how-to.html) I can see how to use a SAXParser to go through the document. This way I also can see that checkboxes are on the sheet, but I can't get to their value. What I have is this:

public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
    XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
    ContentHandler handler = new SheetHandler();
    parser.setContentHandler(handler);
    return parser;
}

private static class SheetHandler extends DefaultHandler {
    private boolean inControl;

    public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
        if (inControl || "control".equals(localName)) {
            System.out.print(name + ": ");
            for (int i = 0; i < attributes.getLength(); i++) {
                System.out.print(attributes.getQName(i) + "=" + attributes.getValue(i) + "; ");
            }
            inControl = true;
            System.out.println();
        }
    }

    public void endElement(String uri, String localName, String name) throws SAXException {
        if (inControl) {
            if ("control".equals(localName)) {
                inControl = false;
            }
        }
    }

    public void characters(char[] ch, int start, int length) throws SAXException {
        if (inControl) {
            System.out.println("  value: " + new String(ch, start, length));
        }
    }
}

When I use this on an xlsx file with two checkboxes on it I get the following in my console:

control: shapeId=1025; r:id=rId4; name=Check Box 1; 
controlPr: defaultSize=0; autoFill=0; autoLine=0; autoPict=0; 
anchor: moveWithCells=1; 
from: 
xdr:col: 
  value: 1
xdr:colOff: 
  value: 371475
xdr:row: 
  value: 1
xdr:rowOff: 
  value: 85725
to: 
xdr:col: 
  value: 2
xdr:colOff: 
  value: 542925
xdr:row: 
  value: 2
xdr:rowOff: 
  value: 104775
control: shapeId=1026; r:id=rId5; name=Check Box 2; 
controlPr: defaultSize=0; autoFill=0; autoLine=0; autoPict=0; 
anchor: moveWithCells=1; 
from: 
xdr:col: 
  value: 1
xdr:colOff: 
  value: 323850
xdr:row: 
  value: 3
xdr:rowOff: 
  value: 9525
to: 
xdr:col: 
  value: 2
xdr:colOff: 
  value: 495300
xdr:row: 
  value: 4
xdr:rowOff: 
  value: 28575

So the checkboxs are there, but I do not know its values... Can someone help me out on this?

1 Answer 1

4

You are right, POI cannot read these values currently because it requires a newer version of the OOXML spec ("http://schemas.microsoft.com/office/spreadsheetml/2009/9/main") than used right now: http://schemas.openxmlformats.org/spreadsheetml/2006/main

The relation for manual XML parsing seems to be from worksheets/sheetX.xml via worksheets/_rels/sheetX.xml.rels and from there to ctrlProps/ctrlPropX.xml which contains the actual checked-property.

In the sheetX.xml, the r:id in the control tag points to the relation:

<mc:AlternateContent>
    <mc:Choice Requires="x14">
      <controls>
        <mc:AlternateContent>
          <mc:Choice Requires="x14">
            <control name="Check Box 1" r:id="rId3" shapeId="1025">
              <controlPr autoFill="0" autoLine="0" autoPict="0" defaultSize="0">
                <anchor moveWithCells="1">
                  <from>
                    <xdr:col>1</xdr:col>
                    <xdr:colOff>190500</xdr:colOff>
                    <xdr:row>0</xdr:row>
                    <xdr:rowOff>171450</xdr:rowOff>

With this id you can identify the related ctrlProp.xml-file via the relations-file:

<Relationship Id="rId3" Target="../ctrlProps/ctrlProp1.xml" 
Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/ctrlProp"/>

Via this you can the file ../ctrlProps/ctrlProp1.xml which contains the checked-status:

<formControlPr xmlns="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" 
checked="Checked" lockText="1" noThreeD="1" objectType="CheckBox"/>
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.