Example usage for org.apache.poi.ss.util CellRangeAddress valueOf

List of usage examples for org.apache.poi.ss.util CellRangeAddress valueOf

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.util.*
  5. CellRangeAddress
  6. valueOf

Introduction

In this page you can find the example usage for org.apache.poi.ss.util CellRangeAddress valueOf.

Prototype

public static CellRangeAddress valueOf(String ref)

Source Link

Document

Creates a CellRangeAddress from a cell range reference string.

Usage

From source file:nz.ac.auckland.abi.formatting.poi.ModelJSONToExcel.java

License:LGPL

private void addWorkSheet(Workbook wb, String measure, List<FEMModelMeasure> measures, int maxTimePoints,
        Map<String, CellStyle> styles) {
    Sheet sheet = wb.createSheet(measure);
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);//from   w  w  w. j a v a  2  s .c om
    sheet.setHorizontallyCenter(true);

    final int leadingHeaders = 4;
    int rowCounter = 0;
    // Create header row
    Row headerRow = sheet.createRow(rowCounter++);
    headerRow.setHeightInPoints(40);
    Cell headerCell;
    int colCtr = 0;
    {
        headerCell = headerRow.createCell(colCtr++);
        headerCell.setCellValue("ModelName");
        headerCell.setCellStyle(styles.get("header"));
    }
    {
        headerCell = headerRow.createCell(colCtr++);
        headerCell.setCellValue("StartTime");
        headerCell.setCellStyle(styles.get("header"));
    }
    {
        headerCell = headerRow.createCell(colCtr++);
        headerCell.setCellValue("EndTime");
        headerCell.setCellStyle(styles.get("header"));
    }
    {
        headerCell = headerRow.createCell(colCtr++);
        headerCell.setCellValue("MetaData");
        headerCell.setCellStyle(styles.get("header"));
    }
    {
        headerCell = headerRow.createCell(colCtr++);
        headerCell.setCellValue("ID");
        headerCell.setCellStyle(styles.get("header"));
    }
    // Insert Time points
    for (int i = 0; i < maxTimePoints; i++) {
        headerCell = headerRow.createCell(colCtr++);
        headerCell.setCellValue("" + i);
        headerCell.setCellStyle(styles.get("header"));
    }
    // Insert composite variables
    {
        headerCell = headerRow.createCell(colCtr++);
        headerCell.setCellValue("MAX");
        headerCell.setCellStyle(styles.get("header"));
    }
    {
        headerCell = headerRow.createCell(colCtr++);
        headerCell.setCellValue("MIN");
        headerCell.setCellStyle(styles.get("header"));
    }
    {
        headerCell = headerRow.createCell(colCtr++);
        headerCell.setCellValue("MEAN");
        headerCell.setCellStyle(styles.get("header"));
    }
    {
        headerCell = headerRow.createCell(colCtr++);
        headerCell.setCellValue("SD");
        headerCell.setCellStyle(styles.get("header"));
    }
    // Output the values for each measure

    for (FEMModelMeasure mes : measures) {
        double[][] strains = mes.getMeasure(measure);
        if (strains == null)
            continue;
        int numRows = strains.length + 1; // 1 for Avg
        int rowStarts = rowCounter;
        int colCounter = 0;
        for (int rctr = 0; rctr < numRows - 1; rctr++) {
            colCounter = 0;
            int myRowID = rowCounter + 1;
            Row row = sheet.createRow(rowCounter++);
            for (int colc = 0; colc < leadingHeaders; colc++) { // Common
                // Elements
                row.createCell(colCounter++);
            }
            // Create ROW ID
            {
                Cell cell = row.createCell(colCounter++);
                cell.setCellValue("S" + (rctr + 1));
            }
            String strainStartXLColName = getColumnPrefix(colCounter);
            int strainLength = strains[rctr].length;
            for (int stc = 0; stc < strainLength; stc++) {
                Cell cell = row.createCell(colCounter++);
                cell.setCellValue(strains[rctr][stc]);
            }
            String strainEndXLColName = getColumnPrefix(colCounter - 1);
            while (strainLength < maxTimePoints) { // Create dummy cells to
                // fill up space
                row.createCell(colCounter++);
                strainLength++;
            }
            // Add formulas and create names
            {
                //String modelName = mes.getModelName();
                //String sname = "";
                // MAX
                Cell cell = row.createCell(colCounter++);
                String ref = strainStartXLColName + "" + myRowID + ":" + strainEndXLColName + "" + myRowID;
                cell.setCellFormula("MAX(" + ref + ")");
                cell.setCellStyle(styles.get("MAX"));
                /*
                 * sname =
                 * (modelName+"_"+measure+"_"+"MAX_S"+(rctr+1)..replaceAll
                 * (" ", "_").trim(); Name namedCel = wb.createName();
                 * namedCel.setNameName(sname); String reference =
                 * measure+"!"+getColumnPrefix(colCounter-1)+myRowID; //
                 * cell reference namedCel.setRefersToFormula(reference);
                 */
                // MIN
                cell = row.createCell(colCounter++);
                cell.setCellFormula("MIN(" + ref + ")");
                cell.setCellStyle(styles.get("MIN"));
                /*
                 * sname = modelName+"_"+measure+"_"+"MIN_S"+(rctr+1);
                 * namedCel = wb.createName(); namedCel.setNameName(sname);
                 * reference =
                 * measure+"!"+getColumnPrefix(colCounter-1)+myRowID; //
                 * cell reference namedCel.setRefersToFormula(reference);
                 */
                // MEAN
                cell = row.createCell(colCounter++);
                cell.setCellFormula("AVERAGE(" + ref + ")");
                cell.setCellStyle(styles.get("AVERAGE"));
                /*
                 * sname = modelName+"_"+measure+"_"+"AVERAGE_S"+(rctr+1);
                 * namedCel = wb.createName(); namedCel.setNameName(sname);
                 * reference =
                 * measure+"!"+getColumnPrefix(colCounter-1)+myRowID; //
                 * cell reference namedCel.setRefersToFormula(reference);
                 */
                // STANDARD DEVIATION
                cell = row.createCell(colCounter++);
                cell.setCellFormula("STDEV(" + ref + ")");
                cell.setCellStyle(styles.get("STDEV"));
                /*
                 * sname = modelName+"_"+measure+"_"+"STDEV_S"+(rctr+1);
                 * namedCel = wb.createName(); namedCel.setNameName(sname);
                 * reference =
                 * measure+"!"+getColumnPrefix(colCounter-1)+""+myRowID; //
                 * cell reference namedCel.setRefersToFormula(reference);
                 */
            }
        }

        // Add user defined series
        Hashtable<String, String> formulaMap = new Hashtable<String, String>();
        for (String exp : userSeries) {

            // Replace all S[0-9]*, and D[0-9]* with appropriate column
            // values
            String toks[] = exp.split("=");
            int myRowID = rowCounter;
            colCounter = 0;
            Row row = sheet.createRow(rowCounter++);
            for (int colc = 0; colc < leadingHeaders; colc++) { // Common
                // Elements
                row.createCell(colCounter++);
            }
            // Create ROW ID
            {
                Cell cell = row.createCell(colCounter++);
                cell.setCellValue(toks[0]);
            }
            String strainStartXLColName = getColumnPrefix(colCounter);
            int strainLength = strains[numRows - 2].length;
            for (int stc = 0; stc < strainLength; stc++) {
                Cell cell = row.createCell(colCounter++);
                // Get the expression
                String expression = toks[1].toLowerCase();// Regex is case
                // senstive,
                // since th
                // COLUMN
                // PREFIXs are
                // CAPS,
                // replaceAll
                // will work as
                // expected else
                // S17 will
                // mathc for S1
                // (but not s1)
                for (int sCtr = mes.numSegments; sCtr > 0; sCtr--) {
                    String XLColName = (char) ('A' + stc + leadingHeaders + 1) + "" + (rowStarts + sCtr); // Note
                    // excel
                    // formulas
                    // need
                    // base
                    // 1
                    expression = expression.replaceAll("s" + sCtr + "{1}", XLColName);
                }
                cell.setCellFormula(expression);
                cell.setCellStyle(styles.get("AVGSERIES"));
            }
            String strainEndXLColName = getColumnPrefix(colCounter - 1);
            while (strainLength < maxTimePoints) { // Create dummy cells to
                // fill up space
                row.createCell(colCounter++);
                strainLength++;
            }
            // Add formulas and create names
            {
                StringBuffer formulas = new StringBuffer();

                String modelName = mes.getModelName();
                char c = modelName.charAt(0);
                if (c >= '0' && c <= '9') {
                    modelName = "_" + modelName;
                }
                String measureName = measure.replaceAll("\\(", "_").replaceAll("\\)", "_").replaceAll(" ", "");
                String sname = "";
                // MAX
                Cell cell = row.createCell(colCounter++);
                String ref = strainStartXLColName + myRowID + ":" + strainEndXLColName + myRowID;
                cell.setCellFormula("MAX(" + ref + ")");
                cell.setCellStyle(styles.get("MAX"));
                sname = (modelName + "_" + measureName + "_" + "MAX_" + toks[0]).replaceAll(" ", "_").trim();
                Name namedCel = wb.createName();
                namedCel.setNameName(sname);
                String reference = measureName + "!" + getColumnPrefix(colCounter - 1) + (myRowID + 1); // cell
                // reference
                // in
                // xl
                // base
                try {
                    namedCel.setRefersToFormula(reference);
                    formulas.append(reference + "#");
                    // MIN
                    cell = row.createCell(colCounter++);
                    cell.setCellFormula("MIN(" + ref + ")");
                    cell.setCellStyle(styles.get("MIN"));
                    sname = (modelName + "_" + measureName + "_" + "MIN_" + toks[0]).replaceAll(" ", "_")
                            .trim();
                    namedCel = wb.createName();
                    namedCel.setNameName(sname);
                    reference = measureName + "!" + getColumnPrefix(colCounter - 1) + (myRowID + 1); // cell
                    // reference
                    // in
                    // xl
                    // base
                    namedCel.setRefersToFormula(reference);
                    formulas.append(reference + "#");
                    // MEAN
                    cell = row.createCell(colCounter++);
                    cell.setCellFormula("AVERAGE(" + ref + ")");
                    cell.setCellStyle(styles.get("AVERAGE"));
                    sname = (modelName + "_" + measureName + "_" + "AVERAGE_" + toks[0]).replaceAll(" ", "_")
                            .trim();
                    namedCel = wb.createName();
                    namedCel.setNameName(sname);
                    reference = measureName + "!" + getColumnPrefix(colCounter - 1) + (myRowID + 1); // cell
                    // reference
                    // in
                    // xl
                    // base
                    namedCel.setRefersToFormula(reference);
                    formulas.append(reference + "#");
                    // STANDARD DEVIATION
                    cell = row.createCell(colCounter++);
                    cell.setCellFormula("STDEV(" + ref + ")");
                    cell.setCellStyle(styles.get("STDEV"));
                    sname = (modelName + "_" + measureName + "_" + "STDEV_" + toks[0]).replaceAll(" ", "_")
                            .trim();
                    namedCel = wb.createName();
                    namedCel.setNameName(sname);
                    reference = measureName + "!" + getColumnPrefix(colCounter - 1) + (myRowID + 1); // cell
                    // reference
                    // in
                    // xl
                    // base
                    namedCel.setRefersToFormula(reference);
                    formulas.append(reference);
                    formulaMap.put(toks[0], formulas.toString());
                } catch (Exception exx) {
                    //exx.printStackTrace();
                    System.out.println(exx + " occured for formula " + reference);
                }
            }
        }
        mes.addToFormulaMap(measure, formulaMap);
        // Set the commom columns
        Row row;
        Cell cell;
        row = sheet.getRow(rowStarts);
        cell = row.getCell(0);
        cell.setCellValue(mes.getModelName());
        cell = row.getCell(1);
        cell.setCellValue(mes.getStartTime());
        cell = row.getCell(2);
        cell.setCellValue(mes.getEndTime());
        cell = row.getCell(3);
        cell.setCellValue(mes.getMetaData());
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$" + (rowStarts + 1) + ":$A$" + (rowCounter))); // Since
        // excel
        // number
        // starts
        // from
        // 1
        // but
        // api
        // is
        // 0
        sheet.addMergedRegion(CellRangeAddress.valueOf("$B$" + (rowStarts + 1) + ":$B$" + (rowCounter)));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$C$" + (rowStarts + 1) + ":$C$" + (rowCounter)));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$D$" + (rowStarts + 1) + ":$D$" + (rowCounter)));
        sheet.createRow(rowCounter++);// Create Empty row for model break
    }

}

From source file:om.edu.squ.squportal.portlet.tsurvey.dao.excel.TeachingSurveyExcelImpl.java

License:Open Source License

/**
 * //from   www  .  j a v  a 2 s.  c om
 * method name  : getExcelSurveyReport
 * @param object
 * @param response
 * @param params
 * @param locale
 * @return
 * @throws DocumentException
 * @throws IOException
 * TeachingSurveyExcelImpl
 * return type  : OutputStream
 * 
 * purpose      : Get Streaming excel object for valid/invalid survey report
 *
 * Date          :   Mar 16, 2016 1:23:57 PM
 */
public OutputStream getExcelSurveyReport(String templateName, Object object, ResourceResponse response,
        Map<String, String> params, Locale locale) throws DocumentException, IOException {
    int colHead = 0;
    int rowNum = 0;
    String paramStaffRole = params.get(Constants.CONST_ROLE_STAFF);
    String paramTypeSurvey = params.get(Constants.CONST_PARAM_TYPE_SURVEY);
    String paramSemesterCode = params.get(Constants.CONST_PARAM_SEMESTER_CODE);
    String titleRegion = null;

    Workbook workbook = new HSSFWorkbook();
    CreationHelper creationHelper = workbook.getCreationHelper();
    Map<String, CellStyle> styles = createStyles(workbook);
    Sheet sheet = null;
    Cell cellSH = null;
    List<ReportSummary> reportSummaries = (List<ReportSummary>) object;

    if (templateName.equals(Constants.CONST_VALID_SURVEY_REPORT)) {
        sheet = workbook.createSheet(
                UtilProperty.getMessage("prop.course.teaching.survey.report.survey.valid", null, locale));
    }
    if (templateName.equals(Constants.CONST_INVALID_SURVEY_REPORT)) {
        sheet = workbook.createSheet(
                UtilProperty.getMessage("prop.course.teaching.survey.report.survey.invalid", null, locale));
    }

    sheet.getPrintSetup().setLandscape(true);
    sheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);

    /**  Header Footer **/
    Footer footer = sheet.getFooter();
    Header header = sheet.getHeader();
    footer.setRight("Page &P of &N");
    footer.setLeft("&D");
    header.setLeft(UtilProperty.getMessage("prop.course.teaching.survey.heading", null, locale));
    header.setCenter(UtilProperty.getMessage("prop.course.teaching.survey.title", null, locale));
    header.setRight(paramTypeSurvey + " - " + paramSemesterCode);

    sheet.setRepeatingRows(CellRangeAddress.valueOf("2:2"));
    sheet.setDisplayGridlines(true);
    sheet.setPrintGridlines(true);

    /**  Title **/
    Row titleRow = sheet.createRow(rowNum);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue(paramTypeSurvey + " - " + paramSemesterCode);
    titleCell.setCellStyle(styles.get(TITLE));

    ++rowNum;
    titleRegion = "$A$" + rowNum + ":$O$" + rowNum;
    sheet.addMergedRegion(CellRangeAddress.valueOf(titleRegion));

    /**  Header Row **/
    Row rowSubHeader = sheet.createRow(rowNum++);

    if (!paramStaffRole.equals(Constants.CONST_ROLE_STAFF_HOD)
            && !templateName.equals(Constants.CONST_INVALID_SURVEY_REPORT)) {

        cellSH = rowSubHeader.createCell(colHead++);
        cellSH.setCellValue(creationHelper.createRichTextString(
                UtilProperty.getMessage("prop.course.teaching.survey.rank.university", null, locale)));
        cellSH.setCellStyle(styles.get(SUB_HEADER));
        cellSH = rowSubHeader.createCell(colHead++);
        cellSH.setCellValue(creationHelper.createRichTextString(
                UtilProperty.getMessage("prop.course.teaching.survey.rank.college", null, locale)));
        cellSH.setCellStyle(styles.get(SUB_HEADER));
        cellSH = rowSubHeader.createCell(colHead++);
        cellSH.setCellValue(creationHelper.createRichTextString(
                UtilProperty.getMessage("prop.course.teaching.survey.rank.department", null, locale)));
        cellSH.setCellStyle(styles.get(SUB_HEADER));
    }
    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.instructor.id", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));
    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.instructor.name", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));
    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.college", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));
    if (!paramStaffRole.equals(Constants.CONST_ROLE_STAFF_HOD)) {
        cellSH = rowSubHeader.createCell(colHead++);
        cellSH.setCellValue(creationHelper.createRichTextString(
                UtilProperty.getMessage("prop.course.teaching.survey.department", null, locale)));
        cellSH.setCellStyle(styles.get(SUB_HEADER));
    }
    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.course.code", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));
    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.section", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));
    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.student.registered", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));
    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.response.number", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));
    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.analysis.mean", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));
    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.analysis.response.percentage", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));
    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.analysis.mean", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));
    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.analysis.response.percentage", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));

    /**  Report details **/
    for (ReportSummary reportSummary : reportSummaries) {
        int colNum = 0;
        Row row = sheet.createRow((short) rowNum);

        if (!paramStaffRole.equals(Constants.CONST_ROLE_STAFF_HOD)
                && !templateName.equals(Constants.CONST_INVALID_SURVEY_REPORT)) {
            row.createCell(colNum++).setCellValue(reportSummary.getUniversityRank());
            row.createCell(colNum++).setCellValue(reportSummary.getCollegeRank());
            row.createCell(colNum++).setCellValue(reportSummary.getDepartmentRank());
        }
        row.createCell(colNum++).setCellValue(Double.parseDouble(reportSummary.getEmpNumber()));
        row.createCell(colNum++).setCellValue(creationHelper.createRichTextString(reportSummary.getEmpName()));
        row.createCell(colNum++)
                .setCellValue(creationHelper.createRichTextString(reportSummary.getCollegeCode()));
        if (!paramStaffRole.equals(Constants.CONST_ROLE_STAFF_HOD)) {
            row.createCell(colNum++)
                    .setCellValue(creationHelper.createRichTextString(reportSummary.getDepartmentName()));
        }
        row.createCell(colNum++).setCellValue(reportSummary.getCourseCode());
        row.createCell(colNum++).setCellValue(Integer.parseInt(reportSummary.getSectionNo()));
        row.createCell(colNum++).setCellValue(reportSummary.getRegisteredStudent());

        Cell cellStudentNoResponse = row.createCell(colNum++);
        cellStudentNoResponse.setCellValue(reportSummary.getStudentNoResponse());
        cellStudentNoResponse.setCellStyle(styles.get(FORMULA_1));

        Cell cellTeachingMean = row.createCell(colNum++);
        cellTeachingMean.setCellValue(reportSummary.getTeachingMean());
        cellTeachingMean.setCellStyle(styles.get(FORMULA_1));

        Cell cellTeachingPercentageFavor = row.createCell(colNum++);
        cellTeachingPercentageFavor.setCellValue(reportSummary.getTeachingPercentageFavor());
        cellTeachingPercentageFavor.setCellStyle(styles.get(FORMULA_1));

        Cell cellQuestionMean = row.createCell(colNum++);
        cellQuestionMean.setCellValue(reportSummary.getQuestionMean());
        cellQuestionMean.setCellStyle(styles.get(FORMULA_1));

        Cell cellQuestionPercentageFavor = row.createCell(colNum++);
        cellQuestionPercentageFavor.setCellValue(reportSummary.getQuestionPercentageFavor());
        cellQuestionPercentageFavor.setCellStyle(styles.get(FORMULA_1));

        rowNum++;

    }

    response.setContentType("application/vnd.ms-excel");
    OutputStream outputStream = response.getPortletOutputStream();
    workbook.write(outputStream);
    outputStream.flush();
    outputStream.close();

    return null;
}

From source file:om.edu.squ.squportal.portlet.tsurvey.dao.excel.TeachingSurveyExcelImpl.java

License:Open Source License

/**
 * //w  w w. j  a v a  2 s.com
 * method name  : getExcelCollegeCoursesAsstDean
 * @param templateName
 * @param object
 * @param response
 * @param params
 * @param locale
 * @return
 * @throws DocumentException
 * @throws IOException
 * TeachingSurveyExcelImpl
 * return type  : OutputStream
 * 
 * purpose      : 
 *
 * Date          :   Jun 7, 2016 11:49:24 AM
 */
public OutputStream getExcelCollegeCoursesAsstDean(String templateName, Object object,
        ResourceResponse response, Map<String, String> params, Locale locale)
        throws DocumentException, IOException {
    int colHead = 0;
    int rowNum = 0;
    String paramTypeSurvey = params.get(Constants.CONST_PARAM_TYPE_SURVEY);
    String titleRegion = null;
    List<StudentResponse> studentResponses = (List<StudentResponse>) object;

    Workbook workbook = new HSSFWorkbook();
    CreationHelper creationHelper = workbook.getCreationHelper();
    Map<String, CellStyle> styles = createStyles(workbook);
    Sheet sheet = null;
    Cell cellSH = null;

    sheet = workbook
            .createSheet(UtilProperty.getMessage("prop.course.teaching.survey.courses.list", null, locale));

    sheet.getPrintSetup().setLandscape(true);
    sheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);

    /**  Header Footer **/
    Footer footer = sheet.getFooter();
    Header header = sheet.getHeader();
    footer.setRight("Page &P of &N");
    footer.setLeft("&D");
    header.setLeft(UtilProperty.getMessage("prop.course.teaching.survey.heading", null, locale));
    header.setCenter(UtilProperty.getMessage("prop.course.teaching.survey.title", null, locale));
    header.setRight(paramTypeSurvey);

    sheet.setRepeatingRows(CellRangeAddress.valueOf("2:2"));
    sheet.setDisplayGridlines(true);
    sheet.setPrintGridlines(true);

    /**  Title **/
    Row titleRow = sheet.createRow(rowNum);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue(paramTypeSurvey);
    titleCell.setCellStyle(styles.get(TITLE));

    ++rowNum;
    titleRegion = "$A$" + rowNum + ":$O$" + rowNum;
    sheet.addMergedRegion(CellRangeAddress.valueOf(titleRegion));

    /**  Header Row **/
    Row rowSubHeader = sheet.createRow(rowNum++);

    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.analysis.department", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));

    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper
            .createRichTextString(UtilProperty.getMessage("prop.course.teaching.survey.course", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));

    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.section", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));

    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.committee.member.number", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));

    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.committee.member.name", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));

    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.seats.taken", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));

    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.response.students", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));

    cellSH = rowSubHeader.createCell(colHead++);
    cellSH.setCellValue(creationHelper.createRichTextString(
            UtilProperty.getMessage("prop.course.teaching.survey.include.exclude", null, locale)));
    cellSH.setCellStyle(styles.get(SUB_HEADER));

    /**  Report details **/
    for (StudentResponse studentResponse : studentResponses) {
        int colNum = 0;
        Row row = sheet.createRow((short) rowNum);

        row.createCell(colNum++)
                .setCellValue(creationHelper.createRichTextString(studentResponse.getDepartmentName()));
        row.createCell(colNum++)
                .setCellValue(creationHelper.createRichTextString(studentResponse.getCourseCode()));
        row.createCell(colNum++).setCellValue(Integer.parseInt(studentResponse.getSectionNo()));
        row.createCell(colNum++)
                .setCellValue(creationHelper.createRichTextString(studentResponse.getEmpNumber()));
        row.createCell(colNum++)
                .setCellValue(creationHelper.createRichTextString(studentResponse.getEmpName()));
        row.createCell(colNum++).setCellValue(studentResponse.getSeatsTaken());
        row.createCell(colNum++).setCellValue(studentResponse.getStudentResponse());
        row.createCell(colNum++)
                .setCellValue(creationHelper.createRichTextString(studentResponse.getIncludeExclude()));

        rowNum++;

    }

    response.setContentType("application/vnd.ms-excel");
    OutputStream outputStream = response.getPortletOutputStream();
    workbook.write(outputStream);
    outputStream.flush();
    outputStream.close();

    return null;
}

