Thanksfor your interestig! It's a possible solution, but i need to open manually the file and re-save twice.
So, it's much faster open (even manually) the file with MS Office and re-save it once... ----- Segue Messaggio Originale ----- Da : MSB <[email protected]> A : [email protected] oggetto : Re: Error with a XLS file Data : Sat, 30 May 2009 02:25:33 -0700 (PDT) > 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] > ------------------------------------------------------------------- Roberto Santini www.lasfidacontinua.it MSN: [email protected] Skype: cent89 --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
