Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook createCellStyle

List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createCellStyle

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFWorkbook
  6. createCellStyle

Introduction

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

Prototype

@Override
public HSSFCellStyle createCellStyle()

Source Link

Document

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

Usage

From source file:org.etudes.mneme.impl.AttachmentServiceImpl.java

License:Apache License

/**
 * Iterates through all question types, captures responses and creates export summary spreadsheet
 * @param workbook Workbook object//from  w w w.j  ava 2s.  c  om
 * @param assessment Assessment object
 * @return Spreadsheet with Export summary
 */
HSSFSheet createResponsesSheet(HSSFWorkbook workbook, Assessment assessment) {
    boolean isSurvey;
    if (assessment.getType() == AssessmentType.survey)
        isSurvey = true;
    else
        isSurvey = false;

    Map<String, Integer> userRowMap = new HashMap();
    HSSFSheet sheet = workbook.createSheet("Submission responses");

    HSSFRow headerRow = sheet.createRow((short) 0);
    AssessmentParts part = assessment.getParts();

    List<Part> parts = part.getParts();
    if (parts == null || parts.size() == 0)
        return null;

    List<Question> questions = new ArrayList();
    for (Iterator partIt = parts.iterator(); partIt.hasNext();) {
        Part partObj = (Part) partIt.next();
        List<Question> questionsUsed = partObj.getQuestionsUsed();
        questions.addAll(questionsUsed);
    }

    HSSFCellStyle style = workbook.createCellStyle();
    HSSFFont font = workbook.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    style.setFont(font);
    // Printing header row and question text
    if (!isSurvey) {
        HSSFCell cell0 = headerRow.createCell((short) (0));
        cell0.setCellStyle(style);
        cell0.setCellValue(this.messages.getFormattedMessage("export_lastname", null));
        HSSFCell cell1 = headerRow.createCell((short) (1));
        cell1.setCellStyle(style);
        cell1.setCellValue(this.messages.getFormattedMessage("export_firstname", null));
        HSSFCell cell2 = headerRow.createCell((short) (2));
        cell2.setCellStyle(style);
        cell2.setCellValue(this.messages.getFormattedMessage("export_username", null));

        HSSFCell cell3 = headerRow.createCell((short) (3));
        cell3.setCellStyle(style);
        cell3.setCellValue(this.messages.getFormattedMessage("export_score", null));
    } else {
        HSSFCell cell0 = headerRow.createCell((short) (0));
        cell0.setCellStyle(style);
        cell0.setCellValue(this.messages.getFormattedMessage("export_user", null));
    }

    int i;
    if (isSurvey)
        i = 1;
    else
        i = 4;
    for (Iterator it = questions.iterator(); it.hasNext();) {
        Question q = (Question) it.next();
        String quest_desc = null;

        TypeSpecificQuestion tsq = q.getTypeSpecificQuestion();
        if (tsq instanceof FillBlanksQuestionImpl) {
            quest_desc = stripHtml(((FillBlanksQuestionImpl) tsq).getText());
        } else if (tsq instanceof FillInlineQuestionImpl) {
            quest_desc = stripHtml(((FillInlineQuestionImpl) tsq).getText());
        } else {
            if (tsq instanceof EssayQuestionImpl || tsq instanceof TaskQuestionImpl) {
                if (tsq instanceof EssayQuestionImpl) {
                    EssayQuestionImpl eqi = (EssayQuestionImpl) tsq;
                    if (eqi.getSubmissionType() == SubmissionType.inline)
                        quest_desc = stripHtml(q.getDescription());
                }
                if (tsq instanceof TaskQuestionImpl) {
                    TaskQuestionImpl tqi = (TaskQuestionImpl) tsq;
                    if (tqi.getSubmissionType() == SubmissionType.inline)
                        quest_desc = stripHtml(q.getDescription());
                }
            } else {
                quest_desc = stripHtml(q.getDescription());
            }
        }
        if (quest_desc != null) {
            HSSFCell cell = headerRow.createCell((short) (i++));
            cell.setCellStyle(style);
            cell.setCellValue(quest_desc);
        }
    }

    int j;
    if (isSurvey)
        j = 1;
    else
        j = 4;
    boolean answersExist = false;
    for (Iterator it = questions.iterator(); it.hasNext();) {
        Question q = (Question) it.next();
        TypeSpecificQuestion tsq = q.getTypeSpecificQuestion();
        //Only captures inline submissions for essays and tasks
        if (tsq instanceof EssayQuestionImpl || tsq instanceof TaskQuestionImpl) {
            if (tsq instanceof EssayQuestionImpl) {
                EssayQuestionImpl eqi = (EssayQuestionImpl) tsq;
                if (eqi.getSubmissionType() != SubmissionType.inline) {
                    continue;
                }
            }
            if (tsq instanceof TaskQuestionImpl) {
                TaskQuestionImpl tqi = (TaskQuestionImpl) tsq;
                if (tqi.getSubmissionType() != SubmissionType.inline) {
                    continue;
                }
            }
        }
        List<Answer> answers = this.submissionService.findSubmissionAnswers(assessment, q, Boolean.TRUE,
                FindAssessmentSubmissionsSort.userName_a, null, null);
        if (answers == null || answers.size() == 0)
            continue;
        else
            answersExist = true;
        for (Answer answer : answers) {
            HSSFRow row;
            try {
                String userId = answer.getSubmission().getUserId();
                String subId = answer.getSubmission().getId();
                if (userRowMap == null || userRowMap.size() == 0 || (userRowMap.get(userId + subId) == null)) {
                    int rowNum = sheet.getLastRowNum() + 1;
                    row = sheet.createRow(rowNum);
                    if (!isSurvey) {
                        User user = this.userDirectoryService.getUser(userId);
                        row.createCell((short) 0).setCellValue(user.getLastName());
                        row.createCell((short) 1).setCellValue(user.getFirstName());
                        row.createCell((short) 2).setCellValue(user.getDisplayId());
                        row.createCell((short) 3).setCellValue(
                                roundTwoDecimals(answer.getSubmission().getTotalScore().floatValue()));
                    } else {
                        row.createCell((short) 0)
                                .setCellValue(this.messages.getFormattedMessage("export_user", null));
                    }
                    userRowMap.put(userId + subId, new Integer(rowNum));
                } else {
                    row = sheet.getRow(userRowMap.get(userId + subId).intValue());
                }

                TypeSpecificAnswer a = answer.getTypeSpecificAnswer();
                if (a instanceof EssayAnswerImpl) {
                    EssayAnswerImpl essay = (EssayAnswerImpl) a;
                    row.createCell((short) j).setCellValue(stripHtml(essay.getAnswerData()));
                }
                if (a instanceof TrueFalseAnswerImpl) {
                    TrueFalseAnswerImpl tf = (TrueFalseAnswerImpl) a;
                    if (!isSurvey && tf.getCompletelyCorrect() != null
                            && tf.getCompletelyCorrect().booleanValue())
                        row.createCell((short) j).setCellValue("*" + tf.getAnswer() + "*");
                    else
                        row.createCell((short) j).setCellValue(tf.getAnswer());
                }
                if (a instanceof MultipleChoiceAnswerImpl) {
                    MultipleChoiceAnswerImpl mc = (MultipleChoiceAnswerImpl) a;
                    List<MultipleChoiceQuestionImpl.MultipleChoiceQuestionChoice> choiceList = ((MultipleChoiceQuestionImpl) mc
                            .getAnswerObject().getQuestion().getTypeSpecificQuestion()).getChoicesAsAuthored();
                    String[] ansArray = mc.getAnswers();
                    String[] choiceArray = new String[mc.getAnswers().length];
                    Set<Integer> correctAnswers = ((MultipleChoiceQuestionImpl) mc.getAnswerObject()
                            .getQuestion().getTypeSpecificQuestion()).getCorrectAnswerSet();

                    int l = 0;
                    for (Iterator chIt = choiceList.iterator(); chIt.hasNext();) {
                        MultipleChoiceQuestionImpl.MultipleChoiceQuestionChoice mq = (MultipleChoiceQuestionImpl.MultipleChoiceQuestionChoice) chIt
                                .next();

                        if (Arrays.asList(ansArray).contains(mq.getId())) {
                            if (!isSurvey && correctAnswers.contains(Integer.parseInt(mq.getId()))) {
                                choiceArray[l] = "*" + stripHtml(mq.getText().trim()) + "*";
                            } else {
                                choiceArray[l] = stripHtml(mq.getText().trim());
                            }
                            l++;
                        }
                    }

                    row.createCell((short) j).setCellValue(getCommaAnswers(choiceArray));
                }
                if (a instanceof OrderAnswerImpl) {
                    OrderAnswerImpl oa = (OrderAnswerImpl) a;
                    List<OrderQuestionImpl.OrderQuestionChoice> choiceList = ((OrderQuestionImpl) oa
                            .getAnswerObject().getQuestion().getTypeSpecificQuestion()).getChoicesAsAuthored();
                    Map<String, Value> ansMap = oa.getAnswer();
                    String[] choiceArray = new String[ansMap.size()];
                    int l = 0;
                    for (Map.Entry entry : ansMap.entrySet()) {
                        String entryId = (String) entry.getKey();
                        // Value value = ansMap.get(oqc.getId());
                        if (!isSurvey && entry.getValue() != null
                                && ((Value) entry.getValue()).getValue() != null
                                && ((Value) entry.getValue()).getValue().equals(
                                        entryId)/*((OrderQuestionImpl.OrderQuestionChoice) choiceList.get(l)).getId().equals(entryId)*/) {
                            choiceArray[l] = "*" + stripHtml(getChoiceText(choiceList, entryId).trim()) + "*";
                        } else {
                            if (((Value) entry.getValue()).getValue() == null) {
                                choiceArray[l] = "Select";
                            } else {
                                choiceArray[l] = stripHtml(getChoiceText(choiceList, entryId).trim());
                            }
                        }

                        l++;
                    }

                    row.createCell((short) j).setCellValue(getCommaAnswers(choiceArray));
                }
                if (a instanceof FillBlanksAnswerImpl) {
                    FillBlanksAnswerImpl fb = (FillBlanksAnswerImpl) a;
                    row.createCell((short) j)
                            .setCellValue(stripHtml(getCommaAnswers(checkCorrectFill(fb, isSurvey))));
                }
                if (a instanceof FillInlineAnswerImpl) {
                    FillInlineAnswerImpl fi = (FillInlineAnswerImpl) a;
                    row.createCell((short) j)
                            .setCellValue(stripHtml(getCommaAnswers(checkCorrectFillInline(fi, isSurvey))));
                }
                if (a instanceof LikertScaleAnswerImpl) {
                    LikertScaleAnswerImpl ls = (LikertScaleAnswerImpl) a;
                    LikertScaleQuestionImpl lsq = (LikertScaleQuestionImpl) ls.getAnswerObject().getQuestion()
                            .getTypeSpecificQuestion();
                    List<LikertScaleQuestionImpl.LikertScaleQuestionChoice> choiceList = lsq.getChoices();
                    for (Iterator chIt = choiceList.iterator(); chIt.hasNext();) {
                        LikertScaleQuestionImpl.LikertScaleQuestionChoice lqc = (LikertScaleQuestionImpl.LikertScaleQuestionChoice) chIt
                                .next();
                        if (lqc.getId().equals(ls.getAnswer())) {
                            row.createCell((short) j).setCellValue(stripHtml(lqc.getText()));
                            break;
                        }
                    }
                }
                if (a instanceof MatchAnswerImpl) {
                    MatchAnswerImpl ma = (MatchAnswerImpl) a;
                    Map matchMap = (LinkedHashMap) ma.getAnswer();
                    Iterator it2 = matchMap.entrySet().iterator();
                    StringBuffer matchStrBuf = new StringBuffer();

                    List choiceList = ((MatchQuestionImpl) ma.getAnswerObject().getQuestion()
                            .getTypeSpecificQuestion()).getPairsForDelivery();
                    while (it2.hasNext()) {
                        Map.Entry entry = (Map.Entry) it2.next();
                        String key = (String) entry.getKey();
                        String value = (String) ((Value) entry.getValue()).getValue();
                        String matchVal = fetchName(choiceList, key, true);
                        boolean correctMatch = checkCorrectMatch(choiceList, key, value);
                        if (!isSurvey && correctMatch)
                            matchStrBuf.append("*");
                        matchStrBuf.append(stripHtml(matchVal.trim()));
                        matchStrBuf.append("->");
                        String choiceVal = fetchName(choiceList, value, false);
                        if (choiceVal == null)
                            matchStrBuf.append(this.messages.getFormattedMessage("nosel_made", null));
                        else
                            matchStrBuf.append(stripHtml(choiceVal.trim()));

                        if (!isSurvey && correctMatch)
                            matchStrBuf.append("*");
                        matchStrBuf.append(", ");
                    }
                    if (matchStrBuf.length() > 0 && matchStrBuf.charAt(matchStrBuf.length() - 2) == ',') {
                        String matchStrBufTrim = matchStrBuf.substring(0, matchStrBuf.length() - 2);
                        row.createCell((short) j).setCellValue(stripHtml(matchStrBufTrim));
                    }
                }
                if (a instanceof TaskAnswerImpl) {
                    TaskAnswerImpl ta = (TaskAnswerImpl) a;
                    row.createCell((short) j).setCellValue(stripHtml(ta.getAnswerData()));
                }
            } catch (UserNotDefinedException e) {
                M_log.warn("createResponsesSheet: " + e.toString());
            }
        }
        j = j + 1;
    }
    if (!answersExist)
        return null;
    return sheet;
}

