Obtain textbox value from Excel in Java

Question

I have an Excel file and I need to read a value from a textbox inside that Excel file.

I am using org.apache.poi library and I tried to obtain the value in the following way:

   List<HSSFObjectData> obj=workbook.getAllEmbeddedObjects();
   for (int i = 0; i < obj.size(); i++) {           
       HSSFTextbox t = (HSSFTextbox) obj.get(i);
   }

Unfortunetly I couldn't cast HSSFTextbox to a HSSFObjectData element.

Does anyone know how could this be done?

The Excel document contains Textboxes and buttons and has VBA code in the back that handles the data from the Textboxes. I have a Java application in which I need the values from the Texboxes. — Mike, Dec 20 '12 at 08:00

lichengwu · Accepted Answer · 2012-12-20T08:10:22.620

Maybe you can do like this:

    try {
        InputStream input = new FileInputStream("qa-textbox.xls");
        POIFSFileSystem fs = new POIFSFileSystem(input);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFPatriarch pat = sheet.getDrawingPatriarch();
        List children = pat.getChildren();

        Iterator it = children.iterator(); 
        while(it.hasNext()) {           
            HSSFShape shape = (HSSFShape)it.next();
            if (shape instanceof HSSFTextbox){
              HSSFTextbox textbox = (HSSFTextbox)shape;
              HSSFRichTextString richString = textbox.getString();
              String str = richString.getString();
              System.out.println("String: " + str);
              System.out.println("String length: " + str.length());
            }
        }  
    } catch (IOException ex) {
        ex.printStackTrace();
    }

Thank you for your answer. I adapted your code in my application. There were no errors but none of the shapes matched HSSFTextbox. Initially when I iterated through the objects using HSSFObjectData and obj.get(i).getOLE2ClassName() the elements printed out were: Forms.TextBox.1 Forms.TextBox.1 Forms.TextBox.1 Forms.Label.1 Forms.TextBox.1 Forms.Label.1 Forms.Label.1 Forms.Label.1 Forms.CommandButton.1 Forms.TextBox.1 and I thought that Forms.TextBox.1 was of HSSFTextbox type — Mike, Dec 20 '12 at 08:27

score 0 · Answer 2 · answered Dec 20 '12 at 07:45

this will help to read your excel sheet

HSSFWorkbook workbook = new HSSFWorkbook(fs);
      for (HSSFObjectData obj : workbook.getAllEmbeddedObjects()) {
          //the OLE2 Class Name of the object
          String oleName = obj.getOLE2ClassName();
          if (oleName.equals("Worksheet")) {
              DirectoryNode dn = (DirectoryNode) obj.getDirectory();
              HSSFWorkbook embeddedWorkbook = new HSSFWorkbook(dn, fs, false);
              //System.out.println(entry.getName() + ": " + embeddedWorkbook.getNumberOfSheets());
        readSheetElements(embeddedWorkbook);

          } else if (oleName.equals("Document")) {
              DirectoryNode dn = (DirectoryNode) obj.getDirectory();
              HWPFDocument embeddedWordDocument = new HWPFDocument(dn, fs);
              //System.out.println(entry.getName() + ": " + embeddedWordDocument.getRange().text());
          }  else if (oleName.equals("Presentation")) {
              DirectoryNode dn = (DirectoryNode) obj.getDirectory();
              SlideShow embeddedPowerPointDocument = new SlideShow(new HSLFSlideShow(dn, fs));
              //System.out.println(entry.getName() + ": " + embeddedPowerPointDocument.getSlides().length);
          } else {
              if(obj.hasDirectoryEntry()){
                  // The DirectoryEntry is a DocumentNode. Examine its entries to find out what it is
                  DirectoryNode dn = (DirectoryNode) obj.getDirectory();
                  for (Iterator entries = dn.getEntries(); entries.hasNext();) {
                      Entry entry = (Entry) entries.next();
                      //System.out.println(oleName + "." + entry.getName());
                  }
              } else {
                  // There is no DirectoryEntry
                  // Recover the object's data from the HSSFObjectData instance.
                  byte[] objectData = obj.getObjectData();
              }
          }
      }

    public void readSheetElements(HSSFWorkbook embeddedWorkbook) {
     HSSFSheet  hSSFSheet = embeddedWorkbook.getSheet(0);
     java.util.Iterator<Row>    rowItr = hSSFSheet .rowIterator() ;
    while(rowItr .hasNext()) {

     java.util.Iterator<Cell>   cellItr  = rowItr.next().cellIterator() ;
     // read cell value from each cell

    }

Obtain textbox value from Excel in Java

2 Answers2

Linked