From source file:org.aio.handy.poi.ConditionalFormats.java

License:Apache License

/**
 * Highlight cells based on their values
 *//*from   ww  w  .ja  va 2s. co m*/
static void sameCell(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue(84);
    sheet.createRow(1).createCell(0).setCellValue(74);
    sheet.createRow(2).createCell(0).setCellValue(50);
    sheet.createRow(3).createCell(0).setCellValue(51);
    sheet.createRow(4).createCell(0).setCellValue(49);
    sheet.createRow(5).createCell(0).setCellValue(41);

    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    // Condition 1: Cell Value Is greater than 70 (Blue Fill)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.GT, "70");
    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.BLUE.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    // Condition 2: Cell Value Is less than 50 (Green Fill)
    ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.LT, "50");
    PatternFormatting fill2 = rule2.createPatternFormatting();
    fill2.setFillBackgroundColor(IndexedColors.GREEN.index);
    fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:A6") };

    sheetCF.addConditionalFormatting(regions, rule1, rule2);

    sheet.getRow(0).createCell(2).setCellValue("<== Condition 1: Cell Value Is greater than 70 (Blue Fill)");
    sheet.getRow(4).createCell(2).setCellValue("<== Condition 2: Cell Value Is less than 50 (Green Fill)");
}

From source file:org.aio.handy.poi.ConditionalFormats.java

