Dec 01

The following just puts together everything we’ve covered on POI so far.

The following is a simple utility method to read an Excel file (XLS or XLSX) and write the reference and contents of each cell to a writer of your choice. So you could use it to dump stdout, or a file.

public static void dump(File excel, PrintWriter out) throws IOException, InvalidFormatException {
	FileInputStream in = new FileInputStream(excel);
	Workbook workbook = WorkbookFactory.create(in);

	int nsheets = workbook.getNumberOfSheets();
	for (int i = 0; i < nsheets; i++) {
		Sheet sheet = workbook.getSheetAt(i);
		for (Row row : sheet) {
			for (Cell cell : row) {
				String cellValue = null;

				switch (cell.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						cellValue = cell.getStringCellValue();
						break;

					case Cell.CELL_TYPE_FORMULA:
						cellValue = cell.getCellFormula();
						break;

					case Cell.CELL_TYPE_NUMERIC:
						if (DateUtil.isCellDateFormatted(cell)) {
							cellValue = 
								cell.getDateCellValue().toString();
						} else {
							cellValue = Double.toString(
								cell.getNumericCellValue());
						}
						break;

					case Cell.CELL_TYPE_BLANK:
						cellValue = "";
						break;

					case Cell.CELL_TYPE_BOOLEAN:
						cellValue = Boolean
								.toString(cell.getBooleanCellValue());
						break;
				}
				CellReference cellReference = new CellReference(
						row.getRowNum(), cell.getColumnIndex());
				out.println(cellReference.formatAsString()+"="+cellValue);
			}
		}
	}
	in.close();
}

written by objects \\ tags: , , , , , , , , , ,

Nov 30

Once you have found the cell you want in your Excel spreadsheet you are going to want to get the value it contains.

The simplest method is to just use the Cell.toString() method which will return a string representation of the cell.

If you need to know a bit more detail, like does it contain a string, or a formula, or perhaps a number then you need to do a bit more work.

The Cell.getCellType() method will first tell you what type of value the cell contains. With that information you can now call the appropriate method to get the value. The following snippet shows some typical usage:


String cellValue = null;

switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
	cellValue = cell.getStringCellValue();
	break;
	
case Cell.CELL_TYPE_FORMULA:
	cellValue = cell.getCellFormula();
	break;

case Cell.CELL_TYPE_NUMERIC:
	if (DateUtil.isCellDateFormatted(cell)) {
		cellValue = cell.getDateCellValue().toString();
	} else {
		cellValue = Double.toString(cell.getNumericCellValue());
	}
	break;

case Cell.CELL_TYPE_BLANK:
	cellValue = "";
	break;
	
case Cell.CELL_TYPE_BOOLEAN:
	cellValue = Boolean.toString(cell.getBooleanCellValue());
	break;

}

written by objects \\ tags: , , , ,

Nov 25

In previous posts we have covered opening Excel files, and accessing sheets. Now we need to move onto reading the rows and cells in that sheet.

FileInputStream in = new FileInputStream(file);
Workbook workbook = WorkbookFactory.create(in);

int nsheets = workbook.getNumberOfSheets();
for (int i=0; i<nsheets; i++) {
	Sheet sheet = workbook.getSheetAt(i);
	for (Row row : sheet) {
		for (Cell cell : row) {
			
			// Do what you want with the cell value
		}
	}
}

written by objects \\ tags: , , , , ,