File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Other JSE/JEE APIs and the fly likes How to read .xlsx sheet from Client Side using poi 3.5 Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Other JSE/JEE APIs
Bookmark "How to read .xlsx sheet from Client Side using poi 3.5" Watch "How to read .xlsx sheet from Client Side using poi 3.5" New topic
Author

How to read .xlsx sheet from Client Side using poi 3.5

Anshul Agrawal
Greenhorn

Joined: Jul 15, 2008
Posts: 19
Hello All


Please somebody explain and send me code to read .xlsx file from client side .. read its cell contents and then manipulate the data using POI 3.5 API ?

Please help


Regards
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 39548
    
  27
The POI site has lots of examples: http://poi.apache.org/spreadsheet/quick-guide.html That page should also be part of the POI distribution you downloaded. You need to use the "XSSF" API calls; the "HSSF" calls are for the old binary Excel format (.xls).


Ping & DNS - updated with new look and Ping home screen widget
Anshul Agrawal
Greenhorn

Joined: Jul 15, 2008
Posts: 19
Thank you Sir

I tried ..but i am really not getting it.
First i have to create a file ???
Then read it contents?

I have a file directly at client FTP server which is a .xlsx .
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 39548
    
  27
First i have to create a file ?

No; why do you think that? The sections "Reading and Rewriting Workbooks" and "Iterate over rows and cells" should be particularly helpful.
Anshul Agrawal
Greenhorn

Joined: Jul 15, 2008
Posts: 19
When i am running my thread it says.

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)



Code >>>

salesFileName is the name of the File .


//File salesFile = new File(destDir + salesFileName);

InputStream inputStream = null;

try {
inputStream = new FileInputStream(destDir);
} catch (FileNotFoundException e) {
System.out.println("File not found in the specified path.");
e.printStackTrace();
}

try {
fileSystem = new POIFSFileSystem(inputStream);
XSSFWorkbook workBook = new XSSFWorkbook(salesFileName);
XSSFSheet sheet = workBook.getSheetAt(0);
totalRows = sheet.getPhysicalNumberOfRows();
System.out.println("total no of rows >>>>"+totalRows);

} catch (IOException e) {
e.printStackTrace();
}

Also In Reading and Writing section

It is using WorkBookFactory...

Workbook wb = WorkbookFactory.create(inp);

If we are talikng about poi then why my IDE is asking to import .jxl (API for Workbook)
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 39548
    
  27
The code looks odd:
inputStream = new FileInputStream(destDir)

Is "destDir" a directory or a file? If the former, then this doesn't do anything useful. But since you're not using the "inputStream" field later it probably doesn't matter.


Also In Reading and Writing section It is using WorkBookFactory...

Workbook wb = WorkbookFactory.create(inp);

Since the POI user guide recommends to use this for reading a file, that's where I would start.

If we are talikng about poi then why my IDE is asking to import .jxl (API for Workbook)

I don't understand this. Where is a message appearing about ".jxl"? How is that related to the code posted above?
Anshul Agrawal
Greenhorn

Joined: Jul 15, 2008
Posts: 19
I got it sir.
Thank you very much.

I applied



try {
System.out.println("destDir==> "+destDir);
XSSFWorkbook workBook = new XSSFWorkbook(destDir);
XSSFSheet sheet = workBook.getSheetAt(0);
totalRows = sheet.getPhysicalNumberOfRows();
System.out.println("total no of rows >>>>"+totalRows);

} catch (IOException e) {
e.printStackTrace();
}




Just got the rows will now be applying to fetch the cell data.


vamshi kiran
Greenhorn

Joined: Dec 08, 2009
Posts: 2
hi Anshul Agrawal,

i also had the same problem of "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)
".

i will try the sample you wrote in this forum

try {
System.out.println("destDir==> "+destDir);
XSSFWorkbook workBook = new XSSFWorkbook(destDir);
XSSFSheet sheet = workBook.getSheetAt(0);
totalRows = sheet.getPhysicalNumberOfRows();
System.out.println("total no of rows >>>>"+totalRows);

} catch (IOException e) {
e.printStackTrace();
}

but i would like to know the jar file to be imported. if you have can you share it.

Thanks in Advance,
Vamshi
Anshul Agrawal
Greenhorn

Joined: Jul 15, 2008
Posts: 19
Thanks Vamshi
Really nice of you..
ya it worked for me with XSSF.

Regards
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 39548
    
  27
vamshi kiran wrote:but i would like to know the jar file to be imported.

The file is called poi-3.5.jar, and it's available from the POI site.
Anshul Agrawal
Greenhorn

Joined: Jul 15, 2008
Posts: 19
ohh.. i misunderstood...
sorry vamshi...and thanks Ulf Dittmer


Regards
vamshi kiran
Greenhorn

Joined: Dec 08, 2009
Posts: 2
ok i shall download the file and try it...

Thanks
Vamshi
Murty Medisetti
Greenhorn

Joined: Jan 23, 2010
Posts: 22
I want read a particular row and cell data. Is there any way to read particular position data?
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18129
    
    8