License:Apache License

/**
 * Highlight multiple cells based on a formula
 */// w  ww. j  a  va 2 s  . c om
static void multiCell(Sheet sheet) {
    // header row
    Row row0 = sheet.createRow(0);
    row0.createCell(0).setCellValue("Units");
    row0.createCell(1).setCellValue("Cost");
    row0.createCell(2).setCellValue("Total");

    Row row1 = sheet.createRow(1);
    row1.createCell(0).setCellValue(71);
    row1.createCell(1).setCellValue(29);
    row1.createCell(2).setCellValue(2059);

    Row row2 = sheet.createRow(2);
    row2.createCell(0).setCellValue(85);
    row2.createCell(1).setCellValue(29);
    row2.createCell(2).setCellValue(2059);

    Row row3 = sheet.createRow(3);
    row3.createCell(0).setCellValue(71);
    row3.createCell(1).setCellValue(29);
    row3.createCell(2).setCellValue(2059);

    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    // Condition 1: Formula Is =$B2>75 (Blue Fill)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("$A2>75");
    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.BLUE.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:C4") };

    sheetCF.addConditionalFormatting(regions, rule1);

    sheet.getRow(2).createCell(4).setCellValue("<== Condition 1: Formula Is =$B2>75   (Blue Fill)");
}

