How can I change TextBox in Excel with XSSF from Apache POI

Question

I am using Apache POI to process Excel file. My Excel file has 2 TextBoxes for which I would like to read the text and change it. How is it possible with the XSSF model? I do not want to create a new TextBox- I know how to do this. So far I was trying, but there is no TextBox anywhere there (that I can see).

XSSFWorkbook wb = //get the Workbook somehow
        XSSFSheet sheet = wb.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.rowIterator();
        while(rowIterator.hasNext()){
            Row row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();
            while(cellIterator.hasNext()){
                Cell cell = cellIterator.next();
            }
        }
        for(PackagePart pp : wb.getAllEmbedds()){

        }

So where are the TextBoxes?

score 3 · Answer 1 · edited Nov 06 '18 at 22:22

Here's what I did to obtain references to Textboxes and change their contents in POI 3.10.

For XSSF (untested):

XSSFDrawing draw = sheet.createDrawingPatriarch();
List<XSSFShape> shapes = draw.getShapes();
Iterator<XSSFShape> it = shapes.iterator();

while(it.hasNext()) {           
    XSSFShape shape = it.next();
    if (shape instanceof XSSFTextBox){
        XSSFTextBox textbox = (XSSFTextBox) shape;
        textbox.setText("foo"); // Could take an XSSFRichTextString instead
    }
}

For HSSF:

HSSFPatriarch pat = (HSSFPatriarch) sheet.createDrawingPatriarch();
List<HSSFShape> children = pat.getChildren();
Iterator<HSSFShape> it = children.iterator();
HSSFRichTextString richStr = new HSSFRichTextString("foo"); 

while(it.hasNext()) {           
    HSSFShape shape = it.next();
    if (shape instanceof HSSFTextbox){
        HSSFTextbox textbox = (HSSFTextbox) shape;
        textbox.setString(richStr);
    }
}

It doesn't seem like this solution is very flexible though since setting different values for different Textboxes would require some conditional logic. Luckily for me, I was merely changing all of the Textboxes to the same text.

Adapted from: Obtain textbox value from Excel in Java

I am working with XSSF rather than HSSF. Any idea how to do it for XSSF? — bjedrzejewski, Jul 08 '14 at 16:49
Oh sorry, no I haven't. I updated my answer with what I think will work (using the same basic solution as my HSSF approach). Referred to this: http://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFDrawing.html#getShapes() — Afterglow375, Jul 08 '14 at 19:01
for XSSF, you may change the `XSSFTextbox` with `XSSFSimpleShape` — Taha BASRI, Nov 24 '16 at 19:22

How can I change TextBox in Excel with XSSF from Apache POI

1 Answers1