Using POI in Java

Hi experts,
Pls help to guide me to use POI in Java. I am new to Java and have read many posts about POI but still unable to start using POI.
I have JDK 1.6 and JCreator intalled in my PC. I also downloaded the poi-bin-3.6-20091214 and extracted to C:\jdk6.
Pls help me to modify the classpath...and so on...
Thanks a lot
dalatianAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

miahiCommented:
You don't have to copy it to the jdk, you should extract the POI jar files in your lib folder (where all your project libraries are) and add the jar to the classpath when you compile and run (the -cp option for javac and java). Otherwise, just add poi-3.6-20091214.jar (and the others if needed, like log4j-1.2.13.jar and poi-ooxml-schemas-3.6-20091214.jar) to the classpath.

So, if you have a class called POITest.java that contains code using POI, and the POI jars are in the lib folder, you compile it with
javac -cp .;lib/poi-3.6-20091214.jar;lib/log4j-1.2.13.jar POITest.java
and you run it with
java -cp .;lib/poi-3.6-20091214.jar;lib/log4j-1.2.13.jar POITest

For JCreator, see http://www.skylit.com/javamethods/faqs/jcreator4.html#jars
0
dalatianAuthor Commented:
Thank you for your respond. I've tried but here is the message:
Exception in thread "main" java.lang.NullPointerException
    at org.apache.poi.poifs.filesystem.POIFSFileSystem.closeInputStream(POIFSFileSystem.java:196)
    at org.apache.poi.poifs.filesystem.POIFSFileSystem.<init>(POIFSFileSystem.java:158)
    at OpenExcel.main(OpenExcel.java:28)

Process completed.
My questions are:
1- the lib folder means c:\jdk6\lib or c:\jdk6\jre\lib? (the jdk is installed in c:\jdk6)
2- where should I put the lines:
   javac -cp .;lib/poi-3.6-20091214.jar;lib/log4j-1.2.13.jar POITest.java
    and
   java -cp .;lib/poi-3.6-20091214.jar;lib/log4j-1.2.13.jar POITest
Pls help because I new to java, thank you very much,
0
miahiCommented:
Looks like the POI jar is found when you run the program, but there is another problem there. Is OpenExcel your test program? Can you share the sourcecode?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

dalatianAuthor Commented:
I just try to test using POI with this code fragment:


import java.lang.Object;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFRow;

/**
 * A simple POI example of opening an Excel spreadsheet
 * and writing its contents to the command line.
 * @author  Tony Sintes
 */
public class OpenExcel
{
    public static void main( String [] args )
          {
                
        try
              {
            InputStream input = OpenExcel.class.getResourceAsStream( "c:/List.xls" );
            POIFSFileSystem fs = new POIFSFileSystem( input );
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheetAt(0);
           
            // Iterate over each row in the sheet
            Iterator rows = sheet.rowIterator();
            while( rows.hasNext() ) {          
                HSSFRow row = (HSSFRow) rows.next();
                System.out.println( "Row #" + row.getRowNum() );
 
                // Iterate over each cell in the row and print out the cell's content
                Iterator cells = row.cellIterator();
                while( cells.hasNext() ) {
                    HSSFCell cell = (HSSFCell) cells.next();
                    System.out.println( "Cell #" + cell.getCellNum() );
                    switch ( cell.getCellType() ) {
                        case HSSFCell.CELL_TYPE_NUMERIC:
                            System.out.println( cell.getNumericCellValue() );
                            break;
                        case HSSFCell.CELL_TYPE_STRING:
                            System.out.println( cell.getStringCellValue() );
                            break;
                        default:
                            System.out.println( "unsuported cell type" );
                            break;
                    }
                }      
            }  
        } catch ( IOException ex ) {
            ex.printStackTrace();
        }
    }
   
}

Thanks,
0
VenabiliCommented:
One thing that I do not see in this code is a closing of the input stream -- please never leave streams open like this...

Might not be the issue, but let's get this cleared out first.

And because this sounded familiar:
http://forums.sun.com/thread.jspa?threadID=5321509

