List of usage examples for org.apache.poi.ss.usermodel CellStyle setBorderLeft
void setBorderLeft(BorderStyle border);
From source file:reports.nutritionexcel.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {/*from w ww .jav 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./* w w w. jav a 2 s. c o m*/ * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 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.// w w w .j a v a2 s. c o m * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 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 {// ww w .j a va 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 = "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:ro.dabuno.office.integration.BusinessPlan.java
License:Apache License
private static CellStyle createBorderedStyle(Workbook wb) { BorderStyle thin = BorderStyle.THIN; short black = IndexedColors.BLACK.getIndex(); CellStyle style = wb.createCellStyle(); style.setBorderRight(thin);//from www . j a v a2 s .c o m style.setRightBorderColor(black); style.setBorderBottom(thin); style.setBottomBorderColor(black); style.setBorderLeft(thin); style.setLeftBorderColor(black); style.setBorderTop(thin); style.setTopBorderColor(black); return style; }
From source file:ro.dabuno.office.integration.LoadCalculator.java
/** * cell styles used for formatting calendar sheets *///from ww w .j av a 2 s. c o 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) 14); titleFont.setFontName("Trebuchet MS"); style = wb.createCellStyle(); style.setFont(titleFont); style.setBorderBottom(BorderStyle.DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); styles.put("title", style); Font itemFont = wb.createFont(); itemFont.setFontHeightInPoints((short) 9); itemFont.setFontName("Trebuchet MS"); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.LEFT); style.setFont(itemFont); styles.put("item_left", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.RIGHT); style.setFont(itemFont); styles.put("item_right", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.RIGHT); style.setFont(itemFont); style.setBorderRight(BorderStyle.DOTTED); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom(BorderStyle.DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft(BorderStyle.DOTTED); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop(BorderStyle.DOTTED); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat( wb.createDataFormat().getFormat("_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)")); styles.put("input_$", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.RIGHT); style.setFont(itemFont); style.setBorderRight(BorderStyle.DOTTED); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom(BorderStyle.DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft(BorderStyle.DOTTED); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop(BorderStyle.DOTTED); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("0.000%")); styles.put("input_%", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.RIGHT); style.setFont(itemFont); style.setBorderRight(BorderStyle.DOTTED); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom(BorderStyle.DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft(BorderStyle.DOTTED); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop(BorderStyle.DOTTED); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("0")); styles.put("input_i", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setFont(itemFont); style.setDataFormat(wb.createDataFormat().getFormat("m/d/yy")); styles.put("input_d", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.RIGHT); style.setFont(itemFont); style.setBorderRight(BorderStyle.DOTTED); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom(BorderStyle.DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft(BorderStyle.DOTTED); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop(BorderStyle.DOTTED); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("$##,##0.00")); style.setBorderBottom(BorderStyle.DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); styles.put("formula_$", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.RIGHT); style.setFont(itemFont); style.setBorderRight(BorderStyle.DOTTED); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom(BorderStyle.DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft(BorderStyle.DOTTED); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop(BorderStyle.DOTTED); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("0")); style.setBorderBottom(BorderStyle.DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); styles.put("formula_i", style); return styles; }
From source file:ru.wmbdiff.ExportIntoExcel.java
License:Apache License
public void export(File file, WMBDiffNoRootTreeTableModel model) { logger.info("export begin"); Workbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.createSheet("WMBDiff"); int rowNum = 0; //Create Header CellStyle style; Font headerFont = workbook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setColor(IndexedColors.WHITE.getIndex()); headerFont.setFontHeightInPoints((short) 10); style = workbook.createCellStyle();//from ww w . j a va 2 s .c o m style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.WHITE.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.WHITE.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.WHITE.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.WHITE.getIndex()); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.AQUA.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(headerFont); Row row = sheet.createRow(rowNum++); Cell cell; cell = row.createCell(0); cell.setCellValue("Result"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("Broker"); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue("Execution Group"); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue("Name"); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellValue("Type"); cell.setCellStyle(style); cell = row.createCell(5); cell.setCellValue("Last Modification"); cell.setCellStyle(style); cell = row.createCell(6); cell.setCellValue("Deployment Date"); cell.setCellStyle(style); cell = row.createCell(7); cell.setCellValue("Bar File"); cell.setCellStyle(style); cell = row.createCell(8); cell.setCellValue("Result Description"); cell.setCellStyle(style); sheet.createFreezePane(0, 1); List<DiffExecutionGroup> dEG = model.getDiffExecutionGroupList(); ListIterator<DiffExecutionGroup> litr = dEG.listIterator(); while (litr.hasNext()) { DiffExecutionGroup element = litr.next(); element.getDiffResultList(); ListIterator<DiffDeployedObjectResult> litr2 = element.getDiffResultList().listIterator(); while (litr2.hasNext()) { DiffDeployedObjectResult res = litr2.next(); switch (res.getResult()) { case ONLY_IN_A: createRow(rowNum++, sheet, res.getAObject(), "A", res.getResultDesc()); break; case ONLY_IN_B: createRow(rowNum++, sheet, res.getBObject(), "B", res.getResultDesc()); break; case EQUAL: createRow(rowNum++, sheet, res.getAObject(), "=", res.getResultDesc()); createRow(rowNum++, sheet, res.getBObject(), "=", res.getResultDesc()); sheet.groupRow(rowNum - 2, rowNum - 2); break; case DIFF: createRow(rowNum++, sheet, res.getAObject(), "!=", res.getResultDesc()); createRow(rowNum++, sheet, res.getBObject(), "!=", res.getResultDesc()); sheet.groupRow(rowNum - 2, rowNum - 2); break; } ; } ; } ; //Adjust column width to fit the contents for (int i = 0; i < 9; i++) sheet.autoSizeColumn(i); //set Filter sheet.setAutoFilter(new CellRangeAddress(0, rowNum - 1, 0, 8)); try { FileOutputStream out = new FileOutputStream(file); workbook.write(out); workbook.close(); out.close(); } catch (Exception e) { logger.error("export", e); } logger.info("export end"); }
From source file:Sales.MainMenu.java
private void submitToPublishingPDFButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_submitToPublishingPDFButtonActionPerformed String validityFrom = validityFromDatePicker.getJFormattedTextField().getText(); String validityTo = validityToDatePicker.getJFormattedTextField().getText(); String kkluNumber = kkluNumberTextField.getText(); String pol = pPolTextField.getText(); String pod = pPodTextField.getText(); String commClass = pCommodityClassComboBox.getSelectedItem().toString(); String handlingInstructions = pHandlingInstructions.getSelectedItem().toString(); String commDesc = pCommodityDescriptionTextField.getText(); String oft = pOftTextField.getText(); String oftUnit = pOftComboBox.getSelectedItem().toString(); String baf = null;// w w w. j a va2s . c o m String bafText = pBafTextField.getText(); Boolean bafIncluded = pBafIncludedCheckBox.isSelected(); String eca = null; String ecaText = pEcaTextField.getText(); String ecaUnit = pEcaComboBox.getSelectedItem().toString(); Boolean ecaIncluded = pEcaIncludedCheckBox.isSelected(); String thc = null; String thcText = "$" + pThcTextField.getText(); String thcUnit = pThcComboBox.getSelectedItem().toString(); Boolean thcIncluded = pThcIncludedCheckBox.isSelected(); Boolean thcSubjectToTariff = thcSubjectToTariffCheckBox.isSelected(); String wfg = null; String wfgText = pWfgTextField.getText(); String wfgUnit = pWfgComboBox.getSelectedItem().toString(); Boolean wfgIncluded = pWfgIncludedCheckBox.isSelected(); Boolean wfgSubjectToTariff = wfgSubjectToTariffCheckBox.isSelected(); String docFee = pDocFeeComboBox.getSelectedItem().toString(); Boolean docFeeIncluded = pDocFeeIncludedCheckBox.isSelected(); String comments = pCommentsTextArea.getText(); Boolean warRisk = pWarRiskCheckBox.isSelected(); String quoteID = pQuoteNumberTextField.getText(); String bookingNumber = pBookingNumberTextField.getText(); String storage = null; String storageText = storageTextField.getText(); String storageUnit = storageUnitComboBox.getSelectedItem().toString(); Boolean storageIncluded = storageIncludedCheckBox.isSelected(); Boolean storageSubjectToTariff = storageSubjectToTariffCheckBox.isSelected(); Boolean bafSubjectToTariff = bafSubjectToTariffCheckBox.isSelected(); Boolean ecaSubjectToTariff = ecaSubjectToTariffCheckBox.isSelected(); String TIME_STAMP = new SimpleDateFormat("MM/dd/yyyy HH:mm").format(Calendar.getInstance().getTime()); if (bafIncluded != true && bafSubjectToTariff != true) { baf = bafText; } else if (bafIncluded == true) { baf = "Included"; } else if (bafSubjectToTariff == true) { baf = "Subject to Tariff"; } if (ecaIncluded != true && ecaSubjectToTariff != true) { eca = ecaText; } else if (ecaIncluded == true) { eca = "Included"; } else if (ecaSubjectToTariff == true) { eca = "Subject to Tariff"; } else if (ecaSubjectToTariff == true) { eca = "Subject to Tariff"; } if (thcSubjectToTariff == true) { thc = "Subject to Tariff"; } else if (thcSubjectToTariff != true && thcIncluded != true) { thc = thcText; } else if (thcSubjectToTariff != true && thcIncluded == true) { thc = "Included"; } else if (thcIncluded == true && thcSubjectToTariff == true) { JOptionPane.showMessageDialog(null, "You can only select one"); } if (wfgSubjectToTariff == true) { wfg = "Subject to Tariff"; wfgUnit = "N/A"; } else if (wfgSubjectToTariff != true && wfgIncluded != true) { wfg = wfgText; } else if (wfgSubjectToTariff != true && wfgIncluded == true) { wfg = "Included"; } else if (wfgIncluded == true && wfgSubjectToTariff == true) { JOptionPane.showMessageDialog(null, "You can only select one"); } if (storageSubjectToTariff == true) { storage = "Subject to Tariff"; storageUnit = "N/A"; } else if (storageSubjectToTariff != true && storageIncluded != true) { storage = storageText; } else if (storageIncluded == true && storageSubjectToTariff != true) { storage = "Included"; } else if (storageIncluded == true && storageSubjectToTariff == true) { JOptionPane.showMessageDialog(null, "You can only select one"); } String sql = "INSERT INTO spotrates (validityFrom, validityTo, tariffNumber, pol, pod, bookingNumber, commClass, handlingInstructions, commDesc, oft, oftUnit, baf, bafIncluded, bafPerTariff, ecaBaf, ecaBafUnit, ecaIncluded, ecaPerTariff, thc, thcUnit,thcIncluded, thcPerTariff, wfg, wfgUnit, wfgIncluded, wfgPerTariff, storage, storageUnit, storageIncluded, storagePerTariff, docFee, docFeeIncluded, comments, quoteID, warRisk) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; try { PreparedStatement ps = CONN.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); ps.setString(1, validityFrom); ps.setString(2, validityTo); ps.setString(3, kkluNumber); ps.setString(4, pol); ps.setString(5, pod); ps.setString(6, bookingNumber); ps.setString(7, commClass); ps.setString(8, handlingInstructions); ps.setString(9, commDesc); ps.setString(10, oft); ps.setString(11, oftUnit); ps.setString(12, baf); ps.setBoolean(13, bafIncluded); ps.setBoolean(14, bafSubjectToTariff); ps.setString(15, eca); ps.setString(16, ecaUnit); ps.setBoolean(17, ecaIncluded); ps.setBoolean(18, ecaSubjectToTariff); ps.setString(19, thc); ps.setString(20, thcUnit); ps.setBoolean(21, thcIncluded); ps.setBoolean(22, thcSubjectToTariff); ps.setString(23, wfg); ps.setString(24, wfgUnit); ps.setBoolean(25, wfgIncluded); ps.setBoolean(26, wfgSubjectToTariff); ps.setString(27, storage); ps.setString(28, storageUnit); ps.setBoolean(29, storageIncluded); ps.setBoolean(30, storageSubjectToTariff); ps.setString(31, docFee); ps.setBoolean(32, docFeeIncluded); ps.setString(33, comments); ps.setString(34, quoteID); ps.setBoolean(35, warRisk); // Execute the update ps.executeUpdate(); //Return the auto-generated key ResultSet keys = ps.getGeneratedKeys(); int lastKey = 1; while (keys.next()) { lastKey = keys.getInt(1); } int pid = lastKey; Double oft1 = (Double) (Double.parseDouble(oft)); String eca1 = ""; switch (eca) { case "Included": eca1 = "Included"; break; case "Subject to Tariff": eca1 = "Subject to Tariff"; break; default: eca1 = eca; break; } String baf1; switch (baf) { case "Included": baf1 = "Included"; break; case "Subject To Tariff": baf1 = "Subject to Tariff"; break; default: baf1 = baf; break; } String thc1; switch (thc) { case "Included": thc1 = thc; break; case "Subject to Tariff": thc1 = "Subject to Tariff"; break; default: thc1 = "$" + thc + " per " + thcUnit; break; } switch (thcUnit) { case "FAS": thc1 = thcUnit; break; case "Subject to local charges": thc1 = thcUnit; default: break; } String wfg1; switch (wfg) { case "Included": wfg1 = wfg; break; case "Subject to Tariff": wfg1 = "Subject to Tariff"; break; default: wfg1 = "$" + wfg + " per " + wfgUnit; break; } switch (wfgUnit) { case "FAS": wfg1 = wfgUnit; break; case "Subject to local charges": wfg1 = wfgUnit; default: break; } /* *************************************** *************************************** *************************************** */ String filename = USER_HOME_FOLDER + "\\Desktop\\Publishing\\" + pol + " To " + pod + "; " + commDesc + " PID" + lastKey + ".xls"; HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("KKLU" + kkluNumber); sheet.setColumnWidth(0, 650); sheet.setColumnWidth(1, 5742); sheet.setColumnWidth(2, 5920); sheet.setColumnWidth(3, 3668); sheet.setColumnWidth(4, 5711); //Bold Font HSSFFont font = workbook.createFont(); font.setBold(true); CellStyle style = workbook.createCellStyle(); style.setFont(font); //Currency cell type CellStyle currency = workbook.createCellStyle(); currency.setDataFormat((short) 7); //Percentage cell type CellStyle percentage = workbook.createCellStyle(); percentage.setDataFormat((short) 0xa); sheet.setDisplayGridlines(false); //Black medium sized border around cell CellStyle blackBorder = workbook.createCellStyle(); blackBorder.setBorderBottom(CellStyle.BORDER_MEDIUM); blackBorder.setBottomBorderColor(IndexedColors.BLACK.getIndex()); blackBorder.setBorderLeft(CellStyle.BORDER_MEDIUM); blackBorder.setLeftBorderColor(IndexedColors.BLACK.getIndex()); blackBorder.setBorderRight(CellStyle.BORDER_MEDIUM); blackBorder.setRightBorderColor(IndexedColors.BLACK.getIndex()); blackBorder.setBorderTop(CellStyle.BORDER_MEDIUM); blackBorder.setTopBorderColor(IndexedColors.BLACK.getIndex()); //Red font CellStyle redFontStyle = workbook.createCellStyle(); HSSFFont redFont = workbook.createFont(); redFont.setColor(HSSFColor.RED.index); redFontStyle.setFont(redFont); HSSFRow rowhead = sheet.createRow((short) 0); rowhead.createCell(3).setCellValue(validityFrom); HSSFRow row1 = sheet.createRow((short) 1); Cell cell = row1.createCell(1); cell.setCellValue("FILING REQUEST TO RICLFILE"); cell.setCellStyle(style); sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 3)); sheet.setPrintGridlines(false); HSSFRow row2 = sheet.createRow((short) 2); row2.createCell(0).setCellValue("A)"); row2.createCell(1).setCellValue("Tariff Number(KKLU):"); row2.createCell(2).setCellValue(kkluNumber); HSSFRow space0 = sheet.createRow((short) 3); HSSFRow row4 = sheet.createRow((short) 4); row4.createCell(0).setCellValue("B)"); row4.createCell(1).setCellValue("Commodity:"); row4.createCell(2).setCellValue(commDesc); HSSFRow space2 = sheet.createRow((short) 5); HSSFRow row5 = sheet.createRow((short) 6); row5.createCell(0).setCellValue("C)"); row5.createCell(1).setCellValue("POL:"); row5.createCell(2).setCellValue(pol); HSSFRow space3 = sheet.createRow((short) 7); HSSFRow row6 = sheet.createRow((short) 8); row6.createCell(0).setCellValue("D)"); row6.createCell(1).setCellValue("POD:"); row6.createCell(2).setCellValue(pod); HSSFRow space4 = sheet.createRow((short) 9); HSSFRow row7 = sheet.createRow((short) 10); row7.createCell(0).setCellValue("E)"); row7.createCell(1).setCellValue("Rate:"); Cell rate = row7.createCell(2); rate.setCellValue(oft1); rate.setCellStyle(currency); HSSFRow space5 = sheet.createRow((short) 11); HSSFRow row8 = sheet.createRow((short) 12); row8.createCell(0).setCellValue("F)"); row8.createCell(1).setCellValue("Rate Basis:"); row8.createCell(2).setCellValue(oftUnit); HSSFRow space6 = sheet.createRow((short) 13); HSSFRow row9 = sheet.createRow((short) 14); row9.createCell(0).setCellValue("G)"); row9.createCell(1).setCellValue("BAF:"); Cell Baf = row9.createCell(2); switch (baf1) { case "Included": Baf.setCellValue("Included"); break; case "Subject to Tariff": Baf.setCellValue("Subject to Tariff"); break; default: Baf.setCellValue(baf1); Baf.setCellStyle(percentage); break; } HSSFRow space7 = sheet.createRow((short) 15); HSSFRow row10 = sheet.createRow((short) 16); row10.createCell(0).setCellValue("H)"); row10.createCell(1).setCellValue("ECA BAF:"); Cell ecaBaf = row10.createCell(2); switch (eca) { case "Included": ecaBaf.setCellValue("Included"); break; case "Subject to Tariff": ecaBaf.setCellValue("Subject to Tariff"); break; default: ecaBaf.setCellValue(eca1 + " per " + ecaUnit); ecaBaf.setCellStyle(currency); break; } HSSFRow space8 = sheet.createRow((short) 17); HSSFRow row11 = sheet.createRow((short) 18); row11.createCell(0).setCellValue("I)"); row11.createCell(1).setCellValue("THC/WFG:"); row11.createCell(2).setCellValue(thc1 + " / " + wfg1); HSSFRow space = sheet.createRow((short) 19); HSSFRow row12 = sheet.createRow((short) 20); row12.createCell(0).setCellValue("J)"); row12.createCell(1).setCellValue("Storage:"); row12.createCell(2).setCellValue(storage); HSSFRow space10 = sheet.createRow((short) 21); HSSFRow row13 = sheet.createRow((short) 22); row13.createCell(0).setCellValue("K)"); row13.createCell(1).setCellValue("Doc Fee:"); row13.createCell(2).setCellValue(docFee); HSSFRow space11 = sheet.createRow((short) 23); HSSFRow row14 = sheet.createRow((short) 24); row14.createCell(0).setCellValue("L)"); row14.createCell(1).setCellValue("War Risk:"); HSSFRow space12 = sheet.createRow((short) 25); if (warRisk == true) { String warRiskPercentage = "3%"; row14.createCell(2).setCellValue(warRiskPercentage); } else if (warRisk != true) { String warRiskPercentage = "N/A"; row14.createCell(2).setCellValue(warRiskPercentage); } HSSFRow row15 = sheet.createRow((short) 26); row15.createCell(0).setCellValue("M)"); row15.createCell(1).setCellValue("Validity"); row15.createCell(2).setCellValue("Effective: " + validityFrom); HSSFRow row16 = sheet.createRow((short) 27); row16.createCell(2).setCellValue("Expiration: " + validityTo); HSSFRow space13 = sheet.createRow((short) 28); HSSFRow row17 = sheet.createRow((short) 29); row17.createCell(0).setCellValue("N)"); row17.createCell(1).setCellValue("Remarks"); row17.createCell(2).setCellValue(comments); HSSFRow space14 = sheet.createRow((short) 30); HSSFRow row18 = sheet.createRow((short) 31); row18.createCell(0).setCellValue("O)"); row18.createCell(1).setCellValue("Booking #:"); row18.createCell(2).setCellValue(bookingNumber); HSSFRow space15 = sheet.createRow((short) 32); HSSFRow row19 = sheet.createRow((short) 33); row19.createCell(0).setCellValue("P)"); row19.createCell(1).setCellValue("RQS #:"); row19.createCell(2).setCellValue(quoteID); HSSFRow space16 = sheet.createRow((short) 34); HSSFRow row20 = sheet.createRow((short) 35); row20.createCell(0).setCellValue("Q)"); row20.createCell(1).setCellValue("PID #:"); row20.createCell(2).setCellValue(pid); HSSFRow space17 = sheet.createRow((short) 36); HSSFRow space18 = sheet.createRow((short) 37); HSSFRow row21 = sheet.createRow((short) 38); row21.createCell(0).setCellValue(""); row21.createCell(1).setCellValue("For RICLFILE Use Only"); HSSFRow space19 = sheet.createRow((short) 39); HSSFRow row22 = sheet.createRow((short) 40); Cell comm = row22.createCell(1); comm.setCellValue("Commodity #:"); comm.setCellStyle(redFontStyle); row22.createCell(2).setCellValue(""); Cell desc = row22.createCell(3); desc.setCellValue("Description:"); desc.setCellStyle(redFontStyle); row22.createCell(4).setCellValue(""); HSSFRow space20 = sheet.createRow((short) 41); HSSFRow row24 = sheet.createRow((short) 42); Cell TLI = row24.createCell(1); TLI.setCellValue("TLI #:"); TLI.setCellStyle(redFontStyle); row24.createCell(2).setCellValue(""); HSSFRow space21 = sheet.createRow((short) 43); HSSFRow row26 = sheet.createRow((short) 44); Cell exp = row26.createCell(1); exp.setCellValue("Expiration: "); exp.setCellStyle(redFontStyle); row26.createCell(2).setCellValue(""); try (FileOutputStream fileOut = new FileOutputStream(filename)) { workbook.write(fileOut); } System.out.print("Your excel file has been generate"); String spotRateId = String.valueOf(lastKey); JOptionPane.showMessageDialog(null, "The spot filing (PID" + spotRateId + ") for " + quoteID + " has been succsefully generated."); pQuoteIDTextField.setText(""); validityFromDatePicker.getJFormattedTextField().setText(""); validityToDatePicker.getJFormattedTextField().setText(""); kkluNumberTextField.setText(""); pPolTextField.setText(""); pPodTextField.setText(""); pCommodityClassComboBox.setSelectedIndex(0); pHandlingInstructions.setSelectedIndex(0); pCommodityDescriptionTextField.setText(""); pOftTextField.setText(""); pOftComboBox.setSelectedItem(""); bafSubjectToTariffCheckBox.setSelected(false); pBafTextField.setText(""); pBafIncludedCheckBox.setSelected(false); pEcaTextField.setText(""); pEcaComboBox.setSelectedIndex(0); pEcaIncludedCheckBox.setSelected(false); ecaSubjectToTariffCheckBox.setSelected(false); pThcTextField.setText(""); pThcComboBox.setSelectedIndex(0); pThcIncludedCheckBox.setSelected(false); thcSubjectToTariffCheckBox.setSelected(false); pWfgTextField.setText(""); pWfgComboBox.setSelectedIndex(0); pWfgIncludedCheckBox.setSelected(false); wfgSubjectToTariffCheckBox.setSelected(false); pDocFeeComboBox.setSelectedIndex(0); pWarRiskCheckBox.setSelected(false); storageSubjectToTariffCheckBox.setSelected(false); pCommentsTextArea.setText(""); pBookingNumberTextField.setText(""); pQuoteNumberTextField.setText(""); pIDTextField.setText(""); } catch (SQLException | IOException e) { JOptionPane.showMessageDialog(null, "Error: " + e.getMessage()); System.out.println(e.getMessage()); } }
From source file:Sales.MainMenu.java
private void saveChangesPublishingPDFButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_saveChangesPublishingPDFButtonActionPerformed // Save spot rate changes String validityFrom = validityFromDatePicker.getJFormattedTextField().getText(); String validityTo = validityToDatePicker.getJFormattedTextField().getText(); String kkluNumber = kkluNumberTextField.getText(); String pol = pPolTextField.getText(); String pod = pPodTextField.getText(); String commClass = pCommodityClassComboBox.getSelectedItem().toString(); String handlingInstructions = pHandlingInstructions.getSelectedItem().toString(); String commDesc = pCommodityDescriptionTextField.getText(); String oft = pOftTextField.getText(); String oftUnit = pOftComboBox.getSelectedItem().toString(); String baf = null;/*from w w w . j a v a2s. c o m*/ String bafText = pBafTextField.getText(); Boolean bafIncluded = pBafIncludedCheckBox.isSelected(); String eca = null; String ecaText = pEcaTextField.getText(); String ecaUnit = pEcaComboBox.getSelectedItem().toString(); Boolean ecaIncluded = pEcaIncludedCheckBox.isSelected(); String thc = null; String thcText = "$" + pThcTextField.getText(); String thcUnit = pThcComboBox.getSelectedItem().toString(); Boolean thcIncluded = pThcIncludedCheckBox.isSelected(); Boolean thcSubjectToTariff = thcSubjectToTariffCheckBox.isSelected(); String wfg = null; String wfgText = pWfgTextField.getText(); String wfgUnit = pWfgComboBox.getSelectedItem().toString(); Boolean wfgIncluded = pWfgIncludedCheckBox.isSelected(); Boolean wfgSubjectToTariff = wfgSubjectToTariffCheckBox.isSelected(); String docFee = pDocFeeComboBox.getSelectedItem().toString(); Boolean docFeeIncluded = pDocFeeIncludedCheckBox.isSelected(); String comments = pCommentsTextArea.getText(); Boolean warRisk = pWarRiskCheckBox.isSelected(); String quoteID = pQuoteNumberTextField.getText(); String bookingNumber = pBookingNumberTextField.getText(); String storage = null; String storageText = storageTextField.getText(); String storageUnit = storageUnitComboBox.getSelectedItem().toString(); Boolean storageIncluded = storageIncludedCheckBox.isSelected(); Boolean storageSubjectToTariff = storageSubjectToTariffCheckBox.isSelected(); Boolean bafSubjectToTariff = bafSubjectToTariffCheckBox.isSelected(); Boolean ecaSubjectToTariff = ecaSubjectToTariffCheckBox.isSelected(); String ID = pQuoteIDTextField.getText(); String timeStamp = new SimpleDateFormat("MM/dd/yyyy HH:mm").format(Calendar.getInstance().getTime()); if (bafIncluded != true && bafSubjectToTariff != true) { baf = bafText; } else if (bafIncluded == true) { baf = "Included"; } else if (bafSubjectToTariff == true) { baf = "Subject to Tariff"; } if (ecaIncluded != true && ecaSubjectToTariff != true) { eca = ecaText; } else if (ecaIncluded == true) { eca = "Included"; } else if (ecaSubjectToTariff == true) { eca = "Subject to Tariff"; } else if (ecaSubjectToTariff == true) { eca = "Subject to Tariff"; } if (thcSubjectToTariff == true) { thc = "Subject to Tariff"; } else if (thcSubjectToTariff != true && thcIncluded != true) { thc = thcText; } else if (thcSubjectToTariff != true && thcIncluded == true) { thc = "Included"; } else if (thcIncluded == true && thcSubjectToTariff == true) { JOptionPane.showMessageDialog(null, "You can only select one"); } if (wfgSubjectToTariff == true) { wfg = "Subject to Tariff"; wfgUnit = "N/A"; } else if (wfgSubjectToTariff != true && wfgIncluded != true) { wfg = wfgText; } else if (wfgSubjectToTariff != true && wfgIncluded == true) { wfg = "Included"; } else if (wfgIncluded == true && wfgSubjectToTariff == true) { JOptionPane.showMessageDialog(null, "You can only select one"); } if (storageSubjectToTariff == true) { storage = "Subject to Tariff"; storageUnit = "N/A"; } else if (storageSubjectToTariff != true && storageIncluded != true) { storage = storageText; } else if (storageIncluded == true && storageSubjectToTariff != true) { storage = "Included"; } else if (storageIncluded == true && storageSubjectToTariff == true) { JOptionPane.showMessageDialog(null, "You can only select one"); } String sql = "UPDATE spotrates SET validityFrom=?, validityTo=?, tariffNumber=?, pol=?, pod=?, bookingNumber=?, commClass=?, handlingInstructions=?, commDesc=?, oft=?, oftUnit=?, baf=?,bafIncluded=?, bafPerTariff=?, ecaBaf=?, ecaBafUnit=?, ecaIncluded=?, ecaPerTariff=?, thc=?, thcUnit=?, thcIncluded=?, thcPerTariff=?, wfg=?, wfgUnit=?, wfgIncluded=?, wfgPerTariff=?, storage=?, storageUnit=?, storageIncluded=?, storagePerTariff=?, docFee=?, docFeeIncluded=?, comments=?, quoteID=?, warRisk=? WHERE ID=?"; try { PreparedStatement ps = CONN.prepareStatement(sql); ps.setString(1, validityFrom); ps.setString(2, validityTo); ps.setString(3, kkluNumber); ps.setString(4, pol); ps.setString(5, pod); ps.setString(6, bookingNumber); ps.setString(7, commClass); ps.setString(8, handlingInstructions); ps.setString(9, commDesc); ps.setString(10, oft); ps.setString(11, oftUnit); ps.setString(12, baf); ps.setBoolean(13, bafIncluded); ps.setBoolean(14, bafSubjectToTariff); ps.setString(15, eca); ps.setString(16, ecaUnit); ps.setBoolean(17, ecaIncluded); ps.setBoolean(18, ecaSubjectToTariff); ps.setString(19, thc); ps.setString(20, thcUnit); ps.setBoolean(21, thcIncluded); ps.setBoolean(22, thcSubjectToTariff); ps.setString(23, wfg); ps.setString(24, wfgUnit); ps.setBoolean(25, wfgIncluded); ps.setBoolean(26, wfgSubjectToTariff); ps.setString(27, storage); ps.setString(28, storageUnit); ps.setBoolean(29, storageIncluded); ps.setBoolean(30, storageSubjectToTariff); ps.setString(31, docFee); ps.setBoolean(32, docFeeIncluded); ps.setString(33, comments); ps.setString(34, quoteID); ps.setBoolean(35, warRisk); ps.setString(40, ID); ps.executeUpdate(); String addBookingNumber = "UPDATE allquotes SET bookingNumber='" + bookingNumber + "', publishingID='" + ID + "' WHERE ID='" + quoteID + "';"; PreparedStatement psAddBookingNumber = CONN.prepareStatement(addBookingNumber); psAddBookingNumber.executeUpdate(addBookingNumber); Double oft1 = (Double) (Double.parseDouble(oft)); String eca1 = ""; switch (eca) { case "Included": eca1 = "Included"; break; case "Subject to Tariff": eca1 = "Subject to Tariff"; break; default: eca1 = "$" + eca + " " + ecaUnit; break; } String baf1; switch (baf) { case "Included": baf1 = "Included"; break; case "Subject To Tariff": baf1 = "Subject to Tariff"; break; default: baf1 = baf + "%"; break; } String thc1; switch (thc) { case "Included": thc1 = thc; break; case "Subject to Tariff": thc1 = "Subject to Tariff"; break; default: thc1 = "$" + thc + " per " + thcUnit; break; } switch (thcUnit) { case "FAS": thc1 = thcUnit; break; case "Subject to local charges": thc1 = thcUnit; default: break; } String wfg1; switch (wfg) { case "Included": wfg1 = wfg; break; case "Subject to Tariff": wfg1 = "Subject to Tariff"; break; default: wfg1 = "$" + wfg + " per " + wfgUnit; break; } switch (wfgUnit) { case "FAS": wfg1 = wfgUnit; break; case "Subject to local charges": wfg1 = wfgUnit; default: break; } /* *************************************** *************************************** *************************************** */ String filename = USER_HOME_FOLDER + "\\Desktop\\Publishing\\" + pol + " To " + pod + "; " + commDesc + " PID" + ID + ".xls"; HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("KKLU" + kkluNumber); sheet.setColumnWidth(0, 650); sheet.setColumnWidth(1, 5742); sheet.setColumnWidth(2, 5920); sheet.setColumnWidth(3, 3668); sheet.setColumnWidth(4, 5711); //Bold Font HSSFFont font = workbook.createFont(); font.setBold(true); CellStyle style = workbook.createCellStyle(); style.setFont(font); //Currency cell type CellStyle currency = workbook.createCellStyle(); currency.setDataFormat((short) 7); //Percentage cell type CellStyle percentage = workbook.createCellStyle(); percentage.setDataFormat((short) 0xa); sheet.setDisplayGridlines(false); //Black medium sized border around cell CellStyle blackBorder = workbook.createCellStyle(); blackBorder.setBorderBottom(CellStyle.BORDER_MEDIUM); blackBorder.setBottomBorderColor(IndexedColors.BLACK.getIndex()); blackBorder.setBorderLeft(CellStyle.BORDER_MEDIUM); blackBorder.setLeftBorderColor(IndexedColors.BLACK.getIndex()); blackBorder.setBorderRight(CellStyle.BORDER_MEDIUM); blackBorder.setRightBorderColor(IndexedColors.BLACK.getIndex()); blackBorder.setBorderTop(CellStyle.BORDER_MEDIUM); blackBorder.setTopBorderColor(IndexedColors.BLACK.getIndex()); //Red font CellStyle redFontStyle = workbook.createCellStyle(); HSSFFont redFont = workbook.createFont(); redFont.setColor(HSSFColor.RED.index); redFontStyle.setFont(redFont); HSSFRow rowhead = sheet.createRow((short) 0); rowhead.createCell(3).setCellValue(validityFrom); HSSFRow row1 = sheet.createRow((short) 1); Cell cell = row1.createCell(1); cell.setCellValue("FILING REQUEST TO RICLFILE"); cell.setCellStyle(style); sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 3)); sheet.setPrintGridlines(false); HSSFRow row2 = sheet.createRow((short) 2); row2.createCell(0).setCellValue("A)"); row2.createCell(1).setCellValue("Tariff Number(KKLU):"); row2.createCell(2).setCellValue(kkluNumber); HSSFRow space0 = sheet.createRow((short) 3); HSSFRow row4 = sheet.createRow((short) 4); row4.createCell(0).setCellValue("B)"); row4.createCell(1).setCellValue("Commodity:"); row4.createCell(2).setCellValue(commDesc); HSSFRow space2 = sheet.createRow((short) 5); HSSFRow row5 = sheet.createRow((short) 6); row5.createCell(0).setCellValue("C)"); row5.createCell(1).setCellValue("POL:"); row5.createCell(2).setCellValue(pol); HSSFRow space3 = sheet.createRow((short) 7); HSSFRow row6 = sheet.createRow((short) 8); row6.createCell(0).setCellValue("D)"); row6.createCell(1).setCellValue("POD:"); row6.createCell(2).setCellValue(pod); HSSFRow space4 = sheet.createRow((short) 9); HSSFRow row7 = sheet.createRow((short) 10); row7.createCell(0).setCellValue("E)"); row7.createCell(1).setCellValue("Rate:"); Cell rate = row7.createCell(2); rate.setCellValue(oft1); rate.setCellStyle(currency); HSSFRow space5 = sheet.createRow((short) 11); HSSFRow row8 = sheet.createRow((short) 12); row8.createCell(0).setCellValue("F)"); row8.createCell(1).setCellValue("Rate Basis:"); row8.createCell(2).setCellValue(oftUnit); HSSFRow space6 = sheet.createRow((short) 13); HSSFRow row9 = sheet.createRow((short) 14); row9.createCell(0).setCellValue("G)"); row9.createCell(1).setCellValue("BAF:"); Cell Baf = row9.createCell(2); switch (baf1) { case "Included": Baf.setCellValue("Included"); break; case "Subject to Tariff": Baf.setCellValue("Subject to Tariff"); break; default: Baf.setCellValue(baf1); break; } HSSFRow space7 = sheet.createRow((short) 15); HSSFRow row10 = sheet.createRow((short) 16); row10.createCell(0).setCellValue("H)"); row10.createCell(1).setCellValue("ECA BAF:"); Cell ecaBaf = row10.createCell(2); switch (eca) { case "Included": ecaBaf.setCellValue("Included"); break; case "Subject to Tariff": ecaBaf.setCellValue("Subject to Tariff"); break; default: ecaBaf.setCellValue("$" + eca1 + " per " + ecaUnit); ecaBaf.setCellStyle(currency); break; } HSSFRow space8 = sheet.createRow((short) 17); HSSFRow row11 = sheet.createRow((short) 18); row11.createCell(0).setCellValue("I)"); row11.createCell(1).setCellValue("THC/WFG:"); row11.createCell(2).setCellValue(thc1 + " / " + wfg1); HSSFRow space = sheet.createRow((short) 19); HSSFRow row12 = sheet.createRow((short) 20); row12.createCell(0).setCellValue("J)"); row12.createCell(1).setCellValue("Storage:"); row12.createCell(2).setCellValue(storage); HSSFRow space10 = sheet.createRow((short) 21); HSSFRow row13 = sheet.createRow((short) 22); row13.createCell(0).setCellValue("K)"); row13.createCell(1).setCellValue("Doc Fee:"); row13.createCell(2).setCellValue(docFee); HSSFRow space11 = sheet.createRow((short) 23); HSSFRow row14 = sheet.createRow((short) 24); row14.createCell(0).setCellValue("L)"); row14.createCell(1).setCellValue("War Risk:"); HSSFRow space12 = sheet.createRow((short) 25); if (warRisk == true) { String warRiskPercentage = "3%"; row14.createCell(2).setCellValue(warRiskPercentage); } else if (warRisk != true) { String warRiskPercentage = "N/A"; row14.createCell(2).setCellValue(warRiskPercentage); } HSSFRow row15 = sheet.createRow((short) 26); row15.createCell(0).setCellValue("M)"); row15.createCell(1).setCellValue("Validity"); row15.createCell(2).setCellValue("Effective: " + validityFrom); HSSFRow row16 = sheet.createRow((short) 27); row16.createCell(2).setCellValue("Expiration: " + validityTo); HSSFRow space13 = sheet.createRow((short) 28); HSSFRow row17 = sheet.createRow((short) 29); row17.createCell(0).setCellValue("N)"); row17.createCell(1).setCellValue("Remarks"); row17.createCell(2).setCellValue(comments); HSSFRow space14 = sheet.createRow((short) 30); HSSFRow row18 = sheet.createRow((short) 31); row18.createCell(0).setCellValue("O)"); row18.createCell(1).setCellValue("Booking #:"); row18.createCell(2).setCellValue(bookingNumber); HSSFRow space15 = sheet.createRow((short) 32); HSSFRow row19 = sheet.createRow((short) 33); row19.createCell(0).setCellValue("P)"); row19.createCell(1).setCellValue("RQS #:"); row19.createCell(2).setCellValue(quoteID); HSSFRow space16 = sheet.createRow((short) 34); HSSFRow row20 = sheet.createRow((short) 35); row20.createCell(0).setCellValue("Q)"); row20.createCell(1).setCellValue("PID #:"); row20.createCell(2).setCellValue(ID); HSSFRow space17 = sheet.createRow((short) 36); HSSFRow space18 = sheet.createRow((short) 37); HSSFRow row21 = sheet.createRow((short) 38); row21.createCell(0).setCellValue(""); row21.createCell(1).setCellValue("For RICLFILE Use Only"); HSSFRow space19 = sheet.createRow((short) 39); HSSFRow row22 = sheet.createRow((short) 40); Cell comm = row22.createCell(1); comm.setCellValue("Commodity #:"); comm.setCellStyle(redFontStyle); row22.createCell(2).setCellValue(""); Cell desc = row22.createCell(3); desc.setCellValue("Description:"); desc.setCellStyle(redFontStyle); row22.createCell(4).setCellValue(""); HSSFRow space20 = sheet.createRow((short) 41); HSSFRow row24 = sheet.createRow((short) 42); Cell TLI = row24.createCell(1); TLI.setCellValue("TLI #:"); TLI.setCellStyle(redFontStyle); row24.createCell(2).setCellValue(""); HSSFRow space21 = sheet.createRow((short) 43); HSSFRow row26 = sheet.createRow((short) 44); Cell exp = row26.createCell(1); exp.setCellValue("Expiration: "); exp.setCellStyle(redFontStyle); row26.createCell(2).setCellValue(""); try (FileOutputStream fileOut = new FileOutputStream(filename)) { workbook.write(fileOut); } System.out.print("Your excel file has been generate"); String spotRateId = String.valueOf(ID); JOptionPane.showMessageDialog(null, "PID" + ID + " has been successfully update."); pQuoteIDTextField.setText(""); validityFromDatePicker.getJFormattedTextField().setText(""); validityToDatePicker.getJFormattedTextField().setText(""); kkluNumberTextField.setText(""); pPolTextField.setText(""); pPodTextField.setText(""); pCommodityClassComboBox.setSelectedIndex(0); pHandlingInstructions.setSelectedIndex(0); pCommodityDescriptionTextField.setText(""); pOftTextField.setText(""); pOftComboBox.setSelectedItem(""); bafSubjectToTariffCheckBox.setSelected(false); pBafTextField.setText(""); pBafIncludedCheckBox.setSelected(false); pEcaTextField.setText(""); pEcaComboBox.setSelectedItem(""); pEcaIncludedCheckBox.setSelected(false); ecaSubjectToTariffCheckBox.setSelected(false); pThcTextField.setText(""); pThcComboBox.setSelectedIndex(0); pThcIncludedCheckBox.setSelected(false); thcSubjectToTariffCheckBox.setSelected(false); pWfgTextField.setText(""); pWfgComboBox.setSelectedIndex(0); pWfgIncludedCheckBox.setSelected(false); wfgSubjectToTariffCheckBox.setSelected(false); pDocFeeComboBox.setSelectedIndex(0); pWarRiskCheckBox.setSelected(false); storageSubjectToTariffCheckBox.setSelected(false); pCommentsTextArea.setText(""); pBookingNumberTextField.setText(""); storageTextField.setText(""); storageUnitComboBox.setSelectedIndex(0); storageIncludedCheckBox.setSelected(false); pQuoteNumberTextField.setText(""); pIDTextField.setText(""); } catch (SQLException | IOException e) { JOptionPane.showMessageDialog(null, "Error: " + e.getMessage()); System.out.println(e.getMessage()); } }
From source file:se.mithlond.services.content.impl.ejb.report.ExcelReportServiceBean.java
License:Apache License
/** * {@inheritDoc}//from w w w .j a v 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; }