List of usage examples for org.apache.poi.ss.usermodel Sheet getSheetName
String getSheetName();
From source file:itpreneurs.itp.report.archive.CellStyleDetails.java
License:Apache License
public static void main(String[] args) throws Exception { // if(args.length == 0) { // throw new IllegalArgumentException("Filename must be given"); // }//from w w w .ja v a2s. co m String filename = "/Users/vincentgong/Documents/workspaces/Resource/itpreneurs/report/Workbook1.xlsx"; Workbook wb = WorkbookFactory.create(new File(filename)); DataFormatter formatter = new DataFormatter(); for (int sn = 0; sn < wb.getNumberOfSheets(); sn++) { Sheet sheet = wb.getSheetAt(sn); System.out.println("Sheet #" + sn + " : " + sheet.getSheetName()); for (Row row : sheet) { System.out.println(" Row " + row.getRowNum()); for (Cell cell : row) { CellReference ref = new CellReference(cell); System.out.print(" " + ref.formatAsString()); System.out.print(" (" + cell.getColumnIndex() + ") "); CellStyle style = cell.getCellStyle(); System.out.print("Format=" + style.getDataFormatString() + " "); System.out.print("FG=" + renderColor(style.getFillForegroundColorColor()) + " "); System.out.print("BG=" + renderColor(style.getFillBackgroundColorColor()) + " "); Font font = wb.getFontAt(style.getFontIndex()); System.out.print("Font=" + font.getFontName() + " "); System.out.print("FontColor="); if (font instanceof HSSFFont) { System.out.print(renderColor(((HSSFFont) font).getHSSFColor((HSSFWorkbook) wb))); } if (font instanceof XSSFFont) { System.out.print(renderColor(((XSSFFont) font).getXSSFColor())); } System.out.println(); System.out.println(" " + formatter.formatCellValue(cell)); } } System.out.println(); } }
From source file:jp.qpg.ExcelTo.java
License:Apache License
/** * excel to pdf/*from w ww . j a va 2 s. 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// www . j a v a2 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:net.bafeimao.umbrella.support.data.entity.ExcelEntityParser.java
License:Apache License
private int getColumnIndex(Sheet sheet, String colName) { Map<String, Integer> columnIndexesMap = sheetColumnIndexesMap.get(sheet.getSheetName()); if (columnIndexesMap == null) { columnIndexesMap = new HashMap<String, Integer>(); Row titleRow = sheet.getRow(1);//from w w w . jav a 2 s.co m int colNum = titleRow.getLastCellNum(); for (int i = titleRow.getFirstCellNum(); i < colNum; i++) { if (titleRow.getCell(i) != null) { columnIndexesMap.put(titleRow.getCell(i).getStringCellValue(), i); } } sheetColumnIndexesMap.put(sheet.getSheetName(), columnIndexesMap); } Integer index = columnIndexesMap.get(colName); return index == null ? -1 : index; }
From source file:net.cpollet.jixture.fixtures.transformers.ExcelFileFixtureTransformer.java
License:Apache License
private List<Object> parseSheet(Sheet sheet, Helpers helpers, Parameters parameters) { parameters.tableName = sheet.getSheetName(); if (parameters.tableName.startsWith(escapeChar)) { return Collections.emptyList(); }/*from ww w .jav a 2s. c om*/ List<Object> mappings = new LinkedList<Object>(); Iterator<Row> rowIterator = sheet.iterator(); if (rowIterator.hasNext()) { Row rowColumnNames = rowIterator.next(); while (rowIterator.hasNext()) { Object mapping = buildMappingForRow(helpers, parameters, rowIterator.next(), rowColumnNames); if (null != mapping) { mappings.add(mapping); } } } return mappings; }
From source file:net.lizhaoweb.maker.code.java.model.excel.read.ExcelFileReader.java
License:Open Source License
private void traversingSheet(Configuration configuration, // ? Map<Integer, ClassInformation> classInformationMap, // ? ExecutorService classNameExecutorService, // ???? List<Future<Map<Integer, String>>> classNameFutureList, // ?? Integer sheetIndex, // Excel Sheet sheet// Excel ) {/* w w w . j av a 2 s. c o m*/ if (configuration == null) { throw new IllegalArgumentException("The configuration is null"); } if (classInformationMap == null) { throw new IllegalArgumentException("The class information map is null"); } if (classNameExecutorService == null) { throw new IllegalArgumentException("The class name executor service is null"); } if (classNameFutureList == null) { throw new IllegalArgumentException("The class name future list is null"); } if (sheetIndex == null) { throw new IllegalArgumentException("The sheet index is null"); } if (sheet == null) { throw new IllegalArgumentException("The sheet is null"); } // Workbook workbook = sheet.getWorkbook(); // Integer sheetIndex = workbook.getSheetIndex(sheet); ClassInformation classData = new ClassInformation(); classData.setDescribe(sheet.getSheetName()); classData.setTitle(sheet.getSheetName()); classData.setPackageName(configuration.getPackageName(sheetIndex)); classInformationMap.put(sheetIndex, classData); // ??? String sheetName = sheet.getSheetName(); Future<Map<Integer, String>> classNameFuture = classNameExecutorService .submit(new TranslateCallable(this.getTranslator(), configuration, sheetIndex, sheetName)); classNameFutureList.add(classNameFuture); }
From source file:net.unit8.axebomber.parser.Book.java
License:Apache License
/** * get sheet objects./*w ww .j a v a 2s . c o m*/ */ public List<Sheet> getSheets(Map<String, Object> options) { SheetNameFilter nameFilter = null; if (options != null) { if (options.get("name") != null) nameFilter = (SheetNameFilter) options.get("name"); } int num = workbook.getNumberOfSheets(); List<Sheet> sheets = new ArrayList<Sheet>(num); for (int i = 0; i < num; i++) { org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheetAt(i); if (nameFilter != null && !nameFilter.accept(sheet.getSheetName())) continue; sheets.add(new Sheet(sheet)); } return sheets; }
From source file:nl.detoren.ijsco.io.ExcelImport.java
License:Open Source License
public GroepsUitslagen importeerUitslagen(File file) { GroepsUitslagen groepen = new GroepsUitslagen(); try {// www . ja v a2 s.c o m FileInputStream excelFile = new FileInputStream(file); Workbook workbook = new XSSFWorkbook(excelFile); String versie = "onbekend"; Sheet sheet = workbook.getSheet("Configuratie"); try { versie = sheet.getRow(0).getCell(1).getStringCellValue(); } catch (Exception ex) { logger.log(Level.INFO, "Indelings Excel versie ophalen mislukt"); } logger.log(Level.INFO, "Indelings Excel is versie " + versie); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { sheet = workbook.getSheetAt(i); if (sheet.getSheetName().startsWith("Groep ")) { logger.log(Level.INFO, "Importeer uitslag van groep : " + sheet.getSheetName()); Row row = sheet.getRow(0); if (row != null) { Cell cell = row.getCell(0); if (cell != null) { int groepsgrootte = (int) cell.getNumericCellValue(); logger.log(Level.INFO, "Groepsgrootte is " + groepsgrootte); switch (groepsgrootte) { case 4: groepen.Add(importeerGroep4(sheet)); break; case 6: groepen.Add(importeerGroep6(sheet)); break; case 8: groepen.Add(importeerGroep8(sheet)); break; case 10: groepen.Add(importeerGroep10(sheet)); break; default: logger.log(Level.WARNING, "Uitslagen verwerken voor groepsgrootte " + groepsgrootte + " niet ondersteund!"); break; } } } logger.log(Level.INFO, "next"); } } workbook.close(); } catch (Exception ex) { logger.log(Level.SEVERE, "Exception! Cause: " + ex.getCause() + ". Internal error is " + ex.getMessage() + ". Stracktrace: \r\n" + Utils.stackTraceToString(ex)); } // Print resultaat; logger.log(Level.INFO, groepen.toString()); return groepen; }
From source file:nl.detoren.ijsco.io.ExcelImport.java
License:Open Source License
private GroepsUitslag importeergroep(int groepsgrootte, Sheet sheet, int rIdxbase, int cUitslag, int cTotaal, int cWedstrijdIDWit, int cWedstrijdIDZwart) { Row row;/*www. j a v a 2 s. c o m*/ HashMap<Integer, Speler> OSBOSpelers = IJSCOController.getI().getStatus().OSBOSpelers; GroepsUitslag groep = new GroepsUitslag(groepsgrootte, cellStringValue(sheet.getRow(0).getCell(6))); for (int i = 0; i <= groepsgrootte - 1; i++) { UitslagSpeler s = new UitslagSpeler(); // Rang Integer rang; try { rang = cellIntValue(sheet.getRow(2 + i + 1).getCell(cTotaal + 2)); } catch (Exception ex) { logger.log(Level.WARNING, "Rang not found."); rang = -1; } // Id Integer id; try { id = cellIntValue(sheet.getRow(2 + i + 1).getCell(1)); } catch (Exception ex) { logger.log(Level.WARNING, "Rang not found."); id = -1; } // Naam String naam; try { naam = cellStringValue(sheet.getRow(2 + i + 1).getCell(2)); } catch (Exception ex) { logger.log(Level.WARNING, "Naam not found."); naam = "Onbekend"; } // Punten Integer punten; try { punten = (int) (10 * cellDoubleValue(sheet.getRow(2 + i + 1).getCell(cTotaal))); } catch (Exception ex) { logger.log(Level.WARNING, "Punten not found."); punten = -1; } // // WP // Integer wp; // try { // wp = (int) (10 * cellDoubleValue(sheet.getRow(2+i+1).getCell(23))); // } catch (Exception ex) { // logger.log(Level.WARNING, "WP not found."); // wp = -1; // } // SB Integer sb; try { sb = (int) (100 * cellDoubleValue(sheet.getRow(2 + i + 1).getCell(cTotaal + 1))); } catch (Exception ex) { logger.log(Level.WARNING, "SB not found."); sb = -1; } // Knsbnummer Integer knsbnummer; try { knsbnummer = cellIntValue(sheet.getRow(2 + i + 1).getCell(3)); } catch (Exception ex) { logger.log(Level.WARNING, "KNSBnummer not found."); knsbnummer = -1; } // Startrating Integer startrating; try { startrating = cellIntValue(sheet.getRow(2 + i + 1).getCell(5)); } catch (Exception ex) { logger.log(Level.WARNING, "Startrating not found."); startrating = -1; } s.setId(id); s.setRang(rang); s.setNaam(naam); s.setPunten(punten); //s.setWP(wp); s.setSB(sb); s.setKNSBnummer(knsbnummer); s.setStartrating(startrating); //logger.log(Level.INFO, s.toFormattedString()); for (Speler osbo : OSBOSpelers.values()) { int osboknsbnummer = 0; try { osboknsbnummer = osbo.getKnsbnummer(); } catch (Exception ex) { logger.log(Level.WARNING, "Exception in finding knsbnummer " + knsbnummer + " in OSBO list"); } int geboortejaar = 0; try { geboortejaar = osbo.getGeboortejaar(); } catch (Exception ex) { logger.log(Level.WARNING, "Exception in finding geboortejaar " + geboortejaar + " in OSBO list"); } if (knsbnummer == osboknsbnummer) { s.setVereniging(osbo.getVereniging()); s.setGeboortejaar(osbo.getGeboortejaar()); s.setCategorie(osbo.getCategorie()); } } groep.addSpeler(s); } for (int i = 0; i <= groepsgrootte - 2; i++) { for (int j = 0; j <= (groepsgrootte - 2) / 2; j++) { int uitslagcode; row = sheet.getRow(rIdxbase + (i * (3 + (groepsgrootte - 2) / 2)) + j); if (row != null) { uitslagcode = cellIntValue(row.getCell(cUitslag)); if (uitslagcode >= 0 && uitslagcode < 10) { WedstrijdUitslag wedstrijd = new WedstrijdUitslag(); Integer speleridwit; try { speleridwit = cellIntValue(row.getCell(cWedstrijdIDWit)); if (speleridwit == null) throw new NullPointerException(); } catch (Exception ex) { logger.log(Level.WARNING, "Player number for white not found."); speleridwit = 0; } //logger.log(Level.INFO, "Player white id : " + speleridwit); Integer speleridzwart; try { speleridzwart = cellIntValue(row.getCell(cWedstrijdIDZwart)); if (speleridzwart == null) throw new NullPointerException(); } catch (Exception ex) { logger.log(Level.WARNING, "Player number for black not found."); speleridzwart = 0; } //logger.log(Level.INFO, "Player black id : " + speleridzwart); // Integer knsbwit; // try { // knsbwit = cellIntValue(sheet.getRow(2+speleridwit).getCell(3)); // } catch (Exception ex) { // logger.log(Level.WARNING, "Player KNSB number for white not found."); // knsbwit = 0; // } // logger.log(Level.INFO, "Player white knsb number : " + knsbwit); // String naamwit; // try { // naamwit = cellStringValue(sheet.getRow(2+speleridwit).getCell(2)); // } catch (Exception ex) { // logger.log(Level.WARNING, "Player name for white not found."); // naamwit = "White"; // } // logger.log(Level.INFO, "Player white name : " + naamwit); // Integer startratingwit; // try { // startratingwit = cellIntValue(sheet.getRow(2+speleridwit).getCell(5)); // } catch (Exception ex) { // logger.log(Level.WARNING, "Start rating for white not found."); // startratingwit = -1; // } // Integer knsbzwart; // try { // knsbzwart = cellIntValue(sheet.getRow(2+speleridzwart).getCell(3)); // } catch (Exception ex) { // logger.log(Level.WARNING, "Player number for black not found."); // knsbzwart = 0; // } // logger.log(Level.INFO, "Player white knsb number : " + knsbzwart); // String naamzwart; // try { // naamzwart = cellStringValue(sheet.getRow(2+speleridzwart).getCell(2)); // } catch (Exception ex) { // logger.log(Level.WARNING, "Player name for black not found."); // naamzwart = "Black"; // } // logger.log(Level.INFO, "Player black name : " + naamzwart); // Integer startratingzwart; // try { // startratingzwart = cellIntValue(sheet.getRow(2+speleridzwart).getCell(5)); // } catch (Exception ex) { // logger.log(Level.WARNING, "Start rating for black not found."); // startratingzwart = -1; // } UitslagSpeler wit = null; try { wit = groep.getSpelerById(speleridwit); } catch (Exception e) { logger.log(Level.WARNING, "Exception in getSpelerbyId wit : " + speleridwit + ""); } UitslagSpeler zwart = null; try { zwart = groep.getSpelerById(speleridzwart); } catch (Exception e) { logger.log(Level.WARNING, "Exception in getSpelerbyId zwart : " + speleridzwart + ""); } // for (Speler s :OSBOSpelers.values()) { // int knsbnummer = 0; // try { // knsbnummer = s.getKnsbnummer(); // } catch (Exception ex) { // logger.log(Level.WARNING, "Exception in finding knsbnummer " + knsbnummer + " in OSBO list"); // } // if (wit != null) { // if (knsbnummer == wit.getKNSBnummer()) { // wit.setVereniging(s.getVereniging()); // wit.setGeboortejaar(s.getGeboortejaar()); // wit.setCategorie(s.getCategorie()); // } //// if (wit.getNaam() == "???" ) { //// logger.log(Level.WARNING, "Speler wit is niet gevonden. Knsbnummer " + knsbwit); //// wit.setKNSBnummer(knsbwit); //// wit.setNaam(naamwit); //// } // } // if (zwart != null) { // if (knsbnummer == zwart.getKNSBnummer()) { // zwart.setVereniging(s.getVereniging()); // zwart.setGeboortejaar(s.getGeboortejaar()); // zwart.setCategorie(s.getCategorie()); // } //// if (zwart.getNaam() == "???" ) { //// logger.log(Level.WARNING, "Speler zwart is niet gevonden. Knsbnummer " + knsbzwart); //// zwart.setKNSBnummer(knsbzwart); //// zwart.setNaam(naamzwart); //// } // } // } boolean bye = false; if (wit.getNaam() != null) if (wit.getNaam().equals("Bye")) bye = true; if (zwart.getNaam() != null) if (zwart.getNaam().equals("Bye")) bye = true; if (!bye) { wedstrijd.setPoule(sheet.getSheetName()); wedstrijd.setRonde(i + 1); wedstrijd.setWit(wit); wedstrijd.setstartratingWit(wit.getStartrating()); wedstrijd.setZwart(zwart); wedstrijd.setstartratingZwart(zwart.getStartrating()); try { logger.log(Level.INFO, "Setting SpelerWit : " + wit.getNaam() + " - KNSBWit : " + wit.getKNSBnummer()); logger.log(Level.INFO, "Setting SpelerZwart : " + zwart.getNaam() + " - KNSBZwart : " + zwart.getKNSBnummer()); } catch (Exception ex) { logger.log(Level.WARNING, "Exception " + ex.getMessage() + " on logging Spelers"); } try { logger.log(Level.INFO, "Setting uitslag"); wedstrijd.setUitslag012(uitslagcode); } catch (Exception ex) { logger.log(Level.SEVERE, "Exception in setting uitslag: " + ex.getMessage()); } try { logger.log(Level.INFO, "Setting Wedstrijd : " + wedstrijd.toString()); } catch (Exception ex) { logger.log(Level.WARNING, "Wedstrijd.tostring failed"); } groep.addWedstrijd(wedstrijd); } else { logger.log(Level.WARNING, "Match is a bye"); } } } } } return groep; }
From source file:nl.meine.scouting.solparser.writer.ExcelWriter.java
License:Open Source License
private void processUpdates(Sheet sheet) { System.out.println("Process updates on sheet: " + sheet.getSheetName()); if (hasPrevious()) { FileInputStream previousStream = null; HSSFWorkbook prevWorkbook = null; try {// w w w.j a va 2 s . co m previousStream = new FileInputStream(previous); //Get the workbook instance for XLS file prevWorkbook = new HSSFWorkbook(previousStream); Sheet prevSheet = prevWorkbook.getSheet(SorterFactory.GROUP_NAME_ALL); if (prevSheet != null) { // Bestaande mensen: eventuele updates for (Iterator<Row> it = sheet.rowIterator(); it.hasNext();) { Row row = it.next(); if (row.getRowNum() > 0) { String lidnummer = row.getCell(NUM_LIDNUMMER_CELL).getStringCellValue(); Row previousRow = getLidFromSheet(lidnummer, prevSheet); processPersonUpdates(row, previousRow); } } } } catch (FileNotFoundException ex) { System.out.println("Could not locate file: " + ex.getLocalizedMessage()); } catch (IOException ex) { System.out.println("Problems reading file: " + ex.getLocalizedMessage()); } finally { try { if (previousStream != null) { previousStream.close(); if (prevWorkbook != null) { FileOutputStream out = new FileOutputStream(previous); prevWorkbook.write(out); out.close(); } } } catch (IOException ex) { System.out.println("Problems closing file: " + ex.getLocalizedMessage()); } } } }