Find String Values of Cell Using POI

In this program we are going to find the String Values of Cell of an excel sheet using POI3.0 API Event.

The class org.apache.poi.hssf.record.LabelSSTRecord extends Record implements CellValueRecord interface and java.lang.Comparable
This is used to make reference of a string and a column value.

The methods used in this example:

getSSTIndex():
The method is used to get the index to the string in the SSTRecord. The return type of this method is integer (index of string in the SST Table).

getString(int i):
This method is used to get the string value from SSTRecord .

The code of the program is given below:


import java.io.*;

import org.apache.poi.hssf.usermodel.*;

import org.apache.poi.poifs.filesystem.*;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.eventusermodel.*;

import org.apache.poi.hssf.record.*;

import org.apache.poi.hssf.dev.EFHSSF;

public class FindStringCellsValues implements HSSFListener

{

   public static void main(String[] args) throws IOException

    {

       FileInputStream fin = new FileInputStream(args[0]);

     

        POIFSFileSystem poifs = new POIFSFileSystem(fin);

        InputStream din = poifs.createDocumentInputStream

("Workbook");

          HSSFRequest req = new HSSFRequest();

           req.addListenerForAllRecords

(new FindStringCellsValues());

           HSSFEventFactory factory = new HSSFEventFactory();

            factory.processEvents(req, din);

            fin.close();

             din.close();

        System.out.println("STOP");

    }

  SSTRecord sstrecord;

  public void processRecord(Record record)

    {

        switch (record.getSid()) {

            case SSTRecord.sid:

                sstrecord = (SSTRecord) record;

                for (int k = 0; k < sstrecord.

getNumUniqueStrings(); k++)

                {

                    System.out.println("String 

table value " + k + " = " + sstrecord.getString(k));

                }

        System.out.println("\n\n");

                break;

        

            case LabelSSTRecord.sid:

                LabelSSTRecord lrecord = (LabelSSTRecord) record;

                System.out.println("String cell found with value \t"

                        + sstrecord.getString(lrecord.getSSTIndex()));

                break;

            }

   }    

}

The output of the program is given below:

C:\POI3.0\exmples\execl>javac FindStringCellsValues.java

C:\POI3.0\exmples\execl>java FindStringCellsValues example.xls
String table value 0 = Name Of Example
String table value 1 = Status
String table value 2 = coding is completed
String table value 3 = compressedUnicode.java
String table value 4 = EventAPIsExample.java
String table value 5 = FindNameOfSheet.java
String table value 6 = FindRowAtColumn.java
String table value 7 = FindStringCellsValues.java
String table value 8 = FindValuesRowAtColumn.java
String table value 9 = setDataFormat.java
String table value 10 = setDataFormatForFullList.java
String table value 11 = setNameUnicodeDataFormat.java
String table value 12 = setSheetNameUnicodeDataFormat.java
String table value 13 = setUnicodeDataFormat.java
String table value 14 = TypeOfSheet.java
String table value 15 = TypeOfWorkBook.java



String cell found with value    Name Of Example
String cell found with value    Status
String cell found with value    compressedUnicode.java
String cell found with value    coding is completed
String cell found with value    EventAPIsExample.java
String cell found with value    coding is completed
String cell found with value    FindNameOfSheet.java
String cell found with value    coding is completed
String cell found with value    FindRowAtColumn.java
String cell found with value    coding is completed
String cell found with value    FindStringCellsValues.java
String cell found with value    coding is completed
String cell found with value    FindValuesRowAtColumn.java
String cell found with value    coding is completed
String cell found with value    setDataFormat.java
String cell found with value    coding is completed
String cell found with value    setDataFormatForFullList.java
String cell found with value    coding is completed
String cell found with value    setNameUnicodeDataFormat.java
String cell found with value    coding is completed
String cell found with value    setSheetNameUnicodeDataFormat.java
String cell found with value    coding is completed
String cell found with value    setUnicodeDataFormat.java
String cell found with value    coding is completed
String cell found with value    TypeOfSheet.java
String cell found with value    coding is completed
String cell found with value    TypeOfWorkBook.java
String cell found with value    coding is completed
STOP