Example usage for org.apache.poi.ss.usermodel Cell getCellStyle

List of usage examples for org.apache.poi.ss.usermodel Cell getCellStyle

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. getCellStyle

Introduction

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

Prototype

CellStyle getCellStyle();

Source Link

Document

Return the cell's style.

Usage

From source file:de.enerko.reports2.engine.Report.java

License:Apache License

/**
 * This method adds a new cell to the sheet of a workbook. It could 
 * (together with {@link #fill(Workbook, Cell, String, String, boolean)}) be moved to
 * the {@link CellDefinition} itself, but that would mean that the {@link CellDefinition} is
 * tied to a specific Excel API. Having those methods here allows the Report to become
 * an interface if a second engine (i.e. JXL) should be added in the future.
 * @param workbook//from  w  w  w.ja  v  a  2  s  . c om
 * @param sheet
 * @param cellDefinition
 */
private void addCell(final Workbook workbook, final Sheet sheet, final CellDefinition cellDefinition) {
    final int columnNum = cellDefinition.column, rowNum = cellDefinition.row;

    Row row = sheet.getRow(rowNum);
    if (row == null)
        row = sheet.createRow(rowNum);

    Cell cell = row.getCell(columnNum);
    // If the cell already exists and is no blank cell
    // it will be used including all formating
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell = fill(workbook, cell, cellDefinition, false);
    }
    // Otherwise a new cell will be created, the datatype set and 
    // optionally a format will be created
    else {
        cell = fill(workbook, row.createCell(columnNum), cellDefinition, true);

        final Sheet referenceSheet;
        if (cellDefinition.getReferenceCell() != null
                && (referenceSheet = workbook.getSheet(cellDefinition.getReferenceCell().sheetname)) != null) {
            final Row referenceRow = referenceSheet.getRow(cellDefinition.getReferenceCell().row);
            final Cell referenceCell = referenceRow == null ? null
                    : referenceRow.getCell(cellDefinition.getReferenceCell().column);
            if (referenceCell != null && referenceCell.getCellStyle() != null)
                cell.setCellStyle(referenceCell.getCellStyle());
        }
    }

    // Add an optional comment      
    if (cellDefinition.hasComment()) {
        final CreationHelper factory = workbook.getCreationHelper();

        final Drawing drawing = sheet.createDrawingPatriarch();
        final ClientAnchor commentAnchor = factory.createClientAnchor();

        final int col1 = cellDefinition.comment.column == null ? cell.getColumnIndex() + 1
                : cellDefinition.comment.column;
        final int row1 = cellDefinition.comment.row == null ? cell.getRowIndex() : cellDefinition.comment.row;

        commentAnchor.setCol1(col1);
        commentAnchor.setRow1(row1);
        commentAnchor.setCol2(col1 + Math.max(1, cellDefinition.comment.width));
        commentAnchor.setRow2(row1 + Math.max(1, cellDefinition.comment.height));

        final Comment comment = drawing.createCellComment(commentAnchor);
        comment.setString(factory.createRichTextString(cellDefinition.comment.text));
        comment.setAuthor(cellDefinition.comment.author);
        comment.setVisible(cellDefinition.comment.visible);

        cell.setCellComment(comment);
    }
}

From source file:de.jlo.talendcomp.excel.SpreadsheetInput.java

License:Apache License

public String getCellStyleCSS(int columnIndex) {
    Cell cell = getCell(columnIndex);
    if (cell != null) {
        CellStyle style = cell.getCellStyle();
        if (style != null) {
            return getStyleUtil().buildCSS(style);
        } else {/*from w  ww.j a va  2s .  c o  m*/
            return "";
        }
    } else {
        CellStyle style = workbook.getCellStyleAt(0);
        if (style != null) {
            return getStyleUtil().buildCSS(style);
        } else {
            return "";
        }
    }
}

From source file:de.jlo.talendcomp.excel.SpreadsheetInput.java

License:Apache License

public CellStyle getCellStyle(int columnIndex) {
    Cell cell = getCell(columnIndex);
    if (cell != null) {
        return cell.getCellStyle();
    } else {/*from w  ww .j  av  a2s . c o  m*/
        return null;
    }
}

From source file:de.jlo.talendcomp.excel.SpreadsheetOutput.java

License:Apache License

