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