You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Henrik <re...@henkelarsson.se> on 2009/10/05 00:57:05 UTC
Basic question for reading xls file
I have been trying to make a very simpe Java program, the only advanced
part is the part using HSSF to read an xls file and get all the data.
What i want to do i ittretare thru ever colum on every row and just
print that data out in the comand prompt. I have found lots of example
of this on the homepage but no whole code that realy works. They all
seems to need something more to get them to run. It would be very much
appriciated if someone just could point me in the right direction to get
this working.
Best regards Henrik
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Basic question for reading xls file
Posted by Chris Lott <ma...@invest-faq.com>.
This example program works pretty well for me to extract all the data in
an XLS file:
http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java
I suggest you may wish to be more specific about the problems you
encountered. Does the program compile? Does it yield the first row and
then quit? Or ??
chris...
Henrik wrote:
> I have been trying to make a very simpe Java program, the only advanced
> part is the part using HSSF to read an xls file and get all the data.
> What i want to do i ittretare thru ever colum on every row and just
> print that data out in the comand prompt. I have found lots of example
> of this on the homepage but no whole code that realy works. They all
> seems to need something more to get them to run. It would be very much
> appriciated if someone just could point me in the right direction to get
> this working.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Basic question for reading xls file
Posted by MSB <ma...@tiscali.co.uk>.
You do not say which file format you are targetting - .xls or .xlsx - so I
have cut out some code that should work equally well with either type of
file. To compile and run the code, you will need to download all of the
dependencies - those jars that support the xml based file format - as well
as POI itslef; they are all avaliable from the build area -
http://encore.torchbox.com/poi-cvs-build/ - if you do not already have them;
just make sure that the versions of the jar archives you download are
similar with respect to date.
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Iterator;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
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;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
/**
* Demonstrates how to iterate through the cells on a worksheet and
* display the formatted value of each cell.
*
* Note that the main purpose of this method is to demonstrate how the
* org.apache.poi.ss.usermodel.DataFormatter class methods can be used
* to display the cells contents much as they would be if the sheet were
* viewed using Excel.
*
* @param filename An instance of the String class that encapsulates
* the name of and path to an Excel workbook. Note that
* as the ss model classes are used this can be either a
* binary (.xls) or an OpenXML (.xlsx) file.
*/
public static void displayCellContents(String filename) {
File file = null;
FileInputStream fis = null;
Workbook workbook = null;
Sheet sheet = null;
Row row = null;
Cell cell = null;
int numSheets = 0;
Iterator<Row> rowIter = null;
Iterator<Cell> cellIter = null;
try {
// Open the workbook.
file = new File(filename);
fis = new FileInputStream(file);
workbook = WorkbookFactory.create(fis);
// Get the number of sheets in the workbook and enter a for loop
// to iterate through them one at a time.
numSheets = workbook.getNumberOfSheets();
for(int i = 0; i < numSheets; i++) {
// Get the sheet and recover from that an iterator that
// allows us to step through all of the rows the sheet
contains.
sheet = workbook.getSheetAt(i);
rowIter = sheet.rowIterator();
while(rowIter.hasNext()) {
// Get a row and recover from it an Iterator that allows
// us to access each of the cells on the row.
row = rowIter.next();
cellIter = row.cellIterator();
while(cellIter.hasNext()) {
// Get a cell
cell = cellIter.next();
switch(cell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
System.out.println("is blank.");
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.println("contains a boolean value
of: " +
cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
System.out.println("contains an error code
of: " +
cell.getErrorCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
System.out.println("contains a formula: " +
cell.getCellFormula());
break;
case Cell.CELL_TYPE_NUMERIC:
if(DateUtil.isCellDateFormatted(cell)) {
System.out.println("contains a date: +
cell.getDateCellValue()");
}
else {
System.out.println("contains a number: "
+
cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_STRING:
System.out.println("contains a String: " +
cell.getStringCellValue());
break;
}
}
}
}
}
catch(FileNotFoundException fnfEx) {
System.out.println("Caught: " + fnfEx.getClass().getName());
System.out.println("Message: " + fnfEx.getMessage());
System.out.println("Stacktrace follows..............");
fnfEx.printStackTrace(System.out);
}
catch(IOException ioEx) {
System.out.println("Caught: " + ioEx.getClass().getName());
System.out.println("Message: " + ioEx.getMessage());
System.out.println("Stacktrace follows..............");
ioEx.printStackTrace(System.out);
}
catch(InvalidFormatException invFEx) {
System.out.println("Caught: " + invFEx.getClass().getName());
System.out.println("Message: " + invFEx.getMessage());
System.out.println("Stacktrace follows..............");
invFEx.printStackTrace(System.out);
}
finally {
if(fis != null) {
try {
fis.close();
fis = null;
}
catch(IOException ioEx) {
// I G N O R E
}
}
}
To use this code, simply insert the static method into a class and then call
it from the classes main() method like this;
Classname.displayCellContents("C:/temp/myfile.xlsx");
Of course, you will need to change this - "C:/temp/myfile.xlsx" - to point
to a file on your machine/server.
You will not need many of the inport statements but they were there in the
example I cut the code from and I just left them as they can do no harm. If
you have any problems at all, just post to the list and I will pick up the
message when I return from work this evening; I cannot deal with any
messages during the day as I do not work at a computer so, if I seem slow in
responding, please be assured I am not ignoring you.
Yours
Mark B
PS Have just reviewed the code and spotted that one aspect is missing; it
used to output the cells address followed by it's contents but does not do
so any longer. When I have the time, I will add the couple of lines needed
to output the address and post the change here.
Henrik-23 wrote:
>
> I have been trying to make a very simpe Java program, the only advanced
> part is the part using HSSF to read an xls file and get all the data.
> What i want to do i ittretare thru ever colum on every row and just
> print that data out in the comand prompt. I have found lots of example
> of this on the homepage but no whole code that realy works. They all
> seems to need something more to get them to run. It would be very much
> appriciated if someone just could point me in the right direction to get
> this working.
>
> Best regards Henrik
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
--
View this message in context: http://www.nabble.com/Basic-question-for-reading-xls-file-tp25743149p25745930.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Basic question for reading xls file
Posted by Henrik <re...@henkelarsson.se>.
Dam, ok thanks anyways for all the help.
MSB skrev:
> Hello Henrik
>
> I downloaded the file and had a look, it almost made me spill my coffee. It
> is html markup in a file with a .xls extension. Excel is quite happy to
> parse this - even though it complains about the file extension - but you can
> open the file using a simple text editor and take a look for yourself, I
> just used Wordpad.
>
> If you want to parse this then POI will not be of any use to you, sorry.
> There are lots of tools that would allow you to parse html, the Java SDK
> even has in-built support in the form of the HTMLEditorKit class.
>
> Yours
>
> Mark B
>
>
> Henrik-23 wrote:
>
>> Thanks again for an extremely good answere. This time however i got a
>> devestating error :)
>>
>> Exception in thread "main" java.lang.IllegalArgumentException: Your
>> InputStream was neither an OLE2 stream, nor an OOXML stream
>> at
>> org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:65)
>> at lasxls.dataExtract.displayCellContents(dataExtract.java:56)
>> at lasxls.Main.main(Main.java:20)
>>
>> This is strange, the datasource im trying to use is from this link
>> http://www.nasdaqomxnordic.com/aktier/Historiska_kurser/?languageId=1&Instrument=SSE366
>> It is the Excel icon next to the search botton to download the
>> hirstorical data. Is this not XLS???
>>
>> MSB skrev:
>>
>>> You need to add additional archives - .jar files - to your classpath.
>>> This is
>>> old code that I have not refactored yet so the list of archives below
>>> relates to an earlier version of POI but you can use it as a guide for
>>> the
>>> similar components from the latest version;
>>>
>>> poi-3.5-beta7-20090612.jar
>>> poi-ooxml-3.5-beta7-20090613.jar
>>> openxml4j-1.0-beta.jar
>>> xmlbeans-2.3.0.jar
>>> ooxml-schemas-1.0.jar
>>> dom4j-1.6.1.jar
>>>
>>> The exact names of the files will have changed now that the developers
>>> are
>>> working on 3.6, but they will still be very similar and if you do not
>>> already have these archives, you will need to visit the torchbox address
>>> I
>>> posted in an earlier response, search thruogh the list of archives there
>>> and
>>> download those whose names are similar to the above. I have just had a
>>> quick
>>> check and it seems these are the current versions;
>>>
>>> poi-3.6-beta1-20091006.jar
>>> poi-ooxml-3.6-beta1-20091006.jar
>>> and the current versions of the other files listed should be in this zip
>>> file
>>> poi-dependencies-3.6-beta1-20091006.zip
>>> that you can download and then unzip into a directory.
>>>
>>> Once you have the archives, add them to your classpath and you should be
>>> able to both compile and run the code.
>>>
>>> Hope that helps. If you have any other problems, just post to the list.
>>> Just
>>> as an aside, do not worry about the beta designation for the archives.
>>> Yegor
>>> and the other developers ensure that the product is stable and 3.6 beta
>>> will
>>> be 3.5 final with the latest fixes and patches added; do not worry that
>>> you
>>> will be using some buggy patched nightmare because this would simply not
>>> be
>>> allowed to happen.
>>>
>>> Yours
>>>
>>> Mark B
>>>
>>>
>>> Henrik-23 wrote:
>>>
>>>
>>>> I get this error:
>>>> Exception in thread "main" java.lang.NoClassDefFoundError:
>>>> org/apache/xmlbeans/XmlOptions
>>>> at
>>>> org.apache.poi.POIXMLDocumentPart.<clinit>(POIXMLDocumentPart.java:43)
>>>> at
>>>> org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:62)
>>>> at lasxls.dataExtract.displayCellContents(dataExtract.java:56)
>>>> at lasxls.Main.main(Main.java:20)
>>>> Caused by: java.lang.ClassNotFoundException:
>>>> org.apache.xmlbeans.XmlOptions
>>>> at java.net.URLClassLoader$1.run(URLClassLoader.java:200)
>>>> at java.security.AccessController.doPrivileged(Native Method)
>>>> at java.net.URLClassLoader.findClass(URLClassLoader.java:188)
>>>> at java.lang.ClassLoader.loadClass(ClassLoader.java:307)
>>>> at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
>>>> at java.lang.ClassLoader.loadClass(ClassLoader.java:252)
>>>> at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:320)
>>>> ... 4 more
>>>> Java Result: 1
>>>> BUILD SUCCESSFUL (total time: 1 second)
>>>>
>>>>
>>>> This is my main class.
>>>> public class Main {
>>>>
>>>> /**
>>>> * @param args the command line arguments
>>>> */
>>>> public static void main(String[] args) {
>>>> // TODO code application logic here
>>>> dataExtract ins = new dataExtract();
>>>> ins.displayCellContents("E:/1_Trading/Java/LasXLS/Volvo.xls");
>>>> }
>>>>
>>>> }
>>>>
>>>> MSB skrev:
>>>>
>>>>
>>>>> Had the chance to play with some code this evening and I have imporved
>>>>> the
>>>>> snippet I posted earlier so that it now outputs the cells address in
>>>>> familar
>>>>> Excel notation - eg A1. Alsom I noticed when I ran it against a
>>>>> problematic
>>>>> test file that an exception could be thrown when the test was made to
>>>>> see
>>>>> if
>>>>> the cell was date formatted and so I have added a catch clause to solve
>>>>> this
>>>>> problem because I could not see what the problem was;
>>>>>
>>>>> public static void displayCellContents(String filename) {
>>>>> File file = null;
>>>>> FileInputStream fis = null;
>>>>> Workbook workbook = null;
>>>>> Sheet sheet = null;
>>>>> Row row = null;
>>>>> Cell cell = null;
>>>>> int numSheets = 0;
>>>>> Iterator<Row> rowIter = null;
>>>>> Iterator<Cell> cellIter = null;
>>>>> CellReference cellRef = null;
>>>>> try {
>>>>> // Open the workbook.
>>>>> file = new File(filename);
>>>>> fis = new FileInputStream(file);
>>>>> workbook = WorkbookFactory.create(fis);
>>>>>
>>>>> // Get the number of sheets in the workbook and enter a for
>>>>> loop
>>>>> // to iterate through them one at a time.
>>>>> numSheets = workbook.getNumberOfSheets();
>>>>> for(int i = 0; i < numSheets; i++) {
>>>>>
>>>>> // Get the sheet and recover from that an iterator that
>>>>> // allows us to step through all of the rows the sheet
>>>>> contains.
>>>>> sheet = workbook.getSheetAt(i);
>>>>>
>>>>> rowIter = sheet.rowIterator();
>>>>> while(rowIter.hasNext()) {
>>>>>
>>>>> // Get a row and recover from it an Iterator that
>>>>> allows
>>>>> // us to access each of the cells on the row.
>>>>> row = rowIter.next();
>>>>> cellIter = row.cellIterator();
>>>>> while(cellIter.hasNext()) {
>>>>>
>>>>> // Get a cell
>>>>> cell = cellIter.next();
>>>>>
>>>>> // The CellReference object must be created for
>>>>> each
>>>>> cell and used
>>>>> // to convert POI's indices into Excel's
>>>>> co-ordinate
>>>>> system.
>>>>> cellRef = new CellReference(row.getRowNum(),
>>>>> cell.getColumnIndex());
>>>>> System.out.print("Cell " +
>>>>> cellRef.formatAsString()
>>>>> + " ");
>>>>>
>>>>> switch(cell.getCellType()) {
>>>>> case Cell.CELL_TYPE_BLANK:
>>>>> System.out.println("is blank.");
>>>>> break;
>>>>> case Cell.CELL_TYPE_BOOLEAN:
>>>>> System.out.println("contains a boolean
>>>>> value
>>>>> of: " +
>>>>> cell.getBooleanCellValue());
>>>>> break;
>>>>> case Cell.CELL_TYPE_ERROR:
>>>>> System.out.println("contains an error
>>>>> code
>>>>> of: " +
>>>>> cell.getErrorCellValue());
>>>>> break;
>>>>> case Cell.CELL_TYPE_FORMULA:
>>>>> System.out.println("contains a formula:
>>>>> "
>>>>> +
>>>>> cell.getCellFormula());
>>>>> break;
>>>>> case Cell.CELL_TYPE_NUMERIC:
>>>>> try {
>>>>>
>>>>> if(DateUtil.isCellDateFormatted(cell)) {
>>>>> System.out.println("contains a
>>>>> date:
>>>>> " +
>>>>> cell.getDateCellValue());
>>>>> }
>>>>> else {
>>>>> System.out.println("contains a
>>>>> number: " +
>>>>>
>>>>> cell.getNumericCellValue());
>>>>> }
>>>>> }
>>>>> catch(IndexOutOfBoundsException iobEx)
>>>>> {
>>>>> System.out.println("an exception
>>>>> was
>>>>> thrown " +
>>>>> "checking the date
>>>>> formatting. "
>>>>> +
>>>>> "The cell contains the
>>>>> following
>>>>> " +
>>>>> "numeric value: " +
>>>>>
>>>>> cell.getNumericCellValue());
>>>>> }
>>>>> break;
>>>>> case Cell.CELL_TYPE_STRING:
>>>>> System.out.println("contains a String:
>>>>> "
>>>>> +
>>>>> cell.getStringCellValue());
>>>>> break;
>>>>> }
>>>>> }
>>>>> }
>>>>> }
>>>>> }
>>>>> catch(FileNotFoundException fnfEx) {
>>>>> System.out.println("Caught: " +
>>>>> fnfEx.getClass().getName());
>>>>> System.out.println("Message: " + fnfEx.getMessage());
>>>>> System.out.println("Stacktrace follows..............");
>>>>> fnfEx.printStackTrace(System.out);
>>>>> }
>>>>> catch(IOException ioEx) {
>>>>> System.out.println("Caught: " + ioEx.getClass().getName());
>>>>> System.out.println("Message: " + ioEx.getMessage());
>>>>> System.out.println("Stacktrace follows..............");
>>>>> ioEx.printStackTrace(System.out);
>>>>> }
>>>>> catch(InvalidFormatException invFEx) {
>>>>> System.out.println("Caught: " +
>>>>> invFEx.getClass().getName());
>>>>> System.out.println("Message: " + invFEx.getMessage());
>>>>> System.out.println("Stacktrace follows..............");
>>>>> invFEx.printStackTrace(System.out);
>>>>> }
>>>>> finally {
>>>>> if(fis != null) {
>>>>> try {
>>>>> fis.close();
>>>>> fis = null;
>>>>> }
>>>>> catch(IOException ioEx) {
>>>>> // I G N O R E
>>>>> }
>>>>> }
>>>>> }
>>>>> }
>>>>>
>>>>> Whilst this example uses Iterator to get at the cells, you are not
>>>>> forced
>>>>> to
>>>>> do this, it is possible to get at a cell be specifying the index number
>>>>> of
>>>>> the row and column. This would allow you to write some code that steps
>>>>> down
>>>>> each column rather than along each row to retrieve a reference to a
>>>>> cell.
>>>>> The changes to the code I have posted would be quite trivial and it
>>>>> might
>>>>> be
>>>>> worth you time seeing if you can modify what I have written to use
>>>>> indices
>>>>> rather than iterators. Simply write code that discovers how many rows
>>>>> there
>>>>> are on the sheet then enter a for loo to step through each row at a
>>>>> time.
>>>>> Within that loop, find out how many cells there are in the row and then
>>>>> write another for loop to get each cell in turn and output it's
>>>>> contents
>>>>> to
>>>>> standard output. That will help you to see how the API hangs together
>>>>> IMO.
>>>>>
>>>>> As always, if you have any questions, just post to this list.
>>>>>
>>>>> Yours
>>>>>
>>>>> Mark B
>>>>>
>>>>>
>>>>> Henrik-23 wrote:
>>>>>
>>>>>
>>>>>
>>>>>> I have been trying to make a very simpe Java program, the only
>>>>>> advanced
>>>>>> part is the part using HSSF to read an xls file and get all the data.
>>>>>> What i want to do i ittretare thru ever colum on every row and just
>>>>>> print that data out in the comand prompt. I have found lots of example
>>>>>> of this on the homepage but no whole code that realy works. They all
>>>>>> seems to need something more to get them to run. It would be very much
>>>>>> appriciated if someone just could point me in the right direction to
>>>>>> get
>>>>>> this working.
>>>>>>
>>>>>> Best regards Henrik
>>>>>>
>>>>>> ---------------------------------------------------------------------
>>>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>>
>>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Basic question for reading xls file
Posted by MSB <ma...@tiscali.co.uk>.
Hello Henrik
I downloaded the file and had a look, it almost made me spill my coffee. It
is html markup in a file with a .xls extension. Excel is quite happy to
parse this - even though it complains about the file extension - but you can
open the file using a simple text editor and take a look for yourself, I
just used Wordpad.
If you want to parse this then POI will not be of any use to you, sorry.
There are lots of tools that would allow you to parse html, the Java SDK
even has in-built support in the form of the HTMLEditorKit class.
Yours
Mark B
Henrik-23 wrote:
>
> Thanks again for an extremely good answere. This time however i got a
> devestating error :)
>
> Exception in thread "main" java.lang.IllegalArgumentException: Your
> InputStream was neither an OLE2 stream, nor an OOXML stream
> at
> org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:65)
> at lasxls.dataExtract.displayCellContents(dataExtract.java:56)
> at lasxls.Main.main(Main.java:20)
>
> This is strange, the datasource im trying to use is from this link
> http://www.nasdaqomxnordic.com/aktier/Historiska_kurser/?languageId=1&Instrument=SSE366
> It is the Excel icon next to the search botton to download the
> hirstorical data. Is this not XLS???
>
> MSB skrev:
>> You need to add additional archives - .jar files - to your classpath.
>> This is
>> old code that I have not refactored yet so the list of archives below
>> relates to an earlier version of POI but you can use it as a guide for
>> the
>> similar components from the latest version;
>>
>> poi-3.5-beta7-20090612.jar
>> poi-ooxml-3.5-beta7-20090613.jar
>> openxml4j-1.0-beta.jar
>> xmlbeans-2.3.0.jar
>> ooxml-schemas-1.0.jar
>> dom4j-1.6.1.jar
>>
>> The exact names of the files will have changed now that the developers
>> are
>> working on 3.6, but they will still be very similar and if you do not
>> already have these archives, you will need to visit the torchbox address
>> I
>> posted in an earlier response, search thruogh the list of archives there
>> and
>> download those whose names are similar to the above. I have just had a
>> quick
>> check and it seems these are the current versions;
>>
>> poi-3.6-beta1-20091006.jar
>> poi-ooxml-3.6-beta1-20091006.jar
>> and the current versions of the other files listed should be in this zip
>> file
>> poi-dependencies-3.6-beta1-20091006.zip
>> that you can download and then unzip into a directory.
>>
>> Once you have the archives, add them to your classpath and you should be
>> able to both compile and run the code.
>>
>> Hope that helps. If you have any other problems, just post to the list.
>> Just
>> as an aside, do not worry about the beta designation for the archives.
>> Yegor
>> and the other developers ensure that the product is stable and 3.6 beta
>> will
>> be 3.5 final with the latest fixes and patches added; do not worry that
>> you
>> will be using some buggy patched nightmare because this would simply not
>> be
>> allowed to happen.
>>
>> Yours
>>
>> Mark B
>>
>>
>> Henrik-23 wrote:
>>
>>> I get this error:
>>> Exception in thread "main" java.lang.NoClassDefFoundError:
>>> org/apache/xmlbeans/XmlOptions
>>> at
>>> org.apache.poi.POIXMLDocumentPart.<clinit>(POIXMLDocumentPart.java:43)
>>> at
>>> org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:62)
>>> at lasxls.dataExtract.displayCellContents(dataExtract.java:56)
>>> at lasxls.Main.main(Main.java:20)
>>> Caused by: java.lang.ClassNotFoundException:
>>> org.apache.xmlbeans.XmlOptions
>>> at java.net.URLClassLoader$1.run(URLClassLoader.java:200)
>>> at java.security.AccessController.doPrivileged(Native Method)
>>> at java.net.URLClassLoader.findClass(URLClassLoader.java:188)
>>> at java.lang.ClassLoader.loadClass(ClassLoader.java:307)
>>> at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
>>> at java.lang.ClassLoader.loadClass(ClassLoader.java:252)
>>> at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:320)
>>> ... 4 more
>>> Java Result: 1
>>> BUILD SUCCESSFUL (total time: 1 second)
>>>
>>>
>>> This is my main class.
>>> public class Main {
>>>
>>> /**
>>> * @param args the command line arguments
>>> */
>>> public static void main(String[] args) {
>>> // TODO code application logic here
>>> dataExtract ins = new dataExtract();
>>> ins.displayCellContents("E:/1_Trading/Java/LasXLS/Volvo.xls");
>>> }
>>>
>>> }
>>>
>>> MSB skrev:
>>>
>>>> Had the chance to play with some code this evening and I have imporved
>>>> the
>>>> snippet I posted earlier so that it now outputs the cells address in
>>>> familar
>>>> Excel notation - eg A1. Alsom I noticed when I ran it against a
>>>> problematic
>>>> test file that an exception could be thrown when the test was made to
>>>> see
>>>> if
>>>> the cell was date formatted and so I have added a catch clause to solve
>>>> this
>>>> problem because I could not see what the problem was;
>>>>
>>>> public static void displayCellContents(String filename) {
>>>> File file = null;
>>>> FileInputStream fis = null;
>>>> Workbook workbook = null;
>>>> Sheet sheet = null;
>>>> Row row = null;
>>>> Cell cell = null;
>>>> int numSheets = 0;
>>>> Iterator<Row> rowIter = null;
>>>> Iterator<Cell> cellIter = null;
>>>> CellReference cellRef = null;
>>>> try {
>>>> // Open the workbook.
>>>> file = new File(filename);
>>>> fis = new FileInputStream(file);
>>>> workbook = WorkbookFactory.create(fis);
>>>>
>>>> // Get the number of sheets in the workbook and enter a for
>>>> loop
>>>> // to iterate through them one at a time.
>>>> numSheets = workbook.getNumberOfSheets();
>>>> for(int i = 0; i < numSheets; i++) {
>>>>
>>>> // Get the sheet and recover from that an iterator that
>>>> // allows us to step through all of the rows the sheet
>>>> contains.
>>>> sheet = workbook.getSheetAt(i);
>>>>
>>>> rowIter = sheet.rowIterator();
>>>> while(rowIter.hasNext()) {
>>>>
>>>> // Get a row and recover from it an Iterator that
>>>> allows
>>>> // us to access each of the cells on the row.
>>>> row = rowIter.next();
>>>> cellIter = row.cellIterator();
>>>> while(cellIter.hasNext()) {
>>>>
>>>> // Get a cell
>>>> cell = cellIter.next();
>>>>
>>>> // The CellReference object must be created for
>>>> each
>>>> cell and used
>>>> // to convert POI's indices into Excel's
>>>> co-ordinate
>>>> system.
>>>> cellRef = new CellReference(row.getRowNum(),
>>>> cell.getColumnIndex());
>>>> System.out.print("Cell " +
>>>> cellRef.formatAsString()
>>>> + " ");
>>>>
>>>> switch(cell.getCellType()) {
>>>> case Cell.CELL_TYPE_BLANK:
>>>> System.out.println("is blank.");
>>>> break;
>>>> case Cell.CELL_TYPE_BOOLEAN:
>>>> System.out.println("contains a boolean
>>>> value
>>>> of: " +
>>>> cell.getBooleanCellValue());
>>>> break;
>>>> case Cell.CELL_TYPE_ERROR:
>>>> System.out.println("contains an error
>>>> code
>>>> of: " +
>>>> cell.getErrorCellValue());
>>>> break;
>>>> case Cell.CELL_TYPE_FORMULA:
>>>> System.out.println("contains a formula:
>>>> "
>>>> +
>>>> cell.getCellFormula());
>>>> break;
>>>> case Cell.CELL_TYPE_NUMERIC:
>>>> try {
>>>>
>>>> if(DateUtil.isCellDateFormatted(cell)) {
>>>> System.out.println("contains a
>>>> date:
>>>> " +
>>>> cell.getDateCellValue());
>>>> }
>>>> else {
>>>> System.out.println("contains a
>>>> number: " +
>>>>
>>>> cell.getNumericCellValue());
>>>> }
>>>> }
>>>> catch(IndexOutOfBoundsException iobEx)
>>>> {
>>>> System.out.println("an exception
>>>> was
>>>> thrown " +
>>>> "checking the date
>>>> formatting. "
>>>> +
>>>> "The cell contains the
>>>> following
>>>> " +
>>>> "numeric value: " +
>>>>
>>>> cell.getNumericCellValue());
>>>> }
>>>> break;
>>>> case Cell.CELL_TYPE_STRING:
>>>> System.out.println("contains a String:
>>>> "
>>>> +
>>>> cell.getStringCellValue());
>>>> break;
>>>> }
>>>> }
>>>> }
>>>> }
>>>> }
>>>> catch(FileNotFoundException fnfEx) {
>>>> System.out.println("Caught: " +
>>>> fnfEx.getClass().getName());
>>>> System.out.println("Message: " + fnfEx.getMessage());
>>>> System.out.println("Stacktrace follows..............");
>>>> fnfEx.printStackTrace(System.out);
>>>> }
>>>> catch(IOException ioEx) {
>>>> System.out.println("Caught: " + ioEx.getClass().getName());
>>>> System.out.println("Message: " + ioEx.getMessage());
>>>> System.out.println("Stacktrace follows..............");
>>>> ioEx.printStackTrace(System.out);
>>>> }
>>>> catch(InvalidFormatException invFEx) {
>>>> System.out.println("Caught: " +
>>>> invFEx.getClass().getName());
>>>> System.out.println("Message: " + invFEx.getMessage());
>>>> System.out.println("Stacktrace follows..............");
>>>> invFEx.printStackTrace(System.out);
>>>> }
>>>> finally {
>>>> if(fis != null) {
>>>> try {
>>>> fis.close();
>>>> fis = null;
>>>> }
>>>> catch(IOException ioEx) {
>>>> // I G N O R E
>>>> }
>>>> }
>>>> }
>>>> }
>>>>
>>>> Whilst this example uses Iterator to get at the cells, you are not
>>>> forced
>>>> to
>>>> do this, it is possible to get at a cell be specifying the index number
>>>> of
>>>> the row and column. This would allow you to write some code that steps
>>>> down
>>>> each column rather than along each row to retrieve a reference to a
>>>> cell.
>>>> The changes to the code I have posted would be quite trivial and it
>>>> might
>>>> be
>>>> worth you time seeing if you can modify what I have written to use
>>>> indices
>>>> rather than iterators. Simply write code that discovers how many rows
>>>> there
>>>> are on the sheet then enter a for loo to step through each row at a
>>>> time.
>>>> Within that loop, find out how many cells there are in the row and then
>>>> write another for loop to get each cell in turn and output it's
>>>> contents
>>>> to
>>>> standard output. That will help you to see how the API hangs together
>>>> IMO.
>>>>
>>>> As always, if you have any questions, just post to this list.
>>>>
>>>> Yours
>>>>
>>>> Mark B
>>>>
>>>>
>>>> Henrik-23 wrote:
>>>>
>>>>
>>>>> I have been trying to make a very simpe Java program, the only
>>>>> advanced
>>>>> part is the part using HSSF to read an xls file and get all the data.
>>>>> What i want to do i ittretare thru ever colum on every row and just
>>>>> print that data out in the comand prompt. I have found lots of example
>>>>> of this on the homepage but no whole code that realy works. They all
>>>>> seems to need something more to get them to run. It would be very much
>>>>> appriciated if someone just could point me in the right direction to
>>>>> get
>>>>> this working.
>>>>>
>>>>> Best regards Henrik
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>>
>>>
>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
--
View this message in context: http://www.nabble.com/Basic-question-for-reading-xls-file-tp25743149p25768379.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Basic question for reading xls file
Posted by Henrik <re...@henkelarsson.se>.
Thanks again for an extremely good answere. This time however i got a
devestating error :)
Exception in thread "main" java.lang.IllegalArgumentException: Your
InputStream was neither an OLE2 stream, nor an OOXML stream
at
org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:65)
at lasxls.dataExtract.displayCellContents(dataExtract.java:56)
at lasxls.Main.main(Main.java:20)
This is strange, the datasource im trying to use is from this link
http://www.nasdaqomxnordic.com/aktier/Historiska_kurser/?languageId=1&Instrument=SSE366
It is the Excel icon next to the search botton to download the
hirstorical data. Is this not XLS???
MSB skrev:
> You need to add additional archives - .jar files - to your classpath. This is
> old code that I have not refactored yet so the list of archives below
> relates to an earlier version of POI but you can use it as a guide for the
> similar components from the latest version;
>
> poi-3.5-beta7-20090612.jar
> poi-ooxml-3.5-beta7-20090613.jar
> openxml4j-1.0-beta.jar
> xmlbeans-2.3.0.jar
> ooxml-schemas-1.0.jar
> dom4j-1.6.1.jar
>
> The exact names of the files will have changed now that the developers are
> working on 3.6, but they will still be very similar and if you do not
> already have these archives, you will need to visit the torchbox address I
> posted in an earlier response, search thruogh the list of archives there and
> download those whose names are similar to the above. I have just had a quick
> check and it seems these are the current versions;
>
> poi-3.6-beta1-20091006.jar
> poi-ooxml-3.6-beta1-20091006.jar
> and the current versions of the other files listed should be in this zip
> file
> poi-dependencies-3.6-beta1-20091006.zip
> that you can download and then unzip into a directory.
>
> Once you have the archives, add them to your classpath and you should be
> able to both compile and run the code.
>
> Hope that helps. If you have any other problems, just post to the list. Just
> as an aside, do not worry about the beta designation for the archives. Yegor
> and the other developers ensure that the product is stable and 3.6 beta will
> be 3.5 final with the latest fixes and patches added; do not worry that you
> will be using some buggy patched nightmare because this would simply not be
> allowed to happen.
>
> Yours
>
> Mark B
>
>
> Henrik-23 wrote:
>
>> I get this error:
>> Exception in thread "main" java.lang.NoClassDefFoundError:
>> org/apache/xmlbeans/XmlOptions
>> at
>> org.apache.poi.POIXMLDocumentPart.<clinit>(POIXMLDocumentPart.java:43)
>> at
>> org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:62)
>> at lasxls.dataExtract.displayCellContents(dataExtract.java:56)
>> at lasxls.Main.main(Main.java:20)
>> Caused by: java.lang.ClassNotFoundException:
>> org.apache.xmlbeans.XmlOptions
>> at java.net.URLClassLoader$1.run(URLClassLoader.java:200)
>> at java.security.AccessController.doPrivileged(Native Method)
>> at java.net.URLClassLoader.findClass(URLClassLoader.java:188)
>> at java.lang.ClassLoader.loadClass(ClassLoader.java:307)
>> at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
>> at java.lang.ClassLoader.loadClass(ClassLoader.java:252)
>> at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:320)
>> ... 4 more
>> Java Result: 1
>> BUILD SUCCESSFUL (total time: 1 second)
>>
>>
>> This is my main class.
>> public class Main {
>>
>> /**
>> * @param args the command line arguments
>> */
>> public static void main(String[] args) {
>> // TODO code application logic here
>> dataExtract ins = new dataExtract();
>> ins.displayCellContents("E:/1_Trading/Java/LasXLS/Volvo.xls");
>> }
>>
>> }
>>
>> MSB skrev:
>>
>>> Had the chance to play with some code this evening and I have imporved
>>> the
>>> snippet I posted earlier so that it now outputs the cells address in
>>> familar
>>> Excel notation - eg A1. Alsom I noticed when I ran it against a
>>> problematic
>>> test file that an exception could be thrown when the test was made to see
>>> if
>>> the cell was date formatted and so I have added a catch clause to solve
>>> this
>>> problem because I could not see what the problem was;
>>>
>>> public static void displayCellContents(String filename) {
>>> File file = null;
>>> FileInputStream fis = null;
>>> Workbook workbook = null;
>>> Sheet sheet = null;
>>> Row row = null;
>>> Cell cell = null;
>>> int numSheets = 0;
>>> Iterator<Row> rowIter = null;
>>> Iterator<Cell> cellIter = null;
>>> CellReference cellRef = null;
>>> try {
>>> // Open the workbook.
>>> file = new File(filename);
>>> fis = new FileInputStream(file);
>>> workbook = WorkbookFactory.create(fis);
>>>
>>> // Get the number of sheets in the workbook and enter a for
>>> loop
>>> // to iterate through them one at a time.
>>> numSheets = workbook.getNumberOfSheets();
>>> for(int i = 0; i < numSheets; i++) {
>>>
>>> // Get the sheet and recover from that an iterator that
>>> // allows us to step through all of the rows the sheet
>>> contains.
>>> sheet = workbook.getSheetAt(i);
>>>
>>> rowIter = sheet.rowIterator();
>>> while(rowIter.hasNext()) {
>>>
>>> // Get a row and recover from it an Iterator that
>>> allows
>>> // us to access each of the cells on the row.
>>> row = rowIter.next();
>>> cellIter = row.cellIterator();
>>> while(cellIter.hasNext()) {
>>>
>>> // Get a cell
>>> cell = cellIter.next();
>>>
>>> // The CellReference object must be created for
>>> each
>>> cell and used
>>> // to convert POI's indices into Excel's
>>> co-ordinate
>>> system.
>>> cellRef = new CellReference(row.getRowNum(),
>>> cell.getColumnIndex());
>>> System.out.print("Cell " +
>>> cellRef.formatAsString()
>>> + " ");
>>>
>>> switch(cell.getCellType()) {
>>> case Cell.CELL_TYPE_BLANK:
>>> System.out.println("is blank.");
>>> break;
>>> case Cell.CELL_TYPE_BOOLEAN:
>>> System.out.println("contains a boolean
>>> value
>>> of: " +
>>> cell.getBooleanCellValue());
>>> break;
>>> case Cell.CELL_TYPE_ERROR:
>>> System.out.println("contains an error
>>> code
>>> of: " +
>>> cell.getErrorCellValue());
>>> break;
>>> case Cell.CELL_TYPE_FORMULA:
>>> System.out.println("contains a formula: "
>>> +
>>> cell.getCellFormula());
>>> break;
>>> case Cell.CELL_TYPE_NUMERIC:
>>> try {
>>>
>>> if(DateUtil.isCellDateFormatted(cell)) {
>>> System.out.println("contains a
>>> date:
>>> " +
>>> cell.getDateCellValue());
>>> }
>>> else {
>>> System.out.println("contains a
>>> number: " +
>>> cell.getNumericCellValue());
>>> }
>>> }
>>> catch(IndexOutOfBoundsException iobEx) {
>>> System.out.println("an exception was
>>> thrown " +
>>> "checking the date
>>> formatting. "
>>> +
>>> "The cell contains the
>>> following
>>> " +
>>> "numeric value: " +
>>> cell.getNumericCellValue());
>>> }
>>> break;
>>> case Cell.CELL_TYPE_STRING:
>>> System.out.println("contains a String: "
>>> +
>>> cell.getStringCellValue());
>>> break;
>>> }
>>> }
>>> }
>>> }
>>> }
>>> catch(FileNotFoundException fnfEx) {
>>> System.out.println("Caught: " + fnfEx.getClass().getName());
>>> System.out.println("Message: " + fnfEx.getMessage());
>>> System.out.println("Stacktrace follows..............");
>>> fnfEx.printStackTrace(System.out);
>>> }
>>> catch(IOException ioEx) {
>>> System.out.println("Caught: " + ioEx.getClass().getName());
>>> System.out.println("Message: " + ioEx.getMessage());
>>> System.out.println("Stacktrace follows..............");
>>> ioEx.printStackTrace(System.out);
>>> }
>>> catch(InvalidFormatException invFEx) {
>>> System.out.println("Caught: " + invFEx.getClass().getName());
>>> System.out.println("Message: " + invFEx.getMessage());
>>> System.out.println("Stacktrace follows..............");
>>> invFEx.printStackTrace(System.out);
>>> }
>>> finally {
>>> if(fis != null) {
>>> try {
>>> fis.close();
>>> fis = null;
>>> }
>>> catch(IOException ioEx) {
>>> // I G N O R E
>>> }
>>> }
>>> }
>>> }
>>>
>>> Whilst this example uses Iterator to get at the cells, you are not forced
>>> to
>>> do this, it is possible to get at a cell be specifying the index number
>>> of
>>> the row and column. This would allow you to write some code that steps
>>> down
>>> each column rather than along each row to retrieve a reference to a cell.
>>> The changes to the code I have posted would be quite trivial and it might
>>> be
>>> worth you time seeing if you can modify what I have written to use
>>> indices
>>> rather than iterators. Simply write code that discovers how many rows
>>> there
>>> are on the sheet then enter a for loo to step through each row at a time.
>>> Within that loop, find out how many cells there are in the row and then
>>> write another for loop to get each cell in turn and output it's contents
>>> to
>>> standard output. That will help you to see how the API hangs together
>>> IMO.
>>>
>>> As always, if you have any questions, just post to this list.
>>>
>>> Yours
>>>
>>> Mark B
>>>
>>>
>>> Henrik-23 wrote:
>>>
>>>
>>>> I have been trying to make a very simpe Java program, the only advanced
>>>> part is the part using HSSF to read an xls file and get all the data.
>>>> What i want to do i ittretare thru ever colum on every row and just
>>>> print that data out in the comand prompt. I have found lots of example
>>>> of this on the homepage but no whole code that realy works. They all
>>>> seems to need something more to get them to run. It would be very much
>>>> appriciated if someone just could point me in the right direction to get
>>>> this working.
>>>>
>>>> Best regards Henrik
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>>
>>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Basic question for reading xls file
Posted by MSB <ma...@tiscali.co.uk>.
You need to add additional archives - .jar files - to your classpath. This is
old code that I have not refactored yet so the list of archives below
relates to an earlier version of POI but you can use it as a guide for the
similar components from the latest version;
poi-3.5-beta7-20090612.jar
poi-ooxml-3.5-beta7-20090613.jar
openxml4j-1.0-beta.jar
xmlbeans-2.3.0.jar
ooxml-schemas-1.0.jar
dom4j-1.6.1.jar
The exact names of the files will have changed now that the developers are
working on 3.6, but they will still be very similar and if you do not
already have these archives, you will need to visit the torchbox address I
posted in an earlier response, search thruogh the list of archives there and
download those whose names are similar to the above. I have just had a quick
check and it seems these are the current versions;
poi-3.6-beta1-20091006.jar
poi-ooxml-3.6-beta1-20091006.jar
and the current versions of the other files listed should be in this zip
file
poi-dependencies-3.6-beta1-20091006.zip
that you can download and then unzip into a directory.
Once you have the archives, add them to your classpath and you should be
able to both compile and run the code.
Hope that helps. If you have any other problems, just post to the list. Just
as an aside, do not worry about the beta designation for the archives. Yegor
and the other developers ensure that the product is stable and 3.6 beta will
be 3.5 final with the latest fixes and patches added; do not worry that you
will be using some buggy patched nightmare because this would simply not be
allowed to happen.
Yours
Mark B
Henrik-23 wrote:
>
> I get this error:
> Exception in thread "main" java.lang.NoClassDefFoundError:
> org/apache/xmlbeans/XmlOptions
> at
> org.apache.poi.POIXMLDocumentPart.<clinit>(POIXMLDocumentPart.java:43)
> at
> org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:62)
> at lasxls.dataExtract.displayCellContents(dataExtract.java:56)
> at lasxls.Main.main(Main.java:20)
> Caused by: java.lang.ClassNotFoundException:
> org.apache.xmlbeans.XmlOptions
> at java.net.URLClassLoader$1.run(URLClassLoader.java:200)
> at java.security.AccessController.doPrivileged(Native Method)
> at java.net.URLClassLoader.findClass(URLClassLoader.java:188)
> at java.lang.ClassLoader.loadClass(ClassLoader.java:307)
> at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
> at java.lang.ClassLoader.loadClass(ClassLoader.java:252)
> at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:320)
> ... 4 more
> Java Result: 1
> BUILD SUCCESSFUL (total time: 1 second)
>
>
> This is my main class.
> public class Main {
>
> /**
> * @param args the command line arguments
> */
> public static void main(String[] args) {
> // TODO code application logic here
> dataExtract ins = new dataExtract();
> ins.displayCellContents("E:/1_Trading/Java/LasXLS/Volvo.xls");
> }
>
> }
>
> MSB skrev:
>> Had the chance to play with some code this evening and I have imporved
>> the
>> snippet I posted earlier so that it now outputs the cells address in
>> familar
>> Excel notation - eg A1. Alsom I noticed when I ran it against a
>> problematic
>> test file that an exception could be thrown when the test was made to see
>> if
>> the cell was date formatted and so I have added a catch clause to solve
>> this
>> problem because I could not see what the problem was;
>>
>> public static void displayCellContents(String filename) {
>> File file = null;
>> FileInputStream fis = null;
>> Workbook workbook = null;
>> Sheet sheet = null;
>> Row row = null;
>> Cell cell = null;
>> int numSheets = 0;
>> Iterator<Row> rowIter = null;
>> Iterator<Cell> cellIter = null;
>> CellReference cellRef = null;
>> try {
>> // Open the workbook.
>> file = new File(filename);
>> fis = new FileInputStream(file);
>> workbook = WorkbookFactory.create(fis);
>>
>> // Get the number of sheets in the workbook and enter a for
>> loop
>> // to iterate through them one at a time.
>> numSheets = workbook.getNumberOfSheets();
>> for(int i = 0; i < numSheets; i++) {
>>
>> // Get the sheet and recover from that an iterator that
>> // allows us to step through all of the rows the sheet
>> contains.
>> sheet = workbook.getSheetAt(i);
>>
>> rowIter = sheet.rowIterator();
>> while(rowIter.hasNext()) {
>>
>> // Get a row and recover from it an Iterator that
>> allows
>> // us to access each of the cells on the row.
>> row = rowIter.next();
>> cellIter = row.cellIterator();
>> while(cellIter.hasNext()) {
>>
>> // Get a cell
>> cell = cellIter.next();
>>
>> // The CellReference object must be created for
>> each
>> cell and used
>> // to convert POI's indices into Excel's
>> co-ordinate
>> system.
>> cellRef = new CellReference(row.getRowNum(),
>> cell.getColumnIndex());
>> System.out.print("Cell " +
>> cellRef.formatAsString()
>> + " ");
>>
>> switch(cell.getCellType()) {
>> case Cell.CELL_TYPE_BLANK:
>> System.out.println("is blank.");
>> break;
>> case Cell.CELL_TYPE_BOOLEAN:
>> System.out.println("contains a boolean
>> value
>> of: " +
>> cell.getBooleanCellValue());
>> break;
>> case Cell.CELL_TYPE_ERROR:
>> System.out.println("contains an error
>> code
>> of: " +
>> cell.getErrorCellValue());
>> break;
>> case Cell.CELL_TYPE_FORMULA:
>> System.out.println("contains a formula: "
>> +
>> cell.getCellFormula());
>> break;
>> case Cell.CELL_TYPE_NUMERIC:
>> try {
>>
>> if(DateUtil.isCellDateFormatted(cell)) {
>> System.out.println("contains a
>> date:
>> " +
>> cell.getDateCellValue());
>> }
>> else {
>> System.out.println("contains a
>> number: " +
>> cell.getNumericCellValue());
>> }
>> }
>> catch(IndexOutOfBoundsException iobEx) {
>> System.out.println("an exception was
>> thrown " +
>> "checking the date
>> formatting. "
>> +
>> "The cell contains the
>> following
>> " +
>> "numeric value: " +
>> cell.getNumericCellValue());
>> }
>> break;
>> case Cell.CELL_TYPE_STRING:
>> System.out.println("contains a String: "
>> +
>> cell.getStringCellValue());
>> break;
>> }
>> }
>> }
>> }
>> }
>> catch(FileNotFoundException fnfEx) {
>> System.out.println("Caught: " + fnfEx.getClass().getName());
>> System.out.println("Message: " + fnfEx.getMessage());
>> System.out.println("Stacktrace follows..............");
>> fnfEx.printStackTrace(System.out);
>> }
>> catch(IOException ioEx) {
>> System.out.println("Caught: " + ioEx.getClass().getName());
>> System.out.println("Message: " + ioEx.getMessage());
>> System.out.println("Stacktrace follows..............");
>> ioEx.printStackTrace(System.out);
>> }
>> catch(InvalidFormatException invFEx) {
>> System.out.println("Caught: " + invFEx.getClass().getName());
>> System.out.println("Message: " + invFEx.getMessage());
>> System.out.println("Stacktrace follows..............");
>> invFEx.printStackTrace(System.out);
>> }
>> finally {
>> if(fis != null) {
>> try {
>> fis.close();
>> fis = null;
>> }
>> catch(IOException ioEx) {
>> // I G N O R E
>> }
>> }
>> }
>> }
>>
>> Whilst this example uses Iterator to get at the cells, you are not forced
>> to
>> do this, it is possible to get at a cell be specifying the index number
>> of
>> the row and column. This would allow you to write some code that steps
>> down
>> each column rather than along each row to retrieve a reference to a cell.
>> The changes to the code I have posted would be quite trivial and it might
>> be
>> worth you time seeing if you can modify what I have written to use
>> indices
>> rather than iterators. Simply write code that discovers how many rows
>> there
>> are on the sheet then enter a for loo to step through each row at a time.
>> Within that loop, find out how many cells there are in the row and then
>> write another for loop to get each cell in turn and output it's contents
>> to
>> standard output. That will help you to see how the API hangs together
>> IMO.
>>
>> As always, if you have any questions, just post to this list.
>>
>> Yours
>>
>> Mark B
>>
>>
>> Henrik-23 wrote:
>>
>>> I have been trying to make a very simpe Java program, the only advanced
>>> part is the part using HSSF to read an xls file and get all the data.
>>> What i want to do i ittretare thru ever colum on every row and just
>>> print that data out in the comand prompt. I have found lots of example
>>> of this on the homepage but no whole code that realy works. They all
>>> seems to need something more to get them to run. It would be very much
>>> appriciated if someone just could point me in the right direction to get
>>> this working.
>>>
>>> Best regards Henrik
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>>
>>>
>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
--
View this message in context: http://www.nabble.com/Basic-question-for-reading-xls-file-tp25743149p25763673.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Basic question for reading xls file
Posted by Henrik <re...@henkelarsson.se>.
I get this error:
Exception in thread "main" java.lang.NoClassDefFoundError:
org/apache/xmlbeans/XmlOptions
at
org.apache.poi.POIXMLDocumentPart.<clinit>(POIXMLDocumentPart.java:43)
at
org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:62)
at lasxls.dataExtract.displayCellContents(dataExtract.java:56)
at lasxls.Main.main(Main.java:20)
Caused by: java.lang.ClassNotFoundException: org.apache.xmlbeans.XmlOptions
at java.net.URLClassLoader$1.run(URLClassLoader.java:200)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:188)
at java.lang.ClassLoader.loadClass(ClassLoader.java:307)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
at java.lang.ClassLoader.loadClass(ClassLoader.java:252)
at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:320)
... 4 more
Java Result: 1
BUILD SUCCESSFUL (total time: 1 second)
This is my main class.
public class Main {
/**
* @param args the command line arguments
*/
public static void main(String[] args) {
// TODO code application logic here
dataExtract ins = new dataExtract();
ins.displayCellContents("E:/1_Trading/Java/LasXLS/Volvo.xls");
}
}
MSB skrev:
> Had the chance to play with some code this evening and I have imporved the
> snippet I posted earlier so that it now outputs the cells address in familar
> Excel notation - eg A1. Alsom I noticed when I ran it against a problematic
> test file that an exception could be thrown when the test was made to see if
> the cell was date formatted and so I have added a catch clause to solve this
> problem because I could not see what the problem was;
>
> public static void displayCellContents(String filename) {
> File file = null;
> FileInputStream fis = null;
> Workbook workbook = null;
> Sheet sheet = null;
> Row row = null;
> Cell cell = null;
> int numSheets = 0;
> Iterator<Row> rowIter = null;
> Iterator<Cell> cellIter = null;
> CellReference cellRef = null;
> try {
> // Open the workbook.
> file = new File(filename);
> fis = new FileInputStream(file);
> workbook = WorkbookFactory.create(fis);
>
> // Get the number of sheets in the workbook and enter a for loop
> // to iterate through them one at a time.
> numSheets = workbook.getNumberOfSheets();
> for(int i = 0; i < numSheets; i++) {
>
> // Get the sheet and recover from that an iterator that
> // allows us to step through all of the rows the sheet
> contains.
> sheet = workbook.getSheetAt(i);
>
> rowIter = sheet.rowIterator();
> while(rowIter.hasNext()) {
>
> // Get a row and recover from it an Iterator that allows
> // us to access each of the cells on the row.
> row = rowIter.next();
> cellIter = row.cellIterator();
> while(cellIter.hasNext()) {
>
> // Get a cell
> cell = cellIter.next();
>
> // The CellReference object must be created for each
> cell and used
> // to convert POI's indices into Excel's co-ordinate
> system.
> cellRef = new CellReference(row.getRowNum(),
> cell.getColumnIndex());
> System.out.print("Cell " + cellRef.formatAsString()
> + " ");
>
> switch(cell.getCellType()) {
> case Cell.CELL_TYPE_BLANK:
> System.out.println("is blank.");
> break;
> case Cell.CELL_TYPE_BOOLEAN:
> System.out.println("contains a boolean value
> of: " +
> cell.getBooleanCellValue());
> break;
> case Cell.CELL_TYPE_ERROR:
> System.out.println("contains an error code
> of: " +
> cell.getErrorCellValue());
> break;
> case Cell.CELL_TYPE_FORMULA:
> System.out.println("contains a formula: " +
> cell.getCellFormula());
> break;
> case Cell.CELL_TYPE_NUMERIC:
> try {
> if(DateUtil.isCellDateFormatted(cell)) {
> System.out.println("contains a date:
> " +
> cell.getDateCellValue());
> }
> else {
> System.out.println("contains a
> number: " +
> cell.getNumericCellValue());
> }
> }
> catch(IndexOutOfBoundsException iobEx) {
> System.out.println("an exception was
> thrown " +
> "checking the date formatting. "
> +
> "The cell contains the following
> " +
> "numeric value: " +
> cell.getNumericCellValue());
> }
> break;
> case Cell.CELL_TYPE_STRING:
> System.out.println("contains a String: " +
> cell.getStringCellValue());
> break;
> }
> }
> }
> }
> }
> catch(FileNotFoundException fnfEx) {
> System.out.println("Caught: " + fnfEx.getClass().getName());
> System.out.println("Message: " + fnfEx.getMessage());
> System.out.println("Stacktrace follows..............");
> fnfEx.printStackTrace(System.out);
> }
> catch(IOException ioEx) {
> System.out.println("Caught: " + ioEx.getClass().getName());
> System.out.println("Message: " + ioEx.getMessage());
> System.out.println("Stacktrace follows..............");
> ioEx.printStackTrace(System.out);
> }
> catch(InvalidFormatException invFEx) {
> System.out.println("Caught: " + invFEx.getClass().getName());
> System.out.println("Message: " + invFEx.getMessage());
> System.out.println("Stacktrace follows..............");
> invFEx.printStackTrace(System.out);
> }
> finally {
> if(fis != null) {
> try {
> fis.close();
> fis = null;
> }
> catch(IOException ioEx) {
> // I G N O R E
> }
> }
> }
> }
>
> Whilst this example uses Iterator to get at the cells, you are not forced to
> do this, it is possible to get at a cell be specifying the index number of
> the row and column. This would allow you to write some code that steps down
> each column rather than along each row to retrieve a reference to a cell.
> The changes to the code I have posted would be quite trivial and it might be
> worth you time seeing if you can modify what I have written to use indices
> rather than iterators. Simply write code that discovers how many rows there
> are on the sheet then enter a for loo to step through each row at a time.
> Within that loop, find out how many cells there are in the row and then
> write another for loop to get each cell in turn and output it's contents to
> standard output. That will help you to see how the API hangs together IMO.
>
> As always, if you have any questions, just post to this list.
>
> Yours
>
> Mark B
>
>
> Henrik-23 wrote:
>
>> I have been trying to make a very simpe Java program, the only advanced
>> part is the part using HSSF to read an xls file and get all the data.
>> What i want to do i ittretare thru ever colum on every row and just
>> print that data out in the comand prompt. I have found lots of example
>> of this on the homepage but no whole code that realy works. They all
>> seems to need something more to get them to run. It would be very much
>> appriciated if someone just could point me in the right direction to get
>> this working.
>>
>> Best regards Henrik
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>>
>>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Basic question for reading xls file
Posted by MSB <ma...@tiscali.co.uk>.
Had the chance to play with some code this evening and I have imporved the
snippet I posted earlier so that it now outputs the cells address in familar
Excel notation - eg A1. Alsom I noticed when I ran it against a problematic
test file that an exception could be thrown when the test was made to see if
the cell was date formatted and so I have added a catch clause to solve this
problem because I could not see what the problem was;
public static void displayCellContents(String filename) {
File file = null;
FileInputStream fis = null;
Workbook workbook = null;
Sheet sheet = null;
Row row = null;
Cell cell = null;
int numSheets = 0;
Iterator<Row> rowIter = null;
Iterator<Cell> cellIter = null;
CellReference cellRef = null;
try {
// Open the workbook.
file = new File(filename);
fis = new FileInputStream(file);
workbook = WorkbookFactory.create(fis);
// Get the number of sheets in the workbook and enter a for loop
// to iterate through them one at a time.
numSheets = workbook.getNumberOfSheets();
for(int i = 0; i < numSheets; i++) {
// Get the sheet and recover from that an iterator that
// allows us to step through all of the rows the sheet
contains.
sheet = workbook.getSheetAt(i);
rowIter = sheet.rowIterator();
while(rowIter.hasNext()) {
// Get a row and recover from it an Iterator that allows
// us to access each of the cells on the row.
row = rowIter.next();
cellIter = row.cellIterator();
while(cellIter.hasNext()) {
// Get a cell
cell = cellIter.next();
// The CellReference object must be created for each
cell and used
// to convert POI's indices into Excel's co-ordinate
system.
cellRef = new CellReference(row.getRowNum(),
cell.getColumnIndex());
System.out.print("Cell " + cellRef.formatAsString()
+ " ");
switch(cell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
System.out.println("is blank.");
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.println("contains a boolean value
of: " +
cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
System.out.println("contains an error code
of: " +
cell.getErrorCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
System.out.println("contains a formula: " +
cell.getCellFormula());
break;
case Cell.CELL_TYPE_NUMERIC:
try {
if(DateUtil.isCellDateFormatted(cell)) {
System.out.println("contains a date:
" +
cell.getDateCellValue());
}
else {
System.out.println("contains a
number: " +
cell.getNumericCellValue());
}
}
catch(IndexOutOfBoundsException iobEx) {
System.out.println("an exception was
thrown " +
"checking the date formatting. "
+
"The cell contains the following
" +
"numeric value: " +
cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_STRING:
System.out.println("contains a String: " +
cell.getStringCellValue());
break;
}
}
}
}
}
catch(FileNotFoundException fnfEx) {
System.out.println("Caught: " + fnfEx.getClass().getName());
System.out.println("Message: " + fnfEx.getMessage());
System.out.println("Stacktrace follows..............");
fnfEx.printStackTrace(System.out);
}
catch(IOException ioEx) {
System.out.println("Caught: " + ioEx.getClass().getName());
System.out.println("Message: " + ioEx.getMessage());
System.out.println("Stacktrace follows..............");
ioEx.printStackTrace(System.out);
}
catch(InvalidFormatException invFEx) {
System.out.println("Caught: " + invFEx.getClass().getName());
System.out.println("Message: " + invFEx.getMessage());
System.out.println("Stacktrace follows..............");
invFEx.printStackTrace(System.out);
}
finally {
if(fis != null) {
try {
fis.close();
fis = null;
}
catch(IOException ioEx) {
// I G N O R E
}
}
}
}
Whilst this example uses Iterator to get at the cells, you are not forced to
do this, it is possible to get at a cell be specifying the index number of
the row and column. This would allow you to write some code that steps down
each column rather than along each row to retrieve a reference to a cell.
The changes to the code I have posted would be quite trivial and it might be
worth you time seeing if you can modify what I have written to use indices
rather than iterators. Simply write code that discovers how many rows there
are on the sheet then enter a for loo to step through each row at a time.
Within that loop, find out how many cells there are in the row and then
write another for loop to get each cell in turn and output it's contents to
standard output. That will help you to see how the API hangs together IMO.
As always, if you have any questions, just post to this list.
Yours
Mark B
Henrik-23 wrote:
>
> I have been trying to make a very simpe Java program, the only advanced
> part is the part using HSSF to read an xls file and get all the data.
> What i want to do i ittretare thru ever colum on every row and just
> print that data out in the comand prompt. I have found lots of example
> of this on the homepage but no whole code that realy works. They all
> seems to need something more to get them to run. It would be very much
> appriciated if someone just could point me in the right direction to get
> this working.
>
> Best regards Henrik
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
--
View this message in context: http://www.nabble.com/Basic-question-for-reading-xls-file-tp25743149p25753125.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org