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