List of usage examples for org.apache.poi.ss.usermodel Font setFontHeightInPoints
void setFontHeightInPoints(short height);
From source file:org.primefaces.component.export.ExcelXExporter.java
License:Open Source License
@Override protected void applyFacetOptions(Workbook wb, ExporterOptions options, CellStyle facetStyle) { Font facetFont = wb.createFont(); facetFont.setFontName("Arial"); if (options != null) { String facetFontStyle = options.getFacetFontStyle(); if (facetFontStyle != null) { if (facetFontStyle.equalsIgnoreCase("BOLD")) { facetFont.setBoldweight(Font.BOLDWEIGHT_BOLD); }/* w ww . j a va 2s. co m*/ if (facetFontStyle.equalsIgnoreCase("ITALIC")) { facetFont.setItalic(true); } } String facetBackground = options.getFacetBgColor(); if (facetBackground != null) { XSSFColor backgroundColor = new XSSFColor(Color.decode(facetBackground)); ((XSSFCellStyle) facetStyle).setFillForegroundColor(backgroundColor); facetStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); } String facetFontColor = options.getFacetFontColor(); if (facetFontColor != null) { XSSFColor facetColor = new XSSFColor(Color.decode(facetFontColor)); ((XSSFFont) facetFont).setColor(facetColor); } String facetFontSize = options.getFacetFontSize(); if (facetFontSize != null) { facetFont.setFontHeightInPoints(Short.valueOf(facetFontSize)); } } facetStyle.setFont(facetFont); }
From source file:org.primefaces.component.export.ExcelXExporter.java
License:Open Source License
@Override protected void applyCellOptions(Workbook wb, ExporterOptions options, CellStyle cellStyle) { Font cellFont = wb.createFont(); cellFont.setFontName("Arial"); if (options != null) { String cellFontColor = options.getCellFontColor(); if (cellFontColor != null) { XSSFColor cellColor = new XSSFColor(Color.decode(cellFontColor)); ((XSSFFont) cellFont).setColor(cellColor); }//w w w . ja v a 2 s .c om String cellFontSize = options.getCellFontSize(); if (cellFontSize != null) { cellFont.setFontHeightInPoints(Short.valueOf(cellFontSize)); } String cellFontStyle = options.getCellFontStyle(); if (cellFontStyle != null) { if (cellFontStyle.equalsIgnoreCase("BOLD")) { cellFont.setBoldweight(Font.BOLDWEIGHT_BOLD); } if (cellFontStyle.equalsIgnoreCase("ITALIC")) { cellFont.setItalic(true); } } } cellStyle.setFont(cellFont); }
From source file:org.primefaces.extensions.component.exporter.ExcelExporter.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 w ww . jav a2 s .c om*/ if (cellFontSize != null) { cellFont.setFontHeightInPoints((short) cellFontSize); } if (cellFontStyle.equalsIgnoreCase("BOLD")) { cellFont.setBoldweight(Font.BOLDWEIGHT_BOLD); } if (cellFontStyle.equalsIgnoreCase("ITALIC")) { cellFont.setItalic(true); } if (facetFontStyle.equalsIgnoreCase("BOLD")) { facetFont.setBoldweight(Font.BOLDWEIGHT_BOLD); } if (facetFontStyle.equalsIgnoreCase("ITALIC")) { facetFont.setItalic(true); } if (fontName != null) { cellFont.setFontName(fontName); facetFont.setFontName(fontName); } if (facetBackground != null) { XSSFColor backgroundColor = new XSSFColor(facetBackground); ((XSSFCellStyle) facetStyle).setFillForegroundColor(backgroundColor); ((XSSFCellStyle) facetStyleLeftAlign).setFillForegroundColor(backgroundColor); ((XSSFCellStyle) facetStyleCenterAlign).setFillForegroundColor(backgroundColor); ((XSSFCellStyle) facetStyleRightAlign).setFillForegroundColor(backgroundColor); facetStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); facetStyleLeftAlign.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); facetStyleCenterAlign.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); facetStyleRightAlign.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); } if (facetFontColor != null) { XSSFColor facetColor = new XSSFColor(facetFontColor); ((XSSFFont) facetFont).setColor(facetColor); } if (facetFontSize != null) { facetFont.setFontHeightInPoints((short) facetFontSize); } cellStyle.setFont(cellFont); cellStyleLeftAlign.setFont(cellFont); cellStyleCenterAlign.setFont(cellFont); cellStyleRightAlign.setFont(cellFont); facetStyle.setFont(facetFont); facetStyleLeftAlign.setFont(facetFont); facetStyleCenterAlign.setFont(facetFont); facetStyleRightAlign.setFont(facetFont); //facetStyle.setAlignment(CellStyle.ALIGN_CENTER); }
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 w ww . jav a 2 s.co m 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.riflemansd.businessprofit.excel.ExcelExampleFont.java
License:Open Source License
public static void main(String[] args) { // create a new file FileOutputStream out = null;//w w w .j a v a 2 s .c om 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.riflemansd.businessprofit.excel.MyExcelDocument.java
License:Open Source License
public void setHeader(int nsheet, int nrow, int ncolumn, String value) { org.apache.poi.ss.usermodel.Cell cell = getCell(nsheet, nrow, ncolumn); CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 12); style.setFont(font);/*from ww w . ja v a 2 s . c o m*/ cell.setCellValue(value); cell.setCellStyle(style); }
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 w w .j a v a2s . com*/ * @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 a v a2s. c o 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.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;//from w ww . j ava 2 s . com 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.sigmah.server.endpoint.export.Export.java
License:Open Source License
private void declareStyles() { dateStyle = book.createCellStyle();//w w w .ja v a 2 s . c om dateStyle.setDataFormat(creationHelper.createDataFormat().getFormat("m/d/yy")); coordStyle = book.createCellStyle(); coordStyle.setDataFormat(creationHelper.createDataFormat().getFormat("0.000000")); indicatorValueStyle = book.createCellStyle(); indicatorValueStyle.setDataFormat(creationHelper.createDataFormat().getFormat("#,##0")); Font headerFont = book.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); Font smallFont = book.createFont(); smallFont.setFontHeightInPoints((short) 8); headerStyle = book.createCellStyle(); headerStyle.setFont(headerFont); headerStyleCenter = book.createCellStyle(); headerStyleCenter.setFont(headerFont); headerStyleCenter.setAlignment(CellStyle.ALIGN_CENTER); headerStyleRight = book.createCellStyle(); headerStyleRight.setFont(headerFont); headerStyleRight.setAlignment(CellStyle.ALIGN_RIGHT); attribHeaderStyle = book.createCellStyle(); attribHeaderStyle.setFont(smallFont); attribHeaderStyle.setRotation((short) 45); attribHeaderStyle.setWrapText(true); indicatorHeaderStyle = book.createCellStyle(); indicatorHeaderStyle.setFont(smallFont); indicatorHeaderStyle.setWrapText(true); indicatorHeaderStyle.setAlignment(CellStyle.ALIGN_RIGHT); attribValueStyle = book.createCellStyle(); attribValueStyle.setFont(smallFont); }