List of usage examples for org.apache.poi.ss.usermodel Sheet getSheetName
String getSheetName();
From source file:dk.cubing.liveresults.action.admin.ScoresheetAction.java
License:Open Source License
/** * @param sheet/* w ww .j a v a 2 s .c o m*/ * @param rownum * @param cellnum * @param cellType * @return * @throws RuntimeException */ private Cell getCell(Sheet sheet, int rownum, int cellnum, int cellType) throws RuntimeException { Row row = sheet.getRow(rownum); if (row == null) { row = sheet.createRow(rownum); } Cell cell = row.getCell(cellnum); if (cell == null) { cell = row.createCell(cellnum); cell.setCellType(cellType); } else { if (cell.getCellType() != cellType && cell.getCellType() != Cell.CELL_TYPE_BLANK) { log.error("Unexpected cell type. Sheet: {}, Row: {}, Cell: {}", new Object[] { sheet.getSheetName(), rownum, cellnum }); throw new RuntimeException("Unexpected cell type."); } } return cell; }
From source file:dk.cubing.liveresults.uploader.parser.WcaParser.java
License:Open Source License
@Override protected boolean isValidSpreadsheet(Workbook workBook) throws IllegalStateException { boolean isValid = false; // get version information from registration sheet Sheet regSheet = workBook.getSheet(SHEET_TYPE_REGISTRATION); log.debug("Validating: {}", regSheet.getSheetName()); isValid = isValidRegistrationSheet(regSheet); // continue validating result sheets if (isValid) { for (int i = 0; i < workBook.getNumberOfSheets(); i++) { Sheet sheet = workBook.getSheetAt(i); if (sheet != null && !SHEET_TYPE_REGISTRATION.equals(sheet.getSheetName()) && !SHEET_TYPE_DUMMY.equals(sheet.getSheetName())) { log.debug("Validating: {}", sheet.getSheetName()); isValid = isValidResultSheet(sheet); if (!isValid) { log.warn("[{}] Invalid result sheet", sheet.getSheetName()); break; }//from www. j ava2 s. com } } } else { log.warn("[{}] Invalid registration sheet", regSheet.getSheetName()); } return isValid; }
From source file:dk.cubing.liveresults.uploader.parser.WcaParser.java
License:Open Source License
/** * @param sheet//from www. java 2 s . c o m * @return * @throws IllegalStateException */ private boolean isValidRegistrationSheet(Sheet sheet) throws IllegalStateException { boolean isValid = false; if (sheet != null && SHEET_TYPE_REGISTRATION.equals(sheet.getSheetName())) { Row versionRow = sheet.getRow(1); if (versionRow != null) { Cell versionCell = versionRow.getCell(0); if (versionCell != null && versionCell.getCellType() == Cell.CELL_TYPE_STRING) { isValid = version.equals(versionCell.getStringCellValue()); } } if (isValid) { // check for basic registration columns Row row = sheet.getRow(2); if (row != null) { for (int i = 0; i < 6; i++) { Cell cell = row.getCell(i); if (cell != null && cell.getCellType() == Cell.CELL_TYPE_STRING) { isValid = basicRegistrationColumns[i].equals(cell.getStringCellValue()); if (!isValid) break; } } } } // check for position formula if (isValid) { Row row = sheet.getRow(3); if (row != null) { Cell cell = row.getCell(0); isValid = (cell != null && cell.getCellType() == Cell.CELL_TYPE_FORMULA); } } } return isValid; }
From source file:dk.cubing.liveresults.uploader.parser.WcaParser.java
License:Open Source License
/** * @param sheet//from www. j a v a 2s . c o m * @return * @throws IllegalStateException */ private boolean isValidResultSheet(Sheet sheet) throws IllegalStateException { boolean isValid = false; if (sheet != null && !SHEET_TYPE_REGISTRATION.equals(sheet.getSheetName())) { // check for basic result columns Row row = sheet.getRow(3); if (row != null) { for (int i = 0; i < 4; i++) { Cell cell = row.getCell(i); if (cell != null && cell.getCellType() == Cell.CELL_TYPE_STRING) { isValid = basicResultColumns[i].equals(cell.getStringCellValue()); if (!isValid) break; } } } // check for position formula if (isValid) { row = sheet.getRow(4); if (row != null) { Cell cell = row.getCell(0); isValid = (cell != null && cell.getCellType() == Cell.CELL_TYPE_FORMULA); } } } return isValid; }
From source file:dk.cubing.liveresults.uploader.parser.WcaParser.java
License:Open Source License
@Override protected List<Competitor> parseCompetitors(Workbook workBook) throws IllegalStateException { List<Competitor> competitors = new CopyOnWriteArrayList<Competitor>(); Sheet sheet = workBook.getSheet(SHEET_TYPE_REGISTRATION); if (isValidRegistrationSheet(sheet)) { parseEventNames(sheet);//from w ww . j a v a2 s . com Row firstRow = sheet.getRow(3); // first row with competitor data if (firstRow != null) { Cell cell = firstRow.getCell(1); // first cell with competitor data if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) { // only parse sheet with content log.debug("Parsing: {}", sheet.getSheetName()); for (Row row : sheet) { if (row.getRowNum() > 2) { Competitor competitor = parseCompetitorRow(row); if (competitor != null) { if (competitor.getRegisteredEvents().hasSignedUp()) { competitors.add(competitor); } else { log.warn("[{}] No events registered for: {}", sheet.getSheetName(), competitor.getFirstname()); } } } } } } } return competitors; }
From source file:dk.cubing.liveresults.uploader.parser.WcaParser.java
License:Open Source License
@Override protected List<Event> parseEvents(Workbook workBook) throws IllegalStateException { evaluator = workBook.getCreationHelper().createFormulaEvaluator(); List<Event> events = new CopyOnWriteArrayList<Event>(); for (int i = 0; i < workBook.getNumberOfSheets(); i++) { Sheet sheet = workBook.getSheetAt(i); if (isValidResultSheet(sheet)) { log.debug("Parsing: {}", sheet.getSheetName()); Row firstRow = sheet.getRow(4); // first row with event results if (firstRow != null) { Cell cell = firstRow.getCell(1); // first cell with event results if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) { // only parse sheet with content Event event = parseEventDetails(sheet); event.setLive(workBook.getActiveSheetIndex() == i); List<Result> results = new CopyOnWriteArrayList<Result>(); for (int j = 4; j < sheet.getLastRowNum(); j++) { Row row = sheet.getRow(j); if (row != null) { Result result = parseResultRow(row, event); if (result.getFirstname() != null && result.getSurname() != null) { results.add(result); }/*from w ww .ja v a 2 s . c om*/ } } if (!results.isEmpty()) { event.setResults(results); events.add(event); } } } } } return events; }
From source file:dk.cubing.wcaspreadsheet.action.ScoresheetAction.java
License:Open Source License
/** * @param workBook/* www .ja v a 2s . c o m*/ * @param sheet * @param competitors * @param startrow */ private void generateCompetitorRows(Workbook workBook, Sheet sheet, List<Competitor> competitors, int startrow) { int line = startrow; for (Competitor competitor : competitors) { // number if (SHEET_TYPE_REGISTRATION.equals(sheet.getSheetName())) { Cell number = getCell(sheet, line, 0, Cell.CELL_TYPE_FORMULA); number.setCellFormula("IF(COUNTBLANK(B" + line + ")>0,\"\",ROW()-3)"); } // name Cell name = getCell(sheet, line, 1, Cell.CELL_TYPE_STRING); name.setCellValue(competitor.getFirstname() + " " + competitor.getSurname()); // country Cell country = getCell(sheet, line, 2, Cell.CELL_TYPE_STRING); country.setCellValue(countryUtil.getCountryByCode(competitor.getCountry())); // wca id String wcaId = competitor.getWcaId(); Cell wcaIdCell = null; if (wcaId == null || "".equals(wcaId)) { wcaIdCell = getCell(sheet, line, 3, Cell.CELL_TYPE_BLANK); } else { wcaIdCell = getCell(sheet, line, 3, Cell.CELL_TYPE_STRING); wcaIdCell.setCellValue(wcaId); } // handle registration sheet if (SHEET_TYPE_REGISTRATION.equals(sheet.getSheetName())) { // gender Cell gender = getCell(sheet, line, 4, Cell.CELL_TYPE_STRING); gender.setCellValue(competitor.getGender()); // birthday Cell birthday = getCell(sheet, line, 5, Cell.CELL_TYPE_NUMERIC); birthday.setCellValue(competitor.getBirthday()); // registered events List<Boolean> signupList = competitor.getRegisteredEvents().getSignupList(); for (int i = 0; i < signupList.size(); i++) { if (signupList.get(i)) { Cell signup = getCell(sheet, line, 7 + i, Cell.CELL_TYPE_NUMERIC); signup.setCellValue(1); } } } // loop line++; } // adjust competitors count per event if (SHEET_TYPE_REGISTRATION.equals(sheet.getSheetName())) { for (int i = 0; i < 19; i++) { Cell count = getCell(sheet, 1, 7 + i, Cell.CELL_TYPE_FORMULA); String ref = (char) ('H' + i) + "4:" + (char) ('H' + i) + line; count.setCellFormula("SUM(" + ref + ")"); } } }
From source file:dk.cubing.wcaspreadsheet.action.ScoresheetAction.java
License:Open Source License
/** * @param workBook//from w ww. ja v a 2 s . c o m * @param template * @param competition * @param event * @param round * @param includeCompetitors */ private void createResultSheetFromTemplate(Workbook workBook, Sheet template, Competition competition, Event event, String round, boolean includeCompetitors) { Sheet resultSheet = workBook.cloneSheet(workBook.getSheetIndex(template)); String sheetName = event.getName() + " - " + getRoundTypesMap().get(round); log.debug("Building result sheet: {}", sheetName); String eventNameFormatted = getText("admin.scoresheet.eventname." + event.getName().toLowerCase()) + " - " + getRoundTypesMap().get(round); workBook.setSheetName(workBook.getSheetIndex(resultSheet), sheetName); workBook.setSheetOrder(sheetName, 1); // first sheet is the registration sheet, let's put results directly after that Cell eventName = getCell(resultSheet, 0, 0, Cell.CELL_TYPE_STRING); eventName.setCellValue(eventNameFormatted); // get cell styles from template List<CellStyle> cellStyles = new ArrayList<CellStyle>(); Row startRow = template.getRow(4); int numberOfColumns = template.getRow(3).getPhysicalNumberOfCells(); if (startRow != null) { log.debug("Start row contains {} cells.", numberOfColumns); for (int i = 0; i < numberOfColumns; i++) { Cell cell = startRow.getCell(i); if (cell != null) { cellStyles.add(cell.getCellStyle()); } else { cellStyles.add(workBook.createCellStyle()); } } } // adjust formulas int numberOfCompetitors = competition.getCompetitors().size(); for (int i = 0; i < numberOfCompetitors; i++) { for (int j = 0; j < numberOfColumns; j++) { if (SHEET_TYPE_AVERAGE5S.equals(template.getSheetName())) { String range = "E" + (i + 5) + ":" + "I" + (i + 5); switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(AND(M" + (i + 4) + "=M" + (i + 5) + ",J" + (i + 4) + "=J" + (i + 5) + "),A" + (i + 4) + ",ROW()-4))"); break; // best case 9: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range + ")=5,\"\",\"DNF\"))"); break; // worst case 11: Cell worst = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); worst.setCellFormula("IF(COUNTBLANK(" + range + ")>0,\"\",IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>0,\"DNF\",MAX(" + range + ")))"); break; // average case 12: Cell average = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); average.setCellFormula("IF(COUNTBLANK(" + range + ")>0,\"\",IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>1,\"DNF\",ROUND(IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>0,(SUM(" + range + ")-J" + (i + 5) + ")/3,(SUM(" + range + ")-J" + (i + 5) + "-L" + (i + 5) + ")/3),2)))"); break; } } else if (SHEET_TYPE_AVERAGE5M.equals(template.getSheetName())) { String range = "E" + (i + 5) + ":" + "I" + (i + 5); switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(AND(M" + (i + 4) + "=M" + (i + 5) + ",J" + (i + 4) + "=J" + (i + 5) + "),A" + (i + 4) + ",ROW()-4))"); break; // best case 9: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range + ")=5,\"\",\"DNF\"))"); break; // worst case 11: Cell worst = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); worst.setCellFormula("IF(COUNTBLANK(" + range + ")>0,\"\",IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>0,\"DNF\",MAX(" + range + ")))"); break; // average case 12: Cell average = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); average.setCellFormula("IF(COUNTBLANK(" + range + ")>0,\"\",IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>1,\"DNF\",IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>0,(SUM(" + range + ")-J" + (i + 5) + ")/3,(SUM(" + range + ")-J" + (i + 5) + "-L" + (i + 5) + ")/3)))"); break; } } else if (SHEET_TYPE_MEAN3S.equals(template.getSheetName()) || SHEET_TYPE_MEAN3M.equals(template.getSheetName())) { String range = "E" + (i + 5) + ":" + "G" + (i + 5); switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(AND(H" + (i + 4) + "=H" + (i + 5) + ",J" + (i + 4) + "=J" + (i + 5) + "),A" + (i + 4) + ",ROW()-4))"); break; // best case 7: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range + ")=3,\"\",\"DNF\"))"); break; // mean case 9: Cell mean = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); mean.setCellFormula( "IF(COUNTBLANK(" + range + ")>0,\"\",IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>0,\"DNF\",ROUND(AVERAGE(" + range + "),2)))"); break; } } else if (SHEET_TYPE_BEST1S.equals(template.getSheetName()) || SHEET_TYPE_BEST1M.equals(template.getSheetName()) || SHEET_TYPE_BEST1N.equals(template.getSheetName())) { switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(E" + (i + 4) + "=E" + (i + 5) + ",A" + (i + 4) + ",ROW()-4))"); break; } } else if (SHEET_TYPE_BEST2S.equals(template.getSheetName()) || SHEET_TYPE_BEST2M.equals(template.getSheetName())) { String range = "E" + (i + 5) + ":" + "F" + (i + 5); switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(G" + (i + 4) + "=G" + (i + 5) + ",A" + (i + 4) + ",ROW()-4))"); break; // best case 6: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range + ")=2,\"\",\"DNF\"))"); break; } } else if (SHEET_TYPE_BEST3S.equals(template.getSheetName()) || SHEET_TYPE_BEST3M.equals(template.getSheetName())) { String range = "E" + (i + 5) + ":" + "G" + (i + 5); switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(H" + (i + 4) + "=H" + (i + 5) + ",A" + (i + 4) + ",ROW()-4))"); break; // best case 7: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range + ")=3,\"\",\"DNF\"))"); break; } } else if (SHEET_TYPE_MULTIBF1.equals(template.getSheetName())) { switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(I" + (i + 4) + "=I" + (i + 5) + ",A" + (i + 4) + ",ROW()-4))"); break; // result case 8: Cell result = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); result.setCellFormula("IF(E" + (i + 5) + "-F" + (i + 5) + ">F" + (i + 5) + ",-1,(99-F" + (i + 5) + "+E" + (i + 5) + "-F" + (i + 5) + ")*10000000+G" + (i + 5) + "*100+E" + (i + 5) + "-F" + (i + 5) + ")"); break; } } else if (SHEET_TYPE_MULTIBF2.equals(template.getSheetName())) { switch (j) { // rank case 0: Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(I" + (i + 4) + "=I" + (i + 5) + ",A" + (i + 4) + ",ROW()-4))"); break; // result1 case 7: Cell result1 = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); result1.setCellFormula("IF(E" + (i + 5) + "=\"DNS\",-2,IF(E" + (i + 5) + "-F" + (i + 5) + ">F" + (i + 5) + ",-1,(99-F" + (i + 5) + "+E" + (i + 5) + "-F" + (i + 5) + ")*10000000+G" + (i + 5) + "*100+E" + (i + 5) + "-F" + (i + 5) + "))"); break; // result2 case 11: Cell result2 = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); result2.setCellFormula("IF(I" + (i + 5) + "=\"DNS\",-2,IF(I" + (i + 5) + "-J" + (i + 5) + ">J" + (i + 5) + ",-1,(99-J" + (i + 5) + "+I" + (i + 5) + "-J" + (i + 5) + ")*10000000+K" + (i + 5) + "*100+I" + (i + 5) + "-J" + (i + 5) + "))"); break; // best case 12: Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA); best.setCellFormula("IF(AND(H" + (i + 5) + "<0,L" + (i + 5) + "<0),-1,IF(" + (i + 5) + "<0,L" + (i + 5) + ",IF(L" + (i + 5) + "<0,H" + (i + 5) + ",MIN(H" + (i + 5) + ",L" + (i + 5) + "))))"); break; } } else { log.error("Unsupported sheet type: {}", template.getSheetName()); } // set cell style Row row = resultSheet.getRow(i + 4); if (row != null) { Cell cell = row.getCell(j); if (cell != null) { cell.setCellStyle(cellStyles.get(j)); } } } } // fill sheet with competitors for this event if (includeCompetitors) { try { generateCompetitorRows(workBook, resultSheet, competition.getCompetitorsByEvent(event), 4); } catch (Exception e) { log.error("[{}] " + e.getLocalizedMessage(), e); throw new RuntimeException("Could not include competitors in this sheet.", e); } } }
From source file:edu.gatech.pmase.capstone.awesome.impl.database.AbstractDatabaseDriver.java
License:Open Source License
/** * Reads the custom attribute values from the custom attribute sheet. * * @param wb the workbook to read the custom attributes from */// w w w. ja va 2s . co m protected synchronized void setCustomAttributes(final Workbook wb) { LOGGER.info("Loading custom attribute list"); customAttributes.clear(); final Sheet customSheet = wb.getSheetAt(CUSTOM_ATTR_SHEET_NUM); if (null != customSheet) { LOGGER.debug("Custom attribute list stored in sheet: " + customSheet.getSheetName()); final int maxRows = customSheet.getPhysicalNumberOfRows(); if (maxRows > 1) { for (int rowIter = 1; rowIter < maxRows; rowIter++) { final Row row = customSheet.getRow(rowIter); if (null != row) { try { customAttributes.add(AbstractDatabaseDriver.getAttributeDescriptionFromRow(row)); } catch (ClassNotFoundException ex) { LOGGER.error("Could not load custom attribute for row: " + row.getRowNum(), ex); } } } } } else { LOGGER.warn("Could not load customer sheet"); } }
From source file:edu.isi.karma.imp.excel.ToCSV.java
License:Apache License
/** * Called to convert the contents of the currently opened workbook into a * CSV file.//from w ww. jav a 2 s . c o m * * @param destination * @param excelFileName * @throws IOException * @throws FileNotFoundException */ private void convertToCSV(File destination, String excelFileName) throws FileNotFoundException, IOException { Sheet sheet; Row row; int lastRowNum; this.csvData = new ArrayList<>(); logger.info("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 < numSheets; i++) { // 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(); for (int j = 0; j <= lastRowNum; j++) { row = sheet.getRow(j); this.rowToCSV(row); } // Save to CSV String sheetName = sheet.getSheetName(); String csvFileName = excelFileName.substring(0, excelFileName.lastIndexOf(".")) + "_" + sheetName; File csvFile = new File(destination, csvFileName); this.saveCSVFile(csvFile); csvFiles.add(csvFile); csvData.clear(); } } }