Created
June 6, 2012 08:11
-
-
Save canthony/2880586 to your computer and use it in GitHub Desktop.
Reading an excel spreadsheet and creating a Vaadin container : Not complete
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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