List of usage examples for org.apache.poi.ss.usermodel Cell getCellFormula
String getCellFormula();
SUM(C4:E4) From source file:net.sf.excelutils.WorkbookUtils.java
License:Apache License
public static void shiftCell(Sheet sheet, Row row, Cell beginCell, int shift, int rowCount) { if (shift == 0) return;//from w w w . j a va 2 s . c om // get the from & to row int fromRow = row.getRowNum(); int toRow = row.getRowNum() + rowCount - 1; for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress r = sheet.getMergedRegion(i); if (r.getFirstRow() == row.getRowNum()) { if (r.getLastRow() > toRow) { toRow = r.getLastRow(); } if (r.getFirstRow() < fromRow) { fromRow = r.getFirstRow(); } } } for (int rownum = fromRow; rownum <= toRow; rownum++) { Row curRow = WorkbookUtils.getRow(rownum, sheet); int lastCellNum = curRow.getLastCellNum(); for (int cellpos = lastCellNum; cellpos >= beginCell.getColumnIndex(); cellpos--) { Cell fromCell = WorkbookUtils.getCell(curRow, cellpos); Cell toCell = WorkbookUtils.getCell(curRow, cellpos + shift); toCell.setCellType(fromCell.getCellType()); toCell.setCellStyle(fromCell.getCellStyle()); switch (fromCell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: toCell.setCellValue(fromCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: toCell.setCellFormula(fromCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: toCell.setCellValue(fromCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: toCell.setCellValue(fromCell.getStringCellValue()); break; case Cell.CELL_TYPE_ERROR: toCell.setCellErrorValue(fromCell.getErrorCellValue()); break; } fromCell.setCellValue(""); fromCell.setCellType(Cell.CELL_TYPE_BLANK); // Workbook wb = new Workbook(); // CellStyle style = wb.createCellStyle(); // fromCell.setCellStyle(style); } // process merged region for (int cellpos = lastCellNum; cellpos >= beginCell.getColumnIndex(); cellpos--) { Cell fromCell = WorkbookUtils.getCell(curRow, cellpos); List shiftedRegions = new ArrayList(); for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress r = sheet.getMergedRegion(i); if (r.getFirstRow() == curRow.getRowNum() && r.getFirstColumn() == fromCell.getColumnIndex()) { r.setFirstColumn((short) (r.getFirstColumn() + shift)); r.setLastColumn((short) (r.getLastColumn() + shift)); // have to remove/add it back shiftedRegions.add(r); sheet.removeMergedRegion(i); // we have to back up now since we removed one i = i - 1; } } // readd so it doesn't get shifted again Iterator iterator = shiftedRegions.iterator(); while (iterator.hasNext()) { CellRangeAddress region = (CellRangeAddress) iterator.next(); sheet.addMergedRegion(region); } } } }
From source file:net.sibcolombia.sibsp.service.portal.implementation.ResourceManagerImplementation.java
License:Creative Commons License
private String readCellValue(Cell cell) throws InvalidFormatException { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: return cell.getStringCellValue(); case Cell.CELL_TYPE_NUMERIC: return Double.toString(cell.getNumericCellValue()); case Cell.CELL_TYPE_BOOLEAN: return Boolean.toString(cell.getBooleanCellValue()); case Cell.CELL_TYPE_FORMULA: return cell.getCellFormula(); case Cell.CELL_TYPE_ERROR: throw new InvalidFormatException("Error en el formato de archivo"); case Cell.CELL_TYPE_BLANK: return ""; default://from w ww .j a va 2 s . c o m throw new InvalidFormatException("Error en el formato de archivo"); } }
From source file:opn.greenwebs.FXMLDocumentController.java
@FXML private void handleQuoteClick(MouseEvent event) { MouseButton butt = event.getButton(); if (butt == MouseButton.PRIMARY) { if (event.getClickCount() == 2) { if (event.getSource() instanceof TableView) { TableView view = (TableView) event.getSource(); if (view.getSelectionModel().getSelectedItem() instanceof Quote) { Quote quote = (Quote) view.getSelectionModel().getSelectedItem(); File[] fileList = fQuoteDir .listFiles((File file) -> file.getName().startsWith(quote.getSspQuote())); if (fileList.length > 0) { try { Desktop.getDesktop().open(fileList[0]); } catch (IOException ex) { Logger.getLogger(FXMLDocumentController.class.getName()).log(Level.SEVERE, null, ex); }//w ww . jav a2 s .com } } } } else if (event.getSource() instanceof TableView) { TableView view = (TableView) event.getSource(); if (view.getSelectionModel().getSelectedItem() instanceof Quote) { Quote quote = (Quote) view.getSelectionModel().getSelectedItem(); txtCustomer.setText(quote.getSspCustomer()); txtPhone.setText(quote.getSspPhone()); File[] fileList = fQuoteDir .listFiles((File file) -> file.getName().startsWith(quote.getSspQuote())); if (fileList.length > 0) { System.out.println("the filelist is bigger than 0"); try (FileInputStream fis = new FileInputStream(fileList[0]); XSSFWorkbook book = new XSSFWorkbook(fis)) { ItemData.clear(); XSSFSheet sheet = book.getSheet("Digital Version"); Iterator<Row> itr = sheet.rowIterator(); int nRow = 0; mainWhile: while (itr.hasNext()) { Row row = itr.next(); nRow++; if (nRow >= 21) { Iterator<Cell> cellIterator = row.cellIterator(); List lstItem = new ArrayList(); int nCell = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: lstItem.add(cell.getStringCellValue()); break; case Cell.CELL_TYPE_NUMERIC: lstItem.add(cell.getNumericCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: lstItem.add(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_BLANK: lstItem.add(""); if (nCell == 0) { System.out.println("Quote Qty found a blank"); break mainWhile; } break; case Cell.CELL_TYPE_FORMULA: lstItem.add(cell.getCellFormula()); break; default: } nCell++; } insertData(lstItem, false); } else if (nRow < 21) { /* getCellData(file, row, cell); Row rowed = sheetStock.getRow(6); Cell celled = rowed.getCell(10); CellStyle cellStyle = celled.getCellStyle(); XSSFFont font = sheetStock.getWorkbook().createFont(); font.setFontHeight(14); cellStyle.setFont(font); celled.setCellValue(Date.from(instant)); celled.setCellStyle(cellStyle); rowed = sheetStock.getRow(10); celled = rowed.getCell(2); inject(wb, Date.from(instant), 3, 14); inject(wb, txtCustomer.getText(), 10, 2); inject(wb, txtAddress.getText(), 11, 2); inject(wb, txtCity.getText(), 12, 2); inject(wb, txtProvince.getText(), 13, 2); inject(wb, txtPhone.getText(), 14, 2); inject(wb, txtContact.getText(), 15, 2); inject(wb, txtFax.getText(), 14, 4); inject(wb, txtEmail.getText(), 16, 2); inject(wb, txtPO.getText(), 15, 4); if (chkGST.isSelected()) { inject(wb, "Y", 36, 8); } else { inject(wb, "N", 36, 8); } if (chkPST.isSelected()) { inject(wb, "Y", 37, 8); } else { inject(wb, "N", 37, 8); Data starts at row 21 Qty, Mfr, Sku, Descrip, Supplier, Suppart,Serial,SalePrice,EOS */ } } System.out.println("completely out of the while"); } catch (FileNotFoundException fe) { } catch (IOException ex) { Logger.getLogger(FXMLDocumentController.class.getName()).log(Level.SEVERE, null, ex); } } } } } }
From source file:opn.greenwebs.FXMLDocumentController.java
private void getData() { try {/* w ww . ja v a2s . co m*/ fStockDir = new File(fUserDir, "Stock"); if (!fStockDir.exists()) { fStockDir.mkdirs(); } FilenameFilter xlsxFilter = (File dir, String name) -> { String lowercaseName = name.toLowerCase(); return lowercaseName.endsWith(".xlsx"); }; File[] fStock = fStockDir.listFiles(xlsxFilter); for (File excel : fStock) { try (FileInputStream fis = new FileInputStream(excel); XSSFWorkbook book = new XSSFWorkbook(fis)) { XSSFSheet sheet = book.getSheet("Digital Version"); List<XSSFTable> lstTables = sheet.getTables(); if (!lstTables.isEmpty()) { shootTables(sheet, lstTables); } else { //System.out.println("we have one without a table"); Iterator<Row> itr = sheet.iterator(); boolean bData = false; int nRow = 0; while (itr.hasNext()) { Row row = itr.next(); int nCou = 0; Iterator<Cell> cellIterator = row.cellIterator(); List lstItem = new ArrayList(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: String strCell = cell.getStringCellValue(); if (strCell.equalsIgnoreCase("Qty")) { nCou = nRow + 1; } if (bData) { lstItem.add(strCell); } break; case Cell.CELL_TYPE_NUMERIC: if (bData) { lstItem.add(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: if (bData) { lstItem.add(cell.getBooleanCellValue()); } break; case Cell.CELL_TYPE_BLANK: if (bData) { lstItem.add(""); } break; case Cell.CELL_TYPE_FORMULA: if (bData) { lstItem.add(cell.getCellFormula()); } break; default: } } if (bData) { System.out.print(lstItem); insertData(lstItem, true); } nRow++; if (nCou == nRow) { bData = true; } } //bIsGood = false; } } } } catch (FileNotFoundException fe) { //System.out.println("File not found exception " + fe.getMessage()); } catch (IOException ie) { //System.out.println("IO Exception " + ie.getMessage()); } }
From source file:opn.greenwebs.FXMLDocumentController.java
private void shootTables(XSSFSheet sheet, List<XSSFTable> list) { for (XSSFTable xTable : list) { for (int j = xTable.getStartCellReference().getRow(); j < xTable.getEndCellReference().getRow(); j++) { Row row = sheet.getRow(j + 1); List lstItem = new ArrayList(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: String strCell = cell.getStringCellValue(); lstItem.add(strCell); break; case Cell.CELL_TYPE_NUMERIC: lstItem.add(cell.getNumericCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: lstItem.add(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_BLANK: lstItem.add(""); break; case Cell.CELL_TYPE_FORMULA: lstItem.add(cell.getCellFormula()); break; default: }/* w ww . j a va 2 s.com*/ } insertData(lstItem, true); } } }
From source file:org.alanwilliamson.openbd.plugin.spreadsheet.functions.SpreadsheetGetCellFormula.java
License:Open Source License
private cfData getAllFormulaForSheet(cfSession _session, cfSpreadSheetData spreadsheet) throws cfmRunTimeException { cfArrayData array = cfArrayListData.createArray(1); Iterator<Row> rowIt = spreadsheet.getActiveSheet().rowIterator(); while (rowIt.hasNext()) { Row row = rowIt.next();// w w w . j a v a2 s . co m Iterator<Cell> cellIt = row.cellIterator(); while (cellIt.hasNext()) { Cell cell = cellIt.next(); if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { cfStructData s = new cfStructData(); s.setData("formula", new cfStringData(cell.getCellFormula())); s.setData("row", new cfNumberData(row.getRowNum() + 1)); s.setData("column", new cfNumberData(cell.getColumnIndex() + 1)); array.addElement(s); } } } return array; }
From source file:org.alanwilliamson.openbd.plugin.spreadsheet.SheetUtility.java
License:Open Source License
public static void cloneCell(Cell cNew, Cell cOld) { cNew.setCellComment(cOld.getCellComment()); cNew.setCellStyle(cOld.getCellStyle()); cNew.setCellType(cOld.getCellType()); switch (cNew.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: { cNew.setCellValue(cOld.getBooleanCellValue()); break;/*from w w w . j a v a 2s. c om*/ } case Cell.CELL_TYPE_NUMERIC: { cNew.setCellValue(cOld.getNumericCellValue()); break; } case Cell.CELL_TYPE_STRING: { cNew.setCellValue(cOld.getStringCellValue()); break; } case Cell.CELL_TYPE_ERROR: { cNew.setCellValue(cOld.getErrorCellValue()); break; } case Cell.CELL_TYPE_FORMULA: { cNew.setCellFormula(cOld.getCellFormula()); break; } case Cell.CELL_TYPE_BLANK: { cNew.setCellValue(cOld.getNumericCellValue()); break; } } }
From source file:org.apache.metamodel.excel.ExcelUtils.java
License:Apache License
private static String getFormulaCellValue(Workbook wb, Cell cell) { // first try with a cached/precalculated value try {/*from w ww . ja va 2 s . c om*/ double numericCellValue = cell.getNumericCellValue(); // TODO: Consider not formatting it, but simple using // Double.toString(...) return _numberFormat.format(numericCellValue); } catch (Exception e) { if (logger.isInfoEnabled()) { logger.info("Failed to fetch cached/precalculated formula value of cell: " + cell, e); } } // evaluate cell first, if possible try { if (logger.isInfoEnabled()) { logger.info("cell({},{}) is a formula. Attempting to evaluate: {}", new Object[] { cell.getRowIndex(), cell.getColumnIndex(), cell.getCellFormula() }); } final FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); // calculates the formula and puts it's value back into the cell final Cell evaluatedCell = evaluator.evaluateInCell(cell); return getCellValue(wb, evaluatedCell); } catch (RuntimeException e) { logger.warn("Exception occurred while evaluating formula at position ({},{}): {}", new Object[] { cell.getRowIndex(), cell.getColumnIndex(), e.getMessage() }); // Some exceptions we simply log - result will be then be the // actual formula if (e instanceof FormulaParseException) { logger.error("Parse exception occurred while evaluating cell formula: " + cell, e); } else if (e instanceof IllegalArgumentException) { logger.error("Illegal formula argument occurred while evaluating cell formula: " + cell, e); } else { logger.error("Unexpected exception occurred while evaluating cell formula: " + cell, e); } } // last resort: return the string formula return cell.getCellFormula(); }
From source file:org.azkfw.datasource.excel.ExcelDatasourceBuilder.java
License:Apache License
private String toStringFromCell(final Cell aCell) { // ??? String string = ""; if (null != aCell) { switch (aCell.getCellType()) { case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_BOOLEAN: string = Boolean.toString(aCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: string = aCell.getCellFormula(); // string = cell.getStringCellValue();( break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(aCell)) { java.util.Date dt = aCell.getDateCellValue(); string = (new SimpleDateFormat("yyyy/MM/dd HH:mm:ss")).format(dt); } else { string = Double.toString(aCell.getNumericCellValue()); }//from w ww . j a va2s. c om break; case Cell.CELL_TYPE_STRING: { string = aCell.getStringCellValue(); break; } case Cell.CELL_TYPE_ERROR: { break; } } } return string; }
From source file:org.azkfw.datasource.excel.ExcelDatasourceFactory.java
License:Apache License
private static String toStringFromCell(final Cell aCell) { // ??? String string = ""; if (null != aCell) { switch (aCell.getCellType()) { case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_BOOLEAN: string = Boolean.toString(aCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: string = aCell.getCellFormula(); // string = cell.getStringCellValue();( break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(aCell)) { java.util.Date dt = aCell.getDateCellValue(); string = (new SimpleDateFormat("yyyy/MM/dd HH:mm:ss")).format(dt); } else { string = Double.toString(aCell.getNumericCellValue()); }//ww w .j ava 2 s . c o m break; case Cell.CELL_TYPE_STRING: { string = aCell.getStringCellValue(); break; } case Cell.CELL_TYPE_ERROR: { break; } } } return string; }