List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet createFreezePane
@Override public void createFreezePane(int colSplit, int rowSplit)
From source file:org.metaeffekt.core.inventory.processor.writer.InventoryWriter.java
License:Apache License
private void writeArtifacts(Inventory inventory, HSSFWorkbook myWorkBook) { HSSFSheet mySheet = myWorkBook.createSheet("Artifact Inventory"); mySheet.createFreezePane(0, 1); mySheet.setDefaultColumnWidth(20);//from w w w. jav a2 s . c o m HSSFRow myRow = null; HSSFCell myCell = null; int rowNum = 0; myRow = mySheet.createRow(rowNum++); HSSFCellStyle headerStyle = createHeaderStyle(myWorkBook); int cellNum = 0; myCell = myRow.createCell(cellNum++); myCell.setCellStyle(headerStyle); myCell.setCellValue(new HSSFRichTextString("Id")); myCell = myRow.createCell(cellNum++); myCell.setCellStyle(headerStyle); myCell.setCellValue(new HSSFRichTextString("Checksum")); myCell = myRow.createCell(cellNum++); myCell.setCellStyle(headerStyle); myCell.setCellValue(new HSSFRichTextString("Component")); myCell = myRow.createCell(cellNum++); myCell.setCellStyle(headerStyle); myCell.setCellValue(new HSSFRichTextString("Group Id")); myCell = myRow.createCell(cellNum++); myCell.setCellStyle(headerStyle); myCell.setCellValue(new HSSFRichTextString("Version")); myCell = myRow.createCell(cellNum++); myCell.setCellStyle(headerStyle); myCell.setCellValue(new HSSFRichTextString("Latest Version")); myCell = myRow.createCell(cellNum++); myCell.setCellStyle(headerStyle); myCell.setCellValue(new HSSFRichTextString("License")); myCell = myRow.createCell(cellNum++); myCell.setCellStyle(headerStyle); myCell.setCellValue(new HSSFRichTextString("Security Relevance")); myCell = myRow.createCell(cellNum++); myCell.setCellStyle(headerStyle); myCell.setCellValue(new HSSFRichTextString("Security Category")); myCell = myRow.createCell(cellNum++); myCell.setCellStyle(headerStyle); myCell.setCellValue(new HSSFRichTextString("Vulnerability")); myCell = myRow.createCell(cellNum++); myCell.setCellStyle(headerStyle); myCell.setCellValue(new HSSFRichTextString("Classification")); myCell = myRow.createCell(cellNum++); myCell.setCellStyle(headerStyle); myCell.setCellValue(new HSSFRichTextString("Comment")); myCell = myRow.createCell(cellNum++); myCell.setCellStyle(headerStyle); myCell.setCellValue(new HSSFRichTextString("URL")); myCell = myRow.createCell(cellNum++); myCell.setCellStyle(headerStyle); myCell.setCellValue(new HSSFRichTextString("Projects")); myCell = myRow.createCell(cellNum++); myCell.setCellStyle(headerStyle); myCell.setCellValue(new HSSFRichTextString("Verified")); // create columns for key / value map content Set<String> attributes = new HashSet<>(); for (Artifact artifact : inventory.getArtifacts()) { attributes.addAll(artifact.getAttributes()); } List<String> ordered = new ArrayList<>(attributes); Collections.sort(ordered); for (String key : ordered) { myCell = myRow.createCell(cellNum++); myCell.setCellStyle(headerStyle); myCell.setCellValue(new HSSFRichTextString(key)); } int numCol = cellNum; for (Artifact artifact : inventory.getArtifacts()) { myRow = mySheet.createRow(rowNum++); cellNum = 0; myCell = myRow.createCell(cellNum++); myCell.setCellValue(new HSSFRichTextString(artifact.getId())); myCell = myRow.createCell(cellNum++); myCell.setCellValue(new HSSFRichTextString(artifact.getChecksum())); myCell = myRow.createCell(cellNum++); myCell.setCellValue(new HSSFRichTextString(artifact.getComponent())); myCell = myRow.createCell(cellNum++); myCell.setCellValue(new HSSFRichTextString(artifact.getGroupId())); myCell = myRow.createCell(cellNum++); myCell.setCellValue(new HSSFRichTextString(artifact.getVersion())); myCell = myRow.createCell(cellNum++); myCell.setCellValue(new HSSFRichTextString(artifact.getLatestAvailableVersion())); myCell = myRow.createCell(cellNum++); myCell.setCellValue(new HSSFRichTextString(artifact.getLicense())); myCell = myRow.createCell(cellNum++); myCell.setCellValue(new HSSFRichTextString(artifact.isSecurityRelevant() ? "X" : "")); myCell = myRow.createCell(cellNum++); myCell.setCellValue(new HSSFRichTextString(artifact.getSecurityCategory())); myCell = myRow.createCell(cellNum++); myCell.setCellValue(new HSSFRichTextString(artifact.getVulnerability())); myCell = myRow.createCell(cellNum++); myCell.setCellValue(new HSSFRichTextString(artifact.getClassification())); myCell = myRow.createCell(cellNum++); myCell.setCellValue(new HSSFRichTextString(artifact.getComment())); myCell = myRow.createCell(cellNum++); myCell.setCellValue(new HSSFRichTextString(artifact.getUrl())); myCell = myRow.createCell(cellNum++); String projects = artifact.getProjects().toString(); projects = projects.substring(1, projects.length() - 1); myCell.setCellValue(new HSSFRichTextString(projects)); myCell = myRow.createCell(cellNum++); myCell.setCellValue(new HSSFRichTextString(artifact.isVerified() ? "X" : "")); for (String key : ordered) { myCell = myRow.createCell(cellNum++); myCell.setCellValue(new HSSFRichTextString(artifact.get(key))); } } /** adjust with of cells for (int i = 0; i <= numCol; i++) { Integer width = (Integer) inventory.getContextMap().get("artifacts.column[" + i + "].width"); if (width != null) { mySheet.setColumnWidth(i, Math.min(width, 255)); } }*/ mySheet.setAutoFilter(new CellRangeAddress(0, 65000, 0, numCol - 1)); }
From source file:org.metaeffekt.core.inventory.processor.writer.InventoryWriter.java
License:Apache License
private void writeNotices(Inventory inventory, HSSFWorkbook myWorkBook) { HSSFSheet mySheet = myWorkBook.createSheet("License Notices"); mySheet.createFreezePane(0, 1); mySheet.setDefaultColumnWidth(80);//from w w w .j a v a 2 s. co m HSSFRow myRow = null; HSSFCell myCell = null; int rowNum = 0; myRow = mySheet.createRow(rowNum++); HSSFCellStyle headerStyle = createHeaderStyle(myWorkBook); int cellNum = 0; myCell = myRow.createCell(cellNum++); myCell.setCellStyle(headerStyle); myCell.setCellValue(new HSSFRichTextString("Component")); myCell = myRow.createCell(cellNum++); myCell.setCellStyle(headerStyle); myCell.setCellValue(new HSSFRichTextString("Version")); myCell = myRow.createCell(cellNum++); myCell.setCellStyle(headerStyle); myCell.setCellValue(new HSSFRichTextString("License")); myCell = myRow.createCell(cellNum++); myCell.setCellStyle(headerStyle); myCell.setCellValue(new HSSFRichTextString("License in Effect")); myCell = myRow.createCell(cellNum++); myCell.setCellStyle(headerStyle); myCell.setCellValue(new HSSFRichTextString("Source Category")); myCell = myRow.createCell(cellNum++); myCell.setCellStyle(headerStyle); myCell.setCellValue(new HSSFRichTextString("License Notice")); myCell = myRow.createCell(cellNum++); myCell.setCellStyle(headerStyle); myCell.setCellValue(new HSSFRichTextString("Comment")); // create columns for key / value map content Set<String> attributes = new HashSet<>(); for (LicenseMetaData licenseMetaData : inventory.getLicenseMetaData()) { attributes.addAll(licenseMetaData.getAttributes()); } List<String> ordered = new ArrayList<>(attributes); Collections.sort(ordered); for (String key : ordered) { myCell = myRow.createCell(cellNum++); myCell.setCellStyle(headerStyle); myCell.setCellValue(new HSSFRichTextString(key)); } int numCol = cellNum; for (LicenseMetaData licenseMetaData : inventory.getLicenseMetaData()) { myRow = mySheet.createRow(rowNum++); cellNum = 0; myCell = myRow.createCell(cellNum++); myCell.setCellValue(new HSSFRichTextString(licenseMetaData.getComponent())); myCell = myRow.createCell(cellNum++); myCell.setCellValue(new HSSFRichTextString(licenseMetaData.getVersion())); myCell = myRow.createCell(cellNum++); myCell.setCellValue(new HSSFRichTextString(licenseMetaData.getLicense())); myCell = myRow.createCell(cellNum++); String licenseInEffect = licenseMetaData.getLicenseInEffect(); if (StringUtils.isEmpty(licenseInEffect)) { licenseInEffect = licenseMetaData.getLicense(); } myCell.setCellValue(new HSSFRichTextString(licenseInEffect)); myCell = myRow.createCell(cellNum++); myCell.setCellValue(new HSSFRichTextString(licenseMetaData.getSourceCategory())); myCell = myRow.createCell(cellNum++); myCell.setCellValue(new HSSFRichTextString(licenseMetaData.getNotice())); myCell = myRow.createCell(cellNum++); myCell.setCellValue(new HSSFRichTextString(licenseMetaData.getComment())); for (String key : ordered) { myCell = myRow.createCell(cellNum++); myCell.setCellValue(new HSSFRichTextString(licenseMetaData.get(key))); } } /** for (int i = 0; i < 6; i++) { Integer width = (Integer) inventory.getContextMap().get("obligations.column[" + i + "].width"); if (width != null) { mySheet.setColumnWidth(i, Math.min(width, 255)); } } */ mySheet.setAutoFilter(new CellRangeAddress(0, 65000, 0, numCol - 1)); }
From source file:org.sigmah.server.endpoint.export.Export.java
License:Open Source License
public void export(ActivityDTO activity) { HSSFSheet sheet = book.createSheet(composeUniqueSheetName(activity)); sheet.createFreezePane(4, 2); // initConditionalFormatting(sheet); createHeaders(activity, sheet);/* w w w . jav a2 s. c om*/ createDataRows(activity, sheet); }
From source file:org.sigmah.server.endpoint.export.sigmah.spreadsheet.template.BaseSynthesisExcelTemplate.java
License:Open Source License
public BaseSynthesisExcelTemplate(final BaseSynthesisData data, final HSSFWorkbook wb, final Class clazz) throws Throwable { this.wb = wb; this.data = data; this.clazz = clazz; String title = data.getLocalizedVersion("projectSynthesis"); if (clazz.equals(OrgUnit.class)) title = data.getLocalizedVersion("orgUnitSynthesis"); final HSSFSheet sheet = wb.createSheet(title); utils = new ExcelUtils(wb); int rowIndex = -1; // empty row//from w w w . j av a 2 s .c om utils.putEmptyRow(sheet, ++rowIndex, 8.65f); // title utils.putMainTitle(sheet, ++rowIndex, title.toUpperCase(), data.getNumbOfCols()); // empty row utils.putEmptyRow(sheet, ++rowIndex, ExportConstants.EMPTY_ROW_HEIGHT); // column headers row = sheet.createRow(++rowIndex); utils.putHeader(row, 2, data.getLocalizedVersion("adminFlexibleName")); utils.putHeader(row, 3, data.getLocalizedVersion("value")); // empty row utils.putEmptyRow(sheet, ++rowIndex, ExportConstants.EMPTY_ROW_HEIGHT); // freeze pane sheet.createFreezePane(0, rowIndex); // detail row = sheet.createRow(++rowIndex); utils.putHeader(row, 1, data.getLocalizedVersion("projectDetails")); // Project synthesis if (clazz.equals(Project.class)) { rowIndex = putLayout(sheet, data.getProject().getProjectModel().getProjectDetails().getLayout(), rowIndex); // empty row utils.putEmptyRow(sheet, ++rowIndex, ExportConstants.EMPTY_ROW_HEIGHT); // run through project phases to get synthesis data for (final PhaseModel phaseModel : data.getProject().getProjectModel().getPhases()) { // phase name row = sheet.createRow(++rowIndex); utils.putHeader(row, 1, phaseModel.getName()); rowIndex = putLayout(sheet, phaseModel.getLayout(), rowIndex); } } else { // Org Unit synthesis rowIndex = putLayout(sheet, data.getOrgUnit().getOrgUnitModel().getDetails().getLayout(), rowIndex); } sheet.setColumnWidth(0, 256 * 2); sheet.setColumnWidth(1, 256 * 25); sheet.setColumnWidth(2, 256 * labelColWidth); sheet.setColumnWidth(3, 256 * valueColWidth); }
From source file:org.sigmah.server.endpoint.export.sigmah.spreadsheet.template.IndicatorEntryExcelTemplate.java
License:Open Source License
public IndicatorEntryExcelTemplate(final IndicatorEntryData data, final HSSFWorkbook wb) { this.data = data; this.wb = wb; final HSSFSheet sheet = wb.createSheet(data.getLocalizedVersion("flexibleElementIndicatorsList")); utils = new ExcelUtils(wb); int rowIndex = -1; int cellIndex = 0; // empty row// w w w .j av a 2 s . c om utils.putEmptyRow(sheet, ++rowIndex, 8.65f); //title utils.putMainTitle(sheet, ++rowIndex, data.getLocalizedVersion("flexibleElementIndicatorsList").toUpperCase(), data.getNumbOfCols()); // empty row utils.putEmptyRow(sheet, ++rowIndex, ExportConstants.EMPTY_ROW_HEIGHT); // column headers row = sheet.createRow(++rowIndex); row.setHeightInPoints(ExportConstants.TITLE_ROW_HEIGHT); cellIndex = 0; utils.putHeader(row, ++cellIndex, data.getLocalizedVersion("name")); utils.putHeader(row, ++cellIndex, data.getLocalizedVersion("code")); utils.putHeader(row, ++cellIndex, data.getLocalizedVersion("targetValue")); utils.putHeader(row, ++cellIndex, data.getLocalizedVersion("value")); // empty row utils.putEmptyRow(sheet, ++rowIndex, 8.65f); // freeze pane sheet.createFreezePane(0, rowIndex); for (final IndicatorGroup group : data.getIndicators().getGroups()) { row = sheet.createRow(++rowIndex); row.setHeightInPoints(ExportConstants.TITLE_ROW_HEIGHT); putGroupCell(sheet, rowIndex, group.getName()); for (final IndicatorDTO indicator : group.getIndicators()) { //indicator's detail sheet createDetailSheet(indicator); row = sheet.createRow(++rowIndex); row.setHeightInPoints(ExportConstants.TITLE_ROW_HEIGHT); //ind name utils.createLinkCell(row.createCell(1), indicator.getName(), ExportConstants.INDICATOR_SHEET_PREFIX + indicator.getName(), true); //code utils.putBorderedBasicCell(sheet, rowIndex, 2, indicator.getCode()); //target putRightAlignedCell(sheet, rowIndex, 3, indicator.getObjective()); //current value putRightAlignedCell(sheet, rowIndex, 4, data.getFormattedValue(indicator)); } } sheet.setColumnWidth(0, 256 * 2); sheet.setColumnWidth(1, 256 * 45); sheet.setColumnWidth(2, 256 * 27); sheet.setColumnWidth(3, 256 * 27); sheet.setColumnWidth(4, 256 * 27); }
From source file:org.sourcecodemetrics.report.generators.ReportGeneratorImpl.java
License:Open Source License
@Override public void generateReport(IProject project, String filePath) { try {//from w w w . j a v a2 s .c o m FileOutputStream fileOut = new FileOutputStream(filePath); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet worksheet = workbook.createSheet("Source Code Metrics"); // creation of the header row whiteStyle = workbook.createCellStyle(); whiteStyle.setFillForegroundColor(HSSFColor.WHITE.index); whiteStyle.setFillBackgroundColor(HSSFColor.WHITE.index); whiteStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); dottedBlueStyle = workbook.createCellStyle(); dottedBlueStyle.setFillForegroundColor(HSSFColor.BLACK.index); dottedBlueStyle.setFillBackgroundColor(HSSFColor.AQUA.index); dottedBlueStyle.setFillPattern(HSSFCellStyle.SPARSE_DOTS); blueStyle = workbook.createCellStyle(); blueStyle.setFillForegroundColor(HSSFColor.AQUA.index); blueStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.RED.index); redBlueStyle = workbook.createCellStyle(); redBlueStyle.setFillForegroundColor(HSSFColor.AQUA.index); redBlueStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); redBlueStyle.setFont(font); dottedYellowStyle = workbook.createCellStyle(); dottedYellowStyle.setFillForegroundColor(HSSFColor.BLACK.index); dottedYellowStyle.setFillBackgroundColor(HSSFColor.YELLOW.index); dottedYellowStyle.setFillPattern(HSSFCellStyle.SPARSE_DOTS); yellowStyle = workbook.createCellStyle(); yellowStyle.setFillForegroundColor(HSSFColor.YELLOW.index); yellowStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); redYellowStyle = workbook.createCellStyle(); redYellowStyle.setFillForegroundColor(HSSFColor.YELLOW.index); redYellowStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); redYellowStyle.setFont(font); dottedStyle = workbook.createCellStyle(); dottedStyle.setFillForegroundColor(HSSFColor.BLACK.index); dottedStyle.setFillBackgroundColor(HSSFColor.WHITE.index); dottedStyle.setFillPattern(HSSFCellStyle.SPARSE_DOTS); redWhiteStyle = workbook.createCellStyle(); redWhiteStyle.setFillForegroundColor(HSSFColor.WHITE.index); redWhiteStyle.setFillBackgroundColor(HSSFColor.WHITE.index); redWhiteStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); redWhiteStyle.setFont(font); HSSFRow headerRow = worksheet.createRow(worksheet.getPhysicalNumberOfRows()); for (int i = 0; i < headings.size(); i++) { String title = headings.get(i); HSSFCell cell = headerRow.createCell(i); cell.setCellValue(title); cell.setCellStyle(whiteStyle); } for (IPackage ip : project.getPackages()) { if (!ip.isTests() && !ip.getSourceFiles().isEmpty()) { PackageGenerator.generate(ip, worksheet, workbook); } } // adjusting first three columns try { for (int i = 0; i < 3; i++) { worksheet.autoSizeColumn(i); } } catch (ArrayIndexOutOfBoundsException e) { logger.log(Level.SEVERE, "Exception when trying to adjust columns", e); } // setting constant width to other columns for (int i = 3; i < headings.size(); i++) { worksheet.setColumnWidth(i, 2000); } // writing out the statistics to the report StatisticsGenerator.generateStatistics(project, workbook); // writing the raw data to the report RawDataGenerator.generateRawData(project, workbook); worksheet.createFreezePane(3, 1); workbook.write(fileOut); fileOut.flush(); fileOut.close(); } catch (FileNotFoundException ex) { Exceptions.printStackTrace(ex); } catch (IOException ex) { Exceptions.printStackTrace(ex); } catch (Exception ex) { Exceptions.printStackTrace(ex); } }
From source file:PPMP.ppmpreport.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { response.setContentType("text/html;charset=UTF-8"); try {/*w w w. ja v a2s . co m*/ /* TODO output your page here. You may use following sample code. */ //create ppmp report here //define some variables for keeping number of columns. // this should be dynamic because of the annual cumulatives depending on the selected year //the minimum year is 2011 // int selectedyear = 2018; int projectstartyear = 2018; int minimumcolumns = 9;//this is if the year is 2011 int currentcolumns = minimumcolumns + (selectedyear - projectstartyear); String selectedQTR = "Q2"; if (request.getParameter("year") != null) { selectedyear = new Integer(request.getParameter("year")); } if (request.getParameter("year") != null) { selectedQTR = request.getParameter("quarter"); } HSSFWorkbook wb = new HSSFWorkbook(); Calendar cal = Calendar.getInstance(); int year = cal.get(Calendar.YEAR); String month = String.format("%02d", cal.get(Calendar.MONTH) + 1); String date = String.format("%02d", cal.get(Calendar.DATE)); String hour = String.format("%02d", cal.get(Calendar.HOUR_OF_DAY)); String min = String.format("%02d", cal.get(Calendar.MINUTE)); String sec = String.format("%02d", cal.get(Calendar.SECOND)); String generationtime = "(" + year + "_" + month + "_" + date + ")_" + hour + "-" + min + "-" + sec; //______________________________________________________________________________________ //______________________________________________________________________________________ HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 12); font.setFontName("Times New Roman"); font.setColor((short) 0000); font.setBoldweight(HSSFFont.COLOR_NORMAL); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setAlignment(HSSFCellStyle.ALIGN_LEFT); style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); style.setWrapText(true); CellStyle spstyle = wb.createCellStyle(); spstyle.setFont(font); spstyle.setBorderTop(HSSFCellStyle.BORDER_THIN); spstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); spstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); spstyle.setBorderRight(HSSFCellStyle.BORDER_THIN); spstyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); spstyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); spstyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); spstyle.setWrapText(true); spstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); System.out.println("Blue index:" + HSSFColor.BLUE.index); HSSFFont font2 = wb.createFont(); font2.setFontHeightInPoints((short) 12); font2.setFontName("Times New Roman"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); style2.setWrapText(true); HSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.WHITE.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_LEFT); HSSFFont fontx = wb.createFont(); fontx.setFontHeightInPoints((short) 12); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Times New Roman"); fontx.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); fontx.setFontHeightInPoints((short) 16); stylex.setFont(fontx); stylex.setWrapText(true); HSSFSheet shet = wb.createSheet("PPMP " + selectedyear + " Report "); //create headers for that worksheet HSSFRow rw = shet.createRow(0); rw.setHeightInPoints(25); HSSFCell cl0 = rw.createCell(0); cl0.setCellValue("PROJECT PERFORMANCE MONITORING PLAN (PPMP)"); cl0.setCellStyle(stylex); for (int a = 1; a < currentcolumns; a++) { HSSFCell clx = rw.createCell(a); clx.setCellValue(""); clx.setCellStyle(stylex); } //merge row one shet.addMergedRegion(new CellRangeAddress(0, 0, 0, currentcolumns - 1)); //firt row ArrayList headerone = new ArrayList(); //headerone.add("Sub Purpose"); headerone.add("Code"); headerone.add("Indicator"); headerone.add("Baseline"); headerone.add("Year " + selectedyear + " Target"); headerone.add(selectedyear + " Quarterly Achievements "); headerone.add(""); headerone.add(""); headerone.add(""); headerone.add("Cumulative Year Achievements"); //the header Cumulative Year Achievements could be in the report depending on the selected year //for 2011, its not expected to appear in the report for (int a = 0; a <= (selectedyear - projectstartyear); a++) { if (a == 0) { //do nothing } else { headerone.add(""); } } headerone.add("Percentage (%) Achieved vs Year " + selectedyear); //header two which contains quartersa dn yearly achievement ArrayList headertwo = new ArrayList(); //headertwo.add("Sub Purpose"); headertwo.add("Code"); headertwo.add("Indicator"); headertwo.add("Baseline"); headertwo.add(""); headertwo.add("Oct-Dec " + (selectedyear - 1)); headertwo.add("Jan-Mar"); headertwo.add("Apr-Jun"); headertwo.add("Jul-Sep"); headertwo.add(selectedyear); //the header Cumulative Year Achievements could be in the report depending on the selected year //for 2011, its not expected to appear in the report for (int a = 0; a <= (selectedyear - projectstartyear); a++) { if (a == 0) { //do nothing } else { headertwo.add(selectedyear - a); //eg 2016, 2015, 2014 ... } } headertwo.add(""); //================================================================================= //DISPLAY HEADER DISPLAY HEADER DISPLAY HEADER DISPLAY HEADER DISPLAY HEADER //================================================================================= //display the header values for row one and two HSSFRow rw1 = shet.createRow(1); for (int a = 0; a < headerone.size(); a++) { HSSFCell cellx = rw1.createCell(a); cellx.setCellValue(headerone.get(a).toString()); cellx.setCellStyle(style); shet.setColumnWidth(a, 3500); } //===================row 2======================= //display the header values for row one and two HSSFRow rw2 = shet.createRow(2); rw2.setHeightInPoints(35); for (int a = 0; a < headertwo.size(); a++) { HSSFCell cellx = rw2.createCell(a); if (headertwo.get(a).toString().startsWith("20")) { cellx.setCellValue(new Integer(headertwo.get(a).toString())); } else { cellx.setCellValue(headertwo.get(a).toString()); } cellx.setCellStyle(style); } // shet.addMergedRegion(new CellRangeAddress(start row, end row, start column ,end column)); String mergingarray[] = { "1_2_0_0", "1_2_1_1", "1_2_2_2", "1_2_3_3", "1_1_4_7", "1_1_8_" + (8 + (selectedyear - projectstartyear)), "1_2_" + (8 + (selectedyear - projectstartyear) + 1) + "_" + (8 + (selectedyear - projectstartyear) + 1) }; for (int a = 0; a < mergingarray.length; a++) { String content[] = mergingarray[a].split("_"); shet.addMergedRegion(new CellRangeAddress(new Integer(content[0]), new Integer(content[1]), new Integer(content[2]), new Integer(content[3]))); } //=================================================================================== //DISPLAY VALUES DISPLAY VALUES DISPLAY VALUES DISPLAY VALUES DISPLAY VALUES //=================================================================================== String getindicators = "select * from indicatortitles where active='yes' and inppmp='yes' order by tableNo,output "; dbConnect conn = new dbConnect(); conn.rs = conn.state.executeQuery(getindicators); int rownumber = 3; shet.setColumnWidth(1, 20000); shet.setColumnWidth(2, 2300); shet.setColumnWidth(0, 2100); shet.setColumnWidth(3, 2500); shet.setColumnWidth(4, 2500); shet.setColumnWidth(5, 2500); shet.setColumnWidth(6, 2500); shet.setColumnWidth(7, 2500); shet.setColumnWidth(8, 2500); shet.setColumnWidth(9, 2500); shet.setColumnWidth(10, 2500); shet.setColumnWidth(11, 2500); shet.setColumnWidth(12, 2500); shet.setColumnWidth(13, 2500); shet.setColumnWidth(14, 2500); ArrayList sp = new ArrayList(); int subpurposerow = 3; int subpurposerowcopy = 3; ArrayList op = new ArrayList(); int outputrow = 3; int outputrowcopy = 4; int colpos = 0; int count = 0; int rowgani = 1; while (conn.rs.next()) { System.out.println("SP__" + conn.rs.getString("output")); //merge subpartner row sp.add(conn.rs.getString("output")); if (sp.size() > 1) { //check if subporpose has changed if (!sp.get(count).toString().equals(sp.get(count - 1).toString())) { subpurposerow = rownumber; //===================================================subpurpose======================== HSSFRow rwxa = shet.createRow(rownumber); rwxa.setHeightInPoints(25); HSSFCell cl01 = rwxa.createCell(colpos); cl01.setCellValue(conn.rs.getString("output")); cl01.setCellStyle(spstyle); subpurposerowcopy = subpurposerow; //should merge entire row shet.addMergedRegion( new CellRangeAddress(subpurposerow, subpurposerow, 0, selectedyear - 2009)); rownumber++; } } else { subpurposerow = rownumber; //===================================================subpurpose======================== HSSFRow rwxa = shet.createRow(rownumber); rwxa.setHeightInPoints(25); HSSFCell cl01 = rwxa.createCell(colpos); cl01.setCellValue(conn.rs.getString("output")); cl01.setCellStyle(spstyle); subpurposerowcopy = subpurposerow; //should merge entire row shet.addMergedRegion( new CellRangeAddress(subpurposerow, subpurposerow, 0, selectedyear - 2009)); rownumber++; } String outputval = ""; if (conn.rs.getString("output") != null) { outputval = conn.rs.getString("output"); } //merge ouput rows op.add(outputval); if (op.size() > 1) { //check if out has changed if (!op.get(count).toString().equals(op.get(count - 1).toString())) { outputrow = rownumber; //should merge shet.addMergedRegion(new CellRangeAddress(outputrowcopy, outputrow - 1, 0, 0)); outputrowcopy = outputrow; System.out.println(" Comparison :" + op.get(count).toString() + " %%% " + op.get(count - 1).toString()); } } //now output the first part of the report HSSFRow rwx = shet.createRow(rownumber); //====================================================output============================= HSSFCell cl02 = rwx.createCell(colpos); // cl02.setCellValue(conn.rs.getString("output")); String tn = conn.rs.getString("tableno"); if (!tn.contains(".")) { if (isNumeric(tn)) { cl02.setCellValue(new Integer(tn)); } else { cl02.setCellValue(tn); } } else { cl02.setCellValue(tn); } cl02.setCellStyle(style2); colpos++; //===================================================indicators=========================== HSSFCell cl03 = rwx.createCell(colpos); cl03.setCellValue(conn.rs.getString("title")); cl03.setCellStyle(style2); colpos++; //====================================================baseline============================= HSSFCell cl04 = rwx.createCell(colpos); cl04.setCellValue(conn.rs.getString("totalbaseline")); cl04.setCellStyle(style2); colpos++; //====================================================targets================================= String gettargets = " select sum(target_combined) as target from yearly_targets where indicator_id='" + conn.rs.getString("titleID") + "' and year='" + selectedyear + "' "; //for percent indicators, get avg if (conn.rs.getInt("percentage") == 1) { gettargets = "select avg(target_combined) as target from yearly_targets where indicator_id='" + conn.rs.getString("titleID") + "' and year='" + selectedyear + "' "; } HSSFCell cl05 = rwx.createCell(colpos); colpos++; int annualtarget = 1; conn.rs1 = conn.state1.executeQuery(gettargets); if (conn.rs1.next()) { //set the target cl05.setCellValue(conn.rs1.getInt("target")); if (conn.rs.getInt("percentage") == 1) { if (conn.rs1.getInt("target") < 200) { cl05.setCellValue(conn.rs1.getInt("target") + "%"); } else { cl05.setCellValue(conn.rs1.getInt("target")); } } else { cl05.setCellValue(conn.rs1.getInt("target")); } cl05.setCellStyle(style2); if (conn.rs1.getString("target") != null) { if (!conn.rs1.getString("target").equals("")) { annualtarget = conn.rs1.getInt("target"); } } } //===========================================current year values==================================== String ispercent = ""; int highestvalue = 0; String getdata = " select sum(case when reportingPeriod='Q1' then totalAchieved end) as Q1, sum(case when reportingPeriod='Q2' then totalAchieved end) as Q2, sum(case when reportingPeriod='Q3' then totalAchieved end) as Q3, sum(case when reportingPeriod='Q4' then totalAchieved end) as Q4 where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear + "' group by titleID "; //for percent indicators, get avg if (conn.rs.getInt("percentage") == 1) { ispercent = "%"; if (conn.rs.getString("tableIdentifier").equals("1")) { //by gender and thus separate columns if (selectedyear >= 2018) { getdata = " select ROUND((sum(case when reportingPeriod='Q1' then ((men_numerator + women_numerator)) end)/sum(case when reportingPeriod='Q1' then ((men_denominator + women_denominator)) end))*100 ) as Q1, ROUND((sum(case when reportingPeriod='Q2' then ((men_numerator + women_numerator)) end)/sum(case when reportingPeriod='Q2' then ((men_denominator + women_denominator)) end))*100 ) as Q2, ROUND((sum(case when reportingPeriod='Q3' then ((men_numerator + women_numerator)) end)/sum(case when reportingPeriod='Q3' then ((men_denominator + women_denominator)) end))*100 ) as Q3, ROUND((sum(case when reportingPeriod='Q4' then ((men_numerator + women_numerator)) end)/sum(case when reportingPeriod='Q4' then ((men_denominator + women_denominator)) end))*100 ) as Q4 from indicatorachieved where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear + "' group by titleID "; System.out.println("@@" + getdata); } else { getdata = " select ROUND(AVG(case when reportingPeriod='Q1' then ((menAchieved + womenAchieved)/2) end)) as Q1, ROUND(AVG(case when reportingPeriod='Q2' then ((menAchieved + womenAchieved)/2) end)) as Q2, ROUND(AVG(case when reportingPeriod='Q3' then ((menAchieved + womenAchieved)/2) end)) as Q3, ROUND(AVG(case when reportingPeriod='Q4' then ((menAchieved + womenAchieved)/2) end)) as Q4 from indicatorachieved where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear + "' group by titleID "; } } else { //combined i.e male and female if (selectedyear >= 2018) { getdata = "select ROUND((SUM(case when reportingPeriod='Q1' then numerator end)/SUM(case when reportingPeriod='Q1' then denominator end))*100) as Q1 , ROUND((SUM(case when reportingPeriod='Q2' then numerator end)/SUM(case when reportingPeriod='Q2' then denominator end))*100) as Q2, ROUND((SUM(case when reportingPeriod='Q3' then numerator end)/SUM(case when reportingPeriod='Q3' then denominator end))*100) as Q3, ROUND((SUM(case when reportingPeriod='Q4' then numerator end)/SUM(case when reportingPeriod='Q4' then denominator end))*100) as Q4 from indicatorachievedcombined where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear + "' group by titleID "; } else { //use old way of averages getdata = " select ROUND(AVG(case when reportingPeriod='Q1' then totalAchieved end)) as Q1,ROUND(AVG(case when reportingPeriod='Q2' then totalAchieved end)) as Q2, ROUND(AVG(case when reportingPeriod='Q3' then totalAchieved end)) as Q3, ROUND(AVG(case when reportingPeriod='Q4' then totalAchieved end)) as Q4 from indicatorachievedcombined where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear + "' group by titleID "; } System.out.println("@@" + getdata); } } //non percentages else { //if if (conn.rs.getString("tableIdentifier").equals("1")) { //by gender and thus separate columns getdata = " select sum(case when reportingPeriod='Q1' then (menAchieved + womenAchieved) end) as Q1,sum(case when reportingPeriod='Q2' then (menAchieved + womenAchieved) end) as Q2, sum(case when reportingPeriod='Q3' then (menAchieved + womenAchieved) end) as Q3, sum(case when reportingPeriod='Q4' then (menAchieved + womenAchieved) end) as Q4 from indicatorachieved where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear + "' group by titleID "; } else { getdata = " select sum(case when reportingPeriod='Q1' then totalAchieved end) as Q1, sum(case when reportingPeriod='Q2' then totalAchieved end) as Q2, sum(case when reportingPeriod='Q3' then totalAchieved end) as Q3, sum(case when reportingPeriod='Q4' then totalAchieved end) as Q4 from indicatorachievedcombined where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear + "' group by titleID "; } } String Q1 = ""; String Q2 = ""; String Q3 = ""; String Q4 = ""; conn.rs1 = conn.state1.executeQuery(getdata); //Q1 HSSFCell clQ1 = rwx.createCell(colpos); colpos++; HSSFCell clQ2 = rwx.createCell(colpos); colpos++; HSSFCell clQ3 = rwx.createCell(colpos); colpos++; HSSFCell clQ4 = rwx.createCell(colpos); colpos++; if (conn.rs1.next()) { if (conn.rs1.getString("Q1") != null) { if (!conn.rs1.getString("Q1").equals("")) { highestvalue = conn.rs1.getInt("Q1"); if (!ispercent.equals("")) { clQ1.setCellValue(conn.rs1.getInt("Q1") + ispercent); } else { clQ1.setCellValue(conn.rs1.getInt("Q1")); } } } if (conn.rs1.getString("Q2") != null) { if (!conn.rs1.getString("Q2").equals("")) { if (conn.rs1.getInt("Q2") > highestvalue) { highestvalue = conn.rs1.getInt("Q2"); } if (!ispercent.equals("")) { clQ2.setCellValue(conn.rs1.getInt("Q2") + ispercent); } else { clQ2.setCellValue(conn.rs1.getInt("Q2")); } } } if (conn.rs1.getString("Q3") != null) { if (!conn.rs1.getString("Q3").equals("")) { if (conn.rs1.getInt("Q3") > highestvalue) { highestvalue = conn.rs1.getInt("Q3"); } if (!ispercent.equals("")) { clQ3.setCellValue(conn.rs1.getInt("Q3") + ispercent); } else { clQ3.setCellValue(conn.rs1.getInt("Q3")); } } } if (conn.rs1.getString("Q4") != null) { if (!conn.rs1.getString("Q4").equals("")) { if (conn.rs1.getInt("Q4") > highestvalue) { highestvalue = conn.rs1.getInt("Q4"); } if (!ispercent.equals("")) { clQ4.setCellValue(conn.rs1.getInt("Q4") + ispercent); } else { clQ4.setCellValue(conn.rs1.getInt("Q4")); } } } } clQ2.setCellStyle(style2); clQ1.setCellStyle(style2); clQ3.setCellStyle(style2); clQ4.setCellStyle(style2); //====================================Annual figures======================= String percentageachievement = ""; String achievednonpercent = "No target / achieved value"; int curcol = colpos; String annualispercent = ""; int currentyearvalue = 0; int currentyearhighestqtr = 0; for (int yearval = selectedyear; yearval >= projectstartyear; yearval--) { HSSFCell clx = rwx.createCell(curcol); // System.out.println("******"+curcol); //separate cumulates + average with the rest if (conn.rs.getString("cumulative_chooser").equalsIgnoreCase("Cumulative") || conn.rs.getString("cumulative_chooser").equalsIgnoreCase("Average")) { String qry = ""; if (conn.rs.getString("percentage").equals("1")) { annualispercent = "%"; if (conn.rs.getString("tableidentifier").equals("2")) { //no gender thus its combined if (yearval >= 2018) { qry = " select ROUND((SUM(case when financialYear='" + yearval + "' then numerator end)/SUM(case when financialYear='" + yearval + "' then denominator end))*100) as y" + yearval + " from indicatorachievedcombined where financialyear='" + yearval + "' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } else { qry = " select ROUND(AVG(totalAchieved)) as y" + yearval + " from indicatorachievedcombined where financialyear='" + yearval + "' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } } else { if (yearval >= 2018) { qry = " select ROUND((sum(case when financialYear='" + yearval + "' then ((men_numerator + women_numerator)) end)/sum(case when financialYear='" + yearval + "' then ((men_denominator + women_denominator)) end))*100) as y" + yearval + " from indicatorachieved where financialyear='" + yearval + "' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } else { qry = " select ROUND(AVG((menAchieved + womenAchieved)/2)) as y" + yearval + " from indicatorachieved where financialyear='" + yearval + "' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } } } //non percents else { //for cumulatives //check tabletype //1 is by gender //_____________CUMULATIVES______________ if (conn.rs.getString("cumulative_chooser").equalsIgnoreCase("Cumulative")) { if (conn.rs.getString("tableIdentifier").equals("1")) { qry = " select sum((menAchieved + womenAchieved)) as y" + yearval + " from indicatorachieved where financialyear='" + yearval + "' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } else { qry = " select sum(totalAchieved) as y" + yearval + " from indicatorachievedcombined where financialyear='" + yearval + "' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } //end of else of table identifier } //end of sum/cumulatives else { System.out.println("failed:" + conn.rs.getString("cumulative_chooser") + " *** " + conn.rs.getString("title")); } } //end of else of non percents System.out.println("@ annual query" + qry); conn.rs1 = conn.state1.executeQuery(qry); if (conn.rs1.next()) { // System.out.println("__"+conn.rs1.getString(1)); if (conn.rs1.getString(1) != null) { if (!conn.rs1.getString(1).equals("")) { if (annualispercent.equals("")) { clx.setCellValue(conn.rs1.getInt(1)); //achieved nonpercentage if (yearval == selectedyear) { if (annualtarget > 1) { achievednonpercent = "" + (int) (((double) conn.rs1.getDouble(1) * 100) / (double) annualtarget) + "%"; //System.out.println("For id "+conn.rs.getString("titleID")+" "+achievednonpercent); } } } else { clx.setCellValue(conn.rs1.getInt(1) + annualispercent); //do this for the cureent year if (yearval == selectedyear) { percentageachievement = conn.rs1.getInt(1) + annualispercent; } } } } } clx.setCellStyle(style2); } //end of cumulatives and percentages else if (conn.rs.getString("cumulative_chooser").equalsIgnoreCase("Highest")) { String qry = ""; if (conn.rs.getString("tableIdentifier").equals("1")) { //by gender and thus separate columns qry = " select sum(case when reportingPeriod='Q1' then (menAchieved + womenAchieved) end) as Q1,sum(case when reportingPeriod='Q2' then (menAchieved + womenAchieved) end) as Q2, sum(case when reportingPeriod='Q3' then (menAchieved + womenAchieved) end) as Q3, sum(case when reportingPeriod='Q4' then (menAchieved + womenAchieved) end) as Q4 from indicatorachieved where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + yearval + "' group by titleID "; } else { qry = " select sum(case when reportingPeriod='Q1' then totalAchieved end) as Q1, sum(case when reportingPeriod='Q2' then totalAchieved end) as Q2, sum(case when reportingPeriod='Q3' then totalAchieved end) as Q3, sum(case when reportingPeriod='Q4' then totalAchieved end) as Q4 from indicatorachievedcombined where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + yearval + "' group by titleID "; } int highestqtr = 0; //excecute query conn.rs1 = conn.state1.executeQuery(qry); while (conn.rs1.next()) { // if (conn.rs1.getString("Q1") != null) { if (!conn.rs1.getString("Q1").equals("")) { if (conn.rs1.getInt("Q1") > highestqtr) { highestqtr = conn.rs1.getInt("Q1"); } } } else if (conn.rs1.getString("Q2") != null) { if (!conn.rs1.getString("Q2").equals("")) { if (conn.rs1.getInt("Q2") > highestqtr) { highestqtr = conn.rs1.getInt("Q2"); } } } else if (conn.rs1.getString("Q3") != null) { if (!conn.rs1.getString("Q3").equals("")) { if (conn.rs1.getInt("Q3") > highestqtr) { highestqtr = conn.rs1.getInt("Q3"); } } } else if (conn.rs1.getString("Q4") != null) { if (!conn.rs1.getString("Q4").equals("")) { if (conn.rs1.getInt("Q4") > highestqtr) { highestqtr = conn.rs1.getInt("Q4"); } } } } if (highestqtr > 0) { clx.setCellValue(highestqtr); } else { clx.setCellValue(""); } //achieved nonpercentage if (yearval == selectedyear) { if (annualtarget != 1) { achievednonpercent = "" + (int) highestqtr * 100 / (int) annualtarget + "%"; System.out.println(highestqtr + " / " + annualtarget + "___" + (int) highestqtr * 100 / (int) (annualtarget) + "%"); } } clx.setCellStyle(style2); } // end of highest else if (conn.rs.getString("cumulative_chooser").equalsIgnoreCase("OLMIS") || conn.rs.getString("cumulative_chooser").equalsIgnoreCase("Last Reported")) { String qry = ""; //get data for the last input quarter if (yearval == selectedyear) { //get data for that quarter if (conn.rs.getString("tableidentifier").equals("2")) { //no gender qry = " select sum((totalAchieved)) as y" + yearval + " from indicatorachievedcombined where financialyear='" + yearval + "' and reportingPeriod='" + selectedQTR + "' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } else { qry = " select sum((menAchieved + womenAchieved)) as y" + yearval + " from indicatorachieved where financialyear='" + yearval + "' and reportingPeriod='" + selectedQTR + "' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } } else { if (conn.rs.getString("tableidentifier").equals("2")) { //no gender qry = " select sum((totalAchieved)) as y" + yearval + " from indicatorachievedcombined where financialyear='" + yearval + "' and reportingPeriod='Q4' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } else { qry = " select sum((menAchieved + womenAchieved)) as y" + yearval + " from indicatorachieved where financialyear='" + yearval + "' and reportingPeriod='Q4' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } } //execute the query conn.rs1 = conn.state1.executeQuery(qry); if (conn.rs1.next()) { // System.out.println("__"+conn.rs1.getString(1)); if (conn.rs1.getString(1) != null) { if (!conn.rs1.getString(1).equals("")) { clx.setCellValue(conn.rs1.getInt(1)); //this is for percentage purpose if (selectedyear == yearval) { currentyearvalue = conn.rs1.getInt(1); } } } } //achieved nonpercentage if (yearval == selectedyear) { if (annualtarget != 1 && annualtarget != 0) { achievednonpercent = "" + (((int) currentyearvalue * 100) / (int) annualtarget) + "%"; } } clx.setCellStyle(style2); } //end of olmis and last reported indicators curcol++; } //end of for loop HSSFCell clx = rwx.createCell(curcol); if (conn.rs.getInt("percentage") == 1) { clx.setCellValue(percentageachievement); } else { clx.setCellValue(achievednonpercent); } clx.setCellStyle(style2); rwx.setHeightInPoints(42); rownumber++; count++; colpos = 0; } for (int e = 0; e < 13; e++) { //shet.getRow(rowgani).autoSizeColumn(e,true); } //shet.autoSizeColumn(1,false); //Made my life veery simple... shet.setDisplayGridlines(false); shet.createFreezePane(2, 3); if (conn.rs != null) { conn.rs.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.state1 != null) { conn.state1.close(); } if (conn.state2 != null) { conn.state2.close(); } // write it as an excel attachment ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PPMPREPORT_" + selectedyear + "_" + selectedQTR + "_gen_on_" + generationtime + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } finally { } }
From source file:PPMP.ppmpreport_2.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { response.setContentType("text/html;charset=UTF-8"); try {//from www . j a va 2 s . c om /* TODO output your page here. You may use following sample code. */ //create ppmp report here //define some variables for keeping number of columns. // this should be dynamic because of the annual cumulatives depending on the selected year //the minimum year is 2011 // int selectedyear = 2016; int projectstartyear = 2011; int minimumcolumns = 11;//this is if the year is 2011 int currentcolumns = minimumcolumns + (selectedyear - projectstartyear); String selectedQTR = "Q2"; if (request.getParameter("year") != null) { selectedyear = new Integer(request.getParameter("year")); } if (request.getParameter("year") != null) { selectedQTR = request.getParameter("quarter"); } HSSFWorkbook wb = new HSSFWorkbook(); Calendar cal = Calendar.getInstance(); int year = cal.get(Calendar.YEAR); String month = String.format("%02d", cal.get(Calendar.MONTH) + 1); String date = String.format("%02d", cal.get(Calendar.DATE)); String hour = String.format("%02d", cal.get(Calendar.HOUR_OF_DAY)); String min = String.format("%02d", cal.get(Calendar.MINUTE)); String sec = String.format("%02d", cal.get(Calendar.SECOND)); String generationtime = "(" + year + "_" + month + "_" + date + ")_" + hour + "-" + min + "-" + sec; //______________________________________________________________________________________ //______________________________________________________________________________________ HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 12); font.setFontName("Arial Narrow"); font.setColor((short) 0000); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setBorderTop(HSSFCellStyle.BORDER_THICK); style.setBorderBottom(HSSFCellStyle.BORDER_THICK); style.setBorderLeft(HSSFCellStyle.BORDER_THICK); style.setBorderRight(HSSFCellStyle.BORDER_THICK); style.setAlignment(HSSFCellStyle.ALIGN_LEFT); style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setWrapText(true); CellStyle spstyle = wb.createCellStyle(); spstyle.setFont(font); spstyle.setBorderTop(HSSFCellStyle.BORDER_THICK); spstyle.setBorderBottom(HSSFCellStyle.BORDER_THICK); spstyle.setBorderLeft(HSSFCellStyle.BORDER_THICK); spstyle.setBorderRight(HSSFCellStyle.BORDER_THICK); spstyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); spstyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index); spstyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); spstyle.setWrapText(true); System.out.println("Blue index:" + HSSFColor.BLUE.index); HSSFFont font2 = wb.createFont(); font2.setFontName("Cambria"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); style2.setBorderTop(HSSFCellStyle.BORDER_THICK); style2.setBorderBottom(HSSFCellStyle.BORDER_THICK); style2.setBorderLeft(HSSFCellStyle.BORDER_THICK); style2.setBorderRight(HSSFCellStyle.BORDER_THICK); style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); style2.setWrapText(true); HSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THICK); stborder.setBorderBottom(HSSFCellStyle.BORDER_THICK); stborder.setBorderLeft(HSSFCellStyle.BORDER_THICK); stborder.setBorderRight(HSSFCellStyle.BORDER_THICK); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.WHITE.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THICK); stylex.setBorderBottom(HSSFCellStyle.BORDER_THICK); stylex.setBorderLeft(HSSFCellStyle.BORDER_THICK); stylex.setBorderRight(HSSFCellStyle.BORDER_THICK); stylex.setAlignment(HSSFCellStyle.ALIGN_LEFT); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Arial Narrow"); fontx.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); fontx.setFontHeightInPoints((short) 16); stylex.setFont(fontx); stylex.setWrapText(true); HSSFSheet shet = wb.createSheet("PPMP " + selectedyear + " Report "); //create headers for that worksheet HSSFRow rw = shet.createRow(0); rw.setHeightInPoints(25); HSSFCell cl0 = rw.createCell(0); cl0.setCellValue("PROJECT PERFORMANCE MONITORING PLAN (PPMP)"); cl0.setCellStyle(stylex); for (int a = 1; a < currentcolumns; a++) { HSSFCell clx = rw.createCell(a); clx.setCellValue(""); clx.setCellStyle(stylex); } //merge row one shet.addMergedRegion(new CellRangeAddress(0, 0, 0, currentcolumns - 1)); //firt row ArrayList headerone = new ArrayList(); //headerone.add("Sub Purpose"); headerone.add("Output"); headerone.add("Indicators"); headerone.add("Baseline"); headerone.add("Year " + selectedyear + " Target"); headerone.add(selectedyear + " Quarterly Achievements "); headerone.add(""); headerone.add(""); headerone.add(""); headerone.add("Cumulative Year Achievements"); //the header Cumulative Year Achievements could be in the report depending on the selected year //for 2011, its not expected to appear in the report for (int a = 0; a <= (selectedyear - projectstartyear); a++) { if (a == 0) { //do nothing } else { headerone.add(""); } } headerone.add("Percentage (%) Achieved vs Year " + selectedyear); //header two which contains quartersa dn yearly achievement ArrayList headertwo = new ArrayList(); //headertwo.add("Sub Purpose"); headertwo.add("Output"); headertwo.add("Indicators"); headertwo.add("Baseline"); headertwo.add(""); headertwo.add("Oct-Dec " + (selectedyear - 1)); headertwo.add("Jan-Mar"); headertwo.add("Apr-Jun"); headertwo.add("Jul-Sep"); headertwo.add(selectedyear); //the header Cumulative Year Achievements could be in the report depending on the selected year //for 2011, its not expected to appear in the report for (int a = 0; a <= (selectedyear - projectstartyear); a++) { if (a == 0) { //do nothing } else { headertwo.add(selectedyear - a); //eg 2016, 2015, 2014 ... } } headertwo.add(""); //================================================================================= //DISPLAY HEADER DISPLAY HEADER DISPLAY HEADER DISPLAY HEADER DISPLAY HEADER //================================================================================= //display the header values for row one and two HSSFRow rw1 = shet.createRow(1); for (int a = 0; a < headerone.size(); a++) { HSSFCell cellx = rw1.createCell(a); cellx.setCellValue(headerone.get(a).toString()); cellx.setCellStyle(style); shet.setColumnWidth(a, 3500); } //===================row 2======================= //display the header values for row one and two HSSFRow rw2 = shet.createRow(2); rw2.setHeightInPoints(35); for (int a = 0; a < headertwo.size(); a++) { HSSFCell cellx = rw2.createCell(a); if (headertwo.get(a).toString().startsWith("20")) { cellx.setCellValue(new Integer(headertwo.get(a).toString())); } else { cellx.setCellValue(headertwo.get(a).toString()); } cellx.setCellStyle(style); } // shet.addMergedRegion(new CellRangeAddress(start row, end row, start column ,end column)); String mergingarray[] = { "1_2_0_0", "1_2_1_1", "1_2_2_2", "1_2_3_3", "1_1_4_7", "1_1_8_" + (8 + (selectedyear - projectstartyear)), "1_2_" + (8 + (selectedyear - projectstartyear) + 1) + "_" + (8 + (selectedyear - projectstartyear) + 1) }; for (int a = 0; a < mergingarray.length; a++) { String content[] = mergingarray[a].split("_"); shet.addMergedRegion(new CellRangeAddress(new Integer(content[0]), new Integer(content[1]), new Integer(content[2]), new Integer(content[3]))); } //=================================================================================== //DISPLAY VALUES DISPLAY VALUES DISPLAY VALUES DISPLAY VALUES DISPLAY VALUES //=================================================================================== String getindicators = "select * from indicatortitles where active='yes' order by subpurpose, output , title "; dbConnect conn = new dbConnect(); conn.rs = conn.state.executeQuery(getindicators); int rownumber = 3; shet.setColumnWidth(1, 14000); shet.setColumnWidth(2, 2300); shet.setColumnWidth(0, 7000); shet.setColumnWidth(3, 2300); shet.setColumnWidth(4, 2300); shet.setColumnWidth(5, 2300); shet.setColumnWidth(6, 2300); shet.setColumnWidth(7, 2300); shet.setColumnWidth(8, 2300); shet.setColumnWidth(9, 2300); shet.setColumnWidth(10, 2300); shet.setColumnWidth(11, 2300); shet.setColumnWidth(12, 2300); shet.setColumnWidth(13, 2300); shet.setColumnWidth(14, 2300); ArrayList sp = new ArrayList(); int subpurposerow = 3; int subpurposerowcopy = 3; ArrayList op = new ArrayList(); int outputrow = 3; int outputrowcopy = 4; int colpos = 0; int count = 0; while (conn.rs.next()) { System.out.println("SP__" + conn.rs.getString("subpurpose")); //merge subpartner row sp.add(conn.rs.getString("subpurpose")); if (sp.size() > 1) { //check if subporpose has changed if (!sp.get(count).toString().equals(sp.get(count - 1).toString())) { subpurposerow = rownumber; //===================================================subpurpose======================== HSSFRow rwxa = shet.createRow(rownumber); HSSFCell cl01 = rwxa.createCell(colpos); cl01.setCellValue(conn.rs.getString("subpurpose")); cl01.setCellStyle(spstyle); subpurposerowcopy = subpurposerow; //should merge entire row shet.addMergedRegion(new CellRangeAddress(subpurposerow, subpurposerow, 0, 15)); rownumber++; } } else { subpurposerow = rownumber; //===================================================subpurpose======================== HSSFRow rwxa = shet.createRow(rownumber); HSSFCell cl01 = rwxa.createCell(colpos); cl01.setCellValue(conn.rs.getString("subpurpose")); cl01.setCellStyle(spstyle); subpurposerowcopy = subpurposerow; //should merge entire row shet.addMergedRegion(new CellRangeAddress(subpurposerow, subpurposerow, 0, 15)); rownumber++; } String outputval = ""; if (conn.rs.getString("output") != null) { outputval = conn.rs.getString("output"); } //merge ouput rows op.add(outputval); if (op.size() > 1) { //check if out has changed if (!op.get(count).toString().equals(op.get(count - 1).toString())) { outputrow = rownumber; //should merge shet.addMergedRegion(new CellRangeAddress(outputrowcopy, outputrow - 1, 0, 0)); outputrowcopy = outputrow; System.out.println(" Comparison :" + op.get(count).toString() + " %%% " + op.get(count - 1).toString()); } } //now output the first part of the report HSSFRow rwx = shet.createRow(rownumber); //====================================================output============================= HSSFCell cl02 = rwx.createCell(colpos); cl02.setCellValue(conn.rs.getString("output")); cl02.setCellStyle(style2); colpos++; //===================================================indicators=========================== HSSFCell cl03 = rwx.createCell(colpos); cl03.setCellValue(conn.rs.getString("title")); cl03.setCellStyle(style2); colpos++; //====================================================baseline============================= HSSFCell cl04 = rwx.createCell(colpos); cl04.setCellValue(conn.rs.getString("totalbaseline")); cl04.setCellStyle(style2); colpos++; //====================================================targets================================= String gettargets = " select sum(target_combined) as target from yearly_targets where indicator_id='" + conn.rs.getString("titleID") + "' and year='" + selectedyear + "' "; //for percent indicators, get avg if (conn.rs.getInt("percentage") == 1) { gettargets = "select avg(target_combined) as target from yearly_targets where indicator_id='" + conn.rs.getString("titleID") + "' and year='" + selectedyear + "' "; } HSSFCell cl05 = rwx.createCell(colpos); colpos++; int annualtarget = 1; conn.rs1 = conn.state1.executeQuery(gettargets); if (conn.rs1.next()) { //set the target cl05.setCellValue(conn.rs1.getInt("target")); if (conn.rs.getInt("percentage") == 1) { if (conn.rs1.getInt("target") < 200) { cl05.setCellValue(conn.rs1.getInt("target") + "%"); } else { cl05.setCellValue(conn.rs1.getInt("target")); } } else { cl05.setCellValue(conn.rs1.getInt("target")); } cl05.setCellStyle(style2); if (conn.rs1.getString("target") != null) { if (!conn.rs1.getString("target").equals("")) { annualtarget = conn.rs1.getInt("target"); } } } //===========================================current year values==================================== String ispercent = ""; int highestvalue = 0; String getdata = " select sum(case when reportingPeriod='Q1' then totalAchieved end) as Q1, sum(case when reportingPeriod='Q2' then totalAchieved end) as Q2, sum(case when reportingPeriod='Q3' then totalAchieved end) as Q3, sum(case when reportingPeriod='Q4' then totalAchieved end) as Q4 where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear + "' group by titleID "; //for percent indicators, get avg if (conn.rs.getInt("percentage") == 1) { ispercent = "%"; if (conn.rs.getString("tableIdentifier").equals("1")) { //by gender and thus separate columns getdata = " select ROUND(AVG(case when reportingPeriod='Q1' then ((menAchieved + womenAchieved)/2) end)) as Q1, ROUND(AVG(case when reportingPeriod='Q2' then ((menAchieved + womenAchieved)/2) end)) as Q2, ROUND(AVG(case when reportingPeriod='Q3' then ((menAchieved + womenAchieved)/2) end)) as Q3, ROUND(AVG(case when reportingPeriod='Q4' then ((menAchieved + womenAchieved)/2) end)) as Q4 from indicatorachieved where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear + "' group by titleID "; System.out.println("@@" + getdata); } else { //combined i.e male and female getdata = " select ROUND(AVG(case when reportingPeriod='Q1' then totalAchieved end)) as Q1,ROUND(AVG(case when reportingPeriod='Q2' then totalAchieved end)) as Q2, ROUND(AVG(case when reportingPeriod='Q3' then totalAchieved end)) as Q3, ROUND(AVG(case when reportingPeriod='Q4' then totalAchieved end)) as Q4 from indicatorachievedcombined where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear + "' group by titleID "; System.out.println("@@" + getdata); } } //non percentages else { //if if (conn.rs.getString("tableIdentifier").equals("1")) { //by gender and thus separate columns getdata = " select sum(case when reportingPeriod='Q1' then (menAchieved + womenAchieved) end) as Q1,sum(case when reportingPeriod='Q2' then (menAchieved + womenAchieved) end) as Q2, sum(case when reportingPeriod='Q3' then (menAchieved + womenAchieved) end) as Q3, sum(case when reportingPeriod='Q4' then (menAchieved + womenAchieved) end) as Q4 from indicatorachieved where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear + "' group by titleID "; } else { getdata = " select sum(case when reportingPeriod='Q1' then totalAchieved end) as Q1, sum(case when reportingPeriod='Q2' then totalAchieved end) as Q2, sum(case when reportingPeriod='Q3' then totalAchieved end) as Q3, sum(case when reportingPeriod='Q4' then totalAchieved end) as Q4 from indicatorachievedcombined where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear + "' group by titleID "; } } String Q1 = ""; String Q2 = ""; String Q3 = ""; String Q4 = ""; conn.rs1 = conn.state1.executeQuery(getdata); //Q1 HSSFCell clQ1 = rwx.createCell(colpos); colpos++; HSSFCell clQ2 = rwx.createCell(colpos); colpos++; HSSFCell clQ3 = rwx.createCell(colpos); colpos++; HSSFCell clQ4 = rwx.createCell(colpos); colpos++; if (conn.rs1.next()) { if (conn.rs1.getString("Q1") != null) { if (!conn.rs1.getString("Q1").equals("")) { highestvalue = conn.rs1.getInt("Q1"); if (!ispercent.equals("")) { clQ1.setCellValue(conn.rs1.getInt("Q1") + ispercent); } else { clQ1.setCellValue(conn.rs1.getInt("Q1")); } } } if (conn.rs1.getString("Q2") != null) { if (!conn.rs1.getString("Q2").equals("")) { if (conn.rs1.getInt("Q2") > highestvalue) { highestvalue = conn.rs1.getInt("Q2"); } if (!ispercent.equals("")) { clQ2.setCellValue(conn.rs1.getInt("Q2") + ispercent); } else { clQ2.setCellValue(conn.rs1.getInt("Q2")); } } } if (conn.rs1.getString("Q3") != null) { if (!conn.rs1.getString("Q3").equals("")) { if (conn.rs1.getInt("Q3") > highestvalue) { highestvalue = conn.rs1.getInt("Q3"); } if (!ispercent.equals("")) { clQ3.setCellValue(conn.rs1.getInt("Q3") + ispercent); } else { clQ3.setCellValue(conn.rs1.getInt("Q3")); } } } if (conn.rs1.getString("Q4") != null) { if (!conn.rs1.getString("Q4").equals("")) { if (conn.rs1.getInt("Q4") > highestvalue) { highestvalue = conn.rs1.getInt("Q4"); } if (!ispercent.equals("")) { clQ4.setCellValue(conn.rs1.getInt("Q4") + ispercent); } else { clQ4.setCellValue(conn.rs1.getInt("Q4")); } } } } clQ2.setCellStyle(style2); clQ1.setCellStyle(style2); clQ3.setCellStyle(style2); clQ4.setCellStyle(style2); //====================================Annual figures======================= String percentageachievement = ""; String achievednonpercent = "No target / achieved value"; int curcol = colpos; String annualispercent = ""; int currentyearvalue = 0; int currentyearhighestqtr = 0; for (int yearval = selectedyear; yearval >= projectstartyear; yearval--) { HSSFCell clx = rwx.createCell(curcol); // System.out.println("******"+curcol); //separate cumulates + average with the rest if (conn.rs.getString("cumulative_chooser").equalsIgnoreCase("Cumulative") || conn.rs.getString("cumulative_chooser").equalsIgnoreCase("Average")) { String qry = ""; if (conn.rs.getString("percentage").equals("1")) { annualispercent = "%"; if (conn.rs.getString("tableidentifier").equals("2")) { //no gender thus its combined qry = " select ROUND(AVG(totalAchieved)) as y" + yearval + " from indicatorachievedcombined where financialyear='" + yearval + "' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } else { qry = " select ROUND(AVG((menAchieved + womenAchieved)/2)) as y" + yearval + " from indicatorachieved where financialyear='" + yearval + "' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } } //non percents else { //for cumulatives //check tabletype //1 is by gender //_____________CUMULATIVES______________ if (conn.rs.getString("cumulative_chooser").equalsIgnoreCase("Cumulative")) { if (conn.rs.getString("tableIdentifier").equals("1")) { qry = " select sum((menAchieved + womenAchieved)) as y" + yearval + " from indicatorachieved where financialyear='" + yearval + "' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } else { qry = " select sum(totalAchieved) as y" + yearval + " from indicatorachievedcombined where financialyear='" + yearval + "' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } //end of else of table identifier } //end of sum/cumulatives } //end of else of non percents System.out.println("@" + qry); conn.rs1 = conn.state1.executeQuery(qry); if (conn.rs1.next()) { // System.out.println("__"+conn.rs1.getString(1)); if (conn.rs1.getString(1) != null) { if (!conn.rs1.getString(1).equals("")) { if (annualispercent.equals("")) { clx.setCellValue(conn.rs1.getInt(1)); //achieved nonpercentage if (yearval == selectedyear) { if (annualtarget > 1) { achievednonpercent = "" + (int) conn.rs1.getInt(1) * 100 / (int) annualtarget + "%"; } } } else { clx.setCellValue(conn.rs1.getInt(1) + annualispercent); //do this for the cureent year if (yearval == selectedyear) { percentageachievement = conn.rs1.getInt(1) + annualispercent; } } } } } clx.setCellStyle(style2); } //end of cumulatives and percentages else if (conn.rs.getString("cumulative_chooser").equalsIgnoreCase("Highest")) { String qry = ""; if (conn.rs.getString("tableIdentifier").equals("1")) { //by gender and thus separate columns qry = " select sum(case when reportingPeriod='Q1' then (menAchieved + womenAchieved) end) as Q1,sum(case when reportingPeriod='Q2' then (menAchieved + womenAchieved) end) as Q2, sum(case when reportingPeriod='Q3' then (menAchieved + womenAchieved) end) as Q3, sum(case when reportingPeriod='Q4' then (menAchieved + womenAchieved) end) as Q4 from indicatorachieved where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + yearval + "' group by titleID "; } else { qry = " select sum(case when reportingPeriod='Q1' then totalAchieved end) as Q1, sum(case when reportingPeriod='Q2' then totalAchieved end) as Q2, sum(case when reportingPeriod='Q3' then totalAchieved end) as Q3, sum(case when reportingPeriod='Q4' then totalAchieved end) as Q4 from indicatorachievedcombined where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + yearval + "' group by titleID "; } int highestqtr = 0; //excecute query conn.rs1 = conn.state1.executeQuery(qry); while (conn.rs1.next()) { // if (conn.rs1.getString("Q1") != null) { if (!conn.rs1.getString("Q1").equals("")) { if (conn.rs1.getInt("Q1") > highestqtr) { highestqtr = conn.rs1.getInt("Q1"); } } } else if (conn.rs1.getString("Q2") != null) { if (!conn.rs1.getString("Q2").equals("")) { if (conn.rs1.getInt("Q2") > highestqtr) { highestqtr = conn.rs1.getInt("Q2"); } } } else if (conn.rs1.getString("Q3") != null) { if (!conn.rs1.getString("Q3").equals("")) { if (conn.rs1.getInt("Q3") > highestqtr) { highestqtr = conn.rs1.getInt("Q3"); } } } else if (conn.rs1.getString("Q4") != null) { if (!conn.rs1.getString("Q4").equals("")) { if (conn.rs1.getInt("Q4") > highestqtr) { highestqtr = conn.rs1.getInt("Q4"); } } } } if (highestqtr > 0) { clx.setCellValue(highestqtr); } else { clx.setCellValue(""); } //achieved nonpercentage if (yearval == selectedyear) { if (annualtarget != 1) { achievednonpercent = "" + (int) highestqtr * 100 / (int) annualtarget + "%"; System.out.println(highestqtr + " / " + annualtarget + "___" + (int) highestqtr * 100 / (int) (annualtarget) + "%"); } } clx.setCellStyle(style2); } // end of highest else if (conn.rs.getString("cumulative_chooser").equalsIgnoreCase("OLMIS") || conn.rs.getString("cumulative_chooser").equalsIgnoreCase("Last Reported")) { String qry = ""; //get data for the last input quarter if (yearval == selectedyear) { //get data for that quarter if (conn.rs.getString("tableidentifier").equals("2")) { //no gender qry = " select sum((totalAchieved)) as y" + yearval + " from indicatorachievedcombined where financialyear='" + yearval + "' and reportingPeriod='" + selectedQTR + "' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } else { qry = " select sum((menAchieved + womenAchieved)) as y" + yearval + " from indicatorachieved where financialyear='" + yearval + "' and reportingPeriod='" + selectedQTR + "' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } } else { if (conn.rs.getString("tableidentifier").equals("2")) { //no gender qry = " select sum((totalAchieved)) as y" + yearval + " from indicatorachievedcombined where financialyear='" + yearval + "' and reportingPeriod='Q4' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } else { qry = " select sum((menAchieved + womenAchieved)) as y" + yearval + " from indicatorachieved where financialyear='" + yearval + "' and reportingPeriod='Q4' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } } //execute the query conn.rs1 = conn.state1.executeQuery(qry); if (conn.rs1.next()) { // System.out.println("__"+conn.rs1.getString(1)); if (conn.rs1.getString(1) != null) { if (!conn.rs1.getString(1).equals("")) { clx.setCellValue(conn.rs1.getInt(1)); //this is for percentage purpose if (selectedyear == yearval) { currentyearvalue = conn.rs1.getInt(1); } } } } //achieved nonpercentage if (yearval == selectedyear) { if (annualtarget != 1) { achievednonpercent = "" + (int) currentyearvalue * 100 / (int) annualtarget + "%"; } } clx.setCellStyle(style2); } //end of olmis and last reported indicators curcol++; } //end of for loop HSSFCell clx = rwx.createCell(curcol); if (conn.rs.getInt("percentage") == 1) { clx.setCellValue(percentageachievement); } else { clx.setCellValue(achievednonpercent); } clx.setCellStyle(style2); rwx.setHeightInPoints(42); rownumber++; count++; colpos = 0; } for (int e = 0; e < 13; e++) { //shet.autoSizeColumn(e); } //Made my life veery simple... shet.setDisplayGridlines(false); shet.createFreezePane(2, 3); if (conn.rs != null) { conn.rs.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.state1 != null) { conn.state1.close(); } if (conn.state2 != null) { conn.state2.close(); } // write it as an excel attachment ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PPMPREPORT_" + selectedyear + "_" + selectedQTR + "_gen_on_" + generationtime + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } finally { } }
From source file:quickreports.masterlist.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods.// w w w. j ava2 s . c o m * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { response.setContentType("text/html;charset=UTF-8"); //PrintWriter out = response.getWriter(); /* TODO output your page here. You may use following sample code. */ //______________________________________________________________________________________ // CREATE THE WORKSHEETS //______________________________________________________________________________________ HSSFWorkbook wb = new HSSFWorkbook(); HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Cambria"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont font2 = wb.createFont(); font2.setFontName("Cambria"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); HSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_LEFT); HSSFCellStyle stylesum = wb.createCellStyle(); stylesum.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylesum.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylesum.setBorderTop(HSSFCellStyle.BORDER_THIN); stylesum.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylesum.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylesum.setBorderRight(HSSFCellStyle.BORDER_THIN); stylesum.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); stylex.setFont(fontx); stylex.setWrapText(true); stylesum.setFont(fontx); stylesum.setWrapText(true); HSSFSheet shet = wb.createSheet("Masterlist"); String year = ""; if (request.getParameter("year") != null) { year = request.getParameter("year"); } dbConn conn = new dbConn(); //========Query 1================= HSSFRow rw0 = shet.createRow(1); HSSFCell cell = rw0.createCell(0); cell.setCellValue("APHIAPLUS Nuru Ya Bonde Supported Sites Summary " + year); cell.setCellStyle(style); shet.addMergedRegion(new CellRangeAddress(1, 1, 0, 3)); int count1 = 3; String qry1 = "call rpt_masterlist_summary('2015-10-01','2016-09-30','" + year + "')"; conn.rs = conn.st.executeQuery(qry1); ResultSetMetaData metaData = conn.rs.getMetaData(); int columnCount = metaData.getColumnCount(); ArrayList mycolumns1 = new ArrayList(); while (conn.rs.next()) { if (count1 == 3) { //header rows HSSFRow rw = shet.createRow(count1); rw.setHeightInPoints(26); for (int i = 1; i <= columnCount; i++) { mycolumns1.add(metaData.getColumnLabel(i)); HSSFCell cell0 = rw.createCell(i - 1); cell0.setCellValue(metaData.getColumnLabel(i)); cell0.setCellStyle(stylex); //create row header } //end of for loop count1++; } //end of if //data rows HSSFRow rw = shet.createRow(count1); for (int a = 0; a < columnCount; a++) { // System.out.print(mycolumns1.get(a) + ":" + conn.rs.getString("" + mycolumns1.get(a))); HSSFCell cell0 = rw.createCell(a); if (a > 0) { cell0.setCellValue(conn.rs.getInt(mycolumns1.get(a).toString())); } else { cell0.setCellValue(conn.rs.getString("" + mycolumns1.get(a))); } cell0.setCellStyle(style2); } // System.out.println(""); count1++; } HSSFRow rw01 = shet.createRow(count1 + 1); HSSFCell cell1 = rw01.createCell(0); cell1.setCellValue("APHIAPLUS Nuru Ya Bonde supported sites details " + year); cell1.setCellStyle(style); shet.addMergedRegion(new CellRangeAddress(count1 + 1, count1 + 1, 0, 3)); //========Query two====Facility Details============== String qry = "call rpt_masterlist('2015-10-01','2016-09-30','" + year + "')"; conn.rs = conn.st.executeQuery(qry); metaData = conn.rs.getMetaData(); columnCount = metaData.getColumnCount(); int count = count1 + 3; ArrayList mycolumns = new ArrayList(); while (conn.rs.next()) { if (count == (count1 + 3)) { //header rows HSSFRow rw = shet.createRow(count); rw.setHeightInPoints(26); for (int i = 1; i <= columnCount; i++) { mycolumns.add(metaData.getColumnLabel(i)); HSSFCell cell0 = rw.createCell(i - 1); cell0.setCellValue(metaData.getColumnLabel(i)); cell0.setCellStyle(stylex); //create row header } //end of for loop count++; } //end of if //data rows HSSFRow rw = shet.createRow(count); for (int a = 0; a < columnCount; a++) { //System.out.print(mycolumns.get(a) + ":" + conn.rs.getString("" + mycolumns.get(a))); HSSFCell cell0 = rw.createCell(a); if ((a >= 3 && a <= 14) || (a >= 18 && a <= 22) || (a >= 27 && a <= 29)) { cell0.setCellValue(conn.rs.getInt(mycolumns.get(a).toString())); } else { cell0.setCellValue(conn.rs.getString("" + mycolumns.get(a))); } cell0.setCellStyle(style2); } // System.out.println(""); count++; } //Autofreeze || Autofilter || Remove Gridlines || shet.setAutoFilter(new CellRangeAddress(count1 + 3, count - 1, 0, columnCount - 1)); //System.out.println("1,"+rowpos+",0,"+colposcopy); for (int i = 0; i <= columnCount; i++) { shet.autoSizeColumn(i); } shet.setDisplayGridlines(false); shet.createFreezePane(4, 14); if (conn.rs != null) { conn.rs.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st != null) { conn.st.close(); } if (conn.st1 != null) { conn.st1.close(); } IdGenerator IG = new IdGenerator(); String createdOn = IG.CreatedOn(); System.out.println("" + "MasterList_Gen_" + createdOn.trim() + ".xls"); ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=" + "MasterList_Gen_" + createdOn.trim() + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); }
From source file:teamdash.wbs.excel.WBSExcelWriter.java
License:Open Source License
public void addData(String tabName, TableColumnModel columns) { String safeTabName = cleanupTabName(tabName); HSSFSheet sheet = xls.createSheet(safeTabName); // WORKAROUND, for POI bug // ------ http://issues.apache.org/bugzilla/show_bug.cgi?id=30714 // We should have been able to use the next line... // sheet.setRowSumsBelow(false); sheet.setAlternativeExpression(false); createHeaderRow(sheet, columns);//from w w w .jav a 2 s . co m writeDataForNodes(sheet, 1, wbs.getRoot(), columns); autoSizeColumns(sheet, columns); sheet.createFreezePane(1, 1); }