List of usage examples for org.apache.poi.ss.usermodel DataFormatter formatCellValue
public String formatCellValue(Cell cell)
Returns the formatted value of a cell as a String regardless of the cell type.
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 ww w . ja v a 2s .co 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 www . j av a 2 s .co 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.kitodo.production.services.data.ProcessService.java
License:Open Source License
private PdfPTable getPdfTable(List<List<HSSFCell>> rowList) { // create formatter for cells with default locale DataFormatter formatter = new DataFormatter(); PdfPTable table = new PdfPTable(9); table.setSpacingBefore(20);//from w w w .ja v a 2 s . c o m for (List<HSSFCell> row : rowList) { for (HSSFCell hssfCell : row) { String stringCellValue = formatter.formatCellValue(hssfCell); table.addCell(stringCellValue); } } return table; }
From source file:org.matonto.etl.rest.impl.DelimitedRestImpl.java
License:Open Source License
/** * Converts the specified number of rows of a Excel file into JSON and returns * them as a String.// w ww .j a v a 2 s . c om * * @param input the Excel file to convert into JSON * @param numRows the number of rows from the Excel file to convert * @return a string with the JSON of the Excel rows * @throws IOException excel file could not be read * @throws InvalidFormatException file is not in a valid excel format */ private String convertExcelRows(File input, int numRows) throws IOException, InvalidFormatException { Workbook wb = WorkbookFactory.create(input); // Only support single sheet files for now Sheet sheet = wb.getSheetAt(0); DataFormatter df = new DataFormatter(); JSONArray rowList = new JSONArray(); String[] columns; for (Row row : sheet) { if (row.getRowNum() <= numRows) { columns = new String[row.getPhysicalNumberOfCells()]; int index = 0; for (Cell cell : row) { columns[index] = df.formatCellValue(cell); index++; } rowList.add(columns); } } return rowList.toString(); }
From source file:org.matonto.etl.rest.impl.DelimitedRestImplTest.java
License:Open Source License
private List<String> getExcelResourceLines(String fileName) { List<String> expectedLines = new ArrayList<>(); try {/*from w ww. java2s .c om*/ Workbook wb = WorkbookFactory.create(getClass().getResourceAsStream("/" + fileName)); Sheet sheet = wb.getSheetAt(0); DataFormatter df = new DataFormatter(); int index = 0; for (Row row : sheet) { String rowStr = ""; for (Cell cell : row) { rowStr += df.formatCellValue(cell); } expectedLines.add(index, rowStr); index++; } } catch (IOException | InvalidFormatException e) { e.printStackTrace(); } return expectedLines; }
From source file:org.matonto.etl.service.delimited.DelimitedConverterImpl.java
License:Open Source License
@Override public Model convert(ExcelConfig config) throws IOException, MatOntoException { String[] nextRow;// ww w . j a v a 2 s. c om Model convertedRDF = modelFactory.createModel(); ArrayList<ClassMapping> classMappings = parseClassMappings(config.getMapping()); try { Workbook wb = WorkbookFactory.create(config.getData()); Sheet sheet = wb.getSheetAt(0); DataFormatter df = new DataFormatter(); boolean containsHeaders = config.getContainsHeaders(); long offset = config.getOffset(); Optional<Long> limit = config.getLimit(); //Traverse each row and convert column into RDF for (Row row : sheet) { // If headers exist or the row is before the offset point, skip the row if ((containsHeaders && row.getRowNum() == 0) || row.getRowNum() - (containsHeaders ? 1 : 0) < offset || (limit.isPresent() && row.getRowNum() >= limit.get() + offset)) { continue; } nextRow = new String[row.getPhysicalNumberOfCells()]; int cellIndex = 0; for (Cell cell : row) { nextRow[cellIndex] = df.formatCellValue(cell); cellIndex++; } writeClassMappingsToModel(convertedRDF, nextRow, classMappings); } } catch (InvalidFormatException e) { throw new MatOntoException(e); } return convertedRDF; }
From source file:org.opentestsystem.delivery.testreg.upload.ExcelUtils.java
License:Open Source License
private Object[] getCellValues(final Row row, final MissingCellPolicy policy) { final List<Object> excelColumnList = new ArrayList<Object>(); final DataFormatter dataFormat = new DataFormatter(); final int lastCellNo = row.getLastCellNum(); for (int cellNum = 0; cellNum < lastCellNo; cellNum++) { final Cell cell = row.getCell(cellNum, policy); if (cell != null) { switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_STRING: excelColumnList.add(cell.getStringCellValue()); break; case XSSFCell.CELL_TYPE_BOOLEAN: cell.setCellType(XSSFCell.CELL_TYPE_STRING); excelColumnList.add(cell.getStringCellValue()); break; case XSSFCell.CELL_TYPE_NUMERIC: excelColumnList.add(dataFormat.formatCellValue(cell)); break; case XSSFCell.CELL_TYPE_BLANK: excelColumnList.add(""); break; case XSSFCell.CELL_TYPE_FORMULA: excelColumnList.add(cell.getCellFormula()); break; case XSSFCell.CELL_TYPE_ERROR: excelColumnList.add(cell.getErrorCellValue()); break; default: excelColumnList.add(cell.toString()); }/*from w w w. j a v a 2 s. c o m*/ } } return excelColumnList.toArray(new Object[excelColumnList.size()]); }
From source file:org.project.utilities.ExcelRead.java
public ArrayList readexl(File exfile, String exfilename) { ArrayList storvalues = new ArrayList(); try {/*from www.j ava 2 s . c o m*/ // File file2 = new File("/home/asl/Desktop/html work/ProgramFile/test_template.xls"); FileInputStream file = new FileInputStream(exfile); //Workbook workbook = null; // String name = file2.getName(); String name = exfilename; Workbook workbook = null; if (name.toLowerCase().endsWith("xlsx")) { workbook = new XSSFWorkbook(file); } else if (name.toLowerCase().endsWith("xls")) { workbook = new HSSFWorkbook(file); } // workbook.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL); DataFormatter fmt = new DataFormatter(); for (int sn = 0; sn < workbook.getNumberOfSheets(); sn++) { Sheet sheet = workbook.getSheetAt(sn); for (int rn = sheet.getFirstRowNum() + 1; rn <= sheet.getLastRowNum(); rn++) { Row row = sheet.getRow(rn); if (row == null) { // There is no data in this row, handle as needed } else { // Row "rn" has data ArrayList storeval = new ArrayList(); // System.out.println("size " + row.getLastCellNum()); for (int cn = 0; cn < 17; cn++) { // for (int cn = 0; cn < row.getLastCellNum(); cn++) { Cell cell = row.getCell(cn); // cell.setCellType(Cell.CELL_TYPE_STRING); String val = ""; // String strCellValue = ""; if (cell == null) { // This cell is empty/blank/un-used, handle as needed } else { String cellStr = fmt.formatCellValue(cell); val = cellStr; // Do something with the value } storeval.add(val); } storvalues.add(storeval); } } } } catch (Exception e) { e.printStackTrace(); } return storvalues; }
From source file:org.rakuten.util.XLSUtility.java
License:Open Source License
/** * @param file//w w w.j a v a 2s . c o m * @return map of testData * * This method take file name for test data and convert it into map of testCaseId and Object of testData * Excel file is expected to have at-least 3 columns else that row will be skipped, testCase id is supposed * to be unique other wise test data will be overridden * This method will return null if file does not exists or if file does not contains any data row */ public Map<Integer, TestData> getTestData(String file) { Iterator<Row> rows = getSheetData(file); DataFormatter fmt = new DataFormatter(); if (rows == null) return null; Map<Integer, TestData> testData = new HashMap<>(); while (rows.hasNext()) { Row nextRow = rows.next(); if (nextRow.getPhysicalNumberOfCells() >= 3) { try { Cell testCaseId = nextRow.getCell(0, Row.RETURN_BLANK_AS_NULL); Cell request = nextRow.getCell(1); Cell response = nextRow.getCell(2); TestData testData2 = new TestData(request.getStringCellValue(), response.getStringCellValue()); testData.put(Integer.parseInt(fmt.formatCellValue(testCaseId)), testData2); } catch (Exception e) { e.printStackTrace(); } } } return testData; }
From source file:org.talend.dataprep.schema.xls.XlsUtils.java
License:Open Source License
/** * Return the numeric value.//from ww w. j a va 2 s .co m * * @param cell the cell to extract the value from. * @return the numeric value from the cell. */ private static String getNumericValue(Cell cell, CellValue cellValue, boolean fromFormula) { // Date is typed as numeric if (HSSFDateUtil.isCellDateFormatted(cell)) { // TODO configurable?? DateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy", Locale.ENGLISH); return sdf.format(cell.getDateCellValue()); } // Numeric type (use data formatter to get number format right) DataFormatter formatter = new HSSFDataFormatter(Locale.ENGLISH); if (cellValue == null) { return formatter.formatCellValue(cell); } return fromFormula ? cellValue.formatAsString() : formatter.formatCellValue(cell); }