Example usage for org.apache.poi.ss.usermodel Font setItalic

List of usage examples for org.apache.poi.ss.usermodel Font setItalic

Introduction

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

Prototype


void setItalic(boolean italic);

Source Link

Document

set whether to use italics or not

Usage

From source file:org.primefaces.extensions.showcase.util.ExcelCustomExporter.java

License:Apache License

protected void createCustomFonts() {

    Font facetFont = wb.createFont();
    Font cellFont = wb.createFont();

    if (cellFontColor != null) {
        XSSFColor cellColor = new XSSFColor(cellFontColor);
        ((XSSFFont) cellFont).setColor(cellColor);
    }//from ww  w .  j  a  va 2s  .  com
    if (cellFontSize != null) {
        cellFont.setFontHeightInPoints(cellFontSize);
    }

    if (cellFontStyle.equalsIgnoreCase("BOLD")) {
        cellFont.setBold(true);
    }
    if (cellFontStyle.equalsIgnoreCase("ITALIC")) {
        cellFont.setItalic(true);
    }

    if (facetFontStyle.equalsIgnoreCase("BOLD")) {
        facetFont.setBold(true);
    }
    if (facetFontStyle.equalsIgnoreCase("ITALIC")) {
        facetFont.setItalic(true);
    }

    if (fontName != null) {
        cellFont.setFontName(fontName);
        facetFont.setFontName(fontName);
    }

    cellStyle.setFont(cellFont);

    if (facetBackground != null) {
        XSSFColor backgroundColor = new XSSFColor(facetBackground);
        ((XSSFCellStyle) facetStyle).setFillForegroundColor(backgroundColor);
        facetStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

    }

    if (facetFontColor != null) {
        XSSFColor facetColor = new XSSFColor(facetFontColor);
        ((XSSFFont) facetFont).setColor(facetColor);

    }
    if (facetFontSize != null) {
        facetFont.setFontHeightInPoints(facetFontSize);
    }

    facetStyle.setFont(facetFont);
    facetStyle.setAlignment(HorizontalAlignment.CENTER);

}

From source file:org.sakaiproject.evaluation.tool.reporting.XLSReportExporter.java

License:Educational Community License

/**
 * Build the .xls report in the new (section based) format.
 * //from   w w  w.  j a va2 s.co  m
 * @param evaluation
 * @param groupIDs
 * @param outputStream
 */
