Roberto
I have found a possible workaround for you but it is a little long-winded
but it does depend upon your having OpenOffice installed on your PC.
Firstly, rename the file from dmgg5o5e.xls to dmgg5o5e.xml. Next open it
using OpenOffice Calc which contains filters that are able to render lots of
different formats correctly. That should result in you looking at a
spreadsheet containing your data.
Now, you have to re-save the file in the correct format. Again, OpenOffice
uses filters to transcode files and all you need to do is select the Save As
option from the File menu, type in a name for the file and ensure that you
select the 'Microsoft Excel 97/2000/XP (.xls)' option from the 'Save as
type' dropdown list. Click on the Save button and then you can close
OpenOffice down again.
Now, all you need to do is specify the name of the file you have just saved
in the code I sent you yesterday, to compile and then run it. I have just
done that and got the following output;
init:
deps-jar:
compile:
run:
You got an HSSFWorkbook
Processing row number: 0
Cell 0, 0 contains the following text: ID
Cell 1, 0 contains the following text: Counter
Cell 2, 0 contains the following text: SendTime
Cell 3, 0 contains the following text: Sender
Cell 4, 0 contains the following text: Message
Cell 5, 0 contains the following text: AnswerSmsCode
Cell 6, 0 contains the following text: Correct
Processing row number: 1
Cell 0, 1 contains the following number: 140.0
Cell 1, 1 contains the following number: 1.0
Cell 2, 1 contains the following number: 0.8543402777777779
Cell 3, 1 contains the following text: 393355851409
Cell 4, 1 contains the following text: A
Cell 5, 1 contains the following text: A
Cell 6, 1 contains the following text: True
Processing row number: 2
Cell 0, 2 contains the following number: 141.0
Cell 1, 2 contains the following number: 2.0
Cell 2, 2 contains the following number: 0.854363425925926
Cell 3, 2 contains the following text: 393386119625
Cell 4, 2 contains the following text: b
Cell 6, 2 contains the following text: False
Processing row number: 3
Cell 0, 3 contains the following number: 142.0
Cell 1, 3 contains the following number: 3.0
Cell 2, 3 contains the following number: 0.8543518518518519
Cell 3, 3 contains the following text: 393355851409
Cell 4, 3 contains the following text: a
Cell 5, 3 contains the following text: A
Cell 6, 3 contains the following text: True
Processing row number: 4
Cell 0, 4 contains the following number: 143.0
Cell 1, 4 contains the following number: 4.0
Cell 2, 4 contains the following number: 0.3157523148148148
Cell 3, 4 contains the following text: 3386119625
Cell 4, 4 contains the following text: A roberto
Cell 5, 4 contains the following text: A
Cell 6, 4 contains the following text: True
BUILD SUCCESSFUL (total time: 1 second)
This listing is different as I have added to the original code, modifying
the final while() loop;
while(cellIterator.hasNext()) {
cell = cellIterator.next();
switch(cell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
System.out.println("Cell " +
cell.getCellNum() +
", " +
row.getRowNum() +
" is blank.");
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.println("Cell " +
cell.getCellNum() +
", " +
row.getRowNum() +
" contains the boolean value: " +
cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
System.out.println("Cell " +
cell.getCellNum() +
", " +
row.getRowNum() +
" contains the following error code:
" +
cell.getErrorCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
System.out.println("Cell " +
cell.getCellNum() +
", " +
row.getRowNum() +
" contains the following formula: "
+
cell.getCellFormula());
break;
case Cell.CELL_TYPE_NUMERIC:
System.out.println("Cell " +
cell.getCellNum() +
", " +
row.getRowNum() +
" contains the following number: " +
cell.getNumericCellValue() );
break;
case Cell.CELL_TYPE_STRING:
System.out.println("Cell " +
cell.getCellNum() +
", " +
row.getRowNum() +
" contains the following text: " +
cell.getRichStringCellValue().getString() );
break;
}
}
OpenOffice does expose an API that you can access from Java code. It is
called UNO (Universal Network Objects) and would allow you to directly work
upon almost any format of Excel files - though I am not too sure about it's
support for OpenXML yet and remain to be convinced it can fully process
complex files. However, it is a complex technique to use and the
documentation is not very easy to understand as the interface is based upon
the concept of services and service providers.
If you do not have access to Openoffice, then I would look at using parsers
such as Xerces and SAX to write your own java code to parse this file.
Support for the SAX parser is even included as a part of the core Java API.
Roberto Santini wrote:
>
> Thanks for all, but when i 'm running, I have an exception:
> ----
> Caught an: java.lang.IllegalArgumentException
> Message: Your InputStream was neither an OLE2 stream, nor an
> OOXML stream
> Stacktrace follows.........
> ------
> Can you help me? :D
> ----- Segue Messaggio Originale -----
> Da : MSB <[email protected]>
> A : [email protected]
> oggetto : Re: Error with a XLS file
> Data : Fri, 29 May 2009 05:39:08 -0700 (PDT)
>
>> 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]
>>
>
> -------------------------------------------------------------------
> 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-tp23757478p23791733.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]
