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 23

POI supports the older OLE2 format Excel (XLS) files using HSSF, and using XSSF can also handle the newer OOXML format (XLSX) used by Microsoft Office 2007, 2008 and onwards.

If you want to open an Excel file in either format then you can use something like the following:

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

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

Nov 22

The Apache POI project helps you read and write Microsoft OLE2 and Office Open XML (OOXML) documents.

OLE2 includes Excel (XLS), Word (DOC) and Powerpoint (PPT) files.

Office Open XML Format is the new standards based XML file format found in Microsoft Office 2007 and 2008. This includes XLSX, DOCX and PPTX files.

Use POIFS to read OLE2 files

HSSF is used to read and write Excel (XLS) files.

XSSF is used to access OOXML Excel files (XLSX).

The combined SS interface (SS = HSSF + XSSF) is designed to allow you to access all Excel files (XLS and XLSX).

We shall be posting some POI examples in the next few weeks, let us know if there is anything specific you need.

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