Changing XLSX form control location with Apache POI

I've some number of xlsm files containing form controls. I'd like to programmatically move a particular button down a few rows on each sheet. My first hope was to do something like this:

FileInputStream inputStream = new FileInputStream(new File("t.xlsm"));
XSSFWorkbook wb = new XSSFWorkbook(inputStream);
XSSFSheet xs = (XSSFSheet)wb.getSheetAt(1);
RelationPart rp = xs.getRelationParts().get(0);
XSSFDrawing drawing = (XSSFDrawing)rp.getDocumentPart();

for(XSSFShape sh : drawing.getShapes()){
    XSSFClientAnchor a = (XSSFClientAnchor)sh.getAnchor();
    if (sh.getShapeName().equals("Button 2")) {
        a.setRow1(a.getRow1()+10);
        a.setRow2(a.getRow2()+10);
    }
}

However, the shape objects given by XSSFDrawing.getShapes() are copies and any changes to them are not reflected in the document after a wb.write() .

I tried a couple other approaches, such as getting the CTShape and parsing the XML within but things quickly got hairy.

Is there a recommended way to manage form controls like this via POI?


I ended up fiddling directly with the XML:

wb = new XSSFWorkbook(new File(xlsmFile));
XSSFSheet s = wb.getSheet("TWO");
XmlObject[] subobj = s.getCTWorksheet().selectPath(declares+
            " .//mc:AlternateContent/mc:Choice/main:controls/mc:AlternateContent/mc:Choice/main:control");

String targetButton = "Button 2";
int rowsDown = 10;

for (XmlObject obj : subobj) {
    XmlCursor cursor = obj.newCursor();
    cursor.push();
    String attrName = cursor.getAttributeText(new QName("name"));
    if (attrName.equals(targetButton)) {
        cursor.selectPath(declares+" .//main:from/xdr:row");
        if (!cursor.toNextSelection()) {
            throw new Exception();
        }
        int newRow = Integer.parseInt(cursor.getTextValue()) + rowsDown;
        cursor.setTextValue(Integer.toString(newRow));

        cursor.pop();
        cursor.selectPath(declares+" .//main:to/xdr:row");
        if (!cursor.toNextSelection()) {
            throw new Exception();
        }
        newRow = Integer.parseInt(cursor.getTextValue()) + rowsDown;
        cursor.setTextValue(Integer.toString(newRow));
    }
    cursor.dispose();
}

This moves the named button down 10 rows. I had to discover the button name (which may not be easy to do via Excel, I inspected the file directly). I'm guessing this is going to be very sensitive to the version of Excel in use.

链接地址: http://www.djcxy.com/p/37988.html

上一篇: 从Excel VBA发送格式化的Lotus Notes富文本电子邮件

下一篇: 使用Apache POI更改XLSX表单控制位置