Example usage for org.apache.poi.ss.usermodel Workbook setSheetOrder

List of usage examples for org.apache.poi.ss.usermodel Workbook setSheetOrder

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. setSheetOrder

Introduction

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

Prototype

void setSheetOrder(String sheetname, int pos);

Source Link

Document

Sets the order of appearance for a given sheet.

Usage

From source file:com.admin.poi.ExcelUtils.java

License:Apache License

/**
 *  excel/*ww  w  . jav a  2  s . c om*/
 *
 * @param excelSheet   sheet ?
 * @param workbook     
 * @param outputStream ?
 */
private static void export(ExcelSheet excelSheet, Workbook workbook, OutputStream outputStream)
        throws IOException, InvocationTargetException, IllegalAccessException {

    Sheet sheet;
    sheet = workbook.createSheet();
    workbook.setSheetOrder(sheet.getSheetName(), excelSheet.getSheetIndex());

    // write head
    writeHead(excelSheet, sheet);
    // sheet
    int writeRowIndex = excelSheet.getStartRowIndex();
    if (excelSheet.getDataList() != null && !excelSheet.getDataList().isEmpty()) {
        for (Object rowData : excelSheet.getDataList()) {
            // proc row
            Row row = Optional.ofNullable(sheet.getRow(writeRowIndex)).orElse(sheet.createRow(writeRowIndex));

            writeRow(excelSheet, row, rowData);
            writeRowIndex++;
        }
    }
    workbook.write(outputStream);
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.elasticExcel.util.ExcelGeneratorUtils.java

License:Open Source License

/**
 * Sorts the sheets in the {@link Workbook}
 * @param wbContext/*from w w  w.j  a v  a 2s. c om*/
 */
public static void sortSheets(WorkbookContext wbContext) {
    Workbook wb = wbContext.getWb();
    Collections.sort(wbContext.getSheetContexts(),
            new SheetContextComparator<UniversalTypeExpressionOrder>(new UniversalTypeExpressionOrderFix()));

    // the introduction sheet remains the first (0th) sheet
    int i = 1;
    for (SheetContext sheetContext : wbContext.getSheetContexts()) {
        wb.setSheetOrder(sheetContext.getSheetName(), i++);
    }
}

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

License:Open Source License

/**
 * @param workBook/*from   www  . 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.wcaspreadsheet.action.ScoresheetAction.java

License:Open Source License

/**
 * @param workBook/*from  ww  w.j a  v  a 2  s  . c o m*/
 * @param template
 * @param competition
 * @param event
 * @param round
 * @param includeCompetitors
 */
private void createResultSheetFromTemplate(Workbook workBook, Sheet template, Competition competition,
        Event event, String round, boolean includeCompetitors) {
    Sheet resultSheet = workBook.cloneSheet(workBook.getSheetIndex(template));
    String sheetName = event.getName() + " - " + getRoundTypesMap().get(round);
    log.debug("Building result sheet: {}", sheetName);
    String eventNameFormatted = getText("admin.scoresheet.eventname." + event.getName().toLowerCase()) + " - "
            + getRoundTypesMap().get(round);
    workBook.setSheetName(workBook.getSheetIndex(resultSheet), sheetName);
    workBook.setSheetOrder(sheetName, 1); // first sheet is the registration sheet, let's put results directly after that
    Cell eventName = getCell(resultSheet, 0, 0, Cell.CELL_TYPE_STRING);
    eventName.setCellValue(eventNameFormatted);

    // get cell styles from template
    List<CellStyle> cellStyles = new ArrayList<CellStyle>();
    Row startRow = template.getRow(4);
    int numberOfColumns = template.getRow(3).getPhysicalNumberOfCells();
    if (startRow != null) {
        log.debug("Start row contains {} cells.", numberOfColumns);
        for (int i = 0; i < numberOfColumns; i++) {
            Cell cell = startRow.getCell(i);
            if (cell != null) {
                cellStyles.add(cell.getCellStyle());
            } else {
                cellStyles.add(workBook.createCellStyle());
            }
        }
    }

    // adjust formulas
    int numberOfCompetitors = competition.getCompetitors().size();
    for (int i = 0; i < numberOfCompetitors; i++) {
        for (int j = 0; j < numberOfColumns; j++) {
            if (SHEET_TYPE_AVERAGE5S.equals(template.getSheetName())) {
                String range = "E" + (i + 5) + ":" + "I" + (i + 5);
                switch (j) {
                // rank
                case 0:
                    Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA);
                    rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(AND(M" + (i + 4) + "=M"
                            + (i + 5) + ",J" + (i + 4) + "=J" + (i + 5) + "),A" + (i + 4) + ",ROW()-4))");
                    break;
                // best
                case 9:
                    Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA);
                    best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range
                            + ")=5,\"\",\"DNF\"))");
                    break;
                // worst
                case 11:
                    Cell worst = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA);
                    worst.setCellFormula("IF(COUNTBLANK(" + range + ")>0,\"\",IF(COUNTIF(" + range
                            + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>0,\"DNF\",MAX(" + range + ")))");
                    break;
                // average
                case 12:
                    Cell average = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA);
                    average.setCellFormula("IF(COUNTBLANK(" + range + ")>0,\"\",IF(COUNTIF(" + range
                            + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>1,\"DNF\",ROUND(IF(COUNTIF(" + range
                            + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>0,(SUM(" + range + ")-J" + (i + 5)
                            + ")/3,(SUM(" + range + ")-J" + (i + 5) + "-L" + (i + 5) + ")/3),2)))");
                    break;
                }
            } else if (SHEET_TYPE_AVERAGE5M.equals(template.getSheetName())) {
                String range = "E" + (i + 5) + ":" + "I" + (i + 5);
                switch (j) {
                // rank
                case 0:
                    Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA);
                    rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(AND(M" + (i + 4) + "=M"
                            + (i + 5) + ",J" + (i + 4) + "=J" + (i + 5) + "),A" + (i + 4) + ",ROW()-4))");
                    break;
                // best
                case 9:
                    Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA);
                    best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range
                            + ")=5,\"\",\"DNF\"))");
                    break;
                // worst
                case 11:
                    Cell worst = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA);
                    worst.setCellFormula("IF(COUNTBLANK(" + range + ")>0,\"\",IF(COUNTIF(" + range
                            + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>0,\"DNF\",MAX(" + range + ")))");
                    break;
                // average
                case 12:
                    Cell average = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA);
                    average.setCellFormula("IF(COUNTBLANK(" + range + ")>0,\"\",IF(COUNTIF(" + range
                            + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>1,\"DNF\",IF(COUNTIF(" + range
                            + ",\"DNF\")+COUNTIF(" + range + ",\"DNS\")>0,(SUM(" + range + ")-J" + (i + 5)
                            + ")/3,(SUM(" + range + ")-J" + (i + 5) + "-L" + (i + 5) + ")/3)))");
                    break;
                }
            } else if (SHEET_TYPE_MEAN3S.equals(template.getSheetName())
                    || SHEET_TYPE_MEAN3M.equals(template.getSheetName())) {
                String range = "E" + (i + 5) + ":" + "G" + (i + 5);
                switch (j) {
                // rank
                case 0:
                    Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA);
                    rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(AND(H" + (i + 4) + "=H"
                            + (i + 5) + ",J" + (i + 4) + "=J" + (i + 5) + "),A" + (i + 4) + ",ROW()-4))");
                    break;
                // best
                case 7:
                    Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA);
                    best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range
                            + ")=3,\"\",\"DNF\"))");
                    break;
                // mean
                case 9:
                    Cell mean = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA);
                    mean.setCellFormula(
                            "IF(COUNTBLANK(" + range + ")>0,\"\",IF(COUNTIF(" + range + ",\"DNF\")+COUNTIF("
                                    + range + ",\"DNS\")>0,\"DNF\",ROUND(AVERAGE(" + range + "),2)))");
                    break;
                }
            } else if (SHEET_TYPE_BEST1S.equals(template.getSheetName())
                    || SHEET_TYPE_BEST1M.equals(template.getSheetName())
                    || SHEET_TYPE_BEST1N.equals(template.getSheetName())) {
                switch (j) {
                // rank
                case 0:
                    Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA);
                    rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(E" + (i + 4) + "=E" + (i + 5)
                            + ",A" + (i + 4) + ",ROW()-4))");
                    break;
                }
            } else if (SHEET_TYPE_BEST2S.equals(template.getSheetName())
                    || SHEET_TYPE_BEST2M.equals(template.getSheetName())) {
                String range = "E" + (i + 5) + ":" + "F" + (i + 5);
                switch (j) {
                // rank
                case 0:
                    Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA);
                    rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(G" + (i + 4) + "=G" + (i + 5)
                            + ",A" + (i + 4) + ",ROW()-4))");
                    break;
                // best
                case 6:
                    Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA);
                    best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range
                            + ")=2,\"\",\"DNF\"))");
                    break;
                }
            } else if (SHEET_TYPE_BEST3S.equals(template.getSheetName())
                    || SHEET_TYPE_BEST3M.equals(template.getSheetName())) {
                String range = "E" + (i + 5) + ":" + "G" + (i + 5);
                switch (j) {
                // rank
                case 0:
                    Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA);
                    rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(H" + (i + 4) + "=H" + (i + 5)
                            + ",A" + (i + 4) + ",ROW()-4))");
                    break;
                // best
                case 7:
                    Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA);
                    best.setCellFormula("IF(MIN(" + range + ")>0,MIN(" + range + "),IF(COUNTBLANK(" + range
                            + ")=3,\"\",\"DNF\"))");
                    break;
                }
            } else if (SHEET_TYPE_MULTIBF1.equals(template.getSheetName())) {
                switch (j) {
                // rank
                case 0:
                    Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA);
                    rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(I" + (i + 4) + "=I" + (i + 5)
                            + ",A" + (i + 4) + ",ROW()-4))");
                    break;
                // result
                case 8:
                    Cell result = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA);
                    result.setCellFormula("IF(E" + (i + 5) + "-F" + (i + 5) + ">F" + (i + 5) + ",-1,(99-F"
                            + (i + 5) + "+E" + (i + 5) + "-F" + (i + 5) + ")*10000000+G" + (i + 5) + "*100+E"
                            + (i + 5) + "-F" + (i + 5) + ")");
                    break;
                }
            } else if (SHEET_TYPE_MULTIBF2.equals(template.getSheetName())) {
                switch (j) {
                // rank
                case 0:
                    Cell rank = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA);
                    rank.setCellFormula("IF(COUNTBLANK(B" + (i + 5) + ")>0,\"\",IF(I" + (i + 4) + "=I" + (i + 5)
                            + ",A" + (i + 4) + ",ROW()-4))");
                    break;
                // result1
                case 7:
                    Cell result1 = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA);
                    result1.setCellFormula("IF(E" + (i + 5) + "=\"DNS\",-2,IF(E" + (i + 5) + "-F" + (i + 5)
                            + ">F" + (i + 5) + ",-1,(99-F" + (i + 5) + "+E" + (i + 5) + "-F" + (i + 5)
                            + ")*10000000+G" + (i + 5) + "*100+E" + (i + 5) + "-F" + (i + 5) + "))");
                    break;
                // result2
                case 11:
                    Cell result2 = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA);
                    result2.setCellFormula("IF(I" + (i + 5) + "=\"DNS\",-2,IF(I" + (i + 5) + "-J" + (i + 5)
                            + ">J" + (i + 5) + ",-1,(99-J" + (i + 5) + "+I" + (i + 5) + "-J" + (i + 5)
                            + ")*10000000+K" + (i + 5) + "*100+I" + (i + 5) + "-J" + (i + 5) + "))");
                    break;
                // best
                case 12:
                    Cell best = getCell(resultSheet, i + 4, j, Cell.CELL_TYPE_FORMULA);
                    best.setCellFormula("IF(AND(H" + (i + 5) + "<0,L" + (i + 5) + "<0),-1,IF(" + (i + 5)
                            + "<0,L" + (i + 5) + ",IF(L" + (i + 5) + "<0,H" + (i + 5) + ",MIN(H" + (i + 5)
                            + ",L" + (i + 5) + "))))");
                    break;
                }
            } else {
                log.error("Unsupported sheet type: {}", template.getSheetName());
            }

            // set cell style
            Row row = resultSheet.getRow(i + 4);
            if (row != null) {
                Cell cell = row.getCell(j);
                if (cell != null) {
                    cell.setCellStyle(cellStyles.get(j));
                }
            }
        }
    }

    // fill sheet with competitors for this event
    if (includeCompetitors) {
        try {
            generateCompetitorRows(workBook, resultSheet, competition.getCompetitorsByEvent(event), 4);
        } catch (Exception e) {
            log.error("[{}] " + e.getLocalizedMessage(), e);
            throw new RuntimeException("Could not include competitors in this sheet.", e);
        }
    }
}

From source file:org.bbreak.excella.reports.processor.ReportProcessor.java

License:Open Source License

/**
 * ???????/*ww w.  j ava2  s  . c  o  m*/
 *
 * @param workbook 
 * @param reportBook 
 * @return ?????
 */
private Set<String> expandTemplate(Workbook workbook, ReportBook reportBook) {

    Set<String> delSheetNames = new TreeSet<String>(Collections.reverseOrder());
    Set<String> useSheetNames = new HashSet<String>();

    // ????
    for (ReportSheet reportSheet : reportBook.getReportSheets()) {

        if (reportSheet != null) {
            if (reportSheet.getSheetName().equals(reportSheet.getTemplateName())) {
                // ?????
                int lastSheetIndex = workbook.getNumberOfSheets() - 1;
                workbook.setSheetOrder(reportSheet.getSheetName(), lastSheetIndex);
                useSheetNames.add(reportSheet.getTemplateName());
            } else {
                int tempIdx = workbook.getSheetIndex(reportSheet.getTemplateName());
                Sheet sheet = workbook.cloneSheet(tempIdx);
                ReportsUtil.copyPrintSetup(workbook, tempIdx, sheet);
                workbook.setSheetName(workbook.getSheetIndex(sheet), reportSheet.getSheetName());
                delSheetNames.add(reportSheet.getTemplateName());
            }
        }
    }

    // ?
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        Sheet sheet = workbook.getSheetAt(i);
        if (!isOutputSheet(sheet, reportBook)) {
            delSheetNames.add(sheet.getSheetName());
        }
    }

    delSheetNames.removeAll(useSheetNames);

    // ?????

    return delSheetNames;

}

From source file:org.hellojavaer.poi.excel.utils.ExcelUtils.java

License:Apache License

@SuppressWarnings("unchecked")
private static void write(boolean useTemplate, Workbook workbook, OutputStream outputStream,
        ExcelWriteSheetProcessor<?>... sheetProcessors) {

    for (@SuppressWarnings("rawtypes")
    ExcelWriteSheetProcessor sheetProcessor : sheetProcessors) {
        @SuppressWarnings("rawtypes")
        ExcelWriteContext context = new ExcelWriteContext();

        try {// w  ww  . j  av a2  s. c  o m
            if (sheetProcessor == null) {
                continue;
            }
            String sheetName = sheetProcessor.getSheetName();
            Integer sheetIndex = sheetProcessor.getSheetIndex();
            Sheet sheet = null;
            if (sheetProcessor.getTemplateStartRowIndex() == null
                    && sheetProcessor.getTemplateEndRowIndex() == null) {
                sheetProcessor.setTemplateRows(sheetProcessor.getStartRowIndex(),
                        sheetProcessor.getStartRowIndex());
            }
            // sheetName priority,
            if (useTemplate) {
                if (sheetName != null) {
                    try {
                        sheet = workbook.getSheet(sheetName);
                    } catch (IllegalArgumentException e) {
                        // ignore
                    }
                    if (sheet != null && sheetIndex != null
                            && !sheetIndex.equals(workbook.getSheetIndex(sheet))) {
                        throw new IllegalArgumentException(
                                "sheetName[" + sheetName + "] and sheetIndex[" + sheetIndex + "] not match.");
                    }
                } else if (sheetIndex != null) {
                    try {
                        sheet = workbook.getSheetAt(sheetIndex);
                    } catch (IllegalArgumentException e) {
                        // ignore
                    }
                } else {
                    throw new IllegalArgumentException("sheetName or sheetIndex can't be null");
                }
                if (sheet == null) {
                    ExcelWriteException e = new ExcelWriteException(
                            "Sheet Not Found Exception. for sheet name:" + sheetName);
                    e.setCode(ExcelWriteException.CODE_OF_SHEET_NOT_EXSIT);
                    throw e;
                }
            } else {
                if (sheetName != null) {
                    sheet = workbook.getSheet(sheetName);
                    if (sheet != null) {
                        if (sheetIndex != null && !sheetIndex.equals(workbook.getSheetIndex(sheet))) {
                            throw new IllegalArgumentException("sheetName[" + sheetName + "] and sheetIndex["
                                    + sheetIndex + "] not match.");
                        }
                    } else {
                        sheet = workbook.createSheet(sheetName);
                        if (sheetIndex != null) {
                            workbook.setSheetOrder(sheetName, sheetIndex);
                        }
                    }
                } else if (sheetIndex != null) {
                    sheet = workbook.createSheet();
                    workbook.setSheetOrder(sheet.getSheetName(), sheetIndex);
                } else {
                    throw new IllegalArgumentException("sheetName or sheetIndex can't be null");
                }
            }

            if (sheetIndex == null) {
                sheetIndex = workbook.getSheetIndex(sheet);
            }
            if (sheetName == null) {
                sheetName = sheet.getSheetName();
            }

            // proc sheet
            context.setCurSheet(sheet);
            context.setCurSheetIndex(sheetIndex);
            context.setCurSheetName(sheet.getSheetName());
            context.setCurRow(null);
            context.setCurRowIndex(null);
            context.setCurCell(null);
            context.setCurColIndex(null);
            // beforeProcess
            sheetProcessor.beforeProcess(context);
            // write head
            writeHead(useTemplate, sheet, sheetProcessor);
            // sheet
            ExcelProcessControllerImpl controller = new ExcelProcessControllerImpl();
            int writeRowIndex = sheetProcessor.getStartRowIndex();
            boolean isBreak = false;
            Map<Integer, InnerRow> cacheForTemplateRow = new HashMap<Integer, InnerRow>();

            List<?> dataList = sheetProcessor.getDataList(); //
            if (dataList != null && !dataList.isEmpty()) {
                for (Object rowData : dataList) {
                    // proc row
                    Row row = sheet.getRow(writeRowIndex);
                    if (row == null) {
                        row = sheet.createRow(writeRowIndex);
                    }
                    InnerRow templateRow = getTemplateRow(cacheForTemplateRow, sheet, sheetProcessor,
                            writeRowIndex);
                    if (templateRow != null) {
                        row.setHeight(templateRow.getHeight());
                        row.setHeightInPoints(templateRow.getHeightInPoints());
                        row.setRowStyle(templateRow.getRowStyle());
                        row.setZeroHeight(templateRow.isZeroHeight());
                    }
                    context.setCurRow(row);
                    context.setCurRowIndex(writeRowIndex);
                    context.setCurColIndex(null);
                    context.setCurCell(null);
                    //
                    try {
                        controller.reset();
                        if (sheetProcessor.getRowProcessor() != null) {
                            sheetProcessor.getRowProcessor().process(controller, context, rowData, row);
                        }
                        if (!controller.isDoSkip()) {
                            writeRow(context, templateRow, row, rowData, sheetProcessor);
                            writeRowIndex++;
                        }
                        if (controller.isDoBreak()) {
                            isBreak = true;
                            break;
                        }
                    } catch (RuntimeException e) {
                        if (e instanceof ExcelWriteException) {
                            ExcelWriteException ewe = (ExcelWriteException) e;
                            // ef.setColIndex(null); user may want to set this value,
                            ewe.setRowIndex(writeRowIndex);
                            throw ewe;
                        } else {
                            ExcelWriteException ewe = new ExcelWriteException(e);
                            ewe.setColIndex(null);
                            ewe.setCode(ExcelWriteException.CODE_OF_PROCESS_EXCEPTION);
                            ewe.setRowIndex(writeRowIndex);
                            throw ewe;
                        }
                    }
                }
                if (isBreak) {
                    break;
                }
            }
            if (sheetProcessor.getTemplateStartRowIndex() != null
                    && sheetProcessor.getTemplateEndRowIndex() != null) {
                writeDataValidations(sheet, sheetProcessor);
                writeStyleAfterFinish(useTemplate, sheet, sheetProcessor);
            }
        } catch (RuntimeException e) {
            sheetProcessor.onException(context, e);
        } finally {
            sheetProcessor.afterProcess(context);
        }
    }

    try {
        workbook.write(outputStream);
    } catch (IOException e) {
        throw new RuntimeException(e);
    }
}

From source file:qmul.align.AlignmentTester.java

License:Open Source License

/**
 * Print a summary sheet on the (gulp) excel spreadsheet
 * // w  ww . j  av  a 2 s  .  c o m
 * @param wb
 * @param sheetName
 * @param speakerScores
 * @param originalSpks
 * @param speakerN
 */
private void printSummarySheet(Workbook wb, String sheetName, HashMap<String, ArrayList<Double>> speakerScores,
        HashMap<String, String> originalSpks, HashMap<String, ArrayList<Double>> speakerN,
        MetricsMap spkMetrics, MetricsMap totMetrics, boolean pairedCorpus) {

    CreationHelper creationHelper = wb.getCreationHelper();
    sheetName = (sheetName == null ? "Summary" : shorten(sheetName));
    System.out.println("Checking workbook " + wb + " for sheet " + sheetName);
    Sheet sheet = wb.getSheet(sheetName);
    if (sheet != null) {
        System.out.println("Exists, removing sheet " + sheetName);
        wb.removeSheetAt(wb.getSheetIndex(sheet));
    }
    sheet = wb.createSheet(sheetName);
    wb.setSheetOrder(sheetName, 0);
    int iRow = 0;
    // first general identifying stuff
    Row row = sheet.createRow(iRow++);
    row.createCell(0, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Corpus"));
    row.createCell(1, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString(getCorpus().getId()));
    row = sheet.createRow(iRow++);
    row.createCell(0, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Windower"));
    row.createCell(1, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString(getWin().toString()));
    row = sheet.createRow(iRow++);
    row.createCell(0, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString("Similarity Measure"));
    row.createCell(1, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString(getSim().toString()));
    // now header
    row = sheet.createRow(iRow++);
    row = sheet.createRow(iRow++);
    int iCol = 0;
    row.createCell(iCol++, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Speaker"));
    row.createCell(iCol++, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Genre"));
    row.createCell(iCol++, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString("Orig Speaker"));
    row.createCell(iCol++, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString("Orig Genre"));
    row.createCell(iCol++, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString("Speaker #units"));
    row.createCell(iCol++, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString("Dialogue #units"));
    row.createCell(iCol++, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString("Speaker #words"));
    row.createCell(iCol++, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString("Dialogue #words"));
    row.createCell(iCol++, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString("Speaker #tokens"));
    row.createCell(iCol++, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString("Dialogue #tokens"));
    row.createCell(iCol++, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString("Speaker avg offset"));
    row.createCell(iCol++, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString("Dialogue avg offset"));
    row.createCell(iCol++, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString("Speaker avg wordrate"));
    row.createCell(iCol++, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString("Dialogue avg wordrate"));
    iCol++;
    for (int i = 0; i < getWin().getLeftWindowSize(); i++) {
        row.createCell(i + iCol, Cell.CELL_TYPE_STRING)
                .setCellValue(creationHelper.createRichTextString("Mean i-" + (i + 1)));
    }
    // now means per speaker
    List<String> spks = new ArrayList<String>(speakerScores.keySet());
    Collections.sort(spks);
    List<Double> means = new ArrayList<Double>();
    List<Double> nums = new ArrayList<Double>();
    for (int i = 0; i < getWin().getLeftWindowSize(); i++) {
        means.add(0.0);
        nums.add(0.0);
    }
    int nAll = 0;
    int nMatch = 0;
    for (String spk : spks) {
        // System.out.println("org chk [" + originalSpks.get(spk) + "][" + spk + "]");
        boolean matching = false;
        if ((originalSpks.get(spk) != null) && originalSpks.get(spk).contains(":")) {
            int li = originalSpks.get(spk).lastIndexOf(":");
            String pre = originalSpks.get(spk).substring(0, li);
            String suf = originalSpks.get(spk).substring(li);
            matching = spk.startsWith(pre) && spk.endsWith(suf);
        }
        nAll++;
        if (!pairedCorpus || matching) {
            nMatch++;
            // System.out.println("match " + pre + " " + suf);
            row = sheet.createRow(iRow++);
            iCol = 0;
            String dId = spk.replaceFirst("(.*)_.*", "$1");
            row.createCell(iCol++, Cell.CELL_TYPE_STRING)
                    .setCellValue(creationHelper.createRichTextString(spk));
            row.createCell(iCol++, Cell.CELL_TYPE_STRING).setCellValue(
                    creationHelper.createRichTextString(corpus.getGenreMap().get(spk.split(":")[0])));
            row.createCell(iCol++, Cell.CELL_TYPE_STRING)
                    .setCellValue(creationHelper.createRichTextString(originalSpks.get(spk)));
            row.createCell(iCol++, Cell.CELL_TYPE_STRING).setCellValue(creationHelper
                    .createRichTextString(corpus.getGenreMap().get(originalSpks.get(spk).split(":")[0])));
            row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(spkMetrics.getNumUnits(spk));
            row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(totMetrics.getNumUnits(dId));
            row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(spkMetrics.getNumWords(spk));
            row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(totMetrics.getNumWords(dId));
            row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(spkMetrics.getNumTokens(spk));
            row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(totMetrics.getNumTokens(dId));
            if (Double.isNaN(spkMetrics.getTurnOffset(spk)) || spkMetrics.getNumTurnOffsets(spk) == 0) {
                iCol++;
            } else {
                row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(
                        spkMetrics.getTurnOffset(spk) / (double) spkMetrics.getNumTurnOffsets(spk));
            }
            if (Double.isNaN(totMetrics.getTurnOffset(dId)) || totMetrics.getNumTurnOffsets(dId) == 0) {
                iCol++;
            } else {
                row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(
                        totMetrics.getTurnOffset(dId) / (double) totMetrics.getNumTurnOffsets(dId));
            }
            if (Double.isNaN(spkMetrics.getWordRate(spk)) || spkMetrics.getNumWordRates(spk) == 0) {
                iCol++;
            } else {
                row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC)
                        .setCellValue(spkMetrics.getWordRate(spk) / (double) spkMetrics.getNumWordRates(spk));
            }
            if (Double.isNaN(totMetrics.getWordRate(dId)) || totMetrics.getNumWordRates(dId) == 0) {
                iCol++;
            } else {
                row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC)
                        .setCellValue(totMetrics.getWordRate(dId) / (double) totMetrics.getNumWordRates(dId));
            }
            iCol++;
            for (int i = 0; i < speakerScores.get(spk).size(); i++) {
                if (speakerN.get(spk).get(i) > 0.0) {
                    double mean = speakerScores.get(spk).get(i) / speakerN.get(spk).get(i);
                    row.createCell(i + iCol, Cell.CELL_TYPE_NUMERIC).setCellValue(mean);
                    means.set(i, means.get(i) + mean);
                    nums.set(i, nums.get(i) + 1);
                }
            }
        }
    }
    System.out.println("Matched " + nMatch + " of " + nAll);
    // and a final row for overall means
    row = sheet.createRow(iRow++);
    iCol = 14;
    row.createCell(iCol++, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Overall"));
    for (int i = 0; i < getWin().getLeftWindowSize(); i++) {
        means.set(i, means.get(i) / nums.get(i));
        row.createCell(i + iCol, Cell.CELL_TYPE_NUMERIC).setCellValue(means.get(i));
    }
}

From source file:qmul.align.AlignmentTester.java

License:Open Source License

/**
 * Print a summary sheet on the (gulp) excel spreadsheet
 *//*w  w w .  ja v a2  s .  c om*/
private void printSummaryCountSheet(Workbook wb, String sheetName,
        HashMap<String, HashMap<Object, Integer>> allCounts,
        HashMap<String, HashMap<Object, Integer>> commonCounts) {

    CreationHelper creationHelper = wb.getCreationHelper();
    sheetName = (sheetName == null ? "Summary" : shorten(sheetName));
    System.out.println("Checking workbook " + wb + " for sheet " + sheetName);
    Sheet sheet = wb.getSheet(sheetName);
    if (sheet != null) {
        System.out.println("Exists, removing sheet " + sheetName);
        wb.removeSheetAt(wb.getSheetIndex(sheet));
    }
    sheet = wb.createSheet(sheetName);
    wb.setSheetOrder(sheetName, 0);
    int iRow = 0;
    // first general identifying stuff
    Row row = sheet.createRow(iRow++);
    row.createCell(0, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Corpus"));
    row.createCell(1, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString(getCorpus().getId()));
    row = sheet.createRow(iRow++);
    row.createCell(0, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Windower"));
    row.createCell(1, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString(getWin().toString()));
    row = sheet.createRow(iRow++);
    row.createCell(0, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString("Similarity Measure"));
    row.createCell(1, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString(getSim().toString()));
    // now header
    row = sheet.createRow(iRow++);
    row = sheet.createRow(iRow++);
    int iCol = 0;
    row.createCell(iCol++, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Type"));
    row.createCell(iCol++, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString("Overall count"));
    row.createCell(iCol++, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString("Common count"));
    for (String genre : allCounts.keySet()) {
        if (genre.isEmpty())
            continue;
        row.createCell(iCol++, Cell.CELL_TYPE_STRING)
                .setCellValue(creationHelper.createRichTextString(genre + " overall count"));
        row.createCell(iCol++, Cell.CELL_TYPE_STRING)
                .setCellValue(creationHelper.createRichTextString(genre + " common count"));
    }
    ArrayList<Object> keys = new ArrayList<Object>(allCounts.get("").keySet());
    Collections.sort(keys, new DescendingComparator<Object>(allCounts.get("")));
    for (Object key : keys) {
        row = sheet.createRow(iRow++);
        iCol = 0;
        row.createCell(iCol++, Cell.CELL_TYPE_STRING)
                .setCellValue(creationHelper.createRichTextString(key.toString()));
        Cell cell = row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC);
        if (allCounts.get("").get(key) != null) {
            cell.setCellValue(allCounts.get("").get(key));
        }
        cell = row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC);
        if (commonCounts.get("").get(key) != null) {
            cell.setCellValue(commonCounts.get("").get(key));
        }
        for (String genre : allCounts.keySet()) {
            if (genre.isEmpty())
                continue;
            cell = row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC);
            if (allCounts.get(genre).get(key) != null) {
                cell.setCellValue(allCounts.get(genre).get(key));
            }
            cell = row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC);
            if (commonCounts.get(genre).get(key) != null) {
                cell.setCellValue(commonCounts.get(genre).get(key));
            }
        }
    }
}