From source file:org.aio.handy.poi.ConditionalFormats.java

License:Apache License

/**
 * Use Excel conditional formatting to check for errors, and change the font
 * colour to match the cell colour. In this example, if formula result is
 * #DIV/0! then it will have white font colour.
 *///from   w  w  w  .j  av a  2s .c om
static void errors(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue(84);
    sheet.createRow(1).createCell(0).setCellValue(0);
    sheet.createRow(2).createCell(0).setCellFormula("ROUND(A1/A2,0)");
    sheet.createRow(3).createCell(0).setCellValue(0);
    sheet.createRow(4).createCell(0).setCellFormula("ROUND(A6/A4,0)");
    sheet.createRow(5).createCell(0).setCellValue(41);

    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    // Condition 1: Formula Is =ISERROR(C2) (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("ISERROR(A1)");
    FontFormatting font = rule1.createFontFormatting();
    font.setFontColorIndex(IndexedColors.WHITE.index);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:A6") };

    sheetCF.addConditionalFormatting(regions, rule1);

    sheet.getRow(2).createCell(1).setCellValue(
            "<== The error in this cell is hidden. Condition: Formula Is   =ISERROR(C2)   (White Font)");
    sheet.getRow(4).createCell(1).setCellValue(
            "<== The error in this cell is hidden. Condition: Formula Is   =ISERROR(C2)   (White Font)");
}

From source file:org.aio.handy.poi.ConditionalFormats.java

License:Apache License

/**
 * Use Excel conditional formatting to hide the duplicate values, and make
 * the list easier to read. In this example, when the table is sorted by
 * Region, the second (and subsequent) occurences of each region name will
 * have white font colour./*from  ww  w . ja v  a 2 s.com*/
 */
static void hideDupplicates(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue("City");
    sheet.createRow(1).createCell(0).setCellValue("Boston");
    sheet.createRow(2).createCell(0).setCellValue("Boston");
    sheet.createRow(3).createCell(0).setCellValue("Chicago");
    sheet.createRow(4).createCell(0).setCellValue("Chicago");
    sheet.createRow(5).createCell(0).setCellValue("New York");

    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    // Condition 1: Formula Is =A2=A1 (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("A2=A1");
    FontFormatting font = rule1.createFontFormatting();
    font.setFontColorIndex(IndexedColors.WHITE.index);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A6") };

    sheetCF.addConditionalFormatting(regions, rule1);

    sheet.getRow(1).createCell(1)
            .setCellValue("<== the second (and subsequent) "
                    + "occurences of each region name will have white font colour.  "
                    + "Condition: Formula Is   =A2=A1   (White Font)");
}

From source file:org.aio.handy.poi.ConditionalFormats.java

License:Apache License

/**
 * Use Excel conditional formatting to highlight duplicate entries in a
 * column./* w w  w .  ja va 2s .  c o  m*/
 */
static void formatDuplicates(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue("Code");
    sheet.createRow(1).createCell(0).setCellValue(4);
    sheet.createRow(2).createCell(0).setCellValue(3);
    sheet.createRow(3).createCell(0).setCellValue(6);
    sheet.createRow(4).createCell(0).setCellValue(3);
    sheet.createRow(5).createCell(0).setCellValue(5);
    sheet.createRow(6).createCell(0).setCellValue(8);
    sheet.createRow(7).createCell(0).setCellValue(0);
    sheet.createRow(8).createCell(0).setCellValue(2);
    sheet.createRow(9).createCell(0).setCellValue(8);
    sheet.createRow(10).createCell(0).setCellValue(6);

    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    // Condition 1: Formula Is =A2=A1 (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("COUNTIF($A$2:$A$11,A2)>1");
    FontFormatting font = rule1.createFontFormatting();
    font.setFontStyle(false, true);
    font.setFontColorIndex(IndexedColors.BLUE.index);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A11") };

    sheetCF.addConditionalFormatting(regions, rule1);

    sheet.getRow(2).createCell(1).setCellValue("<== Duplicates numbers in the column are highlighted.  "
            + "Condition: Formula Is =COUNTIF($A$2:$A$11,A2)>1   (Blue Font)");
}

From source file:org.aio.handy.poi.ConditionalFormats.java

License:Apache License

/**
 * Use Excel conditional formatting to highlight items that are in a list on
 * the worksheet.//from www  .  ja v  a 2 s.c o  m
 */
static void inList(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue("Codes");
    sheet.createRow(1).createCell(0).setCellValue("AA");
    sheet.createRow(2).createCell(0).setCellValue("BB");
    sheet.createRow(3).createCell(0).setCellValue("GG");
    sheet.createRow(4).createCell(0).setCellValue("AA");
    sheet.createRow(5).createCell(0).setCellValue("FF");
    sheet.createRow(6).createCell(0).setCellValue("XX");
    sheet.createRow(7).createCell(0).setCellValue("CC");

    sheet.getRow(0).createCell(2).setCellValue("Valid");
    sheet.getRow(1).createCell(2).setCellValue("AA");
    sheet.getRow(2).createCell(2).setCellValue("BB");
    sheet.getRow(3).createCell(2).setCellValue("CC");

    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    // Condition 1: Formula Is =A2=A1 (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("COUNTIF($C$2:$C$4,A2)");
    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.LIGHT_BLUE.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A8") };

    sheetCF.addConditionalFormatting(regions, rule1);

    sheet.getRow(2).createCell(3).setCellValue(
            "<== Use Excel conditional formatting to highlight items that are in a list on the worksheet");
}

From source file:org.aio.handy.poi.ConditionalFormats.java

License:Apache License

/**
 * Use Excel conditional formatting to highlight payments that are due in
 * the next thirty days. In this example, Due dates are entered in cells
 * A2:A4./*from   w w w.j  a  v a  2 s . com*/
 */
static void expiry(Sheet sheet) {
    CellStyle style = sheet.getWorkbook().createCellStyle();
    style.setDataFormat((short) BuiltinFormats.getBuiltinFormat("d-mmm"));

    sheet.createRow(0).createCell(0).setCellValue("Date");
    sheet.createRow(1).createCell(0).setCellFormula("TODAY()+29");
    sheet.createRow(2).createCell(0).setCellFormula("A2+1");
    sheet.createRow(3).createCell(0).setCellFormula("A3+1");

    for (int rownum = 1; rownum <= 3; rownum++)
        sheet.getRow(rownum).getCell(0).setCellStyle(style);

    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    // Condition 1: Formula Is =A2=A1 (White Font)
    ConditionalFormattingRule rule1 = sheetCF
            .createConditionalFormattingRule("AND(A2-TODAY()>=0,A2-TODAY()<=30)");
    FontFormatting font = rule1.createFontFormatting();
    font.setFontStyle(false, true);
    font.setFontColorIndex(IndexedColors.BLUE.index);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A4") };

    sheetCF.addConditionalFormatting(regions, rule1);

    sheet.getRow(0).createCell(1).setCellValue("Dates within the next 30 days are highlighted");
}