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

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

Introduction

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

Prototype

CellStyle createCellStyle();

Source Link

Document

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

Usage

From source file:org.riflemansd.businessprofit.excel.ExcelExampleFont.java

License:Open Source License

public static void main(String[] args) {
    // create a new file
    FileOutputStream out = null;// ww w .  j  av a2  s  . co  m
    try {
        out = new FileOutputStream("workbook.xls");
    } catch (FileNotFoundException ex) {
        Logger.getLogger(ExcelExampleFont.class.getName()).log(Level.SEVERE, null, ex);
    }
    // create a new workbook
    Workbook wb = new HSSFWorkbook();
    // create a new sheet
    Sheet s = wb.createSheet();
    // declare a row object reference
    Row r = null;
    // declare a cell object reference
    Cell c = null;
    // create 3 cell styles
    CellStyle cs = wb.createCellStyle();
    CellStyle cs2 = wb.createCellStyle();
    CellStyle cs3 = wb.createCellStyle();
    DataFormat df = wb.createDataFormat();
    // create 2 fonts objects
    Font f = wb.createFont();
    Font f2 = wb.createFont();

    //set font 1 to 12 point type
    f.setFontHeightInPoints((short) 12);
    //make it blue
    f.setColor((short) 0xc);
    // make it bold
    //arial is the default font
    f.setBoldweight(Font.BOLDWEIGHT_BOLD);

    //set font 2 to 10 point type
    f2.setFontHeightInPoints((short) 10);
    //make it red
    f2.setColor((short) Font.COLOR_RED);
    //make it bold
    f2.setBoldweight(Font.BOLDWEIGHT_BOLD);

    f2.setStrikeout(true);

    //set cell stlye
    cs.setFont(f);
    //set the cell format 
    cs.setDataFormat(df.getFormat("#,##0.0"));

    //set a thin border
    cs2.setBorderBottom(cs2.BORDER_THIN);
    //fill w fg fill color
    cs2.setFillPattern((short) CellStyle.SOLID_FOREGROUND);
    //set the cell format to text see DataFormat for a full list
    cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));

    // set the font
    cs2.setFont(f2);

    // set the sheet name in Unicode
    wb.setSheetName(0, "\u0422\u0435\u0441\u0442\u043E\u0432\u0430\u044F "
            + "\u0421\u0442\u0440\u0430\u043D\u0438\u0447\u043A\u0430");
    // in case of plain ascii
    // wb.setSheetName(0, "HSSF Test");
    // create a sheet with 30 rows (0-29)
    int rownum;
    for (rownum = (short) 0; rownum < 30; rownum++) {
        // create a row
        r = s.createRow(rownum);
        // on every other row
        if ((rownum % 2) == 0) {
            // make the row height bigger  (in twips - 1/20 of a point)
            r.setHeight((short) 0x249);
        }

        //r.setRowNum(( short ) rownum);
        // create 10 cells (0-9) (the += 2 becomes apparent later
        for (short cellnum = (short) 0; cellnum < 10; cellnum += 2) {
            // create a numeric cell
            c = r.createCell(cellnum);
            // do some goofy math to demonstrate decimals
            c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000)));

            String cellValue;

            // create a string cell (see why += 2 in the
            c = r.createCell((short) (cellnum + 1));

            // on every other row
            if ((rownum % 2) == 0) {
                // set this cell to the first cell style we defined
                c.setCellStyle(cs);
                // set the cell's string value to "Test"
                c.setCellValue("Test");
            } else {
                c.setCellStyle(cs2);
                // set the cell's string value to "\u0422\u0435\u0441\u0442"
                c.setCellValue("\u0422\u0435\u0441\u0442");
            }

            // make this column a bit wider
            s.setColumnWidth((short) (cellnum + 1), (short) ((50 * 8) / ((double) 1 / 20)));
        }
    }

    //draw a thick black border on the row at the bottom using BLANKS
    // advance 2 rows
    rownum++;
    rownum++;

    r = s.createRow(rownum);

    // define the third style to be the default
    // except with a thick black border at the bottom
    cs3.setBorderBottom(cs3.BORDER_THICK);

    //create 50 cells
    for (short cellnum = (short) 0; cellnum < 50; cellnum++) {
        //create a blank type cell (no value)
        c = r.createCell(cellnum);
        // set it to the thick black border style
        c.setCellStyle(cs3);
    }

    //end draw thick black border

    // demonstrate adding/naming and deleting a sheet
    // create a sheet, set its title then delete it
    s = wb.createSheet();
    wb.setSheetName(1, "DeletedSheet");
    wb.removeSheetAt(1);
    //end deleted sheet
    try {
        // write the workbook to the output stream
        // close our file (don't blow out our file handles
        wb.write(out);
    } catch (IOException ex) {
        Logger.getLogger(ExcelExampleFont.class.getName()).log(Level.SEVERE, null, ex);
    }
    try {
        out.close();
    } catch (IOException ex) {
        Logger.getLogger(ExcelExampleFont.class.getName()).log(Level.SEVERE, null, ex);
    }
}

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

