List of usage examples for org.apache.poi.ss.usermodel Workbook getSheetIndex
int getSheetIndex(Sheet sheet);
From source file:de.iteratec.iteraplan.businesslogic.exchange.nettoExport.AbstractNettoTransformer.java
License:Open Source License
/** * Set the sheet name like "Information System (IS)". * @param sheet A POI {@link Sheet}/*from www.j a v a2 s . c om*/ * @param typeOfBuildingBlock The used type of building block. */ protected final void configSheetName(Sheet sheet, TypeOfBuildingBlock typeOfBuildingBlock) { String msgLookupKey = typeOfBuildingBlock.getValue(); String msgLookupKeyAbbr = typeOfBuildingBlock.getAbbreviationValue(); String tobName = MessageAccess.getString(msgLookupKey); String tobAbbrName = MessageAccess.getString(msgLookupKeyAbbr); Workbook workbook = sheet.getWorkbook(); int sheetIndex = workbook.getSheetIndex(sheet); workbook.setSheetName(sheetIndex, tobName + " (" + tobAbbrName + ")"); }
From source file:dk.cubing.liveresults.action.admin.ScoresheetAction.java
License:Open Source License
/** * @return/*from www . ja v a 2s .c om*/ */ public String generateScoresheet() { if (!getFormats().isEmpty() && !getTimeFormats().isEmpty() && !getRound1().isEmpty()) { try { // load WCA template from file InputStream is = ServletActionContext.getServletContext() .getResourceAsStream(getSpreadSheetFilename()); Workbook workBook; workBook = WorkbookFactory.create(is); is.close(); // build special registration sheet generateRegistrationSheet(workBook, getCompetition()); // build result sheets generateResultSheets(workBook, getCompetition(), getFormats(), getTimeFormats(), getRound1(), getRound2(), getRound3(), getRound4()); // set default selected sheet workBook.setActiveSheet(workBook.getSheetIndex(SHEET_TYPE_REGISTRATION)); // output generated spreadsheet log.debug("Ouputting generated workbook"); out = new ByteArrayOutputStream(); workBook.write(out); out.close(); return Action.SUCCESS; } catch (InvalidFormatException e) { log.error("Spreadsheet template are using an unsupported format.", e); } catch (IOException e) { log.error("Error reading spreadsheet template.", e); } return Action.ERROR; } else { return Action.INPUT; } }
From source file:dk.cubing.liveresults.action.admin.ScoresheetAction.java
License:Open Source License
/** * @return//from ww w. ja v a 2 s .com */ public String exportResults() { if (competitionId != null) { Competition competitionTemplate = getCompetitionService().find(competitionId); if (competitionTemplate == null) { log.error("Could not load competition: {}", competitionId); return Action.ERROR; } setCompetition(competitionTemplate); try { // load WCA template from file InputStream is = ServletActionContext.getServletContext() .getResourceAsStream(getSpreadSheetFilename()); Workbook workBook; workBook = WorkbookFactory.create(is); is.close(); // build special registration sheet generateRegistrationSheet(workBook, getCompetition()); // build result sheets generateResultSheets(workBook, getCompetition()); // set default selected sheet workBook.setActiveSheet(workBook.getSheetIndex(SHEET_TYPE_REGISTRATION)); // email or just output to pdf? if (isSubmitResultsToWCA()) { // write workbook to temp file File temp = File.createTempFile(getCompetitionId(), ".xls"); temp.deleteOnExit(); OutputStream os = new FileOutputStream(temp); workBook.write(os); os.close(); // Create the attachment EmailAttachment attachment = new EmailAttachment(); attachment.setPath(temp.getPath()); attachment.setDisposition(EmailAttachment.ATTACHMENT); attachment.setName(getCompetitionId() + ".xls"); // send email MultiPartEmail email = new MultiPartEmail(); email.setCharset(Email.ISO_8859_1); email.setHostName(getText("email.smtp.server")); if (!getText("email.username").isEmpty() && !getText("email.password").isEmpty()) { email.setAuthentication(getText("email.username"), getText("email.password")); } email.setSSL("true".equals(getText("email.ssl"))); email.setSubject("Results from " + getCompetition().getName()); email.setMsg(getText("admin.export.message", new String[] { getCompetition().getName(), getCompetition().getOrganiser() })); email.setFrom(getCompetition().getOrganiserEmail(), getCompetition().getOrganiser()); email.addTo(getText("admin.export.resultsteamEmail"), getText("admin.export.resultsteam")); email.addCc(getCompetition().getOrganiserEmail(), getCompetition().getOrganiser()); email.addCc(getCompetition().getWcaDelegateEmail(), getCompetition().getWcaDelegate()); email.attach(attachment); email.send(); return Action.SUCCESS; } else { // output generated spreadsheet log.debug("Ouputting generated workbook"); out = new ByteArrayOutputStream(); workBook.write(out); out.close(); return "spreadsheet"; } } catch (InvalidFormatException e) { log.error("Spreadsheet template are using an unsupported format.", e); } catch (IOException e) { log.error("Error reading spreadsheet template.", e); } catch (EmailException e) { log.error(e.getMessage(), e); } return Action.ERROR; } else { return Action.INPUT; } }
From source file:dk.cubing.liveresults.action.admin.ScoresheetAction.java
License:Open Source License
/** * @param workBook/*from w w w. j ava 2 s.co 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 = null; String eventNameFormatted = null; if (round == null) { sheetName = event.getName(); // TODO: format to short sheetname eventNameFormatted = event.getName(); } else { sheetName = event.getName() + " - " + getRoundTypesMap().get(round); eventNameFormatted = getText("admin.scoresheet.eventname." + event.getName().toLowerCase()) + " - " + getRoundTypesMap().get(round); } log.debug("Building result sheet: {}", sheetName); 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 = (round == null) ? event.getResults().size() : 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())) { 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_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\",AVERAGE(" + range + ")))"); 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_TEAMBEST1M.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(H" + (i + 4) + "=H" + (i + 5) + ",A" + (i + 4) + ",ROW()-4))"); break; } } else if (SHEET_TYPE_BEST2S.equals(template.getSheetName()) || SHEET_TYPE_BEST2M.equals(template.getSheetName()) || SHEET_TYPE_BEST2N.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_TEAMBEST2M.equals(template.getSheetName())) { String range = "H" + (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(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 + ")=2,\"\",\"DNF\"))"); break; } } else if (SHEET_TYPE_BEST3S.equals(template.getSheetName()) || SHEET_TYPE_BEST3M.equals(template.getSheetName()) || SHEET_TYPE_BEST3N.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_TEAMBEST3M.equals(template.getSheetName())) { String range = "H" + (i + 5) + ":" + "J" + (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(K" + (i + 4) + "=K" + (i + 5) + ",A" + (i + 4) + ",ROW()-4))"); break; // best case 10: 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_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) { if (round == null) { try { generateCompetitorResultsRows(workBook, resultSheet, event); } catch (Exception e) { log.error("[{}] " + e.getLocalizedMessage(), e); throw new RuntimeException("Could not include competitors and results in this sheet.", e); } } else { 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:dk.cubing.wcaspreadsheet.action.ScoresheetAction.java
License:Open Source License
/** * @param workBook/*from w ww .j a va 2s . co 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:info.informationsea.java.excel2csv.Utilities.java
License:Open Source License
public static Sheet createUniqueNameSheetForWorkbook(Workbook workbook, String sheetName, boolean overwrite) { if (overwrite) { int index = workbook.getSheetIndex(workbook.getSheet(sheetName)); if (index >= 0) workbook.removeSheetAt(index); return workbook.createSheet(sheetName); }//from w ww . j a v a 2 s . c om String realSheetName = sheetName; int index = 1; Sheet sheet; while (true) { try { sheet = workbook.createSheet(realSheetName); break; } catch (IllegalArgumentException e) { realSheetName = sheetName + "-" + index++; if (index > 20) { throw e; } } } return sheet; }
From source file:javaapp.ExReadSample.java
public static void main(String[] args) throws IOException { String excelFilePath = "GBRCNCOR.xlsx"; FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); Workbook workbook = new XSSFWorkbook(inputStream); /*/*from www . ja va2 s .com*/ ArrayList<String> open = parseReport(5,1,2,5,8,44,46,"open"); ArrayList<String> close = parseReport(24,1,2,5,8,44,46,"close"); ArrayList<String> rinvoice = parseReport(34,0,1,2,3,5,6,"rinvoice"); ArrayList<String> correction = parseReport(14,0,1,4,5,10,10,"correction"); ArrayList<String> adjust = parseReport(18,0,4,7,8,11,11,"adjust"); ArrayList<String> o1cf = parseReport(22,1,2,8,5,44,46,"o1cf"); ArrayList<String> cdata = parseReport(36,0,1,2,3,7,7,"cdata"); */ //ArrayList<String> sheet_names = {"Uninv Opening Position","Uninv Closing Position","Debtor Reconciled Invoices","Uninv Debtor Data Corrections","Uninv Debtor Adjustments","Uninv One1Clear Features","Debtor Control Data"}; String sheet_names[] = { "Uninv Opening Position", "Uninv Closing Position", "Debtor Reconciled Invoices", "Uninv Debtor Data Corrections", "Uninv Debtor Adjustments", "Uninv One1Clear Features", "Debtor Control Data" }; int sheet_no; for (String str : sheet_names) { sheet_no = workbook.getSheetIndex(str); Sheet wb_sheet = workbook.getSheetAt(sheet_no); String sheet_name = str; Iterator<Row> iterator = wb_sheet.iterator(); String rec = ""; String pay = ""; String per = ""; String svc = ""; double dval = 0; double cval = 0; String rpps = ""; int j = 0; while (iterator.hasNext()) { j++; System.out.println(sheet_name + "----->row" + j); if (j == 10) { j = 0; break; } Row nextRow = iterator.next(); Iterator<Cell> cellIterator = nextRow.cellIterator(); dval = 0; cval = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() == 1 || cell.getColumnIndex() == 2 || cell.getColumnIndex() == 5 || cell.getColumnIndex() == 8 || cell.getColumnIndex() == 44 || cell.getColumnIndex() == 46) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: if (cell.getColumnIndex() == 1) { rec = cell.getStringCellValue(); } if (cell.getColumnIndex() == 2) { pay = cell.getStringCellValue(); } if (cell.getColumnIndex() == 5) { svc = cell.getStringCellValue(); } if (cell.getColumnIndex() == 8) { per = cell.getStringCellValue(); } break; case Cell.CELL_TYPE_BOOLEAN: //System.out.print(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: //System.out.print(cell.getNumericCellValue()); if (cell.getColumnIndex() == 44) { dval = cell.getNumericCellValue(); } if (cell.getColumnIndex() == 46) { cval = cell.getNumericCellValue(); } break; } } } if (rec.length() == 5 || rec.length() == 8) { rpps = rec + "-" + pay + "-" + per + "-" + svc; System.out.println(rpps + "|" + dval + "|" + cval); //System.out.println("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); //System.out.println(); // ADD insert Query to Array //stmt.executeUpdate("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); } } } }
From source file:net.lizhaoweb.maker.code.java.model.excel.read.ExcelFileReader.java
License:Open Source License
private Set<ClassInformation> analysisWorkbook(Configuration configuration, Workbook workbook) { if (configuration == null) { throw new IllegalArgumentException("The configuration is null"); }/*from w ww. j av a 2 s . c o m*/ if (workbook == null) { throw new IllegalArgumentException("The excel workbook is null"); } Set<ClassInformation> result = null; if (AnalysisSwitch.INDEX == configuration.getAnalysisSwitch()) { List<Integer> sheetIndexes = configuration.getAnalysisIndexes(); result = this.analysisBySheetIndex(configuration, sheetIndexes, workbook); } else if (AnalysisSwitch.NAME == configuration.getAnalysisSwitch()) { List<String> classNames = configuration.getAnalysisNames(); List<Integer> sheetIndexes = null; if (classNames != null) { sheetIndexes = new ArrayList<Integer>(); for (String className : classNames) { Integer sheetIndex = workbook.getSheetIndex(className); sheetIndexes.add(sheetIndex); } } result = this.analysisBySheetIndex(configuration, sheetIndexes, workbook); } try { this.getGenerator().generate(result, configuration); } catch (Exception e) { logger.error(e.getMessage(), e); } return result; }
From source file:org.alanwilliamson.openbd.plugin.spreadsheet.tags.cfSpreadSheetWrite.java
License:Open Source License
protected void writeQueryToSheet(cfQueryResultData queryData, cfSpreadSheetData spreadsheet, String sheetName) throws dataNotSupportedException { Workbook workbook = spreadsheet.getWorkBook(); if (workbook.getSheet(sheetName) != null) workbook.removeSheetAt(workbook.getSheetIndex(sheetName)); Sheet sheet = workbook.createSheet(sheetName); //WRITE THE SHEET: 1st row to be the columns String[] columnList = queryData.getColumnList(); Row row = sheet.createRow(0);//w w w . j av a2s .co m Cell cell; for (int c = 0; c < columnList.length; c++) { cell = row.createCell(c, Cell.CELL_TYPE_STRING); cell.setCellValue(columnList[c]); } //WRITE THE SHEET: Write out all the rows int rowsToInsert = queryData.getSize(); for (int x = 0; x < rowsToInsert; x++) { row = sheet.createRow(x + 1); for (int c = 0; c < columnList.length; c++) { cell = row.createCell(c); cfData value = queryData.getCell(x + 1, c + 1); if (value.getDataType() == cfData.CFNUMBERDATA) { cell.setCellValue(value.getDouble()); cell.setCellType(Cell.CELL_TYPE_NUMERIC); } else if (value.getDataType() == cfData.CFDATEDATA) { cell.setCellValue(new Date(value.getDateLong())); } else if (value.getDataType() == cfData.CFBOOLEANDATA) { cell.setCellValue(value.getBoolean()); cell.setCellType(Cell.CELL_TYPE_BOOLEAN); } else { cell.setCellValue(value.getString()); cell.setCellType(Cell.CELL_TYPE_STRING); } } } }
From source file:org.bbreak.excella.core.handler.DebugErrorHandler.java
License:Open Source License
/** * ?//from w w w . ja v a 2s . c om * * @param workbook * @param errorCell * @param exception */ protected void markupErrorCell(Workbook workbook, ParseException exception) { Cell errorCell = exception.getCell(); if (errorCell == null) { return; } // ???? workbook.setActiveSheet(workbook.getSheetIndex(errorCell.getSheet())); errorCell.setAsActiveCell(); if (workbook instanceof XSSFWorkbook) { XSSFWorkbook xssfWorkbook = (XSSFWorkbook) workbook; CellStyle errorCellStyle = xssfWorkbook.createCellStyle(); errorCellStyle.setFillForegroundColor(HSSFColorPredefined.ROSE.getIndex()); errorCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); errorCell.setCellStyle(errorCellStyle); // TODO:??????????????? // XSSFComment xssfComment = ((XSSFSheet)sheet).createComment(); // xssfComment.setRow( errorCell.getRowIndex()); // xssfComment.setColumn( (short)errorCell.getColumnIndex()); // XSSFRichTextString string = new XSSFRichTextString( ex.getMessage()); // xssfComment.setString( ex.getMessage()); } else { HSSFWorkbook hssfWorkbook = (HSSFWorkbook) workbook; int sheetNum = hssfWorkbook.getNumberOfSheets(); for (int cnt = 0; cnt < sheetNum; cnt++) { hssfWorkbook.getSheetAt(cnt).setSelected(false); } // ? CellStyle errorCellStyle = hssfWorkbook.createCellStyle(); errorCellStyle.setFillForegroundColor(HSSFColorPredefined.ROSE.getIndex()); errorCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); errorCell.setCellStyle(errorCellStyle); // ? short commentColFrom = (short) (errorCell.getColumnIndex() + 1); short commentColTo = (short) (errorCell.getColumnIndex() + ERROR_COMENT_COL_SIZE); int commentRowFrom = errorCell.getRowIndex(); int commentRowTo = errorCell.getRowIndex() + ERROR_COMENT_ROW_SIZE; HSSFSheet hssfSheet = (HSSFSheet) errorCell.getSheet(); HSSFPatriarch patr = hssfSheet.createDrawingPatriarch(); hssfSheet.setSelected(true); HSSFComment comment = patr.createComment( new HSSFClientAnchor(0, 0, 0, 0, commentColFrom, commentRowFrom, commentColTo, commentRowTo)); comment.setVisible(true); comment.setString(new HSSFRichTextString(createCommentMessage(exception))); errorCell.setCellComment(comment); } }