• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Read a .xls,.xlsx file format using XSSF

 
Ranch Hand
Posts: 300
Eclipse IDE Oracle Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi in my application i want to read a .xls and .xlsx file and print the contents in xml format so that i can load a dhtmlx grid which accepts only csv, xml files.To acheieve this i have written a servlet using POI-XSSF
EXCELFileRead.java



here xls_filename containts the name of file "test.xlsx" my program compiles fine but when i run it it shows me error

java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlObject


Please suggest me wether i am reading the file properly ,
i imported all the jars and these jars are in my project build path too
Please help me out in this issue.
[ October 22, 2008: Message edited by: Martijn Verburg ]
 
Rancher
Posts: 43081
77
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
How are you running the program? Do you have the XMLBeans library in your run classpath (as opposed to the compile classpath)?
 
carina caoor
Ranch Hand
Posts: 300
Eclipse IDE Oracle Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
my class path is
C:\Tomcat 5.5\webapps\ExcelRead\src\excelread>javac -classpath .;"c:\Tomcat 5.5\
common\lib\poi-3.5-beta3-20080926.jar";"c:\Tomcat 5.5\common\lib\poi-jdk14-3.5-b
eta3-20080926.jar";"c:\Tomcat 5.5\common\lib\poi-contrib-3.5-beta3-20080926.jar"
;"c:\Tomcat 5.5\common\lib\poi-ooxml-3.5-beta3-20080926.jar";"c:\Tomcat 5.5\comm
on\lib\poi-scratchpad-3.5-beta3-20080926.jar";"c:\Tomcat 5.5\common\lib\servlet-
api.jar";"c:\Tomcat 5.5\common\lib\jsp-api.jar";"c:\Tomcat 5.5common\lib\ooxml-
schemas.jar";"c:\Tomcat 5.5\common\lib\openxml4j-bin-beta-080728.jar" -Xlint FAFilePopulate.java


but my program gets compiled
 
carina caoor
Ranch Hand
Posts: 300
Eclipse IDE Oracle Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I downloaded xmlbeans-2.3.0 and added xmlbeans-qname.jar,xbean.jar, xbean_xpath.jar in my project build path and also in my projects WEB-INF/lib folder now when i run the program it gives me the following error


java.lang.NoClassDefFoundError: org/apache/log4j/Logger
org.openxml4j.opc.Package.<clinit>(Package.java:72)
org.apache.poi.POIXMLDocument.openPackage(POIXMLDocument.java:98)
org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:104)
excelread.FAFilePopulate.doGet(FAFilePopulate.java:46)
javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)


seeing the above error i added log4j-1.2.13.jar file in my project build path and WEB-INF/lib folder and when i restarted the server and on running the program it gives me
javax.servlet.ServletException: Servlet execution threw an exception


root cause

java.lang.NoClassDefFoundError: org/dom4j/DocumentException
org.openxml4j.opc.Package.init(Package.java:154)
org.openxml4j.opc.Package.<init>(Package.java:141)
org.openxml4j.opc.ZipPackage.<init>(ZipPackage.java:109)
org.openxml4j.opc.Package.open(Package.java:203)
org.openxml4j.opc.Package.open(Package.java:182)
org.apache.poi.POIXMLDocument.openPackage(POIXMLDocument.java:98)
org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:104)
excelread.FAFilePopulate.doGet(FAFilePopulate.java:46)
javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)



I am not using log4j but still i added the jar file ,
i dont no what to do next please help me to solve this issue.
 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
openxml4j requires log4j and dom4j, so you need to include the relevant libraries. This is mentioned in the openxml4j documentation.

Having both poi-3.5-beta3-20080926.jar and poi-jdk14-3.5-beta3-20080926.jar looks wrong to me. I think they are the same, with one of them compiled to run on Java 1.4 JVMs. Having both might cause hard-to-diagnose problem.
 
carina caoor
Ranch Hand
Posts: 300
Eclipse IDE Oracle Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
thanks for your reply Ulf .. i added the library dom4j.jar and my program is compiled and runs now but when its reading the excel sheet instead of printing the data from the excel it printing cell array numbers

my test.xlsx file
[code]
----------------------------------------------------------------------------

