Example usage for org.apache.poi.ss.usermodel Sheet getSheetName

List of usage examples for org.apache.poi.ss.usermodel Sheet getSheetName

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. getSheetName

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Sheet getSheetName.

Prototype

String getSheetName();

Source Link

Document

Returns the name of this sheet

Usage

From source file:de.iteratec.iteraplan.businesslogic.exchange.legacyExcel.importer.ObjectRelatedPermissionsWorkbook.java

License:Open Source License

/**
 * Imports the data out of the specified Excel file input stream. If data
 * could not be imported, an {@code empty} list will be returned.
 * //from   w  ww. j a v a2s.  c  om
 * @param is the Excel file input stream
 * @return the list of imported object related permission data objects
 */
public List<ObjectRelatedPermissionsData> doImport(InputStream is) {
    List<ObjectRelatedPermissionsData> result = Lists.newArrayList();

    loadWorkbookFromInputStream(is);

    if (!readInConfigSheet()) {
        getProcessingLog().error("Could not read in configuration sheet. Aborting.");
        return result;
    }

    calculateAllFormulas();
    final Map<String, TypeOfBuildingBlock> supportedSheetNames = getSupportedSheetNames();
    for (int i = 0; i < getWb().getNumberOfSheets(); i++) {
        Sheet sheet = getWb().getSheetAt(i);
        String sheetName = sheet.getSheetName();

        getProcessingLog().debug("Current Sheet: " + StringUtils.defaultIfEmpty(sheetName, "null"));

        if (supportedSheetNames.containsKey(sheetName)) {
            List<ObjectRelatedPermissionsData> sheetObjectPerrmissions = importSheet(sheet,
                    supportedSheetNames.get(sheetName));
            result.addAll(sheetObjectPerrmissions);
        } else if (!DEFAULT_SHEET_KEY.equals(sheetName)) {
            getProcessingLog().warn("Unknown Sheet Name '" + sheetName + "'(different Locale?). Skipping.");
        }
    }

    return result;
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.legacyExcel.importer.ObjectRelatedPermissionsWorkbook.java

License:Open Source License

/**
 * Imports the data from the specified {@code sheet}.
 * //from  www .j  a v a 2 s  .co m
 * @param sheet the sheet to import the data from
 * @param type the type ob building block this sheet represents
 * @return the list of {@link ObjectRelatedPermissionsData} objects containing imported data
 */
private List<ObjectRelatedPermissionsData> importSheet(Sheet sheet, TypeOfBuildingBlock type) {
    getProcessingLog().debug("Importing Sheet: " + sheet.getSheetName());

    String headColumnName = MessageAccess.getStringOrNull(ID_COLUMN_KEY, getLocale());
    int contentPosition = findSheetContentPosition(sheet, headColumnName, 0);
    if (contentPosition != -1) {
        Row headRow = sheet.getRow(contentPosition - 1);
        Map<String, Integer> headline = ExcelImportUtilities.getHeadlineForRange(headRow, new int[] { 0, 3 },
                getProcessingLog());
        List<Row> sheetContentRows = getSheetContentRows(contentPosition, sheet);

        return readContentFromSheet(sheetContentRows, headline, type);
    } else {
        getProcessingLog().warn("Invalid structure of Sheet '" + sheet.getSheetName() + "', skipping");
        return Collections.emptyList();
    }
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.legacyExcel.importer.sheets.SheetImporter.java

License:Open Source License

public void doImport(Sheet sheet, int contentRowOffset, final int headRowIndex, LandscapeData landscapeData) {
    Row headlineRow = sheet.getRow(headRowIndex);
    Map<String, Integer> buildingBlocksHeadline = getBuildingBlocksHeadline(headlineRow);
    Map<String, Integer> attributesHeadline = getAttributeHeadline(headlineRow);
    int currentContentRow = contentRowOffset;

    while (ExcelImportUtilities.hasNextRow(sheet, currentContentRow)) {
        LOGGER.debug(" " + sheet.getSheetName() + ": Row " + currentContentRow);
        Row row = sheet.getRow(currentContentRow);

        // can happen if a row was deleted (but not removed) in Excel -> return no data
        if (row != null) {
            Map<String, Cell> buildingBlocksRow = ExcelImportUtilities.readRow(row, buildingBlocksHeadline);
            Map<String, Cell> attributes = ExcelImportUtilities.readRow(row, attributesHeadline);

            final LandscapeRowData rowData = new LandscapeRowData(buildingBlocksRow, attributes);
            try {
                save(rowData, landscapeData);
            } catch (IteraplanBusinessException e) {
                LOGGER.warn(e);//from w ww . j a  v  a 2  s . c om
                getProcessingLog().warn("Error importing row {0}. Skipping.",
                        Integer.valueOf(row.getRowNum() + 1));
            }
        }
        currentContentRow += 1;
    }
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.timeseriesExcel.exporter.TimeseriesExcelExportServiceImpl.java

License:Open Source License

private void adjustColumnWidths(Workbook workbook) {
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        Sheet sheet = workbook.getSheetAt(i);
        if (!AbstractIntroSheetGenerator.INTRO_SHEET_NAME.equals(sheet.getSheetName())) {
            sheet.autoSizeColumn(TimeseriesExcelImporter.BB_COL_NO, false);
            sheet.setColumnWidth(TimeseriesExcelImporter.DATE_COL_NO, DATE_COL_WIDTH);
            sheet.autoSizeColumn(TimeseriesExcelImporter.VALUE_COL_NO, false);
        }//from w w w  .  j  a v a2  s . c  om
    }
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.timeseriesExcel.exporter.TimeseriesExcelExportServiceImplTest.java

License:Open Source License

private void assertTemplateSheets(Workbook workbook) {
    int actualNumberOfSheets = workbook.getNumberOfSheets();
    assertEquals(4, actualNumberOfSheets);
    for (int i = 0; i < actualNumberOfSheets; i++) {
        Sheet sheet = workbook.getSheetAt(i);
        assertEquals(EXPECTED_SHEET_NAMES[i], sheet.getSheetName());
    }/*from   w w  w  .j  av  a  2s.  c o  m*/
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.timeseriesExcel.exporter.TimeseriesIntroSheetGenerator.java

License:Open Source License

@Override
protected void createSummary() {
    Sheet introSheet = getIntroductionSheet();

    int rowNum = SUMMARY_ROW;
    Cell headerCell = introSheet.createRow(rowNum++).createCell(SUMMARY_COL);
    headerCell.setCellValue(MessageAccess.getStringOrNull("excel.export.timeseries.intro.description"));
    headerCell.setCellStyle(wbContext.getCellStyles().get(IteraExcelStyle.HEADER));
    introSheet.addMergedRegion(new CellRangeAddress(headerCell.getRowIndex(), headerCell.getRowIndex(),
            headerCell.getColumnIndex(), headerCell.getColumnIndex() + 1));

    CreationHelper createHelper = getWorkbook().getCreationHelper();

    // TODO group the summary by building block type with subheaders
    for (int i = 0; i < getWorkbook().getNumberOfSheets(); i++) {
        Sheet sheet = getWorkbook().getSheetAt(i);
        String sheetName = sheet.getSheetName();
        if (!introSheet.equals(sheet)) {
            Row summaryRow = introSheet.createRow(rowNum++);
            Cell hyperlinkCell = summaryRow.createCell(SUMMARY_COL);
            hyperlinkCell.setCellValue(sheetName);
            summaryRow.createCell(SUMMARY_COL + 1)
                    .setCellValue(ExcelUtils.getStringCellValue(sheet.getRow(0).getCell(0)));

            Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT);
            link.setAddress("'" + sheetName + "'!A1");
            hyperlinkCell.setHyperlink(link);
            hyperlinkCell.setCellStyle(wbContext.getCellStyles().get(IteraExcelStyle.HYPERLINK));
        }//  w  ww . j av a 2  s.  c  o m
    }

    adjustSheetColumnWidths();
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.timeseriesExcel.importer.TimeseriesExcelImporter.java

License:Open Source License

/**
 * Imports data from the workbook into the database.
 * @return True if the import was successful, false otherwise.
 *///  w  w  w. java2s. c  om
public boolean importExcel(Workbook workbook) {
    assert (workbook != null);
    for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        if (!AbstractIntroSheetGenerator.INTRO_SHEET_NAME.equals(sheet.getSheetName())) {
            importTimeseriesSheet(sheet);
        }
    }
    return errorMessages.isEmpty();
}

From source file:de.ks.idnadrev.expimp.xls.XlsxExporterTest.java

License:Apache License

@Test
public void testExportThoughts() throws Exception {
    File tempFile = File.createTempFile("thoughtExport", ".xlsx");
    EntityExportSource<Thought> source = new EntityExportSource<>(getAllIds(), Thought.class);
    XlsxExporter exporter = new XlsxExporter();
    exporter.export(tempFile, source);/*ww  w  .j  av a  2 s  . c om*/

    Workbook wb = WorkbookFactory.create(tempFile);
    Sheet sheet = wb.getSheetAt(0);
    assertEquals(Thought.class.getName(), sheet.getSheetName());
    int lastRowNum = sheet.getLastRowNum();
    assertEquals(COUNT, lastRowNum);
    Row firstRow = sheet.getRow(0);

    ArrayList<String> titles = new ArrayList<>();
    firstRow.cellIterator().forEachRemaining(col -> titles.add(col.getStringCellValue()));
    assertThat(titles.size(), greaterThanOrEqualTo(3));
    log.info("Found titles {}", titles);

    String creationTime = PropertyPath.property(Thought.class, t -> t.getCreationTime());
    String name = PropertyPath.property(Thought.class, t -> t.getName());
    String description = PropertyPath.property(Thought.class, t -> t.getDescription());

    assertTrue(titles.contains(creationTime));
    assertTrue(titles.contains(name));
    assertTrue(titles.contains(description));

    int nameColumn = titles.indexOf(name);
    ArrayList<String> names = new ArrayList<String>(COUNT);
    for (int i = 1; i <= COUNT; i++) {
        Row row = sheet.getRow(i);
        names.add(row.getCell(nameColumn).getStringCellValue());
    }
    Collections.sort(names);
    assertEquals("Thought000", names.get(0));
    assertEquals("Thought141", names.get(COUNT - 1));

    Date excelDate = sheet.getRow(1).getCell(titles.indexOf(creationTime)).getDateCellValue();

    Thought thought = PersistentWork.forName(Thought.class, "Thought000");

    Timestamp timestamp = java.sql.Timestamp.valueOf(thought.getCreationTime());
    Date creationDate = new Date(timestamp.getTime());
    assertEquals(creationDate, excelDate);
}

From source file:dk.cubing.liveresults.action.admin.ScoresheetAction.java

License:Open Source License

/**
 * @param workBook/*from  w ww.  java2  s  . co 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 < 33; i++) {
            Cell count = getCell(sheet, 1, 7 + i, Cell.CELL_TYPE_FORMULA);
            String col = CellReference.convertNumToColString(7 + i);
            String ref = col + "4:" + col + line;
            count.setCellFormula("SUM(" + ref + ")");
        }
    }
}

From source file:dk.cubing.liveresults.action.admin.ScoresheetAction.java

License:Open Source License

/**
 * @param workBook/*from   ww w .  j  a va  2s.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 = 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);
            }
        }
    }
}