private void setupStyle(Cell cell, int row) {
    CellStyle style = cell.getCellStyle();
    // cell has its own style and not the default style
    if (reuseExistingStyles) {
        // we have to reuse the existing style
        if (reuseExistingStylesAlternating) {
            // we have to reuse the style from the even/odd row
            if (isFirstRow(row)) {
                // we are in the first row, memorize the style
                if (style.getIndex() > 0) {
                    // only if the cell does not use the default style
                    oddRowColumnStyleMap.put(cell.getColumnIndex(), style);
                }/*from   w ww.j  a v  a  2  s .c  o  m*/
            } else if (isSecondRow(row)) {
                // we are in the first row, memorize the style
                if (style.getIndex() > 0) {
                    // only if the cell does not use the default style
                    evenRowColumnStyleMap.put(cell.getColumnIndex(), style);
                }
            } else if (isEvenDataRow(row)) {
                // reference to the previously memorized style for even rows
                CellStyle s = evenRowColumnStyleMap.get(cell.getColumnIndex());
                if (s != null) {
                    style = s;
                    cell.setCellStyle(style);
                }
            } else {
                // reference to the previously memorized style for even rows
                CellStyle s = oddRowColumnStyleMap.get(cell.getColumnIndex());
                if (s != null) {
                    style = s;
                    cell.setCellStyle(style);
                }
            }
        } else {
            // we take the style from the last row
            if (isFirstRow(row)) {
                // memorize the style for reuse in all other rows
                if (style.getIndex() > 0) {
                    // only if the cell does not use the default style
                    columnStyleMap.put(cell.getColumnIndex(), style);
                }
            } else {
                // set the style from the previous row
                CellStyle s = columnStyleMap.get(cell.getColumnIndex());
                if (s != null) {
                    style = s;
                    cell.setCellStyle(style);
                }
            }
        }
    } else {
        Short formatIndex = cellFormatMap.get(cell.getColumnIndex());
        if (formatIndex != null) {
            if ((style.getIndex() == 0) || (style.getDataFormat() != formatIndex)) {
                // this is the default style or the current format differs from the given format
                // we need our own style for this 
                style = columnStyleMap.get(cell.getColumnIndex());
                if (style == null) {
                    style = workbook.createCellStyle();
                    style.setDataFormat(formatIndex.shortValue());
                    columnStyleMap.put(cell.getColumnIndex(), style);
                }
                cell.setCellStyle(style);
            }
        }
    }
}

From source file:de.jlo.talendcomp.excel.SpreadsheetReferencedCellInput.java

License:Apache License

private String getBgColor(Cell cell) {
    CellStyle style = cell.getCellStyle();
    if (style != null) {
        return getColorString(style.getFillBackgroundColorColor());
    } else {//from  ww  w  .  ja  v  a  2  s .c o m
        return null;
    }
}

From source file:de.jlo.talendcomp.excel.SpreadsheetReferencedCellInput.java

License:Apache License

private String getFgColor(Cell cell) {
    CellStyle style = cell.getCellStyle();
    if (style != null) {
        return getColorString(style.getFillForegroundColorColor());
    } else {/*www.j  a  va  2  s .  c  o  m*/
        return null;
    }
}

From source file:de.topicmapslab.jexc.eXql.grammar.expression.ValueExpression.java

License:Apache License

/**
 * Returns the cell value represent by the given token
 * /* w w w .j  a v  a2s .  c  o  m*/
 * @param cell
 *            the cell to extract the values from cell
 * @param token
 *            the token specifies the value to extract
 * @return the cell value
 * @throws JeXcException
 *             thrown if cell value token is unknown
 */
public Object getCellValue(final Cell cell, final String token) throws JeXcException {
    if (VALUE.equalsIgnoreCase(token) || VALUE_STRING.equalsIgnoreCase(token)) {
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_NUMERIC:
            Double d = cell.getNumericCellValue();
            Long l = d.longValue();
            /*
             * check if long value represents the same numeric value then
             * the double origin
             */
            if (d.doubleValue() == l.longValue()) {
                return String.valueOf(l);
            }
            return String.valueOf(d);
        case Cell.CELL_TYPE_BOOLEAN:
            return String.valueOf(cell.getBooleanCellValue());
        case Cell.CELL_TYPE_STRING:
        default:
            return cell.getStringCellValue();
        }
    } else if (VALUE_DATE.equalsIgnoreCase(token)) {
        return cell.getDateCellValue();
    } else if (VALUE_NUMERICAL.equalsIgnoreCase(token)) {
        return cell.getNumericCellValue();
    } else if (STYLE_FOREGROUND.equalsIgnoreCase(token)) {
        CellStyle style = cell.getCellStyle();
        return style == null ? NULL : style.getFillForegroundColor();
    } else if (STYLE_BACKGROUND.equalsIgnoreCase(token)) {
        CellStyle style = cell.getCellStyle();
        return style == null ? NULL : style.getFillBackgroundColor();
    } else if (BORDER_TOP.equalsIgnoreCase(token)) {
        CellStyle style = cell.getCellStyle();
        return style == null ? 0 : style.getBorderTop();
    } else if (BORDER_BOTTOM.equalsIgnoreCase(token)) {
        CellStyle style = cell.getCellStyle();
        return style == null ? 0 : style.getBorderBottom();
    } else if (BORDER_LEFT.equalsIgnoreCase(token)) {
        CellStyle style = cell.getCellStyle();
        return style == null ? 0 : style.getBorderLeft();
    } else if (BORDER_RIGHT.equalsIgnoreCase(token)) {
        CellStyle style = cell.getCellStyle();
        return style == null ? 0 : style.getBorderRight();
    } else if (ADDRESS.equalsIgnoreCase(token)) {
        StringBuilder builder = new StringBuilder();
        builder.append(cell.getSheet().getSheetName());
        builder.append(SLASH);
        builder.append(cell.getRow().getRowNum());
        builder.append(COLON);
        builder.append(cell.getColumnIndex());
        return builder.toString();
    } else if (HEIGHT.equalsIgnoreCase(token)) {
        CellRangeAddress address = XlsxCellUtils.getCellRange(cell);
        if (address != null) {
            return address.getLastRow() - address.getFirstRow() + 1;
        }
        return 1;
    } else if (ROW.equalsIgnoreCase(token)) {
        return cell.getRowIndex();
    } else if (COLUMN.equalsIgnoreCase(token)) {
        return cell.getColumnIndex();
    }
    throw new JeXcException("Unknown constant '" + token + "'!");
}

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

