List of usage examples for org.apache.poi.ss.usermodel DateUtil isCellDateFormatted
public static boolean isCellDateFormatted(Cell cell)
From source file:org.netxilia.impexp.impl.ExcelImportService.java
License:Open Source License
private ICellCommand copyCell(Cell poiCell, CellReference cellReference, HSSFPalette palette, NetxiliaStyleResolver styleResolver) throws FormulaParsingException { CellStyle poiStyle = poiCell.getCellStyle(); Styles styles = PoiUtils.poiStyle2Netxilia(poiStyle, poiCell.getSheet().getWorkbook().getFontAt(poiStyle.getFontIndex()), palette, styleResolver); IGenericValue value = null;//from ww w . j a v a 2s. co m Formula formula = null; // log.info("CELL TYPE:" + cellReference + " type:" + poiCell.getCellType() + " " // + (poiCell.getCellType() == Cell.CELL_TYPE_FORMULA ? poiCell.getCellFormula() : "no")); switch (poiCell.getCellType()) { // TODO translate errors case Cell.CELL_TYPE_STRING: value = new StringValue(poiCell.getStringCellValue()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(poiCell)) { DateTime dt = new DateTime(poiCell.getDateCellValue()); // TODO decide whether is date or time if (dt.isBefore(EXCEL_START)) { value = new DateValue(dt.toLocalTime()); } else if (dt.getMillisOfDay() == 0) { value = new DateValue(dt.toLocalDate()); } else { value = new DateValue(dt.toLocalDateTime()); } } else { value = new NumberValue(poiCell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: value = new BooleanValue(poiCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: if (poiCell.getCellFormula() != null) { formula = formulaParser.parseFormula(new Formula("=" + poiCell.getCellFormula())); } break; } if ((styles == null || styles.getItems().isEmpty()) && formula == null && (value == null || value.equals(GenericValueUtils.EMTPY_STRING))) { return null; } return CellCommands.cell(new AreaReference(cellReference), value, formula, styles); }
From source file:org.nuclos.server.common.ooxml.ExcelReader.java
License:Open Source License
private static Object getCellValue(Cell cell, int cellType) { switch (cellType) { case Cell.CELL_TYPE_BLANK: return null; case Cell.CELL_TYPE_BOOLEAN: return cell.getBooleanCellValue(); case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { return cell.getDateCellValue(); } else {/*from w ww . j a va2 s . c o m*/ return cell.getNumericCellValue(); } case Cell.CELL_TYPE_STRING: return cell.getStringCellValue(); case Cell.CELL_TYPE_ERROR: return FormulaError.forInt(cell.getErrorCellValue()).getString(); case Cell.CELL_TYPE_FORMULA: return cell.getCellFormula(); default: throw new IllegalArgumentException("Unknown POI cell type " + cellType); } }
From source file:org.opencities.berlin.uploaddata.service.Worker.java
/** * loop through all Cells and rows. Firstly, add correct keys to strings. * Secondly, parse corresponding value into correct json and add this * dataset to ckan via middleware.//from w ww. ja v a2s . c o m * * @param args * @throws Exception */ @SuppressWarnings("rawtypes") public String readXlsx() { String errormessage = ""; CKANGateway gw = new CKANGateway(ckan, key); HashMap<String, String> map = new HashMap<String, String>(); ArrayList<String> strings = new ArrayList<String>(); XSSFWorkbook workBook = null; try { workBook = new XSSFWorkbook(uploadFolder + "file.xlsx"); } catch (IOException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } int counter = 0; XSSFSheet sheet = workBook.getSheetAt(0); for (Row row : sheet) { for (Cell cell : row) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: String value = cell.getRichStringCellValue().getString(); // first row, add value to strings if (counter == 0) { if (!value.startsWith("resources:") && !value.startsWith("extras:")) map.put(value, null); strings.add(value); break; } if (strings.get(cell.getColumnIndex()).equalsIgnoreCase("tags") || strings.get(cell.getColumnIndex()).equalsIgnoreCase("groups")) { String[] tmp = value.split(","); String out = buildString(tmp); map.put(strings.get(cell.getColumnIndex()), out); } else if (strings.get(cell.getColumnIndex()).startsWith("resources:")) { String[] tmp = strings.get(cell.getColumnIndex()).split(":"); parseResource(tmp[1], value); } else if (strings.get(cell.getColumnIndex()).startsWith("extras:")) { String[] tmp = strings.get(cell.getColumnIndex()).split(":"); parseExtras(tmp[1], value); } else { map.put(strings.get(cell.getColumnIndex()), "\"" + value + "\""); } break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { // is a date; map = handleDate(map, strings, cell); } else { // is a number; map = handleNumber(map, strings, cell); } break; default: break; } } // finish extras and resources finishParseResource(); finishParseExtras(); map = checkEmptyValues(map); map = toLowerCase(map); // add resources and extras to map map.put("resources", resourceString); map.put("extras", extrasString); if (counter >= 1) { // add dataset to CKAN via middleware HashMap<String, HashMap> out = gw.createMetaDataSet(map); if (out == null) errormessage += String.valueOf(counter) + ","; } ++counter; resourceString = resetResourceString(); extrasString = resetExtrasString(); } if (errormessage.equalsIgnoreCase("")) return errormessage; else return errormessage.substring(0, errormessage.length() - 1); }
From source file:org.openelis.bean.QcChartReport1Bean.java
License:Open Source License
/** * Keeps track of the maximum number of characters in each column of the * spreadsheet; if "cell" has more characters than the number in "maxChars" * for the cell's column, the number in "maxChars" is updated * /*from w w w.j a v a 2 s . co m*/ * @param cell * a cell in a row in the spreadsheet * @param maxChars * the list containing the maximum number of characters in each * column of the spreadsheet */ private void setMaxChars(Cell cell, ArrayList<Integer> maxChars) { int col, chars; String val; col = cell.getColumnIndex(); while (col > maxChars.size() - 1) maxChars.add(0); if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { if (DateUtil.isCellDateFormatted(cell)) val = ReportUtil.toString(cell.getDateCellValue(), Messages.get().dateTimePattern()); else val = Double.toString(cell.getNumericCellValue()); } else { val = cell.getStringCellValue(); } chars = !DataBaseUtil.isEmpty(val) ? val.length() : 0; maxChars.set(col, Math.max(chars, maxChars.get(col))); }
From source file:org.openelis.bean.WorksheetExcelHelperBean.java
License:Open Source License
private Object getCellValue(Cell cell) { Object value;//from w w w.j a v a2 s. c o m FormulaEvaluator eval; value = null; if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_FORMULA: eval = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator(); switch (eval.evaluateFormulaCell(cell)) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { value = new Datetime(Datetime.YEAR, Datetime.MINUTE, cell.getDateCellValue()); } else { cell.setCellType(Cell.CELL_TYPE_STRING); value = cell.getStringCellValue(); if (((String) value).trim().length() == 0) value = null; } break; case Cell.CELL_TYPE_STRING: value = cell.getStringCellValue(); if (((String) value).trim().length() == 0) value = null; break; } break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { value = new Datetime(Datetime.YEAR, Datetime.MINUTE, cell.getDateCellValue()); } else { cell.setCellType(Cell.CELL_TYPE_STRING); value = cell.getStringCellValue(); if (((String) value).trim().length() == 0) value = null; } break; case Cell.CELL_TYPE_STRING: value = cell.getStringCellValue(); if (((String) value).trim().length() == 0) value = null; break; } } return value; }
From source file:org.openelis.bean.WorksheetExcelHelperBean.java
License:Open Source License
@TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED) private void setCellValue(Cell cell, String value) { Date tempDate;/* w ww . j a v a2 s . co m*/ try { if (DateUtil.isCellDateFormatted(cell)) { tempDate = new SimpleDateFormat("yyyy-MM-dd HH:mm").parse(value, new ParsePosition(1)); if (tempDate != null) { cell.setCellValue(tempDate); return; } } cell.setCellValue(Double.parseDouble(value)); return; } catch (Exception ignE) { // ignore exceptions from trying to parse special values } cell.setCellValue(value); }
From source file:org.openmrs.module.spreadsheetimport.SpreadsheetImportUtil.java
License:Open Source License
public static File importTemplate(SpreadsheetImportTemplate template, MultipartFile file, String sheetName, List<String> messages, boolean rollbackTransaction) throws Exception { if (file.isEmpty()) { messages.add("file must not be empty"); return null; }//from w ww . ja v a 2 s. c o m // Open file Workbook wb = WorkbookFactory.create(file.getInputStream()); Sheet sheet; if (!StringUtils.hasText(sheetName)) { sheet = wb.getSheetAt(0); } else { sheet = wb.getSheet(sheetName); } // Header row Row firstRow = sheet.getRow(0); if (firstRow == null) { messages.add("Spreadsheet header row must not be null"); return null; } List<String> columnNames = new Vector<String>(); for (Cell cell : firstRow) { columnNames.add(cell.getStringCellValue()); } if (log.isDebugEnabled()) { log.debug("Column names: " + columnNames.toString()); } // Required column names List<String> columnNamesOnlyInTemplate = new Vector<String>(); columnNamesOnlyInTemplate.addAll(template.getColumnNamesAsList()); columnNamesOnlyInTemplate.removeAll(columnNames); if (columnNamesOnlyInTemplate.isEmpty() == false) { messages.add("required column names not present: " + toString(columnNamesOnlyInTemplate)); return null; } // Extra column names? List<String> columnNamesOnlyInSheet = new Vector<String>(); columnNamesOnlyInSheet.addAll(columnNames); columnNamesOnlyInSheet.removeAll(template.getColumnNamesAsList()); if (columnNamesOnlyInSheet.isEmpty() == false) { messages.add( "Extra column names present, these will not be processed: " + toString(columnNamesOnlyInSheet)); } // Process rows boolean skipThisRow = true; for (Row row : sheet) { if (skipThisRow == true) { skipThisRow = false; } else { boolean rowHasData = false; Map<UniqueImport, Set<SpreadsheetImportTemplateColumn>> rowData = template .getMapOfUniqueImportToColumnSetSortedByImportIdx(); for (UniqueImport uniqueImport : rowData.keySet()) { Set<SpreadsheetImportTemplateColumn> columnSet = rowData.get(uniqueImport); for (SpreadsheetImportTemplateColumn column : columnSet) { int idx = columnNames.indexOf(column.getName()); Cell cell = row.getCell(idx); Object value = null; // check for empty cell (new Encounter) if (cell == null) { rowHasData = true; column.setValue(""); continue; } switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: value = new Boolean(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: value = new Byte(cell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { java.util.Date date = cell.getDateCellValue(); value = "'" + new java.sql.Timestamp(date.getTime()).toString() + "'"; } else { value = cell.getNumericCellValue(); } break; case Cell.CELL_TYPE_STRING: // Escape for SQL value = "'" + cell.getRichStringCellValue() + "'"; break; } if (value != null) { rowHasData = true; column.setValue(value); } else column.setValue(""); } } for (UniqueImport uniqueImport : rowData.keySet()) { Set<SpreadsheetImportTemplateColumn> columnSet = rowData.get(uniqueImport); boolean isFirst = true; for (SpreadsheetImportTemplateColumn column : columnSet) { if (isFirst) { // Should be same for all columns in unique import // System.out.println("SpreadsheetImportUtil.importTemplate: column.getColumnPrespecifiedValues(): " + column.getColumnPrespecifiedValues().size()); if (column.getColumnPrespecifiedValues().size() > 0) { Set<SpreadsheetImportTemplateColumnPrespecifiedValue> columnPrespecifiedValueSet = column .getColumnPrespecifiedValues(); for (SpreadsheetImportTemplateColumnPrespecifiedValue columnPrespecifiedValue : columnPrespecifiedValueSet) { // System.out.println(columnPrespecifiedValue.getPrespecifiedValue().getValue()); } } } } } if (rowHasData) { Exception exception = null; try { DatabaseBackend.validateData(rowData); String encounterId = DatabaseBackend.importData(rowData, rollbackTransaction); if (encounterId != null) { for (UniqueImport uniqueImport : rowData.keySet()) { Set<SpreadsheetImportTemplateColumn> columnSet = rowData.get(uniqueImport); for (SpreadsheetImportTemplateColumn column : columnSet) { if ("encounter".equals(column.getTableName())) { int idx = columnNames.indexOf(column.getName()); Cell cell = row.getCell(idx); if (cell == null) cell = row.createCell(idx); cell.setCellValue(encounterId); } } } } } catch (SpreadsheetImportTemplateValidationException e) { messages.add("Validation failed: " + e.getMessage()); return null; } catch (SpreadsheetImportDuplicateValueException e) { messages.add("found duplicate value for column " + e.getColumn().getName() + " with value " + e.getColumn().getValue()); return null; } catch (SpreadsheetImportSQLSyntaxException e) { messages.add("SQL syntax error: \"" + e.getSqlErrorMessage() + "\".<br/>Attempted SQL Statement: \"" + e.getSqlStatement() + "\""); return null; } catch (Exception e) { exception = e; } if (exception != null) { throw exception; } } } } // write back Excel file to a temp location File returnFile = File.createTempFile("sim", ".xls"); FileOutputStream fos = new FileOutputStream(returnFile); wb.write(fos); fos.close(); return returnFile; }
From source file:org.openstreetmap.josm.plugins.opendata.core.io.tabular.XlsReader.java
License:GNU General Public License
@Override protected String[] readLine(ProgressMonitor progressMonitor) throws IOException { if (sheet != null) { Row row = sheet.getRow(rowIndex++); if (row != null) { List<String> result = new ArrayList<>(); for (Cell cell : row) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: result.add(cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { result.add(cell.getDateCellValue().toString()); } else { result.add(Double.toString(cell.getNumericCellValue())); }/*from www . j av a2 s . c o m*/ break; case Cell.CELL_TYPE_BOOLEAN: result.add(Boolean.toString(cell.getBooleanCellValue())); break; case Cell.CELL_TYPE_FORMULA: result.add(cell.getCellFormula()); break; default: result.add(""); } } return result.toArray(new String[0]); } } return null; }
From source file:org.projectforge.excel.ExportCell.java
License:Open Source License
public Object getCellValue() { if (poiCell == null) { return null; }//from w w w .jav a 2 s . c o m switch (poiCell.getCellType()) { case Cell.CELL_TYPE_STRING: return poiCell.getRichStringCellValue().getString(); case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(poiCell)) { return poiCell.getDateCellValue(); } return poiCell.getNumericCellValue(); case Cell.CELL_TYPE_BOOLEAN: return poiCell.getBooleanCellValue(); case Cell.CELL_TYPE_FORMULA: return poiCell.getCellFormula(); default: return null; } }
From source file:org.shareok.data.documentProcessor.ExcelHandler.java
/** * Check if the cells are date type/*w w w . ja v a 2 s. c o m*/ * * @param cell * @return : bool * @throws Exception */ private boolean isCellDateFormatted(Cell cell) throws Exception { try { return DateUtil.isCellDateFormatted(cell); } catch (Exception ex) { ex.printStackTrace(); throw new Exception("The cell type data formatted cannot be decided!"); } }