List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getNumericCellValue
public double getNumericCellValue()
From source file:org.devgateway.eudevfin.importing.metadata.streamprocessors.ExcelStreamProcessor.java
License:Open Source License
@SuppressWarnings({ "rawtypes", "unchecked" })
private Object generateObject(final MapperInterface<?> mapper, final HSSFRow row)
throws ClassNotFoundException, InstantiationException, IllegalAccessException {
boolean allCellsAreNull = true;
final List<String> values = new ArrayList<String>();
for (int j = OFFSET; j < this.metadataInfoList.size() + OFFSET; j++) {
final HSSFCell cell = row.getCell(j);
if (cell != null) {
allCellsAreNull = false;/*from w w w . j av a 2 s .c o m*/
String val = null;
if (HSSFCell.CELL_TYPE_STRING == cell.getCellType()) {
val = cell.getStringCellValue();
} else if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
// HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
// val = dataFormatter.formatCellValue(cell);
val = new BigDecimal(cell.getNumericCellValue()).toString();
}
if (val != null && val.trim().length() > 0) {
values.add(val.trim());
} else {
values.add(null);
}
} else {
values.add(null);
}
}
if (allCellsAreNull) {
return null;
}
final Object result = mapper.createEntity(values);
return result;
}
From source file:org.egov.egf.web.actions.brs.AutoReconcileHelper.java
License:Open Source License
private String getStrValue(final HSSFCell cell) { if (cell == null) return null; double numericCellValue = 0d; String strValue = ""; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: numericCellValue = cell.getNumericCellValue(); final DecimalFormat decimalFormat = new DecimalFormat("#"); strValue = decimalFormat.format(numericCellValue); break;/*w w w .j a v a2 s . co m*/ case HSSFCell.CELL_TYPE_STRING: strValue = cell.getStringCellValue(); break; } return strValue; }
From source file:org.egov.egf.web.actions.brs.AutoReconcileHelper.java
License:Open Source License
private BigDecimal getNumericValue(final HSSFCell cell) { if (cell == null) return null; double numericCellValue = 0d; BigDecimal bigDecimalValue = BigDecimal.ZERO; String strValue = ""; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: numericCellValue = cell.getNumericCellValue(); bigDecimalValue = BigDecimal.valueOf(numericCellValue); break;// w w w. ja v a 2 s . co m case HSSFCell.CELL_TYPE_STRING: strValue = cell.getStringCellValue(); strValue = strValue.replaceAll("[^\\p{L}\\p{Nd}]", ""); if (strValue != null && strValue.contains("E+")) { final String[] split = strValue.split("E+"); String mantissa = split[0].replaceAll(".", ""); final int exp = Integer.parseInt(split[1]); while (mantissa.length() <= exp + 1) mantissa += "0"; numericCellValue = Double.parseDouble(mantissa); bigDecimalValue = BigDecimal.valueOf(numericCellValue); } else if (strValue != null && strValue.contains(",")) strValue = strValue.replaceAll(",", ""); // Ignore the error and continue Since in numric field we find empty or non numeric value try { numericCellValue = Double.parseDouble(strValue); bigDecimalValue = BigDecimal.valueOf(numericCellValue); } catch (final Exception e) { if (LOGGER.isDebugEnabled()) LOGGER.debug("Found : Non numeric value in Numeric Field :" + strValue + ":"); } break; } return bigDecimalValue; }
From source file:org.encuestame.business.search.IndexerFile.java
License:Apache License
/** * Extract spreadsheets content.//from w w w . j a v a 2s . com * @param workBook * @return * @throws Exception */ public static String extractContentSpreadsheetsDocument(final HSSFWorkbook workBook) throws Exception { StringBuilder contents = new StringBuilder(); for (int i = 0; i < workBook.getNumberOfSheets(); i++) { HSSFSheet sheet = workBook.getSheetAt(i); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); // Display the row number log.debug(row.getRowNum()); Iterator<Cell> cells = row.cellIterator(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); // Display the cell number of the current Row switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: { log.debug(String.valueOf(cell.getNumericCellValue())); contents.append(String.valueOf(cell.getNumericCellValue())).append(" "); break; } case HSSFCell.CELL_TYPE_STRING: { HSSFRichTextString richTextString = cell.getRichStringCellValue(); log.debug(richTextString.toString()); contents.append(richTextString.toString()).append(" "); break; } case HSSFCell.CELL_TYPE_BOOLEAN: { contents.append(String.valueOf(cell.getBooleanCellValue())).append(" "); break; } } } } } return contents.toString(); }
From source file:org.encuestame.business.search.SearchUtils.java
License:Apache License
/** * Create Spreadsheets Document.//w w w . ja va2 s. c o m * @param file Spreadsheet {@link File}. * @param Long attachmentId. * @return {@link Document} * @throws FileNotFoundException */ public static Document createSpreadsheetsDocument(final File file) throws Exception { InputStream is = new FileInputStream(file); StringBuilder contents = new StringBuilder(); POIFSFileSystem fileSystem = new POIFSFileSystem(is); HSSFWorkbook workBook = new HSSFWorkbook(fileSystem); for (int i = 0; i < workBook.getNumberOfSheets(); i++) { HSSFSheet sheet = workBook.getSheetAt(i); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); // Display the row number log.debug(row.getRowNum()); Iterator<Cell> cells = row.cellIterator(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); // Display the cell number of the current Row switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: { log.debug(String.valueOf(cell.getNumericCellValue())); contents.append(String.valueOf(cell.getNumericCellValue())).append(" "); break; } case HSSFCell.CELL_TYPE_STRING: { HSSFRichTextString richTextString = cell.getRichStringCellValue(); log.debug(richTextString.toString()); contents.append(richTextString.toString()).append(" "); break; } case HSSFCell.CELL_TYPE_BOOLEAN: { contents.append(String.valueOf(cell.getBooleanCellValue())).append(" "); break; } } } } } Document doc = SearchUtils.addFields(file, contents.toString()); return doc; }
From source file:org.exoplatform.addon.pulse.service.ws.RestActivitiesStatistic.java
License:Open Source License
private String buildCsvContent(HSSFWorkbook workbook) { HSSFSheet sheet = workbook.getSheetAt(0); StringBuffer buffer = new StringBuffer(); for (int i = 0; i <= sheet.getLastRowNum(); i++) { HSSFRow row = sheet.getRow(i);// w ww . java 2s . c o m for (int j = 0; j < row.getLastCellNum(); j++) { HSSFCell cell = row.getCell(j); int cellType = cell.getCellType(); if (cellType == HSSFCell.CELL_TYPE_STRING) { buffer.append(cell.getStringCellValue()); } else if (cellType == HSSFCell.CELL_TYPE_NUMERIC) { buffer.append(new DecimalFormat("#").format(cell.getNumericCellValue())); } if (j < row.getLastCellNum() - 1) { buffer.append(','); } } buffer.append('\n'); } return buffer.toString(); }
From source file:org.exoplatform.services.document.impl.MSExcelDocumentReader.java
License:Open Source License
/** * Returns only a text from .xls file content. * /* ww w . j a v a 2 s. c o m*/ * @param is an input stream with .xls file content. * @return The string only with text from file content. */ public String getContentAsText(InputStream is) throws IOException, DocumentReadException { if (is == null) { throw new IllegalArgumentException("InputStream is null."); } final StringBuilder builder = new StringBuilder(""); SimpleDateFormat dateFormat = new SimpleDateFormat(DATE_FORMAT); try { if (is.available() == 0) { return ""; } HSSFWorkbook wb; try { wb = new HSSFWorkbook(is); } catch (IOException e) { throw new DocumentReadException("Can't open spreadsheet.", e); } for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) { HSSFSheet sheet = wb.getSheetAt(sheetNum); if (sheet != null) { for (int rowNum = sheet.getFirstRowNum(); rowNum <= sheet.getLastRowNum(); rowNum++) { HSSFRow row = sheet.getRow(rowNum); if (row != null) { int lastcell = row.getLastCellNum(); for (int k = 0; k < lastcell; k++) { final HSSFCell cell = row.getCell((short) k); if (cell != null) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: { double d = cell.getNumericCellValue(); if (isCellDateFormatted(cell)) { Date date = HSSFDateUtil.getJavaDate(d); String cellText = dateFormat.format(date); builder.append(cellText).append(" "); } else { builder.append(d).append(" "); } break; } case HSSFCell.CELL_TYPE_FORMULA: SecurityHelper.doPrivilegedAction(new PrivilegedAction<Void>() { public Void run() { builder.append(cell.getCellFormula().toString()).append(" "); return null; } }); break; case HSSFCell.CELL_TYPE_BOOLEAN: SecurityHelper.doPrivilegedAction(new PrivilegedAction<Void>() { public Void run() { builder.append(cell.getBooleanCellValue()).append(" "); return null; } }); break; case HSSFCell.CELL_TYPE_ERROR: SecurityHelper.doPrivilegedAction(new PrivilegedAction<Void>() { public Void run() { builder.append(cell.getErrorCellValue()).append(" "); return null; } }); break; case HSSFCell.CELL_TYPE_STRING: SecurityHelper.doPrivilegedAction(new PrivilegedAction<Void>() { public Void run() { builder.append(cell.getStringCellValue().toString()).append(" "); return null; } }); break; default: break; } } } } } } } } finally { if (is != null) { try { is.close(); } catch (IOException e) { if (LOG.isTraceEnabled()) { LOG.trace("An exception occurred: " + e.getMessage()); } } } } return builder.toString(); }
From source file:org.exoplatform.services.document.impl.MSExcelDocumentReader.java
License:Open Source License
public static boolean isCellDateFormatted(HSSFCell cell) { boolean bDate = false; double d = cell.getNumericCellValue(); if (HSSFDateUtil.isValidExcelDate(d)) { HSSFCellStyle style = cell.getCellStyle(); int i = style.getDataFormat(); switch (i) { case 0xe: // m/d/yy case 0xf: // d-mmm-yy case 0x10: // d-mmm case 0x11: // mmm-yy case 0x12: // h:mm AM/PM case 0x13: // h:mm:ss AM/PM case 0x14: // h:mm case 0x15: // h:mm:ss case 0x16: // m/d/yy h:mm case 0x2d: // mm:ss case 0x2e: // [h]:mm:ss case 0x2f: // mm:ss.0 case 0xa5: // ?? case 0xa7: // ?? case 0xa9: // ?? case 0xac: // mm:dd:yy not specified in javadoc case 0xad: // yyyy-mm-dd not specified in javadoc case 0xae: // mm:dd:yyyy not specified in javadoc case 0xaf: // m:d:yy not specified in javadoc bDate = true;// w w w . j a v a2s.c o m break; default: bDate = false; break; } } return bDate; }
From source file:org.gageot.excel.core.ObjectCellMapper.java
License:Apache License
@Override public Object mapCell(HSSFCell cell, int rowNum, int columnNum) throws IOException { try {//from w w w. jav a 2 s . c om return cell.getNumericCellValue(); } catch (NumberFormatException e) { return cell.getStringCellValue(); } catch (IllegalStateException e) { return cell.getStringCellValue(); } }
From source file:org.gageot.excel.core.StringCellMapper.java
License:Apache License
private String numericToString(HSSFCell cell) { double numericValue = cell.getNumericCellValue(); if (Double.isNaN(numericValue)) { return ""; }//from ww w .j ava 2 s .c om if (isDateFormat(cell)) { if (null == dateFormat) { dateFormat = new SimpleDateFormat("dd/MM/yyyy"); } return dateFormat.format(cell.getDateCellValue()); } // For text cells, Excel still tries to converts the content into // numerical value. For integer content, we want to convert // into a String value without fraction. // if (isTextFormat(cell) && (((long) numericValue) == numericValue)) { return Long.toString((long) numericValue); } return Double.toString(numericValue); }