License:Open Source License

/**
 * @param workBook/*  w w w. j a  v a  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);
            }
        }
    }
}

From source file:dk.cubing.liveresults.uploader.parser.WcaParser.java

License:Open Source License

/**
 * @param row//from   w w  w .  ja v  a 2 s  .c  om
 * @param i
 * @return
 * @throws IllegalStateException
 */
private int parseResultCell(Row row, int i) throws IllegalStateException {
    int result = 0;
    Cell cell = row.getCell(3 + i);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        String cellStr = null;

        switch (cell.getCellType()) {
        // result values
        case Cell.CELL_TYPE_NUMERIC:
            // seconds
            if ("0.00".equals(cell.getCellStyle().getDataFormatString())) {
                result = new Double(cell.getNumericCellValue() * 100).intValue();

                // minutes
            } else if ("M:SS.00".equalsIgnoreCase(cell.getCellStyle().getDataFormatString())) {
                try {
                    result = resultTimeFormat.formatDateToInt(cell.getDateCellValue());
                } catch (ParseException e) {
                    log.error("[{}] " + e.getLocalizedMessage(), e);
                }

                // number
            } else if ("0".equals(cell.getCellStyle().getDataFormatString())) {
                result = new Double(cell.getNumericCellValue()).intValue();

                // unsupported
            } else {
                log.warn("[{}] Unsupported cell format: {}. Row/Column ({}, {})",
                        new Object[] { row.getSheet().getSheetName(), cell.getCellStyle().getDataFormatString(),
                                cell.getRowIndex(), cell.getColumnIndex() });
            }
            break;

        // Penalties
        case Cell.CELL_TYPE_STRING:
            cellStr = cell.getStringCellValue();
            if (cellStr != null) {
                if (cellStr.equalsIgnoreCase(Result.Penalty.DNF.toString())) {
                    result = Result.Penalty.DNF.getValue();
                } else if (cellStr.equalsIgnoreCase(Result.Penalty.DNS.toString())) {
                    result = Result.Penalty.DNS.getValue();
                }
            }
            break;

        // best / worst
        case Cell.CELL_TYPE_FORMULA:
            CellValue cellValue = evaluator.evaluate(cell);
            switch (cellValue.getCellType()) {
            // calculated value
            case Cell.CELL_TYPE_NUMERIC:
                // seconds
                if ("0.00".equals(cell.getCellStyle().getDataFormatString())) {
                    result = new Double(cellValue.getNumberValue() * 100).intValue();

                    // minutes
                } else if ("M:SS.00".equalsIgnoreCase(cell.getCellStyle().getDataFormatString())) {
                    try {
                        result = resultTimeFormat.formatDateToInt(cell.getDateCellValue());
                    } catch (ParseException e) {
                        log.error("[{}] " + e.getLocalizedMessage(), e);
                    }

                    // number
                } else if ("0".equals(cell.getCellStyle().getDataFormatString())
                        || "GENERAL".equals(cell.getCellStyle().getDataFormatString())) {
                    result = new Double(cell.getNumericCellValue()).intValue();

                    // unsupported
                } else {
                    log.warn("[{}] Unsupported cell format: {}. Row/Column ({}, {})",
                            new Object[] { row.getSheet().getSheetName(),
                                    cell.getCellStyle().getDataFormatString(), cell.getRowIndex(),
                                    cell.getColumnIndex() });
                }
                break;

            // Penalties
            case Cell.CELL_TYPE_STRING:
                cellStr = cellValue.getStringValue();
                if (cellStr != null) {
                    if (cellStr.equalsIgnoreCase(Result.Penalty.DNF.toString())) {
                        result = Result.Penalty.DNF.getValue();
                    } else if (cellStr.equalsIgnoreCase(Result.Penalty.DNS.toString())) {
                        result = Result.Penalty.DNS.getValue();
                    }
                }
                break;
            }
            break;
        }
    }
    return result;
}

From source file:dk.cubing.wcaspreadsheet.action.ScoresheetAction.java

License:Open Source License

/**
 * @param workBook/*from  w  w  w .  j  a v  a  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);
        }
    }
}