mercredi 3 février 2016

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 (http://ift.tt/1ULplbG) 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?




Aucun commentaire:

Enregistrer un commentaire