CODEWITHSUNDEEP
javaexcelcheckboxapache-poixlsx
Ivar Kanters
Ivar Kanters

Reputation: 21

Reading xlsx checkbox values in Java

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?

Upvotes: 2

Views: 2443

Answers (1)

centic
centic

Reputation: 15872

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"/>

Upvotes: 4

Related Questions