private void buildReportSectionAware(EvalEvaluation evaluation, String[] groupIDs, OutputStream outputStream) {
    // Get permission to view, current user and eval owner
    Boolean instructorViewAllResults = evaluation.getInstructorViewAllResults();
    String currentUserId = commonLogic.getCurrentUserId();
    String evalOwner = evaluation.getOwner();

    TemplateItemDataList tidl = getEvalTIDL(evaluation, groupIDs);
    List<DataTemplateItem> dtiList = tidl.getFlatListOfDataTemplateItems(true);
    Workbook wb = new XSSFWorkbook();
    creationHelper = wb.getCreationHelper();

    // Title style
    Sheet courseSheet = wb.createSheet(messageLocator.getMessage("viewreport.xls.courseSheet.name"));
    Sheet instructorSheet = wb.createSheet(messageLocator.getMessage("viewreport.xls.instructorSheet.name"));
    Font font = wb.createFont();
    font.setFontHeightInPoints((short) 12);
    font.setBold(true);
    CellStyle mainTitleStyle = wb.createCellStyle();
    mainTitleStyle.setFont(font);

    // Bold header style
    font = wb.createFont();
    font.setFontHeightInPoints((short) 10);
    font.setBold(true);
    CellStyle boldHeaderStyle = wb.createCellStyle();
    boldHeaderStyle.setFont(font);

    // Italic meta header style
    font = wb.createFont();
    font.setFontHeightInPoints((short) 10);
    font.setItalic(true);
    CellStyle italicMiniHeaderStyle = wb.createCellStyle();
    italicMiniHeaderStyle.setFont(font);

    // Date meta Style
    dateCellStyle = wb.createCellStyle();
    dateCellStyle.setDataFormat((short) 0x16);

    // Evaluation Title
    int rowCounter = 0;
    Row courseSheetRow1 = courseSheet.createRow(rowCounter);
    Row instructorSheetRow1 = instructorSheet.createRow(rowCounter);
    Cell courseSheetCellA1 = courseSheetRow1.createCell((short) 0);
    Cell instructorSheetCellA1 = instructorSheetRow1.createCell((short) 0);
    setPlainStringCell(courseSheetCellA1,
            evaluation.getTitle() + " - " + messageLocator.getMessage("viewreport.xls.courseSheet.name"));
    setPlainStringCell(instructorSheetCellA1,
            evaluation.getTitle() + " - " + messageLocator.getMessage("viewreport.xls.instructorSheet.name"));
    courseSheetCellA1.setCellStyle(mainTitleStyle);
    instructorSheetCellA1.setCellStyle(mainTitleStyle);

    // Calculate the response rate
    rowCounter++;
    int responsesCount = evaluationService.countResponses(null, new Long[] { evaluation.getId() }, groupIDs,
            null);
    int enrollmentsCount = evaluationService.countParticipantsForEval(evaluation.getId(), groupIDs);
    Row courseSheetRow2 = courseSheet.createRow(rowCounter);
    Row instructorSheetRow2 = instructorSheet.createRow(rowCounter);
    Cell courseSheetCellA2 = courseSheetRow2.createCell((short) 0);
    Cell instructorSheetCellA2 = instructorSheetRow2.createCell((short) 0);
    courseSheetCellA2.setCellStyle(boldHeaderStyle);
    instructorSheetCellA2.setCellStyle(boldHeaderStyle);
    setPlainStringCell(courseSheetCellA2,
            EvalUtils.makeResponseRateStringFromCounts(responsesCount, enrollmentsCount));
    setPlainStringCell(instructorSheetCellA2,
            EvalUtils.makeResponseRateStringFromCounts(responsesCount, enrollmentsCount));

    // Dates
    setPlainStringCell(courseSheetRow1.createCell((short) 2),
            messageLocator.getMessage("evalsettings.start.date.header"));
    setPlainStringCell(instructorSheetRow1.createCell((short) 2),
            messageLocator.getMessage("evalsettings.start.date.header"));
    setDateCell(courseSheetRow2.createCell((short) 2), evaluation.getStartDate());
    setDateCell(instructorSheetRow2.createCell((short) 2), evaluation.getStartDate());
    if (evaluation.getDueDate() != null) {
        setPlainStringCell(courseSheetRow1.createCell((short) 3),
                messageLocator.getMessage("evalsettings.due.date.header"));
        setPlainStringCell(instructorSheetRow1.createCell((short) 3),
                messageLocator.getMessage("evalsettings.due.date.header"));
        setDateCell(courseSheetRow2.createCell((short) 3), evaluation.getDueDate());
        setDateCell(instructorSheetRow2.createCell((short) 3), evaluation.getDueDate());
    }

    // List of groups
    if (groupIDs.length > 0) {
        rowCounter++;
        Row courseSheetRow3 = courseSheet.createRow(rowCounter);
        Row instructorSheetRow3 = instructorSheet.createRow(rowCounter);
        Cell courseSheetCellA3 = courseSheetRow3.createCell((short) 0);
        Cell instructorSheetCellA3 = instructorSheetRow3.createCell((short) 0);

        // Get the section/site titles
        setPlainStringCell(courseSheetCellA3, messageLocator.getMessage("reporting.xls.participants",
                new Object[] { responseAggregator.getCommaSeparatedGroupNames(groupIDs) }));
        setPlainStringCell(instructorSheetCellA3, messageLocator.getMessage("reporting.xls.participants",
                new Object[] { responseAggregator.getCommaSeparatedGroupNames(groupIDs) }));
    }

    // Column headers (static)
    rowCounter += 2;
    short courseSheetHeaderCount = 1;
    short instructorSheetHeaderCount = 1;
    Row courseSheetHeaderRow = courseSheet.createRow(rowCounter);
    Row instructorSheetHeaderRow = instructorSheet.createRow(rowCounter);
    Cell courseSheetSectionHeaderCell = courseSheetHeaderRow.createCell(courseSheetHeaderCount++);
    Cell instructorSheetSectionHeaderCell = instructorSheetHeaderRow.createCell(instructorSheetHeaderCount++);
    if (evaluation.getSectionAwareness()) {
        courseSheetSectionHeaderCell.setCellValue(messageLocator.getMessage("viewreport.section.header"));
        instructorSheetSectionHeaderCell.setCellValue(messageLocator.getMessage("viewreport.section.header"));
    } else {
        courseSheetSectionHeaderCell.setCellValue(messageLocator.getMessage("viewreport.site.header"));
        instructorSheetSectionHeaderCell.setCellValue(messageLocator.getMessage("viewreport.site.header"));
    }
    courseSheetSectionHeaderCell.setCellStyle(boldHeaderStyle);
    instructorSheetSectionHeaderCell.setCellStyle(boldHeaderStyle);
    Cell courseSheetResponseIdHeaderCell = courseSheetHeaderRow.createCell(courseSheetHeaderCount++);
    Cell instructorSheetResponseIdHeaderCell = instructorSheetHeaderRow
            .createCell(instructorSheetHeaderCount++);
    courseSheetResponseIdHeaderCell.setCellValue(messageLocator.getMessage("viewreport.responseID.header"));
    instructorSheetResponseIdHeaderCell.setCellValue(messageLocator.getMessage("viewreport.responseID.header"));
    courseSheetResponseIdHeaderCell.setCellStyle(boldHeaderStyle);
    instructorSheetResponseIdHeaderCell.setCellStyle(boldHeaderStyle);
    Cell instructorSheetInstructorIdHeaderCell = instructorSheetHeaderRow
            .createCell(instructorSheetHeaderCount++);
    instructorSheetInstructorIdHeaderCell
            .setCellValue(messageLocator.getMessage("viewreport.instructorID.header"));
    instructorSheetInstructorIdHeaderCell.setCellStyle(boldHeaderStyle);
    Cell instructorSheetFirstNameHeaderCell = instructorSheetHeaderRow.createCell(instructorSheetHeaderCount++);
    instructorSheetFirstNameHeaderCell.setCellValue(messageLocator.getMessage("viewreport.firstName.header"));
    instructorSheetFirstNameHeaderCell.setCellStyle(boldHeaderStyle);
    Cell instructorSheetLastNameHeaderCell = instructorSheetHeaderRow.createCell(instructorSheetHeaderCount++);
    instructorSheetLastNameHeaderCell.setCellValue(messageLocator.getMessage("viewreport.lastName.header"));
    instructorSheetLastNameHeaderCell.setCellStyle(boldHeaderStyle);

    // Generate dynamic question headers
    List<String> instructorRelatedQuestions = new ArrayList<>();
    for (DataTemplateItem dti : dtiList) {
        // Skip items that aren't for the current user
        if (isItemNotForCurrentUser(instructorViewAllResults, currentUserId, evalOwner, dti)) {
            continue;
        }

        // If there's already a header for a specific instructor question, don't list it twice
        String questionText = commonLogic.makePlainTextFromHTML(dti.templateItem.getItem().getItemText());
        if (instructorRelatedQuestions.contains(questionText)) {
            continue;
        }

        // Add the header to the appropriate worksheet
        Cell questionTextHeaderCell;
        if (EvalConstants.ITEM_CATEGORY_ASSISTANT.equals(dti.associateType)
                || EvalConstants.ITEM_CATEGORY_INSTRUCTOR.equals(dti.associateType)) {
            instructorRelatedQuestions.add(questionText);
            questionTextHeaderCell = instructorSheetHeaderRow.createCell(instructorSheetHeaderCount++);
            questionTextHeaderCell.setCellStyle(boldHeaderStyle);
            if (dti.usesComments()) {
                setPlainStringCell(instructorSheetHeaderRow.createCell(instructorSheetHeaderCount++),
                        messageLocator.getMessage("viewreport.comments.header"))
                                .setCellStyle(italicMiniHeaderStyle);
            }
        } else {
            questionTextHeaderCell = courseSheetHeaderRow.createCell(courseSheetHeaderCount++);
            questionTextHeaderCell.setCellStyle(boldHeaderStyle);
            if (dti.usesComments()) {
                setPlainStringCell(courseSheetHeaderRow.createCell(courseSheetHeaderCount++),
                        messageLocator.getMessage("viewreport.comments.header"))
                                .setCellStyle(italicMiniHeaderStyle);
            }
        }
        setPlainStringCell(questionTextHeaderCell, questionText);
    }

    // Parse out the instructor and course related responeses into separate structures
    List<Long> responseIDs = tidl.getResponseIdsForAnswers();
    List<List<String>> courseRelatedResponses = new ArrayList<>();
    Map<Long, Map<User, List<EvalAnswer>>> answerMap = new HashMap<>();
    for (Long responseID : responseIDs) {
        // Dump the (course related) data into a list of strings representing a spreadsheet row (so it can be sorted)
        List<String> row = new ArrayList<>();
        String groupID = "";
        List<EvalAnswer> answers = tidl.getAnswersByResponseId(responseID);
        if (answers != null && !answers.isEmpty()) {
            groupID = answers.get(0).getResponse().getEvalGroupId();
        }
        row.add(SECTION_OR_SITE_COLUMN_NUM,
                responseAggregator.getCommaSeparatedGroupNames(new String[] { groupID }));
        row.add(RESPONSE_ID_COLUMN_NUM, responseID.toString());

        // Add the response ID to the answer map
        answerMap.put(responseID, new HashMap<>());

        // Loop through the data template items...
        int questionCounter = 0;
        for (DataTemplateItem dti : dtiList) {
            // Skip items that aren't for the current user
            if (isItemNotForCurrentUser(instructorViewAllResults, currentUserId, evalOwner, dti)) {
                continue;
            }

            // If it's an instructor related item... 
            EvalAnswer answer = dti.getAnswer(responseID);
            if (EvalConstants.ITEM_CATEGORY_ASSISTANT.equals(dti.associateType)
                    || EvalConstants.ITEM_CATEGORY_INSTRUCTOR.equals(dti.associateType)) {
                // If the answer is NOT null (it would be null for an instructor from a different section than the evaluator)
                if (answer != null) {
                    // Get the instructor
                    User instructor;
                    try {
                        instructor = userDirectoryService.getUser(answer.getAssociatedId());
                    } catch (UserNotDefinedException ex) {
                        continue;
                    }

                    // If the answer map has a list of answers for this response and this instructor, add the answer to the list
                    Map<User, List<EvalAnswer>> responseAnswers = answerMap.get(responseID);
                    List<EvalAnswer> instructorAnswers = responseAnswers.get(instructor);
                    if (instructorAnswers != null) {
                        instructorAnswers.add(answer);
                    }

                    // Otherwise, the answer map doesn't have a list of answers for this response and this instructor,
                    // create the list and add the answer to it
                    else {
                        instructorAnswers = new ArrayList<>();
                        instructorAnswers.add(answer);
                        responseAnswers.put(instructor, instructorAnswers);
                    }
                }
            }

            // If it's a course related item, just add it normally to the course worksheet
            else {
                row.add(QUESTION_COMMENTS_COLUMN_START_INDEX_COURSE_SHEET + questionCounter, "");
                if (answer != null) {
                    row.set(QUESTION_COMMENTS_COLUMN_START_INDEX_COURSE_SHEET + questionCounter,
                            responseAggregator.formatForSpreadSheet(answer.getTemplateItem(), answer));
                }
                if (dti.usesComments()) {
                    row.add(QUESTION_COMMENTS_COLUMN_START_INDEX_COURSE_SHEET + ++questionCounter,
                            StringUtils.trimToEmpty(answer.getComment()));
                }
                questionCounter++;
            }
        }

        // Add the course row data to the list of course data rows
        courseRelatedResponses.add(row);
    }

    // Convert the map structure of instructor responses into a List<List<String>>, representing rows of data
    List<List<String>> instructorRelatedResponses = new ArrayList<>();
    for (Long responseID : answerMap.keySet()) {
        // Loop through the instructors for the current response
        for (User instructor : answerMap.get(responseID).keySet()) {
            // Dump the data into a list of strings representing a spreadsheet row (so it can be sorted)
            List<String> row = new ArrayList<>();
            row.add(SECTION_OR_SITE_COLUMN_NUM, "");
            row.add(RESPONSE_ID_COLUMN_NUM, responseID.toString());
            row.add(INSTRUCTOR_ID_COLUMN_NUM, "");
            row.add(INSTRUCTOR_FIRST_NAME_COLUMN_NUM, "");
            row.add(INSTRUCTOR_LAST_NAME_COLUMN_NUM, "");
            if (instructor != null) {
                row.set(INSTRUCTOR_ID_COLUMN_NUM, instructor.getDisplayId());
                row.set(INSTRUCTOR_FIRST_NAME_COLUMN_NUM, instructor.getFirstName());
                row.set(INSTRUCTOR_LAST_NAME_COLUMN_NUM, instructor.getLastName());
            }

            int questionCounter = 0;
            for (EvalAnswer answer : answerMap.get(responseID).get(instructor)) {
                row.set(SECTION_OR_SITE_COLUMN_NUM, responseAggregator
                        .getCommaSeparatedGroupNames(new String[] { answer.getResponse().getEvalGroupId() }));
                row.add(QUESTION_COMMENTS_COLUMN_START_INDEX_INSTRUCTOR_SHEET + questionCounter, "");
                row.set(QUESTION_COMMENTS_COLUMN_START_INDEX_INSTRUCTOR_SHEET + questionCounter,
                        responseAggregator.formatForSpreadSheet(answer.getTemplateItem(), answer));
                String comment = StringUtils.trimToEmpty(answer.getComment());
                if (!comment.isEmpty()) {
                    row.add(QUESTION_COMMENTS_COLUMN_START_INDEX_INSTRUCTOR_SHEET + ++questionCounter,
                            (StringUtils.trimToEmpty(answer.getComment())));
                }
                questionCounter++;
            }

            // Add the row to the list of rows
            instructorRelatedResponses.add(row);
        }
    }

    // Sort the row data lists
    SortBySectionOrSiteComparator sorter = new SortBySectionOrSiteComparator();
    Collections.sort(instructorRelatedResponses, sorter);
    Collections.sort(courseRelatedResponses, sorter);

    // Output the sorted course related data into the course spreadsheet
    rowCounter = 0;
    for (List<String> row : courseRelatedResponses) {
        // Course sheet answer row, index cell
        short columnCounter = SECTION_OR_SITE_COLUMN_NUM;
        Row courseSheetAnswerRow = courseSheet.createRow(FIRST_ANSWER_ROW + rowCounter);
        Cell courseAnswerIndexCell = courseSheetAnswerRow.createCell(columnCounter++);
        courseAnswerIndexCell.setCellValue(rowCounter + 1);
        courseAnswerIndexCell.setCellStyle(boldHeaderStyle);

        // Course sheet section cell, response ID cell
        Cell courseSheetSectionCell = courseSheetAnswerRow.createCell(columnCounter++);
        Cell courseSheetResponseIdCell = courseSheetAnswerRow.createCell(columnCounter++);
        courseSheetSectionCell.setCellValue(row.get(SECTION_OR_SITE_COLUMN_NUM));
        courseSheetResponseIdCell.setCellValue(Integer.parseInt(row.get(RESPONSE_ID_COLUMN_NUM)));

        // Responses and comments
        for (int i = QUESTION_COMMENTS_COLUMN_START_INDEX_COURSE_SHEET; i < row.size(); i++) {
            setPlainStringCell(courseSheetAnswerRow.createCell(columnCounter++), row.get(i));
        }

        // Increment the row counter
        rowCounter++;
    }

    // Output the sorted instructor related data into the instructor spreadsheet
    rowCounter = 0;
    for (List<String> row : instructorRelatedResponses) {
        // Answer row, index cell
        short columnCounter = SECTION_OR_SITE_COLUMN_NUM;
        Row instructorSheetAnswerRow = instructorSheet.createRow(FIRST_ANSWER_ROW + rowCounter);
        Cell instructorAnswerIndexCell = instructorSheetAnswerRow.createCell(columnCounter++);
        instructorAnswerIndexCell.setCellValue(rowCounter + 1);
        instructorAnswerIndexCell.setCellStyle(boldHeaderStyle);

        // Section cell, response ID cell
        Cell instructorSheetSectionCell = instructorSheetAnswerRow.createCell(columnCounter++);
        Cell instructorSheetResponseIdCell = instructorSheetAnswerRow.createCell(columnCounter++);
        instructorSheetSectionCell.setCellValue(row.get(SECTION_OR_SITE_COLUMN_NUM));
        instructorSheetResponseIdCell.setCellValue(Integer.parseInt(row.get(RESPONSE_ID_COLUMN_NUM)));

        // Instructor ID, first name, last name cells
        Cell instructorIdCell = instructorSheetAnswerRow.createCell(columnCounter++);
        Cell instructorFirstNameCell = instructorSheetAnswerRow.createCell(columnCounter++);
        Cell instructorLastNameCell = instructorSheetAnswerRow.createCell(columnCounter++);
        instructorIdCell.setCellValue(row.get(INSTRUCTOR_ID_COLUMN_NUM));
        instructorFirstNameCell.setCellValue(row.get(INSTRUCTOR_FIRST_NAME_COLUMN_NUM));
        instructorLastNameCell.setCellValue(row.get(INSTRUCTOR_LAST_NAME_COLUMN_NUM));

        // Responses and comments
        for (int i = QUESTION_COMMENTS_COLUMN_START_INDEX_INSTRUCTOR_SHEET; i < row.size(); i++) {
            setPlainStringCell(instructorSheetAnswerRow.createCell(columnCounter++), row.get(i));
        }

        // Increment the row counter
        rowCounter++;
    }

    // Dump the output to the response stream
    try {
        wb.write(outputStream);
    } catch (IOException e) {
        throw UniversalRuntimeException.accumulate(e, "Could not get Writer to dump output to xls");
    }

}

