List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle setWrapText
@Override public void setWrapText(boolean wrapped)
From source file:org.waterforpeople.mapping.dataexport.SurveyFormExporter.java
License:Open Source License
/** * Writes the survey as an XLS document/*from ww w . j av a 2s . c o m*/ */ private void writeSurvey(String title, File fileName, List<QuestionGroupDto> groupList, Map<QuestionGroupDto, List<QuestionDto>> questions) throws Exception { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); sheet.setColumnWidth(0, COL_WIDTH); sheet.setColumnWidth(1, COL_WIDTH); HSSFCellStyle headerStyle = wb.createCellStyle(); headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont headerFont = wb.createFont(); headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerStyle.setFont(headerFont); HSSFCellStyle questionStyle = wb.createCellStyle(); questionStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); questionStyle.setWrapText(true); HSSFCellStyle depStyle = wb.createCellStyle(); depStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont depFont = wb.createFont(); depFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); depFont.setItalic(true); depStyle.setFont(depFont); int curRow = 0; HSSFRow row = sheet.createRow(curRow++); sheet.addMergedRegion(new CellRangeAddress(curRow - 1, curRow - 1, 0, 1)); createCell(row, 0, title, headerStyle); row = sheet.createRow(curRow++); createCell(row, 0, QUESTION_HEADER, headerStyle); createCell(row, 1, RESPONSE_HEADER, headerStyle); Long count = 1L; if (questions != null) { for (int i = 0; i < groupList.size(); i++) { HSSFRow groupHeaderRow = sheet.createRow(curRow++); sheet.addMergedRegion(new CellRangeAddress(curRow - 1, curRow - 1, 0, 1)); createCell(groupHeaderRow, 0, groupList.get(i).getDisplayName(), headerStyle); for (QuestionDto q : questions.get(groupList.get(i))) { int questionStartRow = curRow; HSSFRow tempRow = sheet.createRow(curRow++); if (q.getQuestionDependency() != null) { // if there is a dependency, add a row about not // answering unless the dependency is satisfied sheet.addMergedRegion(new CellRangeAddress(curRow - 1, curRow - 1, 0, 1)); Long qNum = idToNumberMap.get(q.getQuestionDependency().getQuestionId()); createCell(tempRow, 0, DEP_HEAD + q.getQuestionDependency().getAnswerValue() + DEP_HEAD_TO + "Q" + qNum, depStyle); tempRow = sheet.createRow(curRow++); questionStartRow = curRow; } createCell(tempRow, 0, (count++) + ". " + formText(q.getText(), q.getTranslationMap()), questionStyle); if (q.getOptionContainerDto() != null && q.getOptionContainerDto().getOptionsList() != null) { for (QuestionOptionDto opt : q.getOptionContainerDto().getOptionsList()) { tempRow = sheet.createRow(curRow++); createCell(tempRow, 1, formText(opt.getText(), opt.getTranslationMap()) + SMALL_BLANK, null); } sheet.addMergedRegion(new CellRangeAddress(questionStartRow, curRow - 1, 0, 0)); } else { createCell(tempRow, 1, BLANK, null); } } } } FileOutputStream fileOut = new FileOutputStream(fileName); wb.write(fileOut); fileOut.close(); }
From source file:poi.hssf.usermodel.examples.InCellLists.java
License:Apache License
/** * Inserts a list of plain items - that is items that are neither * numbered or bulleted - into a single cell. * * @param workbook A reference to the HSSFWorkbook that 'contains' the * cell.//from w w w. j a va 2 s .co m * @param listItems An ArrayList whose elements encapsulate the text for * the list's items. * @param cell An instance of the HSSFCell class that encapsulates a * reference to the spreadsheet cell into which the list * will be written. */ public void listInCell(HSSFWorkbook workbook, ArrayList<String> listItems, HSSFCell cell) { StringBuffer buffer = new StringBuffer(); HSSFCellStyle wrapStyle = workbook.createCellStyle(); wrapStyle.setWrapText(true); for (String listItem : listItems) { buffer.append(listItem); buffer.append("\n"); } // The StringBuffer's contents are the source for the contents // of the cell. cell.setCellValue(new HSSFRichTextString(buffer.toString().trim())); cell.setCellStyle(wrapStyle); }
From source file:poi.hssf.usermodel.examples.InCellLists.java
License:Apache License
/** * Inserts a numbered list into a single cell. * * @param workbook A reference to the HSSFWorkbook that 'contains' the * cell.//ww w . j a va 2 s. c o m * @param listItems An ArrayList whose elements encapsulate the text for * the lists items. * @param cell An instance of the HSSFCell class that encapsulates a * reference to the spreadsheet cell into which the list * will be written. * @param startingValue A primitive int containing the number for the first * item in the list. * @param increment A primitive int containing the value that should be used * to calculate subsequent item numbers. */ public void numberedListInCell(HSSFWorkbook workbook, ArrayList<String> listItems, HSSFCell cell, int startingValue, int increment) { StringBuffer buffer = new StringBuffer(); int itemNumber = startingValue; // Note that again, an HSSFCellStye object is required and that // it's wrap text property should be set to 'true' HSSFCellStyle wrapStyle = workbook.createCellStyle(); wrapStyle.setWrapText(true); // Note that the basic method is identical to the listInCell() method // with one difference; a number prefixed to the items text. for (String listItem : listItems) { buffer.append(String.valueOf(itemNumber) + ". "); buffer.append(listItem); buffer.append("\n"); itemNumber += increment; } // The StringBuffer's contents are the source for the contents // of the cell. cell.setCellValue(new HSSFRichTextString(buffer.toString().trim())); cell.setCellStyle(wrapStyle); }
From source file:poi.hssf.usermodel.examples.InCellLists.java
License:Apache License
/** * Insert a bulleted list into a cell.//from ww w. j ava 2 s. co m * * @param workbook A reference to the HSSFWorkbook that 'contains' the * cell. * @param listItems An ArrayList whose elements encapsulate the text for * the lists items. * @param cell An instance of the HSSFCell class that encapsulates a * reference to the spreadsheet cell into which the list * will be written. */ public void bulletedListInCell(HSSFWorkbook workbook, ArrayList<String> listItems, HSSFCell cell) { StringBuffer buffer = new StringBuffer(); // Note that again, an HSSFCellStye object is required and that // it's wrap text property should be set to 'true' HSSFCellStyle wrapStyle = workbook.createCellStyle(); wrapStyle.setWrapText(true); // Note that the basic method is identical to the listInCell() method // with one difference; the bullet character prefixed to the items text. for (String listItem : listItems) { buffer.append(InCellLists.BULLET_CHARACTER + " "); buffer.append(listItem); buffer.append("\n"); } // The StringBuffer's contents are the source for the contents // of the cell. cell.setCellValue(new HSSFRichTextString(buffer.toString().trim())); cell.setCellStyle(wrapStyle); }
From source file:poi.hssf.usermodel.examples.InCellLists.java
License:Apache License
/** * Insert a multi-level list into a cell. * * @param workbook A reference to the HSSFWorkbook that 'contains' the * cell./*from w w w .ja v a 2 s .com*/ * @param multiLevelListItems An ArrayList whose elements contain instances * of the MultiLevelListItem class. Each element * encapsulates the text for the high level item * along with an ArrayList. Each element of this * ArrayList encapsulates the text for a lower * level item. * @param cell An instance of the HSSFCell class that encapsulates a * reference to the spreadsheet cell into which the list * will be written. */ public void multiLevelListInCell(HSSFWorkbook workbook, ArrayList<MultiLevelListItem> multiLevelListItems, HSSFCell cell) { StringBuffer buffer = new StringBuffer(); ArrayList<String> lowerLevelItems = null; // Note that again, an HSSFCellStye object is required and that // it's wrap text property should be set to 'true' HSSFCellStyle wrapStyle = workbook.createCellStyle(); wrapStyle.setWrapText(true); // Step through the ArrayList of MultilLevelListItem instances. for (MultiLevelListItem multiLevelListItem : multiLevelListItems) { // For each element in the ArrayList, get the text for the high // level list item...... buffer.append(multiLevelListItem.getItemText()); buffer.append("\n"); // and then an ArrayList whose elements encapsulate the text // for the lower level list items. lowerLevelItems = multiLevelListItem.getLowerLevelItems(); if (!(lowerLevelItems == null) && !(lowerLevelItems.isEmpty())) { for (String item : lowerLevelItems) { buffer.append(InCellLists.TAB); buffer.append(item); buffer.append("\n"); } } } // The StringBuffer's contents are the source for the contents // of the cell. cell.setCellValue(new HSSFRichTextString(buffer.toString().trim())); cell.setCellStyle(wrapStyle); }
From source file:poi.hssf.usermodel.examples.InCellLists.java
License:Apache License
/** * Insert a multi-level list into a cell. * * @param workbook A reference to the HSSFWorkbook that 'contains' the * cell./*from w w w. ja v a 2s . c om*/ * @param multiLevelListItems An ArrayList whose elements contain instances * of the MultiLevelListItem class. Each element * encapsulates the text for the high level item * along with an ArrayList. Each element of this * ArrayList encapsulates the text for a lower * level item. * @param cell An instance of the HSSFCell class that encapsulates a * reference to the spreadsheet cell into which the list * will be written. * @param highLevelStartingValue A primitive int containing the number * for the first high level item in the list. * @param highLevelIncrement A primitive int containing the value that * should be used to calculate the number of * subsequent high level item. * @param lowLevelStartingValue A primitive int will containing the number * for the first low level item associated * with a high level item. * @param lowLevelIncrement A primitive int containing the value that * should be used to calculate the number of * subsequent low level item. */ public void multiLevelNumberedListInCell(HSSFWorkbook workbook, ArrayList<MultiLevelListItem> multiLevelListItems, HSSFCell cell, int highLevelStartingValue, int highLevelIncrement, int lowLevelStartingValue, int lowLevelIncrement) { StringBuffer buffer = new StringBuffer(); int highLevelItemNumber = highLevelStartingValue; int lowLevelItemNumber = 0; ArrayList<String> lowerLevelItems = null; // Note that again, an HSSFCellStye object is required and that // it's wrap text property should be set to 'true' HSSFCellStyle wrapStyle = workbook.createCellStyle(); wrapStyle.setWrapText(true); // Step through the ArrayList of MultilLevelListItem instances. for (MultiLevelListItem multiLevelListItem : multiLevelListItems) { // For each element in the ArrayList, get the text for the high // level list item...... buffer.append(String.valueOf(highLevelItemNumber)); buffer.append(". "); buffer.append(multiLevelListItem.getItemText()); buffer.append("\n"); // and then an ArrayList whose elements encapsulate the text // for the lower level list items. lowerLevelItems = multiLevelListItem.getLowerLevelItems(); if (!(lowerLevelItems == null) && !(lowerLevelItems.isEmpty())) { lowLevelItemNumber = lowLevelStartingValue; for (String item : lowerLevelItems) { buffer.append(InCellLists.TAB); buffer.append(String.valueOf(highLevelItemNumber)); buffer.append("."); buffer.append(String.valueOf(lowLevelItemNumber)); buffer.append(" "); buffer.append(item); buffer.append("\n"); lowLevelItemNumber += lowLevelIncrement; } } highLevelItemNumber += highLevelIncrement; } // The StringBuffer's contents are the source for the contents // of the cell. cell.setCellValue(new HSSFRichTextString(buffer.toString().trim())); cell.setCellStyle(wrapStyle); }
From source file:poi.hssf.usermodel.examples.InCellLists.java
License:Apache License
/** * Insert a bulleted multi-level list into a cell. * * @param workbook A reference to the HSSFWorkbook that 'contains' the * cell.//from w w w . j a va2 s.co m * @param multiLevelListItems An ArrayList whose elements contain instances * of the MultiLevelListItem class. Each element * encapsulates the text for the high level item * along with an ArrayList. Each element of this * ArrayList encapsulates the text for a lower * level item. * @param cell An instance of the HSSFCell class that encapsulates a * reference to the spreadsheet cell into which the list * will be written. */ public void multiLevelBulletedListInCell(HSSFWorkbook workbook, ArrayList<MultiLevelListItem> multiLevelListItems, HSSFCell cell) { StringBuffer buffer = new StringBuffer(); ArrayList<String> lowerLevelItems = null; // Note that again, an HSSFCellStye object is required and that // it's wrap text property should be set to 'true' HSSFCellStyle wrapStyle = workbook.createCellStyle(); wrapStyle.setWrapText(true); // Step through the ArrayList of MultilLevelListItem instances. for (MultiLevelListItem multiLevelListItem : multiLevelListItems) { // For each element in the ArrayList, get the text for the high // level list item...... buffer.append(InCellLists.BULLET_CHARACTER); buffer.append(" "); buffer.append(multiLevelListItem.getItemText()); buffer.append("\n"); // and then an ArrayList whose elements encapsulate the text // for the lower level list items. lowerLevelItems = multiLevelListItem.getLowerLevelItems(); if (!(lowerLevelItems == null) && !(lowerLevelItems.isEmpty())) { for (String item : lowerLevelItems) { buffer.append(InCellLists.TAB); buffer.append(InCellLists.BULLET_CHARACTER); buffer.append(" "); buffer.append(item); buffer.append("\n"); } } } // The StringBuffer's contents are the source for the contents // of the cell. cell.setCellValue(new HSSFRichTextString(buffer.toString().trim())); cell.setCellStyle(wrapStyle); }
From source file:poi.hssf.usermodel.examples.NewLinesInCells.java
License:Apache License
public static void main(String[] args) throws IOException { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet();/* w w w.j av a2s. c o m*/ HSSFRow r = null; HSSFCell c = null; HSSFCellStyle cs = wb.createCellStyle(); HSSFFont f2 = wb.createFont(); cs = wb.createCellStyle(); cs.setFont(f2); // Word Wrap MUST be turned on cs.setWrapText(true); r = s.createRow(2); r.setHeight((short) 0x349); c = r.createCell(2); c.setCellType(HSSFCell.CELL_TYPE_STRING); c.setCellValue("Use \n with word wrap on to create a new line"); c.setCellStyle(cs); s.setColumnWidth(2, (int) ((50 * 8) / ((double) 1 / 20))); FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); }
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 . j a v a 2 s . 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 v a 2s.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 { } }