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

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

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

Introduction

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

Prototype

CellStyle createCellStyle();

Source Link

Document

Create a new Cell style and add it to the workbook's style table

Usage

From source file:de.unioninvestment.eai.portal.portlet.crud.export.streaming.ExcelExporter.java

License:Apache License

/**
 * Returns the default data cell style. Obtained from:
 * http://svn.apache.org/repos/asf/poi/*from   www  .j  ava2  s .c o m*/
 * /trunk/src/examples/src/org/apache/poi/ss/examples/TimesheetDemo.java
 * 
 * @param wb
 *            the wb
 * 
 * @return the cell style
 */
private CellStyle defaultDataCellStyle(final Workbook wb) {
    CellStyle style;
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setWrapText(false);
    style.setDataFormat(doubleDataFormat);
    if (!Strings.isNullOrEmpty(fontName)) {
        final Font dataFont = wb.createFont();
        dataFont.setFontName(fontName);
        style.setFont(dataFont);
    }
    return style;
}

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

License:Open Source License

/**
 * @param workBook//from w w w  .ja  v  a  2  s .c  o  m
 * @param template
 * @param competition
 * @param event
 * @param round
 * @param includeCompetitors
 */
private void createResultSheetFromTemplate(Workbook workBook, Sheet template, Competition competition,
        Event event, String round, boolean includeCompetitors) {
    Sheet resultSheet = workBook.cloneSheet(workBook.getSheetIndex(template));
    String sheetName = 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/*www  . j  av  a 2  s .com*/
 * @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:Documentos.ClaseAlmacenGeneral.java

private static void createCell(Workbook wb, Row row, int column, short halign, short valign,
        String strContenido, boolean booBorde, boolean booCabecera) {
    CreationHelper ch = wb.getCreationHelper();
    Cell cell = row.createCell(column);//from w  ww . j  ava  2 s .com
    cell.setCellValue(ch.createRichTextString(strContenido));
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(halign);
    cellStyle.setVerticalAlignment(valign);

    if (booBorde) {
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_DOTTED);
        cellStyle.setBottomBorderColor((short) 8);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_DOTTED);
        cellStyle.setLeftBorderColor((short) 8);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_DOTTED);
        cellStyle.setRightBorderColor((short) 8);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_DOTTED);
        cellStyle.setTopBorderColor((short) 8);
    }
    if (booCabecera) {
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
        cellStyle.setBottomBorderColor((short) 8);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        cellStyle.setLeftBorderColor((short) 8);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        cellStyle.setRightBorderColor((short) 8);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        cellStyle.setTopBorderColor((short) 8);

        cellStyle.setFillForegroundColor(HSSFColor.ROYAL_BLUE.index);
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    }
    cell.setCellStyle(cellStyle);
}

From source file:ec.util.spreadsheet.poi.PoiBookWriter.java

License:EUPL

private static CellStyle createDateStyle(Workbook workbook) {
    //        CreationHelper creationHelper = workbook.getCreationHelper();
    CellStyle result = workbook.createCellStyle();
    //        result.setDataFormat(creationHelper.createDataFormat().getFormat("m/d/yy"));
    result.setDataFormat((short) 14);
    return result;
}

From source file:egovframework.rte.fdl.excel.EgovExcelServiceTest.java

License:Apache License

/**
 * [Flow #-2]  ?  : ? ?  ? ?    ?/*from  w w  w.  jav  a  2  s  .  c om*/
 */
