List of usage examples for org.apache.poi.ss.usermodel Cell getStringCellValue
String getStringCellValue();
For numeric cells we throw an exception.
From source file:com.vaadin.addon.spreadsheet.CellValueManager.java
License:Open Source License
private String getCachedFormulaCellValue(Cell formulaCell) { String result = null;/* w w w . j ava 2 s . c o m*/ switch (formulaCell.getCachedFormulaResultType()) { case Cell.CELL_TYPE_STRING: result = formulaCell.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: result = String.valueOf(formulaCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: result = ErrorEval.getText(formulaCell.getErrorCellValue()); break; case Cell.CELL_TYPE_NUMERIC: CellStyle style = formulaCell.getCellStyle(); result = formatter.formatRawCellContents(formulaCell.getNumericCellValue(), style.getDataFormat(), style.getDataFormatString()); break; } return result; }
From source file:com.vaadin.addon.spreadsheet.CellValueManager.java
License:Open Source License
protected CellData createCellDataForCell(Cell cell) { CellData cellData = new CellData(); cellData.row = cell.getRowIndex() + 1; cellData.col = cell.getColumnIndex() + 1; CellStyle cellStyle = cell.getCellStyle(); cellData.cellStyle = "cs" + cellStyle.getIndex(); cellData.locked = spreadsheet.isCellLocked(cell); try {//from w ww. j a va 2 s . c o m if (!spreadsheet.isCellHidden(cell)) { if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { cellData.formulaValue = formulaFormatter.reFormatFormulaValue(cell.getCellFormula(), spreadsheet.getLocale()); try { String oldValue = getCachedFormulaCellValue(cell); String newValue = formatter.formatCellValue(cell, getFormulaEvaluator()); if (!newValue.equals(oldValue)) { changedFormulaCells.add(new CellReference(cell)); } } catch (RuntimeException rte) { // Apache POI throws RuntimeExceptions for an invalid // formula from POI model String formulaValue = cell.getCellFormula(); cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(formulaValue); spreadsheet.markInvalidFormula(cell.getColumnIndex() + 1, cell.getRowIndex() + 1); } } } if (cell.getCellStyle().getDataFormatString().contains("%")) { cellData.isPercentage = true; } String formattedCellValue = formatter.formatCellValue(cell, getFormulaEvaluator()); if (!spreadsheet.isCellHidden(cell)) { if (cell.getCellType() == Cell.CELL_TYPE_FORMULA || cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { formattedCellValue = formattedCellValue.replaceAll("^-(?=0(.0*)?$)", ""); } } if (spreadsheet.isMarkedAsInvalidFormula(cellData.col, cellData.row)) { // The prefix '=' or '+' should not be included in formula value if (cell.getStringCellValue().charAt(0) == '+' || cell.getStringCellValue().charAt(0) == '=') { cellData.formulaValue = cell.getStringCellValue().substring(1); } formattedCellValue = "#VALUE!"; } if (formattedCellValue != null && !formattedCellValue.isEmpty() || cellStyle.getIndex() != 0) { // if the cell is not wrapping text, and is of type numeric or // formula (but not date), calculate if formatted cell value // fits the column width and possibly use scientific notation. cellData.value = formattedCellValue; cellData.needsMeasure = false; if (!cellStyle.getWrapText() && (!SpreadsheetUtil.cellContainsDate(cell) && cell.getCellType() == Cell.CELL_TYPE_NUMERIC || cell.getCellType() == Cell.CELL_TYPE_STRING || (cell.getCellType() == Cell.CELL_TYPE_FORMULA && !cell.getCellFormula().startsWith("HYPERLINK")))) { if (!doesValueFit(cell, formattedCellValue)) { if (valueContainsOnlyNumbers(formattedCellValue) && isGenerallCell(cell)) { cellData.value = cellValueFormatter.getScientificNotationStringForNumericCell( cell.getNumericCellValue(), formattedCellValue, cellStyleWidthRatioMap.get((int) cell.getCellStyle().getIndex()), spreadsheet.getState(false).colW[cell.getColumnIndex()] - 10); } else if (cell.getCellType() != Cell.CELL_TYPE_STRING) { cellData.needsMeasure = true; } } } if (cellStyle.getAlignment() == CellStyle.ALIGN_RIGHT) { cellData.cellStyle = cellData.cellStyle + " r"; } else if (cellStyle.getAlignment() == CellStyle.ALIGN_GENERAL) { if (SpreadsheetUtil.cellContainsDate(cell) || cell.getCellType() == Cell.CELL_TYPE_NUMERIC || (cell.getCellType() == Cell.CELL_TYPE_FORMULA && !cell.getCellFormula().startsWith("HYPERLINK") && !(cell.getCachedFormulaResultType() == Cell.CELL_TYPE_STRING))) { cellData.cellStyle = cellData.cellStyle + " r"; } } } // conditional formatting might be applied even if there isn't a // value (such as borders for the cell to the right) Set<Integer> cellFormattingIndexes = spreadsheet.getConditionalFormatter().getCellFormattingIndex(cell); if (cellFormattingIndexes != null) { for (Integer i : cellFormattingIndexes) { cellData.cellStyle = cellData.cellStyle + " cf" + i; } markedCells.add(SpreadsheetUtil.toKey(cell)); } if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC && DateUtil.isCellDateFormatted(cell)) { cellData.originalValue = cellData.value; } else { cellData.originalValue = getOriginalCellValue(cell); } handleIsDisplayZeroPreference(cell, cellData); } catch (RuntimeException rte) { LOGGER.log(Level.FINEST, rte.getMessage(), rte); cellData.value = "#VALUE!"; } return cellData; }
From source file:com.vaadin.addon.spreadsheet.CellValueManager.java
License:Open Source License
public String getOriginalCellValue(Cell cell) { if (cell == null) { return ""; }//from www . j a v a2s .com int cellType = cell.getCellType(); switch (cellType) { case Cell.CELL_TYPE_FORMULA: return cell.getCellFormula(); case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { Date dateCellValue = cell.getDateCellValue(); if (dateCellValue != null) { return new SimpleDateFormat().format(dateCellValue); } return ""; } return originalValueDecimalFormat.format(cell.getNumericCellValue()); case Cell.CELL_TYPE_STRING: return cell.getStringCellValue(); case Cell.CELL_TYPE_BOOLEAN: return String.valueOf(cell.getBooleanCellValue()); case Cell.CELL_TYPE_BLANK: return ""; case Cell.CELL_TYPE_ERROR: return String.valueOf(cell.getErrorCellValue()); } return ""; }
From source file:com.vaadin.addon.spreadsheet.command.CellValueCommand.java
License:Open Source License
/** * Returns the current value of the given Cell * // w ww . j a v a 2 s. c o m * @param cell * Target cell * @return Current value of the cell or null if not available */ protected Object getCellValue(Cell cell) { if (cell == null) { return null; } else { switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: return cell.getBooleanCellValue(); case Cell.CELL_TYPE_ERROR: return cell.getErrorCellValue(); case Cell.CELL_TYPE_FORMULA: return "=" + cell.getCellFormula(); case Cell.CELL_TYPE_NUMERIC: return cell.getNumericCellValue(); case Cell.CELL_TYPE_STRING: return cell.getStringCellValue(); default: return null; } } }
From source file:com.vaadin.addon.spreadsheet.ConditionalFormatter.java
/** * Checks if the given cell value matches a * {@link ConditionalFormattingRule} of <code>VALUE_IS</code> type. Covers * all cell types and comparison operations. * * @param cell/*from w w w . j av a 2 s . co m*/ * Target cell * @param rule * Conditional formatting rule to match against. * @return True if the given cells value matches the given * <code>VALUE_IS</code> rule, false otherwise */ protected boolean matchesValue(Cell cell, ConditionalFormattingRule rule) { boolean isFormulaType = cell.getCellType() == Cell.CELL_TYPE_FORMULA; boolean isFormulaStringType = isFormulaType && cell.getCachedFormulaResultType() == Cell.CELL_TYPE_STRING; boolean isFormulaBooleanType = isFormulaType && cell.getCachedFormulaResultType() == Cell.CELL_TYPE_BOOLEAN; boolean isFormulaNumericType = isFormulaType && cell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC; if (isFormulaType) { try { // make sure we have the latest value for formula cells getFormulaEvaluator().evaluateFormulaCell(cell); } catch (NotImplementedException e) { LOGGER.log(Level.FINEST, e.getMessage(), e); return false; } } // other than numerical types if (cell.getCellType() == Cell.CELL_TYPE_STRING || isFormulaStringType) { // Excel stores conditional formatting strings surrounded with ", so // we must surround the cell value. String cell value from POI is // never null. String quotedStringValue = String.format("\"%s\"", cell.getStringCellValue()); // Excel string comparison ignores case switch (rule.getComparisonOperation()) { case ComparisonOperator.EQUAL: return quotedStringValue.equalsIgnoreCase(rule.getFormula1()); case ComparisonOperator.NOT_EQUAL: return !quotedStringValue.equalsIgnoreCase(rule.getFormula1()); } } if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN || isFormulaBooleanType) { // not sure if this is used, since no boolean option exists in // Excel.. Boolean formulaVal = Boolean.parseBoolean(rule.getFormula1()); switch (rule.getComparisonOperation()) { case ComparisonOperator.EQUAL: return cell.getBooleanCellValue() == formulaVal; case ComparisonOperator.NOT_EQUAL: return cell.getBooleanCellValue() != formulaVal; } } // numerical types if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC || isFormulaNumericType) { double formula1Val = -1; try { formula1Val = Double.valueOf(rule.getFormula1()); } catch (NumberFormatException w) { // non-numeric formatting rules cannot match return false; } switch (rule.getComparisonOperation()) { case ComparisonOperator.EQUAL: return cell.getNumericCellValue() == formula1Val; case ComparisonOperator.NOT_EQUAL: return cell.getNumericCellValue() != formula1Val; case ComparisonOperator.LT: return cell.getNumericCellValue() < formula1Val; case ComparisonOperator.LE: return cell.getNumericCellValue() <= formula1Val; case ComparisonOperator.GT: return cell.getNumericCellValue() > formula1Val; case ComparisonOperator.GE: return cell.getNumericCellValue() >= formula1Val; case ComparisonOperator.BETWEEN: boolean lt = cell.getNumericCellValue() >= formula1Val; boolean gt = cell.getNumericCellValue() <= Double.valueOf(rule.getFormula2()); return lt && gt; case ComparisonOperator.NOT_BETWEEN: lt = cell.getNumericCellValue() <= formula1Val; gt = cell.getNumericCellValue() >= Double.valueOf(rule.getFormula2()); return lt && gt; } } return false; }
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();/*from w w w . ja v a 2 s.c o 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 .c om*/ 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.validation.manager.core.tool.table.extractor.TableExtractor.java
License:Apache License
public List<DefaultTableModel> extractTables() throws IOException, FileNotFoundException, ClassNotFoundException, VMException { List<DefaultTableModel> tables = new ArrayList<>(); if (source.getName().endsWith(".doc") || source.getName().endsWith(".docx") || source.getName().endsWith(".docm")) { //Word documents tables = loadSerializedTables(); } else if (source.getName().endsWith(".xls")) { //Pre Office 2007+ XML //Excel documents FileInputStream file = new FileInputStream(source); //Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(file); //Get first sheet from the workbook Sheet sheet = workbook.getSheetAt(0); //Get iterator to all the rows in current sheet Iterator<Row> rowIterator = sheet.iterator(); int rowNum = 0; int columns = 0; Map<Integer, ArrayList<Object>> data = new HashMap<>(); while (rowIterator.hasNext()) { ArrayList<Object> cells = new ArrayList<>(); Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); cells.add(cell.getStringCellValue().trim()); if (rowNum == 0) { columns++;/*ww w . j a va 2 s. co m*/ } } data.put(rowNum, cells); rowNum++; } //Process Object[][] data2 = new Object[rowNum][columns]; String[] title = new String[columns]; for (int i = 0; i < columns; i++) { title[i] = format("Column {0}", i + 1); } int row = 0; int col = 0; for (int i = 0; i < rowNum; i++) { for (Object obj : data.get(row)) { LOG.log(Level.FINE, "r: {0} c: {1} v: {2}", new Object[] { row, col, obj }); data2[row][col] = obj; col++; } row++; col = 0; } tables.add(new DefaultTableModel(data2, title)); } else if (source.getName().endsWith(".xlsx") || source.getName().endsWith(".xlsm")) { //Office 2007+ XML FileInputStream file = new FileInputStream(source); //Get the workbook instance for XLS file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); //Get iterator to all the rows in current sheet Iterator<Row> rowIterator = sheet.iterator(); int rowNum = 0; int columns = 0; Map<Integer, ArrayList<Object>> data = new HashMap<>(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); ArrayList<Object> cells = new ArrayList<>(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); cells.add(cell.getStringCellValue().trim()); if (rowNum == 0) { columns++; } } data.put(rowNum, cells); rowNum++; } //Process Object[][] data2 = new Object[rowNum][columns]; String[] title = new String[columns]; for (int i = 0; i < columns; i++) { title[i] = format("Column {0}", i + 1); } int row = 0, col = 0; for (int i = 0; i < rowNum; i++) { for (Object obj : data.get(row)) { LOG.log(Level.FINE, "r: {0} c: {1} v: {2}", new Object[] { row, col, obj }); data2[row][col] = obj; col++; } row++; col = 0; } tables.add(new DefaultTableModel(data2, title)); } else { throw new VMException(format("Invalid import file: {0}", source)); } return tables; }
From source file:com.veeduria.web.cargaarchivo.aut.th.CargaPlanta.java
public void cargarArchivoEmpleados(Path rutaArchivo) { //Get first sheet from the workbook try {//from ww w . ja v a2 s . c o m StringBuilder strBSql = new StringBuilder(); HSSFWorkbook workbook = new HSSFWorkbook(Files.newInputStream(rutaArchivo, StandardOpenOption.READ)); HSSFSheet sheet = workbook.getSheet("EMPLEADOS"); if (sheet != null) { Iterator<org.apache.poi.ss.usermodel.Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = (Row) rowIterator.next(); if (row.getRowNum() >= 10) { if (row.getCell(0) != null) { for (int i = 0; i < 42; i++) { Cell cell = row.getCell(i); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: strBSql.append(cell.getBooleanCellValue()); strBSql.append(","); // System.out.print(cell.getBooleanCellValue() + "\t\t"); break; case Cell.CELL_TYPE_NUMERIC: strBSql.append(cell.getNumericCellValue()); strBSql.append(","); // System.out.print(cell.getNumericCellValue() + "\t\t"); break; case Cell.CELL_TYPE_STRING: strBSql.append(",'"); strBSql.append(cell.getStringCellValue()); strBSql.append("',"); // System.out.print(cell.getStringCellValue() + "\t\t"); break; } } strBSql.replace(strBSql.length() - 1, strBSql.length(), ""); } } strBSql.append(System.getProperty("line.separator")); } System.out.println(""); } // FileOutputStream out = new FileOutputStream(new File(System.getProperty("user.home") + File.separator + "test.xls")); // workbook.write(out); // out.close(); } catch (IOException e) { Logger.getLogger(VigilarCarpetaSLBean.class.getName()).log(Level.SEVERE, null, e); } }
From source file:com.vermeg.convertisseur.service.ConvServiceImpl.java
@Override public JSONObject convert(String 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"); byte[] data = Base64.decodeBase64(file); FileOutputStream fos = new FileOutputStream(filez); fos.write(data);/*from w ww . j av a 2 s. c o m*/ fos.close(); //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(); if (cell.getCellType() == CELL_TYPE_NUMERIC) { if (HSSFDateUtil.isCellDateFormatted(cell)) { cells.put(cell.getDateCellValue()); } else cells.put(cell.getNumericCellValue()); } else cells.put(cell.getStringCellValue()); } jRow.put("cell", cells); rows.put(cells); //rows.put( jRow ); } // Create the JSON. json.put("rows", rows); System.out.println(json.toString()); return json; }