List of usage examples for org.apache.poi.xssf.usermodel XSSFFont setFontHeightInPoints
public void setFontHeightInPoints(short height)
From source file:reports.kePMSServices.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException, InvalidFormatException { session = request.getSession();//www . jav a 2 s .c o m dbConn conn = new dbConn(); incrementor = 0; pos = 0; pepfaryear = Integer.parseInt(session.getAttribute("PepfarYear").toString()); prevyear = pepfaryear - 1; String[] periods = session.getAttribute("period").toString().split("-"); if (session.getAttribute("period").toString().equals("10-12")) { period = "OCT-DEC"; } else if (session.getAttribute("period").toString().equals("01-03")) { period = "JAN-MARCH"; } else if (session.getAttribute("period").toString().equals("04-06")) { period = "APRIL-JUNE"; } else if (session.getAttribute("period").toString().equals("07-09")) { period = "JULY-SEPT"; } else { } startdate = periods[0]; enddate = periods[1]; if (session.getAttribute("period").toString().equals("10-12")) { pepfaryear = pepfaryear - 1; } start = Integer.parseInt(startdate); end = Integer.parseInt(enddate); Path original = Paths.get(getServletContext().getRealPath("/ServicesAll.xlsm")); //original file Path destination = Paths.get(getServletContext().getRealPath("/ServicesAll_1.xlsm")); //new file System.out.println("origin : " + original + " destination : " + destination); try { Files.copy(original, destination, StandardCopyOption.REPLACE_EXISTING); System.out.println("file copied----------------"); } catch (IOException x) { //catch all for IO problems System.out.println("fine not copied"); } String allpath = getServletContext().getRealPath("/ServicesAll_1.xlsm"); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFWorkbook wb; OPCPackage pkg = OPCPackage.open(allpath); wb = new XSSFWorkbook(pkg); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFSheet shet1 = wb.getSheet("sheet1"); XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); // font.setItalic(true); // font.setBoldweight((short)12); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); // font2.setFontHeightInPoints((short)15); font2.setFontName("Arial Black"); // font.setItalic(true); // font2.setBoldweight((short)18); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); // stborder.setFillForegroundColor(HSSFColor.ORANGE.index); // stborder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // HSSFSheet sheet1 = wb.getSheetAt(0); shet1.setColumnWidth(0, 6000); shet1.setColumnWidth(1, 6000); shet1.setColumnWidth(2, 6000); shet1.setColumnWidth(3, 6000); shet1.setColumnWidth(4, 6000); shet1.setColumnWidth(5, 6000); shet1.setColumnWidth(6, 6000); shet1.setColumnWidth(7, 6000); shet1.setColumnWidth(8, 6000); shet1.setColumnWidth(9, 6000); shet1.setColumnWidth(10, 6000); shet1.setColumnWidth(11, 6000); // shet1.setColumnWidth(20, 2000); XSSFCellStyle styleBorder = wb.createCellStyle(); styleBorder.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorder.setFillForegroundColor(HSSFColor.ORANGE.index); styleBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // CREATE HEADING 2 XSSFRow rheading2 = shet1.createRow(0); rheading2.setHeightInPoints(25); XSSFCell cellxx1, cellxx2, cellxx3, cellxx4, cellxx5, cellxx6, cellxx7, cellxx8, cellxx9, cellxx10, cellxx11, cellxx12, cellxx13, cellxx14, cellxx15; cellxx1 = rheading2.createCell(0); cellxx2 = rheading2.createCell(1); cellxx3 = rheading2.createCell(2); cellxx4 = rheading2.createCell(3); cellxx5 = rheading2.createCell(4); cellxx6 = rheading2.createCell(5); cellxx7 = rheading2.createCell(6); cellxx8 = rheading2.createCell(7); cellxx9 = rheading2.createCell(8); cellxx10 = rheading2.createCell(9); cellxx11 = rheading2.createCell(10); cellxx12 = rheading2.createCell(11); cellxx13 = rheading2.createCell(12); // cellxx14=rheading2.createCell(13); // cellxx15=rheading2.createCell(14); cellxx1.setCellValue("COUNTY NAME"); cellxx2.setCellValue("PARTNER NAME"); cellxx3.setCellValue("DISTRICT NAME"); cellxx4.setCellValue("GENDER"); cellxx5.setCellValue("CONTRACEPTIVE METHOD"); cellxx6.setCellValue("REFERRED TO A SERVICE POINT"); cellxx7.setCellValue("GIVEN CONDOMS"); cellxx8.setCellValue("SCREENED FOR TB"); cellxx9.setCellValue("SCREENED FOR STIS"); cellxx10.setCellValue("TESTED PARTNER"); cellxx11.setCellValue("TESTED CHILDREN"); cellxx12.setCellValue("DISCLOSED STATUS"); // cellxx13.setCellValue("YEAR"); // cellxx14.setCellValue("MONTH"); cellxx13.setCellValue("AGE BRACKET"); cellxx1.setCellStyle(styleBorder); cellxx2.setCellStyle(styleBorder); cellxx3.setCellStyle(styleBorder); cellxx4.setCellStyle(styleBorder); cellxx5.setCellStyle(styleBorder); cellxx6.setCellStyle(styleBorder); cellxx7.setCellStyle(styleBorder); cellxx8.setCellStyle(styleBorder); cellxx9.setCellStyle(styleBorder); cellxx10.setCellStyle(styleBorder); cellxx11.setCellStyle(styleBorder); cellxx12.setCellStyle(styleBorder); cellxx13.setCellStyle(styleBorder); // cellxx14.setCellStyle(styleBorder); // cellxx15.setCellStyle(styleBorder); pos = 1; XSSFCellStyle stylex = wb.createCellStyle(); //stylex.setFillForegroundColor(HSSFColor.LIME.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); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); String getServices = "SELECT client_id,COUNTY,GENDER, bit_or(cm) AS CONTRACEPTIVE_METHOD,bit_or(sp) AS REFERRED_TO_SERVICE_POINT, " + "SUM(cg) AS CONDOMS_GIVEN ,bit_or(st) AS SCREENED_TB ,bit_or(ss) AS SCREENED_STIS,bit_or(tp) TESTED_PARTNER, " + "bit_or(tc) AS TESTED_CHILDREN,bit_or(ds) as DISCLOSED_STATUS,year AS pepfaryear,month as pepfarmonth,partnername as partner,districtname as district,AGEBRACKET FROM ( " + "SELECT personal_information.client_id as client_id,county.county_name as COUNTY,personal_information.gender as GENDER, " + " CASE " + " WHEN services_provided.contraceptive_method= 'YES' THEN 1 " + " WHEN services_provided.contraceptive_method= 'NO' THEN 0 " + "ELSE 'NONE' " + "END AS cm, " + "CASE " + " WHEN services_provided.rsp LIKE 'YES' THEN 1 " + " WHEN services_provided.rsp LIKE 'NO' THEN 0 " + "ELSE 'NONE' " + "END AS sp, " + " services_provided.cds_given AS cg, " + "CASE " + " WHEN services_provided.screened_tb='YES' THEN 1 " + " WHEN services_provided.screened_tb='NO' THEN 0 " + "ELSE 'NONE' " + "END AS st, " + "CASE " + " WHEN services_provided.screened_stis='YES' THEN 1 " + " WHEN services_provided.screened_stis='NO' THEN 0 " + "ELSE 'NONE' " + "END AS ss, " + "CASE " + " WHEN services_provided.tested_partner='YES' THEN 1 " + " WHEN services_provided.tested_partner='NO' THEN 0 " + "ELSE 'NONE' " + "END AS tp, " + "CASE " + " WHEN services_provided.tested_children= 'YES' THEN 1 " + " WHEN services_provided.tested_children= 'NO' THEN 0 " + "ELSE 'NONE' " + "END AS tc, " + "CASE " + " WHEN services_provided.disclosed_status= 'YES' THEN 1 " + " WHEN services_provided.disclosed_status= 'NO' THEN 0 " + "ELSE 'NONE' " + "END AS ds,services_provided.submission_month AS month,services_provided.submission_year as year,partner.partner_name as partnername," + "district.district_name as districtname," // + " CASE " // + " WHEN services_provided.submission_month=1 THEN 'JAN' " // + " WHEN services_provided.submission_month=2 THEN 'FEB' " // + " WHEN services_provided.submission_month=3 THEN 'MAR' " // + " WHEN services_provided.submission_month=4 THEN 'APR' " // + " WHEN services_provided.submission_month=5 THEN 'MAY' " // + " WHEN services_provided.submission_month=6 THEN 'JUN' " // + " WHEN services_provided.submission_month=7 THEN 'JUL' " // + " WHEN services_provided.submission_month=8 THEN 'AUG' " // + " WHEN services_provided.submission_month=9 THEN 'SEPT' " // + " WHEN services_provided.submission_month=10 THEN 'OCT' " // + " WHEN services_provided.submission_month=11 THEN 'NOV' " // + " WHEN services_provided.submission_month=12 THEN 'DEC'" // + "ELSE 'NO MONTH' END AS MONTHNAME," + "CASE" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 0 AND 9 THEN '0-9'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 10 AND 14 THEN '10-14'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 15 AND 19 THEN '15-19'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 20 AND 24 THEN '20-24'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 25 AND 49 THEN '25-49'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) >49 THEN '50 and above'" + " ELSE 'NO DATE OF BIRTH' " + " END AS AGEBRACKET " + " FROM personal_information JOIN partner ON personal_information.partner_id=partner.partner_id " + "" + "" + " JOIN (district JOIN county ON county.county_id=district.county_id) ON district.district_id=personal_information.district_id " + " JOIN services_provided ON services_provided.client_id=personal_information.client_id" + " WHERE services_provided.submission_month>='" + startdate + "' && services_provided.submission_month<='" + enddate + "' " + "&& services_provided.submission_year='" + pepfaryear + "' order by personal_information.client_id ) as temptbl" + " WHERE (cm>0 || sp>0 || cg>0 || st>0 || ss>0" + " || tp>0 || tc>0 || ds>0) GROUP BY client_id ORDER BY client_id"; conn.rs = conn.st.executeQuery(getServices); while (conn.rs.next()) { countyname = conn.rs.getString(2); gender = conn.rs.getString(3); contraceptive_method = conn.rs.getInt(4); rsp = conn.rs.getInt(5); cds_given = conn.rs.getInt(6); screened_tb = conn.rs.getInt(7); screened_stis = conn.rs.getInt(8); tested_partner = conn.rs.getInt(9); tested_children = conn.rs.getInt(10); disclosed_status = conn.rs.getInt(11); datekey = Integer.parseInt(conn.rs.getInt(12) + "" + conn.rs.getInt(13)); partner = conn.rs.getString(14); district = conn.rs.getString(15); // monthname=conn.rs.getString(16); agebracket = conn.rs.getString(16); if (contraceptive_method > 0 || rsp > 0 || cds_given > 0 || screened_tb > 0 || screened_stis > 0 || tested_partner > 0 || tested_children > 0 || disclosed_status > 0) { // CREATE ROW AND ADD DATA TO THE DATA CELLS====================== incrementor++; XSSFRow data = shet1.createRow(pos); data.setHeightInPoints(25); XSSFCell cellx1, cellx2, cellx3, cellx4, cellx5, cellx6, cellx7, cellx8, cellx9, cellx10, cellx11, cellx12, cellx13, cellx14, cellx15; cellx1 = data.createCell(0); cellx2 = data.createCell(1); cellx3 = data.createCell(2); cellx4 = data.createCell(3); cellx5 = data.createCell(4); cellx6 = data.createCell(5); cellx7 = data.createCell(6); cellx8 = data.createCell(7); cellx9 = data.createCell(8); cellx10 = data.createCell(9); cellx11 = data.createCell(10); cellx12 = data.createCell(11); cellx13 = data.createCell(12); // cellx14=data.createCell(13); // cellx15=data.createCell(14); cellx1.setCellValue(countyname); cellx2.setCellValue(partner); cellx3.setCellValue(district); cellx4.setCellValue(gender); cellx5.setCellValue(contraceptive_method); cellx6.setCellValue(rsp); cellx7.setCellValue(cds_given); cellx8.setCellValue(screened_tb); cellx9.setCellValue(screened_stis); cellx10.setCellValue(tested_partner); cellx11.setCellValue(tested_children); cellx12.setCellValue(disclosed_status); // cellx13.setCellValue(pepfaryear); cellx13.setCellValue(agebracket); // cellx15.setCellValue(); cellx1.setCellStyle(stylex); cellx2.setCellStyle(stylex); cellx3.setCellStyle(stylex); cellx4.setCellStyle(stylex); cellx5.setCellStyle(stylex); cellx6.setCellStyle(stylex); cellx7.setCellStyle(stylex); cellx8.setCellStyle(stylex); cellx9.setCellStyle(stylex); cellx10.setCellStyle(stylex); cellx11.setCellStyle(stylex); cellx12.setCellStyle(stylex); cellx13.setCellStyle(stylex); // cellx14.setCellStyle(stylex); // cellx15.setCellStyle(stylex); pos++; } } if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } if (incrementor > 0) { // write it as an excel attachment if (session.getAttribute("period").toString().equals("10-12")) { pepfaryear++; } 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=PWP_SERVICES_PROVIDED_REPORT_FOR_PEPFAR_YEAR_" + pepfaryear + "(" + period + ").xlsm"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); pkg.close(); } else { session.setAttribute("kePMSError", "<font color=\"red\"><b>NO DATA WITHIN THE SELECTED PARAMETERS.</b></font>"); response.sendRedirect("kePMS.jsp"); } }
From source file:reports.moh731_Facility.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { session = request.getSession();//from w w w. ja v a2 s. c om dbConn conn = new dbConn(); pmtcthv = 0; htchv = 0; arthv = 0; year = request.getParameter("year"); month = request.getParameter("month"); //-------------------------------------------------------------------------------- //-------------------------------------------------------------------------------- //added later to accomodate the years String subpartnerid = "SubPartnerID"; int monthint = 0; int yearint = 0; monthint = Integer.parseInt(month); yearint = Integer.parseInt(year); String subpartnera = "subpartnera"; if (yearint == 2015) { if (monthint == 10 || monthint == 11 || monthint == 12 || monthint == 1 || monthint == 2 || monthint == 3) { //here use a different subpartner id subpartnerid = "SP_ID"; subpartnera = "subpartnera2014"; } else { subpartnerid = "SubPartnerID"; subpartnera = "subpartnera"; } } else if (yearint <= 2014) { subpartnerid = "SP_ID"; subpartnera = "subpartnera2014"; } else if (yearint > 2015) { subpartnerid = "SubPartnerID"; subpartnera = "subpartnera"; } //--------------------------------------------------------------------------------------- //--------------------------------------------------------------------------------------- // year="2015"; // month="5"; counter = 0; monthName = ""; // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet shet4 = wb.createSheet("HTC"); XSSFSheet shet1 = wb.createSheet("PMTCT"); XSSFSheet shet2 = wb.createSheet("Care and Treatment"); XSSFSheet shet3 = wb.createSheet("PEP"); XSSFFont 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); XSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); XSSFCellStyle 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_LEFT); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_LEFT); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); for (int i = 0; i <= 3; i++) { shet1.setColumnWidth(i, 6000); } for (int i = 4; i <= 47; i++) { shet1.setColumnWidth(i, 4000); } for (int i = 0; i <= 3; i++) { shet2.setColumnWidth(i, 6000); } for (int i = 4; i <= 66; i++) { shet2.setColumnWidth(i, 3000); } for (int i = 0; i <= 3; i++) { shet3.setColumnWidth(i, 6000); } for (int i = 4; i <= 19; i++) { shet3.setColumnWidth(i, 3000); } for (int i = 0; i <= 3; i++) { shet4.setColumnWidth(i, 6000); } for (int i = 4; i <= 14; i++) { shet4.setColumnWidth(i, 3000); } headers = "COUNTY,SUB COUNTY,FACILITY NAME,MFL CODE,ART HIGH VOLUME,HTC HIGH VOLUME,PMTCT HIGH VOLUME"; String arrayHeader[] = headers.split(","); int headerno = 0; // XSSFRow rw0S1=shet1.createRow(0); XSSFRow rw1S1 = shet1.createRow(0); // XSSFRow rw0S2=shet2.createRow(0); XSSFRow rw1S2 = shet2.createRow(0); // XSSFRow rw0S3=shet3.createRow(0); XSSFRow rw1S3 = shet3.createRow(0); // XSSFRow rw0S3=shet3.createRow(0); XSSFRow rw1S4 = shet4.createRow(0); // LOOP THROUGH AND WRITE STATIC HEADERS TO THE ELEMENTS for (String headername : arrayHeader) { XSSFCell S1cell = rw1S1.createCell(headerno); S1cell.setCellValue(headername); S1cell.setCellStyle(stylex); XSSFCell S2cell = rw1S2.createCell(headerno); S2cell.setCellValue(headername); S2cell.setCellStyle(stylex); XSSFCell S3cell = rw1S3.createCell(headerno); S3cell.setCellValue(headername); S3cell.setCellStyle(stylex); XSSFCell S4cell = rw1S4.createCell(headerno); S4cell.setCellValue(headername); S4cell.setCellStyle(stylex); headerno++; } String getMonth = "SELECT name FROM month WHERE id='" + month + "'"; conn.rs = conn.st.executeQuery(getMonth); if (conn.rs.next() == true) { monthName = conn.rs.getString(1); } counterPMTCT = counterART = counterPEP = counterHTC = 6; //get the specifc indicator names and the respective section and a count of indicators in each section which dictates the number of columns in the whole excel String getVariables = "SELECT * FROM moh731_indicators ORDER BY id"; conn.rs1 = conn.st1.executeQuery(getVariables); while (conn.rs1.next()) { elementName = conn.rs1.getString("indicator") + " \n" + conn.rs1.getString("code"); if (conn.rs1.getString("section").equals("1. HIV Counselling and Testing")) { counterHTC++; XSSFCell S4cell = rw1S4.createCell(counterHTC); S4cell.setCellValue(elementName); S4cell.setCellStyle(stylex); } else if (conn.rs1.getString("section").equals("2. Prevention of Mother-to-Child Transmission")) { counterPMTCT++; XSSFCell S1cell = rw1S1.createCell(counterPMTCT); S1cell.setCellValue(elementName); S1cell.setCellStyle(stylex); } else if (conn.rs1.getString("section").equals("3. Care and Treatment")) { counterART++; XSSFCell S2cell = rw1S2.createCell(counterART); S2cell.setCellValue(elementName); S2cell.setCellStyle(stylex); } else if (conn.rs1.getString("section").equals("5. Post-Exposure Prophylaxis")) { counterPEP++; XSSFCell S3cell = rw1S3.createCell(counterPEP); S3cell.setCellValue(elementName); S3cell.setCellStyle(stylex); } else { } } XSSFCell S3cella = rw1S1.createCell(49); S3cella.setCellValue("Validation Run"); S3cella.setCellStyle(stylex); XSSFCell S3cell2a = rw1S2.createCell(68); S3cell2a.setCellValue("Validation Run"); S3cell2a.setCellStyle(stylex); XSSFCell S3cell3a = rw1S3.createCell(20); S3cell3a.setCellValue("Validation Run"); S3cell3a.setCellStyle(stylex); XSSFCell S4cell3a = rw1S4.createCell(22); S4cell3a.setCellValue("Validation Run"); S4cell3a.setCellStyle(stylex); // counter=1; System.out.println("pmtct : " + counterPMTCT + " art : " + counterART + " PEP : " + counterPEP + " HTC : " + counterHTC); // counterPMTCT=counterPMTCT-5; // counterART=counterART-5; // counterPEP=counterPEP-5; counterPMTCT1 = counterART1 = counterPEP1 = counterHTC1 = 0; String getData = "SELECT county.County,district.DistrictNom," + subpartnera + ".SubPartnerNom," + subpartnera + ".CentreSanteId," + " moh731.HV0201,moh731.HV0202,moh731.HV0203,moh731.HV0204,moh731.HV0205,moh731.HV0206,moh731.HV0207,moh731.HV0208,moh731.HV0209,moh731.HV0210,moh731.HV0211,moh731.HV0212,moh731.HV0213," + "moh731.HV0214,moh731.HV0215,moh731.HV0216,moh731.HV0217,moh731.HV0218,moh731.HV0219,moh731.HV0220,moh731.HV0221,moh731.HV0224,moh731.HV0225,moh731.HV0226,moh731.HV0227,moh731.HV0228,moh731.HV0229," + " moh731.HV0230,moh731.HV0231,moh731.HV0232,moh731.HV0233,moh731.HV0234,moh731.HV0235,moh731.HV0236,moh731.HV0237,moh731.HV0238,moh731.HV0239,moh731.HV0240,moh731.HV0241,moh731.HV0242," + " moh731.HV0243,moh731.HV0244,moh731.HV0301,moh731.HV0302,moh731.HV0303,moh731.HV0304,moh731.HV0305,moh731.HV0306,moh731.HV0307,moh731.HV0308,moh731.HV0309,moh731.HV0310,moh731.HV0311,moh731.HV0312,moh731.HV0313,moh731.HV0314," + " moh731.HV0315,moh731.HV0316,moh731.HV0317,moh731.HV0318,moh731.HV0319,moh731.HV0320,moh731.HV0321,moh731.HV0322,moh731.HV0323,moh731.HV0324,moh731.HV0325,moh731.HV0326,moh731.HV0327,moh731.HV0328," + " moh731.HV0329,moh731.HV0330,moh731.HV0331,moh731.HV0332,moh731.HV0333,moh731.HV0334,moh731.HV0335,moh731.HV0336,moh731.HV0337,moh731.HV0338,moh731.HV0339,moh731.HV0340,moh731.HV0341," + " moh731.HV0342,moh731.HV0343,moh731.HV0344,moh731.HV0345,moh731.HV0346,moh731.HV0347,moh731.HV0348,moh731.HV0349,moh731.HV0350,moh731.HV0351,moh731.HV0352,moh731.HV0353," + " moh731.HV0354,moh731.HV0355,moh731.HV0904,moh731.HV0905,moh731.HV0370,moh731.HV0371,moh731.HV0372,moh731.HV0373," + " moh731.HV0501,moh731.HV0502,moh731.HV0503,moh731.HV0504,moh731.HV0505,moh731.HV0506,moh731.HV0507,moh731.HV0508,moh731.HV0509,moh731.HV0510,moh731.HV0511,moh731.HV0512,moh731.HV0513,moh731.HV0514," + " moh731.HV0101,moh731.HV0102,moh731.HV0103,moh731.HV0105,moh731.HV0106,moh731.HV0107,moh731.HV0108,moh731.HV0109,moh731.HV0110,moh731.HV0111,moh731.HV0112,moh731.HV0113,moh731.HV0114,moh731.HV0115,moh731.HV0116 " + "," + subpartnera + ".PMTCT," + subpartnera + ".ART," + subpartnera + ".PEP," + subpartnera + ".HTC,isValidated ,IFNULL(ART_highvolume,0) as ART_highvolume, IFNULL(HTC_highvolume,0) as HTC_highvolume, IFNULL(PMTCT_highvolume,0) as PMTCT_highvolume " + " FROM moh731 JOIN " + subpartnera + " ON moh731.SubPartnerID=" + subpartnera + "." + subpartnerid + " " + "JOIN district ON " + subpartnera + ".DistrictID=district.DistrictID " + "JOIN county ON county.CountyID=district.CountyID " + " WHERE moh731.Mois='" + month + "' && moh731.Annee='" + year + "'" + " ORDER BY county.County,district.DistrictNom," + subpartnera + "." + subpartnerid + ""; conn.rs = conn.st.executeQuery(getData); int valuesstartrow = 7; System.out.println("|__" + getData); while (conn.rs.next()) { counter++; county = conn.rs.getString(1); district = conn.rs.getString(2); facilityname = conn.rs.getString(3); mflcode = conn.rs.getString(4); arthv = conn.rs.getInt("ART_highvolume"); htchv = conn.rs.getInt("HTC_highvolume"); pmtcthv = conn.rs.getInt("PMTCT_highvolume"); System.out.println("@@@@@@" + arthv + "_" + htchv + "_" + pmtcthv); //ART High Volume HTC High Volume PMTCT High Volume String basicDetails = county + "@" + district + "@" + facilityname + "@" + mflcode + "@" + arthv + "@" + htchv + "@" + pmtcthv; String arrayDetails[] = basicDetails.split("@"); if (conn.rs.getInt(subpartnera + ".PMTCT") == 1) { counterPMTCT1++; XSSFRow rw2S1 = shet1.createRow(counterPMTCT1); int facilno = 0; for (int b = 0; b < arrayDetails.length; b++) { XSSFCell S3cell = rw2S1.createCell(facilno); if (b < 3) { S3cell.setCellValue(arrayDetails[b]); } else { S3cell.setCellValue(Integer.parseInt(arrayDetails[b])); } S3cell.setCellStyle(stborder); facilno++; } int pos; for (int i = valuesstartrow; i <= counterPMTCT; i++) { XSSFCell S3cell = rw2S1.createCell(i); pos = i + 1; //System.out.println("cell no 1 : "+i+" value no : "+pos); S3cell.setCellValue(conn.rs.getInt(pos - 3)); S3cell.setCellStyle(stborder); } isValidated = conn.rs.getString("isValidated"); if (isValidated.equals("1")) { isValidated = "Yes"; } else { isValidated = "No"; } XSSFCell S3cell = rw2S1.createCell(49); S3cell.setCellValue(isValidated); S3cell.setCellStyle(stborder); } if (conn.rs.getInt(subpartnera + ".ART") == 1) { counterART1++; XSSFRow rw2S2 = shet2.createRow(counterART1); int facilno = 0; for (int b = 0; b < arrayDetails.length; b++) { XSSFCell S3cell = rw2S2.createCell(facilno); if (b < 3) { S3cell.setCellValue(arrayDetails[b]); } else { S3cell.setCellValue(Integer.parseInt(arrayDetails[b])); } S3cell.setCellStyle(stborder); facilno++; } int pos; for (int i = valuesstartrow; i <= counterART; i++) { XSSFCell S3cell = rw2S2.createCell(i); pos = i + 43; System.out.println("cell no 2 : " + i + " value no : " + pos); S3cell.setCellValue(conn.rs.getInt(pos - 3)); S3cell.setCellStyle(stborder); } isValidated = conn.rs.getString("isValidated"); if (isValidated.equals("1")) { isValidated = "Yes"; } else { isValidated = "No"; } XSSFCell S3cell = rw2S2.createCell(68); S3cell.setCellValue(isValidated); S3cell.setCellStyle(stborder); } if (conn.rs.getInt(subpartnera + ".PEP") == 1) { counterPEP1++; XSSFRow rw2S3 = shet3.createRow(counterPEP1); int facilno = 0; for (int b = 0; b < arrayDetails.length; b++) { XSSFCell S3cell = rw2S3.createCell(facilno); if (b < 3) { S3cell.setCellValue(arrayDetails[b]); } else { S3cell.setCellValue(Integer.parseInt(arrayDetails[b])); } S3cell.setCellStyle(stborder); facilno++; } int pos; for (int i = valuesstartrow; i <= counterPEP; i++) { XSSFCell S3cell = rw2S3.createCell(i); pos = i + 104; //System.out.println("cell no 3 : "+i+" value no : "+pos); S3cell.setCellValue(conn.rs.getInt(pos - 3)); S3cell.setCellStyle(stborder); } isValidated = conn.rs.getString("isValidated"); if (isValidated.equals("1")) { isValidated = "Yes"; } else { isValidated = "No"; } XSSFCell S3cell = rw2S3.createCell(20); S3cell.setCellValue(isValidated); S3cell.setCellStyle(stborder); } //HTC_____________________added 2016 if (conn.rs.getInt(subpartnera + ".HTC") == 1) { counterHTC1++; XSSFRow rw2S4 = shet4.createRow(counterHTC1); int facilno = 0; for (int b = 0; b < arrayDetails.length; b++) { XSSFCell S3cell = rw2S4.createCell(facilno); if (b < 3) { S3cell.setCellValue(arrayDetails[b]); } else { S3cell.setCellValue(Integer.parseInt(arrayDetails[b])); } S3cell.setCellStyle(stborder); facilno++; } String pos; int cellpos = 0; for (int i = 1; i <= 16; i++) { if (i != 4) { cellpos++; XSSFCell S4cell = rw2S4.createCell(cellpos + 6); pos = "0" + i; if (i >= 10) { pos = "" + i; } S4cell.setCellValue(conn.rs.getInt("moh731.HV01" + pos)); S4cell.setCellStyle(stborder); } } isValidated = conn.rs.getString("isValidated"); if (isValidated.equals("1")) { isValidated = "Yes"; } else { isValidated = "No"; } XSSFCell S4cell = rw2S4.createCell(22); S4cell.setCellValue(isValidated); S4cell.setCellStyle(stborder); } System.out.println("counter : " + counter); } if (conn.st != null) { conn.st.close(); } if (conn.st1 != null) { conn.st1.close(); } // if(conn.st2!=null){conn.st2.close();} if (conn.rs != null) { conn.rs.close(); } if (conn.rs1 != null) { conn.rs1.close(); } // if(conn.rs2!=null){conn.rs2.close();} if (conn.conn != null) { conn.conn.close(); } IdGenerator IG = new IdGenerator(); createdOn = IG.CreatedOn(); // 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=MOH731_RAW_DATA_REPORT_FOR_" + year.trim() + "(" + monthName.trim() + ")_CREATED_" + createdOn.trim() + ".xlsx"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); }
From source file:reports.moh731_subSection.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {//w w w . ja v a 2s. co m response.setContentType("text/html;charset=UTF-8"); //a page to get Report of all the servlets String year = "2015"; String month = "4"; String county = "1"; String form = "moh731"; if (request.getParameter("year") != null) { year = request.getParameter("year"); } if (request.getParameter("county") != null) { county = request.getParameter("county"); } if (request.getParameter("month") != null) { month = request.getParameter("month"); } if (request.getParameter("form") != null) { form = request.getParameter("form"); } String facilitywhere = ""; String countywhere = ""; String districtwhere = ""; String reporttype = ""; dbConn conn = new dbConn(); //an array to store haeder information. //the header information should appear only if a certain parameters are met //The parameters listed in here can be removed if the report type doesnt require certain parameters ArrayList Headerorgunits = new ArrayList(); Headerorgunits.add("COUNTY"); Headerorgunits.add("SUB-COUNTY"); Headerorgunits.add("FACILITY"); Headerorgunits.add("MFL CODE"); //An arralist to store a list of columns that will be selected from the database ArrayList dbcolumns = new ArrayList(); ArrayList labels = new ArrayList(); ArrayList tablename = new ArrayList(); ArrayList iscumulative = new ArrayList(); ArrayList ispercent = new ArrayList(); // ArrayList isactive=new ArrayList(); //An arralist to store a list of worksheets that will be selected from the sections ArrayList worksheets = new ArrayList(); //An arralist to store distinct worksheets. This will be derived from the the sections column ArrayList distinctsheets = new ArrayList(); String selectdistinctworksheet = "select distinct(section) from pivottable where form='" + form + "' and active='1'"; conn.rs = conn.st.executeQuery(selectdistinctworksheet); while (conn.rs.next()) { //add the name of distinct sections distinctsheets.add(conn.rs.getString(1).replace("/", "_")); } String getattribs = "select indicator,label,section,cumulative,percentage,active ,shortlabel from pivottable where form='" + form + "' order by tableid, section"; conn.rs = conn.st.executeQuery(getattribs); while (conn.rs.next()) { //add active indicators only if (conn.rs.getString("active").equals("1")) { System.out.println(conn.rs.getString("indicator") + ""); //add indicator dbcolumns.add(conn.rs.getString("indicator")); //add label if (form.equals("moh731")) { labels.add(conn.rs.getString("shortlabel") + " \n " + conn.rs.getString("label")); } else { labels.add(conn.rs.getString("label")); } //add worksheets worksheets.add(conn.rs.getString("section").replace("/", "_")); String perc = "0"; String cum = "0"; if (conn.rs.getString("cumulative") != null) { iscumulative.add(conn.rs.getString("cumulative")); } else { iscumulative.add(cum); } if (conn.rs.getString("percentage") != null) { ispercent.add(conn.rs.getString("percentage")); } else { ispercent.add(perc); } } //end of active } //end of worksheet //if String perfacilselect = "select Upper(County) as County , Upper(DistrictNom) as District , UPPER(SubPartnerNom) as facility ,CentreSanteId as mflcode , "; //-------------------------------------------------------------------------------------------- // PREPARE SELECT //-------------------------------------------------------------------------------------------- //prepare selects for (int a = 0; a < dbcolumns.size(); a++) { //if the indicator is a percent, get an avaerage if (ispercent.get(a).equals("1")) { perfacilselect += " AVG(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } else if (iscumulative.get(a).equals("1")) { perfacilselect += " " + dbcolumns.get(a) + " as " + dbcolumns.get(a); } else { perfacilselect += " SUM(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } //if the item is not the last, append a comma if (a < dbcolumns.size() - 1) { perfacilselect += " ,"; } } //------------------------------------------------------------------------------------ // FROM //------------------------------------------------------------------------------------ perfacilselect += " from " + form + " join ( subpartnera join (district join county on county.CountyID=district.CountyID ) on district.DistrictID = subpartnera.DistrictID ) on " + form + ".SubPartnerID = subpartnera.SubPartnerID "; //------------------------------------------------------------------------------------------ // WHERE //------------------------------------------------------------------------------------------ //----------------------------------------------------------------------------------------- //GROUP BY //---------------------------------------------------------------------------------------- perfacilselect += " group by subpartnera.SubPartnerID"; System.out.println(perfacilselect); //______________________________________________________________________________________ // NOW CREATE THE WORKSHEETS //______________________________________________________________________________________ XSSFWorkbook wb = new XSSFWorkbook(); //______________________________________________________________________________________ //______________________________________________________________________________________ XSSFFont 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); XSSFFont 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); XSSFCellStyle 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); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_LEFT); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); stylex.setFont(fontx); stylex.setWrapText(true); for (int b = 0; b < distinctsheets.size(); b++) { XSSFSheet shet = wb.createSheet(distinctsheets.get(b).toString().toUpperCase()); //create headers for that worksheet XSSFRow rw = shet.createRow(1); int headercellpos = 0; //create the orgunit header eg COUNTY | SUBCOUNTY | FACILITY for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(Headerorgunits.get(e).toString()); cell0.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(e, 6000); } //create the indicators header eg HV0101 | HIV 09676 | TOTAL for (int c = 0; c < dbcolumns.size(); c++) { //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(b))) { shet.setColumnWidth(headercellpos, 6000); XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(labels.get(c).toString()); cell0.setCellStyle(stylex); headercellpos++; } //end of comparing if } //end of for loop } conn.rs = conn.st.executeQuery(perfacilselect); String sectioncopy = ""; int sheetpos = 0; int rowpos = 2; while (conn.rs.next()) { //-----------------INSIDE THE DATA FORM--------------------------------- //if the section changes, change the position of the worksheet too //also, reset the position counter to begin from 2 again. XSSFSheet shet = null; // if(--!sectioncopy.equals(shet)){} for (int g = 0; g < distinctsheets.size(); g++) { shet = wb.getSheetAt(g); int colpos = 0; //the fourth cell should XSSFRow rw = shet.createRow(rowpos); for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(conn.rs.getString(e + 1)); cell0.setCellStyle(style2); colpos++; } //_________________________________________________________________ //VALUES //_________________________________________________________________ //create the indicators values eg 90 | 45 | 356 for (int c = 0; c < dbcolumns.size(); c++) { //get the section of the current dbcolumn //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(g))) { XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(conn.rs.getString(dbcolumns.get(c).toString())); cell0.setCellStyle(stborder); colpos++; } //end of comparing if } //end of for loop } rowpos++; } 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.toUpperCase() + "_REPORT.xlsx"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } catch (SQLException ex) { Logger.getLogger(allStaticReportsMonthly.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:reports.new711report.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {/*from ww w. j av a 2 s.c om*/ response.setContentType("text/html;charset=UTF-8"); //a page to get Report of all the servlets String year = "2016"; String month = "4"; String county = ""; String form = "moh711_new"; if (request.getParameter("year") != null) { year = request.getParameter("year"); } if (request.getParameter("county") != null) { county = request.getParameter("county"); } if (request.getParameter("month") != null) { month = request.getParameter("month"); } if (request.getParameter("form") != null) { form = request.getParameter("form"); } String pivotform = form; if (form.equalsIgnoreCase("MOH 731")) { form = "MOH731"; } if (form.equalsIgnoreCase("MOH 711 (New)")) { form = "moh711_new"; } String facilitywhere = ""; String yearwhere = ""; String monthwhere = ""; String countywhere = ""; String districtwhere = ""; String reporttype = ""; if (!year.equals("")) { yearwhere = " and Annee = '" + year + "'"; } if (!county.equals("")) { countywhere = " and countyid = '" + county + "'"; } if (!month.equals("")) { monthwhere = " and Mois = '" + month + "'"; } dbConn conn = new dbConn(); //an array to store haeder information. //the header information should appear only if a certain parameters are met //The parameters listed in here can be removed if the report type doesnt require certain parameters ArrayList Headerorgunits = new ArrayList(); Headerorgunits.add("COUNTY"); Headerorgunits.add("SUB-COUNTY"); Headerorgunits.add("FACILITY"); Headerorgunits.add("MFL CODE"); //An arralist to store a list of columns that will be selected from the database ArrayList dbcolumns = new ArrayList(); ArrayList labels = new ArrayList(); ArrayList tablename = new ArrayList(); ArrayList iscumulative = new ArrayList(); ArrayList ispercent = new ArrayList(); // ArrayList isactive=new ArrayList(); //An arralist to store a list of worksheets that will be selected from the sections and the respective service area to determine the facilities whose data will appear in that sheet ArrayList worksheets = new ArrayList(); //An arralist to store distinct worksheets. This will be derived from the the sections column ArrayList distinctsheets = new ArrayList(); ArrayList distinctservicearea = new ArrayList(); String selectdistinctworksheet = "select section,servicearea from pivottable where form='" + form.replace("_", "") + "' and active='1' group by section order by order_per_form"; conn.rs = conn.st.executeQuery(selectdistinctworksheet); while (conn.rs.next()) { //add the name of distinct sections distinctsheets.add(conn.rs.getString(1).replace("/", "_")); String servicearea = " 2=2 "; if (conn.rs.getString(2) != null) { servicearea = " " + conn.rs.getString(2) + "=1"; } distinctservicearea.add(servicearea); } String getattribs = "select indicator,label,section,cumulative,percentage,active ,shortlabel from pivottable where form='" + form.replace("_", "") + "' order by order_per_form, section"; conn.rs = conn.st.executeQuery(getattribs); while (conn.rs.next()) { //add active indicators only if (conn.rs.getString("active").equals("1")) { System.out.println(conn.rs.getString("indicator") + ""); //add indicator dbcolumns.add(conn.rs.getString("indicator")); //add label if (form.equals("moh731")) { labels.add(conn.rs.getString("shortlabel") + " \n " + conn.rs.getString("label")); } else { labels.add(conn.rs.getString("label")); } //add worksheets worksheets.add(conn.rs.getString("section").replace("/", "_")); String perc = "0"; String cum = "0"; if (conn.rs.getString("cumulative") != null) { iscumulative.add(conn.rs.getString("cumulative")); } else { iscumulative.add(cum); } if (conn.rs.getString("percentage") != null) { ispercent.add(conn.rs.getString("percentage")); } else { ispercent.add(perc); } } //end of active } //end of pivot table active //if String perfacilselect = "select Upper(County) as County , Upper(DistrictNom) as District , UPPER(SubPartnerNom) as facility ,CentreSanteId as mflcode , district.CountyID as countyid , "; //-------------------------------------------------------------------------------------------- // PREPARE SELECT //-------------------------------------------------------------------------------------------- //prepare selects for (int a = 0; a < dbcolumns.size(); a++) { //if the indicator is a percent, get an avaerage if (ispercent.get(a).equals("1")) { perfacilselect += " AVG(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } else if (iscumulative.get(a).equals("1")) { perfacilselect += " " + dbcolumns.get(a) + " as " + dbcolumns.get(a); } else { perfacilselect += " SUM(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } //if the item is not the last, append a comma //if(a<dbcolumns.size()-1){ perfacilselect += " ,"; // } } //------------------------------------------------------------------------------------ // FROM //------------------------------------------------------------------------------------ perfacilselect += " isValidated as Form_Validated from " + form + " join ( subpartnera join (district join county on county.CountyID=district.CountyID ) on district.DistrictID = subpartnera.DistrictID ) on " + form + ".SubPartnerID = subpartnera.SubPartnerID "; //------------------------------------------------------------------------------------------ // WHERE //------------------------------------------------------------------------------------------ perfacilselect += " where 1=1 " + monthwhere + yearwhere; //----------------------------------------------------------------------------------------- //GROUP BY //---------------------------------------------------------------------------------------- perfacilselect += " group by subpartnera.SubPartnerID"; //System.out.println(perfacilselect); //______________________________________________________________________________________ // NOW CREATE THE WORKSHEETS //______________________________________________________________________________________ XSSFWorkbook wb = new XSSFWorkbook(); //______________________________________________________________________________________ //______________________________________________________________________________________ XSSFFont 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); XSSFFont 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); XSSFCellStyle 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); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_LEFT); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); stylex.setFont(fontx); stylex.setWrapText(true); for (int b = 0; b < distinctsheets.size(); b++) { XSSFSheet shet = wb.createSheet(distinctsheets.get(b).toString().toUpperCase()); //create headers for that worksheet XSSFRow rw = shet.createRow(1); int headercellpos = 0; //create the orgunit header eg COUNTY | SUBCOUNTY | FACILITY for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(Headerorgunits.get(e).toString()); cell0.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(e, 6000); } //create the indicators header eg HV0101 | HIV 09676 | TOTAL for (int c = 0; c < dbcolumns.size(); c++) { //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(b))) { shet.setColumnWidth(headercellpos, 6000); XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(labels.get(c).toString()); cell0.setCellStyle(stylex); headercellpos++; } //end of comparing if } //end of for loop //create is validated header shet.setColumnWidth(headercellpos, 6000); XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue("Form Validated ?"); cell0.setCellStyle(stylex); headercellpos++; } String sectioncopy = ""; int sheetpos = 0; int rowpos = 2; //-----------------INSIDE THE DATA FORM--------------------------------- //if the section changes, change the position of the worksheet too //also, reset the position counter to begin from 2 again. XSSFSheet shet = null; // if(--!sectioncopy.equals(shet)){} //create the org unit data values e.g BARINGO | BARINGO CENTRAL |KABARNET DISTRICT HOSPITAL | MFL CODE for (int g = 0; g < distinctsheets.size(); g++) { shet = wb.getSheetAt(g); int colpos = 0; String finalquery = perfacilselect.replace("1=1", distinctservicearea.get(g).toString()); System.out.println("" + finalquery); conn.rs = conn.st.executeQuery(finalquery); while (conn.rs.next()) { //the fourth cell should XSSFRow rw = shet.createRow(rowpos); for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(colpos); //for mfl code, last header, print integers if (Headerorgunits.get(e).toString().equals("MFL CODE")) { cell0.setCellValue(conn.rs.getInt(e + 1)); } else { cell0.setCellValue(conn.rs.getString(e + 1)); } cell0.setCellStyle(style2); colpos++; } //_________________________________________________________________ //VALUES //_________________________________________________________________ //create the indicators values eg 90 | 45 | 356 for (int c = 0; c < dbcolumns.size(); c++) { //get the section of the current dbcolumn //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(g))) { XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(conn.rs.getInt(dbcolumns.get(c).toString())); cell0.setCellStyle(stborder); colpos++; } //end of comparing if } //end of for loop String isvalidated = "Yes"; if (conn.rs.getString("Form_Validated").equals("0")) { isvalidated = "No"; } XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(isvalidated); cell0.setCellStyle(stborder); colpos++; rowpos++; colpos = 0; } rowpos = 2; } IdGenerator IG = new IdGenerator(); String createdOn = IG.CreatedOn(); System.out.println("" + form.toUpperCase().trim() + "_REPORT_FOR_" + year.trim() + "(" + month.trim() + ")_CREATED_" + createdOn.trim() + ".xlsx"); 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.toUpperCase().trim() + "_REPORT_FOR_" + year.trim() + "(" + month.trim() + ")_CREATED_" + createdOn.trim() + ".xlsx"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } catch (SQLException ex) { Logger.getLogger(allStaticReportsMonthly.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:reports.Reached_OthersMessages.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException, InvalidFormatException { session = request.getSession();//from w w w. j a v a 2 s. co m dbConn conn = new dbConn(); ouputyear = outputMonth = ""; if (session.getAttribute("PepfarYear") != null) { year = Integer.parseInt(session.getAttribute("PepfarYear").toString()); quarter = Integer.parseInt(session.getAttribute("period").toString()); // year=2015; // quarter=2; System.out.println("dates are : " + year + " quarter : " + quarter); if (quarter == 4) { reportYear = year - 1; startmonth = "10"; endMonth = "12"; prevReportYear = reportYear; prevReportMonth = "09"; } else { reportYear = year; if (quarter == 1) { startmonth = "01"; endMonth = "03"; prevReportYear = reportYear - 1; prevReportMonth = "12"; } if (quarter == 2) { startmonth = "04"; endMonth = "06"; prevReportYear = reportYear; prevReportMonth = "03"; } if (quarter == 3) { startmonth = "07"; endMonth = "09"; prevReportYear = reportYear; prevReportMonth = "06"; } } startDate = "" + startmonth + "/01/" + reportYear + ""; endDate = "" + endMonth + "/31/" + reportYear + ""; startDate1 = "" + reportYear + "-" + startmonth + "-01"; endDate1 = "" + reportYear + "-" + endMonth + "-31"; endYearMonth = prevReportYear + "" + prevReportMonth; position = 1; // ouputyear=""+reportYear; if (quarter == 1) { outputMonth = "Jan_March_" + reportYear; } if (quarter == 2) { outputMonth = "Apr_Jun_" + reportYear; } if (quarter == 3) { outputMonth = "July_Sept_" + reportYear; } if (quarter == 4) { outputMonth = "Oct_Dec_" + reportYear; } String reportHeader[] = ("COUNTY NAME ,PARTNER NAME,DISTRICT NAME,DIC NAME, GROUP NAME,CLIENT NAME , AGE BRACKET, GENDER,YEAR,MONTH") .split(","); // COPY FILE TO BE WRITTEN TO Path original = Paths.get(getServletContext().getRealPath("/OTHER_MESSAGES.xlsm")); //original file Path destination = Paths.get(getServletContext().getRealPath("/OTHER_MESSAGES_1.xlsm")); //new file System.out.println("origin : " + original + " destination : " + destination); try { Files.copy(original, destination, StandardCopyOption.REPLACE_EXISTING); System.out.println("file copied----------------"); } catch (IOException x) { //catch all for IO problems System.out.println("fine not copied"); } String allpath = getServletContext().getRealPath("/OTHER_MESSAGES_1.xlsm"); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFWorkbook wb; OPCPackage pkg = OPCPackage.open(allpath); wb = new XSSFWorkbook(pkg); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ // HSSFWorkbook wb=new HSSFWorkbook(); XSSFSheet shet1 = wb.getSheet("Sheet1"); XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); XSSFCellStyle 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); for (int i = 0; i <= reportHeader.length; i++) { shet1.setColumnWidth(i, 4000); } XSSFCellStyle styleBorder = wb.createCellStyle(); styleBorder.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.LIME.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); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); XSSFCell cell; XSSFRow rw0 = shet1.createRow(0); rw0.setHeightInPoints(30); rw0.setRowStyle(style2); for (int i = 0; i <= (reportHeader.length - 1); i++) { cell = rw0.createCell(i); cell.setCellValue(reportHeader[i]); cell.setCellStyle(stylex); } String query = "SELECT CLIENT, " + "" + "if( (dateRegister BETWEEN '" + startDate1 + "' AND '" + endDate1 + "'),dateRegister,dateAdherence) REPORTDATE," + " " + "dateRegister,dateAdherence, countyName, partnerName, districtName, AGEBRACKET, SEX,fname,mname,lname,GROUPNAME,DIC FROM (" + "SELECT DISTINCT(tempData.clientID) as CLIENT,if( (reg2Date BETWEEN '" + startDate1 + "' AND '" + endDate1 + "'),reg2Date,'0') dateRegister,if( (AdherenceDate BETWEEN '" + startDate1 + "' AND '" + endDate1 + "'),AdherenceDate,'0') dateAdherence, countyName, partnerName, districtName, AGEBRACKET, SEX,fname,mname,lname,GROUPNAME,DIC FROM (" + "SELECT DISTINCT(personal_information.client_id) as clientID,STR_TO_DATE(register2.date,'%m/%d/%Y') as reg2Date,STR_TO_DATE(adherence.date_of_session,'%m/%d/%Y') as AdherenceDate," + " CONCAT(personal_information.completionyear,personal_information.completionmonth) AS YEARMONTH," + "county.county_name AS countyName,partner.partner_name AS partnerName,district.district_name AS districtName," + "CASE " + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 0 AND 9 THEN '0-9' " + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 10 AND 14 THEN '10-14' " + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 15 AND 19 THEN '15-19' " + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 20 AND 24 THEN '20-24' " + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 25 AND 49 THEN '25-49' " + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) >49 THEN '50 and above' " + " ELSE 'NO DATE OF BIRTH'" + " END AS AGEBRACKET," + " CASE " + "when personal_information.gender LIKE 'Female' THEN 'F' " + "when personal_information.gender LIKE 'Male' THEN 'M' " + "ELSE 'NO SEX' " + "END AS SEX,personal_information.fname as fname,personal_information.mname as mname ,personal_information.lname as lname," + "groups.group_name as GROUPNAME,dic.dic_name as DIC " + " FROM personal_information " + "LEFT JOIN register2 ON personal_information.client_id=register2.client_id " + "LEFT JOIN adherence ON personal_information.client_id=adherence.client_id " + "LEFT JOIN groups ON personal_information.group_id=groups.group_id " + "LEFT JOIN dic ON personal_information.dic_id=dic.dic_id " + "LEFT JOIN district ON personal_information.district_id=district.district_id " + "LEFT JOIN partner ON personal_information.partner_id=partner.partner_id " + "LEFT JOIN county ON district.county_id=county.county_id " + "WHERE personal_information.completionyear>0 " + "&& (register2.datekey BETWEEN '" + startDate1.replace("-", "") + "' AND '" + endDate1.replace("-", "") + "' || STR_TO_DATE(adherence.date_of_session,'%m/%d/%Y') BETWEEN STR_TO_DATE('" + startDate + "','%m/%d/%Y') AND STR_TO_DATE('" + endDate + "','%m/%d/%Y'))" + " HAVING YEARMONTH<=" + endYearMonth + ") AS tempData GROUP BY tempData.clientID) AS finalTable "; System.out.println(query); conn.rs = conn.st.executeQuery(query); while (conn.rs.next()) { countyName = conn.rs.getString(5); partnerName = conn.rs.getString(6); districtName = conn.rs.getString(7); reportDate = conn.rs.getString(2); ageBracket = conn.rs.getString(8); gender = conn.rs.getString(9); fname = conn.rs.getString(10); mname = conn.rs.getString(11); lname = conn.rs.getString(12); if (conn.rs.getString(13) != null) { groupName = conn.rs.getString(13); } else { groupName = "Individual"; } if (conn.rs.getString(14) != null) { groupName = conn.rs.getString(14); } else { dic_name = "NO DIC"; } if (mname.equals(lname)) { mname = ""; } fullName = fname + " " + mname + " " + lname; String dateSplit[] = reportDate.split("-"); if (dateSplit[1].equals("01")) { month = "Jan"; } if (dateSplit[1].equals("02")) { month = "Feb"; } if (dateSplit[1].equals("03")) { month = "Mar"; } if (dateSplit[1].equals("04")) { month = "Apr"; } if (dateSplit[1].equals("05")) { month = "May"; } if (dateSplit[1].equals("06")) { month = "Jun"; } if (dateSplit[1].equals("07")) { month = "Jul"; } if (dateSplit[1].equals("08")) { month = "Aug"; } if (dateSplit[1].equals("09")) { month = "Sept"; } if (dateSplit[1].equals("10")) { month = "Oct"; } if (dateSplit[1].equals("11")) { month = "Nov"; } if (dateSplit[1].equals("12")) { month = "Dec"; } year2 = dateSplit[0]; String data[] = (countyName + "," + partnerName + "," + districtName + "," + dic_name + "," + groupName + "," + fullName + "," + ageBracket + "," + gender + "," + year2 + "," + month) .split(","); XSSFRow rw1 = shet1.createRow(position); rw1.setHeightInPoints(25); rw1.setRowStyle(style2); for (int i = 0; i <= (data.length - 1); i++) { cell = rw1.createCell(i); cell.setCellValue(data[i]); cell.setCellStyle(styleBorder); } position++; } IdGenerator CRT = new IdGenerator(); 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=PWP_REACHED_WITH_OTHER_MESSAGES_REPORT_FOR_" + outputMonth + "_CREATED_ON_" + CRT.timestamp() + ".xlsm"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); pkg.close(); } else { session.setAttribute("kePMSError", "<font color=\"red\">Error : Please try again.</font>"); response.sendRedirect("kePMS.jsp"); } }
From source file:ru.jeene.zapretparser.controller.XLSXReportController.java
public void WriteReport(FullReport rep, String timestamp_csv) { try (FileInputStream inp = new FileInputStream(shab_name)) { XSSFWorkbook wb = new XSSFWorkbook(inp); // Declare XSSF WorkBook XSSFSheet sheet = wb.getSheet(" "); XSSFCellStyle cs1 = wb.createCellStyle(); //cs1.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); cs1.setBorderBottom(XSSFCellStyle.BORDER_THIN); cs1.setBorderTop(XSSFCellStyle.BORDER_THIN); cs1.setBorderRight(XSSFCellStyle.BORDER_THIN); cs1.setBorderLeft(XSSFCellStyle.BORDER_THIN); cs1.setAlignment(XSSFCellStyle.ALIGN_CENTER); cs1.setWrapText(true);/*www . ja v a 2 s . c om*/ cs1.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); XSSFFont f = wb.createFont(); f.setBold(false); f.setFontName("Times New Roman"); f.setFontHeightInPoints((short) 14); cs1.setFont(f); int cnt = 0; XSSFRow row; XSSFCell cell; for (Model_FullReport m : rep.getList()) { int cnt_cell = 0; row = sheet.getRow(t1_start - 1 + cnt); if (row == null) { row = sheet.createRow(t1_start - 1 + cnt); } //? URL cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(m.getElement().getUrl()); cell.setCellStyle(cs1); cnt_cell++; // cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(m.getElement().getOrg()); cell.setCellStyle(cs1); cnt_cell++; //? cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(m.getElement().getDoc()); cell.setCellStyle(cs1); cnt_cell++; // cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(m.getElement().getDate()); cell.setCellStyle(cs1); cnt_cell++; // cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(m.getResult().name()); cell.setCellStyle(cs1); cnt_cell++; // (?) cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(m.getResult().getDesc()); cell.setCellStyle(cs1); cnt_cell++; // () cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(m.getResult().getCode()); cell.setCellStyle(cs1); cnt_cell++; cnt++; } // row = sheet.getRow(0); cell = row.getCell(0); cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(MAIN_ZAG_TEMPL + timestamp_csv); // HashMap<ResponseResult, Model_NumberReport> map = rep.reportCountBytype(); // ? sheet = wb.getSheet(""); // cnt = 0; for (Map.Entry<ResponseResult, Model_NumberReport> entry : map.entrySet()) { ResponseResult key = entry.getKey(); Model_NumberReport value = entry.getValue(); int cnt_cell = 0; row = sheet.getRow(t0_start - 1 + cnt); if (row == null) { row = sheet.createRow(t0_start - 1 + cnt); } /*// cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(key.name()); cell.setCellStyle(cs1); cnt_cell++;*/ // (?) cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(key.getDesc()); cell.setCellStyle(cs1); cnt_cell++; /*// () cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(key.getCode()); cell.setCellStyle(cs1); cnt_cell++;*/ // (?) cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(value.getNumber()); cell.setCellStyle(cs1); cnt_cell++; // () cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(FormatUtils.FormatDoubleD(value.getPercent())); cell.setCellStyle(cs1); cnt_cell++; cnt++; } // row = sheet.getRow(0); cell = row.getCell(0); cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(MAIN_ZAG_TEMPL + timestamp_csv); // HTTP map = rep.reportCountBytypeHTTP(); // ? sheet = wb.getSheet(" HTTP"); // cnt = 0; for (Map.Entry<ResponseResult, Model_NumberReport> entry : map.entrySet()) { ResponseResult key = entry.getKey(); Model_NumberReport value = entry.getValue(); int cnt_cell = 0; row = sheet.getRow(t0_start - 1 + cnt); if (row == null) { row = sheet.createRow(t0_start - 1 + cnt); } /*// cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(key.name()); cell.setCellStyle(cs1); cnt_cell++;*/ // (?) cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(key.getDesc()); cell.setCellStyle(cs1); cnt_cell++; /*// () cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(key.getCode()); cell.setCellStyle(cs1); cnt_cell++;*/ // (?) cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(value.getNumber()); cell.setCellStyle(cs1); cnt_cell++; // () cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(FormatUtils.FormatDoubleD(value.getPercent())); cell.setCellStyle(cs1); cnt_cell++; cnt++; } // row = sheet.getRow(0); cell = row.getCell(0); cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(MAIN_ZAG_TEMPL + timestamp_csv); // HTTPS map = rep.reportCountBytypeHTTPS(); // ? sheet = wb.getSheet(" HTTPS"); // cnt = 0; for (Map.Entry<ResponseResult, Model_NumberReport> entry : map.entrySet()) { ResponseResult key = entry.getKey(); Model_NumberReport value = entry.getValue(); int cnt_cell = 0; row = sheet.getRow(t0_start - 1 + cnt); if (row == null) { row = sheet.createRow(t0_start - 1 + cnt); } /*// cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(key.name()); cell.setCellStyle(cs1); cnt_cell++;*/ // (?) cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(key.getDesc()); cell.setCellStyle(cs1); cnt_cell++; /*// () cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(key.getCode()); cell.setCellStyle(cs1); cnt_cell++;*/ // (?) cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(value.getNumber()); cell.setCellStyle(cs1); cnt_cell++; // () cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(FormatUtils.FormatDoubleD(value.getPercent())); cell.setCellStyle(cs1); cnt_cell++; cnt++; } // row = sheet.getRow(0); cell = row.getCell(0); cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(MAIN_ZAG_TEMPL + timestamp_csv); //? String tmp_out = StringUtils.replaceAll(report_name, "!dt!", DateUtils.DateToString(new Date(System.currentTimeMillis()), "ddMMyyyy_Hms")); try (FileOutputStream out = new FileOutputStream(tmp_out)) { wb.write(out); logger.info("Report file " + tmp_out + " created"); } } catch (Exception ex) { logger.error(ex); } }
From source file:se.inera.intyg.rehabstod.service.export.xlsx.XlsxExportServiceImpl.java
License:Open Source License
private XSSFFont buildFont(XSSFWorkbook wb, int heightInPoints, String fontName, boolean bold, boolean underline) { XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) heightInPoints); font.setFontName(fontName);/*from w w w . j a va2 s . c o m*/ font.setColor(IndexedColors.BLACK.getIndex()); font.setBold(bold); font.setBoldweight(bold ? Font.BOLDWEIGHT_BOLD : Font.BOLDWEIGHT_NORMAL); font.setUnderline(underline ? XSSFFont.U_SINGLE : XSSFFont.U_NONE); return font; }
From source file:se.minstrel.tools.xssfbuilder.style.impl.StyleBuilderImpl.java
License:Open Source License
private XSSFCellStyle buildStyle() { XSSFWorkbook wb = support.getWorkbook(); XSSFFont font = wb.createFont(); font.setFontName(style.getFont());/*ww w . j a v a2s.c o m*/ font.setFontHeightInPoints(style.getFontSize()); font.setBold(style.isBold()); font.setItalic(style.isItalics()); if (style.getFgColor() != null) { font.setColor(new XSSFColor(style.getFgColor())); } XSSFCellStyle cs = support.getWorkbook().createCellStyle(); cs.setFont(font); if (style.getBgColor() != null) { cs.setFillForegroundColor(new XSSFColor(style.getBgColor())); cs.setFillPattern(FillPatternType.SOLID_FOREGROUND); } if (style.getFormat() != null) { cs.setDataFormat(support.getDataFormat().getFormat(style.getFormat())); } // style.getBgColor(); // style.getFgColor(); // style.isBold(); // style.isItalics(); return cs; }
From source file:step.datapool.excel.StyleSyntax.java
License:Open Source License
/** * Analysiert den angegebenen Stil und setzt diesen excel-konform. * /* w w w . ja v a 2 s . c o m*/ * @param strStyle strStyle * @param _wb _wb * @return XSSFCellStyle */ public static XSSFCellStyle composeStyle(String strStyle, Workbook _wb) { if (_wb instanceof XSSFWorkbook) { XSSFWorkbook wb = (XSSFWorkbook) _wb; if (strStyle == null || strStyle.isEmpty()) return null; XSSFCellStyle style = wb.createCellStyle(); XSSFFont font = wb.createFont(); strStyle = replaceColors(strStyle); /* Unterteilung der verschiedenen Stilangaben: Schriftschnitt, Vordergrund/Hintergrundfarbe, Schriftgroesse, Schriftsatz */ String[] arr = strStyle.split(","); for (String str : arr) { /* Abhandlung des Schriftschnitts. Diese koennen alle zusammen auftreten und schliessen sich nicht aus */ if (str.matches("( *bold *| *italic *| *underline *| *strikeout *)*")) { if (str.contains("bold")) { font.setBoldweight(Font.BOLDWEIGHT_BOLD); } if (str.contains("italic")) { font.setItalic(true); } if (str.contains("underline")) { font.setUnderline(Font.U_SINGLE); } if (str.contains("strikeout")) { font.setStrikeout(true); } continue; } /* Abhandlung der Farben */ if (str.contains("/") && !str.contains(":")) { continue; } if (str.contains(":")) { String[] fgBg; /* Vordergrund / Hintergrund */ if (str.contains("/")) { fgBg = str.split("/"); } else { /* Verkuerzte Angabe ohne / (nur Vordergrund) */ fgBg = new String[1]; fgBg[0] = str; } for (int i = 0; i < fgBg.length; i++) { fgBg[i] = fgBg[i].trim(); if (!fgBg[i].isEmpty()) { String[] clr = fgBg[i].split(":"); if (clr.length != 3) { return null; } int red = Integer.parseInt(clr[0].trim()); int green = Integer.parseInt(clr[1].trim()); int blue = Integer.parseInt(clr[2].trim()); if (i == 0) { /* Schriftfarbe * ------------------------------------------------------------- * | ACHTUNG: poi hat einen Fehler beim Setzen der Schriftfarbe! | * ------------------------------------------------------------- * Weiss und Schwarz sind verwechselt worden! Gilt NUR fuer * font.setColor! * Deshalb wird hier einfach Weiss auf Schwarz korrigiert und * umgekehrt. */ if (red == 0 && green == 0 && blue == 0) { red = 255; green = 255; blue = 255; } else if (red == 255 && green == 255 && blue == 255) { red = 0; green = 0; blue = 0; } XSSFColor xssfColor = new XSSFColor(new Color(red, green, blue)); font.setColor(xssfColor); } else { // Vordergrund/Hintergrundfarbe der Zelle XSSFColor xssfColor = new XSSFColor(new Color(red, green, blue)); style.setFillForegroundColor(xssfColor); style.setFillBackgroundColor(xssfColor); style.setFillPattern(CellStyle.SOLID_FOREGROUND); } } } continue; } /* Abhandlung der Schriftgroesse */ if (str.matches(" *[1-9][0-9]* *")) { short fontHeightInPoints = Short.parseShort(str.trim()); font.setFontHeightInPoints(fontHeightInPoints); continue; } /* Abhandlung der Schriftart */ if (!str.isEmpty()) { font.setFontName(str); continue; } } style.setFont(font); return style; } else { return null; } }
From source file:tools.IOHelper.java
public static void plate1ResultSheet(ANAPlate plate, File outputFolder) throws IOException { File outputFile = new File(outputFolder, plate.getPlateId() + "_" + TIME + ".xlsx"); Workbook excelFile = null;/*from w ww. j ava2 s. c o m*/ // if (outputFile.exists()) { // try { // excelFile = WorkbookFactory.create(outputFile); // } catch (EncryptedDocumentException ex) { // System.out.println("file with assigned name already exists but is encrypted..."); // Logger.getLogger(IOHelper.class.getName()).log(Level.SEVERE, null, ex); // } catch (InvalidFormatException ex) { // Logger.getLogger(IOHelper.class.getName()).log(Level.SEVERE, null, ex); // } // } else { // outputFile.createNewFile(); // excelFile = new XSSFWorkbook(); // } if (outputFile.exists()) { outputFile.delete(); } outputFile.createNewFile(); excelFile = new XSSFWorkbook(); if (excelFile == null) { throw new RuntimeException("fail to create the xlsx file"); } int rowCount = plate.getSampleNumber(); //not including the 2 control samples + "_"+time String sheetName = plate.getPlateId(); //create a working sheet Sheet sheet = excelFile.createSheet(sheetName); //starting row & col int rowIndex = 0; int colIndex = 0; int totalCol = 0; int pos = 0, neg = 0, all = 0; XSSFFont fontTitle = (XSSFFont) excelFile.createFont(); fontTitle.setFontHeightInPoints((short) 10); fontTitle.setFontName("Arial"); fontTitle.setColor(IndexedColors.GREEN.getIndex()); fontTitle.setBold(true); fontTitle.setItalic(false); XSSFCellStyle styleTitle = (XSSFCellStyle) excelFile.createCellStyle(); styleTitle.setAlignment(CellStyle.ALIGN_CENTER); styleTitle.setFont(fontTitle); Cell cell0 = sheet.createRow(rowIndex++).createCell(0); cell0.setCellValue(plate.getPlateId() + " Summary"); //title cell0.setCellStyle(styleTitle); // //optional set Cell Style // CellStyle styleTitle = null; // CellStyle style = null; Row row = sheet.createRow(rowIndex++); //names Cell column = row.createCell(colIndex++); column.setCellValue("Sample ID"); totalCol++; column = row.createCell(colIndex++); column.setCellValue("Chip Location"); totalCol++; column = row.createCell(colIndex++); column.setCellValue("Result"); totalCol++; // column = row.createCell(colIndex++); // column.setCellValue("Positivity 0.3P"); // totalCol++; // column = row.createCell(colIndex++); // column.setCellValue("Positivity0.275P+0.5N"); // totalCol++; column = row.createCell(colIndex++); column.setCellValue("Signal"); totalCol++; column = row.createCell(colIndex++); column.setCellValue("Comments"); totalCol++; for (ANATestResult result : plate.getTestResultList()) { row = sheet.createRow(rowIndex++); //data colIndex = 0; column = row.createCell(colIndex++); column.setCellValue(result.getJulien_barcode()); column = row.createCell(colIndex++); column.setCellValue(result.getPillarPosition()); column = row.createCell(colIndex++); if (result.getPositivity() == null) { column.setCellValue("Null Result"); } else { if (ANA_Result.POSITIVE.equals(result.getPositivity())) { pos++; } else if (ANA_Result.NEGATIVE.equals(result.getPositivity())) { neg++; } all++; column.setCellValue(result.getPositivity().name()); } //make sure all fis has not-null pn result // column = row.createCell(colIndex++); // if (result.positivity30 == null) { // column.setCellValue("Null Result"); // } else { // if (ANA_Result.POSITIVE.equals(result.positivity30)) { //// pos++; // } else if (ANA_Result.NEGATIVE.equals(result.positivity30)) { //// neg++; // } //// all++; // column.setCellValue(result.positivity30.name()); // } //make sure all fis has not-null pn result // column = row.createCell(colIndex++); // if (result.positivityCombined == null) { // column.setCellValue("Null Result"); // } else { // if (ANA_Result.POSITIVE.equals(result.positivityCombined)) { //// pos++; // } else if (ANA_Result.NEGATIVE.equals(result.positivityCombined)) { //// neg++; // } //// all++; // column.setCellValue(result.positivityCombined.name()); // } //make sure all fis has not-null pn result column = row.createCell(colIndex++); column.setCellValue(result.getFirstPlateSignal()); // if(result.getFirstPlateSignal()<0){ // column.setCellValue("ROI exception: unable to get signal for this sample"); // }else{ // column.setCellValue(result.getFirstPlateSignal()); // } column = row.createCell(colIndex++); //warning msg concat mthd; merge plateErr to sampErr column.setCellValue(result.concatWarningMsgs()); } if (rowIndex - rowCount == 2) { for (int i = 0; i < totalCol; i++) { sheet.autoSizeColumn(i); } } else { System.out .println((rowIndex - 2) + " records are writen into file while " + rowCount + " are expected"); } row = sheet.createRow(rowIndex++); //total colIndex = 0; column = row.createCell(colIndex++); column.setCellValue("all samples"); column = row.createCell(colIndex++); column.setCellValue(all); column = row.createCell(colIndex++); column.setCellValue("positive samples"); column = row.createCell(colIndex++); column.setCellValue(pos); column = row.createCell(colIndex++); column.setCellValue("negative samples"); column = row.createCell(colIndex++); column.setCellValue(neg); column = row.createCell(colIndex++); column = row.createCell(colIndex++); column = row.createCell(colIndex++); column.setCellValue("PosCtrl"); column = row.createCell(colIndex++); column.setCellValue(plate.getPosCtrl().getFirstPlateSignal()); column = row.createCell(colIndex++); column.setCellValue("NegCtrl"); column = row.createCell(colIndex++); column.setCellValue(plate.getNegCtrlSignal()); FileOutputStream fos = null; try { fos = new FileOutputStream(outputFile.getAbsolutePath());//,true excelFile.write(fos); } catch (FileNotFoundException ex) { Logger.getLogger(IOHelper.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(IOHelper.class.getName()).log(Level.SEVERE, null, ex); } finally { if (fos != null) { try { fos.close(); } catch (IOException ex) { Logger.getLogger(IOHelper.class.getName()).log(Level.SEVERE, null, ex); } } } }