List of usage examples for org.apache.poi.hssf.usermodel HSSFFont setFontHeightInPoints
public void setFontHeightInPoints(short height)
From source file:reports.vmmcexcel.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {// www.j a va 2 s.c o m 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.nextreports.engine.exporter.XlsExporter.java
License:Apache License
private HSSFCellStyle buildBandElementStyle(BandElement bandElement, Object value, int gridRow, int gridColumn, int colSpan) { Map<String, Object> style = buildCellStyleMap(bandElement, value, gridRow, gridColumn, colSpan); HSSFCellStyle cellStyle;/* www . j av a 2s .co m*/ HSSFFont cellFont = null; int fontKey = -1; // we have to create new fonts and styles if some formatting conditions are met // also for subreports we may have a subreportCellStyle passed by ReportBandElement boolean cacheFont = false; boolean cacheAllFont = false; if ((modifiedStyle[gridRow][gridColumn]) || bean.isSubreport()) { fontKey = getFontKey(style); if (fontKey != -1) { cellFont = condFonts.get(fontKey); } cellStyle = wb.createCellStyle(); if (cellFont == null) { cellFont = wb.createFont(); cacheFont = true; } modifiedStyle[gridRow][gridColumn] = false; } else { cellStyle = styles[gridRow][gridColumn]; fontKey = getFontKey(style); if (fontKey != -1) { cellFont = fonts.get(fontKey); } if ((cellFont == null) && (bandElement != null)) { cellFont = wb.createFont(); cacheAllFont = true; } } // HSSFPalette cellPal = wb.getCustomPalette(); if (style.containsKey(StyleFormatConstants.FONT_FAMILY_KEY)) { String val = (String) style.get(StyleFormatConstants.FONT_FAMILY_KEY); cellFont.setFontName(val); } if (style.containsKey(StyleFormatConstants.FONT_SIZE)) { Float val = (Float) style.get(StyleFormatConstants.FONT_SIZE); cellFont.setFontHeightInPoints(val.shortValue()); } if (style.containsKey(StyleFormatConstants.FONT_COLOR)) { Color val = (Color) style.get(StyleFormatConstants.FONT_COLOR); cellFont.setColor(ExcelColorSupport.getNearestColor(val)); } if (style.containsKey(StyleFormatConstants.FONT_STYLE_KEY)) { if (StyleFormatConstants.FONT_STYLE_NORMAL.equals(style.get(StyleFormatConstants.FONT_STYLE_KEY))) { cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); } if (StyleFormatConstants.FONT_STYLE_BOLD.equals(style.get(StyleFormatConstants.FONT_STYLE_KEY))) { cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); } if (StyleFormatConstants.FONT_STYLE_ITALIC.equals(style.get(StyleFormatConstants.FONT_STYLE_KEY))) { cellFont.setItalic(true); } if (StyleFormatConstants.FONT_STYLE_BOLDITALIC.equals(style.get(StyleFormatConstants.FONT_STYLE_KEY))) { cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellFont.setItalic(true); } } if (cacheFont && (fontKey != -1)) { condFonts.put(fontKey, cellFont); } if (cacheAllFont && (fontKey != -1)) { fonts.put(fontKey, cellFont); } if (style.containsKey(StyleFormatConstants.BACKGROUND_COLOR)) { Color val = (Color) style.get(StyleFormatConstants.BACKGROUND_COLOR); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setFillForegroundColor(ExcelColorSupport.getNearestColor(val)); } if (style.containsKey(StyleFormatConstants.HORIZONTAL_ALIGN_KEY)) { if (StyleFormatConstants.HORIZONTAL_ALIGN_LEFT .equals(style.get(StyleFormatConstants.HORIZONTAL_ALIGN_KEY))) { cellStyle.setAlignment((short) 1); } if (StyleFormatConstants.HORIZONTAL_ALIGN_RIGHT .equals(style.get(StyleFormatConstants.HORIZONTAL_ALIGN_KEY))) { cellStyle.setAlignment((short) 3); } if (StyleFormatConstants.HORIZONTAL_ALIGN_CENTER .equals(style.get(StyleFormatConstants.HORIZONTAL_ALIGN_KEY))) { cellStyle.setAlignment((short) 2); } } if (style.containsKey(StyleFormatConstants.VERTICAL_ALIGN_KEY)) { if (StyleFormatConstants.VERTICAL_ALIGN_TOP .equals(style.get(StyleFormatConstants.VERTICAL_ALIGN_KEY))) { cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); } if (StyleFormatConstants.VERTICAL_ALIGN_MIDDLE .equals(style.get(StyleFormatConstants.VERTICAL_ALIGN_KEY))) { cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); } if (StyleFormatConstants.VERTICAL_ALIGN_BOTTOM .equals(style.get(StyleFormatConstants.VERTICAL_ALIGN_KEY))) { cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_BOTTOM); } } else { cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); } short left = 0, right = 0, top = 0, bottom = 0; Color leftColor = Color.BLACK, rightColor = Color.BLACK, topColor = Color.BLACK, bottomColor = Color.BLACK; if (style.containsKey(StyleFormatConstants.BORDER_LEFT)) { Float val = (Float) style.get(StyleFormatConstants.BORDER_LEFT); // left = val.shortValue(); if (left == BORDER_THIN_VALUE) { cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); } if (left == BORDER_MEDIUM_VALUE) { cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); } if (left == BORDER_THICK_VALUE) { cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THICK); } Color color = (Color) style.get(StyleFormatConstants.BORDER_LEFT_COLOR); leftColor = color; cellStyle.setLeftBorderColor(ExcelColorSupport.getNearestColor(color)); } if (style.containsKey(StyleFormatConstants.BORDER_RIGHT)) { Float val = (Float) style.get(StyleFormatConstants.BORDER_RIGHT); // right = val.shortValue(); if (right == BORDER_THIN_VALUE) { cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); } if (right == BORDER_MEDIUM_VALUE) { cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); } if (right == BORDER_THICK_VALUE) { cellStyle.setBorderRight(HSSFCellStyle.BORDER_THICK); } Color color = (Color) style.get(StyleFormatConstants.BORDER_RIGHT_COLOR); rightColor = color; cellStyle.setRightBorderColor(ExcelColorSupport.getNearestColor(color)); } if (style.containsKey(StyleFormatConstants.BORDER_TOP)) { Float val = (Float) style.get(StyleFormatConstants.BORDER_TOP); // top = val.shortValue(); if (top == BORDER_THIN_VALUE) { cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); } if (top == BORDER_MEDIUM_VALUE) { cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); } if (top == BORDER_THICK_VALUE) { cellStyle.setBorderTop(HSSFCellStyle.BORDER_THICK); } Color color = (Color) style.get(StyleFormatConstants.BORDER_TOP_COLOR); topColor = color; cellStyle.setTopBorderColor(ExcelColorSupport.getNearestColor(color)); } if (style.containsKey(StyleFormatConstants.BORDER_BOTTOM)) { Float val = (Float) style.get(StyleFormatConstants.BORDER_BOTTOM); // bottom = val.shortValue(); if (bottom == BORDER_THIN_VALUE) { cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); } if (bottom == BORDER_MEDIUM_VALUE) { cellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); } if (bottom == BORDER_THICK_VALUE) { cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THICK); } Color color = (Color) style.get(StyleFormatConstants.BORDER_BOTTOM_COLOR); bottomColor = color; cellStyle.setBottomBorderColor(ExcelColorSupport.getNearestColor(color)); } border = new Border(left, right, top, bottom); border.setLeftColor(leftColor); border.setRightColor(rightColor); border.setTopColor(topColor); border.setBottomColor(bottomColor); if (cellFont != null) { cellStyle.setFont(cellFont); } if (style.containsKey(StyleFormatConstants.PATTERN)) { String pattern = (String) style.get(StyleFormatConstants.PATTERN); HSSFDataFormat format = wb.createDataFormat(); cellStyle.setDataFormat(format.getFormat(pattern)); } if (bandElement != null) { cellStyle.setWrapText(bandElement.isWrapText()); } cellStyle = updateSubreportBandElementStyle(cellStyle, bandElement, value, gridRow, gridColumn, colSpan); return cellStyle; }
From source file:senselogic.excelbundle.ExcelExporter.java
License:Apache License
/** * Do stuff like creating the workbook and creating the styles. *//*from w w w . j a v a2s . c o m*/ private void initialize() { wb = new HSSFWorkbook(); //Create style for bundle path cells bundlePathStyle = wb.createCellStyle(); HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 14); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); bundlePathStyle.setFont(font); bundlePathStyle.setLocked(true); //Create style for language column languageStyle = wb.createCellStyle(); font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); languageStyle.setFont(font); languageStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); languageStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); languageStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); languageStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); languageStyle.setLocked(true); //Create style for key cells keyStyle = wb.createCellStyle(); font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); keyStyle.setFont(font); keyStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); keyStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); keyStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); keyStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); keyStyle.setLocked(true); //Create style for value cells valueStyle = wb.createCellStyle(); valueStyle.setWrapText(true); valueStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); valueStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); valueStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); valueStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); valueStyle.setLocked(false); if (redmark) { //Create style for cells where values are missing missingStyle = wb.createCellStyle(); missingStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); missingStyle.setFillForegroundColor(HSSFColor.ORANGE.index); missingStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); missingStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); missingStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); missingStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); missingStyle.setLocked(false); } }
From source file:servlets.ReportesCSV.java
@Override public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { Connection con = null;/* www. j a va 2 s. com*/ PreparedStatement ps = null; ResultSet rs = null; String fechaInicial = request.getParameter("fechaInicial"); String fechaFinal = request.getParameter("fechaFinal"); String url = getServletContext().getRealPath("/"); String identificacion = request.getParameter("identificacion"); String producto = request.getParameter("producto"); String cotizacion = request.getParameter("cotizacion"); int sede = Integer.parseInt(request.getParameter("sede")); String tipo = request.getParameter("tipo"); String query = null; try { con = DBConnector.getInstance().getConnection(); switch (tipo) { case "rotacion": query = "SELECT p.codigoInterno,p.nomProducto,sum(cantidad),mim.nombreMovimiento,p.costo " + "FROM kalamarypos.inv_movimiento m " + "inner join inv_movimiento_detalle md on md.inv_movimiento_numDoc = m.numDoc " + "inner join cfg_mov_inventario_detalle mi on mi.idMovInventarioDetalle = m.cfg_mov_inventario_detalle_idMovInventarioDetalle " + "inner join cfg_mov_inventario_maestro mim on mim.idMovInventarioMaestro = mi.cfg_mov_inventario_maestro_idMovInventarioMaestro " + "inner join cfg_producto p on p.idProducto = md.cfg_producto_IdProducto " + "where fecha between ? and ADDDATE(?, INTERVAL 13 DAY) " + "and cfg_empresasede_idSede = ? " + "group by p.codProducto,p.nomProducto,mim.nombreMovimiento " + "order by 3 desc"; break; case "vc": query = "SELECT f.fecCrea,d.prefijoDoc, fc.fac_documentosmaster_numDocumento,ifnull(di.valorImpuesto,0) iva,f.subtotal-f.descuento as subtotal,f.total, " + "concat(ti.abreviatura,' ',c.numDoc,' ',c.nom1Cliente,' ',c.nom2Cliente,' ',c.apellido1,' ',c.apellido2) as nombre " + "FROM kalamarypos.fac_cartera_cliente fc " + "inner join fac_documentosmaster f on fc.fac_documentosmaster_numDocumento = f.numDocumento " + "inner join cfg_cliente c on c.idCliente = fc.cfg_cliente_idCliente " + "inner join cfg_documento d on d.cfg_empresasede_idSede = f.cfg_empresasede_idSede " + "inner join cfg_tipoidentificacion ti on ti.id = c.cfg_tipoidentificacion_id " + "left join fac_documentoimpuesto di on di.fac_documentosmaster_numDocumento = fc.fac_documentosmaster_numDocumento and di.porcentajeImpuesto=16 and di.fac_documentosmaster_cfg_documento_idDoc=f.cfg_documento_idDoc " + "where f.estado!='CANCELADA' and f.estado!='ANULADA' " + "and f.cfg_empresasede_idSede=? " + "and d.codDocumento !=6 " + "and f.cfg_documento_idDoc =d.idDoc "; if (!identificacion.equals("0")) { query = query + " and c.numDoc = ? "; } query = query + " order by 1 desc"; break; case "productosVendidos": query = "SELECT c.nombreCategoria, " + " r.nombreReferencia, " + " m.nombreMarca, " + " p.codigoInterno, " + " p.nomProducto, " + " sum(fd.cantidad) as total, " + " max(f.fecCrea) as maxFeCrea " + "FROM kalamarypos.fac_cartera_cliente fc " + "inner join fac_documentosmaster f on fc.fac_documentosmaster_numDocumento = f.numDocumento " + "inner join cfg_documento d on d.cfg_empresasede_idSede = f.cfg_empresasede_idSede " + "inner join fac_documentodetalle fd on fd.fac_documentosmaster_cfg_documento_idDoc=f.cfg_documento_idDoc " + "inner join cfg_producto p on p.idProducto = fd.cfg_producto_idProducto " + "inner join cfg_categoriaproducto c on c.idCategoria = p.cfg_categoriaproducto_idCategoria " + "inner join cfg_referenciaproducto r on r.idReferencia = p.cfg_referenciaproducto_idReferencia " + "inner join cfg_marcaproducto m on m.idMarca = p.cfg_marcaproducto_idMarca " + "where f.estado!='CANCELADA' and f.estado!='ANULADA' " + "and f.cfg_empresasede_idSede=? " + "and fd.fac_documentosmaster_numDocumento=f.numDocumento " + "and f.cfg_documento_idDoc =d.idDoc " + "and f.fecCrea between ? and ADDDATE(?, INTERVAL 1 DAY) " + "and fd.fac_documentosmaster_numDocumento = f.numDocumento " + "group by fd.cfg_producto_idProducto " + "order by 6 desc "; break;//productosVendidos case "ventasVendedores": query = "SELECT f.fecCrea,d.prefijoDoc, fc.fac_documentosmaster_numDocumento,ifnull(di.valorImpuesto,0) iva,f.subtotal-f.descuento as subtotal,f.total, " + "concat('CC ',s.numDoc,' ',s.nom1Usuario,' ',s.nom2Usuario,' ',s.apellido1,' ',s.apellido2) as nombre " + "FROM kalamarypos.fac_cartera_cliente fc " + "inner join fac_documentosmaster f on fc.fac_documentosmaster_numDocumento = f.numDocumento " + "inner join cfg_documento d on d.cfg_empresasede_idSede = f.cfg_empresasede_idSede " + "inner join seg_usuario s on s.idUsuario = f.seg_usuario_idUsuario1 " + " left join fac_documentoimpuesto di on di.fac_documentosmaster_numDocumento = fc.fac_documentosmaster_numDocumento and di.porcentajeImpuesto=16 and di.fac_documentosmaster_cfg_documento_idDoc=f.cfg_documento_idDoc " + "where f.estado!='CANCELADA' and f.estado!='ANULADA' " + "and f.cfg_empresasede_idSede=? " + "and f.fecCrea between ? and ADDDATE(?, INTERVAL 13 DAY) " + "and f.cfg_documento_idDoc =d.idDoc " + "and d.codDocumento !=6 " + "and s.cfg_rol_idrol=3 "; if (!identificacion.equals("0")) { if (!identificacion.equals("")) { query = query + "and s.numDoc = ? "; } } query = query + " order by 1 desc"; break;//Ventas vendedores case "productosA": query = "select c.nombreCategoria, " + " r.nombreReferencia, " + " m.nombreMarca, " + " p.codigoInterno, " + " p.nomProducto, " + " p.precio " + "from cfg_producto p " + "inner join cfg_empresasede s on s.cfg_empresa_idEmpresa = p.cfg_empresa_idEmpresa " + "inner join cfg_categoriaproducto c on c.idCategoria = p.cfg_categoriaproducto_idCategoria " + "inner join cfg_referenciaproducto r on r.idReferencia = p.cfg_referenciaproducto_idReferencia " + "inner join cfg_marcaproducto m on m.idMarca = p.cfg_marcaproducto_idMarca " + "where s.idSede =? "; if (producto != null) { if (!producto.equals("")) { query = query + "and p.codigoInterno= ? "; } } break;//Productos case "servicios": query = "SELECT c.nombreCategoria, " + " r.nombreReferencia, " + " m.nombreMarca, " + " p.codigoInterno, " + " p.codBarProducto , " + " p.nomProducto, " + " p.costo, " + " p.utilidad, " + " p.precio, " + " p.fecCrea " + "FROM " + " cfg_producto p " + "inner join cfg_empresasede s on s.cfg_empresa_idEmpresa = p.cfg_empresa_idEmpresa " + "inner join cfg_categoriaproducto c on c.idCategoria = p.cfg_categoriaproducto_idCategoria " + "inner join cfg_referenciaproducto r on r.idReferencia = p.cfg_referenciaproducto_idReferencia " + "inner join cfg_marcaproducto m on m.idMarca = p.cfg_marcaproducto_idMarca " + "where p.esServicio=1 " + "and s.idSede =? "; if (producto != null) { if (!producto.equals("")) { query = query + "and p.codigoInterno= ? "; } } query = query + "order by 1 desc"; break;//Servicios case "stock": query = "SELECT c.nombreCategoria, " + " r.nombreReferencia, " + " m.nombreMarca, " + " p.codigoInterno, " + " p.nomProducto, " + " co.existencia " + "FROM kalamarypos.cfg_producto p " + "inner join inv_consolidado co on co.cfg_producto_idProducto = p.idProducto " + "inner join cfg_categoriaproducto c on c.idCategoria = p.cfg_categoriaproducto_idCategoria " + "inner join cfg_referenciaproducto r on r.idReferencia = p.cfg_referenciaproducto_idReferencia " + "inner join cfg_marcaproducto m on m.idMarca = p.cfg_marcaproducto_idMarca " + "where co.cfg_empresasede_idSede=? "; if (producto != null) { if (!producto.equals("")) { query = query + "and p.codigoInterno= ? "; } } query = query + " order by co.existencia desc"; break;//stock case "productoClientes": query = "SELECT concat(ti.abreviatura,' ',c.numDoc,' ',c.nom1Cliente,' ',c.nom2Cliente,' ',c.apellido1,' ',c.apellido2) as nombre, " + "fc.cfg_cliente_idCliente,fc.fac_documentosmaster_numDocumento,sum(cantidad) as total,fd.cfg_producto_idProducto, " + "max(f.fecCrea),concat(ca.nombreCategoria,' ',r.nombreReferencia,' ',m.nombreMarca,' ',p.codigoInterno,' ',p.nomProducto) as producto " + "FROM kalamarypos.fac_cartera_cliente fc " + "inner join fac_documentosmaster f on fc.fac_documentosmaster_numDocumento = f.numDocumento " + "inner join fac_documentodetalle fd on fd.fac_documentosmaster_cfg_documento_idDoc=f.cfg_documento_idDoc " + "inner join cfg_cliente c on c.idCliente = fc.cfg_cliente_idCliente " + "inner join cfg_documento d on d.cfg_empresasede_idSede = f.cfg_empresasede_idSede " + "inner join cfg_tipoidentificacion ti on ti.id = c.cfg_tipoidentificacion_id " + "inner join cfg_producto p on p.idProducto = fd.cfg_producto_idProducto " + "inner join cfg_categoriaproducto ca on ca.idCategoria = p.cfg_categoriaproducto_idCategoria " + "inner join cfg_referenciaproducto r on r.idReferencia = p.cfg_referenciaproducto_idReferencia " + "inner join cfg_marcaproducto m on m.idMarca = p.cfg_marcaproducto_idMarca " + "where f.estado!='CANCELADA' and f.estado!='ANULADA' " + "and f.cfg_empresasede_idSede=? " + "and fd.fac_documentosmaster_numDocumento = f.numDocumento " + "and d.codDocumento =1 "; if (producto != null) { if (!producto.equals("")) { query = query + "and p.codigoInterno= ? "; } } query = query + " group by fd.cfg_producto_idProducto " + "order by 4 desc"; break;//productosClientes case "cotizacion": query = "SELECT p.codigoInterno, " + "p.nomProducto, " + "p.precio, " + "ifnull(di.valorImpuesto,0) as iva, " + "f.total, " + "concat(ti.abreviatura,' ',cl.numDoc,' ',cl.nom1Cliente,' ',cl.nom2Cliente,' ',cl.apellido1,' TELEFONO ',cl.tel1) as nombre, " + "concat('No Cotizacin:',' ',d.prefijoDoc,' ', fc.fac_documentosmaster_numDocumento) cotizacion, " + "f.fecCrea, concat('Vendedor:',s.nom1Usuario,' ',s.nom2Usuario,' ',s.apellido1,' ',s.apellido2) as vendedor " + " " + "FROM kalamarypos.fac_documentosmaster fc " + "inner join fac_documentosmaster f on fc.fac_documentosmaster_numDocumento = f.numDocumento " + "inner join fac_documentodetalle fd on fd.fac_documentosmaster_cfg_documento_idDoc=f.cfg_documento_idDoc " + "inner join cfg_cliente cl on cl.idCliente = fc.cfg_cliente_idCliente " + "inner join cfg_documento d on d.cfg_empresasede_idSede = f.cfg_empresasede_idSede " + "inner join cfg_producto p on p.idProducto = fd.cfg_producto_idProducto " + "inner join cfg_categoriaproducto c on c.idCategoria = p.cfg_categoriaproducto_idCategoria " + "inner join cfg_referenciaproducto r on r.idReferencia = p.cfg_referenciaproducto_idReferencia " + "inner join cfg_marcaproducto m on m.idMarca = p.cfg_marcaproducto_idMarca " + "inner join cfg_tipoidentificacion ti on ti.id = cl.cfg_tipoidentificacion_id " + "inner join seg_usuario s on s.idUsuario = f.seg_usuario_idUsuario1 " + "left join fac_documentoimpuesto di on di.fac_documentosmaster_numDocumento = fc.fac_documentosmaster_numDocumento and di.porcentajeImpuesto=16 and di.fac_documentosmaster_cfg_documento_idDoc=f.cfg_documento_idDoc " + "where f.estado!='CANCELADA' and f.estado!='ANULADA' " + "and f.cfg_empresasede_idSede=? "; if (identificacion != null) { if (!identificacion.equals("")) query = query + "and cl.numDoc = ? "; } if (cotizacion != null) { if (!cotizacion.equals("")) query = query + "and fc.fac_documentosmaster_numDocumento = ? "; } query = query + " and d.codDocumento =6 " + "and fd.fac_documentosmaster_numDocumento=f.numDocumento " + "and f.cfg_documento_idDoc = d.idDoc " + "and s.cfg_rol_idrol=3"; break;//cotizacion case "vencimientosFacturas": query = "SELECT cc.numDoc, " + " concat(cc.nom1Cliente,' ',cc.nom2Cliente,' ',cc.apellido1,' ',cc.apellido2) cliente, " + " cc.tel1, " + " fc.valor, " + " fc.saldo, " + " fc.fecha_limite, " + " concat(d.prefijoDoc,' ',fd.numDocumento) documento " + "FROM kalamarypos.fac_cartera_cliente fc " + "inner join fac_documentosmaster fd on fc.fac_documentosmaster_numDocumento = fd.numDocumento " + "inner join cfg_cliente cc on cc.idCliente = fc.cfg_cliente_idCliente " + "inner join cfg_documento d on d.cfg_empresasede_idSede = fd.cfg_empresasede_idSede " + " where fc.estado='PENDIENTE' " + "and fecha_limite<=ADDDATE(now(), INTERVAL 15 DAY) " + "and fd.cfg_documento_idDoc = fc.fac_documentosmaster_cfg_documento_idDoc " + "and d.codDocumento =1 " + "and fd.cfg_documento_idDoc =d.idDoc " + "and fd.cfg_empresasede_idSede=? " + "and fd.cfg_documento_idDoc = fc.fac_documentosmaster_cfg_documento_idDoc " + "order by fecha_limite"; break;//vencimiento factruas case "vencimientosSeparados": query = "SELECT cc.numDoc, " + " concat(cc.nom1Cliente,' ',cc.nom2Cliente,' ',cc.apellido1,' ',cc.apellido2) cliente, " + " cc.tel1, " + " fc.valor, " + " fc.saldo, " + " fc.fecha_limite, " + " concat(d.prefijoDoc,' ',fd.numDocumento) documento " + "FROM kalamarypos.fac_cartera_cliente fc " + "inner join fac_documentosmaster fd on fc.fac_documentosmaster_numDocumento = fd.numDocumento " + "inner join cfg_cliente cc on cc.idCliente = fc.cfg_cliente_idCliente " + "inner join cfg_documento d on d.cfg_empresasede_idSede = fd.cfg_empresasede_idSede " + " where fc.estado='PENDIENTE' " + "and fecha_limite<=ADDDATE(now(), INTERVAL 15 DAY) " + "and fd.cfg_documento_idDoc = fc.fac_documentosmaster_cfg_documento_idDoc " + "and d.codDocumento =7 " + "and fd.cfg_empresasede_idSede=? " + "and fd.cfg_documento_idDoc =d.idDoc " + "and fd.cfg_documento_idDoc = fc.fac_documentosmaster_cfg_documento_idDoc " + "order by fecha_limite"; break;//vencimientoSeparados case "productosSinRotacion": query = "SELECT c.nombreCategoria, " + " r.nombreReferencia, " + " ma.nombreMarca, " + " p.codigoInterno, " + " p.nomProducto, " + " max(m.fecha) fechaultimo " + " " + "FROM kalamarypos.inv_movimiento_detalle im " + "inner join inv_movimiento m on im.inv_movimiento_numDoc = m.numDoc " + "left join cfg_producto p on p.idProducto = im.cfg_producto_idProducto " + "inner join cfg_categoriaproducto c on c.idCategoria = p.cfg_categoriaproducto_idCategoria " + "inner join cfg_referenciaproducto r on r.idReferencia = p.cfg_referenciaproducto_idReferencia " + "inner join cfg_marcaproducto ma on ma.idMarca = p.cfg_marcaproducto_idMarca " + "where cfg_empresasede_idSede=? " + "group by im.cfg_producto_idProducto " + "having DATEDIFF(now(),max(m.fecha))>=90 " + "order by 6"; break;//productosSinRotacion } ps = con.prepareStatement(query); ps.clearParameters(); int indice = 1; switch (tipo) { case "rotacion": ps.setString(indice++, fechaInicial); ps.setString(indice++, fechaFinal); ps.setInt(indice++, sede); break; case "vc": ps.setInt(indice++, sede); if (!identificacion.equals("0")) { ps.setString(indice++, identificacion); } break; case "productosVendidos": ps.setInt(indice++, sede); ps.setString(indice++, fechaInicial); ps.setString(indice++, fechaFinal); break; case "ventasVendedores": ps.setInt(indice++, sede); ps.setString(indice++, fechaInicial); ps.setString(indice++, fechaFinal); if (!identificacion.equals("0")) { if (!identificacion.equals("")) ps.setString(indice++, identificacion); } break; case "productosA": ps.setInt(indice++, sede); if (producto != null) { if (!producto.equals("")) { ps.setString(indice++, producto); } } break;//Producros case "servicios": ps.setInt(indice++, sede); if (producto != null) { if (!producto.equals("")) { ps.setString(indice++, producto); } } break;//servicios case "stock": ps.setInt(indice++, sede); if (producto != null) { if (!producto.equals("")) { ps.setString(indice++, producto); } } break;//stock case "productoClientes": ps.setInt(indice++, sede); if (producto != null) { if (!producto.equals("")) { ps.setString(indice++, producto); } } break;//productoClientes case "cotizacion": ps.setInt(indice++, sede); if (identificacion != null) { if (!identificacion.equals("")) ps.setString(indice++, identificacion); } if (cotizacion != null) { if (!cotizacion.equals("")) ps.setString(indice++, cotizacion); } break; case "vencimientosFacturas": ps.setInt(indice++, sede); break; case "vencimientosSeparados": ps.setInt(indice++, sede); break; case "productosSinRotacion": ps.setInt(indice++, sede); break; } rs = ps.executeQuery(); Date fechaActual = new Date(); SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd"); SimpleDateFormat sd2 = new SimpleDateFormat("dd-MMM-yyyy"); String rutaArchivo = url + "/informes/reportes/"; String nombreArchivo = null; switch (tipo) { case "rotacion": nombreArchivo = "rotacionProducto_" + sd.format(fechaActual) + "_" + sede + ".xls"; break; case "vc": nombreArchivo = "ventasPorCliente_" + sd.format(fechaActual) + "_" + sede + ".xls"; break; case "productosVendidos": nombreArchivo = "productosVendidos" + sd.format(fechaActual) + "_" + sede + ".xls"; break; case "ventasVendedores": nombreArchivo = "ventasVendedores" + sd.format(fechaActual) + "_" + sede + ".xls"; break; case "productosA": nombreArchivo = "productos" + sd.format(fechaActual) + "_" + sede + ".xls"; break; case "servicios": nombreArchivo = "servicios" + sd.format(fechaActual) + "_" + sede + ".xls"; break; case "stock": nombreArchivo = "stock" + sd.format(fechaActual) + "_" + sede + ".xls"; break; case "productoClientes": nombreArchivo = "ventasProductosClientes" + sd.format(fechaActual) + "_" + sede + ".xls"; break; case "cotizacion": nombreArchivo = "cotizacion" + sd.format(fechaActual) + "_" + sede + ".xls"; break; case "vencimientosFacturas": nombreArchivo = "vencimientoFacturas" + sd.format(fechaActual) + "_" + sede + ".xls"; break; case "vencimientosSeparados": nombreArchivo = "vencimientoSeparados" + sd.format(fechaActual) + "_" + sede + ".xls"; break; case "productosSinRotacion": nombreArchivo = "productosSinRotacion" + sd.format(fechaActual) + "_" + sede + ".xls"; break; } rutaArchivo = rutaArchivo + nombreArchivo; File archivo1 = new File(rutaArchivo); if (archivo1.exists()) { archivo1.delete(); } archivo1.createNewFile(); HSSFWorkbook libro = new HSSFWorkbook(); HSSFCellStyle cellStyle = libro.createCellStyle(); HSSFFont font = libro.createFont(); font.setFontName(HSSFFont.FONT_ARIAL); font.setFontHeightInPoints((short) 10); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.BLACK.index); font.setCharSet(HSSFFont.ANSI_CHARSET); cellStyle.setFont(font); /*Se inicializa el flujo de datos con el archivo xls*/ FileOutputStream archi = new FileOutputStream(rutaArchivo); Sheet hoja; Cell celda; Row fila; int i = 0; switch (tipo) { case "rotacion": hoja = libro.createSheet("Rotacin de Producto"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("CODIGO PRODUCTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("PRODUCTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("CANTIDAD MOVIMIENTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("TIPO MOVIMIENTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(4); celda.setCellValue("COSTO UNITARIO"); celda.setCellStyle(cellStyle); celda = fila.createCell(5); celda.setCellValue("COSTO TOTAL"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(rs.getString(1)); celda = fila.createCell(1); celda.setCellValue(rs.getString(2)); celda = fila.createCell(2); celda.setCellValue(rs.getInt(3)); celda = fila.createCell(3); celda.setCellValue(rs.getString(4)); celda = fila.createCell(4); celda.setCellValue(rs.getDouble(5)); celda = fila.createCell(5); celda.setCellValue((rs.getDouble(5) * rs.getInt(3))); ////celda.getStringCellValue().getBytes(Charset.forName("UTF-8")); i = i + 1; } break; //En rotacin case "vc": hoja = libro.createSheet("Ventas por clientes"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("FECHA"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("DOCUMENTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("CLIENTE"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("SUBTOTAL"); celda.setCellStyle(cellStyle); celda = fila.createCell(4); celda.setCellValue("IVA"); celda.setCellStyle(cellStyle); celda = fila.createCell(5); celda.setCellValue("COSTO TOTAL"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(sd2.format(rs.getDate(1))); celda = fila.createCell(1); celda.setCellValue(rs.getString(2) + " " + rs.getString(3)); celda = fila.createCell(2); celda.setCellValue(rs.getString(7)); celda = fila.createCell(3); celda.setCellValue(rs.getDouble(5)); celda = fila.createCell(4); celda.setCellValue(rs.getDouble(4)); celda = fila.createCell(5); celda.setCellValue(rs.getDouble(6)); i = i + 1; } break;//Ventas por cliente case "productosVendidos": hoja = libro.createSheet("Productos Vendidos"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("CATEGORIA"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("REFERENCIA"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("MARCA"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("CODIGO INTERNO"); celda.setCellStyle(cellStyle); celda = fila.createCell(4); celda.setCellValue("PRODUCTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(5); celda.setCellValue("CANTIDAD VENDIDA"); celda.setCellStyle(cellStyle); celda = fila.createCell(6); celda.setCellValue("FECHA ULTIMA VENTA"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(rs.getString(1)); celda = fila.createCell(1); celda.setCellValue(rs.getString(2)); celda = fila.createCell(2); celda.setCellValue(rs.getString(3)); celda = fila.createCell(3); celda.setCellValue(rs.getString(4)); celda = fila.createCell(4); celda.setCellValue(rs.getString(5)); celda = fila.createCell(5); celda.setCellValue(rs.getDouble(6)); celda = fila.createCell(6); celda.setCellValue(sd2.format(rs.getDate(7))); i = i + 1; } break;//Productos Vendidos case "ventasVendedores": hoja = libro.createSheet("Ventas Vendedores"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("FECHA"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("VENDEDOR"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("DOCUMENTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("SUBTOTAL"); celda.setCellStyle(cellStyle); celda = fila.createCell(4); celda.setCellValue("IVA"); celda.setCellStyle(cellStyle); celda = fila.createCell(5); celda.setCellValue("TOTAL"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(sd2.format(rs.getDate(1))); celda = fila.createCell(1); celda.setCellValue(rs.getString("nombre")); celda = fila.createCell(2); celda.setCellValue(rs.getString(2) + " " + rs.getString(3)); celda = fila.createCell(3); celda.setCellValue(rs.getDouble("subtotal")); celda = fila.createCell(4); celda.setCellValue(rs.getDouble("IVA")); celda = fila.createCell(5); celda.setCellValue(rs.getDouble("TOTAL")); i = i + 1; } break;//ventas vendedores case "productosA": hoja = libro.createSheet("Informe Productos"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("CATEGORIA"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("REFERENCIA"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("MARCA"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("CODIGO INTERNO"); celda.setCellStyle(cellStyle); celda = fila.createCell(4); celda.setCellValue("PRODUCTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(5); celda.setCellValue("VALOR"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(rs.getString("nombreCategoria")); celda = fila.createCell(1); celda.setCellValue(rs.getString("nombreReferencia")); celda = fila.createCell(2); celda.setCellValue(rs.getString("nombreMarca")); celda = fila.createCell(3); celda.setCellValue(rs.getString("codigoInterno")); celda = fila.createCell(4); celda.setCellValue(rs.getString("nomProducto")); celda = fila.createCell(5); celda.setCellValue(rs.getDouble("PRECIO")); i = i + 1; } break;//productos case "servicios": hoja = libro.createSheet("Servicios"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("CATEGORIA"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("REFERENCIA"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("MARCA"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("CODIGO INTERNO"); celda.setCellStyle(cellStyle); celda = fila.createCell(4); celda.setCellValue("CODIGO DE BARRAS"); celda.setCellStyle(cellStyle); celda = fila.createCell(5); celda.setCellValue("PRODUCTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(6); celda.setCellValue("COSTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(7); celda.setCellValue("UTILIDAD"); celda.setCellStyle(cellStyle); celda = fila.createCell(8); celda.setCellValue("PRECIO"); celda.setCellStyle(cellStyle); celda = fila.createCell(9); celda.setCellValue("FECHA CREACION"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(rs.getString("nombreCategoria")); celda = fila.createCell(1); celda.setCellValue(rs.getString("nombreReferencia")); celda = fila.createCell(2); celda.setCellValue(rs.getString("nombreMarca")); celda = fila.createCell(3); celda.setCellValue(rs.getString("codigoInterno")); celda = fila.createCell(4); celda.setCellValue(rs.getString("codBarProducto")); celda = fila.createCell(5); celda.setCellValue(rs.getString("nomProducto")); celda = fila.createCell(6); celda.setCellValue(rs.getDouble("costo")); celda = fila.createCell(7); celda.setCellValue(rs.getDouble("utilidad")); celda = fila.createCell(8); celda.setCellValue(rs.getDouble("precio")); celda = fila.createCell(9); celda.setCellValue(sd2.format(rs.getDate("fecCrea"))); i = i + 1; } break;//Servicios case "stock": hoja = libro.createSheet("Stock"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("CATEGORIA"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("REFERENCIA"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("MARCA"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("CODIGO INTERNO"); celda.setCellStyle(cellStyle); celda = fila.createCell(4); celda.setCellValue("PRODUCTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(5); celda.setCellValue("EXISTENCIA"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(rs.getString("nombreCategoria")); celda = fila.createCell(1); celda.setCellValue(rs.getString("nombreReferencia")); celda = fila.createCell(2); celda.setCellValue(rs.getString("nombreMarca")); celda = fila.createCell(3); celda.setCellValue(rs.getString("codigoInterno")); celda = fila.createCell(4); celda.setCellValue(rs.getString("nomProducto")); celda = fila.createCell(5); celda.setCellValue(rs.getLong("existencia")); i = i + 1; } break;//stock case "productoClientes": hoja = libro.createSheet("Ventas productos Clientes"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("CLIENTE"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("PRODUCTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("CANTIDAD VENDIDA"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("FECHA ULTIMA VENTA"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(rs.getString("nombre")); celda = fila.createCell(1); celda.setCellValue(rs.getString("producto")); celda = fila.createCell(2); celda.setCellValue(rs.getDouble("total")); celda = fila.createCell(3); celda.setCellValue(sd2.format(rs.getDate(6))); i = i + 1; } break;//productosClientes case "cotizacion": hoja = libro.createSheet("Ventas Cotizaciones"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("CLIENTE"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("COTIZACION"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("FECHA"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("CODIGO PRODUCTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(4); celda.setCellValue("PRODUCTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(5); celda.setCellValue("PRECIO"); celda.setCellStyle(cellStyle); celda = fila.createCell(6); celda.setCellValue("IVA"); celda.setCellStyle(cellStyle); celda = fila.createCell(7); celda.setCellValue("TOTAL"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(rs.getString("nombre")); celda = fila.createCell(1); celda.setCellValue(rs.getString("cotizacion")); celda = fila.createCell(2); celda.setCellValue(sd2.format(rs.getDate("fecCrea"))); celda = fila.createCell(3); celda.setCellValue(rs.getString("codigoInterno")); celda = fila.createCell(4); celda.setCellValue(rs.getString("nomProducto")); celda = fila.createCell(5); celda.setCellValue(rs.getDouble("precio")); celda = fila.createCell(6); celda.setCellValue(rs.getString("iva")); celda = fila.createCell(7); celda.setCellValue(rs.getString("total")); i = i + 1; } break;//cotizacion case "vencimientosFacturas": hoja = libro.createSheet("Vencimiento Facturas"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("DOCUMENTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("NUMERO IDENTIFICACION CLIENTE"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("CLIENTE"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("TELEFONO"); celda.setCellStyle(cellStyle); celda = fila.createCell(4); celda.setCellValue("VALOR"); celda.setCellStyle(cellStyle); celda = fila.createCell(5); celda.setCellValue("SALDO"); celda.setCellStyle(cellStyle); celda = fila.createCell(6); celda.setCellValue("FECHA VENCIMIENTO"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(rs.getString("documento")); celda = fila.createCell(1); celda.setCellValue(rs.getString("numDoc")); celda = fila.createCell(2); celda.setCellValue(rs.getString("cliente")); celda = fila.createCell(3); celda.setCellValue(rs.getString("tel1")); celda = fila.createCell(4); celda.setCellValue(rs.getDouble("valor")); celda = fila.createCell(5); celda.setCellValue(rs.getDouble("saldo")); celda = fila.createCell(6); celda.setCellValue(sd2.format(rs.getDate("fecha_limite"))); i = i + 1; } break;//vencimientofactuas case "vencimientosSeparados": hoja = libro.createSheet("Vencimiento Separados"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("DOCUMENTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("NUMERO IDENTIFICACION CLIENTE"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("CLIENTE"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("TELEFONO"); celda.setCellStyle(cellStyle); celda = fila.createCell(4); celda.setCellValue("VALOR"); celda.setCellStyle(cellStyle); celda = fila.createCell(5); celda.setCellValue("SALDO"); celda.setCellStyle(cellStyle); celda = fila.createCell(6); celda.setCellValue("FECHA VENCIMIENTO"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(rs.getString("documento")); celda = fila.createCell(1); celda.setCellValue(rs.getString("numDoc")); celda = fila.createCell(2); celda.setCellValue(rs.getString("cliente")); celda = fila.createCell(3); celda.setCellValue(rs.getString("tel1")); celda = fila.createCell(4); celda.setCellValue(rs.getDouble("valor")); celda = fila.createCell(5); celda.setCellValue(rs.getDouble("saldo")); celda = fila.createCell(6); celda.setCellValue(sd2.format(rs.getDate("fecha_limite"))); i = i + 1; } break;//vencimientosSeprados case "productosSinRotacion": hoja = libro.createSheet("productos sin rotacin"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("CATEGORIA"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("REFERENCIA"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("MARCA"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("CODIGO INTERNO"); celda.setCellStyle(cellStyle); celda = fila.createCell(4); celda.setCellValue("PRODUCTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(5); celda.setCellValue("FECHA ULTIMO MOVIMIENTO"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(rs.getString("nombreCategoria")); celda = fila.createCell(1); celda.setCellValue(rs.getString("nombreReferencia")); celda = fila.createCell(2); celda.setCellValue(rs.getString("nombreMarca")); celda = fila.createCell(3); celda.setCellValue(rs.getString("codigoInterno")); celda = fila.createCell(4); celda.setCellValue(rs.getString("nomProducto")); celda = fila.createCell(5); celda.setCellValue(sd2.format(rs.getDate("fechaultimo"))); i = i + 1; } break;//ProductosSinRotacion } /*Escribimos en el libro*/ libro.write(archi); /*Cerramos el flujo de datos*/ archi.close(); /*DESCARGAMOS EL ARCHIVO */ File f; f = new File(rutaArchivo); int bit; InputStream in; ServletOutputStream out; response.setContentType("application/vnd.ms-excel"); //Tipo de fichero. response.setHeader("Content-Disposition", "attachment;filename=\"" + nombreArchivo + "\""); //Configurar cabecera http in = new FileInputStream(f); out = response.getOutputStream(); bit = 256; while ((bit) >= 0) { bit = in.read(); out.write(bit); } out.flush(); out.close(); in.close(); } catch (Exception e) { e.printStackTrace(); } finally { try { if (rs != null) { rs.close(); } if (ps != null) { ps.close(); } if (con != null) { con.close(); } DBConnector.getInstance().closeConnection(); } catch (Exception e) { } } }
From source file:temp1.ExportExcel.java
public void export() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet hs = wb.createSheet();/* ww w . j a va 2 s . c o m*/ TableModel tm = table.getModel(); int row = tm.getRowCount(); int cloumn = tm.getColumnCount(); HSSFCellStyle style = wb.createCellStyle(); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 11); style.setFont(font); HSSFCellStyle style1 = wb.createCellStyle(); style1.setBorderBottom(HSSFCellStyle.BORDER_THIN); style1.setBorderLeft(HSSFCellStyle.BORDER_THIN); style1.setBorderRight(HSSFCellStyle.BORDER_THIN); style1.setBorderTop(HSSFCellStyle.BORDER_THIN); style1.setFillForegroundColor(HSSFColor.ORANGE.index); style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font1 = wb.createFont(); font1.setFontHeightInPoints((short) 15); font1.setBoldweight((short) 700); style1.setFont(font); for (int i = 0; i < row + 1; i++) { HSSFRow hr = hs.createRow(i); for (int j = 0; j < cloumn; j++) { if (i == 0) { String value = tm.getColumnName(j); int len = value.length(); hs.setColumnWidth((short) j, (short) (len * 400)); HSSFRichTextString srts = new HSSFRichTextString(value); HSSFCell hc = hr.createCell((short) j); hc.setEncoding((short) 1); hc.setCellStyle(style1); hc.setCellValue(srts); } else { System.out.println("vlue " + tm.getValueAt(i - 1, j)); if (tm.getValueAt(i - 1, j) != null) { String value = tm.getValueAt(i - 1, j).toString(); HSSFRichTextString srts = new HSSFRichTextString(value); HSSFCell hc = hr.createCell((short) j); hc.setEncoding((short) 1); hc.setCellStyle(style); if (value.equals("") || value == null) { hc.setCellValue(new HSSFRichTextString("")); } else { hc.setCellValue(srts); } } } } } try { wb.write(fos); fos.close(); } catch (IOException ex) { ex.printStackTrace(); } }
From source file:tw.edu.chit.struts.action.course.ReportPrintAction.java
/** * ?/* w w w. j a va 2 s. c om*/ * * @param mapping org.apache.struts.action.ActionMapping object * @param form org.apache.struts.action.ActionForm object * @param request request javax.servlet.http.HttpServletRequest object * @param response response javax.servlet.http.HttpServletResponse object * @param sterm */ @SuppressWarnings("unchecked") private void printCalculate(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { HttpSession session = request.getSession(false); AdminManager am = (AdminManager) getBean(ADMIN_MANAGER_BEAN_NAME); MemberManager mm = (MemberManager) getBean(IConstants.MEMBER_MANAGER_BEAN_NAME); CourseManager cm = (CourseManager) getBean(IConstants.COURSE_MANAGER_BEAN_NAME); ScoreManager sm = (ScoreManager) getBean(IConstants.SCORE_MANAGER_BEAN_NAME); ServletContext context = request.getSession().getServletContext(); String thisYear = cm.getSchoolYear().toString(); String thisTerm = am.findTermBy(PARAMETER_SCHOOL_TERM); List<Clazz> clazzes = sm.findClassBy(new Clazz(processClassInfo(form)), getUserCredential(session).getClassInChargeAry(), false); int thisTermCounts = 0, lastTermCounts = 0; String departClass = null, deptCode = null, histDeptCode = null, currentDeptCode = null, chiName = null; ScoreHist scoreHist = null; List<Student> students = null; List<ScoreHist> scoreHistList = null; List<Map> seldInfo = null; List csnos = null; if (!clazzes.isEmpty()) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("?"); sheet.setColumnWidth(0, 3000); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 5000); sheet.setColumnWidth(3, 5000); sheet.setColumnWidth(4, 5000); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4)); HSSFFont fontSize16 = workbook.createFont(); fontSize16.setFontHeightInPoints((short) 16); fontSize16.setFontName("Arial Unicode MS"); HSSFFont fontSize10 = workbook.createFont(); fontSize10.setFontHeightInPoints((short) 10); fontSize10.setFontName("Arial Unicode MS"); // Header Toolket.setCellValue(workbook, sheet, 0, 0, "?", fontSize16, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null); // Column Header Toolket.setCellValue(workbook, sheet, 1, 0, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 1, "??", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 2, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 3, "??", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 4, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); int index = 2; for (Clazz clazz : clazzes) { departClass = clazz.getClassNo(); deptCode = StringUtils.substring(departClass, 3, 4); if (Toolket.isDelayClass(departClass) || Toolket.isLiteracyClass(departClass)) continue; students = mm.findStudentsByClassNo(departClass); if (!students.isEmpty()) { if (thisYear.equals(request.getParameter("year")) && thisTerm.equals(sterm)) { // (Seld) for (Student student : students) { seldInfo = cm.findStudentSeldCourse(student.getStudentNo(), sterm); if (!seldInfo.isEmpty()) { for (Map m : seldInfo) { currentDeptCode = StringUtils.substring((String) m.get("depart_class"), 3, 4); if (!deptCode.equalsIgnoreCase(currentDeptCode) && !Toolket.isLiteracyClass((String) m.get("depart_class"))) { thisTermCounts++; Toolket.setCellValue(workbook, sheet, index, 0, student.getStudentNo(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 1, student.getStudentName(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 2, Toolket.getClassFullName(student.getDepartClass()), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 3, Toolket.getClassFullName((String) m.get("depart_class")), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index++, 4, (String) m.get("chi_name"), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); break; } } } } } else { // ?(ScoreHist) for (Student student : students) { scoreHist = new ScoreHist(student.getStudentNo()); scoreHist.setSchoolYear((short) Integer.parseInt(request.getParameter("year"))); scoreHist.setSchoolTerm(sterm); scoreHistList = sm.findScoreHistBy(scoreHist); HIST: { if (!scoreHistList.isEmpty()) { for (ScoreHist hist : scoreHistList) { if (StringUtils.isNotBlank(hist.getStdepartClass()) && !Toolket.isLiteracyClass(hist.getStdepartClass())) { histDeptCode = StringUtils.substring(hist.getStdepartClass(), 3, 4); if (!deptCode.equalsIgnoreCase(histDeptCode)) { lastTermCounts++; Toolket.setCellValue(workbook, sheet, index, 0, student.getStudentNo(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 1, student.getStudentName(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 2, Toolket.getClassFullName(student.getDepartClass()), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 3, Toolket.getClassFullName(hist.getStdepartClass()), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); csnos = cm.getCsnameBy(hist.getCscode()); if (!csnos.isEmpty()) chiName = ((Csno) csnos.get(0)).getChiName(); else chiName = ""; Toolket.setCellValue(workbook, sheet, index++, 4, chiName, fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); break HIST; } } } } } } } } } File tempDir = new File( context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno() + (new SimpleDateFormat("yyyyMMdd").format(new Date())))); if (!tempDir.exists()) tempDir.mkdirs(); File output = new File(tempDir, "Calculate.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); System.out.println("This Term: " + thisTermCounts); System.out.println("Last Term: " + lastTermCounts); } }
From source file:tw.edu.chit.struts.action.course.ReportPrintAction.java
/** * /* ww w . j a v a 2 s . c o m*/ * * @param mapping * @param form * @param request * @param response * @param sterm * @throws Exception */ @SuppressWarnings("unchecked") private void printStayTimePrint(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { HttpSession session = request.getSession(false); MemberManager mm = (MemberManager) getBean(IConstants.MEMBER_MANAGER_BEAN_NAME); CourseManager cm = (CourseManager) getBean(IConstants.COURSE_MANAGER_BEAN_NAME); ScoreManager sm = (ScoreManager) getBean(IConstants.SCORE_MANAGER_BEAN_NAME); ServletContext context = request.getSession().getServletContext(); Integer year = cm.getSchoolYear(); String term = form.getString("sterm"); List<Clazz> clazzes = sm.findClassBy(new Clazz(processClassInfo(form)), getUserCredential(session).getClassInChargeAry(), true); if (!clazzes.isEmpty()) { File templateXLS = new File(context.getRealPath("/WEB-INF/reports/TeachSchedAll.xls")); HSSFWorkbook workbook = Toolket.getHSSFWorkbook(templateXLS); HSSFFont fontSize12 = workbook.createFont(); fontSize12.setFontHeightInPoints((short) 12); fontSize12.setFontName("Arial Unicode MS"); HSSFSheet sheet = null; int sheetIndex = 0, colOffset = 1, col = 0; boolean isLocationNull = false; String departClass = null; Dtime dtime = null; Empl empl = null; Set<String> idnoSet = new HashSet<String>(); Short colorForStayTime = HSSFColor.AUTOMATIC.index; Short colorForLifeCounseling = HSSFColor.LIGHT_GREEN.index; List<TeacherStayTime> tsts = null; List<LifeCounseling> lcs = null; List<Dtime> dtimes = null; List<Map> map = null; Map content = null; for (Clazz clazz : clazzes) { departClass = clazz.getClassNo(); dtime = new Dtime(); dtime.setDepartClass(departClass); dtime.setSterm(sterm); dtimes = cm.findDtimeBy(dtime, "cscode"); if (!dtimes.isEmpty()) { for (Dtime d : dtimes) { if (StringUtils.isNotBlank(d.getTechid())) idnoSet.add(d.getTechid()); } } } for (String idno : idnoSet) { empl = mm.findEmplByIdno(idno); if (empl != null && "1".equalsIgnoreCase(empl.getCategory())) { sheet = workbook.getSheetAt(sheetIndex); workbook.setSheetName(sheetIndex++, empl.getCname()); isLocationNull = empl.getLocation() == null; Toolket.setCellValue(sheet, 0, 1, year + "" + term + "" + empl.getCname() + "?" + " (:" + (isLocationNull ? "" : StringUtils.defaultIfEmpty(empl.getLocation().getExtension(), "")) + " ?:" + (isLocationNull ? "" : StringUtils.defaultIfEmpty(empl.getLocation().getRoomId(), "")) + ")"); map = cm.findCourseByTeacherTermWeekdaySched(empl.getIdno(), term.toString()); for (int i = 0; i < 14; i++) { for (int j = 0; j < 7; j++) { content = map.get(j * 15 + i); if (!CollectionUtils.isEmpty(content)) { Toolket.setCellValue(sheet, i + 2, j + 2, (String) content.get("ClassName") + "\n" + (String) content.get("chi_name") + "\n" + (String) content.get("place")); } } } List<TeacherStayTime> myTsts = cm.ezGetBy( " Select Week, Node1, Node2, Node3, Node4, Node5, Node6, Node7, Node8, Node9, Node10, " + " Node11, Node12, Node13, Node14 " + " From TeacherStayTime " + " Where SchoolYear='" + year + "'" + " And SchoolTerm='" + term + "' " + " And parentOid='" + empl.getOid() + "'"); List myTsts2 = new ArrayList(); //int colOffset = 1, col = 0; for (int i = 0; i < myTsts.size(); i++) { //for (TeacherStayTime tst : tsts) { myTsts2.add(myTsts.get(i)); String s = myTsts2.get(i).toString(); col = Integer.parseInt(s.substring(6, 7)) + colOffset; //Week //if (tst.getNode1() != null && tst.getNode1() == 1) { if (Integer.parseInt(s.substring(15, 16)) == 1) { //Node1 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 2, col))) Toolket.setCellValue(workbook, sheet, 2, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (Integer.parseInt(s.substring(24, 25)) == 1) { //Node2 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 3, col))) Toolket.setCellValue(workbook, sheet, 3, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (Integer.parseInt(s.substring(33, 34)) == 1) { //Node3 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 4, col))) Toolket.setCellValue(workbook, sheet, 4, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (Integer.parseInt(s.substring(42, 43)) == 1) { //Node4 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 5, col))) Toolket.setCellValue(workbook, sheet, 5, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (Integer.parseInt(s.substring(51, 52)) == 1) { //Node5 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 6, col))) Toolket.setCellValue(workbook, sheet, 6, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (Integer.parseInt(s.substring(60, 61)) == 1) { //Node6 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 7, col))) Toolket.setCellValue(workbook, sheet, 7, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (Integer.parseInt(s.substring(69, 70)) == 1) { //Node7 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 8, col))) Toolket.setCellValue(workbook, sheet, 8, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (Integer.parseInt(s.substring(78, 79)) == 1) { //Node8 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 9, col))) Toolket.setCellValue(workbook, sheet, 9, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (Integer.parseInt(s.substring(87, 88)) == 1) { //Node9 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 10, col))) Toolket.setCellValue(workbook, sheet, 10, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (Integer.parseInt(s.substring(97, 98)) == 1) { //Node10 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 11, col))) Toolket.setCellValue(workbook, sheet, 11, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (Integer.parseInt(s.substring(107, 108)) == 1) { //Node11 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 12, col))) Toolket.setCellValue(workbook, sheet, 12, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (Integer.parseInt(s.substring(117, 118)) == 1) { //Node12 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 13, col))) Toolket.setCellValue(workbook, sheet, 13, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (Integer.parseInt(s.substring(127, 128)) == 1) { //Node13 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 14, col))) Toolket.setCellValue(workbook, sheet, 14, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (Integer.parseInt(s.substring(137, 138)) == 1) { //Node14 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 15, col))) Toolket.setCellValue(workbook, sheet, 15, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } } //List<LifeCounseling> lcs = empl.getLifeCounseling(); List<LifeCounseling> myLcs = cm.ezGetBy( " Select Week, Node1, Node2, Node3, Node4, Node5, Node6, Node7, Node8, Node9, Node10, " + " Node11, Node12, Node13, Node14 " + " From LifeCounseling Where ParentOid='" + empl.getOid() + "'"); List myLcs2 = new ArrayList(); colOffset = 1; col = 0; //for (LifeCounseling lc : lcs) { for (int y = 0; y < myLcs.size(); y++) { myLcs2.add(myLcs.get(y)); String st = myLcs2.get(y).toString(); col = Integer.parseInt(st.substring(6, 7)) + colOffset; //col = lc.getWeek() + colOffset; //if (lc.getNode1() != null && lc.getNode1() == 1) { if (Integer.parseInt(st.substring(15, 16)) == 1) { //Node1 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 2, col))) Toolket.setCellValue(workbook, sheet, 2, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode2() != null && lc.getNode2() == 1) { if (Integer.parseInt(st.substring(24, 25)) == 1) { //Node2 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 3, col))) Toolket.setCellValue(workbook, sheet, 3, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode3() != null && lc.getNode3() == 1) { if (Integer.parseInt(st.substring(33, 34)) == 1) { //Node3 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 4, col))) Toolket.setCellValue(workbook, sheet, 4, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode4() != null && lc.getNode4() == 1) { if (Integer.parseInt(st.substring(42, 43)) == 1) { //Node4 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 5, col))) Toolket.setCellValue(workbook, sheet, 5, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode5() != null && lc.getNode5() == 1) { if (Integer.parseInt(st.substring(51, 52)) == 1) { //Node5 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 6, col))) Toolket.setCellValue(workbook, sheet, 6, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode6() != null && lc.getNode6() == 1) { if (Integer.parseInt(st.substring(60, 61)) == 1) { //Node6 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 7, col))) Toolket.setCellValue(workbook, sheet, 7, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode7() != null && lc.getNode7() == 1) { if (Integer.parseInt(st.substring(69, 70)) == 1) { //Node7 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 8, col))) Toolket.setCellValue(workbook, sheet, 8, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode8() != null && lc.getNode8() == 1) { if (Integer.parseInt(st.substring(78, 79)) == 1) { //Node8 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 9, col))) Toolket.setCellValue(workbook, sheet, 9, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode9() != null && lc.getNode9() == 1) { if (Integer.parseInt(st.substring(87, 88)) == 1) { //Node9 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 10, col))) Toolket.setCellValue(workbook, sheet, 10, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode10() != null && lc.getNode10() == 1) { if (Integer.parseInt(st.substring(97, 98)) == 1) { //Node10 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 11, col))) Toolket.setCellValue(workbook, sheet, 11, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode11() != null && lc.getNode11() == 1) { if (Integer.parseInt(st.substring(107, 108)) == 1) { //Node11 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 12, col))) Toolket.setCellValue(workbook, sheet, 12, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode12() != null && lc.getNode12() == 1) { if (Integer.parseInt(st.substring(117, 118)) == 1) { //Node12 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 13, col))) Toolket.setCellValue(workbook, sheet, 13, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode13() != null && lc.getNode13() == 1) { if (Integer.parseInt(st.substring(127, 128)) == 1) { //Node13 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 14, col))) Toolket.setCellValue(workbook, sheet, 14, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode14() != null && lc.getNode14() == 1) { if (Integer.parseInt(st.substring(137, 138)) == 1) { //Node14 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 15, col))) Toolket.setCellValue(workbook, sheet, 15, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } } } } //===================================>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> /* tsts = empl.getStayTime(); for (TeacherStayTime tst : tsts) { col = tst.getWeek() + colOffset; if (tst.getNode1() != null && tst.getNode1() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 2, col))) Toolket.setCellValue(workbook, sheet, 2, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (tst.getNode2() != null && tst.getNode2() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 3, col))) Toolket.setCellValue(workbook, sheet, 3, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (tst.getNode3() != null && tst.getNode3() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 4, col))) Toolket.setCellValue(workbook, sheet, 4, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (tst.getNode4() != null && tst.getNode4() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 5, col))) Toolket.setCellValue(workbook, sheet, 5, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (tst.getNode5() != null && tst.getNode5() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 6, col))) Toolket.setCellValue(workbook, sheet, 6, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (tst.getNode6() != null && tst.getNode6() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 7, col))) Toolket.setCellValue(workbook, sheet, 7, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (tst.getNode7() != null && tst.getNode7() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 8, col))) Toolket.setCellValue(workbook, sheet, 8, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (tst.getNode8() != null && tst.getNode8() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 9, col))) Toolket.setCellValue(workbook, sheet, 9, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (tst.getNode9() != null && tst.getNode9() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 10, col))) Toolket.setCellValue(workbook, sheet, 10, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (tst.getNode10() != null && tst.getNode10() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 11, col))) Toolket.setCellValue(workbook, sheet, 11, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (tst.getNode11() != null && tst.getNode11() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 12, col))) Toolket.setCellValue(workbook, sheet, 12, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (tst.getNode12() != null && tst.getNode12() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 13, col))) Toolket.setCellValue(workbook, sheet, 13, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (tst.getNode13() != null && tst.getNode13() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 14, col))) Toolket.setCellValue(workbook, sheet, 14, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } if (tst.getNode14() != null && tst.getNode14() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 15, col))) Toolket.setCellValue(workbook, sheet, 15, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } } lcs = empl.getLifeCounseling(); colOffset = 1; col = 0; for (LifeCounseling lc : lcs) { col = lc.getWeek() + colOffset; if (lc.getNode1() != null && lc.getNode1() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 2, col))) Toolket.setCellValue(workbook, sheet, 2, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } if (lc.getNode2() != null && lc.getNode2() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 3, col))) Toolket.setCellValue(workbook, sheet, 3, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } if (lc.getNode3() != null && lc.getNode3() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 4, col))) Toolket.setCellValue(workbook, sheet, 4, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } if (lc.getNode4() != null && lc.getNode4() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 5, col))) Toolket.setCellValue(workbook, sheet, 5, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } if (lc.getNode5() != null && lc.getNode5() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 6, col))) Toolket.setCellValue(workbook, sheet, 6, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } if (lc.getNode6() != null && lc.getNode6() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 7, col))) Toolket.setCellValue(workbook, sheet, 7, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } if (lc.getNode7() != null && lc.getNode7() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 8, col))) Toolket.setCellValue(workbook, sheet, 8, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } if (lc.getNode8() != null && lc.getNode8() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 9, col))) Toolket.setCellValue(workbook, sheet, 9, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } if (lc.getNode9() != null && lc.getNode9() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 10, col))) Toolket.setCellValue(workbook, sheet, 10, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } if (lc.getNode10() != null && lc.getNode10() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 11, col))) Toolket.setCellValue(workbook, sheet, 11, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } if (lc.getNode11() != null && lc.getNode11() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 12, col))) Toolket.setCellValue(workbook, sheet, 12, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } if (lc.getNode12() != null && lc.getNode12() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 13, col))) Toolket.setCellValue(workbook, sheet, 13, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } if (lc.getNode13() != null && lc.getNode13() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 14, col))) Toolket.setCellValue(workbook, sheet, 14, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } if (lc.getNode14() != null && lc.getNode14() == 1) { if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 15, col))) Toolket.setCellValue(workbook, sheet, 15, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } } } */ //=================================>>>>>>>>>>>>>>>>>>>>> File tempDir = new File( context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno() + (new SimpleDateFormat("yyyyMMdd").format(new Date())))); if (!tempDir.exists()) tempDir.mkdirs(); File output = new File(tempDir, "StayTimeList.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); } }
From source file:tw.edu.chit.struts.action.course.ReportPrintAction.java
/** * //w w w .ja va2 s . c o m * * @param mapping org.apache.struts.action.ActionMapping object * @param form org.apache.struts.action.ActionForm object * @param request request javax.servlet.http.HttpServletRequest object * @param response response javax.servlet.http.HttpServletResponse object * @param sterm */ private void printIdnoCheckErrorStudentsList(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { HttpSession session = request.getSession(false); MemberManager mm = (MemberManager) getBean(IConstants.MEMBER_MANAGER_BEAN_NAME); ScoreManager sm = (ScoreManager) getBean(IConstants.SCORE_MANAGER_BEAN_NAME); ServletContext context = request.getSession().getServletContext(); List<Clazz> clazzes = sm.findClassBy(new Clazz(processClassInfo(form)), getUserCredential(session).getClassInChargeAry(), false); if (!clazzes.isEmpty()) { List<Student> students = null; HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("?"); sheet.setColumnWidth(0, 3000); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 5000); sheet.setColumnWidth(3, 3500); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3)); HSSFFont fontSize16 = workbook.createFont(); fontSize16.setFontHeightInPoints((short) 16); fontSize16.setFontName("Arial Unicode MS"); HSSFFont fontSize12 = workbook.createFont(); fontSize12.setFontHeightInPoints((short) 12); fontSize12.setFontName("Arial Unicode MS"); // Header Toolket.setCellValue(workbook, sheet, 0, 0, "?", fontSize16, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null); // Column Header Toolket.setCellValue(workbook, sheet, 1, 0, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 1, "??", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 2, "?", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 3, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); int index = 2; for (Clazz clazz : clazzes) { if (Toolket.isDelayClass(clazz.getClassNo())) continue; students = mm.findStudentsByClassNo(clazz.getClassNo()); if (!students.isEmpty()) { for (Student student : students) { if (!Toolket.checkIdno(student.getIdno())) { Toolket.setCellValue(workbook, sheet, index, 0, student.getStudentNo(), fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 1, student.getStudentName(), fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 2, Toolket.getClassFullName(student.getDepartClass()), null, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index++, 3, student.getIdno(), null, HSSFCellStyle.ALIGN_CENTER, true, null); } } } } File tempDir = new File( context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno() + (new SimpleDateFormat("yyyyMMdd").format(new Date())))); if (!tempDir.exists()) tempDir.mkdirs(); File output = new File(tempDir, "RegisterList.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); } else { Map<String, String> param = new HashMap<String, String>(); File image = new File(context.getRealPath("/pages/images/2002chitS.jpg")); param.put("IMAGE", image.getAbsolutePath()); byte[] bytes = JasperRunManager.runReportToPdf(JasperReportUtils.getNoResultReport(context), param, new JREmptyDataSource()); JasperReportUtils.printPdfToFrontEnd(response, bytes); } }
From source file:tw.edu.chit.struts.action.course.ReportPrintAction.java
/** * ?/* w w w . j a va2 s .c om*/ * * @param mapping org.apache.struts.action.ActionMapping object * @param form org.apache.struts.action.ActionForm object * @param request request javax.servlet.http.HttpServletRequest object * @param response response javax.servlet.http.HttpServletResponse object * @param sterm */ @SuppressWarnings("unchecked") private void printSyllabusCheck(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { HttpSession session = request.getSession(false); AdminManager am = (AdminManager) getBean(IConstants.ADMIN_MANAGER_BEAN_NAME); CourseManager cm = (CourseManager) getBean(IConstants.COURSE_MANAGER_BEAN_NAME); MemberManager mm = (MemberManager) getBean(IConstants.MEMBER_MANAGER_BEAN_NAME); ScoreManager sm = (ScoreManager) getBean(IConstants.SCORE_MANAGER_BEAN_NAME); ServletContext context = request.getSession().getServletContext(); String year = am.findTermBy(IConstants.PARAMETER_SCHOOL_YEAR); // ??? List<Clazz> clazzes = sm.findClassBy(new Clazz(processClassInfo(form)), getUserCredential(session).getClassInChargeAry(), true); String hql = "SELECT COUNT(*) FROM Dtime d WHERE d.techid != '' AND d.techid IS NOT NULL " + "AND d.sterm = ? AND d.departClass LIKE ?"; List<Object> count = (List<Object>) am.find(hql, new Object[] { sterm, processClassInfo(form) + "%" }); if (!clazzes.isEmpty()) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("?"); sheet.setColumnWidth(0, 3000); sheet.setColumnWidth(1, 5500); sheet.setColumnWidth(2, 3000); sheet.setColumnWidth(3, 8000); sheet.setColumnWidth(4, 2000); sheet.setColumnWidth(5, 5000); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); HSSFFont fontSize16 = workbook.createFont(); fontSize16.setFontHeightInPoints((short) 16); fontSize16.setFontName("Arial Unicode MS"); HSSFFont fontSize12 = workbook.createFont(); fontSize12.setFontHeightInPoints((short) 12); fontSize12.setFontName("Arial Unicode MS"); // Header Toolket.setCellValue(workbook, sheet, 0, 0, year + "" + sterm + "?", fontSize16, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null); // Column Header Toolket.setCellValue(workbook, sheet, 1, 0, "?", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 1, "???", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 2, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 3, "??", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 4, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 5, "??", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); int index = 2; List<Object> maps = null; Dtime dtime = null; Csno csno = null; CourseSyllabus cs = null, target = null; Empl empl = null; String[] excluded = { "50000", "T0001", "T0002" }; target = new CourseSyllabus(); target.setSchoolYear(Integer.parseInt(year)); target.setSchoolTerm(Integer.parseInt(sterm)); for (Clazz clazz : clazzes) { if (Toolket.isDelayClass(clazz.getClassNo())) continue; //maps = cm.findDtimeCsnoBy(new Dtime(clazz.getClassNo(), sterm), //"cscode"); if (maps != null) { for (Object o : maps) { dtime = (Dtime) ((Object[]) o)[0]; csno = (Csno) ((Object[]) o)[1]; // ? if (!ArrayUtils.contains(excluded, csno.getCscode()) && !ArrayUtils.contains(IConstants.COURSE_SYLLABUS_INTRO, dtime.getCscode()) && StringUtils.isNotBlank(dtime.getTechid().trim())) { target.setDepartClass(clazz.getClassNo()); target.setCscode(csno.getCscode()); cs = cm.findCourseSyllabusBy1(target); if (cs == null) { empl = mm.findEmplByIdno(dtime.getTechid()); Toolket.setCellValue(workbook, sheet, index, 0, clazz.getClassNo(), fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 1, Toolket.getClassFullName(clazz.getClassNo()), fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 2, csno.getCscode(), fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 3, csno.getChiName().trim(), fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 4, dtime.getCredit().toString(), fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index++, 5, (empl == null ? "" : empl.getCname().trim()), fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); } else { cs = null; } } dtime = null; csno = null; } } } int xx = index - 2; // ? int yy = (Integer) count.get(0); // ? index++; sheet.addMergedRegion(new CellRangeAddress(index, index, 3, 5)); Toolket.setCellValue(workbook, sheet, index++, 3, " : " + xx + " / " + yy + " = " + ((float) xx / (float) yy), fontSize16, HSSFCellStyle.ALIGN_RIGHT, false, null); File tempDir = new File( context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno() + (new SimpleDateFormat("yyyyMMdd").format(new Date())))); if (!tempDir.exists()) tempDir.mkdirs(); File output = new File(tempDir, "RegisterList.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); } else { Map<String, String> param = new HashMap<String, String>(); File image = new File(context.getRealPath("/pages/images/2002chitS.jpg")); param.put("IMAGE", image.getAbsolutePath()); byte[] bytes = JasperRunManager.runReportToPdf(JasperReportUtils.getNoResultReport(context), param, new JREmptyDataSource()); JasperReportUtils.printPdfToFrontEnd(response, bytes); } }
From source file:tw.edu.chit.struts.action.course.ReportPrintAction.java
/** * /*ww w . ja va2s.c o m*/ * * @param mapping org.apache.struts.action.ActionMapping object * @param form org.apache.struts.action.ActionForm object * @param request request javax.servlet.http.HttpServletRequest object * @param response response javax.servlet.http.HttpServletResponse object * @param sterm */ private void printIntroCheck(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { HttpSession session = request.getSession(false); AdminManager am = (AdminManager) getBean(IConstants.ADMIN_MANAGER_BEAN_NAME); CourseManager cm = (CourseManager) getBean(IConstants.COURSE_MANAGER_BEAN_NAME); MemberManager mm = (MemberManager) getBean(IConstants.MEMBER_MANAGER_BEAN_NAME); ScoreManager sm = (ScoreManager) getBean(IConstants.SCORE_MANAGER_BEAN_NAME); ServletContext context = request.getSession().getServletContext(); String year = am.findTermBy(IConstants.PARAMETER_SCHOOL_YEAR); // ??? List<Clazz> clazzes = sm.findClassBy(new Clazz(processClassInfo(form)), getUserCredential(session).getClassInChargeAry(), true); if (!clazzes.isEmpty()) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(""); sheet.setColumnWidth(0, 3000); sheet.setColumnWidth(1, 5000); sheet.setColumnWidth(2, 3000); sheet.setColumnWidth(3, 8000); sheet.setColumnWidth(4, 2000); sheet.setColumnWidth(5, 5000); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); HSSFFont fontSize16 = workbook.createFont(); fontSize16.setFontHeightInPoints((short) 16); fontSize16.setFontName("Arial Unicode MS"); HSSFFont fontSize12 = workbook.createFont(); fontSize12.setFontHeightInPoints((short) 12); fontSize12.setFontName("Arial Unicode MS"); // Header Toolket.setCellValue(workbook, sheet, 0, 0, year + "" + sterm + "", fontSize16, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null); // Column Header Toolket.setCellValue(workbook, sheet, 1, 0, "?", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 1, "???", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 2, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 3, "??", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 4, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 5, "??", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); int index = 2; List<Object> maps = null; Dtime dtime = null; Csno csno = null; CourseIntroduction ci = null; Empl empl = null; String[] excluded = { "50000", "T0001", "T0002" }; for (Clazz clazz : clazzes) { if (Toolket.isDelayClass(clazz.getClassNo())) continue; //maps = cm.findDtimeCsnoBy(new Dtime(clazz.getClassNo(), sterm), //"cscode"); if (maps != null) { for (Object o : maps) { dtime = (Dtime) ((Object[]) o)[0]; csno = (Csno) ((Object[]) o)[1]; // ? if (!ArrayUtils.contains(excluded, csno.getCscode()) && !ArrayUtils.contains(IConstants.COURSE_SYLLABUS_INTRO, dtime.getCscode()) && StringUtils.isNotBlank(dtime.getTechid().trim())) { ci = cm.getCourseIntrosByDtimeOid(dtime.getOid(), Integer.parseInt(year), Integer.parseInt(sterm)); if (ci == null) { empl = mm.findEmplByIdno(dtime.getTechid()); Toolket.setCellValue(workbook, sheet, index, 0, clazz.getClassNo(), null, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 1, Toolket.getClassFullName(clazz.getClassNo()), null, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 2, csno.getCscode(), null, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 3, csno.getChiName().trim(), null, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 4, dtime.getCredit().toString(), null, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index++, 5, (empl == null ? "" : empl.getCname().trim()), null, HSSFCellStyle.ALIGN_CENTER, true, null); } else { ci = null; } } dtime = null; csno = null; } } } File tempDir = new File( context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno() + (new SimpleDateFormat("yyyyMMdd").format(new Date())))); if (!tempDir.exists()) tempDir.mkdirs(); File output = new File(tempDir, "RegisterList.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); } else { Map<String, String> param = new HashMap<String, String>(); File image = new File(context.getRealPath("/pages/images/2002chitS.jpg")); param.put("IMAGE", image.getAbsolutePath()); byte[] bytes = JasperRunManager.runReportToPdf(JasperReportUtils.getNoResultReport(context), param, new JREmptyDataSource()); JasperReportUtils.printPdfToFrontEnd(response, bytes); } }