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

[Anshul Agrawal]

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

[Ulf Dittmer]

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]

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]

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]

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]

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]

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]

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]

Thanks Vamshi
Really nice of you..
ya it worked for me with XSSF.

Regards

[Ulf Dittmer]

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]

ohh.. i misunderstood...
sorry vamshi...and thanks Ulf Dittmer


Regards

[vamshi kiran]

ok i shall download the file and try it...

Thanks
Vamshi

[Murty Medisetti]

I want read a particular row and cell data. Is there any way to read particular position data?

[Paul Clapham]

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]

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]

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]

String currencyVal = row.getCell(56).getStringCellValue(); // gets cel's data

[teja chandra]

Hi, Please help me out with code for reading an excel file of xlsx format (excel2007) using XSSFWorkBook. its urgent

[Ulf Dittmer]

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]

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]

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]

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]

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]

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]

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]

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]

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]

Patel Sweta,
Your post was moved to a new topic.