License:Educational Community License

/**
 * Build the .xls report in the new (section based) format.
 * //w  ww.  ja v  a  2 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) {

    /*/*from  w  w w. j a v a  2 s  . co m*/
     * 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.sakaiproject.jsf.spreadsheet.SpreadsheetDataFileWriterXlsx.java

License:Educational Community License

private Workbook getAsWorkbook(List<List<Object>> spreadsheetData) {
    Workbook wb = new SXSSFWorkbook();
    Sheet sheet = wb.createSheet();/*from  www. j  av a 2s.c  om*/
    CellStyle headerCs = wb.createCellStyle();
    Iterator<List<Object>> dataIter = spreadsheetData.iterator();

    // Set the header style
    headerCs.setBorderBottom(BorderStyle.THICK);
    headerCs.setFillBackgroundColor(IndexedColors.BLUE_GREY.getIndex());

    // Set the font
    CellStyle cellStyle = null;
    String fontName = ServerConfigurationService.getString("spreadsheet.font");
    if (fontName != null) {
        Font font = wb.createFont();
        font.setFontName(fontName);
        headerCs.setFont(font);
        cellStyle = wb.createCellStyle();
        cellStyle.setFont(font);
    }

    // By convention, the first list in the list contains column headers.
    Row headerRow = sheet.createRow((short) 0);
    List<Object> headerList = dataIter.next();
    for (short i = 0; i < headerList.size(); i++) {
        Cell headerCell = createCell(headerRow, i);
        headerCell.setCellValue((String) headerList.get(i));
        headerCell.setCellStyle(headerCs);
        //TODO
        //sheet.autoSizeColumn(i);
    }

    short rowPos = 1;
    while (dataIter.hasNext()) {
        List<Object> rowData = dataIter.next();
        Row row = sheet.createRow(rowPos++);
        for (short i = 0; i < rowData.size(); i++) {
            Cell cell = createCell(row, i);
            Object data = rowData.get(i);
            if (data != null) {
                if (data instanceof Double) {
                    cell.setCellValue(((Double) data).doubleValue());
                } else {
                    cell.setCellValue(data.toString());
                }
                if (cellStyle != null) {
                    cell.setCellStyle(cellStyle);
                }
            }
        }
    }

    return wb;
}

From source file:org.sakaiproject.signup.tool.downloadEvents.WorksheetStyleClass.java

License:Educational Community License

public static Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    CellStyle style;/* w w  w. j a  v  a 2  s .co m*/
    Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short) 18);
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    titleFont.setColor(IndexedColors.DARK_BLUE.getIndex());
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFont(titleFont);
    styles.put("title", style);

    Font commentTitleFont = wb.createFont();
    commentTitleFont.setFontHeightInPoints((short) 12);
    commentTitleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    commentTitleFont.setColor(IndexedColors.DARK_BLUE.getIndex());
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFont(commentTitleFont);
    styles.put("commentTitle", style);

    Font itemFont = wb.createFont();
    itemFont.setFontHeightInPoints((short) 10);
    itemFont.setFontName("Trebuchet MS");
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFont(itemFont);
    styles.put("item_left", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFont(itemFont);
    style.setWrapText(true);
    styles.put("item_left_wrap", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    style.setFont(itemFont);
    style.setWrapText(true);
    styles.put("item_left_wrap_top", style);

    itemFont.setFontHeightInPoints((short) 10);
    itemFont.setFontName("Trebuchet MS");
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFont(itemFont);
    styles.put("tabItem_fields", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(itemFont);
    styles.put("item_right", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFont(itemFont);
    style.setWrapText(true);
    styles.put("attendee_layout", style);

    Font itemBoldFont = wb.createFont();
    itemBoldFont.setFontHeightInPoints((short) 10);
    itemBoldFont.setFontName("Trebuchet MS");
    itemBoldFont.setColor(IndexedColors.DARK_BLUE.getIndex());
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    itemBoldFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style.setFont(itemBoldFont);
    styles.put("item_leftBold", style);

    Font tableFont = wb.createFont();
    tableFont.setFontHeightInPoints((short) 12);
    tableFont.setColor(IndexedColors.WHITE.getIndex());
    tableFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(tableFont);
    styles.put("tabColNames", style);

    tableFont.setFontHeightInPoints((short) 10);
    tableFont.setColor(IndexedColors.WHITE.getIndex());
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(tableFont);
    style.setWrapText(true);
    styles.put("header", style);

    Font linkFont = wb.createFont();
    linkFont.setFontHeightInPoints((short) 10);
    linkFont.setColor(IndexedColors.BLUE.getIndex());
    linkFont.setUnderline(Font.U_SINGLE);
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFont(linkFont);
    styles.put("hyperLink", style);

    style = wb.createCellStyle();
    style.setBorderTop(CellStyle.BORDER_THICK);
    style.setTopBorderColor(IndexedColors.DARK_BLUE.getIndex());
    styles.put("tab_endline", style);

    return styles;
}

From source file:org.sakaiproject.tool.assessment.ui.bean.evaluation.ExportResponsesBean.java

License:Educational Community License

public Workbook getAsWorkbook(List<List<Object>> spreadsheetData) {
    // outer list is rows, inner list is columns (cells in the row)
    int columns = findColumnSize(spreadsheetData);
    Workbook wb;
    if (columns < 255) {
        log.info("Samigo export (" + columns + " columns): Using xsl format");
        wb = new HSSFWorkbook();
    } else {/*from   w  w w  . j  a  va 2s  .  c  o m*/
        // allows for greater than 255 columns - SAK-16560
        log.info("Samigo export (" + columns + " columns): Using xslx format");
        wb = new XSSFWorkbook();
    }

    CellStyle boldStyle = wb.createCellStyle();
    Font font = wb.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    String fontName = ServerConfigurationService.getString("spreadsheet.font");
    if (fontName != null) {
        font.setFontName(fontName);
    }
    boldStyle.setFont(font);
    CellStyle headerStyle = boldStyle;

    CellStyle cellStyle = null;
    if (fontName != null) {
        font = wb.createFont();
        font.setFontName(fontName);
        cellStyle = wb.createCellStyle();
        cellStyle.setFont(font);
    }

    Sheet sheet = null;

    Iterator<List<Object>> dataIter = spreadsheetData.iterator();

    short rowPos = 0;
    while (dataIter.hasNext()) {
        List<Object> rowData = dataIter.next();

        if (rowData.get(0).toString().equals(NEW_SHEET_MARKER)) {
            sheet = wb.createSheet(rowData.get(1).toString());
            rowPos = 0;
        }
        // By convention, the first list in the list contains column headers.
        // This should only happen once and usually only in a single-sheet workbook
        else if (rowData.get(0).toString().equals(HEADER_MARKER)) {
            if (sheet == null) {
                sheet = wb.createSheet("responses"); // avoid NPE
            }
            Row headerRow = sheet.createRow(rowPos++);
            for (short i = 0; i < rowData.size() - 1; i++) {
                createCell(headerRow, i, headerStyle).setCellValue(rowData.get(i + 1).toString());
            }
        } else {
            if (sheet == null) {
                sheet = wb.createSheet("responses"); // avoid NPE
            }
            Row row = sheet.createRow(rowPos++);
            short colPos = 0;
            Iterator colIter = rowData.iterator();
            while (colIter.hasNext()) {
                //for (short i = 0; i < rowData.size(); i++) {
                Cell cell = null;

                //Object data = rowData.get(i);
                Object data = colIter.next();
                if (data != null) {
                    if (data.toString().startsWith(FORMAT)) {
                        if (data.equals(FORMAT_BOLD)) {
                            cell = createCell(row, colPos++, boldStyle);
                        }
                        data = colIter.next();
                    } else {
                        cell = createCell(row, colPos++, cellStyle);
                    }
                    if (data != null) {
                        if (data instanceof Double) {
                            cell.setCellValue(((Double) data).doubleValue());
                        } else {
                            AnswerSurveyConverter converter = new AnswerSurveyConverter();
                            String datac = converter.getAsString(null, null, data.toString());
                            // stripping html for export, SAK-17021
                            cell.setCellValue(FormattedText.convertFormattedTextToPlaintext(datac));
                        }
                    }
                }
            }
        }

    }

    return wb;
}

From source file:org.sakaiproject.tool.assessment.ui.bean.questionpool.QuestionPoolBean.java

License:Educational Community License

/**
 * //from w  w w  . jav a2  s  . com
 * @param spreadsheetData
 * @return
 */
public Workbook getAsWorkbook(List<List<Object>> spreadsheetData) {
    // outer list is rows, inner list is columns (cells in the row)
    int columns = findColumnSize(spreadsheetData);
    Workbook wb = new HSSFWorkbook();
    if (columns < 255) {
        log.info("Samigo export (" + columns + " columns): Using xsl format");
    } else {
        // allows for greater than 255 columns - SAK-16560
        log.info("Samigo export (" + columns + " columns): Using xslx format");
    }

    CellStyle boldStyle = wb.createCellStyle();
    Font font = wb.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    boldStyle.setFont(font);
    CellStyle headerStyle = boldStyle;

    Sheet sheet = null;

    short rowPos = 0;
    for (List<Object> rowData : spreadsheetData) {

        if (ExportResponsesBean.NEW_SHEET_MARKER.equals(rowData.get(0).toString())) {
            sheet = wb.createSheet(rowData.get(1).toString());
            rowPos = 0;
        }
        // By convention, the first list in the list contains column headers.
        // This should only happen once and usually only in a single-sheet workbook
        else if (ExportResponsesBean.HEADER_MARKER.equals(rowData.get(0).toString())) {
            if (sheet == null) {
                sheet = wb.createSheet("responses"); // avoid NPE
            }
            Row headerRow = sheet.createRow(rowPos++);
            short colPos = 0;
            for (Object data : rowData) {
                createCell(headerRow, colPos++, headerStyle).setCellValue(data.toString());
            }
        } else {
            if (sheet == null) {
                sheet = wb.createSheet("responses"); // avoid NPE
            }
            Row row = sheet.createRow(rowPos++);
            short colPos = 0;
            for (Object data : rowData) {
                Cell cell = null;

                if (data != null) {
                    if (StringUtils.startsWith(data.toString(), ExportResponsesBean.FORMAT)) {
                        if (ExportResponsesBean.FORMAT_BOLD.equals(data)) {
                            cell = createCell(row, colPos++, boldStyle);
                        }
                    } else {
                        cell = createCell(row, colPos++, null);
                    }
                    if (data != null) {
                        if (data instanceof Double) {
                            cell.setCellValue(((Double) data).doubleValue());
                        } else {
                            // stripping html for export, SAK-17021
                            cell.setCellValue(data.toString());
                        }
                    }
                }
            }
        }

    }

    return wb;
}

From source file:org.seedstack.io.jasper.fixtures.CustomXlsRenderer.java

License:Mozilla Public License

@Override
public void render(OutputStream outputStream, Object model, String mimeType, Map<String, Object> parameters) {
    Validate.isTrue(StringUtils.equals(mimeType, "application/xls"));
    try {/*from   ww  w  . j  a  v a 2  s  .co m*/
        Workbook wb = new HSSFWorkbook();
        Sheet sheet = wb.createSheet("new sheet");

        CreationHelper createHelper = wb.getCreationHelper();
        // Create a row and put some cells in it. Rows are 0 based.
        Row row = sheet.createRow((short) 0);

        // Or do it on one line.
        CustomerBean bean = (CustomerBean) model;
        row.createCell(1).setCellValue(bean.getCustomerNo());
        row.createCell(2).setCellValue(createHelper.createRichTextString(bean.getFirstName()));
        row.createCell(3).setCellValue(createHelper.createRichTextString(bean.getLastName()));
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
        Cell cell = row.createCell(4);
        cell.setCellValue(bean.getBirthDate());
        cell.setCellStyle(cellStyle);
        row.createCell(5).setCellValue(bean.getMailingAddress());
        row.createCell(6).setCellValue(bean.getMarried());
        row.createCell(7).setCellValue(bean.getNumberOfKids());
        row.createCell(8).setCellValue(bean.getFavouriteQuote());
        row.createCell(9).setCellValue(bean.getEmail());
        row.createCell(10).setCellValue(bean.getLoyaltyPoints());

        wb.write(outputStream);
        outputStream.close();
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}

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

License:Open Source License

public CellStyle getInfoStyle(Workbook wb, boolean bold) {
    Font font = getBoldFont(wb, (short) 11);
    if (!bold)/* www.  java2s.c o  m*/
        font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
    CellStyle style = wb.createCellStyle();
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFont(font);
    style.setIndention((short) 1);
    style.setWrapText(true);
    return style;
}

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

License:Open Source License

public CellStyle createBorderedStyle(Workbook wb) {
    CellStyle style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    return style;
}