@Test
public void testModifyCellContents() throws Exception {

    try {
        String content = "Use \n with word wrap on to create a new line";
        short rownum = 2;
        int cellnum = 2;

        LOGGER.debug("testModifyCellContents start....");

        StringBuffer sb = new StringBuffer();
        sb.append(fileLocation).append("/").append("testModifyCellContents.xls");

        if (!EgovFileUtil.isExistsFile(sb.toString())) {
            Workbook wbT = new HSSFWorkbook();
            wbT.createSheet();

            //  ? ?
            excelService.createWorkbook(wbT, sb.toString());
        }

        //  ? 
        Workbook wb = excelService.loadWorkbook(sb.toString());
        LOGGER.debug("testModifyCellContents after loadWorkbook....");

        Sheet sheet = wb.getSheetAt(0);
        Font f2 = wb.createFont();
        CellStyle cs = wb.createCellStyle();
        cs = wb.createCellStyle();

        cs.setFont(f2);
        //Word Wrap MUST be turned on
        cs.setWrapText(true);

        Row row = sheet.createRow(rownum);
        row.setHeight((short) 0x349);
        Cell cell = row.createCell(cellnum);
        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue(new HSSFRichTextString(content));
        cell.setCellStyle(cs);

        sheet.setColumnWidth(20, (int) ((50 * 8) / ((double) 1 / 20)));

        //excelService.writeWorkbook(wb);

        FileOutputStream out = new FileOutputStream(sb.toString());
        wb.write(out);
        out.close();

        //  ? 
        Workbook wb1 = excelService.loadWorkbook(sb.toString());

        Sheet sheet1 = wb1.getSheetAt(0);
        Row row1 = sheet1.getRow(rownum);
        Cell cell1 = row1.getCell(cellnum);

        // ? ?  ?
        LOGGER.debug("cell ###{}###", cell1.getRichStringCellValue());
        LOGGER.debug("cont ###{}###", content);

        assertNotSame("TEST", cell1.getRichStringCellValue().toString());
        assertEquals(content, cell1.getRichStringCellValue().toString());

    } catch (Exception e) {
        LOGGER.error(e.toString());
        throw new Exception(e);
    } finally {
        LOGGER.debug("testModifyCellContents end....");
    }
}

From source file:egovframework.rte.fdl.excel.EgovExcelServiceTest.java

License:Apache License

/**
 * [Flow #-3]  ? ?  :  ?? ?(? ?, Border? ?, ? ?,  )? 
 *//*  ww w.  j  a  va2 s. c  o m*/
@Test
public void testWriteExcelFileAttribute() throws Exception {

    try {
        LOGGER.debug("testWriteExcelFileAttribute start....");

        short rowheight = 40 * 10;
        int columnwidth = 30;

        StringBuffer sb = new StringBuffer();
        sb.append(fileLocation).append("/").append("testWriteExcelFileAttribute.xls");

        // delete file
        if (EgovFileUtil.isExistsFile(sb.toString())) {
            EgovFileUtil.delete(new File(sb.toString()));

            LOGGER.debug("Delete file....{}", sb.toString());
        }

        Workbook wb = new HSSFWorkbook();

        Sheet sheet1 = wb.createSheet("new sheet");
        wb.createSheet("second sheet");

        // ? ?
        sheet1.setDefaultRowHeight(rowheight);
        sheet1.setDefaultColumnWidth(columnwidth);

        Font f2 = wb.createFont();
        CellStyle cs = wb.createCellStyle();
        cs = wb.createCellStyle();

        cs.setFont(f2);
        cs.setWrapText(true);

        // 
        cs.setAlignment(CellStyle.ALIGN_RIGHT);

        cs.setFillPattern(CellStyle.DIAMONDS); //  ?

        // ? ?
        cs.setFillForegroundColor(new HSSFColor.BLUE().getIndex()); //  
        cs.setFillBackgroundColor(new HSSFColor.RED().getIndex()); // 

        sheet1.setDefaultColumnStyle((short) 0, cs);

        Workbook tmp = excelService.createWorkbook(wb, sb.toString());

        Sheet sheetTmp1 = tmp.getSheetAt(0);

        assertEquals(rowheight, sheetTmp1.getDefaultRowHeight());
        assertEquals(columnwidth, sheetTmp1.getDefaultColumnWidth());

        CellStyle cs1 = tmp.getCellStyleAt((short) (tmp.getNumCellStyles() - 1));

        LOGGER.debug("getAlignment : {}", cs1.getAlignment());
        assertEquals(CellStyle.ALIGN_RIGHT, cs1.getAlignment());

        LOGGER.debug("getFillPattern : {}", cs1.getFillPattern());
        assertEquals(CellStyle.DIAMONDS, cs1.getFillPattern());

        LOGGER.debug("getFillForegroundColor : {}", cs1.getFillForegroundColor());
        LOGGER.debug("getFillBackgroundColor : {}", cs1.getFillBackgroundColor());
        assertEquals(new HSSFColor.BLUE().getIndex(), cs1.getFillForegroundColor());
        assertEquals(new HSSFColor.RED().getIndex(), cs1.getFillBackgroundColor());

    } catch (Exception e) {
        LOGGER.error(e.toString());
        throw new Exception(e);
    } finally {
        LOGGER.debug("testWriteExcelFileAttribute end....");
    }
}

