Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2827
  • Last Modified:

apache POI excel 2007 reading examples

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
0
gudii9
Asked:
gudii9
  • 4
  • 4
  • 2
2 Solutions
 
gudii9Author Commented:
this link doe not have any source code. Please advise any other alternate links, resources
0
 
CEHJCommented:
The page is full of links to source code
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Pramod KumarCommented:
Attached is a modified code from:
http://www.coderanch.com/t/424181/open-source/Read-xls-xlsx-file-format

The sample is reading XLSX

Tested with: poi-3.7
XLSX2CSV.java
0
 
gudii9Author Commented:
i tried the example getting error like

Exception in thread "main" java.lang.NoClassDefFoundError: org/dom4j/DocumentException
      at org.apache.poi.openxml4j.opc.OPCPackage.init(OPCPackage.java:154)
      at org.apache.poi.openxml4j.opc.OPCPackage.<init>(OPCPackage.java:141)
      at org.apache.poi.openxml4j.opc.Package.<init>(Package.java:54)
      at org.apache.poi.openxml4j.opc.ZipPackage.<init>(ZipPackage.java:99)
      at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:207)
      at com.aaa.XLSX2CSV.main(XLSX2CSV.java:392)
Caused by: java.lang.ClassNotFoundException: org.dom4j.DocumentException
      at java.net.URLClassLoader$1.run(URLClassLoader.java:202)

Please advbise
0
 
gudii9Author Commented:
i put dom4j 1.4 jar and

xmlbeans 2.2.0 jar
now i am seeing errror 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
0
 
Pramod KumarCommented:
i used below jars from poi distribution:

dom4j-1.6.1.jar;
poi-3.7-beta2-20100809.jar;
poi-contrib-3.7-beta2-20100809.jar;
poi-examples-3.7-beta2-20100809.jar;
poi-ooxml-schemas-3.7-beta2-20100809.jar;
poi-ooxml-3.7-beta2-20100809.jar;
poi-scratchpad-3.7-beta2-20100809.jar;
xmlbeans-2.3.0.jar
0
 
CEHJCommented:
i used below jars from poi distribution:
...
xmlbeans-2.3.0.jar

i put dom4j 1.4 jar and

xmlbeans 2.2.0 jar
now i am seeing errror like
...
      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?
Which is it?
0
 
gudii9Author Commented:
putting both xmlbean and xbean same version solved issue like

xmlbeans-2.3.0.jar
and
xbean2.3 jar
0
 
CEHJCommented:
So it's ok then?
It's got to be said that's it's almost laughable to have to use these complex apis to read what's essentially a 2 column csv file ;)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now