List of usage examples for org.apache.poi.ss.usermodel DataFormatter DataFormatter
public DataFormatter(Locale locale)
From source file:org.deri.tarql.XLSToValues.java
License:Apache License
public TableData read() { try {// w w w . j a v a2 s.co m List<Binding> bindings = new ArrayList<Binding>(); // Read workbook into HSSFWorkbook HSSFWorkbook workbook = new HSSFWorkbook(this.is); HSSFSheet sheet = workbook.getSheetAt(this.sheet); this.evaluator = workbook.getCreationHelper().createFormulaEvaluator(); this.formatter = new DataFormatter(true); // To iterate over the rows Iterator<Row> rowIterator = sheet.iterator(); String[] row; try { if (varsFromHeader) { while (rowIterator.hasNext()) { row = getRow(rowIterator.next()); boolean foundValidColumnName = false; for (int i = 0; i < row.length; i++) { if (toVar(row[i]) == null) continue; foundValidColumnName = true; } // If row was empty or didn't contain anything usable // as column name, then try next row if (!foundValidColumnName) continue; for (int i = 0; i < row.length; i++) { Var var = toVar(row[i]); if (var == null || vars.contains(var) || var.equals(TarqlQuery.ROWNUM)) { getVar(i); } else { vars.add(var); } } break; } } rownum = 1; while (rowIterator.hasNext()) { row = getRow(rowIterator.next()); // Skip rows without data if (isEmpty(row)) continue; bindings.add(toBinding(row)); rownum++; } vars.add(TarqlQuery.ROWNUM); //Make sure variables exists for all columns even if no data is available, otherwise ARQ will complain. for (int i = 0; i < vars.size(); i++) { if (vars.get(i) == null) { getVar(i); } } return new TableData(vars, bindings); } finally { this.is.close(); } } catch (IOException ex) { throw new JenaException(ex); } }
From source file:org.drools.decisiontable.parser.xls.ExcelParser.java
License:Apache License
private void processSheet(Sheet sheet, List<? extends DataListener> listeners) { int maxRows = sheet.getLastRowNum(); CellRangeAddress[] mergedRanges = getMergedCells(sheet); DataFormatter formatter = new DataFormatter(Locale.ENGLISH); FormulaEvaluator formulaEvaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator(); for (int i = 0; i <= maxRows; i++) { Row row = sheet.getRow(i);//from w w w .j ava2 s. c o m int lastCellNum = row != null ? row.getLastCellNum() : 0; newRow(listeners, i, lastCellNum); for (int cellNum = 0; cellNum < lastCellNum; cellNum++) { Cell cell = row.getCell(cellNum); if (cell == null) { continue; } double num = 0; CellRangeAddress merged = getRangeIfMerged(cell, mergedRanges); if (merged != null) { Cell topLeft = sheet.getRow(merged.getFirstRow()).getCell(merged.getFirstColumn()); newCell(listeners, i, cellNum, formatter.formatCellValue(topLeft), topLeft.getColumnIndex()); } else { switch (cell.getCellType()) { case Cell.CELL_TYPE_FORMULA: String cellValue = null; try { CellValue cv = formulaEvaluator.evaluate(cell); cellValue = getCellValue(cv); newCell(listeners, i, cellNum, cellValue, DataListener.NON_MERGED); } catch (RuntimeException e) { // This is thrown if an external link cannot be resolved, so try the cached value log.warn("Cannot resolve externally linked value: " + formatter.formatCellValue(cell)); String cachedValue = tryToReadCachedValue(cell); newCell(listeners, i, cellNum, cachedValue, DataListener.NON_MERGED); } break; case Cell.CELL_TYPE_NUMERIC: num = cell.getNumericCellValue(); default: if (num - Math.round(num) != 0) { newCell(listeners, i, cellNum, String.valueOf(num), DataListener.NON_MERGED); } else { newCell(listeners, i, cellNum, formatter.formatCellValue(cell), DataListener.NON_MERGED); } } } } } finishSheet(listeners); }
From source file:org.drools.decisiontable.parser.xls.ExcelParser.java
License:Apache License
private String tryToReadCachedValue(Cell cell) { DataFormatter formatter = new DataFormatter(Locale.ENGLISH); String cachedValue;/*from ww w . j a va 2 s. c o m*/ switch (cell.getCachedFormulaResultType()) { case Cell.CELL_TYPE_NUMERIC: double num = cell.getNumericCellValue(); if (num - Math.round(num) != 0) { cachedValue = String.valueOf(num); } else { cachedValue = formatter.formatCellValue(cell); } break; case Cell.CELL_TYPE_STRING: cachedValue = cell.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: cachedValue = String.valueOf(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: cachedValue = String.valueOf(cell.getErrorCellValue()); break; default: throw new DecisionTableParseException( format("Can't read cached value for cell[row=%d, col=%d, value=%s]!", cell.getRowIndex(), cell.getColumnIndex(), cell)); } return cachedValue; }
From source file:org.meveo.commons.utils.ExcelToCsv.java
License:Apache License
/** * Open an Excel workbook ready for conversion. * * @param file An instance of the File class that encapsulates a handle * to a valid Excel workbook. Note that the workbook can be in * either binary (.xls) or SpreadsheetML (.xlsx) format. * @throws java.io.FileNotFoundException Thrown if the file cannot be located. * @throws java.io.IOException Thrown if a problem occurs in the file system. * @throws org.apache.poi.openxml4j.exceptions.InvalidFormatException Thrown * if invalid xml is found whilst parsing an input SpreadsheetML * file./*from w w w . jav a 2 s . c o m*/ */ private void openWorkbook(File file) throws FileNotFoundException, IOException, InvalidFormatException { FileInputStream fis = null; try { log.debug("Opening workbook [" + file.getName() + "]"); fis = new FileInputStream(file); // Open the workbook and then create the FormulaEvaluator and // DataFormatter instances that will be needed to, respectively, // force evaluation of forumlae found in cells and create a // formatted String encapsulating the cells contents. this.workbook = WorkbookFactory.create(fis); this.evaluator = this.workbook.getCreationHelper().createFormulaEvaluator(); this.formatter = new DataFormatter(true); } finally { if (fis != null) { fis.close(); } } }
From source file:org.tiefaces.components.websheet.TieWebSheetBean.java
License:MIT License
/** * get data formatter.// w w w.ja v a 2 s . co m * * @return dataformatter. */ public DataFormatter getDataFormatter() { if (this.dataFormatter == null) { this.dataFormatter = new DataFormatter(this.getDefaultLocale()); } return dataFormatter; }
From source file:util.ToCSV.java
License:Apache License
private void openWorkbook(InputStream stream) throws IOException, InvalidFormatException { try {//from w w w .j a v a2 s. c o m this.workbook = WorkbookFactory.create(stream); this.evaluator = this.workbook.getCreationHelper().createFormulaEvaluator(); this.formatter = new DataFormatter(true); } finally { if (stream != null) { stream.close(); } } }