From source file:egovframework.rte.fdl.excel.EgovExcelServiceTest.java

License:Apache License

/**
 * [Flow #-5]    :  ?? ?  ? ?  /*from w  ww  .  j  a  v  a2  s.  c o  m*/
 */
@Test
public void testGetCellContents() throws Exception {

    try {
        LOGGER.debug("testGetCellContents start....");

        StringBuffer sb = new StringBuffer();
        sb.append(fileLocation).append("/").append("testGetCellContents.xls");

        if (EgovFileUtil.isExistsFile(sb.toString())) {
            EgovFileUtil.delete(new File(sb.toString()));

            LOGGER.debug("Delete file....{}", sb.toString());
        }

        Workbook wbTmp = new HSSFWorkbook();
        wbTmp.createSheet();

        //  ? ?
        excelService.createWorkbook(wbTmp, sb.toString());

        //  ? 
        Workbook wb = excelService.loadWorkbook(sb.toString());
        LOGGER.debug("testGetCellContents after loadWorkbook....");

        Sheet sheet = wb.createSheet("cell test sheet");

        CellStyle cs = wb.createCellStyle();
        cs = wb.createCellStyle();
        cs.setWrapText(true);

        for (int i = 0; i < 100; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 5; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(new HSSFRichTextString("row " + i + ", cell " + j));
                cell.setCellStyle(cs);
            }
        }

        //  ? 
        FileOutputStream out = new FileOutputStream(sb.toString());
        wb.write(out);
        out.close();

        //////////////////////////////////////////////////////////////////////////
        // ?
        Workbook wbT = excelService.loadWorkbook(sb.toString());
        Sheet sheetT = wbT.getSheet("cell test sheet");

        for (int i = 0; i < 100; i++) {
            Row row1 = sheetT.getRow(i);
            for (int j = 0; j < 5; j++) {
                Cell cell1 = row1.getCell(j);
                LOGGER.debug("row {}, cell {} : {}", i, j, cell1.getRichStringCellValue());
                assertEquals("row " + i + ", cell " + j, cell1.getRichStringCellValue().toString());
            }
        }

    } catch (Exception e) {
        LOGGER.error(e.toString());
        throw new Exception(e);
    } finally {
        LOGGER.debug("testGetCellContents end....");
    }
}

From source file:egovframework.rte.fdl.excel.EgovExcelServiceTest.java

License:Apache License

/**
 * [Flow #-6]  ?  :  ? ?(?, ? )? /*from www.  jav a 2 s  . co  m*/
 */
