List of usage examples for org.apache.poi.xssf.usermodel XSSFCell getNumericCellValue
@Override public double getNumericCellValue()
From source file:no.sintef.ict.splcatool.XLSXLib.java
License:Open Source License
public static String getCSV(File file) throws IOException { String filename = file.getAbsoluteFile().toString(); FileInputStream fis = new FileInputStream(filename); XSSFWorkbook workbook = new XSSFWorkbook(fis); XSSFSheet sheet = workbook.getSheetAt(0); //System.out.println(" number of rows"+ sheet.getLastRowNum()); String csv = ""; int w = Integer.MAX_VALUE; outerloop: for (Row r : sheet) { int x = 0; XSSFRow row = (XSSFRow) r;/*from w w w.j a va 2s.c o m*/ for (Cell c : row) { if (x >= w) break; XSSFCell cell = (XSSFCell) c; String v = ""; if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) { v = cell.getStringCellValue(); } else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { v = "" + (int) cell.getNumericCellValue(); } else { System.out.println("Unknown type " + cell.getCellType() + " " + cell.getRawValue()); System.exit(-1); } //System.out.println(v + ", " + x + ", " + w); if (x == 0 && v.equals("#end")) break outerloop; if (v.equals("#end")) { w = x; break; } csv += v + ";"; x++; } csv += "\n"; } //csv = csv.substring(0, csv.length()-1); //System.out.println(csv); return csv; }
From source file:org.addition.epanet.network.io.input.ExcelParser.java
License:Open Source License
private String convertCell(XSSFCell cell) throws ENException { if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { if (timeStyles.contains(cell.getCellStyle())) return Utilities.getClockTime(Math.round(cell.getNumericCellValue() * 86400)); return Double.toString(cell.getNumericCellValue()); } else if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) return cell.getStringCellValue(); else/*from w ww .j a v a2s . c o m*/ throw new ENException(201); }
From source file:org.apache.ofbiz.pricat.AbstractPricatParser.java
License:Apache License
private void copyRow(XSSFRow sourceRow, XSSFRow targetRow, XSSFCreationHelper factory, XSSFDrawing patriarch) { for (int j = 0; j < sourceRow.getPhysicalNumberOfCells(); j++) { XSSFCell cell = sourceRow.getCell(j); if (cell != null) { XSSFCell newCell = targetRow.createCell(j); int cellType = cell.getCellType(); newCell.setCellType(cellType); switch (cellType) { case XSSFCell.CELL_TYPE_BOOLEAN: newCell.setCellValue(cell.getBooleanCellValue()); break; case XSSFCell.CELL_TYPE_ERROR: newCell.setCellErrorValue(cell.getErrorCellValue()); break; case XSSFCell.CELL_TYPE_FORMULA: newCell.setCellFormula(cell.getCellFormula()); break; case XSSFCell.CELL_TYPE_NUMERIC: newCell.setCellValue(cell.getNumericCellValue()); break; case XSSFCell.CELL_TYPE_STRING: newCell.setCellValue(cell.getRichStringCellValue()); break; default: newCell.setCellValue(formatter.formatCellValue(cell)); }//from ww w . j ava2s. c o m if (cell.getCellComment() != null) { XSSFClientAnchor anchor = factory.createClientAnchor(); anchor.setDx1(100); anchor.setDx2(100); anchor.setDy1(100); anchor.setDy2(100); anchor.setCol1(newCell.getColumnIndex()); anchor.setCol2(newCell.getColumnIndex() + 4); anchor.setRow1(newCell.getRowIndex()); anchor.setRow2(newCell.getRowIndex() + 4); anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE); XSSFComment comment = patriarch.createCellComment(anchor); comment.setString(cell.getCellComment().getString()); newCell.setCellComment(comment); } newCell.setCellStyle(cell.getCellStyle()); newCell.getSheet().setColumnWidth(newCell.getColumnIndex(), cell.getSheet().getColumnWidth(cell.getColumnIndex())); } } }
From source file:org.apache.ofbiz.pricat.AbstractPricatParser.java
License:Apache License
/** * Get data by version definition./*ww w. j a v a 2s . c o m*/ * * @param row * @param colNames * @param size * @return */ public List<Object> getCellContents(XSSFRow row, List<Object[]> colNames, int size) { List<Object> results = new ArrayList<Object>(); boolean foundError = false; if (isEmptyRow(row, size, true)) { return null; } for (int i = 0; i < size; i++) { XSSFCell cell = null; if (row.getPhysicalNumberOfCells() > i) { cell = row.getCell(i); } if (cell == null) { if (((Boolean) colNames.get(i)[2]).booleanValue()) { report.print(UtilProperties.getMessage(resource, "ErrorColCannotEmpty", new Object[] { colNames.get(i)[0] }, locale), InterfaceReport.FORMAT_WARNING); errorMessages.put(new CellReference(cell), UtilProperties.getMessage(resource, "ErrorColCannotEmpty", new Object[] { colNames.get(i)[0] }, locale)); foundError = true; continue; } else { cell = row.createCell(i); } } int cellType = cell.getCellType(); String cellValue = formatter.formatCellValue(cell); if (UtilValidate.isNotEmpty(cellValue)) { if (cellType == XSSFCell.CELL_TYPE_FORMULA) { cellValue = BigDecimal.valueOf(cell.getNumericCellValue()) .setScale(FinAccountHelper.decimals, FinAccountHelper.rounding).toString(); report.print(((i == 0) ? "" : ", ") + cellValue, InterfaceReport.FORMAT_NOTE); } else { report.print(((i == 0) ? "" : ", ") + cellValue, InterfaceReport.FORMAT_NOTE); } } else { report.print(((i == 0) ? "" : ","), InterfaceReport.FORMAT_NOTE); } if (((Boolean) colNames.get(i)[2]).booleanValue() && UtilValidate.isEmpty(cellValue)) { report.print(UtilProperties.getMessage(resource, "ErrorColCannotEmpty", new Object[] { colNames.get(i)[0] }, locale), InterfaceReport.FORMAT_WARNING); errorMessages.put(new CellReference(cell), UtilProperties.getMessage(resource, "ErrorColCannotEmpty", new Object[] { colNames.get(i)[0] }, locale)); foundError = true; results.add(null); continue; } if (((Boolean) colNames.get(i)[2]).booleanValue() && cellType != (int) colNames.get(i)[1]) { // String warningMessage = ""; if ((int) colNames.get(i)[1] == XSSFCell.CELL_TYPE_STRING) { results.add(cellValue); } else if ((int) colNames.get(i)[1] == XSSFCell.CELL_TYPE_NUMERIC) { if (cell.getCellType() != XSSFCell.CELL_TYPE_STRING) { cell.setCellType(XSSFCell.CELL_TYPE_STRING); } try { results.add(BigDecimal.valueOf(Double.parseDouble(cell.getStringCellValue())) .setScale(FinAccountHelper.decimals, FinAccountHelper.rounding)); } catch (NumberFormatException e) { results.add(null); errorMessages.put(new CellReference(cell), UtilProperties.getMessage(resource, "ErrorParseValueToNumeric", locale)); } } } else { if (UtilValidate.isEmpty(cellValue)) { results.add(null); continue; } if ((int) colNames.get(i)[1] == XSSFCell.CELL_TYPE_STRING) { if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) { results.add(cell.getStringCellValue()); } else { results.add(cellValue); } } else if ((int) colNames.get(i)[1] == XSSFCell.CELL_TYPE_NUMERIC) { if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) { try { results.add(BigDecimal.valueOf(Double.valueOf(cell.getStringCellValue()))); } catch (NumberFormatException e) { results.add(null); errorMessages.put(new CellReference(cell), UtilProperties.getMessage(resource, "ErrorParseValueToNumeric", locale)); } } else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { try { results.add(BigDecimal.valueOf(cell.getNumericCellValue()) .setScale(FinAccountHelper.decimals, FinAccountHelper.rounding)); } catch (NumberFormatException e) { results.add(null); errorMessages.put(new CellReference(cell), UtilProperties.getMessage(resource, "ErrorParseValueToNumeric", locale)); } } else { try { results.add(BigDecimal.valueOf(Double.valueOf(cellValue)) .setScale(FinAccountHelper.decimals, FinAccountHelper.rounding)); } catch (NumberFormatException e) { results.add(null); errorMessages.put(new CellReference(cell), UtilProperties.getMessage(resource, "ErrorParseValueToNumeric", locale)); } } } } } if (foundError) { return null; } return results; }
From source file:org.apache.ofbiz.pricat.sample.SamplePricatParser.java
License:Apache License
/** * Get data by version definition.//from ww w.jav a 2 s. c om * * @param row * @param colNames * @param size * @return */ public List<Object> getCellContents(XSSFRow row, List<Object[]> colNames, int size) { List<Object> results = new ArrayList<Object>(); boolean foundError = false; if (isEmptyRow(row, size, true)) { return null; } // check and get data for (int i = 0; i < size; i++) { XSSFCell cell = null; if (row.getPhysicalNumberOfCells() > i) { cell = row.getCell(i); } if (cell == null) { if (((Boolean) colNames.get(i)[2]).booleanValue() && (facilities.keySet().size() > 1 || (facilities.keySet().size() == 1 && i >= 2))) { report.print(UtilProperties.getMessage(resource, "ErrorColCannotEmpty", new Object[] { colNames.get(i)[0] }, locale), InterfaceReport.FORMAT_WARNING); cell = row.createCell(i); errorMessages.put(new CellReference(cell), UtilProperties.getMessage(resource, "ErrorColCannotEmpty", new Object[] { colNames.get(i)[0] }, locale)); foundError = true; results.add(null); continue; } else { cell = row.createCell(i); } } int cellType = cell.getCellType(); String cellValue = formatter.formatCellValue(cell); if (UtilValidate.isNotEmpty(cellValue) && UtilValidate.isNotEmpty(cellValue.trim())) { if (cellType == XSSFCell.CELL_TYPE_FORMULA) { try { cellValue = BigDecimal.valueOf(cell.getNumericCellValue()) .setScale(FinAccountHelper.decimals, FinAccountHelper.rounding).toString(); } catch (IllegalStateException e) { try { cellValue = cell.getStringCellValue(); } catch (IllegalStateException e1) { // do nothing } } report.print(((i == 0) ? "" : ", ") + cellValue, InterfaceReport.FORMAT_NOTE); } else { report.print(((i == 0) ? "" : ", ") + cellValue, InterfaceReport.FORMAT_NOTE); } } else { report.print(((i == 0) ? "" : ","), InterfaceReport.FORMAT_NOTE); } if (((Boolean) colNames.get(i)[2]).booleanValue() && UtilValidate.isEmpty(cellValue) && (facilities.keySet().size() > 1 || (facilities.keySet().size() == 1 && i >= 2))) { report.print(UtilProperties.getMessage(resource, "ErrorColCannotEmpty", new Object[] { colNames.get(i)[0] }, locale), InterfaceReport.FORMAT_WARNING); errorMessages.put(new CellReference(cell), UtilProperties.getMessage(resource, "ErrorColCannotEmpty", new Object[] { colNames.get(i)[0] }, locale)); foundError = true; results.add(null); continue; } if (((Boolean) colNames.get(i)[2]).booleanValue() && cellType != (int) colNames.get(i)[1]) { // String warningMessage = ""; if ((int) colNames.get(i)[1] == XSSFCell.CELL_TYPE_STRING) { if (UtilValidate.isNotEmpty(cellValue) && UtilValidate.isNotEmpty(cellValue.trim())) { results.add(cellValue); } else { results.add(null); } } else if ((int) colNames.get(i)[1] == XSSFCell.CELL_TYPE_NUMERIC) { if (cell.getCellType() != XSSFCell.CELL_TYPE_STRING) { cell.setCellType(XSSFCell.CELL_TYPE_STRING); } try { results.add(BigDecimal.valueOf(Double.parseDouble(cell.getStringCellValue())) .setScale(FinAccountHelper.decimals, FinAccountHelper.rounding)); } catch (NumberFormatException e) { results.add(null); errorMessages.put(new CellReference(cell), UtilProperties.getMessage(resource, "ErrorParseValueToNumeric", locale)); } } } else { if (UtilValidate.isEmpty(cellValue) || UtilValidate.isEmpty(cellValue.trim())) { results.add(null); continue; } if ((int) colNames.get(i)[1] == XSSFCell.CELL_TYPE_STRING) { if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) { cellValue = cell.getStringCellValue().trim(); results.add(cellValue); } else { results.add(cellValue.trim()); } } else if ((int) colNames.get(i)[1] == XSSFCell.CELL_TYPE_NUMERIC) { if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) { try { results.add(BigDecimal.valueOf(Double.valueOf(cell.getStringCellValue()))); } catch (NumberFormatException e) { results.add(null); errorMessages.put(new CellReference(cell), UtilProperties.getMessage(resource, "ErrorParseValueToNumeric", locale)); } } else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { try { results.add(BigDecimal.valueOf(cell.getNumericCellValue()) .setScale(FinAccountHelper.decimals, FinAccountHelper.rounding)); } catch (NumberFormatException e) { results.add(null); errorMessages.put(new CellReference(cell), UtilProperties.getMessage(resource, "ErrorParseValueToNumeric", locale)); } } else { try { results.add(BigDecimal.valueOf(Double.valueOf(cellValue)) .setScale(FinAccountHelper.decimals, FinAccountHelper.rounding)); } catch (NumberFormatException e) { results.add(null); errorMessages.put(new CellReference(cell), UtilProperties.getMessage(resource, "ErrorParseValueToNumeric", locale)); } } } } } if (foundError) { return null; } return results; }
From source file:org.exoplatform.services.document.impl.MSXExcelDocumentReader.java
License:Open Source License
/** * Returns only a text from .xlsx file content. * //from www .ja v a2 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(final InputStream is) throws IOException, DocumentReadException { if (is == null) { throw new IllegalArgumentException("InputStream is null."); } StringBuilder builder = new StringBuilder(""); SimpleDateFormat dateFormat = new SimpleDateFormat(DATE_FORMAT); try { if (is.available() == 0) { return ""; } XSSFWorkbook wb; try { wb = SecurityHelper.doPrivilegedIOExceptionAction(new PrivilegedExceptionAction<XSSFWorkbook>() { public XSSFWorkbook run() throws Exception { return new XSSFWorkbook(is); } }); } catch (IOException e) { throw new DocumentReadException("Can't open spreadsheet.", e); } catch (OpenXML4JRuntimeException e) { return builder.toString(); } for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) { XSSFSheet sheet = wb.getSheetAt(sheetNum); if (sheet != null) { for (int rowNum = sheet.getFirstRowNum(); rowNum <= sheet.getLastRowNum(); rowNum++) { XSSFRow row = sheet.getRow(rowNum); if (row != null) { int lastcell = row.getLastCellNum(); for (int k = 0; k < lastcell; k++) { XSSFCell cell = row.getCell(k); if (cell != null) { switch (cell.getCellType()) { case XSSFCell.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 XSSFCell.CELL_TYPE_FORMULA: builder.append(cell.getCellFormula().toString()).append(" "); break; case XSSFCell.CELL_TYPE_BOOLEAN: builder.append(cell.getBooleanCellValue()).append(" "); break; case XSSFCell.CELL_TYPE_ERROR: builder.append(cell.getErrorCellValue()).append(" "); break; case XSSFCell.CELL_TYPE_STRING: builder.append(cell.getStringCellValue().toString()).append(" "); 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.MSXExcelDocumentReader.java
License:Open Source License
public static boolean isCellDateFormatted(XSSFCell cell) { boolean bDate = false; double d = cell.getNumericCellValue(); if (HSSFDateUtil.isValidExcelDate(d)) { XSSFCellStyle 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;//from w ww.ja v a 2 s .c om break; default: bDate = false; break; } } return bDate; }
From source file:org.finra.jtaf.core.utilities.ExcelFileParser.java
License:Apache License
public List<List<String>> parseExcelFile(boolean isXlsx) throws Exception { List<List<String>> parsedExcelFile = new ArrayList<List<String>>(); if (isXlsx) { for (int i = 0, numberOfRows = workBookSheetXlsx.getPhysicalNumberOfRows(); i < numberOfRows + 1; i++) { XSSFRow row = workBookSheetXlsx.getRow(i); if (row != null) { List<String> parsedExcelRow = new ArrayList<String>(); for (int j = 0, numberOfColumns = row.getLastCellNum(); j < numberOfColumns; j++) { XSSFCell cell = row.getCell(j); if (cell != null) { try { if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) { parsedExcelRow.add(cell.getStringCellValue()); } else if (cell.getCellType() == XSSFCell.CELL_TYPE_BLANK) { parsedExcelRow.add(""); } else if (cell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) { parsedExcelRow.add(String.valueOf(cell.getBooleanCellValue())); } else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { parsedExcelRow.add(String.valueOf(cell.getNumericCellValue())); } else if (cell.getCellType() == XSSFCell.CELL_TYPE_FORMULA) { parsedExcelRow.add(""); } else { parsedExcelRow.add(cell.getStringCellValue()); }/* w w w . j av a 2 s. c o m*/ } catch (Exception e) { logger.fatal("Oops! Can't read cell (row = " + i + ", column = " + j + ") in the excel file! Change cell format to 'Text', please!"); return null; } } else { parsedExcelRow.add(""); } } parsedExcelFile.add(parsedExcelRow); } } } else { for (int i = 0, numberOfRows = workBookSheetXls.getPhysicalNumberOfRows(); i < numberOfRows + 1; i++) { HSSFRow row = workBookSheetXls.getRow(i); if (row != null) { List<String> parsedExcelRow = new ArrayList<String>(); for (int j = 0, numberOfColumns = row.getLastCellNum(); j < numberOfColumns; j++) { HSSFCell cell = row.getCell(j); if (cell != null) { try { if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { parsedExcelRow.add(cell.getStringCellValue()); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) { parsedExcelRow.add(""); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) { parsedExcelRow.add(String.valueOf(cell.getBooleanCellValue())); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { parsedExcelRow.add(String.valueOf(cell.getNumericCellValue())); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) { parsedExcelRow.add(String.valueOf("")); } else { parsedExcelRow.add(cell.getStringCellValue()); } } catch (Exception e) { logger.fatal("Oops! Can't read cell (row = " + i + ", column = " + j + ") in the excel file! Change cell format to 'Text', please!"); return null; } } else { parsedExcelRow.add(""); } } parsedExcelFile.add(parsedExcelRow); } } } return parsedExcelFile; }
From source file:org.kuali.test.runner.output.PoiHelper.java
License:Educational Community License
private void copyCell(XSSFCell oldCell, XSSFCell newCell, Map<Integer, XSSFCellStyle> styleMap) { if (styleMap != null) { int stHashCode = oldCell.getCellStyle().hashCode(); XSSFCellStyle newCellStyle = styleMap.get(stHashCode); if (newCellStyle == null) { newCellStyle = newCell.getSheet().getWorkbook().createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); newCellStyle.setFont(oldCell.getCellStyle().getFont()); styleMap.put(stHashCode, newCellStyle); }/*from w w w . j a v a 2s . c o m*/ newCell.setCellStyle(newCellStyle); } switch (oldCell.getCellType()) { case XSSFCell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getStringCellValue()); break; case XSSFCell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case XSSFCell.CELL_TYPE_BLANK: newCell.setCellType(HSSFCell.CELL_TYPE_BLANK); break; case XSSFCell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case XSSFCell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case XSSFCell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; default: break; } }
From source file:org.nuxeo.ecm.core.convert.plugins.text.extractors.XLX2TextConverter.java
License:Apache License
protected void appendTextFromCell(XSSFCell cell, StringBuffer sb) { String cellValue = null;//from ww w . j a va 2 s. com switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: cellValue = Double.toString(cell.getNumericCellValue()).trim(); break; case XSSFCell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue().trim(); break; } if (cellValue != null && cellValue.length() > 0) { sb.append(cellValue).append(CELL_SEP); } }