aspose file tools*
The moose likes Other Open Source Projects and the fly likes apache POI excel 2007 reading examples Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Products » Other Open Source Projects
Bookmark "apache POI excel 2007 reading examples" Watch "apache POI excel 2007 reading examples" New topic
Author

apache POI excel 2007 reading examples

sai rama krishna
Ranch Hand

Joined: May 29, 2009
Posts: 160
Hi,

I am looking for good examples on how to read.xlsx files using apache POI

I am getting error as follows when I try to read it

The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)
Any ideas, suggestions, sample code, links, source code highly appreciated. Thanks in advance
Steve Luke
Bartender

Joined: Jan 28, 2003
Posts: 3932
    
  17

For spreadsheets, there are 3 base packages that you can work in:

org.apache.poi.hssf
For example, using the HSSFWorkbook (org.apache.poi.hssf.usermodel.HSSFWorkbook)

These classes are for working with *.xls files.


org.apache.poi.xssf
For example, using the XSSFWorkbook (org.apache.poi.xssf.usermodel.XSSFWorkbook)

These classes are for working with *.xlsx files.


org.apache.poi.ss

Generally, you want to be less specific - you don't want to have to know and choose between *.xls or *.xlsx file so POI has this third package which is more interfaces and factories for working with either type of file. I think most of the time you want to work with the classes in this package. For example:
org.apache.poi.ss.usermodel.WorkbookFactory
Is a great generic place to start to open up org.apache.poi.ss.usermodel.Workbooks that may be from either .xls or .xlsx files. From there you work with the interfaces for Sheets, etc...

See the API here: http://poi.apache.org/apidocs/index.html

Steve
sai rama krishna
Ranch Hand

Joined: May 29, 2009
Posts: 160


i tried the code like above getting error like

Exception in thread "main" java.lang.ExceptionInInitializerError
at sun.misc.Unsafe.ensureClassInitialized(Native Method)
at sun.reflect.UnsafeFieldAccessorFactory.newFieldAccessor(UnsafeFieldAccessorFactory.java:25)
at sun.reflect.ReflectionFactory.newFieldAccessor(ReflectionFactory.java:122)
at java.lang.reflect.Field.acquireFieldAccessor(Field.java:918)
at java.lang.reflect.Field.getFieldAccessor(Field.java:899)
at java.lang.reflect.Field.get(Field.java:358)
at org.apache.xmlbeans.XmlBeans.typeSystemForClassLoader(XmlBeans.java:770)
at org.openxmlformats.schemas.spreadsheetml.x2006.main.StyleSheetDocument.<clinit>(Unknown Source)
at org.openxmlformats.schemas.spreadsheetml.x2006.main.StyleSheetDocument$Factory.parse(Unknown Source)
at org.apache.poi.xssf.model.StylesTable.readFrom(StylesTable.java:121)
at org.apache.poi.xssf.model.StylesTable.<init>(StylesTable.java:92)
at org.apache.poi.xssf.eventusermodel.XSSFReader.getStylesTable(XSSFReader.java:90)
at com.aa.XLSX2CSV.process(XLSX2CSV.java:358)
at com.aaa.XLSX2CSV.main(XLSX2CSV.java:394)
Caused by: java.lang.RuntimeException: Could not instantiate SchemaTypeSystemImpl (java.lang.reflect.InvocationTargetException): is the version of xbean.jar correct?
at schemaorg_apache_xmlbeans.system.sE130CAA0A01A7CDE5A2B4FEB8B311707.TypeSystemHolder.loadTypeSystem(Unknown Source)
at schemaorg_apache_xmlbeans.system.sE130CAA0A01A7CDE5A2B4FEB8B311707.TypeSystemHolder.<clinit>(Unknown Source)
... 14 more
Caused by: java.lang.reflect.InvocationTargetException
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
... 16 more
Caused by: org.apache.xmlbeans.SchemaTypeLoaderException: XML-BEANS compiled schema: Incompatible minor version - expecting up to 23, got 24 (schemaorg_apache_xmlbeans.system.sE130CAA0A01A7CDE5A2B4FEB8B311707.index) - code 3
at org.apache.xmlbeans.impl.schema.SchemaTypeSystemImpl$XsbReader.<init>(SchemaTypeSystemImpl.java:1522)
at org.apache.xmlbeans.impl.schema.SchemaTypeSystemImpl.initFromHeader(SchemaTypeSystemImpl.java:260)
at org.apache.xmlbeans.impl.schema.SchemaTypeSystemImpl.<init>(SchemaTypeSystemImpl.java:183)
... 20 more


Please advise
Steve Luke
Bartender

Joined: Jan 28, 2003
Posts: 3932
    
  17

Caused by: org.apache.xmlbeans.SchemaTypeLoaderException: XML-BEANS compiled schema: Incompatible minor version - expecting up to 23, got 24


Looks like you have the wrong version of some JAR...

Caused by: java.lang.RuntimeException: Could not instantiate SchemaTypeSystemImpl (java.lang.reflect.InvocationTargetException): is the version of xbean.jar


Probably the xbean.jar.
sai rama krishna
Ranch Hand

Joined: May 29, 2009
Posts: 160
Is there is any sample application source code link with all the required proper version jars. I am going through lot of trial and error between many jars and their versions. Please advise
sai rama krishna
Ranch Hand

Joined: May 29, 2009
Posts: 160
I checked this link

http://www.coderanch.com/t/424181/open-source/Read-xls-xlsx-file-format

this is also incomplete. Please advise
Steve Luke
Bartender

Joined: Jan 28, 2003
Posts: 3932
    
  17

As far as I recall, the download for POI contains all the JARs and resources you need to run it. Here is what I would do:

1) Download a fresh version of POI (the complete package, not the component parts
2) Look inside to see what JAR files it contains
3) Search your entire computer for all versions of all of those JARs and get rid of them
4) Unpack the newly downloaded version of POI
5) Add POI's jars (and those in the two lib/ directories) to your classpath.
sai rama krishna
Ranch Hand

Joined: May 29, 2009
Posts: 160

putting both xmlbean and xbean same version solved issue like

xmlbeans-2.3.0.jar
and
xbean2.3 jar
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: apache POI excel 2007 reading examples
 
Similar Threads
want to get more then 256 columns in XL using JXL API
Read a .xls,.xlsx file format using XSSF
How to read .xlsx sheet from Client Side using poi 3.5
Reading word Documents Using JAVA
Opening an Excel file