Skip to content

Instantly share code, notes, and snippets.

@canthony
Created June 6, 2012 08:11
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save canthony/2880586 to your computer and use it in GitHub Desktop.
Save canthony/2880586 to your computer and use it in GitHub Desktop.
Reading an excel spreadsheet and creating a Vaadin container : Not complete
public class CreateContainer {
public static void main(String[] args) throws IOException {
FileInputStream in = new FileInputStream("C:/temp/SampleData.xls");
POIFSFileSystem fileSystem = new POIFSFileSystem(in);
HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
HSSFSheet sheet = workBook.getSheetAt(0);
List<String> propertyNames = Collections.emptyList();
IndexedContainer c = new IndexedContainer();
for (Row row : sheet) {
int rowIndex = row.getRowNum();
if (rowIndex == 0) {
propertyNames = readHeaderRow(row);
} else {
PropertysetItem item = createItemFromRow(propertyNames, row);
}
}
}
private static PropertysetItem createItemFromRow(List<String> propertyNames, Row row) {
CreationHelper creationHelper = row.getSheet().getWorkbook().getCreationHelper();
FormulaEvaluator formulaEvaluator = creationHelper.createFormulaEvaluator();
DataFormatter df = new DataFormatter(false);
PropertysetItem item = new PropertysetItem();
for (Cell cell : row) {
String cellValueAsString = df.formatCellValue(cell, formulaEvaluator);
String propertyName = propertyNames.get(cell.getColumnIndex());
DecimalFormat nf = new DecimalFormat();
int cellType = cell.getCellType();
// If it's a formula, let's deal with the cached result
if (cellType == Cell.CELL_TYPE_FORMULA) {
cellType = cell.getCachedFormulaResultType();
}
switch (cellType) {
case Cell.CELL_TYPE_STRING:
item.addItemProperty(propertyName, new ObjectProperty<String>(cellValueAsString, String.class));
break;
case Cell.CELL_TYPE_BOOLEAN:
item.addItemProperty(propertyName, new ObjectProperty<Boolean>(cell.getBooleanCellValue(), Boolean.class));
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
item.addItemProperty(propertyName, new ObjectProperty<Date>(cell.getDateCellValue(), Date.class));
} else {
if (cellValueAsString.startsWith("*")) {
cellValueAsString = cellValueAsString.substring(1);
}
cellValueAsString = cellValueAsString.trim();
try {
Number n = nf.parse(cellValueAsString);
item.addItemProperty(propertyName, new ObjectProperty(n, n.getClass()));
} catch (ParseException e) {
throw new RuntimeException(e);
}
}
}
}
return item;
}
private static List<String> readHeaderRow(Row row) {
List<String> propertyNames = new ArrayList<String>(row.getLastCellNum());
for (Cell cell : row) {
int columnIndex = cell.getColumnIndex();
propertyNames.add(columnIndex, cell.getStringCellValue());
}
return propertyNames;
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment