List of usage examples for org.apache.poi.ss.usermodel CellStyle setVerticalAlignment
void setVerticalAlignment(VerticalAlignment align);
From source file:reports.nutritionexcel.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {//from w w w . j av a 2 s .c om response.setContentType("text/html;charset=UTF-8"); session = request.getSession(); dbConn conn = new dbConn(); //get the existing data for the month, year and facility that is already on session String month = ""; String year = ""; String facil = ""; String form = "nutrition"; //===================================================================================================== year = "2015"; month = "5"; String county = ""; String header = ""; String reportType = ""; if (request.getParameter("reportType") != null) { reportType = request.getParameter("reportType"); } String reportDuration = ""; if (request.getParameter("reportDuration") != null) { reportDuration = request.getParameter("reportDuration"); } if (request.getParameter("year") != null) { year = request.getParameter("year"); } if (request.getParameter("facility") != null && reportType.equals("2")) { facil = request.getParameter("facility"); String getfacil = "select SubPartnerNom,CentreSanteId as mflcode from subpartnera where SubPartnerID='" + facil + "'"; conn.rs = conn.st.executeQuery(getfacil); while (conn.rs.next()) { header += " FACILITY : " + conn.rs.getString(1).toUpperCase() + " MFL CODE : " + conn.rs.getString(2) + " "; } } if (request.getParameter("county") != null && reportType.equals("2")) { county = request.getParameter("county"); String getcounty = "select County from county where CountyID='" + county + "'"; conn.rs = conn.st.executeQuery(getcounty); while (conn.rs.next()) { header += " COUNTY : " + conn.rs.getString(1).toUpperCase() + " "; } } if (request.getParameter("month") != null && reportDuration.equals("4")) { month = request.getParameter("month"); String getmonth = "select name as monthname from month where id='" + month + "'"; conn.rs = conn.st.executeQuery(getmonth); while (conn.rs.next()) { header += " MONTH : " + conn.rs.getString(1).toUpperCase() + " "; } } header += " YEAR : " + year + ""; String facilitywhere = ""; String yearwhere = ""; String monthwhere = ""; String countywhere = ""; String duration = ""; String semi_annual = ""; String quarter = ""; //================================================================================================== //XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX int yearcopy = Integer.parseInt(year); // reportType="2"; // year=2015; // reportDuration="3"; String yearmonth = "" + year; int prevYear = yearcopy - 1; int maxYearMonth = 0; int monthcopy = 0; // GET REPORT DURATION============================================ if (reportDuration.equals("1")) { yearmonth += "_AnnualReport"; duration = " " + form + ".yearmonth BETWEEN " + prevYear + "10 AND " + year + "09"; } else if (reportDuration.equals("2")) { semi_annual = request.getParameter("semi_annual"); // semi_annual="2"; if (semi_annual.equals("1")) { yearmonth = prevYear + "_Oct_" + year + "_Mar"; duration = " " + form + ".yearmonth BETWEEN " + prevYear + "10 AND " + year + "03"; } else { yearmonth += "_Apr_Sep"; duration = " " + form + ".yearmonth BETWEEN " + year + "04 AND " + year + "09"; } } else if (reportDuration.equals("3")) { String startMonth, endMonth; quarter = request.getParameter("quarter"); // quarter="3"; String getMonths = "SELECT months,name FROM quarter WHERE id='" + quarter + "'"; conn.rs = conn.st.executeQuery(getMonths); if (conn.rs.next() == true) { String months[] = conn.rs.getString(1).split(","); startMonth = months[0]; endMonth = months[2]; if (quarter.equals("1")) { duration = " " + form + ".yearmonth BETWEEN " + prevYear + "" + startMonth + " AND " + prevYear + "" + endMonth; yearmonth = prevYear + "_" + conn.rs.getString(2); } else { yearmonth = year + "_" + conn.rs.getString(2); duration = " " + form + ".yearmonth BETWEEN " + year + "" + startMonth + " AND " + year + "" + endMonth; } } } else if (reportDuration.equals("4")) { monthcopy = Integer.parseInt(request.getParameter("month")); // month=5; if (monthcopy >= 10) { yearmonth = prevYear + "_" + month; duration = " " + form + ".yearmonth=" + prevYear + "" + month; } else { duration = " " + form + ".yearmonth=" + year + "0" + month; yearmonth = year + "_(" + month + ")"; } } else { duration = ""; } //====================================================================== //XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX String getexistingdata = ""; if (!county.equals("")) { countywhere = " and countyid = '" + county + "'"; } if (!facil.equals("") && reportType.equalsIgnoreCase("2")) { facilitywhere = " and " + form + ".SubPartnerID = '" + facil + "'"; } //String joinedwhwere=" where 1=1 "+yearwhere+" && "+duration; String joinedwhwere = " where 1=1 " + facilitywhere + " " + yearwhere + " && " + duration; //===================================================================================================== //===================================================================================================== //===================================================================================================== //______________________________________________________________________________________ // NOW 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); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 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_CENTER); stylex.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); fontx.setColor((short) 0000); fontx.setBoldweight((short) 07); stylex.setFont(fontx); stylex.setWrapText(true); HSSFSheet shet = wb.createSheet(form); //create headers for that worksheet HSSFRow rw = shet.createRow(0); rw.setHeightInPoints(25); HSSFCell cl0 = rw.createCell(0); cl0.setCellValue("3.1.9: Nutrition"); cl0.setCellStyle(stylex); for (int a = 1; a <= 6; a++) { HSSFCell clx = rw.createCell(a); clx.setCellValue(""); clx.setCellStyle(stylex); } HSSFRow rw1 = shet.createRow(1); rw1.setHeightInPoints(23); HSSFCell cl = rw1.createCell(0); cl.setCellValue(header); cl.setCellStyle(stylex); for (int a = 1; a <= 6; a++) { HSSFCell clx = rw1.createCell(a); clx.setCellValue(""); clx.setCellStyle(stylex); } HSSFRow rw2 = shet.createRow(2); rw2.setHeightInPoints(23); HSSFCell cl3 = rw2.createCell(0); cl3.setCellValue("3.1.9.2 Population Based Nutrition Service Delivery"); cl3.setCellStyle(stylex); HSSFCell cl3a = rw2.createCell(1); cl3a.setCellValue(""); cl3a.setCellStyle(stylex); HSSFCell cl31 = rw2.createCell(2); cl31.setCellValue( "Number of people trained in child health care and nutrition through USG-supported health area programs"); cl31.setCellStyle(style2); for (int a = 3; a <= 5; a++) { HSSFCell clx = rw2.createCell(a); clx.setCellValue(""); clx.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(2, 11, 0, 1)); shet.addMergedRegion(new CellRangeAddress(2, 2, 2, 5)); shet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6)); shet.addMergedRegion(new CellRangeAddress(1, 1, 0, 6)); shet.addMergedRegion(new CellRangeAddress(12, 20, 0, 0)); shet.setColumnWidth(0, 3000); shet.setColumnWidth(1, 3000); shet.setColumnWidth(2, 16000); shet.setColumnWidth(3, 6900); shet.setColumnWidth(4, 3000); shet.setColumnWidth(5, 3000); getexistingdata = "select sum(MCHCCNtrTM) as MCHCCNtrTM, MCHCCNtrTMC, sum(MCHCCNtrTF) as MCHCCNtrTF, MCHCCNtrTFC, sum(MCHCCNtrTT) as MCHCCNtrTT, MCHCCNtrTTC, sum(MCHNtrnCHWTrain) as MCHNtrnCHWTrain, sum(MCHNutChRch) as MCHNutChRch, sum(MCHNtrnWasted) as MCHNtrnWasted, sum(MCHNtrnUnderweight) as MCHNtrnUnderweight, sum(MCHChild5D) as MCHChild5D, sum(MCHNtrnHealthFacility) as MCHNtrnHealthFacility, sum(MCHVaccVitA) as MCHVaccVitA, sum(MCHNtrnFoodOVC) as MCHNtrnFoodOVC, sum(MCHNtrnFoodPLHIV) as MCHNtrnFoodPLHIV, sum(MCHNtrnFood) as MCHNtrnFood, sum(C51DCM) as C51DCM, sum(C51DCF) as C51DCF, sum(C51DC) as C51DC, sum(C51DAM) as C51DAM, sum(C51DAF) as C51DAF, sum(C51DA) as C51DA, sum(C51DP) as C51DP, sum(C51DMT) as C51DMT, sum(C51DFT) as C51DFT, sum(C51DT) as C51DT from " + form + " join ( subpartnera join (district join county on county.CountyID=district.CountyID ) on district.DistrictID = subpartnera.DistrictID ) on " + form + ".SubPartnerID = subpartnera.SubPartnerID " + joinedwhwere + " "; //System.out.println(getexistingdata); String MCHCCNtrTM = ""; String MCHCCNtrTF = ""; String MCHCCNtrTT = ""; String MCHCCNtrTMC = "0"; String MCHCCNtrTFC = "0"; String MCHCCNtrTTC = "0"; String MCHCCNtrTMCH = "0"; String MCHCCNtrTFCH = "0"; String MCHCCNtrTTCH = "0"; String MCHNtrnCHWTrain = ""; String MCHNutChRch = ""; String MCHNtrnWasted = ""; String MCHNtrnUnderweight = ""; String MCHChild5D = ""; String MCHNtrnHealthFacility = ""; String MCHVaccVitA = ""; String MCHNtrnFoodOVC = ""; String MCHNtrnFoodPLHIV = ""; String MCHNtrnFood = ""; String C51DCM = ""; String C51DCF = ""; String C51DC = ""; String C51DAM = ""; String C51DAF = ""; String C51DA = ""; String C51DP = ""; String C51DMT = ""; String C51DFT = ""; String C51DT = ""; String distid = ""; if (session.getAttribute("subcountyid") != null) { distid = session.getAttribute("subcountyid").toString(); } int counter = 0; conn.rs = conn.st.executeQuery(getexistingdata); while (conn.rs.next()) { //now check if form was updated and if its one month after data entry //now load the column values here MCHCCNtrTM = conn.rs.getString("MCHCCNtrTM"); if (MCHCCNtrTM == null) { MCHCCNtrTM = ""; } MCHCCNtrTF = conn.rs.getString("MCHCCNtrTF"); if (MCHCCNtrTF == null) { MCHCCNtrTF = ""; } MCHCCNtrTT = conn.rs.getString("MCHCCNtrTT"); if (MCHCCNtrTT == null) { MCHCCNtrTT = ""; } MCHCCNtrTMC = conn.rs.getString("MCHCCNtrTMC"); if (MCHCCNtrTMC == null) { MCHCCNtrTMC = "0"; } MCHCCNtrTFC = conn.rs.getString("MCHCCNtrTFC"); if (MCHCCNtrTFC == null) { MCHCCNtrTFC = "0"; } MCHCCNtrTTC = conn.rs.getString("MCHCCNtrTTC"); if (MCHCCNtrTTC == null) { MCHCCNtrTTC = "0"; } MCHNtrnCHWTrain = conn.rs.getString("MCHNtrnCHWTrain"); if (MCHNtrnCHWTrain == null) { MCHNtrnCHWTrain = ""; } MCHNutChRch = conn.rs.getString("MCHNutChRch"); if (MCHNutChRch == null) { MCHNutChRch = ""; } MCHNtrnWasted = conn.rs.getString("MCHNtrnWasted"); if (MCHNtrnWasted == null) { MCHNtrnWasted = ""; } MCHNtrnUnderweight = conn.rs.getString("MCHNtrnUnderweight"); if (MCHNtrnUnderweight == null) { MCHNtrnUnderweight = ""; } MCHChild5D = conn.rs.getString("MCHChild5D"); if (MCHChild5D == null) { MCHChild5D = ""; } MCHNtrnHealthFacility = conn.rs.getString("MCHNtrnHealthFacility"); if (MCHNtrnHealthFacility == null) { MCHNtrnHealthFacility = ""; } MCHVaccVitA = conn.rs.getString("MCHVaccVitA"); if (MCHVaccVitA == null) { MCHVaccVitA = ""; } MCHNtrnFoodOVC = conn.rs.getString("MCHNtrnFoodOVC"); if (MCHNtrnFoodOVC == null) { MCHNtrnFoodOVC = ""; } MCHNtrnFoodPLHIV = conn.rs.getString("MCHNtrnFoodPLHIV"); if (MCHNtrnFoodPLHIV == null) { MCHNtrnFoodPLHIV = ""; } MCHNtrnFood = conn.rs.getString("MCHNtrnFood"); if (MCHNtrnFood == null) { MCHNtrnFood = ""; } C51DCM = conn.rs.getString("C51DCM"); if (C51DCM == null) { C51DCM = ""; } C51DCF = conn.rs.getString("C51DCF"); if (C51DCF == null) { C51DCF = ""; } C51DC = conn.rs.getString("C51DC"); if (C51DC == null) { C51DC = ""; } C51DAM = conn.rs.getString("C51DAM"); if (C51DAM == null) { C51DAM = ""; } C51DAF = conn.rs.getString("C51DAF"); if (C51DAF == null) { C51DAF = ""; } C51DA = conn.rs.getString("C51DA"); if (C51DA == null) { C51DA = ""; } C51DP = conn.rs.getString("C51DP"); if (C51DP == null) { C51DP = ""; } C51DMT = conn.rs.getString("C51DMT"); if (C51DMT == null) { C51DMT = ""; } C51DFT = conn.rs.getString("C51DFT"); if (C51DFT == null) { C51DFT = ""; } C51DT = conn.rs.getString("C51DT"); if (C51DT == null) { C51DT = ""; } } String createdtable = ""; if (1 == 1) { int r = 3; HSSFCell cl3d = rw2.createCell(6); cl3d.setCellValue(MCHCCNtrTTC); cl3d.setCellStyle(style2); if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(2); clx.setCellValue("Number of Men"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(6); clx1.setCellValue(MCHCCNtrTM); clx1.setCellStyle(style2); for (int a = 3; a <= 5; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); } r++; } //================================================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(2); clx.setCellValue("Number of Women"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(6); clx1.setCellValue(MCHCCNtrTF); clx1.setCellStyle(style2); for (int a = 3; a <= 5; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); } r++; } if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(2); clx.setCellValue( "Total Number of people trained in child health care and nutrition through USG-supported health area programs"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(6); clx1.setCellValue(MCHCCNtrTT); clx1.setCellStyle(style2); for (int a = 3; a <= 5; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); } r++; } //============================================================================================ if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(2); clx.setCellValue("Number of Community health workers trained in child health and/or nutrition"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(6); clx1.setCellValue(MCHNtrnCHWTrain); clx1.setCellStyle(style2); for (int a = 3; a <= 5; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); } r++; } //=========================================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(2); clx.setCellValue("Number of children reached by USG-supported nutrition programs"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(6); clx1.setCellValue(MCHNutChRch); clx1.setCellStyle(style2); for (int a = 3; a <= 5; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); } r++; } //=========================================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(2); clx.setCellValue( "Total number of children under five who are wasted (with weight for height Z score < - 2)"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(6); clx1.setCellValue(MCHNtrnWasted); clx1.setCellStyle(style2); for (int a = 3; a <= 5; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); } r++; } if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(2); clx.setCellValue( "Total number of children under five who are underweight (with weight for age Z score < - 2) (see Indicator"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(6); clx1.setCellValue(MCHNtrnUnderweight); clx1.setCellStyle(style2); for (int a = 3; a <= 5; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); } r++; } //=========================================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(2); clx.setCellValue("Total number of children under five years"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(6); clx1.setCellValue(MCHChild5D); clx1.setCellStyle(style2); for (int a = 3; a <= 5; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); } r++; } //=========================================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(2); clx.setCellValue( "Number of health facilities with established capacity to manage acute under-nutrition"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(6); clx1.setCellValue(MCHNtrnHealthFacility); clx1.setCellStyle(style2); for (int a = 3; a <= 5; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); } r++; } //=========================================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue("HIV and Nutrition"); clx0.setCellStyle(stylex); HSSFCell clx1a = rwx.createCell(1); clx1a.setCellValue(""); clx1a.setCellStyle(stylex); HSSFCell clx = rwx.createCell(2); clx.setCellValue( "Number of children under 5 years of age who received Vitamin A from USG-supported programs"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(6); clx1.setCellValue(MCHVaccVitA); clx1.setCellStyle(style2); for (int a = 3; a <= 5; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); //shet.addMergedRegion(new CellRangeAddress(2,11,0,0)); } r++; } //=========================================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(stylex); HSSFCell clx1a = rwx.createCell(1); clx1a.setCellValue("C2.3.D:"); clx1a.setCellStyle(stylex); HSSFCell clx = rwx.createCell(2); clx.setCellValue( "Number of HIV positive clinically malnourished clients who received therapeutic and/or supplementary food < 18"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(6); clx1.setCellValue(MCHNtrnFoodOVC); clx1.setCellStyle(style2); for (int a = 3; a <= 5; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); shet.addMergedRegion(new CellRangeAddress(r, r + 2, 1, 1)); } r++; } //=========================================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(stylex); HSSFCell clx1a = rwx.createCell(1); clx1a.setCellValue(""); clx1a.setCellStyle(stylex); HSSFCell clx = rwx.createCell(2); clx.setCellValue( "Number of HIV positive clinically malnourished clients who received therapeutic and/or supplementary food 18+ (PLHIV)"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(6); clx1.setCellValue(MCHNtrnFoodPLHIV); clx1.setCellStyle(style2); for (int a = 3; a <= 5; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); // shet.addMergedRegion(new CellRangeAddress(r,r+3,1,1)); } r++; } //=========================================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(stylex); HSSFCell clx1a = rwx.createCell(1); clx1a.setCellValue(""); clx1a.setCellStyle(stylex); HSSFCell clx = rwx.createCell(2); clx.setCellValue( "Number of HIV positive clinically malnourished clients who received therapeutic and/or supplementary food - Total"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(6); clx1.setCellValue(MCHNtrnFood); clx1.setCellStyle(style2); for (int a = 3; a <= 5; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); // shet.addMergedRegion(new CellRangeAddress(r,r+3,1,1)); } r++; } //=========================================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(stylex); HSSFCell clx1a = rwx.createCell(1); clx1a.setCellValue(""); clx1a.setCellStyle(stylex); HSSFCell clx = rwx.createCell(2); clx.setCellValue(""); clx.setCellStyle(style2); HSSFCell clx3 = rwx.createCell(3); clx3.setCellValue(""); clx3.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(6); clx1.setCellValue(""); clx1.setCellStyle(style2); String haeade[] = { "Male", "Female", "Total" }; for (int a = 4; a <= 6; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(haeade[a - 4]); clx2.setCellStyle(style2); //shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); // shet.addMergedRegion(new CellRangeAddress(r,r+3,1,1)); } shet.addMergedRegion(new CellRangeAddress(r, r, 2, 3)); r++; } //=========================================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(stylex); HSSFCell clx1a = rwx.createCell(1); clx1a.setCellValue("C5.1.D:"); clx1a.setCellStyle(stylex); HSSFCell clx1b = rwx.createCell(3); clx1b.setCellValue("< 18"); clx1b.setCellStyle(style2); HSSFCell clx = rwx.createCell(2); clx.setCellValue( "Number of eligible clients who received food and / or other nutrition Services"); clx.setCellStyle(style2); String haeade[] = { C51DCM, C51DCF, C51DC }; for (int a = 0; a < haeade.length; a++) { HSSFCell clx2 = rwx.createCell(a + 4); clx2.setCellValue(haeade[a]); clx2.setCellStyle(style2); //shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); // shet.addMergedRegion(new CellRangeAddress(r,r+3,1,1)); } //shet.addMergedRegion(new CellRangeAddress(r,r,2,3)); shet.addMergedRegion(new CellRangeAddress(r, r + 3, 1, 1)); shet.addMergedRegion(new CellRangeAddress(r, r + 3, 2, 2)); r++; } //=========================================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(stylex); HSSFCell clx1a = rwx.createCell(1); clx1a.setCellValue(""); clx1a.setCellStyle(stylex); HSSFCell clx1b = rwx.createCell(3); clx1b.setCellValue(">=18"); clx1b.setCellStyle(style2); HSSFCell clx = rwx.createCell(2); clx.setCellValue(""); clx.setCellStyle(style2); String haeade[] = { C51DAM, C51DAF, C51DA }; for (int a = 0; a < haeade.length; a++) { HSSFCell clx2 = rwx.createCell(a + 4); clx2.setCellValue(haeade[a]); clx2.setCellStyle(style2); //shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); // shet.addMergedRegion(new CellRangeAddress(r,r+3,1,1)); } //shet.addMergedRegion(new CellRangeAddress(r,r,2,3)); r++; } //=========================================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(stylex); HSSFCell clx1a = rwx.createCell(1); clx1a.setCellValue(""); clx1a.setCellStyle(stylex); HSSFCell clx1b = rwx.createCell(3); clx1b.setCellValue("Pregnant/Lactating (PMTCT 1.5)"); clx1b.setCellStyle(style2); HSSFCell clx = rwx.createCell(2); clx.setCellValue(""); clx.setCellStyle(style2); String haeade[] = { "", "", C51DP }; for (int a = 0; a < haeade.length; a++) { HSSFCell clx2 = rwx.createCell(a + 4); clx2.setCellValue(haeade[a]); clx2.setCellStyle(style2); //shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); // shet.addMergedRegion(new CellRangeAddress(r,r+3,1,1)); } shet.addMergedRegion(new CellRangeAddress(r, r, 4, 5)); r++; } //=========================================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(stylex); HSSFCell clx1a = rwx.createCell(1); clx1a.setCellValue(""); clx1a.setCellStyle(stylex); HSSFCell clx1b = rwx.createCell(3); clx1b.setCellValue("Total"); clx1b.setCellStyle(style2); HSSFCell clx = rwx.createCell(2); clx.setCellValue(""); clx.setCellStyle(style2); String haeade[] = { C51DMT, C51DFT, C51DT }; for (int a = 0; a < haeade.length; a++) { HSSFCell clx2 = rwx.createCell(a + 4); clx2.setCellValue(haeade[a]); clx2.setCellStyle(style2); //shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); // shet.addMergedRegion(new CellRangeAddress(r,r+3,1,1)); } //shet.addMergedRegion(new CellRangeAddress(r,r,2,3)); r++; } //=========================================================================================================== createdtable += header + "<br/><table border='1' style='border-color: #e5e5e5;margin-bottom: 3px;font-size:11;'><tr class='form-actions'>" + "<th rowspan='10' colspan='2'><b style='text-align:center;'>3.1.9.2 <br/> population-based Nutrition Service Delivery</b></th>" + "<td colspan='4'><b>Number of People trained in child health care and nutrition through USG-supported health area programs</b></td><td><b>" + MCHCCNtrTTC + "</b></td></tr>"; createdtable += "<tr><td colspan='4' style='text-align:left;'>No of Men </td><td>" + MCHCCNtrTM + "</td></tr>"; createdtable += "<tr><td colspan='4' style='text-align:left;'>No of Women </td><td>" + MCHCCNtrTF + "</td></tr>"; createdtable += "<tr><td colspan='4'><b>Total Number of people trained in child health care and nutrition through USG-supported health area programs</b></td><td>" + MCHCCNtrTT + "</td></tr>"; createdtable += "<tr><td colspan='4'><b>Number of Community health workers trained in child health and/or nutrition</b></td><td>" + MCHNtrnCHWTrain + "</td></tr>"; createdtable += "<tr><td colspan='4'><b>Number of children reached by USG-supported nutrition programs</b></td><td>" + MCHNutChRch + "</td></tr>"; createdtable += "<tr><td colspan='4'>Total number of children under five who are wasted (with weight for height Z score < - 2)</td><td>" + MCHNtrnWasted + "</td></tr>"; createdtable += "<tr><td colspan='4'>Total number of children under five who are underweight (with weight for age Z score < - 2) (SEE Indicator </td><td>" + MCHNtrnUnderweight + "</td></tr>"; createdtable += "<tr><td colspan='4'>Total number of children under five years</td><td>" + MCHChild5D + "</td></tr>"; createdtable += "<tr><td colspan='4'>Number of health facilities with established capacity to manage acute under-nutrition</td><td>" + MCHNtrnHealthFacility + "</td></tr>"; createdtable += "<tr><td colspan='1' rowspan='9'>HIV and Nutrition</td><td></td> <td colspan='4'> <b> Number of children under 5 years of age who received Vitamin A from USG-supported programs </b> </td><td>" + MCHVaccVitA + "</td></tr>"; createdtable += "<tr><td rowspan='3' colspan='1'> <b> C2.3.D </b> </td> <td colspan='4'>Number of HIV positive clinically malnourished clients who received therapeutic and/or supplementary food < 18 </td><td>" + MCHNtrnFoodOVC + "</td></tr>"; createdtable += "<tr><td colspan='4'>Number of HIV positive clinically malnourished clients who received therapeutic and/or supplementary food 18+ (PLHIV)</td><td>" + MCHNtrnFoodPLHIV + "</td></tr>"; createdtable += "<tr><td colspan='4'>Number of HIV positive clinically malnourished clients who received therapeutic and/or supplementary food -<b> Total</b></td><td>" + MCHNtrnFood + "</td></tr>"; createdtable += "<tr> <td></td><td></td><td></td><td><b>Male</b></td><td><b>Female</b></td><td><b>Total</b></td></tr>"; createdtable += "<tr><td rowspan='4' colspan='1'> <b>C5.1.D </b> </td> <td colspan='1' rowspan='4'>Number of eligible clients who received food and / or other nutrition Services</td><td> <b> less Than 18 </b> </td> <td>" + C51DCM + "</td><td>" + C51DCF + "</td><td>" + C51DC + "</td></tr>"; createdtable += "<tr><td> <b> >=18 </b> </td> <td>" + C51DAM + "</td><td>" + C51DAF + "</td><td>" + C51DA + "</td></tr>"; createdtable += "<tr><td colspan='3'> <b> Pregnant/Lactating (PMTCT 1.5)</b> </td><td>" + C51DP + "</td></tr>"; createdtable += "<tr><td> <b> Total </b> </td> <td> " + C51DMT + " </td> <td> " + C51DFT + " </td> <td> " + C51DT + " </td></tr></table>"; } System.out.println(createdtable); if (conn.conn != null) { conn.conn.close(); } if (conn.rs != null) { conn.rs.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st != null) { conn.st.close(); } if (conn.st2 != null) { conn.st2.close(); } IdGenerator IG = new IdGenerator(); String createdOn = IG.CreatedOn(); 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=" + form + yearmonth + "_Generated_On_" + createdOn + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); outStream.close(); } catch (SQLException ex) { Logger.getLogger(Vmmcpdf.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:reports.OverallCharts.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods./*from www. j a v a2s . com*/ * * @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 { try { dbConn conn = new dbConn(); wb = new HSSFWorkbook(); HSSFSheet shet2 = null; String year = ""; String site = ""; String period = ""; String cbo = ""; String startdate = "2015-01-01"; String enddate = "2015-03-30"; // startdate=request.getParameter("startdate"); //enddate=request.getParameter("enddate"); String getdistinctsites = "SELECT county.county_id as countyid,county_name FROM ovc_lip.backgroundinfor join (sites join (district join county on district.county_id=county.county_id) on sites.districtid=district.district_id) on backgroundinfor.site=sites.site_id where ass_date between '" + startdate + "' and '" + enddate + "' group by county_name "; ArrayList countyids = new ArrayList(); ArrayList countynames = new ArrayList(); countyids.add("1000"); countynames.add("OVERALL COUNTIES REPORT"); //ArrayList years=new ArrayList(); //ArrayList periods=new ArrayList(); //ArrayList cbos=new ArrayList(); conn.rs = conn.st.executeQuery(getdistinctsites); while (conn.rs.next()) { countyids.add(conn.rs.getString(1)); countynames.add(conn.rs.getString(2).toUpperCase() + " COUNTY"); } String sitename = ""; String cboname = ""; //begin a loop that will create as many reports as possible for (int u = 0; u < countyids.size(); u++) { HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 12); font.setFontName("Cambria"); // font.setItalic(true); font.setBoldweight((short) 02); font.setColor(HSSFColor.BLACK.index); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setWrapText(true); style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); style.setAlignment(style.ALIGN_CENTER); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //%%%%%%%%%%%%%%%%HEADER FONTS AND COLORATION%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% HSSFFont font_header = wb.createFont(); font_header.setFontHeightInPoints((short) 10); font_header.setFontName("Eras Bold ITC"); // font.setItalic(true); font_header.setBoldweight((short) 05); font_header.setColor(HSSFColor.BLACK.index); CellStyle style_header = wb.createCellStyle(); style_header.setFont(font_header); style_header.setWrapText(true); style_header.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); style_header.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style_header.setAlignment(style_header.ALIGN_CENTER); // style_header.setBorderBottom(HSSFCellStyle.BORDER_THIN); // style_header.setBorderTop(HSSFCellStyle.BORDER_THIN); // style_header.setBorderRight(HSSFCellStyle.BORDER_THIN); // style_header.setBorderLeft(HSSFCellStyle.BORDER_THIN); //%%%%%%%%%%%%%%%%%%%%%%%%%DATA FONT%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% //font data HSSFFont datafont = wb.createFont(); datafont.setBoldweight((short) 03); datafont.setColor(HSSFColor.BLACK.index); datafont.setFontHeightInPoints((short) 10); datafont.setFontName("Cambria"); datafont.setItalic(true); //bold font HSSFFont bolfont = wb.createFont(); bolfont.setBoldweight((short) 05); bolfont.setColor(HSSFColor.BLACK.index); bolfont.setFontHeightInPoints((short) 12); bolfont.setFontName("Cambria"); //=========================ROW STYLE=============================== HSSFCellStyle rowstyle = wb.createCellStyle(); rowstyle.setWrapText(true); //=======INNER DATA STYLING=========================== CellStyle innerdata_style = wb.createCellStyle(); innerdata_style.setFont(datafont); innerdata_style.setWrapText(true); innerdata_style.setAlignment(innerdata_style.ALIGN_CENTER); innerdata_style.setFillForegroundColor(HSSFColor.WHITE.index); innerdata_style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // innerdata_style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderTop(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderRight(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderLeft(HSSFCellStyle.BORDER_THIN); innerdata_style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle lastcellrighborder = wb.createCellStyle(); lastcellrighborder.setFont(datafont); lastcellrighborder.setWrapText(true); lastcellrighborder.setAlignment(lastcellrighborder.ALIGN_CENTER); lastcellrighborder.setFillForegroundColor(HSSFColor.WHITE.index); lastcellrighborder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // lastcellrighborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); // lastcellrighborder.setBorderTop(HSSFCellStyle.BORDER_THIN); lastcellrighborder.setBorderRight(HSSFCellStyle.BORDER_THIN); // lastcellrighborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); lastcellrighborder.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle innerdata_style2 = wb.createCellStyle(); innerdata_style2.setFont(bolfont); innerdata_style2.setWrapText(true); innerdata_style2.setAlignment(innerdata_style.ALIGN_LEFT); innerdata_style2.setFillForegroundColor(HSSFColor.WHITE.index); innerdata_style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // innerdata_style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderTop(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderRight(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderLeft(HSSFCellStyle.BORDER_THIN); innerdata_style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle orangestyle = wb.createCellStyle(); orangestyle.setFont(bolfont); orangestyle.setWrapText(true); orangestyle.setAlignment(orangestyle.ALIGN_CENTER); orangestyle.setFillForegroundColor(HSSFColor.ORANGE.index); orangestyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // innerdata_style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderTop(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderRight(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderLeft(HSSFCellStyle.BORDER_THIN); orangestyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //Code colors CellStyle lg = wb.createCellStyle(); lg.setFont(bolfont); lg.setWrapText(true); lg.setAlignment(lg.ALIGN_CENTER); lg.setFillForegroundColor(HSSFColor.GREEN.index); lg.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); lg.setBorderBottom(HSSFCellStyle.BORDER_THIN); lg.setBorderTop(HSSFCellStyle.BORDER_THIN); lg.setBorderRight(HSSFCellStyle.BORDER_THIN); lg.setBorderLeft(HSSFCellStyle.BORDER_THIN); lg.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle Y = wb.createCellStyle(); Y.setFont(bolfont); Y.setWrapText(true); Y.setAlignment(Y.ALIGN_CENTER); Y.setFillForegroundColor(HSSFColor.WHITE.index); Y.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); Y.setBorderBottom(HSSFCellStyle.BORDER_THIN); Y.setBorderTop(HSSFCellStyle.BORDER_THIN); Y.setBorderRight(HSSFCellStyle.BORDER_THIN); Y.setBorderLeft(HSSFCellStyle.BORDER_THIN); Y.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle R = wb.createCellStyle(); R.setFont(bolfont); R.setWrapText(true); R.setAlignment(R.ALIGN_CENTER); R.setFillForegroundColor(HSSFColor.RED.index); R.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); R.setBorderBottom(HSSFCellStyle.BORDER_THIN); R.setBorderTop(HSSFCellStyle.BORDER_THIN); R.setBorderRight(HSSFCellStyle.BORDER_THIN); R.setBorderLeft(HSSFCellStyle.BORDER_THIN); R.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //=======INNER LEFT DATA STYLING=========================== CellStyle binnerdata_style2 = wb.createCellStyle(); binnerdata_style2.setFont(datafont); binnerdata_style2.setWrapText(true); binnerdata_style2.setAlignment(binnerdata_style2.ALIGN_LEFT); binnerdata_style2.setFillForegroundColor(HSSFColor.WHITE.index); binnerdata_style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); binnerdata_style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setBorderTop(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setBorderRight(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //create a header //=======================Domainname styles CellStyle dnamestyle = wb.createCellStyle(); dnamestyle.setFont(bolfont); dnamestyle.setWrapText(true); dnamestyle.setAlignment(dnamestyle.ALIGN_LEFT); dnamestyle.setFillForegroundColor(HSSFColor.WHITE.index); dnamestyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); dnamestyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); dnamestyle.setBorderTop(HSSFCellStyle.BORDER_THIN); dnamestyle.setBorderRight(HSSFCellStyle.BORDER_THIN); dnamestyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); dnamestyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); shet2 = wb.createSheet(countynames.get(u).toString().toUpperCase()); shet2.setColumnWidth(0, 12000); shet2.setColumnWidth(1, 12000); shet2.setColumnWidth(2, 4000); shet2.setColumnWidth(3, 10000); shet2.setColumnWidth(4, 5000); shet2.setColumnWidth(5, 5000); shet2.setColumnWidth(6, 5000); shet2.setColumnWidth(7, 5000); shet2.setColumnWidth(8, 5000); shet2.setColumnWidth(9, 5000); shet2.setColumnWidth(10, 5000); shet2.setColumnWidth(11, 5000); shet2.setColumnWidth(12, 5000); shet2.setColumnWidth(13, 5000); //11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111 //11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111 //11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111 //11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111 //String gettables= "SELECT avg(aggregate_sum) as aggregate_sum FROM ovc_lip.domain_totals join (sites join cbo on sites.cbo_id=cbo.cboid) on domain_totals.site=sites.site_id join ( domains join sections on domains.section_id=sections.section_id ) on domain_totals.domainid=domains.domain_id "+mywhere+" group by cbo.cboid,domainid order by cbo,domainid"; // String gettables= "SELECT avg(value) as domainvalue,domain_totals.domainid as domainid,domain_name, section_name,cbo,avg(aggregate_sum) as aggregate_sum FROM ovc_lip.domain_totals join (sites join cbo on sites.cbo_id=cbo.cboid) on domain_totals.site=sites.site_id join ( domains join sections on domains.section_id=sections.section_id ) on domain_totals.domainid=domains.domain_id "+mywhere+" group by cbo.cboid,domainid order by cbo,domainid"; String gettables = "SELECT avg(value) as domainvalue,domain_totals.domainid as domainid,domain_name,section_name ,avg(aggregate_sum) as aggregate_sum , domains.section_id as secid FROM ovc_lip.domain_totals join (sites join (district join county on district.county_id=county.county_id) on sites.districtid=district.district_id) on domain_totals.site=sites.site_id join (domains join sections on domains.section_id=sections.section_id) on domain_totals.domainid=domains.domain_id where county.county_id='" + countyids.get(u) + "' and date between '" + startdate + "' and '" + enddate + "' group by domain_totals.domainid,county_name order by domainid"; //if its the first county, themn skip the county part if (countyids.get(u).toString().equalsIgnoreCase("1000")) { gettables = "SELECT avg(value) as domainvalue,domain_totals.domainid as domainid,domain_name,section_name ,avg(aggregate_sum) as aggregate_sum , domains.section_id as secid FROM ovc_lip.domain_totals join (domains join sections on domains.section_id=sections.section_id) on domain_totals.domainid=domains.domain_id where date between '" + startdate + "' and '" + enddate + "' group by domain_totals.domainid order by domainid"; } System.out.println(gettables); conn.rs = conn.st.executeQuery(gettables); int rwcount = 0; int rowcountcopy = 0; String tableheaders[] = { "Section", "Domain", "% Overall Achievement", "Column chart" }; HSSFRow rwx = null; int monitorrows = 0; int secAcopy = 0; int secBcopy = 0; HSSFCell celx = null; int noofcols = 3; boolean isrow1 = true; while (conn.rs.next()) { //if the section has changed monitorrows++; String domainid = conn.rs.getString("domainid"); float domainvalue = conn.rs.getFloat("domainvalue"); domainvalue = domainvalue * 100; domainvalue = Math.round(domainvalue); float totalsum = conn.rs.getFloat("aggregate_sum"); int dmn = (int) domainvalue; totalsum = Math.round(totalsum); //determine the cell to print data on int ttlsm = (int) totalsum; int hearderheight = 40; //if its the first row in each if (isrow1) { isrow1 = false; rwx = shet2.createRow(rwcount); HSSFCell headercel = rwx.createCell(0); headercel.setCellValue(countynames.get(u).toString().toUpperCase()); headercel.setCellStyle(style); rwx.setHeightInPoints(hearderheight); //create a blank HSSFCell cel = null; for (int b = 1; b < tableheaders.length; b++) { cel = rwx.createCell(b); cel.setCellValue(""); cel.setCellStyle(style); } //now merge the header cell shet2.addMergedRegion(new CellRangeAddress(rwcount, rwcount, 0, tableheaders.length - 1)); rwcount++; //now create the header part HSSFRow headerrw = shet2.createRow(rwcount); rwx.setHeightInPoints(hearderheight); for (int b = 0; b < tableheaders.length; b++) { HSSFCell cel1 = headerrw.createCell(b); cel1.setCellValue(tableheaders[b]); cel1.setCellStyle(style); } rwcount++; } //create the section part HSSFRow rw = shet2.createRow(rwcount); rw.setHeightInPoints(25); //column one --- section HSSFCell seccell = rw.createCell(0); seccell.setCellValue(conn.rs.getString("section_name")); seccell.setCellStyle(dnamestyle); HSSFCell domcell = rw.createCell(1); domcell.setCellValue(conn.rs.getString("domain_name")); domcell.setCellStyle(dnamestyle); //values only HSSFCell domval = rw.createCell(2); domval.setCellValue(dmn); domval.setCellStyle(dnamestyle); HSSFCell blank = rw.createCell(3); blank.setCellValue(""); blank.setCellStyle(dnamestyle); //now, draw the chart HSSFPatriarch patriarch = shet2.createDrawingPatriarch(); HSSFTextbox textbox1 = patriarch.createTextbox( new HSSFClientAnchor(0, 0, (dmn * 10), 255, (short) 3, rwcount, (short) 3, rwcount)); textbox1.setString(new HSSFRichTextString("" + dmn)); textbox1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //green 18,174,55 //red 250 32 32 //yellow 248 255 9 if (dmn >= 75) { textbox1.setFillColor(18, 174, 55); } else if (dmn > 59 && dmn < 75) { textbox1.setFillColor(248, 255, 9); } else { textbox1.setFillColor(250, 32, 32); } rwcount++; if (monitorrows == 4) { shet2.addMergedRegion(new CellRangeAddress(rwcount - 4, rwcount - 1, 0, 0)); } if (monitorrows == 12) { shet2.addMergedRegion(new CellRangeAddress(rwcount - 8, rwcount - 1, 0, 0)); HSSFRow lastrw = shet2.createRow(rwcount); lastrw.setHeightInPoints(25); //now create a row with average HSSFCell avcell0 = lastrw.createCell(0); avcell0.setCellValue("Average"); avcell0.setCellStyle(dnamestyle); HSSFCell avcell = lastrw.createCell(1); avcell.setCellValue("Average"); avcell.setCellStyle(dnamestyle); HSSFCell avcell1 = lastrw.createCell(2); avcell1.setCellValue(ttlsm); avcell1.setCellStyle(dnamestyle); HSSFCell blank1 = lastrw.createCell(3); blank1.setCellValue(""); blank1.setCellStyle(dnamestyle); HSSFTextbox textbox = patriarch.createTextbox(new HSSFClientAnchor(0, 0, (dmn * (10)), 255, (short) 3, rwcount, (short) 3, rwcount)); textbox.setString(new HSSFRichTextString("" + ttlsm)); if (dmn >= 75) { textbox.setFillColor(18, 174, 55); } else if (dmn > 59 && dmn < 75) { textbox.setFillColor(248, 255, 9); } else { textbox.setFillColor(250, 32, 32); } textbox.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); isrow1 = true; monitorrows = 0; //dont print anything rwcount++; //last blank cell HSSFRow blankrow = shet2.createRow(rwcount); blankrow.setHeightInPoints(30); for (int b = 0; b < tableheaders.length; b++) { HSSFCell cel1 = blankrow.createCell(b); cel1.setCellValue(""); cel1.setCellStyle(innerdata_style); } rwcount++; } } ///=========================end of while loop } //end of each loop //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=OVC_COUNTY_CHARTS_FROM_" + startdate + "_TO_" + enddate + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } catch (SQLException ex) { Logger.getLogger(resultspercbo.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:reports.resultspercbo.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods.//from w ww .j a v a2 s .c om * * @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 { try { dbConn conn = new dbConn(); wb = new HSSFWorkbook(); HSSFSheet shet2 = null; String year = ""; String site = ""; String period = ""; String cbo = ""; String startdate = "2015-01-01"; String enddate = "2015-03-30"; startdate = request.getParameter("startdate"); enddate = request.getParameter("enddate"); // year=request.getParameter("year"); //site=request.getParameter("sitecbo"); //period=request.getParameter("period"); //cbo=request.getParameter("staffcbo"); String sitename = ""; String cboname = ""; //begin a loop that will create as many reports as possible HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 12); font.setFontName("Cambria"); // font.setItalic(true); font.setBoldweight((short) 02); font.setColor(HSSFColor.BLACK.index); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setWrapText(true); style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); style.setAlignment(style.ALIGN_CENTER); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //%%%%%%%%%%%%%%%%HEADER FONTS AND COLORATION%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% HSSFFont font_header = wb.createFont(); font_header.setFontHeightInPoints((short) 10); font_header.setFontName("Eras Bold ITC"); // font.setItalic(true); font_header.setBoldweight((short) 05); font_header.setColor(HSSFColor.BLACK.index); CellStyle style_header = wb.createCellStyle(); style_header.setFont(font_header); style_header.setWrapText(true); style_header.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); style_header.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style_header.setAlignment(style_header.ALIGN_CENTER); // style_header.setBorderBottom(HSSFCellStyle.BORDER_THIN); // style_header.setBorderTop(HSSFCellStyle.BORDER_THIN); // style_header.setBorderRight(HSSFCellStyle.BORDER_THIN); // style_header.setBorderLeft(HSSFCellStyle.BORDER_THIN); //%%%%%%%%%%%%%%%%%%%%%%%%%DATA FONT%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% //font data HSSFFont datafont = wb.createFont(); datafont.setBoldweight((short) 03); datafont.setColor(HSSFColor.BLACK.index); datafont.setFontHeightInPoints((short) 10); datafont.setFontName("Cambria"); datafont.setItalic(true); //bold font HSSFFont bolfont = wb.createFont(); bolfont.setBoldweight((short) 05); bolfont.setColor(HSSFColor.BLACK.index); bolfont.setFontHeightInPoints((short) 12); bolfont.setFontName("Cambria"); //=========================ROW STYLE=============================== HSSFCellStyle rowstyle = wb.createCellStyle(); rowstyle.setWrapText(true); //=======INNER DATA STYLING=========================== CellStyle innerdata_style = wb.createCellStyle(); innerdata_style.setFont(datafont); innerdata_style.setWrapText(true); innerdata_style.setAlignment(innerdata_style.ALIGN_CENTER); innerdata_style.setFillForegroundColor(HSSFColor.WHITE.index); innerdata_style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // innerdata_style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderTop(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderRight(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderLeft(HSSFCellStyle.BORDER_THIN); innerdata_style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle lastcellrighborder = wb.createCellStyle(); lastcellrighborder.setFont(datafont); lastcellrighborder.setWrapText(true); lastcellrighborder.setAlignment(lastcellrighborder.ALIGN_CENTER); lastcellrighborder.setFillForegroundColor(HSSFColor.WHITE.index); lastcellrighborder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // lastcellrighborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); // lastcellrighborder.setBorderTop(HSSFCellStyle.BORDER_THIN); lastcellrighborder.setBorderRight(HSSFCellStyle.BORDER_THIN); // lastcellrighborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); lastcellrighborder.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle innerdata_style2 = wb.createCellStyle(); innerdata_style2.setFont(bolfont); innerdata_style2.setWrapText(true); innerdata_style2.setAlignment(innerdata_style.ALIGN_LEFT); innerdata_style2.setFillForegroundColor(HSSFColor.WHITE.index); innerdata_style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // innerdata_style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderTop(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderRight(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderLeft(HSSFCellStyle.BORDER_THIN); innerdata_style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle orangestyle = wb.createCellStyle(); orangestyle.setFont(bolfont); orangestyle.setWrapText(true); orangestyle.setAlignment(orangestyle.ALIGN_CENTER); orangestyle.setFillForegroundColor(HSSFColor.ORANGE.index); orangestyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // innerdata_style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderTop(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderRight(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderLeft(HSSFCellStyle.BORDER_THIN); orangestyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //Code colors CellStyle lg = wb.createCellStyle(); lg.setFont(bolfont); lg.setWrapText(true); lg.setAlignment(lg.ALIGN_CENTER); lg.setFillForegroundColor(HSSFColor.GREEN.index); lg.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); lg.setBorderBottom(HSSFCellStyle.BORDER_THIN); lg.setBorderTop(HSSFCellStyle.BORDER_THIN); lg.setBorderRight(HSSFCellStyle.BORDER_THIN); lg.setBorderLeft(HSSFCellStyle.BORDER_THIN); lg.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle Y = wb.createCellStyle(); Y.setFont(bolfont); Y.setWrapText(true); Y.setAlignment(Y.ALIGN_CENTER); Y.setFillForegroundColor(HSSFColor.WHITE.index); Y.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); Y.setBorderBottom(HSSFCellStyle.BORDER_THIN); Y.setBorderTop(HSSFCellStyle.BORDER_THIN); Y.setBorderRight(HSSFCellStyle.BORDER_THIN); Y.setBorderLeft(HSSFCellStyle.BORDER_THIN); Y.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle R = wb.createCellStyle(); R.setFont(bolfont); R.setWrapText(true); R.setAlignment(R.ALIGN_CENTER); R.setFillForegroundColor(HSSFColor.RED.index); R.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); R.setBorderBottom(HSSFCellStyle.BORDER_THIN); R.setBorderTop(HSSFCellStyle.BORDER_THIN); R.setBorderRight(HSSFCellStyle.BORDER_THIN); R.setBorderLeft(HSSFCellStyle.BORDER_THIN); R.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //=======INNER LEFT DATA STYLING=========================== CellStyle binnerdata_style2 = wb.createCellStyle(); binnerdata_style2.setFont(datafont); binnerdata_style2.setWrapText(true); binnerdata_style2.setAlignment(binnerdata_style2.ALIGN_LEFT); binnerdata_style2.setFillForegroundColor(HSSFColor.WHITE.index); binnerdata_style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); binnerdata_style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setBorderTop(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setBorderRight(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //create a header //=======================Domainname styles CellStyle dnamestyle = wb.createCellStyle(); dnamestyle.setFont(bolfont); dnamestyle.setWrapText(true); dnamestyle.setAlignment(dnamestyle.ALIGN_LEFT); dnamestyle.setFillForegroundColor(HSSFColor.WHITE.index); dnamestyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); dnamestyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); dnamestyle.setBorderTop(HSSFCellStyle.BORDER_THIN); dnamestyle.setBorderRight(HSSFCellStyle.BORDER_THIN); dnamestyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); dnamestyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); shet2 = wb.createSheet("LIP_REPORT"); shet2.setColumnWidth(0, 8000); shet2.setColumnWidth(1, 5000); shet2.setColumnWidth(2, 5000); shet2.setColumnWidth(3, 5000); shet2.setColumnWidth(4, 5000); shet2.setColumnWidth(5, 5000); shet2.setColumnWidth(6, 5000); shet2.setColumnWidth(7, 5000); shet2.setColumnWidth(8, 5000); shet2.setColumnWidth(9, 5000); shet2.setColumnWidth(10, 5000); shet2.setColumnWidth(11, 5000); shet2.setColumnWidth(12, 5000); shet2.setColumnWidth(13, 5000); //create header one HSSFRow header = shet2.createRow(0); header.setHeightInPoints(30); HSSFCell cel1 = header.createCell(0); cel1.setCellValue("Results on LIP Initial conducted from " + startdate + " to " + enddate); cel1.setCellStyle(style); for (int b = 1; b < 14; b++) { cel1 = header.createCell(b); cel1.setCellValue(""); cel1.setCellStyle(style); } shet2.addMergedRegion(new CellRangeAddress(0, 0, 0, 13)); //create header two HSSFRow header2 = shet2.createRow(1); header2.setHeightInPoints(28); HSSFCell cel2 = null; for (int b = 1; b < 14; b++) { cel2 = header2.createCell(b); cel2.setCellValue(""); cel2.setCellStyle(style); } cel2 = header2.createCell(0); cel2.setCellValue("Percent Scores Per Domain"); cel2.setCellStyle(style); shet2.addMergedRegion(new CellRangeAddress(1, 1, 0, 13)); //cbo name //create header three //==========DATE OF VISIT String mywhere = " ass_date between '" + startdate + "' and '" + enddate + "' "; //if the current countyid is 0, then the where code should not specify the county name //=============================================================================================== //=============================================================================================== String getdomains = "SELECT domain_id,domain_name,section_name FROM domains join sections on domains.section_id=sections.section_id"; conn.rs = conn.st.executeQuery(getdomains); ArrayList domainids = new ArrayList(); int r = 2; HSSFRow theader = shet2.createRow(r); HSSFRow domainshd = shet2.createRow(r + 1); //create a row with title lip HSSFCell tce = domainshd.createCell(0); tce.setCellValue("LIP"); tce.setCellStyle(Y); int cnt = 1; while (conn.rs.next()) { HSSFCell tcel = theader.createCell(cnt); tcel.setCellValue(conn.rs.getString("section_name")); tcel.setCellStyle(Y); theader.setHeightInPoints(24); HSSFCell tcel1 = domainshd.createCell(cnt); tcel1.setCellValue(conn.rs.getString("domain_name")); tcel1.setCellStyle(Y); domainids.add(conn.rs.getString("domain_id")); cnt++; } //create avarage header HSSFCell tcel = domainshd.createCell(cnt); tcel.setCellValue("Avarage"); tcel.setCellStyle(Y); shet2.addMergedRegion(new CellRangeAddress(r, r, 1, 5)); shet2.addMergedRegion(new CellRangeAddress(r, r, 6, 12)); cnt++; // shet2.addMergedRegion(new CellRangeAddress(7,7,0,5)); // String gettables = "SELECT domain_name,domains.domain_id as domainid,section_name,domains.section_id as secid,value as domainvalue,aggregate_sum,period,year,site FROM domains join sections on domains.section_id=sections.section_id join domain_totals on domains.domain_id=domain_totals.domainid where "+mywhere+" order by domainid"; String gettables = "SELECT avg(value) as domainvalue,domain_totals.domainid as domainid,cbo,avg(aggregate_sum) as aggregate_sum FROM ovc_lip.domain_totals join (sites join cbo on sites.cbo_id=cbo.cboid) on domain_totals.site=sites.site_id where date between '" + startdate + "' and '" + enddate + "' group by cbo.cboid,domainid order by cbo,domainid"; //if its the first county, themn skip the county part System.out.println(gettables); conn.rs = conn.st.executeQuery(gettables); int rwcount = 4; HSSFRow rwx = null; HSSFCell celx = null; String sectioncopy = ""; int rowcopy = 8; while (conn.rs.next()) { //if the section has changed String domainid = conn.rs.getString("domainid"); float domainvalue = conn.rs.getFloat("domainvalue"); domainvalue = domainvalue * 100; //BigDecimal bd=new BigDecimal(domainvalue).setScale(0,RoundingMode.HALF_EVEN); //domainval=bd.doubleValue(); domainvalue = Math.round(domainvalue); float totalsum = conn.rs.getFloat("aggregate_sum"); // totalsum=totalsum*100; int dmn = (int) domainvalue; totalsum = Math.round(totalsum); //determine the cell to print data on int ttlsm = (int) totalsum; if (domainid.equals("1")) { rwx = shet2.createRow(rwcount); rwx.setHeightInPoints(22); HSSFCell celx2 = rwx.createCell(0); celx2.setCellValue("" + conn.rs.getString("cbo")); celx2.setCellStyle(dnamestyle); rwcount++; } for (int t = 0; t < domainids.size(); t++) { //if row is blank create it if (rwx == null) { rwx = shet2.createRow(rwcount); } if (domainids.get(t).equals(domainid)) { int ct = t + 1; HSSFCell celx1 = rwx.createCell(ct); celx1.setCellValue("" + dmn); celx1.setCellStyle(dnamestyle); rwx.setHeightInPoints(22); //System.out.println("worked in row ============="+rwcount+"__col "+(ct)+"_"+domainvalue); } } //incement if the column is the last if (domainid.equals("12")) { //create an avarage // System.out.println("XXXXX LAST LOOP"); if (rwx == null) { rwx = shet2.createRow(rwcount); } celx = rwx.createCell(13); rwx.setHeightInPoints(23); celx.setCellValue("" + ttlsm); celx.setCellStyle(dnamestyle); // rwcount++; } } ///=========================end of while loop //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=OVC_LIP_COUNTY_REPORT_" + startdate + "_" + enddate + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } catch (SQLException ex) { Logger.getLogger(resultspercbo.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:reports.vmmcexcel.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {/*from w w w. j a va2 s .com*/ response.setContentType("text/html;charset=UTF-8"); session = request.getSession(); dbConn conn = new dbConn(); //get the existing data for the month, year and facility that is already on session String month = ""; String year = ""; String facil = "361"; String form = "vmmc"; //===================================================================================================== year = "2015"; month = "5"; String county = ""; String header = ""; String reportType = ""; if (request.getParameter("reportType") != null) { reportType = request.getParameter("reportType"); } String reportDuration = ""; if (request.getParameter("reportDuration") != null) { reportDuration = request.getParameter("reportDuration"); } if (request.getParameter("year") != null) { year = request.getParameter("year"); } if (request.getParameter("facility") != null && reportType.equals("2")) { facil = request.getParameter("facility"); String getfacil = "select SubPartnerNom,CentreSanteId as mflcode from subpartnera where SubPartnerID='" + facil + "'"; conn.rs = conn.st.executeQuery(getfacil); while (conn.rs.next()) { header += " FACILITY : " + conn.rs.getString(1).toUpperCase() + " MFL CODE : " + conn.rs.getString(2) + " "; } } if (request.getParameter("county") != null && reportType.equals("2")) { county = request.getParameter("county"); String getcounty = "select County from county where CountyID='" + county + "'"; conn.rs = conn.st.executeQuery(getcounty); while (conn.rs.next()) { header += " COUNTY : " + conn.rs.getString(1).toUpperCase() + " "; } } if (request.getParameter("month") != null && reportDuration.equals("4")) { month = request.getParameter("month"); String getmonth = "select name as monthname from month where id='" + month + "'"; conn.rs = conn.st.executeQuery(getmonth); while (conn.rs.next()) { header += " MONTH : " + conn.rs.getString(1).toUpperCase() + ""; } } header += " YEAR : " + year + ""; String facilitywhere = ""; String yearwhere = ""; String monthwhere = ""; String countywhere = ""; String duration = ""; String semi_annual = ""; String quarter = ""; //================================================================================================== //XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX int yearcopy = Integer.parseInt(year); // reportType="2"; // year=2015; // reportDuration="3"; String yearmonth = "" + year; int prevYear = yearcopy - 1; int maxYearMonth = 0; int monthcopy = 0; // GET REPORT DURATION============================================ if (reportDuration.equals("1")) { yearmonth += "_AnnualReport"; duration = " " + form + ".yearmonth BETWEEN " + prevYear + "10 AND " + year + "09"; } else if (reportDuration.equals("2")) { semi_annual = request.getParameter("semi_annual"); // semi_annual="2"; if (semi_annual.equals("1")) { yearmonth = prevYear + "_Oct_" + year + "_Mar"; duration = " " + form + ".yearmonth BETWEEN " + prevYear + "10 AND " + year + "03"; } else { yearmonth += "_Apr_Sep"; duration = " " + form + ".yearmonth BETWEEN " + year + "04 AND " + year + "09"; } } else if (reportDuration.equals("3")) { String startMonth, endMonth; quarter = request.getParameter("quarter"); // quarter="3"; String getMonths = "SELECT months,name FROM quarter WHERE id='" + quarter + "'"; conn.rs = conn.st.executeQuery(getMonths); if (conn.rs.next() == true) { String months[] = conn.rs.getString(1).split(","); startMonth = months[0]; endMonth = months[2]; if (quarter.equals("1")) { duration = " " + form + ".yearmonth BETWEEN " + prevYear + "" + startMonth + " AND " + prevYear + "" + endMonth; yearmonth = prevYear + "_" + conn.rs.getString(2); } else { yearmonth = year + "_" + conn.rs.getString(2); duration = " " + form + ".yearmonth BETWEEN " + year + "" + startMonth + " AND " + year + "" + endMonth; } } } else if (reportDuration.equals("4")) { monthcopy = Integer.parseInt(request.getParameter("month")); // month=5; if (monthcopy >= 10) { yearmonth = prevYear + "_" + month; duration = " " + form + ".yearmonth=" + prevYear + "" + month; } else { duration = " " + form + ".yearmonth=" + year + "0" + month; yearmonth = year + "_(" + month + ")"; } } else { duration = ""; } //====================================================================== //================================================================================================== //XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX String getexistingdata = ""; if (!county.equals("")) { countywhere = " and countyid = '" + county + "'"; } if (!facil.equals("") && reportType.equalsIgnoreCase("2")) { facilitywhere = " and " + form + ".SubPartnerID = '" + facil + "' "; } String joinedwhwere = " where 1=1 " + facilitywhere + " " + yearwhere + " && " + duration; //===================================================================================================== //===================================================================================================== //______________________________________________________________________________________ // NOW 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); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 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_CENTER); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); stylex.setFont(fontx); stylex.setWrapText(true); HSSFSheet shet = wb.createSheet(form); //create headers for that worksheet HSSFRow rw = shet.createRow(0); rw.setHeightInPoints(25); HSSFCell cl0 = rw.createCell(0); cl0.setCellValue("VOLUNTARY MALE CIRCUMCISION REPORTING FORM"); cl0.setCellStyle(stylex); for (int a = 1; a <= 4; a++) { HSSFCell clx = rw.createCell(a); clx.setCellValue(""); clx.setCellStyle(stylex); } HSSFRow rw1 = shet.createRow(1); rw1.setHeightInPoints(23); HSSFCell cl = rw1.createCell(0); cl.setCellValue(header); cl.setCellStyle(stylex); for (int a = 1; a <= 4; a++) { HSSFCell clx = rw1.createCell(a); clx.setCellValue(""); clx.setCellStyle(stylex); } HSSFRow rw2 = shet.createRow(2); rw2.setHeightInPoints(23); HSSFCell cl3 = rw2.createCell(0); cl3.setCellValue("P5.1.D:"); cl3.setCellStyle(style2); HSSFCell cl31 = rw2.createCell(1); cl31.setCellValue( "Number of Males Circumcised as part of the minimum package of MC for HIV prevention services:"); cl31.setCellStyle(stylex); for (int a = 2; a <= 4; a++) { HSSFCell clx = rw2.createCell(a); clx.setCellValue(""); clx.setCellStyle(stylex); } shet.addMergedRegion(new CellRangeAddress(2, 11, 0, 0)); shet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4)); shet.addMergedRegion(new CellRangeAddress(1, 1, 0, 4)); shet.addMergedRegion(new CellRangeAddress(2, 2, 1, 4)); shet.setColumnWidth(0, 2000); shet.setColumnWidth(1, 9000); shet.setColumnWidth(2, 5000); shet.setColumnWidth(3, 5000); shet.setColumnWidth(4, 5000); getexistingdata = "select sum(P51D1) as P51D1, sum(P51D9) as P51D9, sum(P51D10) as P51D10, sum(P51D19) as P51D19,sum(P51D24) as P51D24, sum(P51D29) as P51D29, sum(P51D49) as P51D49, sum(P51D50) as P51D50, sum(P51DT) as P51DT, sum(P521DM) as P521DM, sum(P521DS) as P521DS, sum(P521DT) as P521DT, sum(P522DM) as P522DM, sum(P522DS) as P522DS, sum(P522DT) as P522DT, sum(P52DM) as P52DM, sum(P52DS) as P52DS, sum(P52DT) as P52DT, sum(P511KP) as P511KP, sum(P511KN) as P511KN, sum(P511KU) as P511KU, sum(P511Surg) as P511Surg, sum(P511Dev) as P511Dev, sum(P53DF) as P53DF, sum(P53DO) as P53DO, sum(P53DM) as P53DM, sum(P53D) as P53D, sum(P54D) as P54D from " + form + " join ( subpartnera join (district join county on county.CountyID=district.CountyID ) on district.DistrictID = subpartnera.DistrictID ) on " + form + ".SubPartnerID = subpartnera.SubPartnerID " + joinedwhwere + " "; System.out.println(getexistingdata); String P51D1 = ""; String P51D9 = ""; String P51D10 = ""; String P51D19 = ""; String P51D24 = ""; String P51D29 = ""; String P51D49 = ""; String P51D50 = ""; String P51DT = ""; String P521DM = ""; String P521DS = ""; String P521DT = ""; String P522DM = ""; String P522DS = ""; String P522DT = ""; String P52DM = ""; String P52DS = ""; String P52DT = ""; String P511KP = ""; String P511KN = ""; String P511KU = ""; String P511Surg = ""; String P511Dev = ""; String P53DF = ""; String P53DO = ""; String P53DM = ""; String P53D = ""; String P54D = ""; String distid = ""; if (session.getAttribute("subcountyid") != null) { distid = session.getAttribute("subcountyid").toString(); } int counter = 0; conn.rs = conn.st.executeQuery(getexistingdata); while (conn.rs.next()) { //now check if form was updated and if its one month after data entry //now load the column values here P51D1 = conn.rs.getString("P51D1"); if (P51D1 == null) { P51D1 = ""; } P51D9 = conn.rs.getString("P51D9"); if (P51D9 == null) { P51D9 = ""; } P51D10 = conn.rs.getString("P51D10"); if (P51D10 == null) { P51D10 = ""; } P51D19 = conn.rs.getString("P51D19"); if (P51D19 == null) { P51D19 = ""; } P51D24 = conn.rs.getString("P51D24"); if (P51D24 == null) { P51D24 = ""; } P51D29 = conn.rs.getString("P51D29"); if (P51D29 == null) { P51D29 = ""; } P51D49 = conn.rs.getString("P51D49"); if (P51D49 == null) { P51D49 = ""; } P51D50 = conn.rs.getString("P51D50"); if (P51D50 == null) { P51D50 = ""; } P51DT = conn.rs.getString("P51DT"); if (P51DT == null) { P51DT = ""; } P521DM = conn.rs.getString("P521DM"); if (P521DM == null) { P521DM = ""; } P521DS = conn.rs.getString("P521DS"); if (P521DS == null) { P521DS = ""; } P521DT = conn.rs.getString("P521DT"); if (P521DT == null) { P521DT = ""; } P522DM = conn.rs.getString("P522DM"); if (P522DM == null) { P522DM = ""; } P522DS = conn.rs.getString("P522DS"); if (P522DS == null) { P522DS = ""; } P522DT = conn.rs.getString("P522DT"); if (P522DT == null) { P522DT = ""; } P52DM = conn.rs.getString("P52DM"); if (P52DM == null) { P52DM = ""; } P52DS = conn.rs.getString("P52DS"); if (P52DS == null) { P52DS = ""; } P52DT = conn.rs.getString("P52DT"); if (P52DT == null) { P52DT = ""; } P511KP = conn.rs.getString("P511KP"); if (P511KP == null) { P511KP = ""; } P511KN = conn.rs.getString("P511KN"); if (P511KN == null) { P511KN = ""; } P511KU = conn.rs.getString("P511KU"); if (P511KU == null) { P511KU = ""; } P511Surg = conn.rs.getString("P511Surg"); if (P511Surg == null) { P511Surg = ""; } P511Dev = conn.rs.getString("P511Dev"); if (P511Dev == null) { P511Dev = ""; } P53DF = conn.rs.getString("P53DF"); if (P53DF == null) { P53DF = ""; } P53DO = conn.rs.getString("P53DO"); if (P53DO == null) { P53DO = ""; } P53DM = conn.rs.getString("P53DM"); if (P53DM == null) { P53DM = ""; } P53D = conn.rs.getString("P53D"); if (P53D == null) { P53D = ""; } P54D = conn.rs.getString("P54D"); if (P54D == null) { P54D = ""; } } String createdtable = ""; if (1 == 1) { if (1 == 1) { int r = 3; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("< 1"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P51D1); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } } if (1 == 1) { int r = 4; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("1-9"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P51D9); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } //===================================================================================== if (1 == 1) { int r = 5; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("10-14"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P51D10); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } //===================================================================================== if (1 == 1) { int r = 6; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("15-19"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P51D19); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } //================================================================================== if (1 == 1) { int r = 7; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("20-24"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P51D24); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } //================================================================================== if (1 == 1) { int r = 8; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("25-29"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P51D29); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } //================================================================================== if (1 == 1) { int r = 9; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("30-49"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P51D49); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } //================================================================================== if (1 == 1) { int r = 10; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("50 +"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P51D50); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } //================================================================================== if (1 == 1) { int r = 11; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Total"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P51DT); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } //================================================================================== if (1 == 1) { int r = 12; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue("P5.2.D"); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue( "Number of Clients circumcised who experienced one or more moderate or severe adverse events(s)"); clx.setCellStyle(stylex); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(""); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 4)); shet.addMergedRegion(new CellRangeAddress(r, r + 4, 0, 0)); } //================================================================================== if (1 == 1) { int r = 13; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue(""); clx.setCellStyle(style2); HSSFCell clxm = rwx.createCell(2); clxm.setCellValue("Moderate"); clxm.setCellStyle(stylex); HSSFCell clxs = rwx.createCell(3); clxs.setCellValue("Severe"); clxs.setCellStyle(stylex); HSSFCell clxt = rwx.createCell(4); clxt.setCellValue("Total"); clxt.setCellStyle(stylex); //shet.addMergedRegion(new CellRangeAddress(r,r,1,3)); } //================================================================================== if (1 == 1) { int r = 14; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("During Circumcission"); clx.setCellStyle(stylex); HSSFCell clxm = rwx.createCell(2); clxm.setCellValue(P521DM); clxm.setCellStyle(style2); HSSFCell clxs = rwx.createCell(3); clxs.setCellValue(P521DS); clxs.setCellStyle(style2); HSSFCell clxt = rwx.createCell(4); clxt.setCellValue(P521DT); clxt.setCellStyle(style2); //shet.addMergedRegion(new CellRangeAddress(r,r,1,3)); } //================================================================================== if (1 == 1) { int r = 15; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Post CircumCission"); clx.setCellStyle(stylex); HSSFCell clxm = rwx.createCell(2); clxm.setCellValue(P522DM); clxm.setCellStyle(style2); HSSFCell clxs = rwx.createCell(3); clxs.setCellValue(P522DS); clxs.setCellStyle(style2); HSSFCell clxt = rwx.createCell(4); clxt.setCellValue(P522DT); clxt.setCellStyle(style2); //shet.addMergedRegion(new CellRangeAddress(r,r,1,3)); } //================================================================================== if (1 == 1) { int r = 16; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Total Adverse Events"); clx.setCellStyle(stylex); HSSFCell clxm = rwx.createCell(2); clxm.setCellValue(P52DM); clxm.setCellStyle(style2); HSSFCell clxs = rwx.createCell(3); clxs.setCellValue(P52DS); clxs.setCellStyle(style2); HSSFCell clxt = rwx.createCell(4); clxt.setCellValue(P52DT); clxt.setCellStyle(style2); //shet.addMergedRegion(new CellRangeAddress(r,r,1,3)); } //================================================================================== if (1 == 1) { int r = 17; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue("P5.1.1.K"); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("HIV Status of MC clients)"); clx.setCellStyle(stylex); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(""); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 4)); shet.addMergedRegion(new CellRangeAddress(r, r + 3, 0, 0)); } //================================================================================== if (1 == 1) { int r = 18; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Tested/self-reported positive"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P511KP); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } } //================================================================================== if (1 == 1) { int r = 19; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Tested negative"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P511KN); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } } //================================================================================== if (1 == 1) { int r = 20; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Unknown/self-reported negative"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P511KU); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } } //================================================================================== //================================================================================== if (1 == 1) { int r = 21; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue("P5.1.1.T"); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Circumcission Technique)"); clx.setCellStyle(stylex); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(""); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 4)); shet.addMergedRegion(new CellRangeAddress(r, r + 2, 0, 0)); } //================================================================================== if (1 == 1) { int r = 22; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Surgical VMMC"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P511Surg); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } } //================================================================================== if (1 == 1) { int r = 23; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Device-Based VMMC"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P511Dev); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } } //================================================================================== //================================================================================== if (1 == 1) { int r = 24; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue("P5.3.D:"); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue( "Number of locations providing MC surgery as part of the minimum package of MC for HIV prevention services within the reporting period "); clx.setCellStyle(stylex); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(""); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 4)); shet.addMergedRegion(new CellRangeAddress(r, r + 4, 0, 0)); } //================================================================================== if (1 == 1) { int r = 25; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Fixed/Static"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P53DF); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } } //================================================================================== if (1 == 1) { int r = 26; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Outreach"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P53DO); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } } //============================================================================================== //================================================================================== if (1 == 1) { int r = 27; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Mobile"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P53DM); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } } //============================================================================================== //================================================================================== if (1 == 1) { int r = 28; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Total"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P53D); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } } //============================================================================================== //================================================================================== if (1 == 1) { int r = 29; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(28); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue("P5.3.D:"); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue( "Number of males circumcised within the reporting period who return at least once for postoperative follow?up care (routine or emergent) within 14 days of surgery"); clx.setCellStyle(stylex); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(""); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 4)); shet.addMergedRegion(new CellRangeAddress(r, r + 1, 0, 0)); } //================================================================================== if (1 == 1) { int r = 30; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue(""); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P54D); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } } //================================================================================== } //System.out.println(createdtable); if (conn.conn != null) { conn.conn.close(); } if (conn.rs != null) { conn.rs.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st != null) { conn.st.close(); } if (conn.st2 != null) { conn.st2.close(); } IdGenerator IG = new IdGenerator(); String createdOn = IG.CreatedOn(); 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=" + form + yearmonth + "_Generatted_On_" + createdOn + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); outStream.close(); } catch (SQLException ex) { Logger.getLogger(Vmmcpdf.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:se.mithlond.services.content.impl.ejb.report.ExcelReportServiceBean.java
License:Apache License
/** * {@inheritDoc}//from ww w . jav a 2 s.c o m */ @Override @SuppressWarnings("all") public CellStyle getCellStyle(final ExcelElement el, final Workbook workbook) { // Check sanity Validate.notNull(workbook, "workbook"); Validate.notNull(el, "el"); // Acquire the el and Font as expected final CellStyle toReturn = workbook.createCellStyle(); final Font theFont = workbook.createFont(); switch (el) { case TITLE: theFont.setFontHeightInPoints((short) 18); theFont.setBold(true); theFont.setColor(IndexedColors.BLUE_GREY.getIndex()); toReturn.setAlignment(HorizontalAlignment.CENTER); toReturn.setVerticalAlignment(VerticalAlignment.CENTER); break; case HEADER: theFont.setFontHeightInPoints((short) 11); theFont.setColor(IndexedColors.WHITE.getIndex()); toReturn.setAlignment(HorizontalAlignment.CENTER); toReturn.setVerticalAlignment(VerticalAlignment.CENTER); toReturn.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex()); toReturn.setFillPattern(FillPatternType.SOLID_FOREGROUND); toReturn.setWrapText(true); break; case CELL: toReturn.setAlignment(HorizontalAlignment.LEFT); toReturn.setWrapText(true); toReturn.setBorderRight(BORDER_THIN); toReturn.setRightBorderColor(GREY_25_PERCENT); toReturn.setBorderLeft(BORDER_THIN); toReturn.setLeftBorderColor(GREY_25_PERCENT); toReturn.setBorderTop(BORDER_THIN); toReturn.setTopBorderColor(GREY_25_PERCENT); toReturn.setBorderBottom(BORDER_THIN); toReturn.setBottomBorderColor(GREY_25_PERCENT); break; case NON_WRAPPING: toReturn.setAlignment(HorizontalAlignment.LEFT); toReturn.setWrapText(false); toReturn.setBorderRight(BORDER_THIN); toReturn.setRightBorderColor(GREY_25_PERCENT); toReturn.setBorderLeft(BORDER_THIN); toReturn.setLeftBorderColor(GREY_25_PERCENT); toReturn.setBorderTop(BORDER_THIN); toReturn.setTopBorderColor(GREY_25_PERCENT); toReturn.setBorderBottom(BORDER_THIN); toReturn.setBottomBorderColor(GREY_25_PERCENT); break; case FORMULA: toReturn.setAlignment(HorizontalAlignment.CENTER); toReturn.setVerticalAlignment(VerticalAlignment.CENTER); toReturn.setFillForegroundColor(GREY_25_PERCENT); toReturn.setFillPattern(FillPatternType.SOLID_FOREGROUND); toReturn.setDataFormat(workbook.createDataFormat().getFormat("0.00")); break; case ALT_FORMULA: toReturn.setAlignment(HorizontalAlignment.CENTER); toReturn.setVerticalAlignment(VerticalAlignment.CENTER); toReturn.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex()); toReturn.setFillPattern(FillPatternType.SOLID_FOREGROUND); toReturn.setDataFormat(workbook.createDataFormat().getFormat("0.00")); break; default: throw new IllegalArgumentException( "Style [" + el.name() + "] was not defined. " + "Blame the programmer."); } // All done. toReturn.setFont(theFont); return toReturn; }
From source file:servlet.exportScoreSheet.java
private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style; Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 18); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle();//from ww w. j av a 2 s.co m style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFont(titleFont); styles.put("title", style); Font monthFont = wb.createFont(); monthFont.setFontHeightInPoints((short) 11); monthFont.setColor(IndexedColors.WHITE.getIndex()); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(monthFont); style.setWrapText(true); styles.put("header", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setWrapText(true); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styles.put("cell", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula_2", style); return styles; }
From source file:standarapp.algorithm.ReadFileVector.java
public String lectureRegistry(String nameFile, String nameOut, int[] col, double percent, int rowBegin) { String answer = ""; int quantityFound = 0; workbook = Lecture.lectureXLSX(nameFile); sheet = workbook.getSheetAt(0);/* w w w .j ava2 s . co m*/ for (Row row : sheet) { if (row.getRowNum() < rowBegin) { continue; } String[] cellsWI = new String[col.length + 1]; for (int i = 0; i < col.length; i++) { cellsWI[i] = ""; try { Cell cell = row.getCell(col[i]); if (cell.getCellType() == Cell.CELL_TYPE_STRING) { cellsWI[i] = deleteTrash(cell.getStringCellValue()); } else { cellsWI[i] = cell.getDateCellValue().toString(); cellsWI[col.length] = String.valueOf(cell.getDateCellValue().getMonth()); } if (i == 2) { cellsWI[i] = cell.getStringCellValue(); } } catch (Exception e) { } } registry.add(cellsWI); } System.out.println("Total: " + registry.size()); /*for (int i = 0; i < registry.size(); i++) { System.out.println(i +" Municipio: " + registry.get(i)[0] + " | Localidad: " + registry.get(i)[1] + " | Especie: " + registry.get(i)[2] + " | Fecha: " + registry.get(i)[3]); }*/ int rowCount = 0; int columnCount = 0; CellStyle cs = workbook.createCellStyle(); //Font font = workbook.createFont(); cs.setAlignment(HorizontalAlignment.CENTER); cs.setVerticalAlignment(VerticalAlignment.CENTER); cs.setBorderRight(BorderStyle.THIN); cs.setBorderLeft(BorderStyle.THIN); cs.setBorderBottom(BorderStyle.THIN); cs.setBorderTop(BorderStyle.THIN); sheet = workbook.createSheet(); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); sheet.setColumnWidth(0, 20); Row row = sheet.createRow(0); Cell encabezado = row.createCell(rowCount); encabezado.setCellValue("Base de datos coordenadas"); encabezado.setCellStyle(cs); CellRangeAddress region = new CellRangeAddress(0, 0, 0, 8); sheet.addMergedRegion(region); row = sheet.createRow(++rowCount); Cell cell = row.createCell(columnCount); cell.setCellValue("Especie"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Municipio"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Codigo Municipio"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Vereda"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Codigo Vereda"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Mes"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Ao"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Latitud"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Longitud"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Fuente"); cell.setCellStyle(cs); for (int i = 0; i < registry.size(); i++) { try { String[] registro = registry.get(i); columnCount = -1; int cod_Mncp = 0; row = sheet.createRow(++rowCount); double levenstein = 0; double localidad_oficial = 0; double levensteinActual = 0; for (Integer codMunicipio : codigo_Municipio.keySet()) { if (registro[0].equals(codigo_Municipio.get(codMunicipio))) { cod_Mncp = codMunicipio; break; } try { double levenstein_local = FuzzySearch.ratio(registro[0], codigo_Municipio.get(codMunicipio)); if (levenstein_local >= levensteinActual) { cod_Mncp = codMunicipio; levensteinActual = levenstein_local; } if (levensteinActual == 100) { break; } } catch (Exception e) { } } for (Double cod_Loc : mncp_localidad.get(cod_Mncp).keySet()) { String loc = mncp_localidad.get(cod_Mncp).get(cod_Loc); if (registro[1].equals(loc)) { localidad_oficial = cod_Loc; levenstein = 101; } try { double levenstein_local = FuzzySearch.ratio(registro[1], loc); if (levenstein_local >= levenstein) { localidad_oficial = cod_Loc; levenstein = levenstein_local; } if (levenstein == 100) { break; } } catch (Exception e) { } } String mncp_oficial = codigo_Municipio.get(cod_Mncp); String loc_oficial = codigo_localidad.get(localidad_oficial); String especie = registro[2]; double locX = localidad_x.get(localidad_oficial); double locY = localidad_y.get(localidad_oficial); int year = 0; int month = 0; System.out.println(); try { year = Integer.parseInt(registro[3].split(" ")[5]); month = Integer.parseInt(registro[registro.length - 1]) + 1; } catch (Exception e) { year = Integer.parseInt(registro[3].substring(registro[3].length() - 4)); month = Integer .parseInt(registro[3].substring(registro[3].length() - 7, registro[3].length() - 5)); } quantityFound++; cell = row.createCell(++columnCount); cell.setCellValue(especie); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue(mncp_oficial); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue(cod_Mncp); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue(loc_oficial); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue(localidad_oficial); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue(month); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue(year); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue(locY); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue(locX); cell.setCellStyle(cs); } catch (Exception e) { continue; } } sheet.setColumnWidth(0, 5800); sheet.setColumnWidth(1, 5800); sheet.setColumnWidth(2, 3000); sheet.setColumnWidth(3, 5800); sheet.setColumnWidth(4, 3000); sheet.setColumnWidth(5, 3000); sheet.setColumnWidth(6, 3000); sheet.setColumnWidth(7, 6400); sheet.setColumnWidth(8, 6400); answer = "Se generaron " + quantityFound + " vector(es)"; try (FileOutputStream outputStream = new FileOutputStream(nameOut)) { workbook.write(outputStream); } catch (IOException ex) { quantityFound = 0; answer = "Cerrar el archivo de entrada "; } return answer; }
From source file:test.poi.MyExcelDemo.java
License:Apache License
/** * Create a library of cell styles// w w w. j a v a 2s . co m */ private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style; Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 18); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFont(titleFont); styles.put("title", style); Font headerFont = wb.createFont(); headerFont.setFontHeightInPoints((short) 11); headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerFont.setColor(IndexedColors.BLACK.getIndex()); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.WHITE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(headerFont); // style.setWrapText(true); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styles.put("header", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setWrapText(true); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styles.put("cell", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula_2", style); return styles; }
From source file:uk.co.spudsoft.birt.emitters.excel.StyleManager.java
License:Open Source License
/** * Create a new POI CellStyle based upon a BIRT style. * @param birtStyle/*from w w w . j av a 2 s.c o m*/ * The BIRT style to base the CellStyle upon. * @return * The CellStyle whose attributes are described by the BIRT style. */ private CellStyle createStyle(BirtStyle birtStyle) { CellStyle poiStyle = workbook.createCellStyle(); // Font Font font = fm.getFont(birtStyle); if (font != null) { poiStyle.setFont(font); } // Alignment poiStyle.setAlignment( smu.poiAlignmentFromBirtAlignment(birtStyle.getString(StyleConstants.STYLE_TEXT_ALIGN))); // Background colour smu.addBackgroundColourToStyle(workbook, poiStyle, birtStyle.getString(StyleConstants.STYLE_BACKGROUND_COLOR)); // Top border smu.applyBorderStyle(workbook, poiStyle, BorderSide.TOP, birtStyle.getProperty(StyleConstants.STYLE_BORDER_TOP_COLOR), birtStyle.getProperty(StyleConstants.STYLE_BORDER_TOP_STYLE), birtStyle.getProperty(StyleConstants.STYLE_BORDER_TOP_WIDTH)); // Left border smu.applyBorderStyle(workbook, poiStyle, BorderSide.LEFT, birtStyle.getProperty(StyleConstants.STYLE_BORDER_LEFT_COLOR), birtStyle.getProperty(StyleConstants.STYLE_BORDER_LEFT_STYLE), birtStyle.getProperty(StyleConstants.STYLE_BORDER_LEFT_WIDTH)); // Right border smu.applyBorderStyle(workbook, poiStyle, BorderSide.RIGHT, birtStyle.getProperty(StyleConstants.STYLE_BORDER_RIGHT_COLOR), birtStyle.getProperty(StyleConstants.STYLE_BORDER_RIGHT_STYLE), birtStyle.getProperty(StyleConstants.STYLE_BORDER_RIGHT_WIDTH)); // Bottom border smu.applyBorderStyle(workbook, poiStyle, BorderSide.BOTTOM, birtStyle.getProperty(StyleConstants.STYLE_BORDER_BOTTOM_COLOR), birtStyle.getProperty(StyleConstants.STYLE_BORDER_BOTTOM_STYLE), birtStyle.getProperty(StyleConstants.STYLE_BORDER_BOTTOM_WIDTH)); // Number format smu.applyNumberFormat(workbook, birtStyle, poiStyle, locale); // Whitespace/wrap if (CSSConstants.CSS_PRE_VALUE.equals(birtStyle.getString(StyleConstants.STYLE_WHITE_SPACE))) { poiStyle.setWrapText(true); } // Vertical alignment if (CSSConstants.CSS_TOP_VALUE.equals(birtStyle.getString(StyleConstants.STYLE_VERTICAL_ALIGN))) { poiStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP); } else if (CSSConstants.CSS_MIDDLE_VALUE.equals(birtStyle.getString(StyleConstants.STYLE_VERTICAL_ALIGN))) { poiStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); } else if (CSSConstants.CSS_BOTTOM_VALUE.equals(birtStyle.getString(StyleConstants.STYLE_VERTICAL_ALIGN))) { poiStyle.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM); } // Rotation CSSValue rotation = birtStyle.getProperty(BirtStyle.TEXT_ROTATION); if (rotation instanceof FloatValue) { poiStyle.setRotation((short) ((FloatValue) rotation).getFloatValue()); } styles.add(new StylePair(birtStyle.clone(), poiStyle)); return poiStyle; }
From source file:Utility.TestReports.java
public void setHeaderCellStyle(org.apache.poi.hssf.usermodel.HSSFSheet sheet, org.apache.poi.ss.usermodel.Cell cell, org.apache.poi.hssf.usermodel.HSSFWorkbook wb) { CellStyle s = null; s = sheet.getWorkbook().createCellStyle(); cell.setCellStyle(s);/*from ww w. j a v a 2 s .co m*/ HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); s.setFont(font); s.setAlignment(CellStyle.ALIGN_GENERAL); s.setBorderBottom(CellStyle.BORDER_THIN); s.setBorderLeft(CellStyle.BORDER_THIN); s.setBorderRight(CellStyle.BORDER_THIN); s.setBorderTop(CellStyle.BORDER_THIN); s.setVerticalAlignment(CellStyle.VERTICAL_CENTER); s.setAlignment(CellStyle.ALIGN_LEFT); s.setFont(font); }