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

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

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).

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 .

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.

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)

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?

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.

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

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

Regards

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.

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

Regards

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

Thanks
Vamshi

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

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.

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.
}
}
}

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

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

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

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?

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

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.

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

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.

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 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.

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

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 ?

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