In short: make sure that your resource is loading properly - that's the error that usually shows up when the file cannot be found.
0
dalatianAuthor Commented:
I did add the line
InputStream input = new FileInputStream(c:\List.xls );
but there is a compile error saying that "can not find symbol class FileInputStream"
I need help to check/guide me through whether I setup the POI correct or not, since I am even able to test any single line of codes using POI so far,
Thank you,
0
dalatianAuthor Commented:
Please help:
1- How to use POI in JDK and JCreator?
2- There are multiple \lib in jdk. Pls instruct which is the one we shoule place POI files in?
Thank you,
0
VenabiliCommented:
>but there is a compile error saying that "can not find symbol class FileInputStream"
Just import it then.

The code looks ok, what is wrong is the way you get the excel file. Did you see the link I posted above?
0
dalatianAuthor Commented:
I followed the link you posted and  imported java.io.InputStream and used FileInputStream, but there is still a compile error indicating that "can not find symbol constructor FileInputStream (java.io.InpuStream).
Just want to know if my POI setting-up is right? and what else can I do to be able to start using POI for my program.
Pls help!
0
VenabiliCommented:
The compilation error is a core Java one, not a POI one...

Again - are you sure that your file is read correctly? The link above is to a solution to the same problem you have now so either use the code from there or change your code accordingly. In any way - your POI looks fine, something else is happening -- and I suspect you simply cannot read the resource.
0
dalatianAuthor Commented:
The problem is that I can compile other programs without any issue, but when I import the org.apache.poi.poifs.filesystem.POIFSFileSystem or org.apache.poi.hssf.usermodel.HSSFCell,  the compile error occurs as following

Note: C:\JAVA\Project_Work\src\OpenExcel.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.

Process completed.
honestly, I do not know how to recompile with -Xlint, do you have any idea? I greatly appreciate it.
0
miahiCommented:
That's just a warning, the code should compile and work even with that note. The problem is elsewhere.
0
dalatianAuthor Commented:
The program does nothing, just out the following message when I run the program:

Exception in thread "main" java.lang.NullPointerException
    at org.apache.poi.poifs.filesystem.POIFSFileSystem.closeInputStream(POIFSFileSystem.java:196)
    at org.apache.poi.poifs.filesystem.POIFSFileSystem.<init>(POIFSFileSystem.java:158)
    at OpenExcel.main(OpenExcel.java:30)

Process completed.

Do you have any idea of where this err comes from? Is it probably from my JCreator or from my core Java setting? But I do not know how to fix it.
0
miahiCommented:
I attached the correct version (opening the file with FileInputStream).

To test:

- put the OpenExcel.java file in an empty directory
- copy the poi-3.6-20091214.jar to the same directory
- open a command prompt and go to that directory
- run the compile command: javac -cp .;poi-3.6-20091214.jar OpenExcel.java
- start the program: java -cp .;poi-3.6-20091214.jar OpenExcel

Try to understand how Java works before you start working with POI and complicated programs.
import java.lang.Object;
import java.io.IOException;
import java.io.InputStream;
import java.io.FileInputStream;
import java.util.Iterator;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFRow;

/**
 * A simple POI example of opening an Excel spreadsheet
 * and writing its contents to the command line.
 * @author  Tony Sintes
 */
public class OpenExcel
{
    public static void main( String [] args )
          {
                
        try
              {
            InputStream input = new FileInputStream( "c:/List.xls" );
            POIFSFileSystem fs = new POIFSFileSystem( input );
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheetAt(0);
           
            // Iterate over each row in the sheet
            Iterator rows = sheet.rowIterator();
            while( rows.hasNext() ) {          
                HSSFRow row = (HSSFRow) rows.next();
                System.out.println( "Row #" + row.getRowNum() );
 
                // Iterate over each cell in the row and print out the cell's content
                Iterator cells = row.cellIterator();
                while( cells.hasNext() ) {
                    HSSFCell cell = (HSSFCell) cells.next();
                    System.out.println( "Cell #" + cell.getCellNum() );
                    switch ( cell.getCellType() ) {
                        case HSSFCell.CELL_TYPE_NUMERIC:
                            System.out.println( cell.getNumericCellValue() );
                            break;
                        case HSSFCell.CELL_TYPE_STRING:
                            System.out.println( cell.getStringCellValue() );
                            break;
                        default:
                            System.out.println( "unsuported cell type" );
                            break;
                    }
                }      
            }  
        } catch ( IOException ex ) {
            ex.printStackTrace();
        }
    }
   
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dalatianAuthor Commented:
Yes, thanks a lot.
It runs now when I place poi-3.6-20091214.jar and OpenExcel.java in the same directory.
Thank you very much for a long follow-up.
It is highly appreciated,
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.