web development helpdesk

Displaytag is a great tag library for rendering data as a table. As well as handling the generation of the html table, it also provides support for easily exporting the table to a variety of formats including Excel (and CSV).

One problem that can occur is Excel formatting the table cells. When it sees a value that looks like a number then it attempts to format it as a number. This can result in values being displayed in Excel in scientific notation when you really want the data to be displayed as is.

To fix this we can use a displaytag column decorator that quotes the values when exporting to Excel. That way excel will see the quoted value and no longer think it as a number and display it without formatting as you require.

import javax.servlet.jsp.PageContext;
import org.displaytag.decorator.DisplaytagColumnDecorator;
import org.displaytag.properties.MediaTypeEnum;

public class QuotedExportDecorator implements DisplaytagColumnDecorator {
	
	@Override
    public Object decorate(Object value, PageContext pageContext, MediaTypeEnum media) {
        if (media.equals(MediaTypeEnum.EXCEL) || media.equals(MediaTypeEnum.CSV)) {
            value = "=\"" + value + "\"";
        }
        return value;
    }
}

written by objects \\ tags: csv, displaytag, Excel, export, table

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: Cell, CellReference, Excel, HSSF, HSSFWorkbook, POI, row, Workbook, WorkbookFactory, XSSF, XSSFWorkbook

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: Excel, ExcelExtractor, HSSFWorkbook, POI, Workbook