This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes Other Open Source Projects and the fly likes Read a .xls,.xlsx file format using XSSF Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Products » Other Open Source Projects
Bookmark "Read a .xls,.xlsx file format using XSSF" Watch "Read a .xls,.xlsx file format using XSSF" New topic
Author

Read a .xls,.xlsx file format using XSSF

carina caoor
Ranch Hand

Joined: Jun 23, 2007
Posts: 300

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 ]
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41165
    
  45
How are you running the program? Do you have the XMLBeans library in your run classpath (as opposed to the compile classpath)?


Ping & DNS - my free Android networking tools app
carina caoor
Ranch Hand

Joined: Jun 23, 2007
Posts: 300

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

Joined: Jun 23, 2007
Posts: 300

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
Marshal

Joined: Mar 22, 2005
Posts: 41165
    
  45
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

Joined: Jun 23, 2007
Posts: 300

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
Marshal

Joined: Mar 22, 2005
Posts: 41165
    
  45
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

Joined: Jun 23, 2007
Posts: 300

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
Marshal

Joined: Mar 22, 2005
Posts: 41165
    
  45
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.
Kushagra Bindal
Ranch Hand

Joined: Oct 15, 2008
Posts: 156
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
Marshal

Joined: Mar 22, 2005
Posts: 41165
    
  45
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

Joined: Oct 15, 2008
Posts: 156
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
Marshal

Joined: Mar 22, 2005
Posts: 41165
    
  45
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).
Murty Medisetti
Greenhorn

Joined: Jan 23, 2010
Posts: 22
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
Marshal

Joined: Mar 22, 2005
Posts: 41165
    
  45
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

Joined: Jan 23, 2010
Posts: 22
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.
Shah Hiren
Greenhorn

Joined: Jul 28, 2007
Posts: 21
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


SCJP 1.4, SCWCD 1.4
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41165
    
  45
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

Joined: Jul 28, 2007
Posts: 21
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
Marshal

Joined: Mar 22, 2005
Posts: 41165
    
  45
By "multiple spreadsheets" I meant "multiple files", not "multiple worksheets in a single file".
Shah Hiren
Greenhorn

Joined: Jul 28, 2007
Posts: 21



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

Joined: Jul 28, 2007
Posts: 21
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

Joined: Jul 28, 2007
Posts: 21
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.
Prateek Kumar Singh
Greenhorn

Joined: Jan 21, 2011
Posts: 29

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
Marshal

Joined: Mar 22, 2005
Posts: 41165
    
  45
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
Greenhorn

Joined: Jan 21, 2011
Posts: 29

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.
Vinoda Chejarla
Greenhorn

Joined: Feb 17, 2012
Posts: 3
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
Marshal

Joined: Mar 22, 2005
Posts: 41165
    
  45
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

Joined: Feb 17, 2012
Posts: 3
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??
Basavaraj Malagi
Greenhorn

Joined: Jun 23, 2010
Posts: 4
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

Joined: Jun 23, 2010
Posts: 4
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.
Tim Moores
Rancher

Joined: Sep 21, 2011
Posts: 2408
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

Joined: Jun 23, 2010
Posts: 4
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
Rancher

Joined: Sep 21, 2011
Posts: 2408
Yes, POI needs a lot of memory for large files; you'll have to increase the JVM's memory allocation.
Vinoda Chejarla
Greenhorn

Joined: Feb 17, 2012
Posts: 3
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..
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Read a .xls,.xlsx file format using XSSF
 
Similar Threads
convert excel
The system cannot find the path specified
How to read .xlsx sheet from Client Side using poi 3.5
Converting Old POI Reading .xls Files to POI 3.7 Issues
NullPointerException