CM_BATCH_SEQ CM_ASPCL_CD CM_PLOT_UP_STG_FLG VERSION CM_BATCH_SEQ
1 A01/008/02-002+003 P 2 1
1 A02/006/00-016 P 2 1
1 A01/008/10-002 P 2 1
1 A01/009A/02-018 P 2 1
---------------------------------------------------------------------------

When i run my program with input test.xlsx file it gives me
OutPut:-
[code]
----------------------------------------------------------------------------

<rows>
- <row>
<cell>[0,0] 0</cell>
<cell>[0,1] 1</cell>
<cell>[0,2] 2</cell>
<cell>[0,3] 3</cell>
<cell>[0,4] 0</cell>
</row>
- <row>
<cell>[1,0] 1</cell>
<cell>[1,1] 4</cell>
<cell>[1,2] 5</cell>
<cell>[1,3] 2</cell>
<cell>[1,4] 1</cell>
</row>
- <row>
<cell>[2,0] 1</cell>
<cell>[2,1] 6</cell>
<cell>[2,2] 5</cell>
<cell>[2,3] 2</cell>
<cell>[2,4] 1</cell>
</row>
- <row>
<cell>[3,0] 1</cell>
<cell>[3,1] 7</cell>
<cell>[3,2] 5</cell>
<cell>[3,3] 2</cell>
<cell>[3,4] 1</cell>
</row>
- <row>
<cell>[4,0] 1</cell>
<cell>[4,1] 8</cell>
<cell>[4,2] 5</cell>
<cell>[4,3] 2</cell>
<cell>[4,4] 1</cell>
</row>
</rows>
---------------------------------------------------------------------------

while retrieving the value of each cell what am i doing wrong that its printing some cell numbers instead of data, can you please suggest me...

[ October 22, 2008: Message edited by: ruquia tabassum ]

[ October 22, 2008: Message edited by: ruquia tabassum ]
[ October 22, 2008: Message edited by: ruquia tabassum ]
 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
For starters, when you post code of any length, please UseCodeTags. It makes it much more readable, and thus much more likely that people will be willing to look at it. Please go back and edit your first post to use those tags.

The problematic line is this one:

If you concatenate an object reference -like cell- to a string, then its toString method will be called to produce a string. This is in most cases not what you want. You'll need to look at the javadocs of whatever class cell is an object of, and see if there's some method that returns the value of the cell.
 
carina caoor
Ranch Hand
Posts: 300
Eclipse IDE Oracle Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Now when i give .xlsx file as input its reading its contents and giving me the cell value, but when i give .xls file as input its raising an error
[code]
-----------------------------------------------------------------------


C:\Tomcat 5.5\webapps\ExcelRead\Copy of CM_FA_PLOT_STG_UP.xls
org.openxml4j.exceptions.InvalidOperationException: Can't open the specified file: 'C:\Tomcat 5.5\webapps\ExcelRead\Copy of CM_FA_PLOT_STG_UP.xls'
at org.openxml4j.opc.ZipPackage.<init>(ZipPackage.java:113)
at org.openxml4j.opc.Package.open(Package.java:203)
at org.openxml4j.opc.Package.open(Package.java:182)
at excelread.FAFileXLSX.doGet(FAFileXLSX.java:41)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:174)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:174)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:874)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:689)
at java.lang.Thread.run(Unknown Source)

----------------------------------------------------------------------------

Cant XSSF read a .xls file? if it could read a file of new version(2007 format)then it must be able to read a file of old version(97-2003 format)
[ October 22, 2008: Message edited by: ruquia tabassum ]
 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

if it could read a file of new version (2007 format) then it must be able to read a file of old version(97-2003 format)


No. The formats are fundamentally different. You'll have to write code using the HSSF package to read XLS files.
 
Ranch Hand
Posts: 156
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have also got the same exception.
So is there a way to solve the same problem.
Actually I am reading the ms word document 2003 with the help of latest poi version.

Thanks
Kushagra
 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

I have also got the same exception.
So is there a way to solve the same problem.
Actually I am reading the ms word document 2003 with the help of latest poi version.


The problem in the original question was using the Office XML classes for Office binary files, as I pointed out in my previous post. Maybe that's the problem in your case too?
 