From source file:org.etudes.mneme.impl.AttachmentServiceImpl.java

License:Apache License

/**
 * Creates True False tab for answers Item Analysis
 *
 * @param tf_questions List of True False questions
 * @param workbook Workbook object/*  www .j  a  v  a2  s  .  c o m*/
 * @param assessment Assessment object
 */
void createTrueFalseTab(List<Question> tf_questions, HSSFWorkbook workbook, Assessment assessment) {
    if (tf_questions == null || tf_questions.size() == 0)
        return;

    String assmtId = assessment.getId();
    HSSFSheet sheet = null;
    HSSFRow row;

    boolean headerRowDone = false;
    for (Iterator it = tf_questions.iterator(); it.hasNext();) {
        Question q = (Question) it.next();

        List<Answer> answers = this.submissionService.findSubmissionAnswers(assessment, q,
                FindAssessmentSubmissionsSort.userName_a, null, null);
        if (answers == null || answers.size() == 0)
            return;

        if (!headerRowDone) {
            sheet = workbook.createSheet("TrueFalse");

            HSSFRow headerRow = sheet.createRow((short) 0);

            HSSFCellStyle style = workbook.createCellStyle();
            HSSFFont font = workbook.createFont();
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            style.setFont(font);
            // Printing header row
            HSSFCell cell0 = headerRow.createCell((short) (0));
            cell0.setCellStyle(style);
            cell0.setCellValue(this.messages.getFormattedMessage("item_analysis_question", null));
            HSSFCell cell1 = headerRow.createCell((short) (1));
            cell1.setCellStyle(style);
            cell1.setCellValue(this.messages.getFormattedMessage("true-header", null));
            HSSFCell cell2 = headerRow.createCell((short) (2));
            cell2.setCellStyle(style);
            cell2.setCellValue(this.messages.getFormattedMessage("false-header", null));

            headerRowDone = true;
        }

        int trueCount = 0, falseCount = 0;

        for (Answer answer : answers) {
            TypeSpecificAnswer a = answer.getTypeSpecificAnswer();
            if (a instanceof TrueFalseAnswerImpl) {
                TrueFalseAnswerImpl tf = (TrueFalseAnswerImpl) a;
                if (tf.getAnswer().equals("true"))
                    trueCount++;
                if (tf.getAnswer().equals("false"))
                    falseCount++;
            }
        }
        int rowNum = sheet.getLastRowNum() + 1;
        row = sheet.createRow(rowNum);
        String quest_desc = stripHtml(q.getDescription());
        row.createCell((short) 0).setCellValue(quest_desc);
        if (((TrueFalseQuestionImpl) q.getTypeSpecificQuestion()).getCorrectAnswer().equals("true"))
            row.createCell((short) 1).setCellValue("*" + trueCount + "*");
        else
            row.createCell((short) 1).setCellValue(trueCount);
        if (((TrueFalseQuestionImpl) q.getTypeSpecificQuestion()).getCorrectAnswer().equals("false"))
            row.createCell((short) 2).setCellValue("*" + falseCount + "*");
        else
            row.createCell((short) 2).setCellValue(falseCount);

    }
}

From source file:org.everit.jira.reporting.plugin.export.AbstractExportListReport.java

License:Apache License

private void createBodyCellStyle(final HSSFWorkbook workbook) {
    bodyCellStyle = workbook.createCellStyle();
    bodyCellStyle.setWrapText(true);
}

From source file:org.everit.jira.reporting.plugin.export.AbstractExportListReport.java

License:Apache License

private void createHeaderCellStyle(final HSSFWorkbook workbook) {
    headerCellStyle = workbook.createCellStyle();
    HSSFFont headerFont = workbook.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    headerCellStyle.setWrapText(true);/*from  www. ja  v  a 2 s . c om*/
}

From source file:org.exoplatform.addon.pulse.service.ws.RestActivitiesStatistic.java

License:Open Source License

private HSSFWorkbook buildExportDataForExcel(String maxColumn, String filter, Date fromDate,
        boolean isExportNewUsersData, boolean isExportLoginCountData, boolean isExportForumActiveUsersData,
        boolean isExportNewForumPostsData, boolean isExportPlfDownloadsData, boolean isExportUserConnectionData,
        boolean isExportSocialPostData, boolean isExportEmailNotificationData) throws Exception {
    ChartData data = buildExportData(maxColumn, filter, fromDate, isExportPlfDownloadsData);
    String fileName = "export_" + maxColumn + "_" + filter + "_from_" + partString(fromDate, "yyyy.MM.dd")
            + ".xls";

    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet worksheet = workbook.createSheet(fileName);

    // index from 0,0... cell A1 is cell(0,0)

    HSSFCellStyle headerCellStyle = workbook.createCellStyle();
    HSSFFont hSSFFont = workbook.createFont();
    hSSFFont.setFontName(HSSFFont.FONT_ARIAL);
    hSSFFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    hSSFFont.setColor(HSSFColor.BLACK.index);
    headerCellStyle.setFont(hSSFFont);/*from ww w  . j a v a  2 s. com*/

    HSSFRow headerRow = worksheet.createRow((short) 0);
    HSSFCell cellA1 = headerRow.createCell(0);
    cellA1.setCellValue("Statistics");
    cellA1.setCellStyle(headerCellStyle);

    for (int i = 0; i < data.getListTitle().size(); i++) {
        HSSFCell cell = headerRow.createCell(i + 1);
        cell.setCellValue(data.getListTitle().get(i));
        cell.setCellStyle(headerCellStyle);
    }

    HSSFCell totalCell = headerRow.createCell(data.getListTitle().size() + 1);
    totalCell.setCellValue("Total");
    totalCell.setCellStyle(headerCellStyle);

    int rowIndex = 1;
    if (isExportNewUsersData) {
        HSSFRow metricRow = worksheet.createRow(rowIndex);
        HSSFCell metricCell = metricRow.createCell(0);
        metricCell.setCellValue("New Users Registration");
        Long total = 0L;
        for (int i = 0; i < data.getNewUsersData().size(); i++) {
            total += data.getNewUsersData().get(i);
            HSSFCell cell = metricRow.createCell(i + 1);
            cell.setCellValue(data.getNewUsersData().get(i));
        }
        HSSFCell metricTotalCell = metricRow.createCell(data.getNewUsersData().size() + 1);
        metricTotalCell.setCellValue(total);

        rowIndex++;
    }

    if (isExportLoginCountData) {
        HSSFRow metricRow = worksheet.createRow(rowIndex);
        HSSFCell metricCell = metricRow.createCell(0);
        metricCell.setCellValue("Nb Unique Login");
        Long total = 0L;
        for (int i = 0; i < data.getLoginCountData().size(); i++) {
            total += data.getLoginCountData().get(i);
            HSSFCell cell = metricRow.createCell(i + 1);
            cell.setCellValue(data.getLoginCountData().get(i));
        }
        HSSFCell metricTotalCell = metricRow.createCell(data.getLoginCountData().size() + 1);
        metricTotalCell.setCellValue(total);

        rowIndex++;
    }

    if (isExportForumActiveUsersData) {
        HSSFRow metricRow = worksheet.createRow(rowIndex);
        HSSFCell metricCell = metricRow.createCell(0);
        metricCell.setCellValue("Forum Active Users Average");
        Long total = 0L;
        int itemHasData = 0;
        for (int i = 0; i < data.getForumActiveUsersData().size(); i++) {
            total += data.getForumActiveUsersData().get(i);
            if (data.getForumActiveUsersData().get(i) > 0)
                itemHasData++;
            HSSFCell cell = metricRow.createCell(i + 1);
            cell.setCellValue(data.getForumActiveUsersData().get(i));
        }
        HSSFCell metricTotalCell = metricRow.createCell(data.getForumActiveUsersData().size() + 1);
        Long average = itemHasData > 0 ? total / itemHasData : 0L;
        metricTotalCell.setCellValue(average);

        rowIndex++;
    }

    if (isExportNewForumPostsData) {
        HSSFRow metricRow = worksheet.createRow(rowIndex);
        HSSFCell metricCell = metricRow.createCell(0);
        metricCell.setCellValue("New Forum Posts");
        Long total = 0L;
        for (int i = 0; i < data.getNewForumPostsData().size(); i++) {
            total += data.getNewForumPostsData().get(i);
            HSSFCell cell = metricRow.createCell(i + 1);
            cell.setCellValue(data.getNewForumPostsData().get(i));
        }
        HSSFCell metricTotalCell = metricRow.createCell(data.getNewForumPostsData().size() + 1);
        metricTotalCell.setCellValue(total);

        rowIndex++;
    }

    if (isExportUserConnectionData) {
        HSSFRow metricRow = worksheet.createRow(rowIndex);
        HSSFCell metricCell = metricRow.createCell(0);
        metricCell.setCellValue("New User connections");
        Long total = 0L;
        for (int i = 0; i < data.getUserConnectionData().size(); i++) {
            total += data.getUserConnectionData().get(i);
            HSSFCell cell = metricRow.createCell(i + 1);
            cell.setCellValue(data.getUserConnectionData().get(i));
        }
        HSSFCell metricTotalCell = metricRow.createCell(data.getUserConnectionData().size() + 1);
        metricTotalCell.setCellValue(total);

        rowIndex++;
    }

    if (isExportSocialPostData) {
        HSSFRow metricRow = worksheet.createRow(rowIndex);
        HSSFCell metricCell = metricRow.createCell(0);
        metricCell.setCellValue("New posts in activities stream");
        Long total = 0L;
        for (int i = 0; i < data.getSocialPostData().size(); i++) {
            total += data.getSocialPostData().get(i);
            HSSFCell cell = metricRow.createCell(i + 1);
            cell.setCellValue(data.getSocialPostData().get(i));
        }
        HSSFCell metricTotalCell = metricRow.createCell(data.getSocialPostData().size() + 1);
        metricTotalCell.setCellValue(total);

        rowIndex++;
    }

    if (isExportEmailNotificationData) {
        HSSFRow metricRow = worksheet.createRow(rowIndex);
        HSSFCell metricCell = metricRow.createCell(0);
        metricCell.setCellValue("Number of notification emails sent");
        Long total = 0L;
        for (int i = 0; i < data.getEmailNotificationData().size(); i++) {
            total += data.getEmailNotificationData().get(i);
            HSSFCell cell = metricRow.createCell(i + 1);
            cell.setCellValue(data.getEmailNotificationData().get(i));
        }
        HSSFCell metricTotalCell = metricRow.createCell(data.getEmailNotificationData().size() + 1);
        metricTotalCell.setCellValue(total);

        rowIndex++;
    }

    if (isExportPlfDownloadsData) {
        HSSFRow metricRow = worksheet.createRow(rowIndex);
        HSSFCell metricCell = metricRow.createCell(0);
        metricCell.setCellValue("PLF Downloads");
        Long total = 0L;
        for (int i = 0; i < data.getPlfDownloadsData().size(); i++) {
            total += data.getPlfDownloadsData().get(i);
            HSSFCell cell = metricRow.createCell(i + 1);
            cell.setCellValue(data.getPlfDownloadsData().get(i));
        }
        HSSFCell metricTotalCell = metricRow.createCell(data.getPlfDownloadsData().size() + 1);
        metricTotalCell.setCellValue(total);

        rowIndex++;
    }
    return workbook;
}

From source file:org.extremecomponents.table.view.ExtendXlsView.java

License:Apache License

private Map initStyles(HSSFWorkbook wb, short fontHeight) {
    Map result = new HashMap();
    HSSFCellStyle titleStyle = wb.createCellStyle();
    HSSFCellStyle textStyle = wb.createCellStyle();
    HSSFCellStyle boldStyle = wb.createCellStyle();
    HSSFCellStyle numericStyle = wb.createCellStyle();
    HSSFCellStyle numericStyleBold = wb.createCellStyle();
    HSSFCellStyle moneyStyle = wb.createCellStyle();
    HSSFCellStyle moneyStyleBold = wb.createCellStyle();
    HSSFCellStyle percentStyle = wb.createCellStyle();
    HSSFCellStyle percentStyleBold = wb.createCellStyle();

    result.put("titleStyle", titleStyle);
    result.put("textStyle", textStyle);
    result.put("boldStyle", boldStyle);
    result.put("numericStyle", numericStyle);
    result.put("numericStyleBold", numericStyleBold);
    result.put("moneyStyle", moneyStyle);
    result.put("moneyStyleBold", moneyStyleBold);
    result.put("percentStyle", percentStyle);
    result.put("percentStyleBold", percentStyleBold);

    HSSFDataFormat format = wb.createDataFormat();

    // Global fonts
    HSSFFont font = wb.createFont();//from  ww w .j  a v a 2  s.c om
    font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
    font.setColor(HSSFColor.BLACK.index);
    font.setFontName(HSSFFont.FONT_ARIAL);
    font.setFontHeightInPoints(fontHeight);

    HSSFFont fontBold = wb.createFont();
    fontBold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    fontBold.setColor(HSSFColor.BLACK.index);
    fontBold.setFontName(HSSFFont.FONT_ARIAL);
    fontBold.setFontHeightInPoints(fontHeight);

    // Money Style
    moneyStyle.setFont(font);
    moneyStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    moneyStyle.setDataFormat(format.getFormat(moneyFormat));

    // Money Style Bold
    moneyStyleBold.setFont(fontBold);
    moneyStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    moneyStyleBold.setDataFormat(format.getFormat(moneyFormat));

    // Percent Style
    percentStyle.setFont(font);
    percentStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    percentStyle.setDataFormat(format.getFormat(percentFormat));

    // Percent Style Bold
    percentStyleBold.setFont(fontBold);
    percentStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    percentStyleBold.setDataFormat(format.getFormat(percentFormat));

    // Standard Numeric Style
    numericStyle.setFont(font);
    numericStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);

    // Standard Numeric Style Bold
    numericStyleBold.setFont(fontBold);
    numericStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);

    // Title Style
    titleStyle.setFont(font);
    titleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    titleStyle.setBottomBorderColor(HSSFColor.BLACK.index);
    titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    titleStyle.setLeftBorderColor(HSSFColor.BLACK.index);
    titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    titleStyle.setRightBorderColor(HSSFColor.BLACK.index);
    titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    titleStyle.setTopBorderColor(HSSFColor.BLACK.index);
    titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

    // Standard Text Style
    textStyle.setFont(font);
    textStyle.setWrapText(true);

    // Standard Text Style
    boldStyle.setFont(fontBold);
    boldStyle.setWrapText(true);

    return result;
}

From source file:org.fenixedu.academic.ui.struts.action.academicAdministration.executionCourseManagement.CourseLoadOverviewBean.java

License:Open Source License

public StyledExcelSpreadsheet getInconsistencySpreadsheet() {
    final StyledExcelSpreadsheet spreadsheet = new StyledExcelSpreadsheet(
            BundleUtil.getString(Bundle.ACADEMIC, "label.course.load.inconsistency.filename") + "_"
                    + executionSemester.getExecutionYear().getYear().replace('/', '_') + "_"
                    + executionSemester.getSemester());
    CellStyle normalStyle = spreadsheet.getExcelStyle().getValueStyle();
    normalStyle.setAlignment(HorizontalAlignment.CENTER);

    HSSFWorkbook wb = spreadsheet.getWorkbook();
    HSSFFont font = wb.createFont();/* ww w . j a  v  a2s . c  o m*/
    font.setColor(HSSFColor.BLACK.index);
    font.setFontHeightInPoints((short) 8);
    HSSFCellStyle redStyle = wb.createCellStyle();
    redStyle.setFont(font);
    redStyle.setAlignment(HorizontalAlignment.CENTER);
    redStyle.setFillForegroundColor(HSSFColor.ORANGE.index);
    redStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    HSSFCellStyle yellowStyle = wb.createCellStyle();
    yellowStyle.setFont(font);
    yellowStyle.setAlignment(HorizontalAlignment.CENTER);
    yellowStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
    yellowStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

    spreadsheet.newHeaderRow();
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.department"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.degree"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.executionCourse"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.shift"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.shiftType"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.competenceCourse"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.curricularCourse"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.executionCourse"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.lessonInstances"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.lesson.count"));
    spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.lessonInstances.count"));

    for (final ExecutionCourse executionCourse : executionSemester.getAssociatedExecutionCoursesSet()) {
        for (final CourseLoad courseLoad : executionCourse.getCourseLoadsSet()) {
            for (final Shift shift : courseLoad.getShiftsSet()) {
                spreadsheet.newRow();
                spreadsheet.addCell(getDepartmentString(executionCourse));
                spreadsheet.addCell(executionCourse.getDegreePresentationString());
                spreadsheet.addCell(executionCourse.getName());
                spreadsheet.addCell(shift.getNome());
                spreadsheet.addCell(courseLoad.getType().getFullNameTipoAula());
                final BigDecimal competenceCourseLoad = new BigDecimal(getCompetenceCourseLoad(courseLoad))
                        .setScale(2, RoundingMode.HALF_EVEN);
                final BigDecimal curricularCourseLoad = new BigDecimal(getCurricularCourseLoad(courseLoad))
                        .setScale(2, RoundingMode.HALF_EVEN);
                final BigDecimal executionLoad = courseLoad.getTotalQuantity().setScale(2,
                        RoundingMode.HALF_EVEN);
                final BigDecimal shiftCourseLoad = getShiftCourseLoad(shift).setScale(2,
                        RoundingMode.HALF_EVEN);
                if (competenceCourseLoad.signum() < 0) {
                    spreadsheet.addCell(getCompetenceCourseLoadStrings(courseLoad), redStyle);
                } else {
                    spreadsheet.addCell(competenceCourseLoad);
                }
                if (!competenceCourseLoad.equals(curricularCourseLoad) || curricularCourseLoad.signum() < 0) {
                    spreadsheet.addCell(getCurricularCourseLoadString(courseLoad), redStyle);
                } else {
                    spreadsheet.addCell(curricularCourseLoad);
                }
                if (!executionLoad.equals(curricularCourseLoad)) {
                    spreadsheet.addCell(executionLoad, redStyle);
                } else {
                    spreadsheet.addCell(executionLoad);
                }
                if (!shiftCourseLoad.equals(executionLoad)) {
                    if (isLargeDifference(shiftCourseLoad, executionLoad,
                            competenceCourseLoad.divide(new BigDecimal(14), 2, RoundingMode.HALF_EVEN))) {
                        spreadsheet.addCell(shiftCourseLoad, redStyle);
                    } else {
                        spreadsheet.addCell(shiftCourseLoad, yellowStyle);
                    }
                } else {
                    spreadsheet.addCell(shiftCourseLoad);
                }
                spreadsheet.addCell(shift.getAssociatedLessonsSet().size());
                spreadsheet.addCell(getLessonInstanceCount(shift));
            }
        }
    }

    final HSSFSheet sheet = wb.getSheetAt(0);
    sheet.createFreezePane(0, 1, 0, 1);
    sheet.autoSizeColumn(1, true);
    sheet.autoSizeColumn(2, true);
    sheet.autoSizeColumn(3, true);
    sheet.autoSizeColumn(4, true);
    sheet.autoSizeColumn(5, true);
    sheet.autoSizeColumn(6, true);
    sheet.autoSizeColumn(7, true);
    sheet.autoSizeColumn(8, true);
    sheet.autoSizeColumn(9, true);

    return spreadsheet;
}

From source file:org.fenixedu.academic.ui.struts.action.administrativeOffice.scholarship.utl.report.ReportStudentsUTLCandidates.java

License:Open Source License

private CellStyle headerBackgroundStyle(final HSSFWorkbook wb) {
    CellStyle style = wb.createCellStyle();
    style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
    style.setFillPattern(FillPatternType.BIG_SPOTS);

    return style;
}

From source file:org.forzaframework.util.XlsUtils.java

License:Apache License

static public void modelToExcelSheet(HSSFWorkbook wb, String sheetName, List<Map<String, Object>> headers,
        List<Map<String, Object>> data, List<Map<String, Object>> footers, Integer freezePane,
        Boolean defaultFormat, Boolean createNewSheet, Integer indexSheet, Integer startInRow,
        Boolean printHeader, Boolean autoSizeColumns) {
    HSSFSheet sheet = getSheet(wb, sheetName, createNewSheet, indexSheet);
    HSSFCellStyle headerCellStyle = getDefaultHeaderCellStyle(wb, defaultFormat);
    HSSFCellStyle titlesCellStyle = null;
    if (defaultFormat != null && defaultFormat) {
        titlesCellStyle = wb.createCellStyle();
        //Creamos el tipo de fuente
        HSSFFont titleFont = wb.createFont();
        //            headerFont.setFontName(HSSFFont.FONT_ARIAL);
        titleFont.setBold(Boolean.TRUE);
        titleFont.setColor(HSSFFont.COLOR_NORMAL);
        titleFont.setFontHeightInPoints((short) 8);
        titlesCellStyle.setFont(titleFont);
    }/*from   w w w .j  av  a 2 s .  co  m*/

    Integer col = 0;
    Integer row = 0;
    if (startInRow != null) {
        row = startInRow;
    }
    Map<Integer, Integer> columnWidthMap = new HashMap<Integer, Integer>();
    //Indice de la fila donde empieza los encabezados de titulo de cada columna
    Integer principalHeaderIndex = headers.size() - 1;
    if (printHeader != null && printHeader) {
        //Armamos el encabezado
        for (Map<String, Object> header : headers) {
            for (Map.Entry<String, Object> entry : header.entrySet()) {
                HSSFCell cell = getCell(sheet, row, col);
                if (defaultFormat != null && defaultFormat) {
                    if (principalHeaderIndex.equals(row)) {
                        //Colocamos el formato de la celda
                        cell.setCellStyle(headerCellStyle);
                    } else {
                        cell.setCellStyle(titlesCellStyle);
                    }
                }
                setValue(cell, entry.getValue());
                //Especificamos el ancho que tendra la columna
                if (autoSizeColumns != null && autoSizeColumns) {
                    columnWidthMap.put(col, entry.getValue().toString().length());
                }
                col++;
            }
            row++;
            col = 0;
        }
        //Ponemos la altura del encabezado
        setRowHeight(sheet, row - 1, (short) 420);
    }

    HSSFCellStyle detailCellStyle = getDefaultDetailCellStyle(wb, defaultFormat);

    Map<String, Object> principalHeader = headers.get(principalHeaderIndex);
    // datos
    for (Map<String, Object> map : data) {
        for (Map.Entry<String, Object> entry : principalHeader.entrySet()) {
            Object value = map.get(entry.getKey());
            buildCellAndCalculateColumnWidth(sheet, value, col, row, detailCellStyle, columnWidthMap,
                    autoSizeColumns);
            col++;
        }
        col = 0;
        row++;
    }
    HSSFCellStyle totalCellStyle = null;
    if (defaultFormat != null && defaultFormat) {
        //Armamos el formato los totales
        totalCellStyle = wb.createCellStyle();
        HSSFFont totalFont = wb.createFont();
        totalFont.setBold(Boolean.TRUE);
        totalFont.setColor(HSSFFont.COLOR_NORMAL);
        totalFont.setFontHeightInPoints((short) 8);
        totalCellStyle.setFont(totalFont);
    }

    if (footers != null) {
        for (Map<String, Object> footer : footers) {
            for (Map.Entry<String, Object> entry : principalHeader.entrySet()) {
                HSSFCell cell = getCell(sheet, row, col++);
                if (totalCellStyle != null) {
                    //Colocamos el formato de la celda
                    cell.setCellStyle(totalCellStyle);
                }

                Object object = footer.get(entry.getKey());
                if (object != null) {
                    setValue(cell, object);
                } else {
                    setText(cell, "");
                }
            }
        }
    }

    if (autoSizeColumns != null && autoSizeColumns) {
        setColumnsWidth(sheet, columnWidthMap, principalHeader.size());
    }

    if (freezePane != null && freezePane > 0) {
        //Colocamos la columna estatica y las filas del encabezado estaticas
        sheet.createFreezePane(freezePane, headers.size());
    }
}