@Test
public void testModifyCellAttribute() throws Exception {

    try {
        LOGGER.debug("testModifyCellAttribute start....");

        StringBuffer sb = new StringBuffer();
        sb.append(fileLocation).append("/").append("testModifyCellAttribute.xls");

        if (EgovFileUtil.isExistsFile(sb.toString())) {
            EgovFileUtil.delete(new File(sb.toString()));

            LOGGER.debug("Delete file....{}", sb.toString());
        }

        Workbook wbTmp = new HSSFWorkbook();
        wbTmp.createSheet();

        //  ? ?
        excelService.createWorkbook(wbTmp, sb.toString());

        //  ? 
        Workbook wb = excelService.loadWorkbook(sb.toString());
        LOGGER.debug("testModifyCellAttribute after loadWorkbook....");

        Sheet sheet = wb.createSheet("cell test sheet2");
        //           sheet.setColumnWidth((short) 3, (short) 200);   // column Width

        CellStyle cs = wb.createCellStyle();
        Font font = wb.createFont();
        font.setFontHeight((short) 16);
        font.setBoldweight((short) 3);
        font.setFontName("fixedsys");

        cs.setFont(font);
        cs.setAlignment(CellStyle.ALIGN_RIGHT); // cell 
        cs.setWrapText(true);

        for (int i = 0; i < 100; i++) {
            Row row = sheet.createRow(i);
            //              row.setHeight((short)300); // row? height 

            for (int j = 0; j < 5; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(new HSSFRichTextString("row " + i + ", cell " + j));
                cell.setCellStyle(cs);
            }
        }

        //  ? 
        FileOutputStream out = new FileOutputStream(sb.toString());
        wb.write(out);
        out.close();

        //////////////////////////////////////////////////////////////////////////
        // ?
        Workbook wbT = excelService.loadWorkbook(sb.toString());
        Sheet sheetT = wbT.getSheet("cell test sheet2");
        LOGGER.debug("getNumCellStyles : {}", wbT.getNumCellStyles());

        CellStyle cs1 = wbT.getCellStyleAt((short) (wbT.getNumCellStyles() - 1));

        Font fontT = ((HSSFCellStyle) cs1).getFont(wbT);
        LOGGER.debug("font getFontHeight : {}", fontT.getFontHeight());
        LOGGER.debug("font getBoldweight : {}", fontT.getBoldweight());
        LOGGER.debug("font getFontName : {}", fontT.getFontName());
        LOGGER.debug("getAlignment : {}", cs1.getAlignment());
        LOGGER.debug("getWrapText : {}", cs1.getWrapText());

        for (int i = 0; i < 100; i++) {
            Row row1 = sheetT.getRow(i);
            for (int j = 0; j < 5; j++) {
                Cell cell1 = row1.getCell(j);
                LOGGER.debug("row {}, cell {} : {}", i, j, cell1.getRichStringCellValue());
                assertEquals(16, fontT.getFontHeight());
                assertEquals(3, fontT.getBoldweight());
                assertEquals(CellStyle.ALIGN_RIGHT, cs1.getAlignment());
                assertTrue(cs1.getWrapText());
            }
        }

    } catch (Exception e) {
        LOGGER.error(e.toString());
        throw new Exception(e);
    } finally {
        LOGGER.debug("testModifyCellAttribute end....");
    }
}

From source file:egovframework.rte.fdl.excel.EgovExcelXSSFServiceTest.java

License:Apache License

/**
 * [Flow #-5]    :  ?? ?  ? ?  /*from w  w  w  .ja  v  a 2s  .co  m*/
 */
@Test
public void testGetCellContents() throws Exception {

    try {
        LOGGER.debug("testGetCellContents start....");

        StringBuffer sb = new StringBuffer();
        sb.append(fileLocation).append("/").append("testGetCellContents.xlsx");

        if (EgovFileUtil.isExistsFile(sb.toString())) {
            EgovFileUtil.delete(new File(sb.toString()));

            LOGGER.debug("Delete file....{}", sb.toString());
        }

        Workbook wbTmp = new XSSFWorkbook();
        wbTmp.createSheet();

        //  ? ?
        excelService.createWorkbook(wbTmp, sb.toString());

        //  ? 
        Workbook wb = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook());
        LOGGER.debug("testGetCellContents after loadWorkbook....");

        Sheet sheet = wb.createSheet("cell test sheet");

        CellStyle cs = wb.createCellStyle();
        cs = wb.createCellStyle();
        cs.setWrapText(true);

        for (int i = 0; i < 100; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 5; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(new XSSFRichTextString("row " + i + ", cell " + j));
                cell.setCellStyle(cs);
            }
        }

        //  ? 
        FileOutputStream out = new FileOutputStream(sb.toString());
        wb.write(out);
        out.close();

        //////////////////////////////////////////////////////////////////////////
        // ?
        Workbook wbT = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook());
        Sheet sheetT = wbT.getSheet("cell test sheet");

        for (int i = 0; i < 100; i++) {
            Row row1 = sheetT.getRow(i);
            for (int j = 0; j < 5; j++) {
                Cell cell1 = row1.getCell(j);
                LOGGER.debug("row {}, cell : {}", i, j, cell1.getRichStringCellValue());
                assertEquals("row " + i + ", cell " + j, cell1.getRichStringCellValue().toString());
            }
        }

    } catch (Exception e) {
        LOGGER.error(e.toString());
        throw new Exception(e);
    } finally {
        LOGGER.debug("testGetCellContents end....");
    }
}