Kushagra Bindal
Ranch Hand
Posts: 156
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
org.openxml4j.exceptions.InvalidOperationException: Can't open the specified file: 'C:\Documents and Settings\testing\sample1\Test4.doc'
at org.openxml4j.opc.ZipPackage.<init>(ZipPackage.java:113)
at org.openxml4j.opc.Package.open(Package.java:204)
at org.openxml4j.opc.Package.open(Package.java:183)
at org.apache.poi.POIXMLDocument.openPackage(POIXMLDocument.java:63)
at Test.mymain(Test.java:227)
at SampleMainFrame.doProgressBar(SampleMainFrame.java:348)
at SampleMainFrame.access$0(SampleMainFrame.java:331)
at SampleMainFrame$1.actionPerformed(SampleMainFrame.java:269)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
at java.awt.AWTEventMulticaster.mouseReleased(Unknown Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)



This is my exception.

This exception is coming on this line.

POIXMLTextExtractor extractor = new XWPFWordExtractor(POIXMLDocument.openPackage(args));

Here args is file address.
'C:\Documents and Settings\testing\sample1\Test4.doc'


Please suggest any way to proceed

Thanks
Kushagra
 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
So it is the same problem - you're using the Office XML classes for an Office binary file. Note that ".doc" is for Office binary files (for which POI has the HWPF classes), and ".docx" is for the Office XML formats (for which POI has the XWPF classes).
 
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi, I am using XSSF to read .xlsx files. I am able to execute successfully in Eclipse.
But while compiling with ant in my project I am getting error as

bad class file: poi-ooxml-3.6-20091214.jar(org/apache/poi/xssf/usermodel/
class file has wrong version 49.0, should be 48.0
My project is using Java1.4
Please suggest me how can I resolve this issue.
 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
POI 3.5 and later (which support XLSX files) require Java 5, so you can't use a Java 1.4 JVM.
 
Murty Medisetti
Greenhorn
Posts: 24
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
InputStream input = ftp.retrieveFileStream("MPD Variable Table.xlsx");
workbook = new XSSFWorkbook(input); --> OutofMemoryError

I am reading Excel file from FTP and sending inputstream to the work book. For smaller sizes my code is working fine. But Excel in production has more than 40,000 rows and 255 cells with some fancy(colors, etc.,) alignment. So the size is coming as 32 MB. While loading into work book I am getting error as OutofMemoryError. I increased to 2GB (-Xmx2048), but still I am not able to resolve. How can I fix this. The Excel have lot of fancy things, So I don't want to read all those. I need data to insert into table. This is a batch job in my Production system.
 
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi
I also facing out of memory error.How can i fix that?
Did you use any other API.What was the solution that you found
Thanks
Hiren
 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
POI does need a lot of memory for large spreadsheets. Have you considered using multiple spreadsheets with correspondingly less data? No user should be subjected to 40000 rows of data anyway - that's the domain of databases, not spreadsheets.
 
Shah Hiren
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The out of memory comes for reading excel file

System.out.println("Before workbook creation");
XSSFWorkbook wb1 = new XSSFWorkbook(filename);
System.out.println("after workbook creation");


So if the size is large it doesnt read at all.How can this be resolved.Even if the worksheet is broken down still the size of file remains the same.how can this be resolved.?

Also i was considering to use sql loader.You have any idea how can that be used?

 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
By "multiple spreadsheets" I meant "multiple files", not "multiple worksheets in a single file".
 
Shah Hiren
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator



This code helps to read xlsx file as big as 10 lakhs records.Beacuse of sax parser.Apache poi 3.7 is needed to run this file.
 
Shah Hiren
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have very hard time removing A20 spaces in excel sheet. 
i viewed excel cell in pspad and got following thing it in hex as A0
It can be removed using replaceAll("[\\xA0]+","")


 
Shah Hiren
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Convert the string to hex format and then check the char that is creating problem.Then search on google to find solution to that character.
 
Ranch Hand
Posts: 30
Hibernate Eclipse IDE Oracle
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
dear sir,

i want to read a excel file having .xlsx extension using apache poi 3.8 but when i pass InputStream Object inside HSSFWorkbook () i find exception like....

org.apache.poi.poifs.filesystem.OfficeXmlFileException: 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)
at org.apache.poi.poifs.storage.HeaderBlockReader.<init>(HeaderBlockReader.java:98)
at org.apache.poi.poifs.filesystem.POIFSFileSystem.<init>(POIFSFileSystem.java:151)
at com.upload.File_Upload_Servlet.uploadFile1(File_Upload_Servlet.java:421)
at com.upload.File_Upload_Servlet.doPost(File_Upload_Servlet.java:93)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

