List of usage examples for org.apache.poi.ss.usermodel Sheet rowIterator
Iterator<Row> rowIterator();
From source file:IO.FILES.java
public Persona getById(String id) { if (id.equals("")) return null; Persona p = null;//from ww w. j a v a 2 s .co m try { Workbook wb = WorkbookFactory.create(new FileInputStream(new File(ruta))); Sheet hoja = wb.getSheetAt(0); Iterator rows = hoja.rowIterator(); while (rows.hasNext()) { Row row = (Row) rows.next(); if (row.getCell(0).getStringCellValue().equalsIgnoreCase(id)) { p = toPersona(row); } } } catch (IOException | InvalidFormatException | EncryptedDocumentException ex) { Logger.getLogger(FILES.class.getName()).log(Level.SEVERE, null, ex); } return p; }
From source file:IO.FILES.java
public ArrayList<Persona> busquedaConFiltro(Busqueda v) { ArrayList<Persona> p = new ArrayList(); try {//from www.j a va 2s . c o m Workbook wb = WorkbookFactory.create(new FileInputStream(new File(ruta))); Sheet hoja = wb.getSheetAt(0); Iterator rows = hoja.rowIterator(); Row row = (Row) rows.next(); while (rows.hasNext()) { row = (Row) rows.next(); Persona p2 = toPersona(row); if (is(v, p2)) p.add(p2); } } catch (IOException | InvalidFormatException | EncryptedDocumentException ex) { Logger.getLogger(FILES.class.getName()).log(Level.SEVERE, null, ex); } return p; }
From source file:IO.REDACCIONES.java
public static void write(Workbook wb, Persona p, String texto) throws FileNotFoundException, IOException { if ((p == null) || (wb == null)) return;/*from w w w.j av a2 s .c o m*/ Sheet h = wb.getSheetAt(1); int inx = 0; Iterator it = h.rowIterator(); while (it.hasNext()) { inx++; it.next(); } Row row = h.createRow(inx); Cell cell1 = row.createCell(0); cell1.setCellValue(p.getId()); Cell cell2 = row.createCell(1); cell2.setCellValue(texto); }
From source file:IO.REDACCIONES.java
public static void overWrite(Workbook wb, Persona p, String texto) throws FileNotFoundException, IOException { if ((p == null) || (wb == null)) return;// w w w . j ava2 s.co m Sheet h = wb.getSheetAt(1); boolean encontrado = false; Row row = null; Iterator it = h.rowIterator(); while (it.hasNext()) { row = (Row) it.next(); if (row.getCell(0).getStringCellValue().equalsIgnoreCase(p.getId())) { encontrado = true; } } if (!encontrado) return; int aux = row.getRowNum(); h.removeRow(row); row = h.createRow(aux); Cell cell1 = row.createCell(0); cell1.setCellValue(p.getId()); Cell cell2 = row.createCell(1); cell2.setCellValue(texto); }
From source file:IO.REDACCIONES.java
public static void remove(String id, Workbook wb) { try {/*from w w w.j a v a 2 s . c om*/ Sheet hoja = wb.getSheetAt(1); Iterator rows = hoja.rowIterator(); while (rows.hasNext()) { Row row = (Row) rows.next(); if (row.getCell(0).getStringCellValue().equalsIgnoreCase(id)) { FILES.removeRow(hoja, row); break; } } } catch (EncryptedDocumentException ex) { Logger.getLogger(FILES.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:IO.REDACCIONES.java
public static String read(String id) throws FileNotFoundException, IOException { String salida = ""; try {// w ww .j a v a 2s . co m Workbook wb = WorkbookFactory.create(new FileInputStream(new File(FILES.ruta))); Sheet hoja = wb.getSheetAt(1); Iterator rows = hoja.rowIterator(); while (rows.hasNext()) { Row row = (Row) rows.next(); if (row.getCell(0).getStringCellValue().equalsIgnoreCase(id)) { salida = row.getCell(1).getStringCellValue(); break; } } } catch (IOException | InvalidFormatException | EncryptedDocumentException ex) { salida = ""; Logger.getLogger(FILES.class.getName()).log(Level.SEVERE, null, ex); } return salida; }
From source file:jp.qpg.ExcelTo.java
License:Apache License
/** * excel to pdf//from w w w. jav a2s . com * * @param book excel workbook * @param out output to pdf * @param documentSetup document setup * @throws IOException I/O exception */ public static void pdf(Workbook book, OutputStream out, Consumer<PDFPrinter> documentSetup) throws IOException { Objects.requireNonNull(book); Objects.requireNonNull(out); try (PDFPrinter printer = new PDFPrinter()) { printer.documentSetup = Optional.ofNullable(documentSetup); for (int i = 0, end = book.getNumberOfSheets(); i < end; i++) { Sheet sheet = book.getSheetAt(i); int rowCount = sheet.getPhysicalNumberOfRows(); if (rowCount <= 0) { logger.info(sheet.getSheetName() + ": empty"); continue; /* skip blank sheet */ } logger.info(sheet.getSheetName() + ": " + rowCount + " rows"); printer.println("sheet name: " + sheet.getSheetName()); printer.println("max row index: " + sheet.getLastRowNum()); printer.println("max column index: " + Tool.stream(sheet.rowIterator(), rowCount).mapToInt(Row::getLastCellNum).max().orElse(0)); eachCell(sheet, (cell, range) -> Tool.cellValue(cell).ifPresent(value -> printer.println( '[' + (range == null ? new CellReference(cell).formatAsString() : range.formatAsString()) + "] " + value))); eachShape(sheet, shapeText(text -> printer.println("[shape text] " + text))); printer.newPage(); } printer.getDocument().save(out); } }
From source file:jp.qpg.ExcelTo.java
License:Apache License
/** * excel to text// ww w . ja va 2 s .c o m * * @param book excel workbook * @param out output to text */ public static void text(Workbook book, OutputStream out) { Objects.requireNonNull(book); Objects.requireNonNull(out); try (PrintStream printer = Try.to(() -> new PrintStream(out, true, System.getProperty("file.encoding"))) .get()) { for (int i = 0, end = book.getNumberOfSheets(); i < end; i++) { Sheet sheet = book.getSheetAt(i); int rowCount = sheet.getPhysicalNumberOfRows(); if (rowCount <= 0) { logger.info(sheet.getSheetName() + ": empty"); continue; /* skip blank sheet */ } logger.info(sheet.getSheetName() + ": " + rowCount + " rows"); printer.println("sheet name: " + sheet.getSheetName()); printer.println("max row index: " + sheet.getLastRowNum()); printer.println("max column index: " + Tool.stream(sheet.rowIterator(), rowCount).mapToInt(Row::getLastCellNum).max().orElse(0)); eachCell(sheet, (cell, range) -> Tool.cellValue(cell).ifPresent(value -> printer.println( '[' + (range == null ? new CellReference(cell).formatAsString() : range.formatAsString()) + "] " + value))); sheet.getCellComments().entrySet().forEach(entry -> { printer.println("[comment " + entry.getKey() + "] " + entry.getValue().getString()); }); eachShape(sheet, shapeText(text -> printer.println("[shape text] " + text))); printer.println("--------"); } } }
From source file:jp.qpg.ExcelTo.java
License:Apache License
/** * traverse all cells/*w w w . ja v a2 s . c om*/ * * @param sheet sheet * @param consumer cell consumer */ public static void eachCell(Sheet sheet, BiConsumer<Cell, CellRangeAddress> consumer) { sheet.rowIterator().forEachRemaining(row -> { row.cellIterator().forEachRemaining(cell -> { int rowIndex = cell.getRowIndex(); int columnIndex = cell.getColumnIndex(); boolean until = true; for (CellRangeAddress mergedRegion : sheet.getMergedRegions()) { if (mergedRegion.isInRange(rowIndex, columnIndex)) { if (rowIndex == mergedRegion.getFirstRow() && columnIndex == mergedRegion.getFirstColumn()) { consumer.accept(cell, mergedRegion); } until = false; break; } } if (until) { consumer.accept(cell, null); } }); }); }
From source file:ket_noi_DB.ket_noi_kh.java
public void loadFile(Workbook workbook, String tenCSDL) { Sheet sheet = workbook.getSheetAt(0); int num = 0;//from www. j a va 2s .co m for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext();) { String sql = "INSERT INTO " + tenCSDL + " (makh, tenkh, sdt, diachi, tichdiem) VALUES ("; Row row = rit.next(); for (Iterator<Cell> cit = row.cellIterator(); cit.hasNext();) { Cell cell = cit.next(); cell.setCellType(Cell.CELL_TYPE_STRING); sql = sql + "'" + cell.getRichStringCellValue().toString() + "'" + ","; } sql = sql.substring(0, sql.length() - 1); sql = sql + ")"; try { statement.executeUpdate(sql); } catch (SQLException ex) { // li lnh truy vn, do trng lp m khch hng num++; } } JOptionPane.showMessageDialog(null, "? thm d liu, c " + num + " m khch hng b trng khng c thm"); }