List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet rowIterator
@Override @SuppressWarnings("unchecked") public Iterator<Row> rowIterator()
From source file:Model.LeerCaminos.java
public LeerCaminos() { boolean bandera = true; JFileChooser Examinar = new JFileChooser(); Examinar.setFileFilter(new FileNameExtensionFilter("Archivos excel", "xls", "xlsx")); int opcion = Examinar.showOpenDialog(Examinar); File fileName = null;/* www .j av a 2 s. c o m*/ List cellData = new ArrayList(); if (opcion == JFileChooser.APPROVE_OPTION) { fileName = Examinar.getSelectedFile().getAbsoluteFile(); try { FileInputStream fileInputStream = new FileInputStream(fileName); XSSFWorkbook workBook = new XSSFWorkbook(fileInputStream); XSSFSheet hssfSheet = workBook.getSheetAt(0); Iterator rowIterator = hssfSheet.rowIterator(); while (rowIterator.hasNext()) { XSSFRow hssfRow = (XSSFRow) rowIterator.next(); Iterator iterator = hssfRow.cellIterator(); if (iterator != null) { List cellTemp = new ArrayList(); while (iterator.hasNext()) { XSSFCell hssfCell = (XSSFCell) iterator.next(); cellTemp.add(hssfCell); } cellData.add(cellTemp); } } } catch (Exception e) { e.printStackTrace(); } Llenarmatriz(cellData); // ImprimirMatriz(); } }
From source file:mx.infotec.dads.arq.excel.ImplementExcel.java
@Override public List<Object[]> GetFilasColumnas(XSSFSheet sheet) throws ExcelException { List<Object[]> lst = new ArrayList<>(); try {//from w w w.j av a 2 s .c o m XSSFRow row; Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { row = (XSSFRow) rows.next(); lst.add(getFilaArreglo(row)); } } catch (Exception e) { throw new ExcelException("Error al procesar excel", e); } return lst; }
From source file:nl.rabobank.fixtures.aiep.testdata.traffic.LicensePlateDataLoader.java
public void fetchTestData() throws MalformedURLException, IOException { InputStream excelFileToRead = null; if (isTest) { excelFileToRead = getClass().getResourceAsStream("/aiep_testdata_fitnesse.xlsx"); } else {//from www . ja v a2 s . c o m excelFileToRead = new URL(TestDataLoader.TESTDATAPATH).openStream(); } XSSFWorkbook wb = new XSSFWorkbook(excelFileToRead); XSSFSheet sheet = wb.getSheetAt(0); Iterator<Row> rows = sheet.rowIterator(); boolean newRow = true; List<Row> myRowList = Lists.newArrayList(rows); if (this.testStubObject != null) { for (Row row : myRowList) { Iterator<Cell> cells = row.cellIterator(); List<Cell> myRowCellsList = Lists.newArrayList(cells); for (Cell cell : myRowCellsList) { cell.setCellType(Cell.CELL_TYPE_STRING); if (cell.getStringCellValue().equals(this.testStubObject) && cell.getColumnIndex() == 0) { newRow = false; } if (cell.getColumnIndex() == 1 && !newRow) { setLicenseplate(cell.getStringCellValue()); } if (cell.getColumnIndex() == 2 && !newRow) { setBrand(cell.getStringCellValue()); } if (cell.getColumnIndex() == 3 && !newRow) { setModel(cell.getStringCellValue()); } if (cell.getColumnIndex() == 4 && !newRow) { setType(cell.getStringCellValue()); } if (cell.getColumnIndex() == 5 && !newRow) { setGear(cell.getStringCellValue()); } if (cell.getColumnIndex() == 6 && !newRow) { setFuel(cell.getStringCellValue()); } if (cell.getColumnIndex() == 7 && !newRow) { setProductionYear(cell.getStringCellValue()); } if (cell.getColumnIndex() == 8 && !newRow) { setPurchaseYear(cell.getStringCellValue()); } if (cell.getColumnIndex() == 9 && this.testStubObject.startsWith("Motor") && !newRow) { setSideCarValue(cell.getStringCellValue()); } if (cell.getColumnIndex() == 10 && this.testStubObject.startsWith("Trailer")) { setChassisNumber(cell.getStringCellValue()); } if (cell.getColumnIndex() == 11 && !newRow) { setWeight(cell.getStringCellValue()); } if (cell.getColumnIndex() == 12 && !newRow) { setPurchaseValue(cell.getStringCellValue()); } if (cell.getColumnIndex() == 13 && !newRow) { setInsuredInventory(cell.getStringCellValue()); } if (cell.getColumnIndex() == 14 && !newRow) { setSameLicensePlateAsCar(cell.getStringCellValue()); } if (cell.getColumnIndex() == 15 && !newRow) { setCatalogueValue(cell.getStringCellValue()); } if (cell.getColumnIndex() == 16 && !newRow) { setBodyShape(cell.getStringCellValue()); } } if (!newRow) { break; } } } excelFileToRead.close(); }
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 w w. j ava 2s.co m } } } } 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:org.nuxeo.ecm.core.convert.plugins.text.extractors.XLX2TextConverter.java
License:Apache License
@Override public BlobHolder convert(BlobHolder blobHolder, Map<String, Serializable> parameters) throws ConversionException { InputStream stream = null;//from ww w.j a v a2 s . c o m StringBuffer sb = new StringBuffer(); try { Blob blob = blobHolder.getBlob(); if (blob.getLength() > maxSize4POI) { return runFallBackConverter(blobHolder, "xl/"); } stream = blob.getStream(); OPCPackage p = OPCPackage.open(stream); XSSFWorkbook workbook = new XSSFWorkbook(p); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { XSSFSheet sheet = workbook.getSheetAt(i); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { XSSFRow row = (XSSFRow) rows.next(); Iterator<Cell> cells = row.cellIterator(); while (cells.hasNext()) { XSSFCell cell = (XSSFCell) cells.next(); appendTextFromCell(cell, sb); } sb.append(ROW_SEP); } } return new SimpleCachableBlobHolder(Blobs.createBlob(sb.toString())); } catch (IOException | OpenXML4JException e) { throw new ConversionException("Error during XLX2Text conversion", e); } finally { if (stream != null) { try { stream.close(); } catch (IOException e) { log.error("Error while closing Blob stream", e); } } } }
From source file:org.pentaho.reporting.engine.classic.core.RotationTest.java
License:Open Source License
@Test public void testXLSX() throws ResourceException, IOException { URL url = getClass().getResource("BACKLOG-6818.prpt"); final File testOutputFile = File.createTempFile("test", ".xlsx"); MasterReport report = (MasterReport) new ResourceManager().createDirectly(url, MasterReport.class) .getResource();/*from w w w.ja v a 2 s .c om*/ try (FileOutputStream stream = new FileOutputStream(testOutputFile)) { ExcelReportUtil.createXLSX(report, stream); XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(testOutputFile)); assertNotNull(workbook); final XSSFSheet sheet = workbook.getSheetAt(0); assertNotNull(sheet); final Iterator<Row> rowIterator = sheet.rowIterator(); assertNotNull(rowIterator); final Row next = rowIterator.next(); assertNotNull(next); int k = 1; for (int i = 0; i < 5; i++, k = -k) { final Cell cell = next.getCell(i); assertNotNull(cell); assertTrue(cell.getCellStyle().getRotation() == (k > 0 ? 90 : 180)); } for (int i = 6; i < 9; i++, k = -k) { final Cell cell = next.getCell(i); assertNull(cell); } } catch (IOException | ReportProcessingException e) { fail(); } finally { assertTrue(testOutputFile.delete()); } }
From source file:org.protempa.test.XlsxDataProvider.java
License:Apache License
/** * Parse the list of patients from the workbook. * * @return A list of {@link Patient} objects. *//*w w w .jav a 2 s. c o m*/ private List<Patient> readPatients() { XSSFSheet sheet = this.workbook.getSheet("patient"); List<Patient> result = new ArrayList<>(); Iterator<Row> rows = sheet.rowIterator(); rows.next(); // skip header row while (rows.hasNext()) { Row row = rows.next(); Patient patient = new Patient(); patient.setId(XlsxDataProvider.readLongValue(row.getCell(0))); patient.setFirstName(XlsxDataProvider.readStringValue(row.getCell(1))); patient.setLastName(XlsxDataProvider.readStringValue(row.getCell(2))); patient.setDateOfBirth(XlsxDataProvider.readDateValue(row.getCell(3))); patient.setLanguage(XlsxDataProvider.readStringValue(row.getCell(4))); patient.setMaritalStatus(XlsxDataProvider.readStringValue(row.getCell(5))); patient.setRace(XlsxDataProvider.readStringValue(row.getCell(6))); patient.setGender(XlsxDataProvider.readStringValue(row.getCell(7))); patient.setCreateDate(XlsxDataProvider.readDateValue(row.getCell(8))); patient.setUpdateDate(XlsxDataProvider.readDateValue(row.getCell(9))); patient.setDeleteDate(XlsxDataProvider.readDateValue(row.getCell(10))); result.add(patient); } return result; }
From source file:org.protempa.test.XlsxDataProvider.java
License:Apache License
/** * Parse the list of providers in the workbook. * * @return A list of {@link Provider} objects. */// w ww.j a v a2 s . c o m private List<Provider> readProviders() { XSSFSheet sheet = this.workbook.getSheet("provider"); List<Provider> result = new ArrayList<>(); Iterator<Row> rows = sheet.rowIterator(); rows.next(); // skip header row while (rows.hasNext()) { Row row = rows.next(); Provider provider = new Provider(); provider.setId(XlsxDataProvider.readLongValue(row.getCell(0))); provider.setFirstName(XlsxDataProvider.readStringValue(row.getCell(1))); provider.setLastName(XlsxDataProvider.readStringValue(row.getCell(2))); provider.setCreateDate(XlsxDataProvider.readDateValue(row.getCell(3))); provider.setUpdateDate(XlsxDataProvider.readDateValue(row.getCell(4))); provider.setDeleteDate(XlsxDataProvider.readDateValue(row.getCell(5))); result.add(provider); } return result; }
From source file:org.protempa.test.XlsxDataProvider.java
License:Apache License
/** * Parse the list of encounters in the workbook. * * @return A list of {@link Encounter} objects. *//*from ww w.j av a 2 s . co m*/ private List<Encounter> readEncounters() { XSSFSheet sheet = this.workbook.getSheet("encounter"); List<Encounter> result = new ArrayList<>(); Iterator<Row> rows = sheet.rowIterator(); rows.next(); // skip header row while (rows.hasNext()) { Row row = rows.next(); Encounter encounter = new Encounter(); encounter.setId(XlsxDataProvider.readLongValue(row.getCell(0))); encounter.setPatientId(XlsxDataProvider.readLongValue(row.getCell(1))); encounter.setProviderId(XlsxDataProvider.readLongValue(row.getCell(2))); encounter.setStart(XlsxDataProvider.readDateValue(row.getCell(3))); encounter.setEnd(XlsxDataProvider.readDateValue(row.getCell(4))); encounter.setType(XlsxDataProvider.readStringValue(row.getCell(5))); encounter.setDischargeDisposition(XlsxDataProvider.readStringValue(row.getCell(6))); encounter.setCreateDate(XlsxDataProvider.readDateValue(row.getCell(7))); encounter.setUpdateDate(XlsxDataProvider.readDateValue(row.getCell(8))); encounter.setDeleteDate(XlsxDataProvider.readDateValue(row.getCell(9))); result.add(encounter); } return result; }
From source file:org.protempa.test.XlsxDataProvider.java
License:Apache License
/** * Parse the list of CPT codes in the workbook. * * @return A list of {@link CPT} objects. *//*from w w w .j a v a2 s . c om*/ private List<CPT> readCpts() { XSSFSheet sheet = this.workbook.getSheet("eCPT"); List<CPT> result = new ArrayList<>(); Iterator<Row> rows = sheet.rowIterator(); rows.next(); // skip header row while (rows.hasNext()) { Row row = rows.next(); CPT cpt = new CPT(); cpt.setId(XlsxDataProvider.readStringValue(row.getCell(0))); cpt.setEncounterId(XlsxDataProvider.readLongValue(row.getCell(1))); cpt.setTimestamp(XlsxDataProvider.readDateValue(row.getCell(2))); cpt.setEntityId(XlsxDataProvider.readStringValue(row.getCell(3))); cpt.setCreateDate(XlsxDataProvider.readDateValue(row.getCell(4))); cpt.setUpdateDate(XlsxDataProvider.readDateValue(row.getCell(5))); cpt.setDeleteDate(XlsxDataProvider.readDateValue(row.getCell(6))); result.add(cpt); } return result; }