From source file:org.sakaiproject.evaluation.tool.reporting.XLSReportExporter.java

License:Educational Community License

public void buildReport(EvalEvaluation evaluation, String[] groupIds, String evaluateeId,
        OutputStream outputStream, boolean newReportStyle) {

    /*//  w  ww.j  av a2 s.c om
     * Logic for creating this view 1) make tidl 2) get DTIs for this eval from tidl 3) use DTIs
     * to make the headers 4) get responseIds from tidl 5) loop over response ids 6) loop over
     * DTIs 7) check answersmap for an answer, if there put in cell, if missing, insert blank 8)
     * done
     */

    //Make sure responseAggregator is using this messageLocator
    responseAggregator.setMessageLocator(messageLocator);

    // Determine which report style to use; normal or section based
    if (newReportStyle) {
        buildReportSectionAware(evaluation, groupIds, outputStream);
    } else {
        Boolean instructorViewAllResults = (boolean) evaluation.getInstructorViewAllResults();
        String currentUserId = commonLogic.getCurrentUserId();
        String evalOwner = evaluation.getOwner();

        boolean isCurrentUserAdmin = commonLogic.isUserAdmin(currentUserId);

        // 1 Make TIDL
        TemplateItemDataList tidl = getEvalTIDL(evaluation, groupIds);
        // 2: get DTIs for this eval from tidl
        List<DataTemplateItem> dtiList = tidl.getFlatListOfDataTemplateItems(true);

        Workbook wb = new XSSFWorkbook();
        creationHelper = wb.getCreationHelper();

        Sheet sheet = wb.createSheet(messageLocator.getMessage("reporting.xls.sheetname"));

        // Title Style
        Font font = wb.createFont();
        font.setFontHeightInPoints((short) 12);
        font.setBold(true);
        CellStyle mainTitleStyle = wb.createCellStyle();
        mainTitleStyle.setFont(font);

        // Bold header style
        font = wb.createFont();
        font.setFontHeightInPoints((short) 10);
        font.setBold(true);
        CellStyle boldHeaderStyle = wb.createCellStyle();
        boldHeaderStyle.setFont(font);

        // Italic meta header style
        font = wb.createFont();
        font.setFontHeightInPoints((short) 10);
        font.setItalic(true);
        CellStyle italicMiniHeaderStyle = wb.createCellStyle();
        italicMiniHeaderStyle.setFont(font);

        // Date meta Style
        dateCellStyle = wb.createCellStyle();
        // TODO FIXME HELPME To properly
        // String dateCellFormat = ((SimpleDateFormat)DateFormat.getDateInstance(DateFormat.MEDIUM,
        // localeGetter.get())).toLocalizedPattern();
        // http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFDataFormat.html
        dateCellStyle.setDataFormat((short) 0x16);

        // Evaluation Title
        Row row1 = sheet.createRow(0);
        Cell cellA1 = row1.createCell((short) 0);
        setPlainStringCell(cellA1, evaluation.getTitle());
        cellA1.setCellStyle(mainTitleStyle);

        // calculate the response rate
        // int responsesCount = deliveryService.countResponses(evaluation.getId(), null, true);
        int responsesCount = evaluationService.countResponses(null, new Long[] { evaluation.getId() }, groupIds,
                null);
        int enrollmentsCount = evaluationService.countParticipantsForEval(evaluation.getId(), groupIds);

        Row row2 = sheet.createRow(1);
        Cell cellA2 = row2.createCell((short) 0);
        cellA2.setCellStyle(boldHeaderStyle);
        setPlainStringCell(cellA2,
                EvalUtils.makeResponseRateStringFromCounts(responsesCount, enrollmentsCount));

        // dates
        setPlainStringCell(row1.createCell((short) 2),
                messageLocator.getMessage("evalsettings.start.date.header"));
        setDateCell(row2.createCell((short) 2), evaluation.getStartDate());
        if (evaluation.getDueDate() != null) {
            setPlainStringCell(row1.createCell((short) 3),
                    messageLocator.getMessage("evalsettings.due.date.header"));
            setDateCell(row2.createCell((short) 3), evaluation.getDueDate());
        }

        // add in list of groups
        if (groupIds.length > 0) {
            Row row3 = sheet.createRow(2);
            Cell cellA3 = row3.createCell((short) 0);
            setPlainStringCell(cellA3, messageLocator.getMessage("reporting.xls.participants",
                    new Object[] { responseAggregator.getCommaSeparatedGroupNames(groupIds) }));
        }

        // 3 use DTIs to make the headers
        Row questionCatRow = sheet.createRow(QUESTION_CAT_ROW);
        Row questionTypeRow = sheet.createRow(QUESTION_TYPE_ROW);
        Row questionTextRow = sheet.createRow(QUESTION_TEXT_ROW);
        short headerCount = 1;
        for (DataTemplateItem dti : dtiList) {

            if (!instructorViewAllResults // If the eval is so configured,
                    && !isCurrentUserAdmin // and currentUser is not an admin
                    && !currentUserId.equals(evalOwner) // and currentUser is not the eval creator
                    && !EvalConstants.ITEM_CATEGORY_COURSE.equals(dti.associateType)
                    && !currentUserId.equals(commonLogic.getEvalUserById(dti.associateId).userId)) {
                // skip items that aren't for the current user
                continue;
            }

            Cell cell = questionTypeRow.createCell(headerCount);

            setPlainStringCell(cell, responseAggregator.getHeaderLabelForItemType(dti.getTemplateItemType()));
            cell.setCellStyle(italicMiniHeaderStyle);

            Cell questionText = questionTextRow.createCell(headerCount);
            setPlainStringCell(questionText,
                    commonLogic.makePlainTextFromHTML(dti.templateItem.getItem().getItemText()));

            Cell questionCat = questionCatRow.createCell(headerCount);
            if (EvalConstants.ITEM_CATEGORY_INSTRUCTOR.equals(dti.associateType)) {
                EvalUser user = commonLogic.getEvalUserById(dti.associateId);
                String instructorMsg = messageLocator.getMessage("reporting.spreadsheet.instructor",
                        new Object[] { user.displayName });
                setPlainStringCell(questionCat, instructorMsg);
            } else if (EvalConstants.ITEM_CATEGORY_ASSISTANT.equals(dti.associateType)) {
                EvalUser user = commonLogic.getEvalUserById(dti.associateId);
                String assistantMsg = messageLocator.getMessage("reporting.spreadsheet.ta",
                        new Object[] { user.displayName });
                setPlainStringCell(questionCat, assistantMsg);
            } else if (EvalConstants.ITEM_CATEGORY_COURSE.equals(dti.associateType)) {
                setPlainStringCell(questionCat, messageLocator.getMessage("reporting.spreadsheet.course"));
            } else {
                setPlainStringCell(questionCat, messageLocator.getMessage("unknown.caps"));
            }

            headerCount++;

            if (dti.usesComments()) {
                // add an extra column for comments
                setPlainStringCell(questionTypeRow.createCell(headerCount),
                        messageLocator.getMessage("viewreport.comments.header"))
                                .setCellStyle(italicMiniHeaderStyle);
                headerCount++;
            }

        }

        // 4) get responseIds from tidl
        List<Long> responseIds = tidl.getResponseIdsForAnswers();

        // 5) loop over response ids
        short responseIdCounter = 0;
        for (Long responseId : responseIds) {
            Row row = sheet.createRow(responseIdCounter + FIRST_ANSWER_ROW);
            Cell indexCell = row.createCell((short) 0);
            indexCell.setCellValue(responseIdCounter + 1);
            indexCell.setCellStyle(boldHeaderStyle);
            // 6) loop over DTIs
            short dtiCounter = 1;
            for (DataTemplateItem dti : dtiList) {

                if (!instructorViewAllResults // If the eval is so configured,
                        && !isCurrentUserAdmin // and currentUser is not an admin
                        && !currentUserId.equals(evalOwner) // and currentUser is not the eval creator
                        && !EvalConstants.ITEM_CATEGORY_COURSE.equals(dti.associateType)
                        && !currentUserId.equals(commonLogic.getEvalUserById(dti.associateId).userId)) {
                    //skip instructor items that aren't for the current user
                    continue;
                }

                // 7) check answersmap for an answer, if there put in cell, if missing, insert blank
                EvalAnswer answer = dti.getAnswer(responseId);
                Cell responseCell = row.createCell(dtiCounter);
                // In Eval, users can leave questions blank, in which case this will be null
                if (answer != null) {
                    setPlainStringCell(responseCell,
                            responseAggregator.formatForSpreadSheet(answer.getTemplateItem(), answer));
                }
                if (dti.usesComments()) {
                    // put comment in the extra column
                    dtiCounter++;
                    setPlainStringCell(row.createCell(dtiCounter),
                            (answer == null || EvalUtils.isBlank(answer.getComment())) ? ""
                                    : answer.getComment());
                }
                dtiCounter++;
            }
            responseIdCounter++;
        }

        // dump the output to the response stream
        try {
            wb.write(outputStream);
        } catch (IOException e) {
            throw UniversalRuntimeException.accumulate(e, "Could not get Writer to dump output to xls");
        }
    }
}

