List of usage examples for org.apache.poi.ss.usermodel Cell getBooleanCellValue
boolean getBooleanCellValue();
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 . jav a 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.sf.jasperreports.engine.data.AbstractPoiXlsDataSource.java
License:Open Source License
/** * *//* ww w . ja v a 2s .co m*/ public Object getFieldValue(JRField jrField) throws JRException { String fieldName = jrField.getName(); Integer columnIndex = columnNames.get(fieldName); if (columnIndex == null && fieldName.startsWith("COLUMN_")) { columnIndex = Integer.valueOf(fieldName.substring(7)); } if (columnIndex == null) { throw new JRException("Unknown column name : " + fieldName); } Sheet sheet = workbook.getSheetAt(sheetIndex); Cell cell = sheet.getRow(recordIndex).getCell(columnIndex); Class<?> valueClass = jrField.getValueClass(); if (valueClass.equals(String.class)) { return cell.getStringCellValue(); } try { if (valueClass.equals(Boolean.class)) { if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { return cell.getBooleanCellValue(); } else { return convertStringValue(cell.getStringCellValue(), valueClass); } } else if (Number.class.isAssignableFrom(valueClass)) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { return convertNumber(cell.getNumericCellValue(), valueClass); } else { if (numberFormat != null) { return FormatUtils.getFormattedNumber(numberFormat, cell.getStringCellValue(), valueClass); } else { return convertStringValue(cell.getStringCellValue(), valueClass); } } } else if (Date.class.isAssignableFrom(valueClass)) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { return cell.getDateCellValue(); } else { if (dateFormat != null) { return FormatUtils.getFormattedDate(dateFormat, cell.getStringCellValue(), valueClass); } else { return convertStringValue(cell.getStringCellValue(), valueClass); } } } else { throw new JRException("Field '" + jrField.getName() + "' is of class '" + valueClass.getName() + "' and can not be converted"); } } catch (Exception e) { throw new JRException("Unable to get value for field '" + jrField.getName() + "' of class '" + valueClass.getName() + "'", e); } }
From source file:net.sf.taverna.t2.activities.spreadsheet.ExcelSpreadsheetReader.java
License:Open Source License
private String getCellValue(Cell cell, DataFormatter dataFormatter) { String value = null;/*ww w .j a va2 s .co m*/ switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: value = Boolean.toString(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { // value = cell.getDateCellValue().toString(); value = dataFormatter.formatCellValue(cell); } else { value = Double.toString(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_STRING: value = cell.getStringCellValue(); break; case Cell.CELL_TYPE_FORMULA: switch (cell.getCachedFormulaResultType()) { case Cell.CELL_TYPE_BOOLEAN: value = Boolean.toString(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { value = cell.getDateCellValue().toString(); } else { value = Double.toString(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_STRING: value = cell.getStringCellValue(); break; default: break; } default: break; } // value = dataFormatter.formatCellValue(cell); // if ("".equals(value)) value = null; return value; }
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 w w.ja v a2 s . com*/ throw new InvalidFormatException("Error en el formato de archivo"); } }
From source file:Opm_Package.OpenFileName.java
public List<String> readCommits(String file) throws Exception { // String excelFilePath = "Books.xlsx"; //FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); List<String> lists = new ArrayList<>(); Workbook workbook = readFileName(file); Sheet firstSheet = workbook.getSheetAt(1); Iterator<Row> iterator = firstSheet.iterator(); while (iterator.hasNext()) { Row nextRow = iterator.next();// w w w . j a va2 s . c om Iterator<Cell> cellIterator = nextRow.cellIterator(); int y = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: if (y > 7 & !cell.getStringCellValue().equals("-")) { lists.add(cell.getStringCellValue()); } //System.out.print(cell.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: System.out.print(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: System.out.print(cell.getNumericCellValue()); break; } // System.out.print(" - "); y++; } //System.out.println(); } workbook.close(); // inputStream.close(); for (int x = 0; x < lists.size(); x++) { String[] splits = lists.get(x).split(":-"); //System.out.println(lists.get(x)+"\t length = "+splits.length); } return lists; }
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); }//from w ww .ja v a 2 s .c om } } } } 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 {/*from ww w.j a v a 2s . c om*/ 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: }/*from w ww .ja v a2 s . c om*/ } insertData(lstItem, true); } } }
From source file:org.ActSrnv03.core.ResidencialTopComponent.java
public void showExelData(List sheetData) { ///*from ww w .ja va2 s. c o m*/ // Iterates the data and print it out to the console. // for (int i = 0; i < sheetData.size(); i++) { List list = (List) sheetData.get(i); for (int j = 0; j < list.size(); j++) { Cell cell = (Cell) list.get(j); if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { System.out.print(cell.getNumericCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { System.out.print(cell.getRichStringCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { System.out.print(cell.getBooleanCellValue()); } if (j < list.size() - 1) { System.out.print(", "); } } System.out.println(""); } }
From source file:org.ActSrnv03.core.ResidencialTopComponent.java
public void procesaCertificaciones(List sheetData) { ///*from w w w . ja v a2 s. c o m*/ // Iterates the data and print it out to the console. // int cnt = 0; SimpleDateFormat formatDateJava = new SimpleDateFormat("dd-MM-yyyy"); HSSFRichTextString richTextString; for (int i = 1; i < sheetData.size(); i++) { List list = (List) sheetData.get(i); System.out.println(i + " -> Tenemos Exel con " + list.size() + " columnas"); if (list.size() >= 26) { for (int j = 0; j < list.size(); j++) { Cell cell = (Cell) list.get(j); this.tablaCertificaciones[cnt][j] = ""; if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { this.tablaCertificaciones[cnt][j] = Double.toString(cell.getNumericCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { richTextString = (HSSFRichTextString) cell.getRichStringCellValue(); // System.out.print(cell.getRichStringCellValue()); this.tablaCertificaciones[cnt][j] = richTextString.getString(); } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { this.tablaCertificaciones[cnt][j] = Boolean.toString(cell.getBooleanCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { this.tablaCertificaciones[cnt][j] = ""; } } this.tablaCertificaciones[cnt][30] = "-1"; System.out.println(cnt + " -> Cargo registro con NIF=" + this.tablaCertificaciones[cnt][11] + " Y FECHA =" + this.tablaCertificaciones[cnt][3]); cnt++; } // System.out.println("---------"); } this.nCertificaciones = cnt; System.out.println( "----------- HE CARGADO " + this.nCertificaciones + " REGISTROS DE CERTIFICACIONES ------------"); JOptionPane.showMessageDialog(null, "\nHE CARGADO:" + this.nCertificaciones + " CERTIFICACIONES", "INFORMACIN", JOptionPane.WARNING_MESSAGE); }