List of usage examples for org.apache.poi.ss.usermodel Cell getCellFormula
String getCellFormula();
SUM(C4:E4) From source file:com.validation.manager.core.tool.requirement.importer.RequirementImporter.java
License:Apache License
@Override public List<Requirement> importFile(boolean header) throws RequirementImportException, VMException { queue.clear();/*ww w.j a v a 2 s .co m*/ List<Integer> errors = new ArrayList<>(); HashMap<String, Object> parameters = new HashMap<>(); List<Object> result; if (toImport == null) { throw new RequirementImportException("message.requirement.import.file.null"); } else if (!toImport.exists()) { throw new RequirementImportException("message.requirement.import.file.invalid"); } else { //Excel support if (toImport.getName().endsWith(".xls") || toImport.getName().endsWith(".xlsx")) { try { Workbook wb = loadFile(); org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt(0); int rows = sheet.getPhysicalNumberOfRows(); int r = 0; if (header) { //Skip header row r++; } for (; r < rows; r++) { Row row = sheet.getRow(r); if (row == null) { continue; } if (row.getCell(0) == null) { LOG.log(Level.WARNING, "Found an empty row on line: {0}. " + "Stopping processing", r); break; } int cells = row.getPhysicalNumberOfCells(); if (cells < 2) { LOG.log(Level.INFO, "Processing row: {0}", r); LOG.warning(ResourceBundle .getBundle("com.validation.manager.resources.VMMessages", Locale.getDefault()) .getString("message.requirement.import.missing.column") .replaceAll("%c", "" + cells)); errors.add(r); } else { Requirement requirement = new Requirement(); LOG.log(Level.FINE, "Row: {0}", r); for (int c = 0; c < cells; c++) { Cell cell = row.getCell(c); String value = ""; if (cell != null) { switch (cell.getCellTypeEnum()) { case FORMULA: value = cell.getCellFormula(); break; case NUMERIC: value = "" + cell.getNumericCellValue(); break; case STRING: value = cell.getStringCellValue(); break; default: value = ""; break; } } //Remove any extra spaces. value = value.trim(); switch (c) { case 0: //Unique ID LOG.fine("Setting id"); requirement.setUniqueId(value); break; case 1: //Description LOG.fine("Setting desc"); requirement.setDescription(value); break; case 2: //Optional Requirement type LOG.fine("Setting requirement type"); parameters.clear(); parameters.put("name", value); result = namedQuery("RequirementType.findByName", parameters); if (result.isEmpty()) { //Assume a default parameters.clear(); parameters.put("name", "SW"); result = namedQuery("RequirementType.findByName", parameters); } requirement.setRequirementTypeId((RequirementType) result.get(0)); break; case 3: //Optional notes LOG.fine("Setting notes"); requirement.setNotes(value); break; default: throw new RequirementImportException("Invalid column detected: " + c); } LOG.fine(value); } //This shouldn't be null assert rsn != null : "Requirement Spec Node is null?"; requirement.setRequirementSpecNode(rsn); parameters.clear(); parameters.put("status", "general.open"); result = namedQuery("RequirementStatus.findByStatus", parameters); requirement.setRequirementStatusId((RequirementStatus) result.get(0)); assert requirement.getUniqueId() != null && !requirement.getUniqueId().isEmpty() : "Invalid requirement detected!"; try { if (!exists(requirement) && !queue.containsKey(requirement.getUniqueId())) { queue.put(requirement.getUniqueId(), requirement); } } catch (IllegalOrphanException | NonexistentEntityException ex) { Exceptions.printStackTrace(ex); } } } } catch (InvalidFormatException | IOException ex) { LOG.log(Level.SEVERE, null, ex); } finally { try { if (inp != null) { inp.close(); } } catch (IOException ex) { LOG.log(Level.SEVERE, null, ex); } } } else if (toImport.getName().endsWith(".xml")) { throw new RequirementImportException("XML importing not supported yet."); } else if (toImport.getName().endsWith(".doc") || toImport.getName().endsWith(".docx")) { try { TableExtractor te = new TableExtractor(toImport); List<DefaultTableModel> tables = te.extractTables(); Requirement requirement = new Requirement(); LOG.log(Level.INFO, "Imported {0} tables!", tables.size()); int count = 1; for (DefaultTableModel model : tables) { int rows = model.getRowCount(); int cols = model.getColumnCount(); LOG.log(Level.INFO, "Processing table {0} with {1} " + "rows and {2} columns.", new Object[] { count, rows, cols }); for (int r = 0; r < rows; r++) { for (int c = 0; c < cols; c++) { String value = (String) model.getValueAt(rows, cols); switch (c) { case 0: //Unique ID LOG.fine("Setting id"); requirement.setUniqueId(value); break; case 1: //Description LOG.fine("Setting desc"); requirement.setDescription(value); break; case 2: //Requirement type LOG.fine("Setting requirement type"); parameters.clear(); parameters.put("name", value); result = namedQuery("RequirementType.findByName", parameters); if (result.isEmpty()) { //Assume a default parameters.clear(); parameters.put("name", "SW"); result = namedQuery("RequirementType.findByName", parameters); } requirement.setRequirementTypeId((RequirementType) result.get(0)); break; case 3: //Optional notes LOG.fine("Setting notes"); requirement.setNotes(value); break; default: throw new RuntimeException("Invalid column detected: " + c); } } } } } catch (IOException | ClassNotFoundException ex) { Exceptions.printStackTrace(ex); } } else { throw new RequirementImportException("Unsupported file format: " + toImport.getName()); } StringBuilder sb = new StringBuilder("Rows with erros:\n"); errors.stream().forEach((line) -> { sb.append(line).append('\n'); }); if (!errors.isEmpty()) { getDefault().lookup(MessageHandler.class).info(sb.toString()); } return new ArrayList(queue.values()); } }
From source file:com.validation.manager.core.tool.step.importer.StepImporter.java
License:Apache License
@Override public List<Step> importFile(boolean header) throws TestCaseImportException { steps.clear();/* w w w . j a va 2 s . com*/ if (toImport == null) { throw new TestCaseImportException("message.step.import.file.null"); } else if (!toImport.exists()) { throw new TestCaseImportException("message.step.import.file.invalid"); } else { //Excel support if (toImport.getName().endsWith(".xls") || toImport.getName().endsWith(".xlsx")) { InputStream inp = null; try { inp = new FileInputStream(toImport); org.apache.poi.ss.usermodel.Workbook wb = create(inp); org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt(0); int rows = sheet.getPhysicalNumberOfRows(); int r = 0; if (header) { //Skip header row r++; } for (; r < rows; r++) { Row row = sheet.getRow(r); if (row == null) { continue; } int cells = row.getPhysicalNumberOfCells(); if (row.getCell(0) == null) { LOG.log(Level.WARNING, "Found an empty row on line: {0}. " + "Stopping processing", r); break; } if (cells < 2) { throw new TestCaseImportException(RB.getString("message.step.import.missing.column") .replaceAll("%c", "" + cells)); } Step step = new Step(); step.setRequirementList(new ArrayList<>()); HashMap<String, Object> parameters = new HashMap<>(); List<Object> result; LOG.log(Level.FINE, "Row: {0}", r); for (int c = 0; c < cells; c++) { Cell cell = row.getCell(c); String value = null; if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_FORMULA: value = cell.getCellFormula(); break; case Cell.CELL_TYPE_NUMERIC: value = "" + cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: value = cell.getStringCellValue(); break; default: //Do nothing. } } switch (c) { case 0: if (value != null) { //Sequence LOG.fine("Setting sequence"); Integer val = value.contains(".") ? valueOf(value.substring(0, value.indexOf("."))) : valueOf(value); if (!tc.getStepList().isEmpty()) { int max = 0; for (Step s : tc.getStepList()) { if (s.getStepSequence() > max) { max = s.getStepSequence(); } } //Make sure there isn't one on that sequence already val += max; } step.setStepSequence(val); } break; case 1: if (value != null) { //Text LOG.fine("Setting text"); step.setText(value.getBytes("UTF-8")); } break; case 2: //Optional Related requirements if (value != null && !value.trim().isEmpty()) { LOG.fine("Setting related requirements"); StringTokenizer st = new StringTokenizer(value, ","); while (st.hasMoreTokens()) { String token = st.nextToken().trim(); parameters.clear(); parameters.put("uniqueId", token); result = namedQuery("Requirement.findByUniqueId", parameters); if (!result.isEmpty()) { for (Object o : result) { step.getRequirementList().add((Requirement) o); } } } } break; case 3: if (value != null) { //Optional Expected result LOG.fine("Setting expected result"); step.setExpectedResult(value.getBytes("UTF-8")); } break; case 4: if (value != null) { //Optional notes LOG.fine("Setting notes"); step.setNotes(value); } break; default: throw new RuntimeException("Invalid column detected: " + c); } LOG.fine(value); } step.setTestCase(tc); steps.add(step); } } catch (InvalidFormatException | IOException ex) { LOG.log(Level.SEVERE, null, ex); } finally { try { if (inp != null) { inp.close(); } } catch (IOException ex) { LOG.log(Level.SEVERE, null, ex); } } } else if (toImport.getName().endsWith(".xml")) { throw new TestCaseImportException("XML importing not supported yet."); } else { throw new TestCaseImportException("Unsupported file format: " + toImport.getName()); } return steps; } }
From source file:com.vermeg.convertisseur.service.ConvServiceImpl.java
/** * * @param file/*w ww. j a va 2 s . c o m*/ * @return * @throws FileNotFoundException * @throws InvalidFormatException * @throws IOException */ /*this method convert a multipart file to json object */ @Override public JSONObject convert(MultipartFile file, String name) throws FileNotFoundException, InvalidFormatException, IOException { // File file = new File("C:\\Users\\Ramzi\\Documents\\PFE\\developpement\\avancement.xlsx"); File filez = File.createTempFile("fichier", "xslx"); file.transferTo(filez); FileInputStream inp = new FileInputStream(filez); Workbook workbook = WorkbookFactory.create(inp); //Sheet sheet = workbook.getSheetAt( 0 ); Sheet sheet = workbook.getSheet(name); // Start constructing JSON. JSONObject json = new JSONObject(); // Iterate through the rows. JSONArray rows = new JSONArray(); for (Iterator<Row> rowsIT = sheet.rowIterator(); rowsIT.hasNext();) { Row row = rowsIT.next(); JSONObject jRow = new JSONObject(); // Iterate through the cells. JSONArray cells = new JSONArray(); for (Iterator<Cell> cellsIT = row.cellIterator(); cellsIT.hasNext();) { Cell cell = cellsIT.next(); // System.out.println(cell.getCellType()); // cells.put(cell.getDateCellValue()); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: cells.put(cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { cells.put(cell.getDateCellValue()); } else { cells.put(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: cells.put(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: cells.put(cell.getCellFormula()); break; default: System.out.println(); } } jRow.put("cell", cells); rows.put(cells); //rows.put( jRow ); } // Create the JSON. json.put("rows", rows); System.out.println(json.toString()); return json; }
From source file:com.vertec.daoimpl.AttendanceDAOImpl.java
public List<Object> readexcel(String path) { // String data = ""; List<Object> table = new ArrayList<Object>(); try {/*from w ww .ja va2 s. c o m*/ // Get the workbook instance for XLSX file XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(path)); // Get first sheet from the workbook XSSFSheet sheet = wb.getSheetAt(0); Row row; Cell cell; // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { row = rowIterator.next(); // For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); List<String> rows = new ArrayList<String>(); while (cellIterator.hasNext()) { cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: rows.add(cell.getRichStringCellValue().getString()); // data+=cell.getRichStringCellValue().getString(); // System.out.print(cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { rows.add(cell.getDateCellValue() + ""); // data+=cell.getDateCellValue(); // System.out.print(cell.getDateCellValue()); } else { rows.add(cell.getNumericCellValue() + ""); // data+=cell.getNumericCellValue(); // System.out.print(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: rows.add(cell.getBooleanCellValue() + ""); // data+=cell.getBooleanCellValue(); // System.out.print(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: rows.add(cell.getCellFormula() + ""); // data+=cell.getCellFormula(); // System.out.print(cell.getCellFormula()); break; default: // System.out.print(""); } // data += "-"; // System.out.print(" - "); } table.add(rows); // data += ";;;"; // System.out.println(";;;"); } } catch (Exception e) { System.err.println("Exception :" + e.getMessage()); } return table; }
From source file:com.vertec.daoimpl.AttendanceDAOImpl.java
public String readexcel2(String path) { try {//from w ww . j a va 2 s . co m // Get the workbook instance for XLSX file XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(path)); // Get first sheet from the workbook XSSFSheet sheet = wb.getSheetAt(0); Row row; Cell cell; // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { row = rowIterator.next(); // For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: System.out.print(cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { System.out.print(cell.getDateCellValue()); } else { System.out.print(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: System.out.print(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: System.out.print(cell.getCellFormula()); break; default: System.out.print(""); } System.out.print(" - "); } System.out.println(";;;"); } } catch (Exception e) { System.err.println("Exception :" + e.getMessage()); } return null; }
From source file:com.virtusa.isq.vtaf.runtime.SeleniumTestBase.java
License:Apache License
/** * Adds the values from excel./*from w ww . j a va 2 s. c om*/ * * @param path * the path * @param index * the index * @return the string[][] * @throws IOException * Signals that an I/O exception has occurred. * @throws InvalidFormatException * the invalid format exception */ public final String[][] addValuesFromExcel(final String path, final String index) throws IOException, InvalidFormatException { String cellStringValue = null; double cellDoubleValue = 0; Boolean cellBooleanValue; byte cellErrorValue = 0; String[][] arrExcelContent; FileInputStream file = null; Workbook workbook = null; Sheet sheet = null; try { file = new FileInputStream(new File(path)); workbook = WorkbookFactory.create(file); sheet = workbook.getSheetAt(Integer.parseInt(index)); Iterator<Row> rowIterator = sheet.iterator(); arrExcelContent = new String[sheet.getPhysicalNumberOfRows()][]; while (rowIterator.hasNext()) { Row row = rowIterator.next(); int rowNumber = row.getRowNum(); Iterator<Cell> cellIterator = row.cellIterator(); arrExcelContent[rowNumber] = new String[sheet.getRow(rowNumber).getPhysicalNumberOfCells()]; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); int cellNumber = cell.getColumnIndex(); if (cell.getCellType() == Cell.CELL_TYPE_STRING) { cellStringValue = cell.getStringCellValue(); arrExcelContent[rowNumber][cellNumber] = cellStringValue; } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { cellBooleanValue = cell.getBooleanCellValue(); arrExcelContent[rowNumber][cellNumber] = cellBooleanValue.toString(); } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) { cellErrorValue = cell.getErrorCellValue(); arrExcelContent[rowNumber][cellNumber] = Byte.toString(cellErrorValue); } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { cellStringValue = cell.getCellFormula(); arrExcelContent[rowNumber][cellNumber] = cellStringValue; } else { cellDoubleValue = cell.getNumericCellValue(); arrExcelContent[rowNumber][cellNumber] = Double.toString(cellDoubleValue); } } } } finally { if (((InputStream) workbook) != null) { ((InputStream) workbook).close(); } } return arrExcelContent; }
From source file:com.xn.interfacetest.service.impl.TestCaseServiceImpl.java
License:Open Source License
/** * ?Cell?//www .j a va 2s . c o m * @param cell * @return */ private Object getCellFormatValue(Cell cell) { if (null == cell) { return ""; } DataFormatter formatter = new DataFormatter(); switch (cell.getCellTypeEnum()) { case STRING: return cell.getRichStringCellValue().getString(); case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { return cell.getDateCellValue(); } else { return Math.round(cell.getNumericCellValue()); } case BOOLEAN: return cell.getBooleanCellValue(); case FORMULA: return cell.getCellFormula(); case BLANK: return ""; default: return ""; } }
From source file:csv.impl.ExcelReader.java
License:Open Source License
/** * Returns the evaluated cell content.// w w w . j a va2 s.c o m * This assumes the cell contains a formula. * @param cell cell to evaluate * @return cell value */ public Object evaluateCellValue(Cell cell) { FormulaEvaluator evaluator = getFormulaEvaluator(); CellValue value = evaluator.evaluate(cell); switch (value.getCellType()) { case Cell.CELL_TYPE_STRING: return value.getStringValue(); case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { return DateUtil.getJavaDate(value.getNumberValue()); } else { return value.getNumberValue(); } case Cell.CELL_TYPE_BLANK: return null; case Cell.CELL_TYPE_BOOLEAN: return value.getBooleanValue(); case Cell.CELL_TYPE_ERROR: return value.getErrorValue(); default: System.out.println("type=" + cell.getCellType()); } return cell.getCellFormula(); }
From source file:de.enerko.reports2.engine.Report.java
License:Apache License
public List<CellDefinition> evaluateWorkbook() { final List<CellDefinition> rv = new ArrayList<CellDefinition>(); boolean reevaluate = false; if (workbook instanceof HSSFWorkbook) { try {/*from w w w .j a va 2 s . c om*/ workbook.getCreationHelper().createFormulaEvaluator().evaluateAll(); } catch (Exception e) { reevaluate = true; } } final FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook, IStabilityClassifier.TOTALLY_IMMUTABLE); formulaEvaluator.clearAllCachedResultValues(); for (int i = 0; i < workbook.getNumberOfSheets(); ++i) { final Sheet sheet = workbook.getSheetAt(i); for (Row row : sheet) { for (Cell cell : row) { if (reevaluate && cell.getCellType() == Cell.CELL_TYPE_FORMULA) { try { formulaEvaluator.evaluateFormulaCell(cell); } catch (Exception e) { ReportEngine.logger.log(Level.WARNING, String.format("Could not evaluate formula '%s' in cell %s on sheet '%s': %s", cell.getCellFormula(), CellReferenceHelper .getCellReference(cell.getColumnIndex(), row.getRowNum()), sheet.getSheetName(), e.getMessage())); } } final CellDefinition cellDefinition = IMPORTABLE_CELL_TYPES.containsKey( new Integer(cell.getCellType())) ? new CellDefinition(sheet.getSheetName(), cell) : null; if (cellDefinition != null) rv.add(cellDefinition); } } } return rv; }
From source file:de.iteratec.iteraplan.businesslogic.exchange.elasticExcel.util.ExcelUtils.java
License:Open Source License
/** * @param cell//from w w w . j a v a 2 s . c o m * @return true if cell is empty, false if not. */ @SuppressWarnings({ "PMD.MissingBreakInSwitch" }) public static boolean isEmptyCell(Cell cell) { if (cell == null) { return true; } switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: return true; case Cell.CELL_TYPE_BOOLEAN: return false; case Cell.CELL_TYPE_ERROR: return true; case Cell.CELL_TYPE_FORMULA: return StringUtils.isEmpty(cell.getCellFormula()); case Cell.CELL_TYPE_NUMERIC: return false; case Cell.CELL_TYPE_STRING: return StringUtils.isEmpty(cell.getStringCellValue()); default: // impossible. return true; } }