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: , , , , , , , , , ,

Dec 01

If you just need a quick and dirty method to extract the text from an Excel spreadsheet then you can use the ExcelExtractor class included in POI. The following example snippet shows its usage:

FileInputStream in = new FileInputStream(file);
HSSFWorkbook workbook = new HSSFWorkbook(in) :
ExcelExtractor extractor = new ExcelExtractor(workbook);
String text = extractor.getText();

written by objects \\ tags: , , , ,

Nov 24

Once you have opened your Excel file you are probably going to want to access sheets within that file.

The Workbook class contains methods to access sheets by index or by name as shown in the following examples.

int nsheets = workbook.getNumberOfSheets();
for (int i=0; i<nsheets; i++) {
    Sheet sheet = workbook.getSheetAt(i);
				
    // Now you can access the sheet
}

And to access a sheet by name.

Sheet sheet = workbook.getSheet(sheetName);

Stay tuned as we continue to investigate using POI.

written by objects \\ tags: , , ,