Thanks for the file Roberto but I managed to download a few examples form
Microsoft and have used one of those to test this bit of code. It will work
equally well with both binary and OpenXML Excel files and has been compiled
and tested against Version 3.5 beta 1, though I am confident it should work
equally well using any later version of the API.
The first thing to do is to add the folowing archives into your classpath;
poi-3.5-beta1-20080718.jar
poi-ooxml-3.5-beta1-20080718.jar
openxml4j-bin-alpha-080407.jar
xmlbeans-2.3.0.jar
log4j-1.2.13.jar
dom4j-1.6.1.jar
ooxml-schemas.jar
Do not be too concerned with ensuring that the filenames match exactly, just
ensure that archives with very similar names are placed onto your classpath.
Now the code which is very similar to the example I posted earlier with the
exception that I am coding against the interfaces in the ss.usermodel
package now;
import java.io.File;
import java.io.FileInputStream;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;
public class Main {
/**
* @param args the command line arguments
*/
public static void main(String[] args) {
File inputFile = null;
FileInputStream fileIStream = null;
Workbook workbook = null;
Sheet sheet = null;
Row row = null;
Cell cell = null;
Iterator<Row> rowIterator = null;
Iterator<Cell> cellIterator = null;
int numSheets = 0;
try {
inputFile = new File("C:/temp/sample.xlsx");
fileIStream = new FileInputStream(inputFile);
// Use the WorkbookFactory create() factory method to get
// either an HSSFWorkbook or XSSFWorkbook depending on
// the file type wrapped in the FileInputStream
workbook = WorkbookFactory.create(fileIStream);
// Lets see what we got, an XSSF or HSSFWorkbook
if(workbook instanceof
org.apache.poi.hssf.usermodel.HSSFWorkbook) {
System.out.println("You got an HSSFWorkbook");
}
else {
System.out.println("You got an XSSFWorkbook");
}
// Get the number of sheets
numSheets = workbook.getNumberOfSheets();
// Iterate through each sheet
for(int i = 0; i < numSheets; i++) {
// Get the sheet
sheet = workbook.getSheetAt(i);
// Get an iterator to work through the rows on the sheet
rowIterator = sheet.iterator();
// Iterate through the rows
while(rowIterator.hasNext()) {
row = rowIterator.next();
System.out.println("Processing row number: " +
row.getRowNum());
// Get an iterator to work through the cells on the row
cellIterator = row.iterator();
// Iterate through the cells
while(cellIterator.hasNext()) {
cell = cellIterator.next();
// Just print out the num,ber of the cell
// and the String representatin of the instance.
System.out.println("Cell: " +
cell.getCellNum() +
" contains: " +
cell.toString());
}
}
}
}
catch(Exception ex) {
System.out.println("Caught an: " + ex.getClass().getName());
System.out.println("Message: " + ex.getMessage());
System.out.println("Stacktrace follows.........");
ex.printStackTrace(System.out);
}
finally {
if(fileIStream != null) {
try {
fileIStream.close();
}
catch(Exception ex) {
// I G N O R E //
}
}
}
}
}
and this is an example of running it against that sample file I got my hands
on;
init:
deps-jar:
compile:
run:
log4j:WARN No appenders could be found for logger (org.openxml4j.opc).
log4j:WARN Please initialize the log4j system properly.
You got an XSSFWorkbook
Processing row number: 0
Cell: 0 contains: [0,0] 0
Cell: 1 contains: [0,1] 111
Processing row number: 1
Cell: 0 contains: [1,0] 1
Cell: 1 contains: [1,1] 222
Processing row number: 2
Cell: 0 contains: [2,0] 2
Cell: 1 contains: [2,1] 333
Processing row number: 3
Cell: 0 contains: [3,0] 3
Cell: 1 contains: [3,1] 444
Processing row number: 4
Cell: 0 contains: [4,0] 4
Cell: 1 contains: [4,1] 555
Processing row number: 5
Cell: 0 contains: [5,0] 5
Cell: 1 contains: [5,1] 666
Processing row number: 6
Cell: 0 contains: [6,0] 6
Cell: 1 contains: [6,1] 777
Processing row number: 7
Cell: 0 contains: [7,0] 7
Cell: 1 contains: [7,1] 888
Processing row number: 8
Cell: 0 contains: [8,0] 8
Cell: 1 contains: [8,1] 999
Processing row number: 9
Cell: 0 contains: [9,0] 9
Cell: 1 contains: [9,1] 4995
BUILD SUCCESSFUL (total time: 18 seconds)
Roberto Santini wrote:
>
> THANKS! I attach you an OpenXML file format.
>
> Bye
> Roberto
> ----- Segue Messaggio Originale -----
> Da : MSB <[email protected]>
> A : [email protected]
> oggetto : Re: Error with a XLS file
> Data : Fri, 29 May 2009 04:33:44 -0700 (PDT)
>
>> Sorry about that, can you let me know what messages you
>> are receiving at compile/run time please.
>>
>> The real problem I face in helping you out is that I do
>> not have access to many - indeed to any whilst I am at
>> work - OpenXML files. So, I will try to put together some
>> code that should work for both HSSF and XSSF (using the
>> ss.usermodel interfaces). I can only really test it with a
>> binary file and will have to ask you to test it against an
>> OpenXML file yourself.
>>
>> Will try to get something to you in an hour or so - work
>> allowing!!
>>
>>
>> Roberto Santini wrote:
>> >
>> > The code tha you post me yesterday doesn't works...
>> > there is some errors with the dependencies...
>> > ----- Segue Messaggio Originale -----
>> > Da : MSB <[email protected]>
>> > A : [email protected]
>> > oggetto : Re: Error with a XLS file
>> > Data : Thu, 28 May 2009 09:57:40 -0700 (PDT)
>> >
>> >> Well that very much depends upon EXACTLY what you want
>> to >> do. The simplest way to read a file is to do
>> something >> like the following;
>> >>
>> >> import org.apache.poi.xssf.usermodel.*;
>> >> import java.util.Iterator;
>> >>
>> >> Iterator<org.apache.poi.ss.usermodel.Row> rowIterator =
>> >> null; Iterator<org.apache.poi.ss.usermodel.Cell>
>> >> cellIterator = null; //
>> >> // Enter the name and location of your file into the
>> >> // quotes below.
>> >> //
>> >> XSSFWorkbook workbook = new XSSFWorkbook("");
>> >> XSSFSheet sheet = null;
>> >> XSSFRow row = null;
>> >> XSSFCell cell = null;
>> >> int numSheets = workbook.getNumberOfSheets();
>> >> // For loop to iterate over the sheets in the workbook
>> >> for(int i = 0; i < numSheets; i++) {
>> >> sheet = workbook.getSheetAt(i);
>> >> rowIterator = sheet.iterator();
>> >> // While loop to iterate over the rows on the
>> sheet >> while(rowIterator.hasNext()) {
>> >> row = rowIterator.next();
>> >> cellIterator = row.iterator();
>> >> // While loop to iterate over the cells in
>> the >> row.
>> >> while(cellIterator.hasNext()) {
>> >> cell = cellIterator.next();
>> >>
>> >> // And then it all depends what you want
>> to >> do with the cell.
>> >> // This will show the contents of the
>> cell >> as a String
>> >> System.out.println(cell.getStringValue())
>> ; >>
>> >> }
>> >> }
>> >> }
>> >>
>> >> Try this code, it will print out the contents of each
>> cell >> as a String. I do not use the OpenXML version of
>> Excel so >> cannot test the code, but I am fairly
>> confident it works. >> Am leaving for home now so will not
>> be logging on again >> this evening in all likelihood,
>> good luck and I hope this >> helps.
>> >>
>> >>
>> >> Roberto Santini wrote:
>> >> >
>> >> > Thanks for your answer.
>> >> > The code the i found on the link, is to write a file,
>> >> > and I need to read a file.
>> >> > So, can you post me an example?? I have very few time
>> to >> > finish the application (1, 2 hours) and I haven't
>> time >> > to read all the docs... :D
>> >> >
>> >> > Thanks a lot
>> >> > Roberto
>> >> > ----- Segue Messaggio Originale -----
>> >> > Da : MSB <[email protected]>
>> >> > A : [email protected]
>> >> > oggetto : Re: Error with a XLS file
>> >> > Data : Thu, 28 May 2009 07:49:44 -0700 (PDT)
>> >> >
>> >> >> Hello Roberto,
>> >> >>
>> >> >> I have been following your conversation with Fillipo
>> >> and >> would like to ask a couple of questions please.
>> >> >>
>> >> >> In one of your replies, you said;
>> >> >>
>> >> >> Hi, I don't know how the file is saved (is
>> >> automatically >> generated by an ASPX page, with excel
>> xml >> format) because >> i download it from the web....
>> >> >>
>> >> >> If the file is in the xml format then you do not
>> want >> to >> be using HSSF to process it but XSSF. HSSF
>> is >> designed to >> work with the older binary file
>> format not >> the new OpenXML >> based file format. You
>> need to change >> the code that you >> have written to use
>> XSSFWorkbook, >> XSSFSheet, XSSFRow, >> XSSFCell, etc. I
>> do not think that >> the file's extension >> should be a
>> problem but you could >> easily change it from >> .xls to
>> xlsx. >> >>
>> >> >> If you think that you may need to process both BIFF8
>> >> and >> OpenXML (binary and xml) files then you can use
>> the >> >> techniques described here;
>> >> >>
>> >> >> http://poi.apache.org/spreadsheet/converting.html
>> >> >>
>> >> >> under the heading; 'New, generic SS Usermodel Code'
>> >> >>
>> >> >> Hopefully, that should solve the problem though you
>> do >> >> need to be aware that Microsoft made an earlier
>> attempt >> >> with an xml based file format - in Office
>> 2003. If the >> web >> site has created a file using this
>> format then POI >> cannot >> open it - at least not as far
>> as I am aware. >> >>
>> >> >>
>> >> >> Roberto Santini wrote:
>> >> >> >
>> >> >> > Hi to all, I0m a new ITALIAN member.
>> >> >> > I've a problem reading an XLS file; this file is
>> >> >> > automatically generated by an ASPX page.
>> >> >> > The error I'm getting is:
>> >> >> >
>> >> >> > java.io.IOException: Invalid header signature;
>> read >> >> > 7813033141555149807, expected
>> -2226271756974174256 >> >> >
>> >> >> > There is a way to solve or the file is generated
>> with >> an >> > error so I can't read it?
>> >> >> > If i try to open the same file with Office, it
>> run! >> >> >
>> >> >> > Bye
>> >> >> > Roberto
>> >> >> >
>> >> >> >
>> >> >>
>> >>
>> ----------------------------------------------------------
>> >> >> > --------- Roberto Santini >> >
>> www.lasfidacontinua.it >> >> >
>> >> >> > MSN: [email protected]
>> >> >> > Skype: cent89
>> >> >> >
>> >> >> >
>> >> >>
>> >>
>> ----------------------------------------------------------
>> >> >> > ----------- To unsubscribe, e-mail: >> > >>
>> [email protected] For additional commands,
>> >> >> > e-mail: [email protected] >> > >> >> >
>> >> >>
>> >> >> --
>> >> >> View this message in context:
>> >> >>
>> >> >
>> >>
>> >
>>
> http://www.nabble.com/Error-with-a-XLS-file-tp23757478p23763310.html
>> >> >> Sent from the POI - User mailing list archive at
>> >> >> Nabble.com.
>> >> >>
>> >> >>
>> >> >>
>> >>
>> ----------------------------------------------------------
>> >> >> ----------- To unsubscribe, e-mail: >> >>
>> [email protected] For additional commands,
>> >> >> e-mail: [email protected] >> >> >
>> >> >
>> >>
>> ----------------------------------------------------------
>> >> > --------- Roberto Santini >> > www.lasfidacontinua.it
>> >> >
>> >> > MSN: [email protected]
>> >> > Skype: cent89
>> >> >
>> >> >
>> >>
>> ----------------------------------------------------------
>> >> > ----------- To unsubscribe, e-mail: >> >
>> [email protected] For additional commands,
>> >> > e-mail: [email protected] >> >
>> >> >
>> >>
>> >> --
>> >> View this message in context:
>> >>
>> >
>>
> http://www.nabble.com/Error-with-a-XLS-file-tp23757478p23765811.html
>> >> Sent from the POI - User mailing list archive at
>> >> Nabble.com.
>> >>
>> >>
>> >>
>> ----------------------------------------------------------
>> >> ----------- To unsubscribe, e-mail: >>
>> [email protected] For additional commands,
>> >> e-mail: [email protected] >>
>> >
>> >
>> ----------------------------------------------------------
>> > --------- Roberto Santini
>> > www.lasfidacontinua.it
>> >
>> > MSN: [email protected]
>> > Skype: cent89
>> >
>> >
>> ----------------------------------------------------------
>> > ----------- To unsubscribe, e-mail:
>> > [email protected] For additional commands,
>> > e-mail: [email protected]
>> >
>> >
>>
>> --
>> View this message in context:
>>
> http://www.nabble.com/Error-with-a-XLS-file-tp23757478p23778360.html
>> Sent from the POI - User mailing list archive at
>> Nabble.com.
>>
>>
>> ----------------------------------------------------------
>> ----------- To unsubscribe, e-mail:
>> [email protected] For additional commands,
>> e-mail: [email protected]
>>
>
> -------------------------------------------------------------------
> Roberto Santini
> www.lasfidacontinua.it
>
> MSN: [email protected]
> Skype: cent89
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
>
--
View this message in context:
http://www.nabble.com/Error-with-a-XLS-file-tp23757478p23779225.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
