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: [email protected]
> For additional commands, e-mail: [email protected]
>
>
>
--
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: [email protected]
For additional commands, e-mail: [email protected]
