List of usage examples for org.apache.poi.ss.usermodel Sheet getPhysicalNumberOfRows
int getPhysicalNumberOfRows();
From source file:jp.qpg.ExcelTo.java
License:Apache License
/** * excel to pdf/*from ww w .ja va 2 s .c om*/ * * @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//from ww w. java 2 s . co 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:monthly.organizer.MonthlyOrganizer.java
@Override public void start(Stage primaryStage) { wbo = new XSSFWorkbook(); try {//from w w w . j av a2s . c om Button merge = new Button("merge"); Button folder = new Button("Choose Folder"); Button file = new Button("Choose Save File"); Label fileLoc = new Label("Not Selected"); Label folderLoc = new Label("Not Selected"); folder.setOnAction(new EventHandler<ActionEvent>() { @Override public void handle(ActionEvent arg0) { DirectoryChooser folderChooser = new DirectoryChooser(); folderChooser.setTitle("Choose Folder Containing Files"); selectedFolder = folderChooser.showDialog(primaryStage); //folderLoc.setText(selectedFolder.getName()); if (null == selectedFolder) folderLoc.setText("No Folder Selected"); else folderLoc.setText(selectedFolder.getName()); } }); file.setOnAction(new EventHandler<ActionEvent>() { @SuppressWarnings("deprecation") @Override public void handle(ActionEvent arg0) { FileChooser fileSaver = new FileChooser(); fileSaver.setTitle("Create Save File"); fileSaver.getExtensionFilters().addAll(new ExtensionFilter( "ExceBeaconController bc = new BeaconController();ll Files", "*.xlsx")); outputFile = fileSaver.showSaveDialog(primaryStage); //outputFile = new File("/home/camen/Desktop" + "/Final_Books" + LocalDate.now() + ".xlsx"); if (null == outputFile) fileLoc.setText("No File Selected"); else fileLoc.setText(outputFile.getName()); } }); merge.setOnAction(new EventHandler<ActionEvent>() { @Override public void handle(ActionEvent arg0) { try { outputStream = new FileOutputStream(outputFile); } catch (FileNotFoundException e2) { // TODO Auto-generated catch block e2.printStackTrace(); } try ( Stream<Path> paths = Files.walk(Paths.get(selectedFolder.getPath()))) { paths.forEach(filePath -> { if (Files.isRegularFile(filePath) && !filePath.toString().equals(selectedFolder.toPath().toString())) { //System.out.println(filePath); fileNum++; FileInputStream inputStream; try { inputStream = new FileInputStream(filePath.toString()); wb = new XSSFWorkbook(inputStream); CreationHelper createHelper = wbo.getCreationHelper(); Sheet sheetOut = wbo.createSheet(filePath.getFileName().toString()); //add client name to beacon controller clients.add(new Client(filePath.getFileName().toString())); clientNum++; //System.out.println(clientNum + ""); Row rowOut = sheetOut.createRow(0); Cell cellOut; cellOut = rowOut.createCell(0); cellOut.setCellValue(createHelper.createRichTextString("Week")); cellOut = rowOut.createCell(1); cellOut.setCellValue(createHelper.createRichTextString("Behavior/Decel")); cellOut = rowOut.createCell(2); cellOut.setCellValue(createHelper.createRichTextString("Data Input Total")); cellOut = rowOut.createCell(3); cellOut.setCellValue(createHelper.createRichTextString("Measurment Type")); cellOut = rowOut.createCell(4); cellOut.setCellValue(createHelper.createRichTextString("Measurment Unit")); cellOut = rowOut.createCell(5); cellOut.setCellValue( createHelper.createRichTextString("Total Time for Week (Mins)")); int rowCount = 1; for (int k = 0; k < wb.getNumberOfSheets(); k++) { Sheet sheet = wb.getSheetAt(k); double weekTotal = 0; double timeTotal = 0; boolean replace = false; String decelName = null; String replaceName = null; Random rand = null; int n = 0; String measType = null; String measUnit = null; String replacedName = null; String replacedNameOld = null; LocalDateTime date = null; int weekNum = -1; int lastWeek = -1; int year = 0; int rows = sheet.getPhysicalNumberOfRows(); //System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows //+ " row(s)."); for (int r = 0; r < rows; r++) { Row row = sheet.getRow(r); if (row == null) { continue; } int cells = row.getPhysicalNumberOfCells(); //System.out.println("\nROW " + row.getRowNum() + " has " + cells //+ " cell(s)."); for (int c = 0; c < cells; c++) { Cell cell = row.getCell(c); if (cell == null) { //c++; cells++; } else { switch (cell.getCellTypeEnum()) { case STRING: if (r == 0 && cell.getRichStringCellValue().getString() .contains("Replacement")) replace = true; if (r == 0 && replace == true && c == 1) { replaceName = cell.getRichStringCellValue().getString(); rand = new Random(); n = rand.nextInt(20) + 1; } else if (r == 0 && replace == false && c == 1) decelName = cell.getRichStringCellValue().getString(); else if (r == 0 && replace == true && c == 2) replacedName = cell.getRichStringCellValue() .getString(); if (r == 1 && c == 0) measType = cell.getRichStringCellValue().getString(); if (r == 1 && c == 1) measUnit = cell.getRichStringCellValue().getString(); //System.out.println(cell.getRichStringCellValue().getString()); break; case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { date = cell.getDateCellValue().toInstant() .atZone(ZoneId.systemDefault()).toLocalDate() .atTime(LocalTime.NOON); year = date.getYear(); weekNum = date.get(IsoFields.WEEK_OF_WEEK_BASED_YEAR); if (lastWeek == -1) lastWeek = weekNum; c++; cell = row.getCell(c); weekTotal += cell.getNumericCellValue(); c++; //cell = row.getCell(c); //timeTotal += cell.getNumericCellValue(); } else { System.out.println(cell.getNumericCellValue()); } break; case BOOLEAN: //System.out.println(cell.getBooleanCellValue()); break; case FORMULA: //System.out.println(cell.getCellFormula()); break; case BLANK: //System.out.println(); break; default: //System.out.println(); } //System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" //+ value); } } //System.out.println(lastWeek + " " + weekNum + " " + weekTotal); if (lastWeek != weekNum && lastWeek != -1) { //weekCount ++; rowOut = sheetOut.createRow(rowCount); Cell cell = rowOut.createCell(0); cell.setCellValue(createHelper.createRichTextString(date.getMonth() + ", Week " + weekNum + " of Year: " + date.getYear())); //create random number to choose behavior cell = rowOut.createCell(1); if (replace == true) { cell.setCellValue( createHelper.createRichTextString(replaceName)); //client.addValue((((date.getYear() - 2014)*53) + weekNum), weekTotal, Arrays.binarySearch(Client.behaviors, replaceName)); //addvalue to random behavior chosen for the week clients.get(clientNum).addValue( (((date.getYear() - 2014) * 53) + weekNum), weekTotal, n); //System.out.println((((date.getYear() - 2014)*53) + weekNum) + " " + weekTotal + " " + Client.behaviors[n] + " " + clients.get(clientNum).getName()); } else cell.setCellValue(createHelper.createRichTextString(decelName)); cell = rowOut.createCell(2); cell.setCellValue(weekTotal); cell = rowOut.createCell(3); cell.setCellValue(createHelper.createRichTextString(measType)); cell = rowOut.createCell(4); cell.setCellValue(createHelper.createRichTextString(measUnit)); //System.out.println(months[wR[weekNum].month-1] + " week " + (wR[weekNum].monthWeek) + ", " + (year+1900) + ": " + weekTotal + " Measurment Type: " + measType + " Measument Unit: " + measUnit); weekTotal = 0; lastWeek = weekNum; //newWeek = false; rowCount++; } } //if(replace == true) //System.out.println(replaceName + " for " + replacedName); //else //System.out.println(decelName); } //controller.addClient(clients.get(clientNum)); //controller.getClients().forEach((names) -> { //System.out.println(names.getName()); //}); wb.close(); inputStream.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } }); wbo.write(outputStream); wbo.close(); outputStream.close(); final FXMLLoader loader = new FXMLLoader(getClass().getResource("beacon.fxml")); final Stage stage = new Stage(StageStyle.DECORATED); stage.setScene(new Scene((Pane) loader.load())); final BeaconController controller = loader.<BeaconController>getController(); controller.addClients(clients); controller.setFields(true); stage.showAndWait(); //getHostServices().showDocument(outputFile.toURI().toURL().toExternalForm()); } catch (IOException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } } }); VBox root = new VBox(); root.setId("pane"); folder.setId("button"); file.setId("button"); merge.setId("button"); root.getStylesheets().addAll(this.getClass().getResource("buttonStyle.css").toExternalForm()); root.setPadding(new Insets(180, 200, 30, 200)); root.setAlignment(Pos.BASELINE_CENTER); root.getChildren().addAll(folder, folderLoc, file, fileLoc, merge); root.setSpacing(10); Scene scene = new Scene(root); primaryStage.setScene(scene); primaryStage.show(); } catch (Exception e) { e.printStackTrace(); } }
From source file:net.sf.dvstar.swirl.desktopdbf.data.ExcelTableModel.java
License:Open Source License
/** * Called to convert the contents of the currently opened workbook into * a CSV file./*from w ww .j a va2 s . c o m*/ */ private void convertToCSV() { Sheet sheet = null; Row row = null; int lastRowNum = 0; ArrayList<ArrayList> csvSheetData = null; this.bookData = new ArrayList<ArrayList>(); maxRowWidths = new ArrayList<Integer>(); this.maxColumnWidths = new ArrayList<Integer>(); // System.out.println("Converting files contents to CSV format."); // Discover how many sheets there are in the workbook.... int numSheets = this.workbook.getNumberOfSheets(); // and then iterate through them. for (int i = 0; i < 1 /*numSheets*/; i++) { // !!!! Only ONE Sheet !!!! maxRowWidth = 0; // Get a reference to a sheet and check to see if it contains // any rows. sheet = this.workbook.getSheetAt(i); if (sheet.getPhysicalNumberOfRows() > 0) { // Note down the index number of the bottom-most row and // then iterate through all of the rows on the sheet starting // from the very first row - number 1 - even if it is missing. // Recover a reference to the row and then call another method // which will strip the data from the cells and build lines // for inclusion in the resylting CSV file. lastRowNum = sheet.getLastRowNum(); csvSheetData = new ArrayList<ArrayList>(); for (int j = 0; j <= lastRowNum; j++) { row = sheet.getRow(j); csvSheetData.add(this.rowToCSV(row)); } this.getBookData().add(csvSheetData); this.maxRowWidths.add(new Integer(getMaxRowWidth())); } } }
From source file:net.sourceforge.squirrel_sql.plugins.dataimport.importer.excel.ExcelFileImporter.java
License:Open Source License
public String[][] getPreview(int noOfLines) throws IOException { String[][] data = null;//from w ww .j ava2 s . c o m Workbook wb = null; Sheet sht = null; try { wb = WorkbookFactory.create(importFile); sht = getSheet(wb); } catch (InvalidFormatException fe) { throw new IOException(fe.toString()); } int y = 0; int x = 0; int maxLines = (noOfLines < sht.getPhysicalNumberOfRows()) ? noOfLines : sht.getPhysicalNumberOfRows(); Row row = sht.getRow(0); data = new String[maxLines][row.getPhysicalNumberOfCells()]; for (y = 0; y < maxLines; y++) { for (x = 0; x < row.getPhysicalNumberOfCells(); x++) { data[y][x] = row.getCell(x).toString(); } } return data; }
From source file:nl.meine.scouting.solparser.writer.ExcelWriterTest.java
License:Open Source License
/** * Test of write method, of class ExcelWriter. *//*from w w w.j a v a 2 s . c o m*/ @Test public void testWrite() throws Throwable { assertNotNull("Persons must be initialized before callinig init", instance.allPersons); assertNotNull("Persons must be initialized before callinig init", instance.sortedPersons); Date begin = new Date(); instance.write(); instance.postprocess(sorter.getOrder()); instance.closeWriter(); Date end = new Date(); Long duration = end.getTime() - begin.getTime(); assertTrue(instance.previous.lastModified() - instance.output.lastModified() < duration); assertTrue(instance.output.exists()); assertEquals(1, instance.workbook.getNumberOfSheets()); Sheet s = instance.workbook.getSheetAt(0); assertEquals(3, s.getPhysicalNumberOfRows()); Row r1 = s.getRow(1); Cell c1 = r1.getCell(0); assertEquals(true, c1.getStringCellValue().equalsIgnoreCase("16") || c1.getStringCellValue().equalsIgnoreCase("1616")); Row r2 = s.getRow(2); Cell c2 = r2.getCell(0); assertEquals(true, c2.getStringCellValue().equalsIgnoreCase("16") || c2.getStringCellValue().equalsIgnoreCase("1616")); int a = 0; }
From source file:nl.meine.scouting.solparser.writer.ExcelWriterTest.java
License:Open Source License
@Test public void testUpdates() throws Throwable { instance.write();/*from w w w.ja va 2 s . c om*/ instance.postprocess(sorter.getOrder()); instance.closeWriter(); File twopersons = ParserTest.getResource("twopersons_update.csv"); Parser p = new Parser(twopersons, SorterFactory.createSorter(SorterFactory.SORTER_ONLYALL)); p.read(true); persons = p.getAllPersons(); sortedPersons = p.getSortedPersons(); instance = new ExcelWriter("dummy.xls"); instance.setAllPersons(allPersons); instance.setSortedPersons(sortedPersons); instance.init(); instance.write(); instance.postprocess(sorter.getOrder()); instance.closeWriter(); Workbook wb = instance.workbook; Sheet s = wb.getSheetAt(0); assertEquals(4, s.getPhysicalNumberOfRows()); // lidnummer: 161616 is nieuw // lidnummer 16: straat geupdatet // lidnummer 1616 alles gelijk for (int i = 1; i < s.getPhysicalNumberOfRows(); i++) { Row r = s.getRow(i); Cell lidnummer = r.getCell(0); if (lidnummer.getStringCellValue().equals("16")) { Cell straat = r.getCell(6); Cell huisnummer = r.getCell(7); assertEquals(ExcelWriter.COLOR_UPDATED, straat.getCellStyle().getFillForegroundColor()); assertEquals(ExcelWriter.COLOR_UPDATED, huisnummer.getCellStyle().getFillForegroundColor()); } else if (lidnummer.getStringCellValue().equals("1616")) { for (int j = 0; j < r.getPhysicalNumberOfCells(); j++) { Cell c = r.getCell(j); assertEquals(IndexedColors.AUTOMATIC.index, c.getCellStyle().getFillForegroundColor()); } } else if (lidnummer.getStringCellValue().equals("161616")) { for (int j = 0; j < r.getPhysicalNumberOfCells(); j++) { Cell c = r.getCell(j); assertEquals(ExcelWriter.COLOR_NEW, c.getCellStyle().getFillForegroundColor()); } } } }
From source file:nl.meine.scouting.solparser.writer.ExcelWriterTest.java
License:Open Source License
@Test public void testRemovedPerson() throws Throwable { instance.write();//from w ww . jav a 2 s.c o m instance.postprocess(sorter.getOrder()); instance.closeWriter(); // lidnummer 16: weg // lidnummer 1616 alles gelijk File twopersons = ParserTest.getResource("twopersons_removed.csv"); Parser p = new Parser(twopersons, SorterFactory.createSorter(SorterFactory.SORTER_ONLYALL)); p.read(true); persons = p.getAllPersons(); sortedPersons = p.getSortedPersons(); instance = new ExcelWriter("dummy.xls"); instance.setAllPersons(allPersons); instance.setSortedPersons(sortedPersons); instance.init(); instance.write(); instance.postprocess(sorter.getOrder()); instance.closeWriter(); Workbook wb = instance.workbook; Sheet s = wb.getSheetAt(0); assertEquals(2, s.getPhysicalNumberOfRows()); Sheet removedSheet = wb.getSheet(ExcelWriter.SHEET_REMOVED_PERSONS); assertNotNull(removedSheet); assertEquals(2, removedSheet.getPhysicalNumberOfRows()); Row r = removedSheet.getRow(1); Cell lidnummer = r.getCell(0); assertEquals("16", lidnummer.getStringCellValue()); }
From source file:no.hild1.bank.KonverterMottagerregister.java
License:Apache License
private void processInnland(Sheet sheet) throws InvalidFormatException { if (sheet.getPhysicalNumberOfRows() > 0) { int lastRowNum = 0; Row row = null;//from www.j a va 2 s . c om lastRowNum = sheet.getLastRowNum(); System.out.println("Innland har " + lastRowNum + " rader"); row = sheet.getRow(0); int lastCellNum = row.getLastCellNum(); System.out.println("Innland rad 0 har " + lastCellNum + " celler"); System.out.println("Kjrer tilregnelighetssjekk"); String KO = text(row, INNLAND_KONTONR); String LE = text(row, INNLAND_LEVNR); String NA = text(row, INNLAND_NAVN); String A1 = text(row, INNLAND_ADDR1); String A2 = text(row, INNLAND_ADDR2); String NR = text(row, INNLAND_POSTNUMMER); String ST = text(row, INNLAND_POSTSTED); if (KO.equals("Kontonr") && LE.equals("Lev.nr") && NA.equals("Navn") && A1.equals("Adresse 1") && A2.equals("Adresse 2") && NR.equals("Postnr.") && ST.equals("Poststed")) { System.out.println("Frste rad ser OK ut, fortsetter"); for (int j = 1; j <= lastRowNum; j++) { System.out.println("Prosesserer rad " + j + " av " + lastRowNum); row = sheet.getRow(j); this.rowToXML(row, j); } } else { throw new InvalidFormatException("Kjenner ikke igjen frste rad\n" + "Skulle vrt (1), fant (2)\n(1) 'Kontonr' 'Lev.nr' 'Navn' 'Adresse 1' 'Adresse 2' 'Postnr.' 'Poststed'\n'" + "" + KO + "' '" + LE + "' '" + NA + "' '" + A1 + "' '" + A2 + "' '" + NR + "' '" + ST + "'"); } } }
From source file:no.hild1.excelsplit.ES.java
private void processSheet(Sheet sheet) throws IOException { // Er det 2 eller flere rows (dvs minst header + 1) i arket? if (sheet.getPhysicalNumberOfRows() >= 2) { int lastRowNum = 0; Row header = null;/* w ww . j a v a 2 s. c o m*/ lastRowNum = sheet.getLastRowNum(); // hent siste row det er skrevet i System.out.println("Regnearket har " + lastRowNum + " rader"); header = sheet.getRow(0); int lastCellNum = header.getLastCellNum(); System.out.println("Header har " + lastCellNum + " kolonner"); String header1 = text(header, 0); String header2 = text(header, 1); String header3 = text(header, 2); String header4 = text(header, 3); if (header1.equals("Header 1") && header2.equals("Header 2") && header3.equals("Header 3") && header4.equals("Header 4")) { System.out.println("Frste rad ser OK ut, fortsetter"); Row row = null; Map<String, XSSFWorkbook> header2types = null; for (int j = 1; j <= lastRowNum; j++) { System.out.println("Prosesserer rad " + j + " av " + lastRowNum); row = sheet.getRow(j); handleRow(row, j, header, header2types); } for (Map.Entry<String, XSSFWorkbook> entry : header2types.entrySet()) { FileOutputStream out = new FileOutputStream("Some_name_" + entry.getKey() + ".xlss"); entry.getValue().write(out); out.close(); } } } }