Yes, there is.

I'm sure I remember using that in some code I wrote last year. Could you not find any suitable methods in the API documentation for XSSFSheet? I don't have it available right now, so it should be faster for you to have a look than for me to search out the docs.
Murty Medisetti
Greenhorn

Joined: Jan 23, 2010
Posts: 22
I didn't find any method to get data directly of Row,column position. I am iterating through the row by using Iterator then iterating through the cell to get particular position data.

Iterator rows = sheet.rowIterator();
while (rows.hasNext())
{
XSSFRow row = ((XSSFRow) rows.next());
if(row.getRowNum() >= 10){
Iterator cells = row.cellIterator();
while (cells.hasNext())
{
cell = (Cell) cells.next();
if(cell.getColumnIndex() == 10){
//My logic here.
}
}
}
Murty Medisetti
Greenhorn

Joined: Jan 23, 2010
Posts: 22
Finally i found the solution to read cell directly.

workbook = new XSSFWorkbook(inputStream);
XSSFSheet sheet = workbook.getSheetAt(0);
Iterator rows = sheet.rowIterator();
XSSFRow row = sheet.getRow(12); //13th row
row.getCell(56) //57th cell
Leena Chopra
Greenhorn

Joined: Jan 18, 2011
Posts: 1
String currencyVal = row.getCell(56).getStringCellValue(); // gets cel's data
teja chandra
Greenhorn

Joined: Sep 13, 2011
Posts: 5
Hi, Please help me out with code for reading an excel file of xlsx format (excel2007) using XSSFWorkBook. its urgent

Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 39548
    
  27
No doubt it's urgent for you, but that doesn't make it urgent for us. This topic as well as the POI web site have examples of how to read XLSX spreadsheets; what specifically are you looking for that isn't covered in either place?
teja chandra
Greenhorn

Joined: Sep 13, 2011
Posts: 5
Hi Ulf Dittmer,

Pleasefind the below code and i amgetting the exception

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.*;
import java.util.*;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


InputStream formFileInputStream = formFileObj.getInputStream();
log.debug("in line 107");
Workbook wb = WorkbookFactory.create(formFileInputStream);
log.debug("in line 109");
Sheet sheet = wb.getSheetAt(0);
Iterator rowIterator = sheet.rowIterator();


when i am trying to upload a 2007 file, i am getting the exception below.

Error Message: java.lang.reflect.InvocationTargetException
null
javax/xml/stream/XMLStreamException
java.lang.NoClassDefFoundError: javax/xml/stream/XMLStreamException


i will import the jar. but the problem is related to reading the stream. can you help me out

Teja
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 39548
    
  27
If you aren't using at least Java 6, then you need to find a JAXP version that includes the javax.xml.stream package and use that instead of the JAXP version built into your JRE.
teja chandra
Greenhorn

Joined: Sep 13, 2011
Posts: 5
Hi Friends,

please help me out in loading the 2007 excel using ss.usermodel or XSSFWorkbook. I am getting the error below.


Error Message: 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)
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)


Is there any sample code of the correct implementation.


Teja
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 39548
    
  27
Is this question about the exact same code as your previous question? That would mean you got previous problem resolved? If it's about different code, then post the relevant extract from the code you're asking about.

It sounds as if you are using HSSF classes to work with an XLSX document, which won't work.
teja chandra
Greenhorn

Joined: Sep 13, 2011
Posts: 5
Hi Ulf Dittmer,

yes, the same code. can you let me know the root cause and the code snippet i need to add. also if there is some sample code, please send me the same.
please send me the code for uploading an excel 2007 using ss.usermodel or xssfworkbook.
thanks,
Teja





Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 39548
    
  27
Ulf Dittmer wrote:It sounds as if you are using HSSF classes to work with an XLSX document, which won't work.

You will need to investigate why that is.
teja chandra
Greenhorn

Joined: Sep 13, 2011
Posts: 5
I pasted the code also right. Am i using HSSF? and i put dicussion in the forums after all analysis my friend. its easy to advise.

Can anyone help me out.

Teja
Prateek Kumar Singh
Greenhorn

Joined: Jan 21, 2011
Posts: 29

Hi Ulf Dittmer,

i find this exception ...

file.org.apache.poi.openxml4j.exceptions.InvalidFormatException: Package should contain a content type part [M1.13]
org.apache.poi.POIXMLException: org.apache.poi.openxml4j.exceptions.InvalidFormatException: Package should contain a content type part [M1.13]
at org.apache.poi.util.PackageHelper.open(PackageHelper.java:55)

in my code...

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

how can i resolve it ?
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 39548
    
  27
Patel Sweta,
Your post was moved to a new topic.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to read .xlsx sheet from Client Side using poi 3.5
 
Similar Threads
How to read .xlsx file format in java 1.4 version
Parsing Excel-2007-Format (xlsx)
How to read xlsx file from local java file
How to read excel2007 file or both .xls and .xlsx files using apache poi
How to read xlsx file from local java file