my code is...



InputStream fis=fieldItem.getInputStream();
POIFSFileSystem fs = new POIFSFileSystem(fis);
HSSFWorkbook workbook1=new HSSFWorkbook(fis);


HSSFSheet sheet1=workbook1.getSheetAt(0);
System.out.println("the poi sheet name is.."+sheet1.getSheetName());
for(Row row:sheet1)
{
for(org.apache.poi.ss.usermodel.Cell cell:row)
{
System.out.println(cell.getStringCellValue()+" ....cell values are>>"+cell.getRichStringCellValue().toString());

/*switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
System.out.println(cell.getRichStringCellValue().getString());
break;
case cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
System.out.println(cell.getDateCellValue());
} else {
System.out.println(cell.getNumericCellValue());
}
break;
case cell.CELL_TYPE_BOOLEAN:
System.out.println(cell.getBooleanCellValue());
break;
case cell.CELL_TYPE_FORMULA:
System.out.println(cell.getCellFormula());
break;
default:
System.out.println();
} */
}



please guide me and send a sample code tt read input stream object inside workbook.
 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The HSSF classes are for old-style files, not 2007 files. You need to rewrite the code to use the XSSF (or, even better, the SS) classes. The POI web site goes into great detail about all this.
 
Prateek Kumar Singh
Ranch Hand
Posts: 30
Hibernate Eclipse IDE Oracle
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
dear friends,
there is a exception...

org.apache.poi.openxml4j.exceptions.InvalidFormatException: Package should contain a content type part [M1.13]
at org.apache.poi.openxml4j.opc.ZipPackage.getPartsImpl(ZipPackage.java:147)

i use the code...

response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

the code for reading data..

InputStream inp=fieldItem.getInputStream();
//POIFSFileSystem fs = new POIFSFileSystem(fis);
XSSFWorkbook workbook1=(XSSFWorkbook) WorkbookFactory.create(inp);


can anybody help me solve this problem.
 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

I need help in reading xls and xlsx format files using POI jars.

I want both xls and xlsx files to be supported in our application.
Even after updating to poi3.5 jars, I am not able to read xlsx files. I tried using XSSFWorkbook in my code for reading xlsx files, but getting NoSuchMethodError. Can anyone help me in fixing this?

Current code being used for supporting xls file :
-------------------------------------------------------

public ExcelParser(File file) throws MatrixException {
this.file = file;
try {
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
this.wb = new HSSFWorkbook(fs);
}catch (Exception ex) {
logger.error(ex.getStackTrace(), ex);
String sPoifsMsg = ex.toString();
if (sPoifsMsg.indexOf("Invalid header signature") != -1 && sPoifsMsg.indexOf("-2226271756974174256") != -1) {
// Very likely (but I'm not 100% sure) this means that the file format is not Excel Workbook.
// It is better to present a message that the user understands (even if there is a small risk
// that the message can be incorrect) than to present a message that 99 percent of the users
// dont understand.
throw new MatrixException("Wrong File Format. Make sure the file is a Microsoft Office Excel Workbook.");
} else if (sPoifsMsg.indexOf("RecordFormatException") != -1) {
throw new MatrixException("Invalid format. Make sure that no formatting, e.g. AutoFilter, has been applied to the spreadsheet.");
} else {
throw new MatrixException(ex.getMessage());
}
}
catch (Throwable e) {
Runtime rt = Runtime.getRuntime();
logger.error("\n\tTotal heap memory : " + rt.totalMemory());
logger.error("\n\tTotal free memory : " + rt.freeMemory() + "\n");
logger.error(e.toString());
throw new MatrixException(e.toString());
}

How can I update the same code for supporting both xls and xlsx files? Please HELP!!
 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
That error message generally means that you're using different jar files for compiling and execution; make sure you use the same jars everytime.

If your code should handle both file formats then you need to rewrite it to use the classes in the ...ss... package, not ...hssf... or ...xssf... There's a page on the POI web site that explains what's involved.
 
Vinoda Chejarla
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thankx for your quick reply. I have gone through apache site, http://poi.apache.org/spreadsheet/quick-guide.html and
applied ss package classes but still getting the error.

I added following jar files in my lib folder:

ooxml-schemas-1.0.jar
dom4j-1.6.1.jar
poi-3.5-FINAL-20090928.jar
poi-contrib-3.5-FINAL-20090928.jar
poi-scratchpad-3.5-FINAL-20090928.jar
poi-ooxml-3.5-FINAL-20090928.jar

Modified Code in try block:
------------------------------

import statements used are,

import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;


private Workbook wb = null;

public ExcelParser(File file) throws MatrixException {

this.file = file;
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
this.wb = WorkbookFactory.create(fs);

}

I am getting following error.

java.io.IOException: Invalid header signature; read 1688935826934608, expected -2226271756974174256
at org.apache.poi.poifs.storage.HeaderBlockReader.<init>(HeaderBlockReader.java:88)
at org.apache.poi.poifs.filesystem.POIFSFileSystem.<init>(POIFSFileSystem.java:83)
at com.ericsson.mars.util.ExcelParser.<init>(ExcelParser.java:142)


Any solution on this??
 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello Everyone'
I am a java greenhorn. I have a requirement of reading a large xlsx sheet using xssf. Can someone help me understand how to do this. And also please tell me what role has SAX parser to play in this.
 
Basavaraj Malagi
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
HI,
I tried using your code but I am getting a warning that XSSFWorkbook is deprecated. Is there any other method I can use. Please help me with this.
 
Saloon Keeper
Posts: 7582
176
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The library does the XML processing; you don't need to concern yourself with how that's done, SAX or otherwise.

The javadocs should tell you which methods to use instead of any deprecated ones.

Also, it's best not to use the HSSF or XSSF classes, since those handle only XLS or XLSX. If you use the classes in the org.apache.poi.ss.... packages, your code will be able to handle both formats.
 
Basavaraj Malagi
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you for the info.
I want to process the data which has over a million rows. So I asked about the sax parser. When I tried processing such large data the heap memory was not enough and I got an error.

This forum has been very helpful for me..

Thanks tim.
 
Tim Moores
Saloon Keeper
Posts: 7582
176
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes, POI needs a lot of memory for large files; you'll have to increase the JVM's memory allocation.
 
Vinoda Chejarla
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

I updated jars to 3.7 version. Following are the jars I added to classpath.

poi-3.7-20101029.jar
poi-ooxml-3.7-20101029.jar
poi-scratchpad-3.7-20101029.jar
poi-ooxml-schemas-3.7-20101029.jar

Piece of code used for reading existing excel file:

InputStream fs = new FileInputStream(file);
Workbook wb = WorkbookFactory.create(fs);

Getting following error when trying to read xls and xlsx file.

Error- java.lang.NoSuchMethodError: org.apache.poi.poifs.filesystem.POIFSFileSystem.hasPOIFSHeader(Ljava/io/InputStream;)Z

Modified code as,

POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
Workbook wb = WorkbookFactory.create(fs);

Then, it works fine for xls but not for xlsx format. getting following error when trying to read .xlsx format

Error- Invalid header signature; read 1688935826934608, expected -2226271756974174256

I don't have any old versions of poi jars in my classpath. Please help me in fixing this..
 
Ranch Hand
Posts: 230
IntelliJ IDE Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am also reading xlsx format file.

When reading the file I am getting error as below :-

Caused by: java.lang.ClassNotFoundException: org.apache.xmlbeans.XmlException

I have added the below jars to my project :-
poi-3.9.jar
poi.3.11.jar
poi-ooxml-3.8.jar
dom4j-1.6.1.jar


Any other jars needs to be added.


 
Santosh Kumar Nayak
Ranch Hand
Posts: 230
IntelliJ IDE Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Exception occurs at the following line :-

XSSFWorkbook workbook = new XSSFWorkbook(fis);
 
Santosh Kumar Nayak
Ranch Hand
Posts: 230
IntelliJ IDE Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Its Resolved.
 
Any sufficiently advanced technology will be used as a cat toy. And this tiny ad contains a very small cat:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic