List of usage examples for org.apache.poi.ss.usermodel Sheet getFooter
Footer getFooter();
Note that XSSF offers more kinds of document footers than HSSF does.
From source file:egovframework.rte.fdl.excel.EgovExcelServiceTest.java
License:Apache License
/** * [Flow #-4] ? : ? ? ?(Header, Footer)? *//*from www. j ava 2 s.c om*/ @Test public void testModifyDocAttribute() throws Exception { try { LOGGER.debug("testModifyDocAttribute start...."); StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testModifyDocAttribute.xls"); if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); LOGGER.debug("Delete file....{}", sb.toString()); } Workbook wbTmp = new HSSFWorkbook(); wbTmp.createSheet(); // ? ? excelService.createWorkbook(wbTmp, sb.toString()); // ? Workbook wb = excelService.loadWorkbook(sb.toString()); LOGGER.debug("testModifyCellContents after loadWorkbook...."); Sheet sheet = wb.createSheet("doc test sheet"); Row row = sheet.createRow(1); Cell cell = row.createCell(1); cell.setCellValue(new HSSFRichTextString("Header/Footer Test")); // Header Header header = sheet.getHeader(); header.setCenter("Center Header"); header.setLeft("Left Header"); header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") + HSSFHeader.fontSize((short) 16) + "Right Stencil-Normal Italic font and size 16"); // Footer Footer footer = sheet.getFooter(); footer.setCenter(HSSFHeader.font("Fixedsys", "Normal") + HSSFHeader.fontSize((short) 12) + "- 1 -"); LOGGER.debug("Style is ...{}", HSSFHeader.font("Fixedsys", "Normal"), HSSFHeader.fontSize((short) 12) + "- 1 -"); footer.setLeft("Left Footer"); footer.setRight("Right Footer"); // ? FileOutputStream out = new FileOutputStream(sb.toString()); wb.write(out); out.close(); assertTrue(EgovFileUtil.isExistsFile(sb.toString())); ////////////////////////////////////////////////////////////////////////// // ? Workbook wbT = excelService.loadWorkbook(sb.toString()); Sheet sheetT = wbT.getSheet("doc test sheet"); Header headerT = sheetT.getHeader(); assertEquals("Center Header", headerT.getCenter()); assertEquals("Left Header", headerT.getLeft()); assertEquals(HSSFHeader.font("Stencil-Normal", "Italic") + HSSFHeader.fontSize((short) 16) + "Right Stencil-Normal Italic font and size 16", headerT.getRight()); Footer footerT = sheetT.getFooter(); assertEquals("Right Footer", footerT.getRight()); assertEquals("Left Footer", footerT.getLeft()); assertEquals(HSSFHeader.font("Fixedsys", "Normal") + HSSFHeader.fontSize((short) 12) + "- 1 -", footerT.getCenter()); } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testModifyDocAttribute end...."); } }
From source file:egovframework.rte.fdl.excel.EgovExcelXSSFServiceTest.java
License:Apache License
/** * [Flow #-4] ? : ? ? ?(Header, Footer)? */// w w w.j a v a 2 s . co m @Test public void testModifyDocAttribute() throws Exception { try { LOGGER.debug("testModifyDocAttribute start...."); StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testModifyDocAttribute.xlsx"); if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); LOGGER.debug("Delete file....{}", sb.toString()); } Workbook wbTmp = new XSSFWorkbook(); wbTmp.createSheet(); // ? ? excelService.createWorkbook(wbTmp, sb.toString()); // ? Workbook wb = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook()); LOGGER.debug("testModifyCellContents after loadWorkbook...."); Sheet sheet = wb.createSheet("doc test sheet"); Row row = sheet.createRow(1); Cell cell = row.createCell(1); cell.setCellValue(new XSSFRichTextString("Header/Footer Test")); // Header Header header = sheet.getHeader(); header.setCenter("Center Header"); header.setLeft("Left Header"); header.setRight(XSSFOddHeader.stripFields("&IRight Stencil-Normal Italic font and size 16")); // Footer Footer footer = (XSSFOddFooter) sheet.getFooter(); footer.setCenter(XSSFOddHeader.stripFields("Fixedsys")); LOGGER.debug("Style is ... {}", XSSFOddHeader.stripFields("Fixedsys")); footer.setLeft("Left Footer"); footer.setRight("Right Footer"); // ? FileOutputStream out = new FileOutputStream(sb.toString()); wb.write(out); out.close(); assertTrue(EgovFileUtil.isExistsFile(sb.toString())); ////////////////////////////////////////////////////////////////////////// // ? Workbook wbT = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook()); Sheet sheetT = wbT.getSheet("doc test sheet"); Header headerT = sheetT.getHeader(); assertEquals("Center Header", headerT.getCenter()); assertEquals("Left Header", headerT.getLeft()); assertEquals(XSSFOddHeader.stripFields("Right Stencil-Normal Italic font and size 16"), headerT.getRight()); Footer footerT = sheetT.getFooter(); assertEquals("Right Footer", footerT.getRight()); assertEquals("Left Footer", footerT.getLeft()); assertEquals(XSSFOddHeader.stripFields("Fixedsys"), footerT.getCenter()); } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testModifyDocAttribute end...."); } }
From source file:om.edu.squ.squportal.portlet.tsurvey.dao.excel.TeachingSurveyExcelImpl.java
License:Open Source License
/** * //from w ww . j av a 2s . co m * method name : getExcelSurveyReport * @param object * @param response * @param params * @param locale * @return * @throws DocumentException * @throws IOException * TeachingSurveyExcelImpl * return type : OutputStream * * purpose : Get Streaming excel object for valid/invalid survey report * * Date : Mar 16, 2016 1:23:57 PM */ public OutputStream getExcelSurveyReport(String templateName, Object object, ResourceResponse response, Map<String, String> params, Locale locale) throws DocumentException, IOException { int colHead = 0; int rowNum = 0; String paramStaffRole = params.get(Constants.CONST_ROLE_STAFF); String paramTypeSurvey = params.get(Constants.CONST_PARAM_TYPE_SURVEY); String paramSemesterCode = params.get(Constants.CONST_PARAM_SEMESTER_CODE); String titleRegion = null; Workbook workbook = new HSSFWorkbook(); CreationHelper creationHelper = workbook.getCreationHelper(); Map<String, CellStyle> styles = createStyles(workbook); Sheet sheet = null; Cell cellSH = null; List<ReportSummary> reportSummaries = (List<ReportSummary>) object; if (templateName.equals(Constants.CONST_VALID_SURVEY_REPORT)) { sheet = workbook.createSheet( UtilProperty.getMessage("prop.course.teaching.survey.report.survey.valid", null, locale)); } if (templateName.equals(Constants.CONST_INVALID_SURVEY_REPORT)) { sheet = workbook.createSheet( UtilProperty.getMessage("prop.course.teaching.survey.report.survey.invalid", null, locale)); } sheet.getPrintSetup().setLandscape(true); sheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); /** Header Footer **/ Footer footer = sheet.getFooter(); Header header = sheet.getHeader(); footer.setRight("Page &P of &N"); footer.setLeft("&D"); header.setLeft(UtilProperty.getMessage("prop.course.teaching.survey.heading", null, locale)); header.setCenter(UtilProperty.getMessage("prop.course.teaching.survey.title", null, locale)); header.setRight(paramTypeSurvey + " - " + paramSemesterCode); sheet.setRepeatingRows(CellRangeAddress.valueOf("2:2")); sheet.setDisplayGridlines(true); sheet.setPrintGridlines(true); /** Title **/ Row titleRow = sheet.createRow(rowNum); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue(paramTypeSurvey + " - " + paramSemesterCode); titleCell.setCellStyle(styles.get(TITLE)); ++rowNum; titleRegion = "$A$" + rowNum + ":$O$" + rowNum; sheet.addMergedRegion(CellRangeAddress.valueOf(titleRegion)); /** Header Row **/ Row rowSubHeader = sheet.createRow(rowNum++); if (!paramStaffRole.equals(Constants.CONST_ROLE_STAFF_HOD) && !templateName.equals(Constants.CONST_INVALID_SURVEY_REPORT)) { cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.rank.university", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.rank.college", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.rank.department", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); } cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.instructor.id", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.instructor.name", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.college", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); if (!paramStaffRole.equals(Constants.CONST_ROLE_STAFF_HOD)) { cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.department", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); } cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.course.code", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.section", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.student.registered", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.response.number", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.analysis.mean", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.analysis.response.percentage", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.analysis.mean", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.analysis.response.percentage", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); /** Report details **/ for (ReportSummary reportSummary : reportSummaries) { int colNum = 0; Row row = sheet.createRow((short) rowNum); if (!paramStaffRole.equals(Constants.CONST_ROLE_STAFF_HOD) && !templateName.equals(Constants.CONST_INVALID_SURVEY_REPORT)) { row.createCell(colNum++).setCellValue(reportSummary.getUniversityRank()); row.createCell(colNum++).setCellValue(reportSummary.getCollegeRank()); row.createCell(colNum++).setCellValue(reportSummary.getDepartmentRank()); } row.createCell(colNum++).setCellValue(Double.parseDouble(reportSummary.getEmpNumber())); row.createCell(colNum++).setCellValue(creationHelper.createRichTextString(reportSummary.getEmpName())); row.createCell(colNum++) .setCellValue(creationHelper.createRichTextString(reportSummary.getCollegeCode())); if (!paramStaffRole.equals(Constants.CONST_ROLE_STAFF_HOD)) { row.createCell(colNum++) .setCellValue(creationHelper.createRichTextString(reportSummary.getDepartmentName())); } row.createCell(colNum++).setCellValue(reportSummary.getCourseCode()); row.createCell(colNum++).setCellValue(Integer.parseInt(reportSummary.getSectionNo())); row.createCell(colNum++).setCellValue(reportSummary.getRegisteredStudent()); Cell cellStudentNoResponse = row.createCell(colNum++); cellStudentNoResponse.setCellValue(reportSummary.getStudentNoResponse()); cellStudentNoResponse.setCellStyle(styles.get(FORMULA_1)); Cell cellTeachingMean = row.createCell(colNum++); cellTeachingMean.setCellValue(reportSummary.getTeachingMean()); cellTeachingMean.setCellStyle(styles.get(FORMULA_1)); Cell cellTeachingPercentageFavor = row.createCell(colNum++); cellTeachingPercentageFavor.setCellValue(reportSummary.getTeachingPercentageFavor()); cellTeachingPercentageFavor.setCellStyle(styles.get(FORMULA_1)); Cell cellQuestionMean = row.createCell(colNum++); cellQuestionMean.setCellValue(reportSummary.getQuestionMean()); cellQuestionMean.setCellStyle(styles.get(FORMULA_1)); Cell cellQuestionPercentageFavor = row.createCell(colNum++); cellQuestionPercentageFavor.setCellValue(reportSummary.getQuestionPercentageFavor()); cellQuestionPercentageFavor.setCellStyle(styles.get(FORMULA_1)); rowNum++; } response.setContentType("application/vnd.ms-excel"); OutputStream outputStream = response.getPortletOutputStream(); workbook.write(outputStream); outputStream.flush(); outputStream.close(); return null; }
From source file:om.edu.squ.squportal.portlet.tsurvey.dao.excel.TeachingSurveyExcelImpl.java
License:Open Source License
/** * // ww w. j a v a 2 s . c o m * method name : getExcelCollegeCoursesAsstDean * @param templateName * @param object * @param response * @param params * @param locale * @return * @throws DocumentException * @throws IOException * TeachingSurveyExcelImpl * return type : OutputStream * * purpose : * * Date : Jun 7, 2016 11:49:24 AM */ public OutputStream getExcelCollegeCoursesAsstDean(String templateName, Object object, ResourceResponse response, Map<String, String> params, Locale locale) throws DocumentException, IOException { int colHead = 0; int rowNum = 0; String paramTypeSurvey = params.get(Constants.CONST_PARAM_TYPE_SURVEY); String titleRegion = null; List<StudentResponse> studentResponses = (List<StudentResponse>) object; Workbook workbook = new HSSFWorkbook(); CreationHelper creationHelper = workbook.getCreationHelper(); Map<String, CellStyle> styles = createStyles(workbook); Sheet sheet = null; Cell cellSH = null; sheet = workbook .createSheet(UtilProperty.getMessage("prop.course.teaching.survey.courses.list", null, locale)); sheet.getPrintSetup().setLandscape(true); sheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); /** Header Footer **/ Footer footer = sheet.getFooter(); Header header = sheet.getHeader(); footer.setRight("Page &P of &N"); footer.setLeft("&D"); header.setLeft(UtilProperty.getMessage("prop.course.teaching.survey.heading", null, locale)); header.setCenter(UtilProperty.getMessage("prop.course.teaching.survey.title", null, locale)); header.setRight(paramTypeSurvey); sheet.setRepeatingRows(CellRangeAddress.valueOf("2:2")); sheet.setDisplayGridlines(true); sheet.setPrintGridlines(true); /** Title **/ Row titleRow = sheet.createRow(rowNum); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue(paramTypeSurvey); titleCell.setCellStyle(styles.get(TITLE)); ++rowNum; titleRegion = "$A$" + rowNum + ":$O$" + rowNum; sheet.addMergedRegion(CellRangeAddress.valueOf(titleRegion)); /** Header Row **/ Row rowSubHeader = sheet.createRow(rowNum++); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.analysis.department", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper .createRichTextString(UtilProperty.getMessage("prop.course.teaching.survey.course", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.section", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.committee.member.number", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.committee.member.name", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.seats.taken", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.response.students", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.include.exclude", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); /** Report details **/ for (StudentResponse studentResponse : studentResponses) { int colNum = 0; Row row = sheet.createRow((short) rowNum); row.createCell(colNum++) .setCellValue(creationHelper.createRichTextString(studentResponse.getDepartmentName())); row.createCell(colNum++) .setCellValue(creationHelper.createRichTextString(studentResponse.getCourseCode())); row.createCell(colNum++).setCellValue(Integer.parseInt(studentResponse.getSectionNo())); row.createCell(colNum++) .setCellValue(creationHelper.createRichTextString(studentResponse.getEmpNumber())); row.createCell(colNum++) .setCellValue(creationHelper.createRichTextString(studentResponse.getEmpName())); row.createCell(colNum++).setCellValue(studentResponse.getSeatsTaken()); row.createCell(colNum++).setCellValue(studentResponse.getStudentResponse()); row.createCell(colNum++) .setCellValue(creationHelper.createRichTextString(studentResponse.getIncludeExclude())); rowNum++; } response.setContentType("application/vnd.ms-excel"); OutputStream outputStream = response.getPortletOutputStream(); workbook.write(outputStream); outputStream.flush(); outputStream.close(); return null; }
From source file:org.alanwilliamson.openbd.plugin.spreadsheet.functions.SpreadsheetSetFooter.java
License:Open Source License
public cfData execute(cfSession _session, List<cfData> parameters) throws cfmRunTimeException { cfSpreadSheetData spreadsheet = null; String left, center, right;//w ww. j a v a 2 s. c o m /* * Collect up the parameters */ spreadsheet = (cfSpreadSheetData) parameters.get(3); left = parameters.get(2).getString(); center = parameters.get(1).getString(); right = parameters.get(0).getString(); /* * Perform the insertion */ Sheet sheet = spreadsheet.getActiveSheet(); Footer footer = sheet.getFooter(); footer.setCenter(center); footer.setLeft(left); footer.setRight(right); return cfBooleanData.TRUE; }
From source file:org.bbreak.excella.reports.ReportsTestUtil.java
License:Open Source License
/** * //from w w w .j a v a2 s .c o m * * @param expected * @param actual * @param isActCopyOfExp ??????true * @throws ReportsCheckException */ public static void checkSheet(Sheet expected, Sheet actual, boolean isActCopyOfExp) throws ReportsCheckException { List<CheckMessage> errors = new ArrayList<CheckMessage>(); Workbook expectedWorkbook = expected.getWorkbook(); Workbook actualWorkbook = actual.getWorkbook(); if (log.isDebugEnabled()) { log.debug("[" + actualWorkbook.getSheetName(actualWorkbook.getSheetIndex(actual)) + "] check start!"); } // ---------------------- // ???? // ---------------------- // ?? String eSheetName = expectedWorkbook.getSheetName(expectedWorkbook.getSheetIndex(expected)); String aSheetName = actualWorkbook.getSheetName(actualWorkbook.getSheetIndex(actual)); if (!isActCopyOfExp) { if (!eSheetName.equals(aSheetName)) { errors.add(new CheckMessage("??", eSheetName, aSheetName)); } } // ? String ePrintSetupString = getPrintSetupString(expected.getPrintSetup()); String aPrintSetupString = getPrintSetupString(actual.getPrintSetup()); if (!ePrintSetupString.equals(aPrintSetupString)) { errors.add(new CheckMessage("?", ePrintSetupString, aPrintSetupString)); } // ? String eHeaderString = getHeaderString(expected.getHeader()); String aHeaderString = getHeaderString(actual.getHeader()); if (!eHeaderString.equals(aHeaderString)) { errors.add(new CheckMessage("", eHeaderString, aHeaderString)); } String eFooterString = getFooterString(expected.getFooter()); String aFooterString = getFooterString(actual.getFooter()); if (!eFooterString.equals(aFooterString)) { errors.add(new CheckMessage("", eFooterString, aFooterString)); } // String eBreaksString = getBreaksString(expected); String aBreaksString = getBreaksString(actual); log.debug(eBreaksString + "/" + aBreaksString); if (!eBreaksString.equals(aBreaksString)) { errors.add(new CheckMessage("", eBreaksString, aBreaksString)); } // ? String expectedPrintArea = expectedWorkbook.getPrintArea(expectedWorkbook.getSheetIndex(expected)); String actualPrintArea = actualWorkbook.getPrintArea(actualWorkbook.getSheetIndex(actual)); if (expectedPrintArea != null || actualPrintArea != null) { // ????????Null????????????? // if ( expectedPrintArea == null || actualPrintArea == null || !equalPrintArea( expectedPrintArea, actualPrintArea, isActCopyOfExp)) { // errors.add( new CheckMessage( "?", expectedPrintArea, actualPrintArea)); // } if (!isActCopyOfExp) { if (expectedPrintArea == null || actualPrintArea == null || !expectedPrintArea.equals(actualPrintArea)) { errors.add(new CheckMessage("?", expectedPrintArea, actualPrintArea)); } } } // (?) String ePaneInformationString = getPaneInformationString(expected.getPaneInformation()); String aPaneInformationString = getPaneInformationString(actual.getPaneInformation()); if (!ePaneInformationString.equals(aPaneInformationString)) { errors.add(new CheckMessage("(?)", expectedPrintArea, actualPrintArea)); } // ?????? // ????? // ????? // // if (expected.isDisplayGridlines() ^ actual.isDisplayGridlines()) { errors.add(new CheckMessage("", String.valueOf(expected.isDisplayGridlines()), String.valueOf(actual.isDisplayGridlines()))); } // ? if (expected.isDisplayRowColHeadings() ^ actual.isDisplayRowColHeadings()) { errors.add(new CheckMessage("?", String.valueOf(expected.isDisplayRowColHeadings()), String.valueOf(actual.isDisplayRowColHeadings()))); } // ? if (expected.isDisplayFormulas() ^ actual.isDisplayFormulas()) { errors.add(new CheckMessage("?", String.valueOf(expected.isDisplayFormulas()), String.valueOf(actual.isDisplayFormulas()))); } // ?? if (expected.getNumMergedRegions() != actual.getNumMergedRegions()) { errors.add(new CheckMessage("??", String.valueOf(expected.getNumMergedRegions()), String.valueOf(actual.getNumMergedRegions()))); } for (int i = 0; i < actual.getNumMergedRegions(); i++) { CellRangeAddress actualAddress = null; if (expected instanceof HSSFSheet) { actualAddress = ((HSSFSheet) actual).getMergedRegion(i); } else if (expected instanceof XSSFSheet) { actualAddress = ((XSSFSheet) actual).getMergedRegion(i); } StringBuffer expectedAdressBuffer = new StringBuffer(); boolean equalAddress = false; for (int j = 0; j < expected.getNumMergedRegions(); j++) { CellRangeAddress expectedAddress = null; if (expected instanceof HSSFSheet) { expectedAddress = ((HSSFSheet) expected).getMergedRegion(j); } else if (expected instanceof XSSFSheet) { expectedAddress = ((XSSFSheet) expected).getMergedRegion(j); } if (expectedAddress.toString().equals(actualAddress.toString())) { equalAddress = true; break; } CellReference crA = new CellReference(expectedAddress.getFirstRow(), expectedAddress.getFirstColumn()); CellReference crB = new CellReference(expectedAddress.getLastRow(), expectedAddress.getLastColumn()); expectedAdressBuffer.append(" [" + crA.formatAsString() + ":" + crB.formatAsString() + "]"); } if (!equalAddress) { errors.add(new CheckMessage("??", expectedAdressBuffer.toString(), actualAddress.toString())); } } int maxColumnNum = -1; if (expected instanceof HSSFSheet) { maxColumnNum = HSSF_MAX_COLUMN_NUMBER; } else if (expected instanceof XSSFSheet) { maxColumnNum = XSSF_MAX_COLUMN_NUMBER; } for (int i = 0; i < maxColumnNum; i++) { try { // checkCellStyle(expected.getWorkbook(), expected.getColumnStyle(i), actual.getWorkbook(), actual.getColumnStyle(i)); } catch (ReportsCheckException e) { CheckMessage checkMessage = e.getCheckMessages().iterator().next(); checkMessage.setMessage("[" + i + "]" + checkMessage.getMessage()); errors.add(checkMessage); } // if (expected.getColumnWidth(i) != actual.getColumnWidth(i)) { errors.add(new CheckMessage("[" + i + "]", String.valueOf(expected.getColumnWidth(i)), String.valueOf(actual.getColumnWidth(i)))); } } // ??? if (expected.getLastRowNum() != actual.getLastRowNum()) { // ?????? if (expected.getLastRowNum() < actual.getLastRowNum()) { int lastRowIndex = -1; if (expected instanceof HSSFSheet) { lastRowIndex = 0; } Iterator<Row> rowIterator = actual.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); // ????? Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getCellTypeEnum() != CellType.BLANK) { lastRowIndex = row.getRowNum(); break; } } } if (expected.getLastRowNum() != lastRowIndex) { errors.add(new CheckMessage("", String.valueOf(expected.getLastRowNum()), String.valueOf(lastRowIndex))); } } else { errors.add(new CheckMessage("", String.valueOf(expected.getLastRowNum()), String.valueOf(actual.getLastRowNum()))); } } if (errors.isEmpty()) { for (int i = 0; i <= expected.getLastRowNum(); i++) { try { checkRow(expected.getRow(i), actual.getRow(i)); } catch (ReportsCheckException e) { errors.addAll(e.getCheckMessages()); } } } if (!errors.isEmpty()) { if (log.isErrorEnabled()) { for (CheckMessage message : errors) { log.error("?[" + message.getMessage() + "]"); log.error(":" + message.getExpected()); log.error(":" + message.getActual()); } } throw new ReportsCheckException(errors); } if (log.isDebugEnabled()) { log.debug("[" + actualWorkbook.getSheetName(actualWorkbook.getSheetIndex(actual)) + "] check end."); } }
From source file:org.pentaho.reporting.engine.classic.core.modules.output.table.xls.helper.ExcelPrinterBase.java
License:Open Source License
protected void configureSheetProperties(final Sheet sheet, final SheetPropertySource excelTableContentProducer) { final String pageHeaderCenter = excelTableContentProducer.getPageHeaderCenter(); final String pageFooterCenter = excelTableContentProducer.getPageFooterCenter(); final String pageHeaderLeft = excelTableContentProducer.getPageHeaderLeft(); final String pageFooterLeft = excelTableContentProducer.getPageFooterLeft(); final String pageHeaderRight = excelTableContentProducer.getPageHeaderRight(); final String pageFooterRight = excelTableContentProducer.getPageFooterRight(); if (isHeaderFooterValid(pageHeaderLeft, pageHeaderCenter, pageHeaderRight)) { if (pageHeaderLeft != null) { sheet.getHeader().setLeft(pageHeaderLeft); }//from ww w .ja v a 2 s .co m if (pageHeaderCenter != null) { sheet.getHeader().setCenter(pageHeaderCenter); } if (pageHeaderRight != null) { sheet.getHeader().setRight(pageHeaderRight); } } else { logger.warn( "Page-Header exceeds the maximum length of 255 characters. No page-header will be added to the sheet."); } if (isHeaderFooterValid(pageFooterLeft, pageFooterCenter, pageFooterRight)) { if (pageFooterCenter != null) { sheet.getFooter().setCenter(pageFooterCenter); } if (pageFooterLeft != null) { sheet.getFooter().setLeft(pageFooterLeft); } if (pageFooterRight != null) { sheet.getFooter().setRight(pageFooterRight); } } else { logger.warn( "Page-Footer exceeds the maximum length of 255 characters. No page-footer will be added to the sheet."); } int sheetFreezeTop = excelTableContentProducer.getFreezeTop(); int sheetFreezeLeft = excelTableContentProducer.getFreezeLeft(); if (sheetFreezeTop > 0 || sheetFreezeLeft > 0) { sheet.createFreezePane(sheetFreezeLeft, sheetFreezeTop); } }
From source file:packtest.HeadersAndFooters.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); Sheet sheet = wb.createSheet("first-header - format sheet"); sheet.createRow(0).createCell(0).setCellValue(123); //set page numbers in the footer Footer footer = sheet.getFooter(); //&P == current page number //&N == page numbers footer.setRight("Page &P of &N"); Header firstHeader = ((XSSFSheet) sheet).getFirstHeader(); //&F == workbook file name firstHeader.setLeft("&F ......... first header"); for (int i = 0; i < 100; i = i + 10) { sheet.createRow(i).createCell(0).setCellValue(123); }/*w ww . j a va 2 s . c om*/ XSSFSheet sheet2 = (XSSFSheet) wb.createSheet("odd header-even footer"); Header oddHeader = sheet2.getOddHeader(); //&B == bold //&E == double underline //&D == date oddHeader.setCenter("&B &E oddHeader &D "); Footer evenFooter = sheet2.getEvenFooter(); evenFooter.setRight("even footer &P"); sheet2.createRow(10).createCell(0).setCellValue("Second sheet with an oddHeader and an evenFooter"); for (int i = 0; i < 200; i = i + 10) { sheet2.createRow(i).createCell(0).setCellValue(123); } XSSFSheet sheet3 = (XSSFSheet) wb.createSheet("odd header- odd footer"); sheet3.createRow(10).createCell(0).setCellValue("Third sheet with oddHeader and oddFooter"); Header oddH = sheet3.getOddHeader(); //&C == centered oddH.setCenter("centered oddHeader"); oddH.setLeft("left "); oddH.setRight("right "); Footer oddF = sheet3.getOddFooter(); oddF.setLeft("Page &P"); oddF.setRight("Pages &N "); FileOutputStream fileOut = new FileOutputStream(Utils.getPath("headerFooter.xlsx")); wb.write(fileOut); fileOut.close(); }
From source file:poi.xssf.usermodel.examples.HeadersAndFooters.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); Sheet sheet = wb.createSheet("first-header - format sheet"); sheet.createRow(0).createCell(0).setCellValue(123); //set page numbers in the footer Footer footer = sheet.getFooter(); //&P == current page number //&N == page numbers footer.setRight("Page &P of &N"); Header firstHeader = ((XSSFSheet) sheet).getFirstHeader(); //&F == workbook file name firstHeader.setLeft("&F ......... first header"); for (int i = 0; i < 100; i = i + 10) { sheet.createRow(i).createCell(0).setCellValue(123); }//from w w w . ja v a 2 s. c o m XSSFSheet sheet2 = (XSSFSheet) wb.createSheet("odd header-even footer"); Header oddHeader = sheet2.getOddHeader(); //&B == bold //&E == double underline //&D == date oddHeader.setCenter("&B &E oddHeader &D "); Footer evenFooter = sheet2.getEvenFooter(); evenFooter.setRight("even footer &P"); sheet2.createRow(10).createCell(0).setCellValue("Second sheet with an oddHeader and an evenFooter"); for (int i = 0; i < 200; i = i + 10) { sheet2.createRow(i).createCell(0).setCellValue(123); } XSSFSheet sheet3 = (XSSFSheet) wb.createSheet("odd header- odd footer"); sheet3.createRow(10).createCell(0).setCellValue("Third sheet with oddHeader and oddFooter"); Header oddH = sheet3.getOddHeader(); //&C == centered oddH.setCenter("centered oddHeader"); oddH.setLeft("left "); oddH.setRight("right "); Footer oddF = sheet3.getOddFooter(); oddF.setLeft("Page &P"); oddF.setRight("Pages &N "); FileOutputStream fileOut = new FileOutputStream("headerFooter.xlsx"); wb.write(fileOut); fileOut.close(); }
From source file:uk.ac.liverpool.spreadsheet.ExcelFeatureAnalysis.java
License:Apache License
private static void analyseSheet(Sheet ss, Element s, Namespace n, ExcelFeatureAnalysis efa) { // generic part boolean costumFormatting = false; boolean formulae = false; boolean UDF = false; boolean hasComments = false; Set<String> udfs = new HashSet<String>(); FormulaEvaluator evaluator = ss.getWorkbook().getCreationHelper().createFormulaEvaluator(); s.setAttribute("name", ss.getSheetName()); s.setAttribute("firstRow", "" + ss.getFirstRowNum()); s.setAttribute("lastRow", "" + ss.getLastRowNum()); try {// w w w .j a v a 2 s . com s.setAttribute("forceFormulaRecalc", "" + ss.getForceFormulaRecalculation()); } catch (Throwable x) { //x.printStackTrace(); } // shapes in detail? Footer footer = ss.getFooter(); if (footer != null) { s.setAttribute("footer", "true"); } Header header = ss.getHeader(); if (header != null) { s.setAttribute("header", "true"); } PaneInformation paneInformation = ss.getPaneInformation(); if (paneInformation != null) { s.setAttribute("panels", "true"); } HSSFSheet hs = null; XSSFSheet xs = null; if (ss instanceof HSSFSheet) { hs = (HSSFSheet) ss; try { if (hs.getDrawingPatriarch() != null) { if (hs.getDrawingPatriarch().containsChart()) s.addContent(new Element("charts", sn)); if (hs.getDrawingPatriarch().countOfAllChildren() > 0) s.addContent(new Element("shapes", sn)); } } catch (Exception x) { x.printStackTrace(); } if (hs.getSheetConditionalFormatting().getNumConditionalFormattings() > 0) { s.setAttribute("conditionalFormatting", "true"); } } if (ss instanceof XSSFSheet) { xs = (XSSFSheet) ss; } Iterator<Row> rows = ss.rowIterator(); int firstColumn = (rows.hasNext() ? Integer.MAX_VALUE : 0); int endColumn = 0; while (rows.hasNext()) { Row row = rows.next(); short firstCell = row.getFirstCellNum(); if (firstCell >= 0) { firstColumn = Math.min(firstColumn, firstCell); endColumn = Math.max(endColumn, row.getLastCellNum()); } } s.setAttribute("firstColumn", "" + firstColumn); s.setAttribute("lastColumn", "" + endColumn); rows = ss.rowIterator(); while (rows.hasNext()) { Row row = rows.next(); for (Cell cell : row) if (cell != null) { try { if (!cell.getCellStyle().getDataFormatString().equals("GENERAL")) costumFormatting = true; } catch (Throwable t) { } if (cell.getCellComment() != null) hasComments = true; switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: // System.out.println(cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: // if (DateUtil.isCellDateFormatted(cell)) { // // System.out.println(cell.getDateCellValue()); // } else { // // System.out.println(cell.getNumericCellValue()); // } break; case Cell.CELL_TYPE_BOOLEAN: // System.out.println(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: // System.out.println(cell.getCellFormula()); formulae = true; if (!UDF) try { evaluator.evaluate(cell); } catch (Exception x) { if (x instanceof NotImplementedException) { Throwable e = x; //e.printStackTrace(); while (e != null) { for (StackTraceElement c : e.getStackTrace()) { if (c.getClassName().contains("UserDefinedFunction")) { UDF = true; System.out.println("UDF " + e.getMessage()); udfs.add(e.getMessage()); } } e = e.getCause(); } } } break; default: } } } if (costumFormatting) { Element cf = new Element("customisedFormatting", sn); s.addContent(cf); } if (formulae) { Element cf = new Element("formulae", sn); s.addContent(cf); } if (UDF) { Element cf = new Element("userDefinedFunctions", sn); for (String sss : udfs) cf.addContent(new Element("userDefinedFunction", sn).setAttribute("functionName", sss)); s.addContent(cf); } if (hasComments) { Element cf = new Element("cellComments", sn); s.addContent(cf); } }