From source file:org.sigmah.server.endpoint.export.sigmah.spreadsheet.ExcelUtils.java

License:Open Source License

public Font getItalicFont(Workbook wb, short size) {
    Font font = wb.createFont();
    font.setFontHeightInPoints(size);/*from  w  w w  .ja  v  a  2  s .com*/
    font.setItalic(true);
    return font;
}

From source file:org.unitime.timetable.export.XLSPrinter.java

License:Apache License

protected Font getFont(boolean bold, boolean italic, boolean underline, Color c) {
    Short color = null;/*from  ww w. j a v  a  2  s . c  o  m*/
    if (c == null)
        c = Color.BLACK;
    if (c != null) {
        String colorId = Integer.toHexString(c.getRGB());
        color = iColors.get(colorId);
        if (color == null) {
            HSSFPalette palette = ((HSSFWorkbook) iWorkbook).getCustomPalette();
            HSSFColor clr = palette.findSimilarColor(c.getRed(), c.getGreen(), c.getBlue());
            color = (clr == null ? IndexedColors.BLACK.getIndex() : clr.getIndex());
            iColors.put(colorId, color);
        }
    }
    String fontId = (bold ? "b" : "") + (italic ? "i" : "") + (underline ? "u" : "")
            + (color == null ? "" : color);
    Font font = iFonts.get(fontId);
    if (font == null) {
        font = iWorkbook.createFont();
        font.setBold(bold);
        font.setItalic(italic);
        font.setUnderline(underline ? Font.U_SINGLE : Font.U_NONE);
        font.setColor(color);
        font.setFontHeightInPoints((short) 10);
        font.setFontName("Arial");
        iFonts.put(fontId, font);
    }
    return font;
}

