• 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

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

 
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
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 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).
 
Anshul Agrawal
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 19
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
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 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
Posts: 19
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.


 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 19
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Vamshi
Really nice of you..
ya it worked for me with XSSF.

Regards
 
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

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
Posts: 19
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
ohh.. i misunderstood...
sorry vamshi...and thanks Ulf Dittmer


Regards
 
vamshi kiran
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
ok i shall download the file and try it...

Thanks
Vamshi
 
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I want read a particular row and cell data. Is there any way to read particular position data?
 
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 24
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 24
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
String currencyVal = row.getCell(56).getStringCellValue(); // gets cel's data
 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi, Please help me out with code for reading an excel file of xlsx format (excel2007) using XSSFWorkBook. its urgent

 
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
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
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
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 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
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
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
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
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Patel Sweta,
Your post was moved to a new topic.
 
Can you hear that? That's my theme music. I don't know where it comes from. Check under this tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic