Apache POI: Get value from textbox

Question

I'm currently writing program which reads values from an excel file. For normal cells it works just fine, but there are also textboxes, selectboxes and comboboxes, which i can't really access. I know, that these elements were created with an id.

Here is my current try:

private void processExcelToXml(File excelFile) throws EncryptedDocumentException, IOException {
        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(excelFile));
    List<HSSFObjectData> embeddedObjects = workbook.getAllEmbeddedObjects();
    List<HSSFObjectData> textBoxList = new ArrayList<>();
    List<HSSFObjectData> comboBoxList = new ArrayList<>();
    List<HSSFObjectData> checkBoxList = new ArrayList<>();


    for (HSSFObjectData hssfObjectData : embeddedObjects) {
        log.info(hssfObjectData.getDirectory().getName());
        switch (hssfObjectData.getOLE2ClassName()) {

            case "Forms.TextBox.1":
                textBoxList.add(hssfObjectData);
                break;
            case "Forms.CheckBox.1":
                checkBoxList.add(hssfObjectData);
                break;
            case "Forms.ComboBox.1":
                comboBoxList.add(hssfObjectData);
                break;
            default:
                break;
        }
    }
}

I know there is an solutuion(Obtain textbox value from Excel in Java), but like the the OP I don't get any instances of HSSFTextbox, but only of HSSFObjectData. My Question is, can I extract the value typed into the textbox from these HSSFObjectDatas?

Thanks!

One suggestion would be to step into your code with an IDE debugger and at the point where you "don't get any instances of HSSFTextbox", poke around to see what the various objects look like. You might be able to find what you need. — Jim Garrison, Mar 15 '16 at 15:43
When I use the solution of http://stackoverflow.com/questions/13967168/obtain-textbox-value-from-excel-in-java I have the same Problem, I get either HSSFObjectData or HSSFPicture. The OLE2Classname resolves to Forms.TextBox.1 or Forms.CheckBox.1 for example. — MaxDak92, Mar 16 '16 at 07:24

score 0 · Answer 1 · answered Aug 24 '17 at 07:38

Resurrecting an old question but I have a Class that reads Excel Textbox String contents from a named textBox. Below is an extract of the relevant parts. Note that the ".trim()" is important as the shape name seems to have a training space added. this caused me a lot of problems.

//Declarations
private HSSFWorkbook wb;
private HSSFWorkbook outputWb;
private HSSFSheet sheet;
private HSSFFont myFont;
private HSSFPatriarch pat;
private HSSFRichTextString str;
private List<HSSFShape> children;

//Constructor
try {
        NPOIFSFileSystem fs = new NPOIFSFileSystem(new File(IN_FILE_NAME));
        wb = new HSSFWorkbook(fs.getRoot(), true);
        sheet = wb.getSheet("Certificate");
        pat = (HSSFPatriarch) sheet.createDrawingPatriarch();
        children = pat.getChildren();

//Methods
private String getText(String textBoxName){
    return getRichText(textBoxName).toString().trim();
}

private  HSSFRichTextString getRichText(String textBoxName) {
    Iterator<HSSFShape> it = children.iterator();
    HSSFRichTextString returnString = null;
    while (it.hasNext()) {
        HSSFShape shape = it.next();
        if (shape.getShapeName().trim().equals(textBoxName)) {
            HSSFTextbox textbox = (HSSFTextbox) shape;
            returnString=textbox.getString();
        }
    }
    return returnString;
}

Apache POI: Get value from textbox

1 Answers1