From source file:org.xianairlines.action.staffs.StaffsList.java

public void exportStaffsByColumNames() throws UnsupportedEncodingException {
    ServletOutputStream os = null;/*from  w  w w .java 2s  . c om*/
    try {
        final HttpServletResponse response = (HttpServletResponse) extCtx.getResponse();
        os = response.getOutputStream();
        response.setContentType("application/x-download");
        final String newFileName = encodeFileName("??.xls");
        response.addHeader("Content-disposition", "attachment;filename=" + newFileName + ";charset=UTF-8");
        Workbook wb = new HSSFWorkbook();
        Sheet sheet1 = wb.createSheet("sheet1");
        Row row = null;
        Cell cell = null;
        CellStyle cellStyle = wb.createCellStyle();
        // ?
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
        // ?
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        // Font
        Font font = wb.createFont();
        font.setFontName("");
        font.setColor(HSSFColor.BLUE.index);
        font.setItalic(true);
        font.setFontHeight((short) 300);
        row = sheet1.createRow(0);
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
        for (int i = 0; i < columNames.length; i++) {
            sheet1.setColumnWidth(i, (short) 6000);
            String[] colums = columNames[i].split(",");
            cell = row.createCell(i);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(colums[1]);
        }
        List<Staffs> list = this.getResultList();
        for (int i = 1; i <= list.size(); i = i + 1) {
            row = sheet1.createRow(i);
            row.setHeightInPoints(20);
            for (int j = 0; j < columNames.length; j++) {
                String[] colums = columNames[j].split(",");
                cell = row.createCell(j);
                cell.setCellStyle(cellStyle);
                Object value = this.getStaffsFieldValue((Staffs) list.get(i - 1), colums[0]);
                if (value == null) {
                    cell.setCellValue("");
                } else if (value instanceof java.util.Date) {
                    String cellValue = dateFormat.format((java.util.Date) value);
                    cell.setCellValue(cellValue);
                } else {
                    cell.setCellValue(value.toString());
                }

            }
        }
        wb.write(os);
        os.flush();
    } catch (Exception e) {
    } finally {
        if (os != null) {
            try {
                os.close();
            } catch (IOException e) {
            }
        }
        facesContext.responseComplete();
    }

}

From source file:packtest.CreateCell.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
    CreationHelper creationHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet("new sheet");

    // Create a row and put some cells in it. Rows are 0 based.
    Row row = sheet.createRow((short) 0);
    // Create a cell and put a value in it.
    Cell cell = row.createCell((short) 0);
    cell.setCellValue(1);//from www  . j  a va  2s.c  o  m

    //numeric value
    row.createCell(1).setCellValue(1.2);

    //plain string value
    row.createCell(2).setCellValue("This is a string cell");

    //rich text string
    RichTextString str = creationHelper.createRichTextString("Apache");
    Font font = wb.createFont();
    font.setItalic(true);
    font.setUnderline(Font.U_SINGLE);
    str.applyFont(font);
    row.createCell(3).setCellValue(str);

    //boolean value
    row.createCell(4).setCellValue(true);

    //formula
    row.createCell(5).setCellFormula("SUM(A1:B1)");

    //date
    CellStyle style = wb.createCellStyle();
    style.setDataFormat(creationHelper.createDataFormat().getFormat("m/d/yy h:mm"));
    cell = row.createCell(6);
    cell.setCellValue(new Date());
    cell.setCellStyle(style);

    //hyperlink
    row.createCell(7).setCellFormula("SUM(A1:B1)");
    cell.setCellFormula("HYPERLINK(\"http://google.com\",\"Google\")");

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("ooxml-cell.xlsx");
    wb.write(fileOut);
    fileOut.close();
}

From source file:pe.gob.mef.gescon.util.ExcelUtil.java

/**
 * Mtodo que crea la fuente para la cabecera.
 * @return fuente Fuente de la cabecera, tipo Font.
 *//* w  w w  .java2  s  .c om*/
private Font creaFuenteCabecera() {
    Font fuente = libroExcel.createFont();
    fuente.setFontHeightInPoints((short) 9);
    fuente.setFontName("Courier New");
    fuente.setItalic(true);
    fuente.setColor((short) 0xc);
    return fuente;
}

From source file:uk.co.certait.htmlexporter.writer.excel.ExcelStyleGenerator.java

License:Apache License

public Font createFont(Workbook workbook, Style style) {
    Font font = workbook.createFont();

    if (style.isFontNameSet()) {
        font.setFontName(style.getProperty(CssStringProperty.FONT_FAMILY));
    }//from   w  w w  . j a va 2s . c  om

    if (style.isFontSizeSet()) {
        font.setFontHeightInPoints((short) style.getProperty(CssIntegerProperty.FONT_SIZE));
    }

    if (style.isColorSet()) {
        Color color = style.getProperty(CssColorProperty.COLOR);

        // if(! color.equals(Color.WHITE)) // POI Bug
        // {
        ((XSSFFont) font).setColor(new XSSFColor(color));
        // }
    }

    if (style.isFontBold()) {
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    }

    font.setItalic(style.isFontItalic());

    if (style.isTextUnderlined()) {
        font.setUnderline(Font.U_SINGLE);
    }

    return font;
}

From source file:uk.co.spudsoft.birt.emitters.excel.FontManager.java

License:Open Source License

/**
 * Create a new POI Font based upon a BIRT style.
 * @param birtStyle/*  ww w  .  j a va  2s. c o  m*/
 * The BIRT style to base the Font upon.
 * @return
 * The Font whose attributes are described by the BIRT style. 
 */
private Font createFont(BirtStyle birtStyle) {
    Font font = workbook.createFont();

    // Family
    String fontName = smu
            .poiFontNameFromBirt(cleanupQuotes(birtStyle.getProperty(StyleConstants.STYLE_FONT_FAMILY)));
    if (fontName == null) {
        fontName = "Calibri";
    }
    font.setFontName(fontName);
    // Size
    short fontSize = smu.fontSizeInPoints(cleanupQuotes(birtStyle.getProperty(StyleConstants.STYLE_FONT_SIZE)));
    if (fontSize > 0) {
        font.setFontHeightInPoints(fontSize);
    }
    // Weight
    short fontWeight = smu
            .poiFontWeightFromBirt(cleanupQuotes(birtStyle.getProperty(StyleConstants.STYLE_FONT_WEIGHT)));
    if (fontWeight > 0) {
        font.setBoldweight(fontWeight);
    }
    // Style
    String fontStyle = cleanupQuotes(birtStyle.getProperty(StyleConstants.STYLE_FONT_STYLE));
    if (CSSConstants.CSS_ITALIC_VALUE.equals(fontStyle) || CSSConstants.CSS_OBLIQUE_VALUE.equals(fontStyle)) {
        font.setItalic(true);
    }
    // Underline
    String fontUnderline = cleanupQuotes(birtStyle.getProperty(StyleConstants.STYLE_TEXT_UNDERLINE));
    if (CSSConstants.CSS_UNDERLINE_VALUE.equals(fontUnderline)) {
        font.setUnderline(FontUnderline.SINGLE.getByteValue());
    }
    // Colour
    smu.addColourToFont(workbook, font, cleanupQuotes(birtStyle.getProperty(StyleConstants.STYLE_COLOR)));

    fonts.add(new FontPair(birtStyle, font));
    return font;
}