List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getRow
@Override public HSSFRow getRow(int rowIndex)
From source file:LoadExcels.loadViralLoad.java
@Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {/* w w w . j a v a 2s .c o m*/ session = request.getSession(); dbConn conn = new dbConn(); nextpage = "loadTBExcel.jsp"; //--------------------------------------------------------------------- String numerator_un, denominator_un; String fun_less1, fun_1to4, fun_5to14, fun_15to19, fun_20; String mun_less1, mun_1to4, mun_5to14, mun_15to19, mun_20; String subtotal_un, numerator_vi, denominator_vi; String fvi_less1, fvi_1to4, fvi_5to14, fvi_15to19, fvi_20; String mvi_less1, mvi_1to4, mvi_5to14, mvi_15to19, mvi_20, subtotal_vi; //--------------------------------------------------------------------- numerator_un = denominator_un = ""; fun_less1 = fun_1to4 = fun_5to14 = fun_15to19 = fun_20 = ""; mun_less1 = mun_1to4 = mun_5to14 = mun_15to19 = mun_20 = ""; subtotal_un = numerator_vi = denominator_vi = ""; fvi_less1 = fvi_1to4 = fvi_5to14 = fvi_15to19 = fvi_20 = ""; mvi_less1 = mvi_1to4 = mvi_5to14 = mvi_15to19 = mvi_20 = subtotal_vi = ""; String applicationPath = request.getServletContext().getRealPath(""); String uploadFilePath = applicationPath + File.separator + UPLOAD_DIR; session = request.getSession(); File fileSaveDir = new File(uploadFilePath); if (!fileSaveDir.exists()) { fileSaveDir.mkdirs(); } System.out.println("Upload File Directory=" + fileSaveDir.getAbsolutePath()); for (Part part : request.getParts()) { fileName = getFileName(part); part.write(uploadFilePath + File.separator + fileName); System.out.println("file name is : " + fileName); } if (!fileName.endsWith(".xls")) { nextpage = "loadExcel.jsp"; session.setAttribute("upload_success", "<font color=\"red\">Failed to load the excel file. Please choose the correct File.</font>"); } else { full_path = fileSaveDir.getAbsolutePath() + "\\" + fileName; System.out.println("the saved file directory is : " + full_path); // GET DATA FROM THE EXCEL AND AND OUTPUT IT ON THE CONSOLE.................................. FileInputStream fileInputStream = new FileInputStream(full_path); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet worksheet = workbook.getSheetAt(0); Iterator rowIterator = worksheet.iterator(); int i = 2, y = 0; while (rowIterator.hasNext()) { System.out.println(" in while"); HSSFRow rowi = worksheet.getRow(i); if (rowi == null) { nextpage = "loadViralLoad.jsp"; break; } HSSFCell cellYear = rowi.getCell((short) 0); year = (int) cellYear.getNumericCellValue(); HSSFCell cellQuarter = rowi.getCell((short) 1); quarterName = cellQuarter.getStringCellValue(); HSSFCell cellFacilityName = rowi.getCell((short) 4); facilityName = cellFacilityName.getStringCellValue(); HSSFCell cellMFLCODE = rowi.getCell((short) 5); mflcode = cellMFLCODE.getStringCellValue(); HSSFCell cellstype = rowi.getCell((short) 6); String supporttype = cellstype.getStringCellValue(); HSSFCell cellNumerator = rowi.getCell((short) 7); numerator_un = "" + (int) cellNumerator.getNumericCellValue(); HSSFCell cellDenominator = rowi.getCell((short) 8); denominator_un = "" + (int) cellDenominator.getNumericCellValue(); HSSFCell less1funcell = rowi.getCell((short) 9); fun_less1 = "" + (int) less1funcell.getNumericCellValue(); HSSFCell cl10 = rowi.getCell((short) 10); fun_1to4 = "" + (int) cl10.getNumericCellValue(); HSSFCell cl11 = rowi.getCell((short) 11); fun_5to14 = "" + (int) cl11.getNumericCellValue(); HSSFCell cl12 = rowi.getCell((short) 12); fun_15to19 = "" + (int) cl12.getNumericCellValue(); HSSFCell cl13 = rowi.getCell((short) 13); fun_20 = "" + (int) cl13.getNumericCellValue(); HSSFCell cl14 = rowi.getCell((short) 14); mun_less1 = "" + (int) cl14.getNumericCellValue(); HSSFCell cl15 = rowi.getCell((short) 15); mun_1to4 = "" + (int) cl15.getNumericCellValue(); HSSFCell cl16 = rowi.getCell((short) 16); mun_5to14 = "" + (int) cl16.getNumericCellValue(); HSSFCell cl17 = rowi.getCell((short) 17); mun_15to19 = "" + (int) cl17.getNumericCellValue(); HSSFCell cl18 = rowi.getCell((short) 18); mun_20 = "" + (int) cl18.getNumericCellValue(); HSSFCell cl19 = rowi.getCell((short) 19); subtotal_un = "" + (int) cl19.getNumericCellValue(); HSSFCell cl20 = rowi.getCell((short) 20); numerator_vi = "" + (int) cl20.getNumericCellValue(); HSSFCell cl21 = rowi.getCell((short) 21); denominator_vi = "" + (int) cl21.getNumericCellValue(); HSSFCell cl22 = rowi.getCell((short) 22); fvi_less1 = "" + (int) cl22.getNumericCellValue(); HSSFCell cl23 = rowi.getCell((short) 23); fvi_1to4 = "" + (int) cl23.getNumericCellValue(); HSSFCell cl24 = rowi.getCell((short) 24); fvi_5to14 = "" + (int) cl24.getNumericCellValue(); HSSFCell cl25 = rowi.getCell((short) 25); fvi_15to19 = "" + (int) cl25.getNumericCellValue(); HSSFCell cl26 = rowi.getCell((short) 26); fvi_20 = "" + (int) cl26.getNumericCellValue(); HSSFCell cl27 = rowi.getCell((short) 27); mvi_less1 = "" + (int) cl27.getNumericCellValue(); HSSFCell cl27a = rowi.getCell((short) 28); mvi_1to4 = "" + (int) cl27a.getNumericCellValue(); HSSFCell cl28 = rowi.getCell((short) 29); mvi_5to14 = "" + (int) cl28.getNumericCellValue(); HSSFCell cl29 = rowi.getCell((short) 30); mvi_15to19 = "" + (int) cl29.getNumericCellValue(); // HSSFCell cl30 = rowi.getCell((short) 31); mvi_20 = "" + (int) cl30.getNumericCellValue(); HSSFCell cl31 = rowi.getCell((short) 32); subtotal_vi = "" + (int) cl31.getNumericCellValue(); // int female_stat,male_stat; //int less1, stat_1to4,stat_5to9,stat_10to14,stat_15to19,stat_20above,positive,negative; // //int art_numerator, art_denominator, art_female; //int art_male,art_less1,art_1to4,art_5to9,art_10to14,art_15to19,art_20above; facilityID = ""; checker = 0; String get_id = "SELECT SubPartnerID FROM subpartnera WHERE CentreSanteId=?"; conn.pst = conn.conn.prepareStatement(get_id); conn.pst.setString(1, mflcode); conn.rs = conn.pst.executeQuery(); if (conn.rs.next() == true) { facilityID = conn.rs.getString(1); } if (facilityID.length() > 0) { // DISTRICT FOUND ADD THE HF TO THE SYSTEM......................... String getQuarterID = "SELECT id FROM quarter WHERE pmtct_fo_name=?"; conn.pst = conn.conn.prepareStatement(getQuarterID); conn.pst.setString(1, quarterName); conn.rs = conn.pst.executeQuery(); if (conn.rs.next() == true) { quarter = conn.rs.getInt(1); } checker = 0; // CHECK IF ALREADY ADDED TO PMTCT_FO TABLE id = year + "_" + quarter + "_" + facilityID; // System.out.println("to add data : "+facilityName+" id : "+facilityID+"mfl code "+mflcode+" year : "+year+" quarter : "+quarter+" numerator : "+Numerator+" denominator : "+Denominator); String checkerExisting = "SELECT id FROM viral_load WHERE id='" + id + "'"; conn.rs = conn.st.executeQuery(checkerExisting); if (conn.rs.next() == true) { checker++; } // // // if (checker == 0) { System.out.println("INSERT >> " + numerator_un); String inserter = "INSERT INTO viral_load (id,SubPartnerID,year,quarter,numerator_un ,denominator_un,less1_fun,1to4_fun,5to14_fun,15to19_fun,20_fun,less1_mun,1to4_mun,5to14_mun,15to19_mun,20_mun,subtotal_un,numerator_vi,denominator_vi,less1_fvi,1to4_fvi ,5to14_fvi,15to19_fvi,20_fvi,less1_mvi,1to4_mvi,5to14_mvi,15to19_mvi,20_mvi ,subtotal_vi,supporttype) " + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; conn.pst = conn.conn.prepareStatement(inserter); conn.pst.setString(1, id); conn.pst.setString(2, facilityID); conn.pst.setInt(3, year); conn.pst.setInt(4, quarter); conn.pst.setString(5, numerator_un); conn.pst.setString(6, denominator_un); conn.pst.setString(7, fun_less1); conn.pst.setString(8, fun_1to4); conn.pst.setString(9, fun_5to14); conn.pst.setString(10, fun_15to19); conn.pst.setString(11, fun_20); conn.pst.setString(12, mun_less1); conn.pst.setString(13, mun_1to4); conn.pst.setString(14, mun_5to14); conn.pst.setString(15, mun_15to19); conn.pst.setString(16, mun_20); conn.pst.setString(17, subtotal_un); conn.pst.setString(18, numerator_vi); conn.pst.setString(19, denominator_vi); conn.pst.setString(20, fvi_less1); conn.pst.setString(21, fvi_1to4); conn.pst.setString(22, fvi_5to14); conn.pst.setString(23, fvi_15to19); conn.pst.setString(24, fvi_20); conn.pst.setString(25, mvi_less1); conn.pst.setString(26, mvi_1to4); conn.pst.setString(27, mvi_5to14); conn.pst.setString(28, mvi_15to19); conn.pst.setString(29, mvi_20); conn.pst.setString(30, subtotal_vi); conn.pst.setString(31, supporttype); conn.pst.executeUpdate(); added++; } else { String inserter = "UPDATE viral_load SET SubPartnerID=?,year=?,quarter=?,numerator_un =?,denominator_un=?,less1_fun=?,1to4_fun=?,5to14_fun=?,15to19_fun=?,20_fun=?,less1_mun=?,1to4_mun=?,5to14_mun=?,15to19_mun=?,20_mun=?,subtotal_un=?,numerator_vi=?,denominator_vi=?,less1_fvi=?,1to4_fvi=? ,5to14_fvi=?,15to19_fvi=?,20_fvi=?,less1_mvi=?,1to4_mvi=?,5to14_mvi=?,15to19_mvi=?,20_mvi=?,subtotal_vi=?,supporttype=?" + "WHERE id=?"; conn.pst = conn.conn.prepareStatement(inserter); conn.pst.setString(1, facilityID); conn.pst.setInt(2, year); conn.pst.setInt(3, quarter); conn.pst.setString(4, numerator_un); conn.pst.setString(5, denominator_un); conn.pst.setString(6, fun_less1); conn.pst.setString(7, fun_1to4); conn.pst.setString(8, fun_5to14); conn.pst.setString(9, fun_15to19); conn.pst.setString(10, fun_20); conn.pst.setString(11, mun_less1); conn.pst.setString(12, mun_1to4); conn.pst.setString(13, mun_5to14); conn.pst.setString(14, mun_15to19); conn.pst.setString(15, mun_20); conn.pst.setString(16, subtotal_un); conn.pst.setString(17, numerator_vi); conn.pst.setString(18, denominator_vi); conn.pst.setString(19, fvi_less1); conn.pst.setString(20, fvi_1to4); conn.pst.setString(21, fvi_5to14); conn.pst.setString(22, fvi_15to19); conn.pst.setString(23, fvi_20); conn.pst.setString(24, mvi_less1); conn.pst.setString(25, mvi_1to4); conn.pst.setString(26, mvi_5to14); conn.pst.setString(27, mvi_15to19); conn.pst.setString(28, mvi_20); conn.pst.setString(29, subtotal_vi); conn.pst.setString(30, supporttype); conn.pst.setString(31, id); conn.pst.executeUpdate(); updated++; } } else { missing++; // missing facilities missingFacility += "facility name : " + facilityName + " mfl code : " + mflcode + " excel row num : " + i + "<br>"; System.out.println(facilityName + "facility is missing mflcode :" + mflcode); } i++; } } } catch (SQLException ex) { Logger.getLogger(loadTBExcel.class.getName()).log(Level.SEVERE, null, ex); } String sessionText = added + "New data added <> " + updated + " updated facilities<br> and " + missing + " missing facilities"; response.sendRedirect(nextpage); }
From source file:LoadExcels.Load_tb_raw.java
@Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String serialnumber = ""; String distregno = ""; String agebracket = ""; String registrationdate = ""; String registrationdatecopy = ""; int age = 0;// w w w . j a va2 s . c o m String agestring = ""; String sex = "F"; String treatmentstartdate = ""; String hivtestdate = ""; String hivstatus = ""; String artstatus = ""; String artdate = ""; String treatmentoutcome = ""; String treatmentoutcomedate = ""; int ageinteger = 0; String supporttype = ""; String datacategory = ""; String dbname = "tibu_tb_raw"; String tbtype = ""; String patienttype = ""; String smear0 = ""; String smear2_3 = ""; String smear5 = ""; String smear6_8 = ""; String genexpert = ""; // if(request.getParameter("datacategory")!=null){ // datacategory=request.getParameter("datacategory"); // System.out.println(" data category fetched"); // } if (datacategory.equals("completeoutcome")) { //this is the data for monthly uploads dbname = "tibu_tb_raw_outcome"; } try { session = request.getSession(); dbConn conn = new dbConn(); nextpage = "sync_tb.jsp"; //--------------------------------------------------------------------- String applicationPath = request.getServletContext().getRealPath(""); String uploadFilePath = applicationPath + File.separator + UPLOAD_DIR; session = request.getSession(); File fileSaveDir = new File(uploadFilePath); if (!fileSaveDir.exists()) { fileSaveDir.mkdirs(); } System.out.println("Upload File Directory=" + fileSaveDir.getAbsolutePath()); for (Part part : request.getParts()) { fileName = getFileName(part); part.write(uploadFilePath + File.separator + fileName); System.out.println("file name is : " + fileName); } if (!fileName.endsWith(".xls")) { nextpage = "upload_tb_raw_data.jsp"; session.setAttribute("upload_success", "<font color=\"red\">Failed to load the excel file. Please choose the correct File.</font>"); } else { full_path = fileSaveDir.getAbsolutePath() + "\\" + fileName; System.out.println("the saved file directory is : " + full_path); // GET DATA FROM THE EXCEL AND AND OUTPUT IT ON THE CONSOLE.................................. FileInputStream fileInputStream = new FileInputStream(full_path); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet worksheet = workbook.getSheetAt(0); Iterator rowIterator = worksheet.iterator(); int i = 1, y = 0; while (rowIterator.hasNext()) { // System.out.println(" in while"); HSSFRow rowi = worksheet.getRow(i); if (rowi == null) { nextpage = "sync_tb.jsp"; break; } //([0])Serial Number ([1])Date of Registration ([2])District Registration Number (3)Province (4)County (5)District (6)Zone ([7])Health Facility ([8])Year ([9])Quarter (10)Sector (11)Patient Name ([12])Sex M/F ([13])Age on registration (14)Weight (Kgs) (15)Height (Mtrs) (16)BMI (17)MUAC (18)Physical address (Neighbor,Primary School) Cell Phone (19)DOT by (20)Type of TB P/EP (21)EPTB Sub Type (22)EPTB Others (23)Type of patient (24)CD4 First Date (25)CD4 Last Date (26)Culture S (27)Culture R (28)Culture E (29)Culture H (30)X-ray Y/N (31)Sputum Smear Examination (32)0th Month Result (33)0th Month Serial No and Quantification (34)Sputum Smear Examination 2by3 Month Result (35)2by3 Month Serial No and Quantification (36)Sputum Smear Examination 5th Month Result (37)5th Month Serial No and Quantification (38)Sputum Smear Examination 6by8 Month Result (39)6by8 Month Serial No and Quantification (40)Regimen ([41])Date of treatment started (42)Gen expert (43)Lipa Hain Rifampicin (44)Lipa Hain Isoniazid ([45])HIV Test Date ([46])HIV Status (47)Partner HIV Test Date (48)Partner HIV Status (49)Referred BY: VCT/HCC/STI/HBC/PS/ANC/SR/CI (50)Referred TO VCT/HCC/STI/HBC/PS/ANC (51)Cotrimoxazole Preventive Therapy Y/N (52)Cotrimoxazole Preventive Therapy (Date Started) ([53])ART Y/N ([54])ART (Date Started) (55)Nutrition Support ([56])Treatment Outcome ([57])Treatment Outcome Date ([58])Remarks //<tr><td>(0)Serial Number</td><td>(1)Date of Registration</td><td>(2)District Registration Number</td><td>(3)Province</td><td>(4) County</td><td>(5)District</td><td>(6)Zone</td><td>(7)Health Facility</td><td>(8)Year</td><td>(9)Quarter</td><td>(10)Sector</td><td>(11)Patient Name</td><td>(12)Sex M/F</td><td>(13)Age on registration</td><td>(14)Weight (Kgs)</td><td>(15)Height (Mtrs)</td><td>(16)BMI</td><td>(17)MUAC</td><td>(18)Physical address (Neighbor,Primary School) Cell Phone</td><td>(19)DOT by</td><td>(20)Type of TB P/EP</td><td>(21)EPTB Sub Type</td><td>(22) EPTB Others</td><td>(23)Type of patient </td><td>(24)CD4 First Date</td><td>(25)CD4 Last Date</td><td>(26)Culture S</td><td>(27)Culture R</td><td>(28)Culture E </td><td>(29)Culture H</td><td>(30)X-ray Y/N</td><td>(31)Sputum Smear Examination</td><td>(32) 0th Month Result</td><td>(33)0th Month Serial No and Quantification</td><td>(34)Sputum Smear Examination 2by3 Month Result</td><td>(35)2by3 Month Serial No and Quantification</td><td>(36)Sputum Smear Examination 5th Month Result</td><td>(37)5th Month Serial No and Quantification</td><td>(38)Sputum Smear Examination 6by8 Month Result</td><td>(39)6by8 Month Serial No and Quantification</td><td>(40)Regimen</td><td>(41)Date of treatment started</td><td>(42)Gen expert</td><td>(43)Lipa Hain Rifampicin</td><td>(44)Lipa Hain Isoniazid</td><td>(45)HIV Test Date</td><td>(46)HIV Status</td><td>(47)Partner HIV Test Date</td><td>(48)Partner HIV Status</td><td>(49)Referred BY: VCT/HCC/STI/HBC/PS/ANC/SR/CI</td><td>(50)Referred TO VCT/HCC/STI/HBC/PS/ANC</td><td>(51)Cotrimoxazole Preventive Therapy Y/N</td><td>(52)Cotrimoxazole Preventive Therapy (Date Started)</td><td>(53)ART Y/N</td><td>(54)ART (Date Started)</td><td>(55)Nutrition Support</td><td>(56)Treatment Outcome</td><td>(57)Treatment Outcome Date</td><td>(58)Remarks</td></tr> //______________________________________________________________________ //([0])Serial Number //([1])Date of Registration //([2])District Registration Number //([4])County //([5])District //([7])Health Facility //([8])Year //([9])Quarter //([12])Sex M/F //([13])Age on registration //([40])Date of treatment started //([44])HIV Test Date //([45])HIV Status //([52])ART Y/N //([53])ART (Date Started) //([55])Treatment Outcome //([56])Treatment Outcome Date //([20])Type of TB P/EP //([23])Type of patient //([31])Sputum Smear Examination 0th Month Result //([33])Sputum Smear Examination 2by3 Month Result //([35])Sputum Smear Examination 5th Month Result //([37])Sputum Smear Examination 6by8 Month Result //([41])Gen expert //tbtype //patienttype //smear0 //smear2_3 //smear5 //smear6_8 //genexpert //______________________________________________________________________ HSSFCell cellserialno = rowi.getCell((short) 0); if (cellserialno.getCellType() == 0) { //numeric serialnumber = "" + (int) cellserialno.getNumericCellValue(); } else if (cellserialno.getCellType() == 1) { serialnumber = cellserialno.getStringCellValue(); } { } HSSFCell cellregdate = rowi.getCell((short) 1); // System.out.println("CELLTYPE IS "+cellregdate.getCellType()); if (cellregdate.getCellType() == 1) { //this is a string registrationdate = (String) cellregdate.getStringCellValue(); } else if (cellregdate.getCellType() == 0) { //this is a numeric value registrationdate = "" + (int) cellregdate.getNumericCellValue(); } else { registrationdate = "" + cellregdate.getDateCellValue(); } // System.out.println("Reg Date "+registrationdate); //District Reg number HSSFCell celldistreg = rowi.getCell((short) 2); if (celldistreg.getCellType() == 0) { //numeric distregno = "" + (int) celldistreg.getNumericCellValue(); } else if (celldistreg.getCellType() == 1) { distregno = celldistreg.getStringCellValue(); } { } //dont save county and subcounty directly since they may change HSSFCell cellcounty = rowi.getCell((short) 4); county_name = cellcounty.getStringCellValue(); HSSFCell cellsubcounty = rowi.getCell((short) 5); district_name = cellsubcounty.getStringCellValue(); HSSFCell cellfacil = rowi.getCell((short) 7); facilityName = cellfacil.getStringCellValue(); //HSSFCell cellmfl = rowi.getCell((short) 7); //mflcode = (int) cellmfl.getNumericCellValue(); HSSFCell cellsex = rowi.getCell((short) 12); if (cellsex.getCellType() == 1) { //string sex = (String) cellsex.getStringCellValue(); } else { //numeric sex = "" + (int) cellsex.getNumericCellValue(); } HSSFCell agestringcell = rowi.getCell((short) 13); if (agestringcell.getCellType() == 1) { //string agestring = (String) agestringcell.getStringCellValue(); } else { //numeric agestring = "" + (int) agestringcell.getNumericCellValue(); } if (agestring.contains("M") && agestring.contains("Y")) { //age is in months String agearray[] = agestring.split(" "); //System.out.println("agestring "+agestring+" _ "+age); age = ((new Double(agearray[0].trim().replace("Y", ""))).intValue()) + (int) Math.round((new Double(agearray[1].replace("M", "")) / 12)); System.out.println(" agestring M&Y " + agestring + " _ " + age); } else if (agestring.contains("Y") && !agestring.contains("M")) { //age is in years age = (new Double(agestring.trim().replace("Y", ""))).intValue(); System.out.println(" agestring Y Only " + agestring + " _ " + age); } else if (agestring.contains("M") && !agestring.contains("Y")) { //age is in months //System.out.println("agestring "+agestring+" _ "+age); age = (int) Math.round((new Double(agestring.trim().replace("M", "")) / 12)); // System.out.println("agestring "+agestring+" _ "+age); System.out.println(" agestring M Only " + agestring + " _ " + age); } ageinteger = age; agebracket = getageBracket(ageinteger); //tbtype HSSFCell celltbtype = rowi.getCell((short) 20); if (celltbtype.getCellType() == 1) { //this is a string tbtype = (String) celltbtype.getStringCellValue(); } else if (celltbtype.getCellType() == 0) { //this is a numeric value tbtype = "" + (int) celltbtype.getNumericCellValue(); } else { tbtype = "" + celltbtype.getDateCellValue(); } //type of patient HSSFCell cellpatienttype = rowi.getCell((short) 23); if (cellpatienttype.getCellType() == 1) { //this is a string patienttype = (String) cellpatienttype.getStringCellValue(); } else if (cellpatienttype.getCellType() == 0) { //this is a numeric value patienttype = "" + (int) cellpatienttype.getNumericCellValue(); } else { patienttype = "" + cellpatienttype.getDateCellValue(); } //------------ //smear0 HSSFCell cellsmear0 = rowi.getCell((short) 31); if (cellsmear0.getCellType() == 1) { //this is a string smear0 = (String) cellsmear0.getStringCellValue(); } else if (cellsmear0.getCellType() == 0) { //this is a numeric value smear0 = "" + (int) cellsmear0.getNumericCellValue(); } else { smear0 = "" + cellsmear0.getDateCellValue(); } //------------ HSSFCell cellsmear2_3 = rowi.getCell((short) 33); if (cellsmear2_3.getCellType() == 1) { //this is a string smear2_3 = (String) cellsmear0.getStringCellValue(); } else if (cellsmear2_3.getCellType() == 0) { //this is a numeric value smear2_3 = "" + (int) cellsmear2_3.getNumericCellValue(); } else { smear2_3 = "" + cellsmear0.getDateCellValue(); } //------------smear5------ HSSFCell cellsmear5 = rowi.getCell((short) 35); if (cellsmear5.getCellType() == 1) { //this is a string smear5 = (String) cellsmear0.getStringCellValue(); } else if (cellsmear5.getCellType() == 0) { //this is a numeric value smear5 = "" + (int) cellsmear5.getNumericCellValue(); } else { smear5 = "" + cellsmear5.getDateCellValue(); } //------------ //------------smear5------ HSSFCell cellsmear6_8 = rowi.getCell((short) 37); if (cellsmear6_8.getCellType() == 1) { //this is a string smear6_8 = (String) cellsmear0.getStringCellValue(); } else if (cellsmear6_8.getCellType() == 0) { //this is a numeric value smear6_8 = "" + (int) cellsmear6_8.getNumericCellValue(); } else { smear6_8 = "" + cellsmear6_8.getDateCellValue(); } //------------------- HSSFCell celltreatmentdate = rowi.getCell((short) 40); if (cellregdate.getCellType() == 1) { //this is a string treatmentstartdate = (String) celltreatmentdate.getStringCellValue(); } else if (cellregdate.getCellType() == 0) { //this is a numeric value treatmentstartdate = "" + (int) celltreatmentdate.getNumericCellValue(); } else { treatmentstartdate = "" + celltreatmentdate.getDateCellValue(); } //------------smear5------ HSSFCell cellgenexpert = rowi.getCell((short) 41); if (cellgenexpert.getCellType() == 1) { //this is a string genexpert = (String) cellgenexpert.getStringCellValue(); } else if (cellgenexpert.getCellType() == 0) { //this is a numeric value genexpert = "" + (int) cellgenexpert.getNumericCellValue(); } else { genexpert = "" + cellgenexpert.getDateCellValue(); } //------------------- //treatmentstartdate=""+celltreatmentdate.getDateCellValue(); //Format formatter = new SimpleDateFormat("MM/dd/yyyy"); //registrationdatecopy= new SimpleDateFormat("MM/dd/yyyy").format(registrationdate); // registrationdatecopy = formatter.format(registrationdatecopy); //hiv test date HSSFCell cellhivtestdate = rowi.getCell((short) 44); hivtestdate = "" + cellhivtestdate.getStringCellValue(); //hiv status HSSFCell cellhivstatus = rowi.getCell((short) 45); hivstatus = (String) cellhivstatus.getStringCellValue(); //art status HSSFCell cellartstatus = rowi.getCell((short) 52); artstatus = (String) cellartstatus.getStringCellValue(); //art date HSSFCell cellartdate = rowi.getCell((short) 53); artdate = "" + cellartdate.getStringCellValue(); //treatment outcome HSSFCell celltreatmentoutcome = rowi.getCell((short) 55); treatmentoutcome = (String) celltreatmentoutcome.getStringCellValue(); //treatment outcome date HSSFCell celltreatmentoutcomedate = rowi.getCell((short) 56); treatmentoutcomedate = "" + celltreatmentoutcomedate.getStringCellValue(); //split the date, year and month //raw date is of form dd mmm yyyy eg 08 Jul 2015 String dateparameters[] = registrationdate.split(" "); if (dateparameters.length == 3) { if (!dateparameters[0].equals("")) { String month = ""; month = dateparameters[1]; if (month.equalsIgnoreCase("Jan") || month.equalsIgnoreCase("Feb") || month.equalsIgnoreCase("Mar")) { quarterName = "January-March"; if (dateparameters[2].length() == 4) { year = Integer.parseInt(dateparameters[2]); } } else if (month.equalsIgnoreCase("Apr") || month.equalsIgnoreCase("May") || month.equalsIgnoreCase("Jun")) { quarterName = "April-June"; if (dateparameters[2].length() == 4) { year = Integer.parseInt(dateparameters[2]); } } else if (month.equalsIgnoreCase("Jul") || month.equalsIgnoreCase("Aug") || month.equalsIgnoreCase("Sep")) { quarterName = "July-September"; if (dateparameters[2].length() == 4) { year = Integer.parseInt(dateparameters[2]); } } else if (month.equalsIgnoreCase("Oct") || month.equalsIgnoreCase("Nov") || month.equalsIgnoreCase("Dec")) { quarterName = "October-December"; if (dateparameters[2].length() == 4) { //assume year = Integer.parseInt(dateparameters[2]) + 1; } } } } else { System.out.println("Error in date of testing _ :" + registrationdate); } // System.out.println("Quarter "+quarterName + "Year "+year); facilityID = ""; checker = 0; supporttype = "DSD"; String get_id = "SELECT SubPartnerID,ART_Support,CentreSanteId as mflcode,HTC_Support1,PMTCT_Support FROM subpartnera WHERE SubPartnerNom like ? or tibu_name like ?"; conn.pst = conn.conn.prepareStatement(get_id); conn.pst.setString(1, "%" + facilityName + "%"); conn.pst.setString(2, "%" + facilityName + "%"); conn.rs = conn.pst.executeQuery(); if (conn.rs.next() == true) { facilityID = conn.rs.getString(1); //supporttype=conn.rs.getString("ART_Support"); mflcode = conn.rs.getInt(3); //if(supporttype==null){supporttype=conn.rs.getString("HTC_Support1");} //if(supporttype==null){supporttype=conn.rs.getString("PMTCT_Support");} //if(supporttype==null){supporttype="";} } if (facilityID.length() > 0) { // DISTRICT FOUND ADD THE HF TO THE SYSTEM......................... String getQuarterID = "SELECT id FROM quarter WHERE pmtct_fo_name like ?"; conn.pst = conn.conn.prepareStatement(getQuarterID); conn.pst.setString(1, quarterName); conn.rs = conn.pst.executeQuery(); if (conn.rs.next() == true) { quarter = conn.rs.getInt(1); } checker = 0; // CHECK IF ALREADY ADDED TO PMTCT_FO TABLE id = serialnumber + "_" + registrationdate + "_" + distregno; // System.out.println("to add data : "+facilityName+" id : "+facilityID+"mfl code "+mflcode+" year : "+year+" quarter : "+quarter+" numerator : "+Numerator+" denominator : "+Denominator); String checkerExisting = "SELECT id FROM " + dbname + " WHERE id='" + id + "'"; conn.rs = conn.st.executeQuery(checkerExisting); if (conn.rs.next() == true) { checker++; } //id //SubPartnerID //Quarter //Year //Sex //Mflcode //age //agebracket //SubPartnerNom //dateoftesting //patientccc //batchno //suppression_status if (checker == 0) { String inserter = "INSERT INTO " + dbname + " (id,SubPartnerID,year,quarter,Mflcode,sex ,age,agebracket,SubPartnerNom,registrationdate,treatmentdate,supporttype,hivstatus,hivtestdate, " + " artstatus,artdate,outcomedate,treatmentoutcome,tbtype,patienttype,smear0,smear2_3,smear5,smear6_8,genexpert) " + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; conn.pst = conn.conn.prepareStatement(inserter); conn.pst.setString(1, id); conn.pst.setString(2, facilityID); conn.pst.setInt(3, year); conn.pst.setInt(4, quarter); conn.pst.setInt(5, mflcode); conn.pst.setString(6, sex); conn.pst.setInt(7, age); conn.pst.setString(8, agebracket); conn.pst.setString(9, facilityName); conn.pst.setString(10, registrationdate); conn.pst.setString(11, treatmentstartdate); conn.pst.setString(12, supporttype); conn.pst.setString(13, hivstatus); conn.pst.setString(14, hivtestdate); conn.pst.setString(15, artstatus); conn.pst.setString(16, artdate); conn.pst.setString(17, treatmentoutcomedate); conn.pst.setString(18, treatmentoutcome); conn.pst.setString(19, tbtype); conn.pst.setString(20, patienttype); conn.pst.setString(21, smear0); conn.pst.setString(22, smear2_3); conn.pst.setString(23, smear5); conn.pst.setString(24, smear6_8); conn.pst.setString(25, genexpert); conn.pst.executeUpdate(); added++; } else { //id,SubPartnerID,Year,Quarter,Mflcode,Sex ,age,agebracket,SubPartnerNom,dateoftesting,patientccc,batchno,supporttype String inserter = " UPDATE " + dbname + " SET SubPartnerID=?,year=?,quarter=?,Mflcode=?,sex=? ,age=?,agebracket=?,SubPartnerNom=?,registrationdate=?,treatmentdate=?,supporttype=?," + " hivstatus=?,hivtestdate=?, " + " artstatus=?,artdate=?,outcomedate=?,treatmentoutcome=? ,tbtype=?,patienttype=?,smear0=?,smear2_3=?,smear5=?,smear6_8=?,genexpert=?" + " WHERE id=?"; // conn.pst = conn.conn.prepareStatement(inserter); conn.pst.setString(1, facilityID); conn.pst.setInt(2, year); conn.pst.setInt(3, quarter); conn.pst.setInt(4, mflcode); conn.pst.setString(5, sex); conn.pst.setInt(6, age); conn.pst.setString(7, agebracket); conn.pst.setString(8, facilityName); conn.pst.setString(9, registrationdate); conn.pst.setString(10, treatmentoutcomedate); conn.pst.setString(11, supporttype); conn.pst.setString(12, hivstatus); conn.pst.setString(13, hivtestdate); conn.pst.setString(14, artstatus); conn.pst.setString(15, artdate); conn.pst.setString(16, treatmentoutcomedate); conn.pst.setString(17, treatmentoutcome); conn.pst.setString(18, tbtype); conn.pst.setString(19, patienttype); conn.pst.setString(20, smear0); conn.pst.setString(21, smear2_3); conn.pst.setString(22, smear5); conn.pst.setString(23, smear6_8); conn.pst.setString(24, genexpert); conn.pst.setString(25, id); conn.pst.executeUpdate(); updated++; } } else { missing++; // missing facilities missingFacility += "facility name : " + facilityName + " mfl code : " + mflcode + " excel row num : " + i + "<br>"; System.out.println(facilityName + "_missing"); } i++; } } if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.pst != null) { conn.pst.close(); } } catch (SQLException ex) { Logger.getLogger(Load_tb_raw.class.getName()).log(Level.SEVERE, null, ex); } String sessionText = "<br/><b> " + added + "</b> New data added <br/> <b> " + updated + "</b> updated facilities<br> <br> <b>" + missing + "</b> sites not in Imis Facilities List "; session.setAttribute("upload_success", sessionText); response.sendRedirect(nextpage); }
From source file:LoadExcels.Load_viral_load_raw.java
@Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String patientccc = ""; String suppression = ""; String testingdate = ""; String agebracket = ""; String dateoftesting = ""; int age = 0;//from ww w . j a v a2 s . co m String sex = ""; String batchno = ""; String supporttype = ""; int ageinteger = 0; try { session = request.getSession(); dbConn conn = new dbConn(); nextpage = "sync_viral_load.jsp"; //--------------------------------------------------------------------- String numerator_un, denominator_un; String fun_less1, fun_1to4, fun_5to14, fun_15to19, fun_20; String mun_less1, mun_1to4, mun_5to14, mun_15to19, mun_20; String subtotal_un, numerator_vi, denominator_vi; String fvi_less1, fvi_1to4, fvi_5to14, fvi_15to19, fvi_20; String mvi_less1, mvi_1to4, mvi_5to14, mvi_15to19, mvi_20, subtotal_vi; //--------------------------------------------------------------------- numerator_un = denominator_un = ""; fun_less1 = fun_1to4 = fun_5to14 = fun_15to19 = fun_20 = ""; mun_less1 = mun_1to4 = mun_5to14 = mun_15to19 = mun_20 = ""; subtotal_un = numerator_vi = denominator_vi = ""; fvi_less1 = fvi_1to4 = fvi_5to14 = fvi_15to19 = fvi_20 = ""; mvi_less1 = mvi_1to4 = mvi_5to14 = mvi_15to19 = mvi_20 = subtotal_vi = ""; String applicationPath = request.getServletContext().getRealPath(""); String uploadFilePath = applicationPath + File.separator + UPLOAD_DIR; session = request.getSession(); File fileSaveDir = new File(uploadFilePath); if (!fileSaveDir.exists()) { fileSaveDir.mkdirs(); } System.out.println("Upload File Directory=" + fileSaveDir.getAbsolutePath()); for (Part part : request.getParts()) { fileName = getFileName(part); part.write(uploadFilePath + File.separator + fileName); System.out.println("file name is : " + fileName); } if (!fileName.endsWith(".xls")) { nextpage = "sync_viral_load.jsp"; session.setAttribute("upload_success", "<font color=\"red\">Failed to load the excel file. Please choose the correct File.</font>"); } else { full_path = fileSaveDir.getAbsolutePath() + "\\" + fileName; System.out.println("the saved file directory is : " + full_path); // GET DATA FROM THE EXCEL AND AND OUTPUT IT ON THE CONSOLE.................................. FileInputStream fileInputStream = new FileInputStream(full_path); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet worksheet = workbook.getSheetAt(0); Iterator rowIterator = worksheet.iterator(); int i = 2, y = 0; while (rowIterator.hasNext()) { System.out.println(" in while"); HSSFRow rowi = worksheet.getRow(i); if (rowi == null) { nextpage = "sync_viral_load.jsp"; break; } //#(0) Batch No(1) Patient CCC No(2) Testing Lab(3) County (4) District(5) Facility Name(6) MFL Code(7) Sex(8) Age(9) Sample Type(10) Collection Date(11) Received Status(12) Reason for Repeat(13) Regimen (14) Justification (15) ART Initiation Date(16) Date of Receiving(17) Date of Testing(18) Date of Dispatch (19) Result(cp/ml) (20) Result(Log) (21) Suppressed? (22) //Patient CCC No(2) County (4) District(5) Facility Name(6) MFL Code(7) Sex(8) Age(9) Date of Testing(18) Suppressed? (22) HSSFCell cellbatcno = rowi.getCell((short) 1); if (cellbatcno.getCellType() == 0) { //numeric batchno = "" + (int) cellbatcno.getNumericCellValue(); } else if (cellbatcno.getCellType() == 1) { batchno = cellbatcno.getStringCellValue(); } { } HSSFCell cellpatienceno = rowi.getCell((short) 2); System.out.println("CELLTYPE IS " + cellpatienceno.getCellType()); if (cellpatienceno.getCellType() == 1) { //this is a string patientccc = (String) cellpatienceno.getStringCellValue(); } else if (cellpatienceno.getCellType() == 0) { //this is a numeric value patientccc = "" + (int) cellpatienceno.getNumericCellValue(); } else { patientccc = (String) cellpatienceno.getStringCellValue(); } //dont save county and subcounty directly since they may change HSSFCell cellcounty = rowi.getCell((short) 4); county_name = cellcounty.getStringCellValue(); HSSFCell cellsubcounty = rowi.getCell((short) 5); district_name = cellsubcounty.getStringCellValue(); HSSFCell cellfacil = rowi.getCell((short) 6); facilityName = cellfacil.getStringCellValue(); HSSFCell cellmfl = rowi.getCell((short) 7); if (cellmfl.getCellType() == 1) { //string mflcode = new Integer(cellmfl.getStringCellValue()); } else { //numeric mflcode = (int) cellmfl.getNumericCellValue(); } HSSFCell cellsex = rowi.getCell((short) 8); if (cellsex.getCellType() == 1) { //string sex = (String) cellsex.getStringCellValue(); } else { //numeric sex = "" + (int) cellsex.getNumericCellValue(); } HSSFCell cellage = rowi.getCell((short) 9); age = (int) cellage.getNumericCellValue(); ageinteger = age; agebracket = getageBracket(ageinteger); HSSFCell celldate = rowi.getCell((short) 18); dateoftesting = "" + celldate.getStringCellValue(); //Format formatter = new SimpleDateFormat("MM/dd/yyyy"); // dateoftesting= new SimpleDateFormat("MM/dd/yyyy").format(celldate.getDateCellValue()); //dateoftesting = formatter.format(dateoftesting); HSSFCell cellsuppression = rowi.getCell((short) 22); if (cellsuppression.getCellType() == 1) { //string suppression = cellsuppression.getStringCellValue(); } else { suppression = "" + (int) cellsuppression.getNumericCellValue(); } //split the date, year and month //raw date is of form m/d/yyyy String dateparameters[] = dateoftesting.split("-"); if (dateparameters.length == 3) { if (!dateparameters[0].equals("")) { String month = ""; month = dateparameters[1]; if (month.equals("01") || month.equals("02") || month.equals("03")) { quarterName = "January-March"; if (dateparameters[0].length() == 4) { year = Integer.parseInt(dateparameters[0]); } } else if (month.equals("04") || month.equals("05") || month.equals("06")) { quarterName = "April-June"; if (dateparameters[0].length() == 4) { year = Integer.parseInt(dateparameters[0]); } } else if (month.equals("07") || month.equals("08") || month.equals("09")) { quarterName = "July-September"; if (dateparameters[0].length() == 4) { year = Integer.parseInt(dateparameters[0]); } } else if (month.equals("10") || month.equals("11") || month.equals("12")) { quarterName = "October-December"; if (dateparameters[0].length() == 4) { //assume year = Integer.parseInt(dateparameters[0]) + 1; } } } } else { System.out.println("Error in date of testing _ :" + dateoftesting); } System.out.println("Quarter " + quarterName + "Year " + year); facilityID = ""; checker = 0; String get_id = "SELECT SubPartnerID,ART_Support FROM subpartnera WHERE CentreSanteId=?"; conn.pst = conn.conn.prepareStatement(get_id); conn.pst.setInt(1, mflcode); conn.rs = conn.pst.executeQuery(); if (conn.rs.next() == true) { facilityID = conn.rs.getString(1); supporttype = conn.rs.getString(2); if (supporttype == null) { supporttype = ""; } } if (facilityID.length() > 0 && !sex.equals("")) { // DISTRICT FOUND ADD THE HF TO THE SYSTEM......................... String getQuarterID = "SELECT id FROM quarter WHERE pmtct_fo_name like ?"; conn.pst = conn.conn.prepareStatement(getQuarterID); conn.pst.setString(1, quarterName); conn.rs = conn.pst.executeQuery(); if (conn.rs.next() == true) { quarter = conn.rs.getInt(1); } checker = 0; // CHECK IF ALREADY ADDED TO PMTCT_FO TABLE id = batchno + "_" + patientccc + "_" + dateoftesting; // System.out.println("to add data : "+facilityName+" id : "+facilityID+"mfl code "+mflcode+" year : "+year+" quarter : "+quarter+" numerator : "+Numerator+" denominator : "+Denominator); String checkerExisting = "SELECT id FROM viral_load_raw WHERE id='" + id + "'"; conn.rs = conn.st.executeQuery(checkerExisting); if (conn.rs.next() == true) { checker++; } //id //SubPartnerID //Quarter //Year //Sex //Mflcode //age //agebracket //SubPartnerNom //dateoftesting //patientccc //batchno //suppression_status if (checker == 0) { System.out.println("INSERT >> " + numerator_un); String inserter = "INSERT INTO viral_load_raw (id,SubPartnerID,Year,Quarter,Mflcode,Sex ,age,agebracket,SubPartnerNom,dateoftesting,patientccc,batchno,supporttype,suppression_status) " + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; conn.pst = conn.conn.prepareStatement(inserter); conn.pst.setString(1, id); conn.pst.setString(2, facilityID); conn.pst.setInt(3, year); conn.pst.setInt(4, quarter); conn.pst.setInt(5, mflcode); conn.pst.setString(6, sex); conn.pst.setInt(7, age); conn.pst.setString(8, agebracket); conn.pst.setString(9, facilityName); conn.pst.setString(10, dateoftesting); conn.pst.setString(11, patientccc); conn.pst.setString(12, batchno); conn.pst.setString(13, supporttype); conn.pst.setString(14, suppression); conn.pst.executeUpdate(); added++; } else { //id,SubPartnerID,Year,Quarter,Mflcode,Sex ,age,agebracket,SubPartnerNom,dateoftesting,patientccc,batchno,supporttype String inserter = " UPDATE viral_load_raw SET SubPartnerID=?,Year=?,Quarter=?,Mflcode =?,Sex=?,age=?,agebracket=?,SubPartnerNom=?,dateoftesting=?,patientccc=?,batchno=?,supporttype=?,suppression_status=? " + " WHERE id=?"; conn.pst = conn.conn.prepareStatement(inserter); conn.pst.setString(1, facilityID); conn.pst.setInt(2, year); conn.pst.setInt(3, quarter); conn.pst.setInt(4, mflcode); conn.pst.setString(5, sex); conn.pst.setInt(6, age); conn.pst.setString(7, agebracket); conn.pst.setString(8, facilityName); conn.pst.setString(9, dateoftesting); conn.pst.setString(10, patientccc); conn.pst.setString(11, batchno); conn.pst.setString(12, supporttype); conn.pst.setString(13, suppression); conn.pst.setString(14, id); conn.pst.executeUpdate(); updated++; } } else { missing++; // missing facilities missingFacility += "facility name : " + facilityName + " mfl code : " + mflcode + " excel row num : " + i + "<br>"; System.out.println(facilityName + "facility is missing mflcode on subpartner :" + mflcode); } i++; } } if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.pst != null) { conn.pst.close(); } } catch (SQLException ex) { Logger.getLogger(loadTBExcel.class.getName()).log(Level.SEVERE, null, ex); } String sessionText = "<br/><b> " + added + "</b> New data added <br/> <b> " + updated + "</b> updated facilities<br> <br> <b>" + missing + "</b> sites not in Imis Facilities List "; session.setAttribute("upload_success", sessionText); response.sendRedirect(nextpage); }
From source file:LogBeanConsultas.BeanConsultaRad.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0); HSSFRow header = sheet.getRow(0); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.AQUA.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { HSSFCell cell = header.getCell(i); cell.setCellStyle(cellStyle);//from w ww .jav a 2 s . c o m } }
From source file:model.SpecificReportDepartment.java
/** * *//* ww w . ja v a 2 s. co m*/ @Override public void generate() { try { @SuppressWarnings("resource") HSSFSheet sheet = workbook.createSheet("Casos " + town.getName() + " municipio"); int acum[]; HSSFRow fisrtRow = sheet.createRow(0); ResultSet result = db.executeSelect("select * from weekdata where id_event = " + event.getValue() + " and id_town = '" + town.getValue() + "' and id_department = '" + department.getValue() + "'" + "order by week,amount"); ResultSet resultYears = db.executeSelect("select distinct year_data from weekdata where id_event = " + event.getValue() + " and id_town = '" + town.getValue() + "' and id_department = '" + department.getValue() + "'" + "order by year_data"); years = years(resultYears); HashMap<Integer, Integer> indexYears = new HashMap<Integer, Integer>(); for (int i = 0; i < years.size(); i++) { indexYears.put(years.get(i), i); } int temp = 0; double value; int numberYears = years.size(); acum = new int[numberYears]; int maxWeek = (db.executeSelect( "select max(week) from weekdata where id_event = " + event.getValue() + " and id_town = '" + town.getValue() + "' and id_department = '" + department.getValue() + "'")).getInt(1); int columns = 1 + numberYears * 3 + 6; HashSet<Integer> discarted = new HashSet<>(); Map<String, Object> map = new HashMap<String, Object>(); map.put(CellUtil.BORDER_LEFT, CellStyle.BORDER_THIN); map.put(CellUtil.BORDER_RIGHT, CellStyle.BORDER_THIN); map.put(CellUtil.BORDER_BOTTOM, CellStyle.BORDER_THIN); map.put(CellUtil.BORDER_TOP, CellStyle.BORDER_THIN); firstLine(fisrtRow);//add first line report //create matrix report for (int i = 1; i <= maxWeek; i++) { sheet.createRow(i); sheet.getRow(i).createCell(0).setCellValue(i); for (int j = 1; j < columns; j++) { sheet.getRow(i).createCell(j); } } autoSize(sheet, columns); while (result.next()) { int week = result.getInt("week"); int year = result.getInt("year_data"); int amount = result.getInt("amount"); sheet.getRow(week).getCell(indexYears.get(year) + 1).setCellType(CellType.NUMERIC); sheet.getRow(week).getCell(indexYears.get(year) + 1).setCellValue(amount); acum[indexYears.get(year)] += amount; CellUtil.setCellStyleProperties(sheet.getRow(week).getCell(indexYears.get(year) + 1), map); } int count = 1; for (int i = 1; i <= maxWeek; i++) { HSSFRow a = sheet.getRow(i); a.getCell(1 + numberYears).setCellType(CellType.FORMULA); String next = nextColumn("B", numberYears - 1); a.getCell(1 + numberYears).setCellFormula("median(B" + (i + 1) + ":" + next + "" + (i + 1) + ")"); a.getCell(2 + numberYears) .setCellFormula("PERCENTILE(B" + (i + 1) + ":" + next + "" + (i + 1) + ",0.25)"); a.getCell(3 + numberYears) .setCellFormula("PERCENTILE(B" + (i + 1) + ":" + next + "" + (i + 1) + ",0.75)"); } /* to do total sum */ HSSFRow last = sheet.createRow(maxWeek + 2); createCell(last, "Total"); String col = "B"; for (int i = 0; i < years.size(); i++) { last.createCell(i + 1); last.getCell(i + 1).setCellFormula("SUM(" + col + "2" + ":" + col + "54" + ")"); col = nextColumn(col); } last.createCell(years.size() + 1).setCellFormula("median(B" + (maxWeek + 3) + ":" + nextColumn("B", years.size() - 1) + "" + (maxWeek + 3) + ")"); last.createCell(years.size() + 2).setCellFormula("PERCENTILE(B" + (maxWeek + 3) + ":" + nextColumn("B", years.size() - 1) + "" + (maxWeek + 3) + ",0.25)"); last.createCell(years.size() + 3).setCellFormula("PERCENTILE(B" + (maxWeek + 3) + ":" + nextColumn("B", years.size() - 1) + "" + (maxWeek + 3) + ",0.75)"); last.createCell(years.size() + 5).setCellFormula(nextColumn("B", years.size() + 2) + (maxWeek + 3) + "-" + nextColumn("B", years.size() + 1) + (maxWeek + 3)); last.createCell(years.size() + 6).setCellFormula("(" + nextColumn("B", years.size() + 4) + (maxWeek + 3) + "*3)+" + nextColumn("B", years.size() + 2) + (maxWeek + 3)); col = "B"; String v = nextColumn("B", years.size() + 6 - 1); for (int j = 4 + numberYears; j < 4 + numberYears * 2; j++) { last.createCell(j + numberYears + 3) .setCellFormula("IF(" + col + "" + (maxWeek + 3) + ">" + v + "" + (maxWeek + 3) + ",1,0)"); value = eval.evaluate(last.getCell(j + numberYears + 3)).getNumberValue(); col = nextColumn(col); if (value == 1) { discarted.add(j - (4 + numberYears)); } } for (int i = 1; i <= maxWeek; i++) { HSSFRow a = sheet.getRow(i); temp = 0; String next = nextColumn("B", numberYears - 1); if (count < months.length && i == months[count]) { if (months[count] == 52 && maxWeek == 53) { temp = 1; a = sheet.getRow(i + 1); } next = "A"; for (int j = 4 + numberYears; j < 4 + numberYears * 2; j++) { next = nextColumn(next, 1); int first = months[count] - (months[count] - months[count - 1]) + 2; a.getCell(j).setCellFormula("sum(" + next + "" + first + ":" + next + (i + 1 + temp) + ")"); } next = nextColumn("A", numberYears + 4); a.getCell(1 + numberYears * 2 + 3).setCellFormula("median(" + next + (i + 1 + temp) + ":" + nextColumn(next, numberYears - 1) + "" + (i + 1 + temp) + ")"); a.getCell(1 + numberYears * 2 + 4).setCellFormula("PERCENTILE(" + next + (i + 1 + temp) + ":" + nextColumn(next, numberYears - 1) + "" + (i + 1 + temp) + ",0.25)"); String percentile = ""; String initAddress = next; for (int k = 0; k < numberYears; k++) { if (!discarted.contains(k)) { if (!percentile.equals("")) { percentile += ":"; } percentile += initAddress + (i + 1 + temp) + ":" + initAddress + (i + 1 + temp); } initAddress = nextColumn(initAddress); } a.getCell(1 + numberYears * 2 + 5).setCellFormula("PERCENTILE(" + percentile + ",0.75)"); count++; next = nextColumn("A", numberYears + 3); for (int j = 4 + numberYears; j < 4 + numberYears * 2; j++) { next = nextColumn(next, 1); //System.out.println("SI(" + next + "" + (i + 1 + temp) + ">" + nextColumn("A", 4 + numberYears * 2) + "" + (i + 1 + temp) + ")"); a.getCell(j + numberYears + 3).setCellFormula("IF(" + next + "" + (i + 1 + temp) + ">" + nextColumn("A", 4 + numberYears * 2 + 2) + "" + (i + 1 + temp) + ",1,0)"); } } } eval.evaluateAll(); FileOutputStream out = new FileOutputStream(path); workbook.write(out); out.close(); } catch (SQLException ex) { Logger.getLogger(SpecificReportDepartment.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(SpecificReportDepartment.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:module.siadap.domain.SiadapRootModule.java
License:Open Source License
private void populateSheet(HSSFSheet sheetToWriteTo, boolean considerQuotas, UnitSiadapWrapper unitToSearchIn, HSSFWorkbook wb, boolean shouldIncludeEndOfRole, boolean includeHarmonizationResponsibles, boolean shouldIncludeUniverse) { CreationHelper creationHelper = wb.getCreationHelper(); // make the sheet fit the page PrintSetup ps = sheetToWriteTo.getPrintSetup(); sheetToWriteTo.setAutobreaks(true);/*from w ww.ja v a 2 s . com*/ ps.setFitHeight((short) 1); ps.setFitWidth((short) 1); /* ** styles ** */ // CostCenter style HSSFFont costCenterFont = wb.createFont(); costCenterFont.setColor(HSSFColor.DARK_BLUE.index); costCenterFont.setFontHeightInPoints((short) 12); costCenterFont.setBoldweight(Font.BOLDWEIGHT_BOLD); CellStyle costCenterStyle = wb.createCellStyle(); costCenterStyle.setFont(costCenterFont); // make the Unit header style CellStyle unitHeaderStyle = wb.createCellStyle(); unitHeaderStyle.setBorderBottom(CellStyle.BORDER_THIN); unitHeaderStyle.setBorderTop(CellStyle.BORDER_THIN); unitHeaderStyle.setBorderLeft(CellStyle.BORDER_THIN); unitHeaderStyle.setBorderRight(CellStyle.BORDER_THIN); unitHeaderStyle.setAlignment(CellStyle.ALIGN_CENTER); unitHeaderStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); HSSFFont headerFont = wb.createFont(); headerFont.setFontHeightInPoints((short) 12); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setItalic(true); unitHeaderStyle.setFont(headerFont); // make the default name style CellStyle defaultTextNameStyle = wb.createCellStyle(); defaultTextNameStyle.setBorderLeft(CellStyle.BORDER_THIN); defaultTextNameStyle.setBorderRight(CellStyle.BORDER_THIN); defaultTextNameStyle.setBorderBottom(CellStyle.BORDER_NONE); defaultTextNameStyle.setBorderTop(CellStyle.BORDER_NONE); defaultTextNameStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); HSSFFont defaultFont = wb.createFont(); defaultFont.setFontHeightInPoints((short) 11); defaultTextNameStyle.setFont(defaultFont); // make the last line name style CellStyle defaultTextNameLastStyle = wb.createCellStyle(); defaultTextNameLastStyle.setBorderLeft(CellStyle.BORDER_THIN); defaultTextNameLastStyle.setBorderRight(CellStyle.BORDER_THIN); defaultTextNameLastStyle.setBorderBottom(CellStyle.BORDER_THIN); defaultTextNameLastStyle.setBorderTop(CellStyle.BORDER_NONE); defaultTextNameLastStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); defaultTextNameLastStyle.setFont(defaultFont); // make the default IST-ID last line style CellStyle defaultTextIstIdLastStyle = wb.createCellStyle(); defaultTextIstIdLastStyle.setBorderLeft(CellStyle.BORDER_THIN); defaultTextIstIdLastStyle.setBorderBottom(CellStyle.BORDER_THIN); defaultTextIstIdLastStyle.setBorderTop(CellStyle.BORDER_NONE); defaultTextIstIdLastStyle.setBorderRight(CellStyle.BORDER_THIN); defaultTextIstIdLastStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); defaultTextIstIdLastStyle.setAlignment(CellStyle.ALIGN_CENTER); defaultTextIstIdLastStyle.setFont(defaultFont); // make the default IST-ID style CellStyle defaultTextIstIdStyle = wb.createCellStyle(); defaultTextIstIdStyle.setBorderLeft(CellStyle.BORDER_THIN); defaultTextIstIdStyle.setBorderBottom(CellStyle.BORDER_NONE); defaultTextIstIdStyle.setBorderTop(CellStyle.BORDER_NONE); defaultTextIstIdStyle.setBorderRight(CellStyle.BORDER_THIN); defaultTextIstIdStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); defaultTextIstIdStyle.setAlignment(CellStyle.ALIGN_CENTER); defaultTextIstIdStyle.setFont(defaultFont); // header style // CellStyle headerStyle = wb.createCellStyle(); // HSSFFont headerFont = wb.createFont(); // headerFont.setFontName(HSSFFont.FONT_ARIAL); // headerFont.setFontHeightInPoints((short) 10); // headerStyle.setFont(headerFont); // // first line style CellStyle firstLineStyle = wb.createCellStyle(); HSSFFont firstLineFont = wb.createFont(); firstLineFont.setColor(HSSFColor.DARK_BLUE.index); firstLineFont.setFontHeightInPoints((short) 14); firstLineFont.setBoldweight(Font.BOLDWEIGHT_BOLD); firstLineStyle.setFont(firstLineFont); firstLineStyle.setAlignment(CellStyle.ALIGN_CENTER); firstLineStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // second line style CellStyle secondLineStyle = wb.createCellStyle(); HSSFFont secondLineFont = wb.createFont(); secondLineFont.setBoldweight(Font.BOLDWEIGHT_BOLD); secondLineFont.setFontHeightInPoints((short) 14); secondLineStyle.setFont(secondLineFont); secondLineStyle.setAlignment(CellStyle.ALIGN_CENTER); secondLineStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // the style for Unit Harmonization responsibles - title CellStyle unitHarmonizationTitleStyle = wb.createCellStyle(); // the BLUE title font - is equal to 'firstLineFont' unitHarmonizationTitleStyle.setFont(firstLineFont); // now we just have to shade it unitHarmonizationTitleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); unitHarmonizationTitleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); unitHarmonizationTitleStyle.setAlignment(CellStyle.ALIGN_CENTER); // the style for Unit Harmonization responsibles - normal // let's create the BLUE Arial 14 font for the responsibles of // harmonization HSSFFont harmonizationResponsibleFont = wb.createFont(); harmonizationResponsibleFont.setColor(HSSFColor.DARK_BLUE.index); harmonizationResponsibleFont.setFontHeightInPoints((short) 14); CellStyle unitHarmonizationResponsibleStyle = wb.createCellStyle(); unitHarmonizationResponsibleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); unitHarmonizationResponsibleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); unitHarmonizationResponsibleStyle.setFont(harmonizationResponsibleFont); unitHarmonizationResponsibleStyle.setAlignment(CellStyle.ALIGN_CENTER); /* ** END of styles ** */ /* ** Immutable IST header ** */ HSSFHeader header = sheetToWriteTo.getHeader(); header.setCenter(HSSFHeader.font("Arial", "Normal") + HSSFHeader.fontSize((short) 10)); header.setCenter("Instituto Superior Tcnico"); int rowIndex = START_ROW_INDEX; int cellIndex = START_CELL_INDEX; int firstLineIndex = rowIndex++; int secondLineIndex = rowIndex++; /* ** Write the first lines with the dates ** */ HSSFRow row = sheetToWriteTo.createRow(firstLineIndex); HSSFCell cell = row.createCell(cellIndex); cell.setCellValue("SIADAP - LISTA DE AVALIADORES " + unitToSearchIn.getYear()); cell.setCellStyle(firstLineStyle); sheetToWriteTo .addMergedRegion(new CellRangeAddress(firstLineIndex, firstLineIndex, cellIndex, cellIndex + 3)); // second line if (!considerQuotas) { cellIndex = START_CELL_INDEX; row = sheetToWriteTo.createRow(secondLineIndex); cell = row.createCell(cellIndex); cell.setCellValue("PESSOAL CONTRATADO PELA ADIST"); cell.setCellStyle(secondLineStyle); } /* ** write the IST logo ** */ int pictureIdx = wb.addPicture(istLogoBytes, Workbook.PICTURE_TYPE_PNG); HSSFPatriarch drawingPatriarch = sheetToWriteTo.createDrawingPatriarch(); ClientAnchor clientAnchor = creationHelper.createClientAnchor(); clientAnchor.setCol1(cellIndex); clientAnchor.setRow1(rowIndex); HSSFPicture picture = drawingPatriarch.createPicture(clientAnchor, pictureIdx); // let's give the next item some space rowIndex += 6; /* ** Dynamic IST footer ** */ HSSFFooter footer = sheetToWriteTo.getFooter(); footer.setLeft("Lista gerada em: " + HSSFFooter.date() + " " + HSSFFooter.time()); footer.setCenter(HSSFFooter.page()); footer.setRight("SIADAP - Lista de avaliadores " + unitToSearchIn.getYear()); for (UnitSiadapWrapper eachUnit : unitToSearchIn .getAllChildUnits(unitToSearchIn.getConfiguration().getUnitRelations())) { Collection<Person> harmonizationResponsibles = eachUnit.getHarmonizationResponsibles(); if (includeHarmonizationResponsibles && !harmonizationResponsibles.isEmpty()) { // let's add the section stating the responsible for // Harmonization cellIndex = START_CELL_INDEX; row = sheetToWriteTo.createRow(++rowIndex); // let's merge the row sheetToWriteTo.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, cellIndex, cellIndex + 3)); cell = row.createCell(cellIndex); cell.setCellStyle(unitHarmonizationTitleStyle); cell.setCellValue("UNIDADE DE HARMONIZAO: " + eachUnit.getName()); // a 'blank' styled line row = sheetToWriteTo.createRow(++rowIndex); // merge it sheetToWriteTo.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, cellIndex, cellIndex + 3)); row.createCell(cellIndex).setCellStyle(unitHarmonizationResponsibleStyle); // each responsible has one of the following lines for (Person harmonizationResponsible : harmonizationResponsibles) { cellIndex = START_CELL_INDEX; row = sheetToWriteTo.createRow(++rowIndex); // merge it sheetToWriteTo .addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, cellIndex, cellIndex + 3)); cell = row.createCell(cellIndex); cell.setCellStyle(unitHarmonizationResponsibleStyle); cell.setCellValue("RESPONS?VEL PELA HARMONIZAO: " + harmonizationResponsible.getName()); } // and let's add an extra 'blank' styled line row = sheetToWriteTo.createRow(++rowIndex); sheetToWriteTo.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, cellIndex, cellIndex + 3)); row.createCell(cellIndex).setCellStyle(unitHarmonizationResponsibleStyle); // and a regular one! (skip one in the index) ++rowIndex; } if (eachUnit.getQuotaAwareTotalPeopleWorkingInUnit(false, considerQuotas) > 0) { row = sheetToWriteTo.createRow(++rowIndex); cellIndex = START_CELL_INDEX; // write the unit name and cost center String unitNameWithCC = eachUnit.getUnit().getPartyName().getContent(); if (eachUnit.getUnit().getPartyTypesSet().contains(PartyType.readBy("CostCenter"))) { unitNameWithCC += " - " + eachUnit.getUnit().getAcronym(); } cell = row.createCell(cellIndex++); sheetToWriteTo.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, --cellIndex, ++cellIndex)); cell.setCellValue(unitNameWithCC); cell.setCellStyle(costCenterStyle); /* **** write the Unit header ***** */ // restart the cell's index cellIndex = START_CELL_INDEX; // IST id avaliado int firstLineAfterUnitNameIndex = ++rowIndex; int secondLineAfterUnitNameIndex = ++rowIndex; row = sheetToWriteTo.createRow(firstLineAfterUnitNameIndex); cell = row.createCell(cellIndex); cell.setCellStyle(unitHeaderStyle); cell.setCellValue("IST id."); row = sheetToWriteTo.createRow(secondLineAfterUnitNameIndex); cell = row.createCell(cellIndex); cell.setCellStyle(unitHeaderStyle); // merge the IST id sheetToWriteTo.addMergedRegion(new CellRangeAddress(firstLineAfterUnitNameIndex, secondLineAfterUnitNameIndex, cellIndex, cellIndex)); // Nome avaliado row = sheetToWriteTo.getRow(firstLineAfterUnitNameIndex); cell = row.createCell(++cellIndex); cell.setCellStyle(unitHeaderStyle); cell.setCellValue("Nome"); row = sheetToWriteTo.getRow(secondLineAfterUnitNameIndex); cell = row.createCell(cellIndex); cell.setCellStyle(unitHeaderStyle); // merge sheetToWriteTo.addMergedRegion(new CellRangeAddress(firstLineAfterUnitNameIndex, secondLineAfterUnitNameIndex, cellIndex, cellIndex)); if (shouldIncludeUniverse) { // SIADAP do avaliado row = sheetToWriteTo.getRow(firstLineAfterUnitNameIndex); cell = row.createCell(++cellIndex); cell.setCellStyle(unitHeaderStyle); cell.setCellValue("SIADAP"); row = sheetToWriteTo.getRow(secondLineAfterUnitNameIndex); cell = row.createCell(cellIndex); cell.setCellStyle(unitHeaderStyle); // merge sheetToWriteTo.addMergedRegion(new CellRangeAddress(firstLineAfterUnitNameIndex, secondLineAfterUnitNameIndex, cellIndex, cellIndex)); } // Ist id do avaliador row = sheetToWriteTo.getRow(firstLineAfterUnitNameIndex); cell = row.createCell(++cellIndex); cell.setCellStyle(unitHeaderStyle); cell.setCellValue("IST id."); row = sheetToWriteTo.getRow(secondLineAfterUnitNameIndex); cell = row.createCell(cellIndex); cell.setCellStyle(unitHeaderStyle); // merge sheetToWriteTo.addMergedRegion(new CellRangeAddress(firstLineAfterUnitNameIndex, secondLineAfterUnitNameIndex, cellIndex, cellIndex)); // avaliador row = sheetToWriteTo.getRow(firstLineAfterUnitNameIndex); cell = row.createCell(++cellIndex); cell.setCellStyle(unitHeaderStyle); cell.setCellValue("Avaliador"); row = sheetToWriteTo.getRow(secondLineAfterUnitNameIndex); cell = row.createCell(cellIndex); cell.setCellStyle(unitHeaderStyle); // merge sheetToWriteTo.addMergedRegion(new CellRangeAddress(firstLineAfterUnitNameIndex, secondLineAfterUnitNameIndex, cellIndex, cellIndex)); List<PersonSiadapWrapper> listToUse = (considerQuotas) ? eachUnit.getUnitEmployeesWithQuotas(false) : eachUnit.getUnitEmployeesWithoutQuotas(true); // now let's take care of exporting the persons for (PersonSiadapWrapper personWrapper : listToUse) { row = sheetToWriteTo.createRow(++rowIndex); // restart the cell's index cellIndex = START_CELL_INDEX; String istIdEvaluated = personWrapper.getPerson().getUser().getUsername(); cell = row.createCell(cellIndex++); cell.setCellValue(istIdEvaluated); cell.setCellStyle(defaultTextIstIdStyle); String nameEvaluatedPerson = personWrapper.getPerson().getName(); cell = row.createCell(cellIndex++); cell.setCellValue(nameEvaluatedPerson); cell.setCellStyle(defaultTextNameStyle); if (shouldIncludeUniverse) { Siadap siadap = personWrapper.getSiadap(); String siadapUniverseToBeWritten = (siadap == null || siadap.getDefaultSiadapUniverse() == null) ? "No definido" : siadap.getDefaultSiadapUniverse().getLocalizedName(); cell = row.createCell(cellIndex++); cell.setCellValue(siadapUniverseToBeWritten); cell.setCellStyle(defaultTextNameStyle); } PersonSiadapWrapper evaluatorWrapper = personWrapper.getEvaluator(); String istIdEvaluator = evaluatorWrapper == null ? "-" : evaluatorWrapper.getPerson().getUser().getUsername(); cell = row.createCell(cellIndex++); cell.setCellValue(istIdEvaluator); cell.setCellStyle(defaultTextIstIdStyle); String nameEvaluatorWrapper = evaluatorWrapper == null ? "-" : evaluatorWrapper.getName(); cell = row.createCell(cellIndex++); cell.setCellValue(nameEvaluatorWrapper); cell.setCellStyle(defaultTextNameStyle); } // let's make a bottom border on the last four cells for (int i = START_CELL_INDEX; i < START_CELL_INDEX + 4; i++) { cell = row.getCell(i); // let's diferentaitate between the IST-id and the name if (i == START_CELL_INDEX || i == START_CELL_INDEX + 2) // first // cell, // IST-ID // then. // or // third // cell // the // other // IST-ID { cell.setCellStyle(defaultTextIstIdLastStyle); } else { cell.setCellStyle(defaultTextNameLastStyle); } } row = sheetToWriteTo.createRow(++rowIndex); row = sheetToWriteTo.createRow(++rowIndex); } } sheetToWriteTo.autoSizeColumn(START_CELL_INDEX); sheetToWriteTo.autoSizeColumn(START_CELL_INDEX + 1); sheetToWriteTo.autoSizeColumn(START_CELL_INDEX + 2); sheetToWriteTo.autoSizeColumn(START_CELL_INDEX + 3); sheetToWriteTo.autoSizeColumn(START_CELL_INDEX + 4); // now let's resize the logo picture.resize(); }
From source file:mongodbutils.Filehandler.java
public boolean processFile(String filePath, MongodbConnection mc, String strdbName, String strCollName) throws IOException { this.mc = mc; FileInputStream fileIn = null; try {/*w w w .j av a2s . c o m*/ fileIn = new FileInputStream(filePath); POIFSFileSystem fs = new POIFSFileSystem(fileIn); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); Object objReturn = null; //Read in first row as field names Row rowH = sheet.getRow(sheet.getFirstRowNum()); String fields[] = new String[sheet.getRow(0).getLastCellNum()]; for (Cell cell : rowH) { objReturn = null; objReturn = getCellValue(cell); fields[cell.getColumnIndex()] = objReturn.toString(); } //loop thru all cells with values int rowcount = 0; for (Row row : sheet) { if (row.getRowNum() == 0) { continue; //skip first row } JSONObject obj = new JSONObject(); for (Cell cell : row) { if (fields.length < cell.getColumnIndex()) { continue; //only export column if we have header set } objReturn = null; objReturn = getCellValue(cell); if (!objReturn.toString().equals("")) { if (objReturn instanceof Double) { obj.put(fields[cell.getColumnIndex()], objReturn); } else if (objReturn instanceof String) { if (objReturn.toString().contains("$date")) { JSONParser parser = new JSONParser(); try { obj.put(fields[cell.getColumnIndex()], parser.parse(objReturn.toString())); } catch (ParseException ex) { Logger.getLogger(Filehandler.class.getName()).log(Level.SEVERE, null, ex); } } else { obj.put(fields[cell.getColumnIndex()], objReturn); } } } } rowcount += 1; mc.insertJSON(strdbName, strCollName, obj.toJSONString()); } return true; } catch (FileNotFoundException ex) { Logger.getLogger(Filehandler.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(Filehandler.class.getName()).log(Level.SEVERE, null, ex); } catch (Exception e) { Logger.getLogger(Filehandler.class.getName()).log(Level.SEVERE, null, e); } finally { if (fileIn != null) { fileIn.close(); } } return false; }
From source file:mx.avanti.siract.ui.FiltrosBeanUI.java
public void Export2ExcelSemaforoProgramasEd() throws Throwable { //variables listaux ReporteAux reporteUI = new ReporteAux(); // objeto de hoja de excel HSSFWorkbook workbook = new HSSFWorkbook(); String nombreLibro = ""; //guardamos imagen UABC en Documento URL location = FiltrosBeanUI.class.getProtectionDomain().getCodeSource().getLocation(); String path = location.getFile(); String replace = path.replace("FiltrosBeanUI.class", "uabclogo.png"); //InputStream uabc_image = new FileInputStream(replace); //InputStream uabc_image = new FileInputStream("C://decode/uabclogo.png"); InputStream uabc_image = new FileInputStream("/home/user/decode/uabclogo.png"); byte[] bytes = IOUtils.toByteArray(uabc_image); int uabcLogo = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG); uabc_image.close();/*from w ww . ja v a2 s . com*/ //definimos estilo a celda titulo de los encabezados con logo escuela HSSFCellStyle style = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setFontName(HSSFFont.FONT_ARIAL); font.setFontHeightInPoints((short) 20); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.GREEN.index); style.setFont(font); //setCellStyle(style); //definimos los bordes de celdas en tabla HSSFCellStyle borderstabla = workbook.createCellStyle(); borderstabla.setBorderBottom(HSSFCellStyle.BORDER_THIN); borderstabla.setBorderTop(HSSFCellStyle.BORDER_THIN); borderstabla.setBorderRight(HSSFCellStyle.BORDER_THIN); borderstabla.setBorderLeft(HSSFCellStyle.BORDER_THIN); font = workbook.createFont(); borderstabla.setFont(font); //definimos el estilo de las cabezeras de una tabla HSSFCellStyle headerTabla = workbook.createCellStyle(); HSSFFont fontwit = workbook.createFont(); fontwit.setColor(HSSFColor.WHITE.index); headerTabla.setBorderBottom(HSSFCellStyle.BORDER_THIN); headerTabla.setBorderTop(HSSFCellStyle.BORDER_THIN); headerTabla.setBorderRight(HSSFCellStyle.BORDER_THIN); headerTabla.setBorderLeft(HSSFCellStyle.BORDER_THIN); headerTabla.setFillForegroundColor(HSSFColor.GREEN.index); headerTabla.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headerTabla.setFont(fontwit); //aqui cambie headerTabla.setWrapText(true); //aqui cambie //if(criterio.equalsIgnoreCase("programa_educativo")){ HSSFSheet sheet = workbook.createSheet("Programas Educativos"); nombreLibro = "- Director"; // if(reporte.equalsIgnoreCase("entregados")){ nombreLibro = "RACT General " + nombreLibro; //definimos encabezado sheet = cabezeraGeneralExcel(sheet, uabcLogo, style); HSSFRow row1 = sheet.createRow(7); row1.setHeight((short) 600); HSSFCell cell = row1.createCell(1); cell.setCellValue( "Concentrado de Reporte de Avance de Contenido Temtico General por Programa Educativo"); cell.setCellStyle(style); // HSSFRow row1 = sheet.createRow(14); // row1.setHeightInPoints(56); // HSSFRow row2 = sheet.createRow(28); // row2.setHeightInPoints(56); // HSSFRow row3 = sheet.createRow(42); // row3.setHeightInPoints(56); //aqui cambie jesus ruelas //for(int i=0;i<28;i++){ // sheet.autoSizeColumn(1); // sheet.autoSizeColumn(2); // sheet.autoSizeColumn(3); // sheet.autoSizeColumn(4); //merge cells de Total de RACT General de + ProgEd sheet.addMergedRegion(new CellRangeAddress(13, 13, 1, 5)); sheet.addMergedRegion(new CellRangeAddress(13, 13, 7, 11)); sheet.addMergedRegion(new CellRangeAddress(13, 13, 13, 17)); sheet.addMergedRegion(new CellRangeAddress(13, 13, 19, 23)); sheet.addMergedRegion(new CellRangeAddress(13, 13, 25, 29)); sheet.addMergedRegion(new CellRangeAddress(27, 27, 1, 5)); sheet.addMergedRegion(new CellRangeAddress(27, 27, 7, 11)); sheet.addMergedRegion(new CellRangeAddress(27, 27, 13, 17)); sheet.addMergedRegion(new CellRangeAddress(27, 27, 19, 23)); sheet.addMergedRegion(new CellRangeAddress(27, 27, 25, 29)); sheet.addMergedRegion(new CellRangeAddress(41, 41, 1, 5)); sheet.addMergedRegion(new CellRangeAddress(41, 41, 7, 11)); sheet.addMergedRegion(new CellRangeAddress(41, 41, 13, 17)); sheet.addMergedRegion(new CellRangeAddress(41, 41, 19, 23)); sheet.addMergedRegion(new CellRangeAddress(41, 41, 25, 29)); sheet.addMergedRegion(new CellRangeAddress(55, 55, 1, 5)); sheet.addMergedRegion(new CellRangeAddress(55, 55, 7, 11)); sheet.addMergedRegion(new CellRangeAddress(55, 55, 13, 17)); sheet.addMergedRegion(new CellRangeAddress(55, 55, 19, 23)); sheet.addMergedRegion(new CellRangeAddress(55, 55, 25, 29)); sheet.addMergedRegion(new CellRangeAddress(69, 69, 1, 5)); sheet.addMergedRegion(new CellRangeAddress(69, 69, 7, 11)); sheet.addMergedRegion(new CellRangeAddress(69, 69, 13, 17)); sheet.addMergedRegion(new CellRangeAddress(69, 69, 19, 23)); sheet.addMergedRegion(new CellRangeAddress(69, 69, 25, 29)); //merge cells de programa educativo sheet.addMergedRegion(new CellRangeAddress(11, 11, 2, 3)); sheet.addMergedRegion(new CellRangeAddress(11, 11, 8, 9)); sheet.addMergedRegion(new CellRangeAddress(11, 11, 14, 15)); sheet.addMergedRegion(new CellRangeAddress(11, 11, 20, 21)); sheet.addMergedRegion(new CellRangeAddress(11, 11, 26, 27)); sheet.addMergedRegion(new CellRangeAddress(25, 25, 2, 3)); sheet.addMergedRegion(new CellRangeAddress(25, 25, 8, 9)); sheet.addMergedRegion(new CellRangeAddress(25, 25, 14, 15)); sheet.addMergedRegion(new CellRangeAddress(25, 25, 20, 21)); sheet.addMergedRegion(new CellRangeAddress(25, 25, 26, 27)); sheet.addMergedRegion(new CellRangeAddress(39, 39, 2, 3)); sheet.addMergedRegion(new CellRangeAddress(39, 39, 8, 9)); sheet.addMergedRegion(new CellRangeAddress(39, 39, 14, 15)); sheet.addMergedRegion(new CellRangeAddress(39, 39, 20, 21)); sheet.addMergedRegion(new CellRangeAddress(39, 39, 26, 27)); sheet.addMergedRegion(new CellRangeAddress(53, 53, 2, 3)); sheet.addMergedRegion(new CellRangeAddress(53, 53, 8, 9)); sheet.addMergedRegion(new CellRangeAddress(53, 53, 14, 15)); sheet.addMergedRegion(new CellRangeAddress(53, 53, 20, 21)); sheet.addMergedRegion(new CellRangeAddress(53, 53, 26, 27)); sheet.addMergedRegion(new CellRangeAddress(67, 67, 2, 3)); sheet.addMergedRegion(new CellRangeAddress(67, 67, 8, 9)); sheet.addMergedRegion(new CellRangeAddress(67, 67, 14, 15)); sheet.addMergedRegion(new CellRangeAddress(67, 67, 20, 21)); sheet.addMergedRegion(new CellRangeAddress(67, 67, 26, 27)); //} //aqui cambie jesus ruelas //mostraremos tabla con // Programas educativos // Total Racs entregados // Total Esperados //preparamos informacion para insertar List<Programaeducativo> programasByUnidad = getProgramasByUnidad(); List<Planestudio> planesByPrograma = getPlanesByPrograma(); ArrayList<String> listaProgEdContar = esperadosDelegate.getSemadoroProgEdValor(1); //setCellStyle(style); //setExDat(sheet, 7, 1, "Concentrado de Reporte de Avance de Contenido Temtico General por Programa Educativo"); //imrpiimiremos la tabla // Definimos los encabezados de la tabla int pos = 0; int row = 11; int col = 1; Boolean bandPe = false; for (Programaeducativo pe : programasByUnidad) { for (String peContar : listaProgEdContar) { String[] contarEntEspProgEd; if (listaProgEdContar.size() >= pos) { if (peContar.contains(pe.getPednombre())) { contarEntEspProgEd = listaProgEdContar.get(pos).split("-"); setExDat(sheet, row, col, "Clave"); setExDat(sheet, row, col + 1, "Programa Educativo"); setExDat(sheet, row, col + 3, "Plan de Estudios"); setExDat(sheet, row, col + 4, "Responsable"); // if (contarEntEspProgEd[0].equalsIgnoreCase(programasByUnidad.get(0).getPednombre())) { setExDat(sheet, row + 1, col, pe.getPedclave()); setExDat(sheet, row + 1, col + 1, pe.getPednombre()); if (!(planesByPrograma.isEmpty())) { setExDat(sheet, row + 1, col + 2, planesByPrograma.get(0).getPesvigenciaPlan()); } setExDat(sheet, row + 1, col + 3, ""); // } setExDat(sheet, row + 2, col, "Total de RACT General de " + programasByUnidad.get(0).getPednombre()); setExDat(sheet, row + 3, col, "Resumen de totales \r\n por numero de RACT"); setExDat(sheet, row + 3, col + 1, "Total entregados"); setExDat(sheet, row + 3, col + 2, "%"); setExDat(sheet, row + 3, col + 3, "Total esperados"); setExDat(sheet, row + 3, col + 4, "%"); sheet.getRow(row + 3).getCell(1);//aqui modifique jesus ruelas float porcentEnt = (Float.parseFloat(contarEntEspProgEd[1])) / (Float.parseFloat(contarEntEspProgEd[2])) * 100; float porcentEsp = 100 - porcentEnt; // setExDat(sheet, 15, 1, "Todos los RACTS"); // setExDat(sheet, 15, 2, contarEntEspProgEd[1]); // setExDat(sheet, 15, 3, "" + porcentEnt + "%"); // setExDat(sheet, 15, 4, contarEntEspProgEd[2]); // setExDat(sheet, 15, 5, "" + porcentEsp + "%"); //String[] contarEntEspProgEd; String[] contarEntRact1; String[] contarEntRact2; String[] contarEntRact3; int esperadosProgEdUnicoRact = 0; contarEntRact1 = contarEntEspProgEd[3].split(":"); contarEntRact2 = contarEntEspProgEd[4].split(":"); contarEntRact3 = contarEntEspProgEd[5].split(":"); esperadosProgEdUnicoRact = (Integer.parseInt(contarEntEspProgEd[2])) / 3; float contarPorcentEntRact1 = (Float.parseFloat(contarEntRact1[1])) / ((float) esperadosProgEdUnicoRact) * 100; float contarPorcentEntRact2 = (Float.parseFloat(contarEntRact2[1])) / ((float) esperadosProgEdUnicoRact) * 100; float contarPorcentEntRact3 = (Float.parseFloat(contarEntRact3[1])) / ((float) esperadosProgEdUnicoRact) * 100; float contarPorcentEspRact1 = 100 - contarPorcentEntRact1; float contarPorcentEspRact2 = 100 - contarPorcentEntRact2; float contarPorcentEspRact3 = 100 - contarPorcentEntRact3; setExDat(sheet, row + 4, col, "RACT 1"); setExDat(sheet, row + 4, col + 1, contarEntRact1[1]); setExDat(sheet, row + 4, col + 2, "" + contarPorcentEntRact1 + "%"); setExDat(sheet, row + 4, col + 3, esperadosProgEdUnicoRact); setExDat(sheet, row + 4, col + 4, "" + contarPorcentEspRact1 + "%"); setExDat(sheet, row + 5, col, "RACT 2"); setExDat(sheet, row + 5, col + 1, contarEntRact2[1]); setExDat(sheet, row + 5, col + 2, "" + contarPorcentEntRact2 + "%"); setExDat(sheet, row + 5, col + 3, esperadosProgEdUnicoRact); setExDat(sheet, row + 5, col + 4, "" + contarPorcentEspRact2 + "%"); setExDat(sheet, row + 6, col, "RACT 3"); setExDat(sheet, row + 6, col + 1, contarEntRact3[1]); setExDat(sheet, row + 6, col + 2, "" + contarPorcentEntRact3 + "%"); setExDat(sheet, row + 6, col + 3, esperadosProgEdUnicoRact); setExDat(sheet, row + 6, col + 4, "" + contarPorcentEspRact3 + "%"); setExDat(sheet, row + 7, col, "Todos los RACTS"); setExDat(sheet, row + 7, col + 1, contarEntEspProgEd[1]); setExDat(sheet, row + 7, col + 2, "" + porcentEnt + "%"); setExDat(sheet, row + 7, col + 3, contarEntEspProgEd[2]); setExDat(sheet, row + 7, col + 4, "" + porcentEsp + "%"); setStyleCell(sheet, headerTabla, row, col); setStyleCell(sheet, headerTabla, row, col + 1); setStyleCell(sheet, headerTabla, row, col + 2); setStyleCell(sheet, headerTabla, row, col + 3); setStyleCell(sheet, headerTabla, row, col + 4); setStyleCell(sheet, headerTabla, row + 2, col); setStyleCell(sheet, headerTabla, row + 2, col + 1); setStyleCell(sheet, headerTabla, row + 2, col + 2); setStyleCell(sheet, headerTabla, row + 2, col + 3); setStyleCell(sheet, headerTabla, row + 3, col); setStyleCell(sheet, headerTabla, row + 3, col + 1); setStyleCell(sheet, headerTabla, row + 3, col + 2); setStyleCell(sheet, headerTabla, row + 3, col + 3); setStyleCell(sheet, headerTabla, row + 3, col + 4); bandPe = true; pos++; } } if (bandPe == true) { if (col >= 25) { row = row + 14; col = -5; } col = col + 6; bandPe = false; } } } /*sheet.autoSizeColumn(2); sheet.autoSizeColumn(3); sheet.autoSizeColumn(4);*/ //imprimimos la informacion en su lugar int prow = 14; for (Programaeducativo programa : programasByUnidad) { if (selectProgramEducativo.contains(programa.getPedid().toString())) { setExDat(sheet, prow, 17, programa.getPednombre()); setExDat(sheet, prow, 18, 75); setExDat(sheet, prow, 19, 100); prow++; } } // } //} //finalizamos con //metodo para descargar el objeto System.out.println("Generando Excel"); if (criterio != "" && reporte != "") { FacesContext facesContext = FacesContext.getCurrentInstance(); ExternalContext externalContext = facesContext.getExternalContext(); externalContext.setResponseContentType("application/vnd.ms-excel"); externalContext.setResponseHeader("Content-Disposition", "attachment; filename=\"" + nombreLibro + ".xls\""); workbook.write(externalContext.getResponseOutputStream()); facesContext.responseComplete(); } else { System.out.println("No se Genero por: Criterio->" + criterio + " , Reporte->" + reporte); } }
From source file:mx.avanti.siract.ui.FiltrosBeanUI.java
public void Export2Excel() throws Throwable { //variables listaux ReporteAux reporteUI = new ReporteAux(); // objeto de hoja de excel HSSFWorkbook workbook = new HSSFWorkbook(); String nombreLibro = ""; //guardamos imagen UABC en Documento URL location = FiltrosBeanUI.class.getProtectionDomain().getCodeSource().getLocation(); String path = location.getFile(); String replace = path.replace("FiltrosBeanUI.class", "uabclogo.png"); //InputStream uabc_image = new FileInputStream(replace); //InputStream uabc_image = new FileInputStream("C://decode/uabclogo.png"); InputStream uabc_image = new FileInputStream("/home/user/decode/uabclogo.png"); byte[] bytes = IOUtils.toByteArray(uabc_image); int uabcLogo = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG); uabc_image.close();/* w w w . j a va 2 s . c o m*/ //definimos estilo a celda titulo de los encabezados con logo escuela HSSFCellStyle style = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setFontName(HSSFFont.FONT_ARIAL); font.setFontHeightInPoints((short) 20); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.GREEN.index); style.setFont(font); //definimos los bordes de celdas en tabla HSSFCellStyle borderstabla = workbook.createCellStyle(); borderstabla.setBorderBottom(HSSFCellStyle.BORDER_THIN); borderstabla.setBorderTop(HSSFCellStyle.BORDER_THIN); borderstabla.setBorderRight(HSSFCellStyle.BORDER_THIN); borderstabla.setBorderLeft(HSSFCellStyle.BORDER_THIN); font = workbook.createFont(); borderstabla.setFont(font); //definimos el estilo de las cabezeras de una tabla HSSFCellStyle headerTabla = workbook.createCellStyle(); HSSFFont fontwit = workbook.createFont(); fontwit.setColor(HSSFColor.WHITE.index); headerTabla.setBorderBottom(HSSFCellStyle.BORDER_THIN); headerTabla.setBorderTop(HSSFCellStyle.BORDER_THIN); headerTabla.setBorderRight(HSSFCellStyle.BORDER_THIN); headerTabla.setBorderLeft(HSSFCellStyle.BORDER_THIN); headerTabla.setFillForegroundColor(HSSFColor.GREEN.index); headerTabla.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headerTabla.setFont(fontwit); if (criterio.equalsIgnoreCase("programa_educativo")) { HSSFSheet sheet = workbook.createSheet("Graficos"); nombreLibro = "- Programa Educativo"; if (reporte.equalsIgnoreCase("entregados")) { nombreLibro = "Entregados " + nombreLibro; //definimos encabezado sheet = cabezeraGeneralExcel(sheet, uabcLogo, style); //mostraremos tabla con // Programas educativos // Total Racs entregados // Total Esperados //preparamos informacion para insertar List<Programaeducativo> programasByUnidad = getProgramasByUnidad(); //imrpiimiremos la tabla // Definimos los encabezados de la tabla setExDat(sheet, 13, 17, "Programa Educativo"); setExDat(sheet, 13, 18, "Total de RACT Entregados"); setExDat(sheet, 13, 19, "Total de RACT Esperados"); setExDat(sheet, 13, 20, " "); setStyleCell(sheet, headerTabla, 13, 17); setStyleCell(sheet, headerTabla, 13, 18); setStyleCell(sheet, headerTabla, 13, 19); //imprimimos la informacion en su lugar int prow = 14; for (Programaeducativo programa : programasByUnidad) { if (selectProgramEducativo.contains(programa.getPedid().toString())) { setExDat(sheet, prow, 17, programa.getPednombre()); setExDat(sheet, prow, 18, 75); setExDat(sheet, prow, 19, 100); prow++; } } //Se obtiene consulta con los parametros seleccionados ArrayList<String> entregados = esperadosDelegate.getFullProgramaEdu(unidadacademica.getUacid(), Plan, Programa, Ciclo); //agregamos programacion de grafico if (tipografico.equalsIgnoreCase("barras")) { BarChartCL demo1 = new BarChartCL("Estadsticas de Reportes Entregados por Programa Educativo", entregados, "ExcelPOIGrafica1"); demo1.addChartToExcel(demo1.createChart(demo1.createDataset()), workbook); } if (tipografico.equalsIgnoreCase("linea")) { LineChartDemo1 demo1 = new LineChartDemo1( "Estadsticas de Reportes Entregados por Programa Educativo", entregados, "ExcelPOIGrafica1"); demo1.addChartToExcel(demo1.createChart(demo1.createDataset()), workbook); } if (tipografico.equalsIgnoreCase("pastel")) { PieChartDemo1 demo1 = new PieChartDemo1( "Estadsticas de Reportes Entregados por Programa Educativo", entregados, "ExcelPOIGrafica1"); demo1.addChartToExcel(demo1.createChart(demo1.createDataset()), workbook); } //Se acaba grafico //recorreremos todo el list aux para revisar los programas educativos que utiliza //aun que podemos usar el selectProgramaeducativo /* Programacion de Hojas*/ for (Programaeducativo programa : programasByUnidad) { if (selectProgramEducativo.contains(Integer.toString(programa.getPedclave()))) { //aqui creamos la hoja para el programa sheet = workbook.createSheet(programa.getPednombre()); //sheet = cabezeraGeneralExcel(sheet,uabcLogo,style); lo moveremos al final de la hoja para que no sea mdoficada la imagen setExDat(sheet, 8, 3, "Concentrado de Reporte de Avance de Contenido Temtico de Entregados por Programa Educativo"); boolean autotam = true; //informacion de programa educativo tabla setExDat(sheet, 14, 1, "Clave"); setExDat(sheet, 14, 2, "Programa Educativo"); setExDat(sheet, 14, 3, "Plan de Estudios"); setExDat(sheet, 14, 4, "Responsable"); setStyleCell(sheet, headerTabla, 14, 1); setStyleCell(sheet, headerTabla, 14, 2); setStyleCell(sheet, headerTabla, 14, 3); setStyleCell(sheet, headerTabla, 14, 4); //llenado de informacion List<Planestudio> planes = filtrosBeanHelper.getConsultaDelegate() .getPlanesByPrograma(programa.getPedclave()); setExDat(sheet, 15, 1, programa.getPedclave()); setExDat(sheet, 15, 2, programa.getPednombre()); setExDat(sheet, 15, 3, planes.get(0).getPesvigenciaPlan()); setExDat(sheet, 15, 4, "Llenar en tabla"); setStyleCell(sheet, borderstabla, 15, 1); setStyleCell(sheet, borderstabla, 15, 2); setStyleCell(sheet, borderstabla, 15, 3); setStyleCell(sheet, borderstabla, 15, 4); boolean uno = true; int currow = 20; Planestudio planeact = new Planestudio(); for (Planestudio planeactn : planes) { planeact = planeactn; break; } ReporteAvanceAux tempAux = new ReporteAvanceAux(); for (ReporteAvanceAux aux : listaAux) { tempAux = aux; break; } //intentaremos usar un solo plan setExDat(sheet, currow, 1, "?reas de conocimiento"); setStyleCell(sheet, headerTabla, currow, 1); currow++; //cada plan de estudios tiene varias areas de conocimiento //List<Areaconocimiento> areasConocimiento = filtrosBeanHelper.getConsultaDelegate().getAreasByPlan( tempAux.getReporteAvance().getUnidadaprendizajeImparteProfesor().getGrupo().getPlanestudio().getPesid() ); //List<Areaconocimiento> areasConocimiento = filtrosBeanHelper.getConsultaDelegate().getAreasByPlan( planeact.getPesid() ); List<Areaconocimiento> areasConocimiento = filtrosBeanHelper.getConsultaDelegate() .getAreasByPlanClave(programa.getPedclave(), tempAux.getReporteAvance().getUnidadaprendizajeImparteProfesor().getGrupo() .getPlanestudio().getPesvigenciaPlan()); for (Areaconocimiento areaCon : areasConocimiento) { //clave , area de conocimiento, responsable setExDat(sheet, currow, 1, "Clave"); setExDat(sheet, currow, 2, "?rea de conocimiento"); setExDat(sheet, currow, 3, "Responsable"); setStyleCell(sheet, headerTabla, currow, 1); setStyleCell(sheet, headerTabla, currow, 2); setStyleCell(sheet, headerTabla, currow, 3); currow++; setExDat(sheet, currow, 1, areaCon.getAcoclave()); //clave setExDat(sheet, currow, 2, areaCon.getAconombre()); //area conociminto setExDat(sheet, currow, 3, ""); //responsable currow += 2; //obtenemos las unidades de aprendisake setExDat(sheet, currow, 1, "Clave unidad de aprendizaje"); setExDat(sheet, currow, 2, "Unidad de aprendizaje"); setExDat(sheet, currow, 3, "No. de empleado"); setExDat(sheet, currow, 4, "Nombre del profesor"); setExDat(sheet, currow, 5, "Grupo"); setExDat(sheet, currow, 6, "% Avance 1er reporte"); setExDat(sheet, currow, 7, "Fecha de elaboracin 1er RACT"); setExDat(sheet, currow, 8, "% Avance 2do reporte"); setExDat(sheet, currow, 9, "Fecha de elaboracin 2do RACT"); setExDat(sheet, currow, 10, "% Avance 3er reporte"); setExDat(sheet, currow, 11, "Fecha de elaboracin 3er RACT"); //autosize para la columna if (autotam) { sheet.autoSizeColumn(2); sheet.autoSizeColumn(4); sheet.autoSizeColumn(11); autotam = false; } // para formatear toda la linea for (int i = 1; i <= 11; i++) { //tenemos qu aajustar texto //tenemos que centrar el texto //cambiar el color de fondo setStyleCell(sheet, headerTabla, currow, i); } currow++; //List<Unidadaprendizaje> unidadesAprendisaje = filtrosBeanHelper.getConsultaDelegate().getUnidadByArea(areaCon.getAcoclave()); //de ls listaAux separamos los los reportes que correspondan a la unidad de aprendisaje acutal ArrayList<ReporteAvanceAux> tempListAux = new ArrayList<ReporteAvanceAux>(); for (ReporteAvanceAux aux : listaAux) { if (aux.getAreaConocimiento().getAcoclave() == areaCon.getAcoclave() && programa.getPedclave() == aux.getReporteAvance() .getUnidadaprendizajeImparteProfesor().getGrupo().getPlanestudio() .getProgramaeducativo().getPedclave()) { tempListAux.add(aux); } } //List<UnidadaprendizajeImparteProfesor> unidadesProfesor = filtrosBeanHelper.getConsultaDelegate().getUnidadesAprendisajeImparteProf(unidadApren.getUapid()); int uniprofeTemp = 0; for (ReporteAvanceAux auxRacs : tempListAux) { String nompreP = auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getProfesor().getPronombre(); String apellidoPP = auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getProfesor().getProapellidoPaterno(); String apellidoPM = auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getProfesor().getProapellidoMaterno(); int claveUnidadApren = auxRacs.getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUnidadaprendizaje().getUapclave(); String nombreUnidad = auxRacs.getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUnidadaprendizaje() .getUapnombre(); uniprofeTemp = claveUnidadApren; setExDat(sheet, currow, 1, claveUnidadApren); //clave setExDat(sheet, currow, 2, nombreUnidad); //nombre unidad //reporteAvance.unidadaprendizajeImparteProfesor.profesor.pronumeroEmpleado setExDat(sheet, currow, 3, auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getProfesor().getPronumeroEmpleado()); //numero empleado setExDat(sheet, currow, 4, apellidoPP + " " + apellidoPM + " " + nompreP); //nombre maestro setExDat(sheet, currow, 5, auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor().getGrupo() .getGponumero() + "-" + auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getUiptipoSubgrupo() + "-" + auxRacs.getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUipsubgrupo()); //grupo numero //marcamos bordes for (int i = 1; i <= 5; i++) { setStyleCell(sheet, borderstabla, currow, i); } //tienne que ser entregados/enviados if (auxRacs.getStatusRact1() != null) { if (auxRacs.getStatusRact1().equalsIgnoreCase("Enviado")) { System.out.println("Entro a enviados 1"); setExDat(sheet, currow, (4 + (1 * 2)), auxRacs.getPorcentAvanceRact1()); //% avance 7** setExDat(sheet, currow, (4 + (1 * 2) + 1), auxRacs.getFechaElaboracRact1().toString()); //fecha elabora 8*** setStyleCell(sheet, borderstabla, currow, (4 + (1 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (1 * 2) + 1)); } else { setExDat(sheet, currow, (4 + (1 * 2)), " "); //% avance 7** setExDat(sheet, currow, (4 + (1 * 2) + 1), " "); //fecha elabora 8*** setStyleCell(sheet, borderstabla, currow, (4 + (1 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (1 * 2) + 1)); } } else { setExDat(sheet, currow, (4 + (1 * 2)), " "); //% avance 7** setExDat(sheet, currow, (4 + (1 * 2) + 1), " "); //fecha elabora 8*** setStyleCell(sheet, borderstabla, currow, (4 + (1 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (1 * 2) + 1)); } if (auxRacs.getStatusRact2() != null) { if (auxRacs.getStatusRact2().equalsIgnoreCase("Enviado")) { setExDat(sheet, currow, (4 + (2 * 2)), auxRacs.getPorcentAvanceRact2()); //% avance 7** setExDat(sheet, currow, (4 + (2 * 2) + 1), auxRacs.getFechaElaboracRact2().toString()); //fecha elabora 8*** setStyleCell(sheet, borderstabla, currow, (4 + (2 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (2 * 2) + 1)); } else { setExDat(sheet, currow, (4 + (2 * 2)), " "); //% avance 7** setExDat(sheet, currow, (4 + (2 * 2) + 1), " "); //fecha elabora 8*** setStyleCell(sheet, borderstabla, currow, (4 + (2 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (2 * 2) + 1)); } } else { setExDat(sheet, currow, (4 + (2 * 2)), " "); //% avance 7** setExDat(sheet, currow, (4 + (2 * 2) + 1), " "); //fecha elabora 8*** setStyleCell(sheet, borderstabla, currow, (4 + (2 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (2 * 2) + 1)); } if (auxRacs.getStatusRact3() != null) { if (auxRacs.getStatusRact3().equalsIgnoreCase("Enviado")) { setExDat(sheet, currow, (4 + (3 * 2)), auxRacs.getPorcentAvanceRact3()); //% avance 7** setExDat(sheet, currow, (4 + (3 * 2) + 1), auxRacs.getFechaElaboracRact3().toString()); //fecha elabora 8*** setStyleCell(sheet, borderstabla, currow, (4 + (3 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (3 * 2) + 1)); } else { setExDat(sheet, currow, (4 + (3 * 2)), " "); //% avance 7** setExDat(sheet, currow, (4 + (3 * 2) + 1), " "); //fecha elabora 8*** setStyleCell(sheet, borderstabla, currow, (4 + (3 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (3 * 2) + 1)); } } else { setExDat(sheet, currow, (4 + (3 * 2)), " "); //% avance 7** setExDat(sheet, currow, (4 + (3 * 2) + 1), " "); //fecha elabora 8*** setStyleCell(sheet, borderstabla, currow, (4 + (3 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (3 * 2) + 1)); } currow++; } currow += 2; } //aqui modifique Jesus Ruelas - Entregados //aqui cambie headerTabla.setWrapText(true); //aqui cambie //aqui cambie jesus ruelas //for(int i=0;i<28;i++){ // sheet.autoSizeColumn(1); // sheet.autoSizeColumn(2); // sheet.autoSizeColumn(3); // sheet.autoSizeColumn(4); //merge cells de Total de RACT General de + ProgEd // sheet.addMergedRegion(new CellRangeAddress(13,13,1,5)); // sheet.addMergedRegion(new CellRangeAddress(13,13,7,11)); // sheet.addMergedRegion(new CellRangeAddress(13,13,13,17)); // sheet.addMergedRegion(new CellRangeAddress(13,13,19,23)); // sheet.addMergedRegion(new CellRangeAddress(13,13,25,29)); //} //aqui cambie jesus ruelas //mostraremos tabla con // Programas educativos // Total Racs entregados // Total Esperados //preparamos informacion para insertar // List<Programaeducativo> programasByUnidad = getProgramasByUnidad(); List<Planestudio> planesByPrograma = getPlanesByPrograma(); int UACid = 1; ArrayList<String> listaProgEdContar = esperadosDelegate.getSemadoroProgEdValor(UACid); //setCellStyle(style); //setExDat(sheet, 7, 1, "Concentrado de Reporte de Avance de Contenido Temtico General por Programa Educativo"); //imrpiimiremos la tabla // Definimos los encabezados de la tabla int pos = 0; int row = currow + 4; int col = 1; Boolean bandPe = false; //for (Programaeducativo pe : programasByUnidad) { for (String peContar : listaProgEdContar) { String[] contarEntEspProgEd; if (listaProgEdContar.size() >= pos) { if (peContar.contains(programa.getPednombre())) { contarEntEspProgEd = listaProgEdContar.get(pos).split("-"); setExDat(sheet, row, col, "Clave"); setExDat(sheet, row, col + 1, "Programa Educativo"); setExDat(sheet, row, col + 3, "Plan de Estudios"); setExDat(sheet, row, col + 4, "Responsable"); // if (contarEntEspProgEd[0].equalsIgnoreCase(programasByUnidad.get(0).getPednombre())) { setExDat(sheet, row + 1, col, programa.getPedclave()); setExDat(sheet, row + 1, col + 1, programa.getPednombre()); if (!(planesByPrograma.isEmpty())) { setExDat(sheet, row + 1, col + 2, planesByPrograma.get(0).getPesvigenciaPlan()); } setExDat(sheet, row + 1, col + 3, ""); // } // sheet.addMergedRegion(new CellRangeAddress(row+2,col,5,1)); setExDat(sheet, row + 2, col, "Total de RACT General de " + programa.getPednombre()); setExDat(sheet, row + 3, col, "Resumen de totales \r\n por numero de RACT"); setExDat(sheet, row + 3, col + 1, "Total entregados"); setExDat(sheet, row + 3, col + 2, "%"); setExDat(sheet, row + 3, col + 3, "Total esperados"); setExDat(sheet, row + 3, col + 4, "%"); sheet.getRow(row + 3).getCell(1);//aqui modifique jesus ruelas float porcentEnt = (Float.parseFloat(contarEntEspProgEd[1])) / (Float.parseFloat(contarEntEspProgEd[2])) * 100; float porcentEsp = 100 - porcentEnt; // setExDat(sheet, 15, 1, "Todos los RACTS"); // setExDat(sheet, 15, 2, contarEntEspProgEd[1]); // setExDat(sheet, 15, 3, "" + porcentEnt + "%"); // setExDat(sheet, 15, 4, contarEntEspProgEd[2]); // setExDat(sheet, 15, 5, "" + porcentEsp + "%"); //String[] contarEntEspProgEd; String[] contarEntRact1; String[] contarEntRact2; String[] contarEntRact3; int esperadosProgEdUnicoRact = 0; contarEntRact1 = contarEntEspProgEd[3].split(":"); contarEntRact2 = contarEntEspProgEd[4].split(":"); contarEntRact3 = contarEntEspProgEd[5].split(":"); esperadosProgEdUnicoRact = (Integer.parseInt(contarEntEspProgEd[2])) / 3; float contarPorcentEntRact1 = (Float.parseFloat(contarEntRact1[1])) / ((float) esperadosProgEdUnicoRact) * 100; float contarPorcentEntRact2 = (Float.parseFloat(contarEntRact2[1])) / ((float) esperadosProgEdUnicoRact) * 100; float contarPorcentEntRact3 = (Float.parseFloat(contarEntRact3[1])) / ((float) esperadosProgEdUnicoRact) * 100; float contarPorcentEspRact1 = 100 - contarPorcentEntRact1; float contarPorcentEspRact2 = 100 - contarPorcentEntRact2; float contarPorcentEspRact3 = 100 - contarPorcentEntRact3; setExDat(sheet, row + 4, col, "RACT 1"); setExDat(sheet, row + 4, col + 1, contarEntRact1[1]); setExDat(sheet, row + 4, col + 2, "" + contarPorcentEntRact1 + "%"); setExDat(sheet, row + 4, col + 3, esperadosProgEdUnicoRact); setExDat(sheet, row + 4, col + 4, "" + contarPorcentEspRact1 + "%"); setExDat(sheet, row + 5, col, "RACT 2"); setExDat(sheet, row + 5, col + 1, contarEntRact2[1]); setExDat(sheet, row + 5, col + 2, "" + contarPorcentEntRact2 + "%"); setExDat(sheet, row + 5, col + 3, esperadosProgEdUnicoRact); setExDat(sheet, row + 5, col + 4, "" + contarPorcentEspRact2 + "%"); setExDat(sheet, row + 6, col, "RACT 3"); setExDat(sheet, row + 6, col + 1, contarEntRact3[1]); setExDat(sheet, row + 6, col + 2, "" + contarPorcentEntRact3 + "%"); setExDat(sheet, row + 6, col + 3, esperadosProgEdUnicoRact); setExDat(sheet, row + 6, col + 4, "" + contarPorcentEspRact3 + "%"); setExDat(sheet, row + 7, col, "Todos los RACTS"); setExDat(sheet, row + 7, col + 1, contarEntEspProgEd[1]); setExDat(sheet, row + 7, col + 2, "" + porcentEnt + "%"); setExDat(sheet, row + 7, col + 3, contarEntEspProgEd[2]); setExDat(sheet, row + 7, col + 4, "" + porcentEsp + "%"); setStyleCell(sheet, headerTabla, row, col); setStyleCell(sheet, headerTabla, row, col + 1); setStyleCell(sheet, headerTabla, row, col + 2); setStyleCell(sheet, headerTabla, row, col + 3); setStyleCell(sheet, headerTabla, row, col + 4); setStyleCell(sheet, headerTabla, row + 2, col); setStyleCell(sheet, headerTabla, row + 2, col + 1); setStyleCell(sheet, headerTabla, row + 2, col + 2); setStyleCell(sheet, headerTabla, row + 2, col + 3); setStyleCell(sheet, headerTabla, row + 3, col); setStyleCell(sheet, headerTabla, row + 3, col + 1); setStyleCell(sheet, headerTabla, row + 3, col + 2); setStyleCell(sheet, headerTabla, row + 3, col + 3); setStyleCell(sheet, headerTabla, row + 3, col + 4); bandPe = true; pos++; } } // if (bandPe == true) { // if (col >= 25) { // row = row + 14; // col = -5; // } // col = col + 6; // bandPe = false; // } pos++; } //} //aqui modifique Jesus Ruelas sheet = cabezeraGeneralExcel(sheet, uabcLogo, style); } // fin del la comparacon de plan } // fin del for por plan de estudios } // fin de si es por entregados if(entregados) if (reporte.equalsIgnoreCase("noentregados")) { nombreLibro = "No Entregados " + nombreLibro; //definimos encabezado sheet = cabezeraGeneralExcel(sheet, uabcLogo, style); //mostraremos tabla con // Programas educativos // Total Racs entregados // Total Esperados //preparamos informacion para insertar List<Programaeducativo> programasByUnidad = getProgramasByUnidad(); //imrpiimiremos la tabla // Definimos los encabezados de la tabla setExDat(sheet, 13, 17, "Programa Educativo"); setExDat(sheet, 13, 18, "Total de RACT Entregados"); setExDat(sheet, 13, 19, "Total de RACT Esperados"); setExDat(sheet, 13, 20, " "); setStyleCell(sheet, headerTabla, 13, 17); setStyleCell(sheet, headerTabla, 13, 18); setStyleCell(sheet, headerTabla, 13, 19); //imprimimos la informacion en su lugar int prow = 14; for (Programaeducativo programa : programasByUnidad) { if (selectProgramEducativo.contains(programa.getPedid().toString())) { setExDat(sheet, prow, 17, programa.getPednombre()); setExDat(sheet, prow, 18, 75); setExDat(sheet, prow, 19, 100); prow++; } } //Se obtiene consulta con los parametros seleccionados ArrayList<String> entregados = esperadosDelegate.getFullProgramaEdu(unidadacademica.getUacid(), Plan, Programa, Ciclo); //agregamos programacion de grafico if (tipografico.equalsIgnoreCase("barras")) { BarChartCL demo1 = new BarChartCL("Estadsticas de Reportes Entregados por Programa Educativo", entregados, "ExcelPOIGrafica1"); demo1.addChartToExcel(demo1.createChart(demo1.createDataset()), workbook); } if (tipografico.equalsIgnoreCase("linea")) { LineChartDemo1 demo1 = new LineChartDemo1( "Estadsticas de Reportes Entregados por Programa Educativo", entregados, "ExcelPOIGrafica1"); demo1.addChartToExcel(demo1.createChart(demo1.createDataset()), workbook); } if (tipografico.equalsIgnoreCase("pastel")) { PieChartDemo1 demo1 = new PieChartDemo1( "Estadsticas de Reportes Entregados por Programa Educativo", entregados, "ExcelPOIGrafica1"); demo1.addChartToExcel(demo1.createChart(demo1.createDataset()), workbook); } //Se acaba grafico //recorreremos todo el list aux para revisar los programas educativos que utiliza //aun que podemos usar el selectProgramaeducativo /* Programacion de Hojas*/ for (Programaeducativo programa : programasByUnidad) { if (selectProgramEducativo.contains(Integer.toString(programa.getPedclave()))) { sheet = workbook.createSheet(programa.getPednombre()); sheet = cabezeraGeneralExcel(sheet, uabcLogo, style); setExDat(sheet, 8, 3, "Concentrado de Reporte de Avance de Contenido Temtico de Entregados por Programa Educativo"); boolean autotam = true; //informacion de programa educativo tabla setExDat(sheet, 14, 1, "Clave"); setExDat(sheet, 14, 2, "Programa Educativo"); setExDat(sheet, 14, 3, "Plan de Estudios"); setExDat(sheet, 14, 4, "Responsable"); setStyleCell(sheet, headerTabla, 14, 1); setStyleCell(sheet, headerTabla, 14, 2); setStyleCell(sheet, headerTabla, 14, 3); setStyleCell(sheet, headerTabla, 14, 4); //llenado de informacion // se tiene imprimir por cada plan de estudios??? setExDat(sheet, 15, 1, programa.getPedclave()); setExDat(sheet, 15, 2, programa.getPednombre()); setExDat(sheet, 15, 3, ""); setExDat(sheet, 15, 4, ""); setStyleCell(sheet, borderstabla, 15, 1); setStyleCell(sheet, borderstabla, 15, 2); setStyleCell(sheet, borderstabla, 15, 3); setStyleCell(sheet, borderstabla, 15, 4); List<Planestudio> planes = filtrosBeanHelper.getConsultaDelegate() .getPlanesByPrograma(programa.getPedid()); boolean uno = true; int currow = 20; ReporteAvanceAux tempAux = new ReporteAvanceAux(); for (ReporteAvanceAux aux : listaAux) { tempAux = aux; break; } //intentaremos usar un solo plan setExDat(sheet, currow, 1, "?reas de conocimiento"); setStyleCell(sheet, headerTabla, currow, 1); currow++; //cada plan de estudios tiene varias areas de conocimiento //List<Areaconocimiento> areasConocimiento = filtrosBeanHelper.getConsultaDelegate().getAreasByPlan( tempAux.getReporteAvance().getUnidadaprendizajeImparteProfesor().getGrupo().getPlanestudio().getPesid() ); List<Areaconocimiento> areasConocimiento = filtrosBeanHelper.getConsultaDelegate() .getAreasByPlanClave(programa.getPedclave(), tempAux.getReporteAvance().getUnidadaprendizajeImparteProfesor().getGrupo() .getPlanestudio().getPesvigenciaPlan()); for (Areaconocimiento areaCon : areasConocimiento) { //clave , area de conocimiento, responsable setExDat(sheet, currow, 1, "Clave"); setExDat(sheet, currow, 2, "?rea de conocimiento"); setExDat(sheet, currow, 3, "Responsable"); setStyleCell(sheet, headerTabla, currow, 1); setStyleCell(sheet, headerTabla, currow, 2); setStyleCell(sheet, headerTabla, currow, 3); currow++; setExDat(sheet, currow, 1, areaCon.getAcoclave()); //clave setExDat(sheet, currow, 2, areaCon.getAconombre()); //area conociminto setExDat(sheet, currow, 3, ""); //responsable currow += 2; //obtenemos las unidades de aprendisake setExDat(sheet, currow, 1, "Clave unidad de aprendizaje"); setExDat(sheet, currow, 2, "Unidad de aprendizaje"); setExDat(sheet, currow, 3, "No. de empleado"); setExDat(sheet, currow, 4, "Nombre del profesor"); setExDat(sheet, currow, 5, "Grupo"); setExDat(sheet, currow, 6, "% Avance 1er reporte"); setExDat(sheet, currow, 7, "Fecha de elaboracin 1er RACT"); setExDat(sheet, currow, 8, "% Avance 2do reporte"); setExDat(sheet, currow, 9, "Fecha de elaboracin 2do RACT"); setExDat(sheet, currow, 10, "% Avance 3er reporte"); setExDat(sheet, currow, 11, "Fecha de elaboracin 3er RACT"); //autosize para la columna if (autotam) { sheet.autoSizeColumn(2); sheet.autoSizeColumn(4); sheet.autoSizeColumn(11); autotam = false; } // para formatear toda la linea for (int i = 1; i <= 11; i++) { //tenemos qu aajustar texto //tenemos que centrar el texto //cambiar el color de fondo setStyleCell(sheet, headerTabla, currow, i); } currow++; //List<Unidadaprendizaje> unidadesAprendisaje = filtrosBeanHelper.getConsultaDelegate().getUnidadByArea(areaCon.getAcoclave()); //de ls listaAux separamos los los reportes que correspondan a la unidad de aprendisaje acutal ArrayList<ReporteAvanceAux> tempListAux = new ArrayList<ReporteAvanceAux>(); for (ReporteAvanceAux aux : listaAux) { if (aux.getAreaConocimiento() != null && aux.getAreaConocimiento().getAcoclave() == areaCon.getAcoclave() && programa.getPedclave() == aux.getReporteAvance() .getUnidadaprendizajeImparteProfesor().getGrupo().getPlanestudio() .getProgramaeducativo().getPedclave()) { tempListAux.add(aux); } } //List<UnidadaprendizajeImparteProfesor> unidadesProfesor = filtrosBeanHelper.getConsultaDelegate().getUnidadesAprendisajeImparteProf(unidadApren.getUapid()); int uniprofeTemp = 0; for (ReporteAvanceAux auxRacs : tempListAux) { String nompreP = auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getProfesor().getPronombre(); String apellidoPP = auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getProfesor().getProapellidoPaterno(); String apellidoPM = auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getProfesor().getProapellidoMaterno(); int claveUnidadApren = auxRacs.getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUnidadaprendizaje().getUapclave(); String nombreUnidad = auxRacs.getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUnidadaprendizaje() .getUapnombre(); uniprofeTemp = claveUnidadApren; setExDat(sheet, currow, 1, claveUnidadApren); //clave setExDat(sheet, currow, 2, nombreUnidad); //nombre unidad //reporteAvance.unidadaprendizajeImparteProfesor.profesor.pronumeroEmpleado setExDat(sheet, currow, 3, auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getProfesor().getPronumeroEmpleado()); //numero empleado setExDat(sheet, currow, 4, apellidoPP + " " + apellidoPM + " " + nompreP); //nombre maestro setExDat(sheet, currow, 5, auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor().getGrupo() .getGponumero() + "-" + auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getUiptipoSubgrupo() + "-" + auxRacs.getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUipsubgrupo()); //grupo numero //marcamos bordes for (int i = 6; i <= 11; i++) { setExDat(sheet, currow, i, " "); } for (int i = 1; i <= 11; i++) { setStyleCell(sheet, borderstabla, currow, i); } //hasta aqui comente Jesus Ruelas - 4 nov 2015 // //tienne que ser entregados/enviados // if(auxRacs.getStatusRact1()!=null){ // // System.out.println("Entro a enviados 1"); // setExDat(sheet, currow,(4 + (1*2)), auxRacs.getPorcentAvanceRact1() ); //% avance 7** // setExDat(sheet, currow,(4 + (1*2)+1), auxRacs.getFechaElaboracRact1().toString() ); //fecha elabora 8*** // setStyleCell(sheet, borderstabla, currow, (4 + (1*2))); // setStyleCell(sheet, borderstabla, currow, (4 + (1*2)+1)); // // } // else{ // setExDat(sheet, currow,(4 + (1*2)), " " ); //% avance 7** // setExDat(sheet, currow,(4 + (1*2)+1), " " ); //fecha elabora 8*** // setStyleCell(sheet, borderstabla, currow, (4 + (1*2))); // setStyleCell(sheet, borderstabla, currow, (4 + (1*2)+1)); // } // // // if(auxRacs.getStatusRact2()!=null){ // // setExDat(sheet, currow,(4 + (2*2)), auxRacs.getPorcentAvanceRact2() ); //% avance 7** // setExDat(sheet, currow,(4 + (2*2)+1), auxRacs.getFechaElaboracRact2().toString() ); //fecha elabora 8*** // setStyleCell(sheet, borderstabla, currow, (4 + (2*2))); // setStyleCell(sheet, borderstabla, currow, (4 + (2*2)+1)); // // }else{ // setExDat(sheet, currow,(4 + (2*2)), " " ); //% avance 7** // setExDat(sheet, currow,(4 + (2*2)+1), " " ); //fecha elabora 8*** // setStyleCell(sheet, borderstabla, currow, (4 + (2*2))); // setStyleCell(sheet, borderstabla, currow, (4 + (2*2)+1)); // } // // if(auxRacs.getStatusRact3()!=null){ // setExDat(sheet, currow,(4 + (3*2)), auxRacs.getPorcentAvanceRact3() ); //% avance 7** // setExDat(sheet, currow,(4 + (3*2)+1), auxRacs.getFechaElaboracRact3().toString() ); //fecha elabora 8*** // setStyleCell(sheet, borderstabla, currow, (4 + (3*2))); // setStyleCell(sheet, borderstabla, currow, (4 + (3*2)+1)); // } // else{ // setExDat(sheet, currow,(4 + (3*2)), " " ); //% avance 7** // setExDat(sheet, currow,(4 + (3*2)+1), " " ); //fecha elabora 8*** // setStyleCell(sheet, borderstabla, currow, (4 + (3*2))); // setStyleCell(sheet, borderstabla, currow, (4 + (3*2)+1)); // } //hasta aqui comente Jesus Ruelas - 4 nov 2015 //tienne que ser entregados/enviados if (auxRacs.getStatusRact1() != null) { System.out.println("Entro a enviados 1"); setExDat(sheet, currow, (4 + (1 * 2)), auxRacs.getPorcentAvanceRact1()); //% avance 7** if (auxRacs.getFechaElaboracRact1() != null) { setExDat(sheet, currow, (4 + (1 * 2) + 1), auxRacs.getFechaElaboracRact1().toString()); //fecha elabora 8*** } else { setExDat(sheet, currow, (4 + (1 * 2) + 1), " "); //fecha elabora 8*** } setStyleCell(sheet, borderstabla, currow, (4 + (1 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (1 * 2) + 1)); } else { setExDat(sheet, currow, (4 + (1 * 2)), " "); //% avance 7** setExDat(sheet, currow, (4 + (1 * 2) + 1), " "); //fecha elabora 8*** setStyleCell(sheet, borderstabla, currow, (4 + (1 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (1 * 2) + 1)); } if (auxRacs.getStatusRact2() != null) { setExDat(sheet, currow, (4 + (2 * 2)), auxRacs.getPorcentAvanceRact2()); //% avance 7** if (auxRacs.getFechaElaboracRact2() != null) { setExDat(sheet, currow, (4 + (2 * 2) + 1), auxRacs.getFechaElaboracRact2().toString()); //fecha elabora 8*** } else { setExDat(sheet, currow, (4 + (2 * 2) + 1), " "); //fecha elabora 8*** } setStyleCell(sheet, borderstabla, currow, (4 + (2 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (2 * 2) + 1)); } else { setExDat(sheet, currow, (4 + (2 * 2)), " "); //% avance 7** setExDat(sheet, currow, (4 + (2 * 2) + 1), " "); //fecha elabora 8*** setStyleCell(sheet, borderstabla, currow, (4 + (2 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (2 * 2) + 1)); } if (auxRacs.getStatusRact3() != null) { setExDat(sheet, currow, (4 + (3 * 2)), auxRacs.getPorcentAvanceRact3()); //% avance 7** if (auxRacs.getFechaElaboracRact3() != null) { setExDat(sheet, currow, (4 + (3 * 2) + 1), auxRacs.getFechaElaboracRact3().toString()); //fecha elabora 8*** } else { setExDat(sheet, currow, (4 + (3 * 2) + 1), " "); //fecha elabora 8*** } setStyleCell(sheet, borderstabla, currow, (4 + (3 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (3 * 2) + 1)); } else { setExDat(sheet, currow, (4 + (3 * 2)), " "); //% avance 7** setExDat(sheet, currow, (4 + (3 * 2) + 1), " "); //fecha elabora 8*** setStyleCell(sheet, borderstabla, currow, (4 + (3 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (3 * 2) + 1)); } currow++; } currow += 2; } //aqui modifique Jesus Ruelas - No entregados //aqui cambie headerTabla.setWrapText(true); //aqui cambie //aqui cambie jesus ruelas //for(int i=0;i<28;i++){ // sheet.autoSizeColumn(1); // sheet.autoSizeColumn(2); // sheet.autoSizeColumn(3); // sheet.autoSizeColumn(4); //merge cells de Total de RACT General de + ProgEd // sheet.addMergedRegion(new CellRangeAddress(13,13,1,5)); // sheet.addMergedRegion(new CellRangeAddress(13,13,7,11)); // sheet.addMergedRegion(new CellRangeAddress(13,13,13,17)); // sheet.addMergedRegion(new CellRangeAddress(13,13,19,23)); // sheet.addMergedRegion(new CellRangeAddress(13,13,25,29)); //} //aqui cambie jesus ruelas //mostraremos tabla con // Programas educativos // Total Racs entregados // Total Esperados //preparamos informacion para insertar // List<Programaeducativo> programasByUnidad = getProgramasByUnidad(); List<Planestudio> planesByPrograma = getPlanesByPrograma(); int UACid = 1; ArrayList<String> listaProgEdContar = esperadosDelegate.getSemadoroProgEdValor(UACid); //setCellStyle(style); //setExDat(sheet, 7, 1, "Concentrado de Reporte de Avance de Contenido Temtico General por Programa Educativo"); //imrpiimiremos la tabla // Definimos los encabezados de la tabla int pos = 0; int row = currow + 4; int col = 1; Boolean bandPe = false; //for (Programaeducativo pe : programasByUnidad) { for (String peContar : listaProgEdContar) { String[] contarEntEspProgEd; if (listaProgEdContar.size() >= pos) { if (peContar.contains(programa.getPednombre())) { contarEntEspProgEd = listaProgEdContar.get(pos).split("-"); setExDat(sheet, row, col, "Clave"); setExDat(sheet, row, col + 1, "Programa Educativo"); setExDat(sheet, row, col + 3, "Plan de Estudios"); setExDat(sheet, row, col + 4, "Responsable"); // if (contarEntEspProgEd[0].equalsIgnoreCase(programasByUnidad.get(0).getPednombre())) { setExDat(sheet, row + 1, col, programa.getPedclave()); setExDat(sheet, row + 1, col + 1, programa.getPednombre()); if (!(planesByPrograma.isEmpty())) { setExDat(sheet, row + 1, col + 2, planesByPrograma.get(0).getPesvigenciaPlan()); } setExDat(sheet, row + 1, col + 3, ""); // } // sheet.addMergedRegion(new CellRangeAddress(row+2,col,5,1)); setExDat(sheet, row + 2, col, "Total de RACT General de " + programa.getPednombre()); setExDat(sheet, row + 3, col, "Resumen de totales \r\n por numero de RACT"); setExDat(sheet, row + 3, col + 1, "Total no entregados"); setExDat(sheet, row + 3, col + 2, "%"); setExDat(sheet, row + 3, col + 3, "Total esperados"); setExDat(sheet, row + 3, col + 4, "%"); sheet.getRow(row + 3).getCell(1);//aqui modifique jesus ruelas float porcentEnt = (Float.parseFloat(contarEntEspProgEd[1])) / (Float.parseFloat(contarEntEspProgEd[2])) * 100; float porcentNoEnt = ((Float.parseFloat(contarEntEspProgEd[2])) - (Float.parseFloat(contarEntEspProgEd[1]))) / (Float.parseFloat(contarEntEspProgEd[2])) * 100; float porcentEsp = 100 - porcentEnt; // setExDat(sheet, 15, 1, "Todos los RACTS"); // setExDat(sheet, 15, 2, contarEntEspProgEd[1]); // setExDat(sheet, 15, 3, "" + porcentEnt + "%"); // setExDat(sheet, 15, 4, contarEntEspProgEd[2]); // setExDat(sheet, 15, 5, "" + porcentEsp + "%"); //String[] contarEntEspProgEd; String[] contarEntRact1; String[] contarEntRact2; String[] contarEntRact3; int esperadosProgEdUnicoRact = 0; contarEntRact1 = contarEntEspProgEd[3].split(":"); contarEntRact2 = contarEntEspProgEd[4].split(":"); contarEntRact3 = contarEntEspProgEd[5].split(":"); esperadosProgEdUnicoRact = (Integer.parseInt(contarEntEspProgEd[2])) / 3; int contarNoEntRact1 = esperadosProgEdUnicoRact - (Integer.parseInt(contarEntRact1[1])); int contarNoEntRact2 = esperadosProgEdUnicoRact - (Integer.parseInt(contarEntRact2[1])); int contarNoEntRact3 = esperadosProgEdUnicoRact - (Integer.parseInt(contarEntRact3[1])); float contarPorcentEntRact1 = (Float.parseFloat(contarEntRact1[1])) / ((float) esperadosProgEdUnicoRact) * 100; float contarPorcentEntRact2 = (Float.parseFloat(contarEntRact2[1])) / ((float) esperadosProgEdUnicoRact) * 100; float contarPorcentEntRact3 = (Float.parseFloat(contarEntRact3[1])) / ((float) esperadosProgEdUnicoRact) * 100; float contarPorcentNoEntRact1 = (((float) esperadosProgEdUnicoRact) - (Float.parseFloat(contarEntRact1[1]))) / ((float) esperadosProgEdUnicoRact) * 100; float contarPorcentNoEntRact2 = (((float) esperadosProgEdUnicoRact) - (Float.parseFloat(contarEntRact2[1]))) / ((float) esperadosProgEdUnicoRact) * 100; float contarPorcentNoEntRact3 = (((float) esperadosProgEdUnicoRact) - (Float.parseFloat(contarEntRact3[1]))) / ((float) esperadosProgEdUnicoRact) * 100; float contarPorcentEspRact1 = 100 - contarPorcentEntRact1; float contarPorcentEspRact2 = 100 - contarPorcentEntRact2; float contarPorcentEspRact3 = 100 - contarPorcentEntRact3; setExDat(sheet, row + 4, col, "RACT 1"); setExDat(sheet, row + 4, col + 1, contarNoEntRact1); setExDat(sheet, row + 4, col + 2, "" + contarPorcentNoEntRact1 + "%"); setExDat(sheet, row + 4, col + 3, esperadosProgEdUnicoRact); setExDat(sheet, row + 4, col + 4, "" + contarPorcentEspRact1 + "%"); setExDat(sheet, row + 5, col, "RACT 2"); setExDat(sheet, row + 5, col + 1, contarNoEntRact2); setExDat(sheet, row + 5, col + 2, "" + contarPorcentNoEntRact2 + "%"); setExDat(sheet, row + 5, col + 3, esperadosProgEdUnicoRact); setExDat(sheet, row + 5, col + 4, "" + contarPorcentEspRact2 + "%"); setExDat(sheet, row + 6, col, "RACT 3"); setExDat(sheet, row + 6, col + 1, contarNoEntRact3); setExDat(sheet, row + 6, col + 2, "" + contarPorcentNoEntRact3 + "%"); setExDat(sheet, row + 6, col + 3, esperadosProgEdUnicoRact); setExDat(sheet, row + 6, col + 4, "" + contarPorcentEspRact3 + "%"); int totalNoEnt = (Integer.parseInt(contarEntEspProgEd[2]) - (Integer.parseInt(contarEntEspProgEd[1]))); setExDat(sheet, row + 7, col, "Todos los RACTS"); setExDat(sheet, row + 7, col + 1, "" + (Integer.parseInt(contarEntEspProgEd[2]) - (Integer.parseInt(contarEntEspProgEd[1])))); setExDat(sheet, row + 7, col + 2, "" + porcentNoEnt + "%"); setExDat(sheet, row + 7, col + 3, contarEntEspProgEd[2]); setExDat(sheet, row + 7, col + 4, "" + porcentEsp + "%"); setStyleCell(sheet, headerTabla, row, col); setStyleCell(sheet, headerTabla, row, col + 1); setStyleCell(sheet, headerTabla, row, col + 2); setStyleCell(sheet, headerTabla, row, col + 3); setStyleCell(sheet, headerTabla, row, col + 4); setStyleCell(sheet, headerTabla, row + 2, col); setStyleCell(sheet, headerTabla, row + 2, col + 1); setStyleCell(sheet, headerTabla, row + 2, col + 2); setStyleCell(sheet, headerTabla, row + 2, col + 3); setStyleCell(sheet, headerTabla, row + 3, col); setStyleCell(sheet, headerTabla, row + 3, col + 1); setStyleCell(sheet, headerTabla, row + 3, col + 2); setStyleCell(sheet, headerTabla, row + 3, col + 3); setStyleCell(sheet, headerTabla, row + 3, col + 4); bandPe = true; pos++; } } // if (bandPe == true) { // if (col >= 25) { // row = row + 14; // col = -5; // } // col = col + 6; // bandPe = false; // } pos++; } //} //aqui modifique Jesus Ruelas } // fin del la comparacon de plan } // fin del for por plan de estudios } // fin de si es por entregados if(entregados) if (reporte.equalsIgnoreCase("entregadosynoentregados")) { nombreLibro = "Entregados y No Entregados " + nombreLibro; //definimos encabezado sheet = cabezeraGeneralExcel(sheet, uabcLogo, style); //mostraremos tabla con // Programas educativos // Total Racs entregados // Total Esperados //preparamos informacion para insertar List<Programaeducativo> programasByUnidad = getProgramasByUnidad(); //imrpiimiremos la tabla // Definimos los encabezados de la tabla setExDat(sheet, 13, 17, "Programa Educativo"); setExDat(sheet, 13, 18, "Total de RACT Entregados"); setExDat(sheet, 13, 19, "Total de RACT Esperados"); setExDat(sheet, 13, 20, " "); setStyleCell(sheet, headerTabla, 13, 17); setStyleCell(sheet, headerTabla, 13, 18); setStyleCell(sheet, headerTabla, 13, 19); /*sheet.autoSizeColumn(2); sheet.autoSizeColumn(3); sheet.autoSizeColumn(4);*/ //imprimimos la informacion en su lugar int prow = 14; for (Programaeducativo programa : programasByUnidad) { if (selectProgramEducativo.contains(programa.getPedid().toString())) { setExDat(sheet, prow, 17, programa.getPednombre()); setExDat(sheet, prow, 18, 75); setExDat(sheet, prow, 19, 100); prow++; } } //Se obtiene consulta con los parametros seleccionados ArrayList<String> entregados = esperadosDelegate.getFullProgramaEdu(unidadacademica.getUacid(), Plan, Programa, Ciclo); //agregamos programacion de grafico if (tipografico.equalsIgnoreCase("barras")) { BarChartCL demo1 = new BarChartCL("Estadsticas de Reportes Entregados por Programa Educativo", entregados, "ExcelPOIGrafica1"); demo1.addChartToExcel(demo1.createChart(demo1.createDataset()), workbook); } if (tipografico.equalsIgnoreCase("linea")) { LineChartDemo1 demo1 = new LineChartDemo1( "Estadsticas de Reportes Entregados por Programa Educativo", entregados, "ExcelPOIGrafica1"); demo1.addChartToExcel(demo1.createChart(demo1.createDataset()), workbook); } if (tipografico.equalsIgnoreCase("pastel")) { PieChartDemo1 demo1 = new PieChartDemo1( "Estadsticas de Reportes Entregados por Programa Educativo", entregados, "ExcelPOIGrafica1"); demo1.addChartToExcel(demo1.createChart(demo1.createDataset()), workbook); } //Se acaba grafico //recorreremos todo el list aux para revisar los programas educativos que utiliza //aun que podemos usar el selectProgramaeducativo /* Programacion de Hojas*/ for (Programaeducativo programa : programasByUnidad) { if (selectProgramEducativo.contains(Integer.toString(programa.getPedclave()))) { sheet = workbook.createSheet(programa.getPednombre()); sheet = cabezeraGeneralExcel(sheet, uabcLogo, style); setExDat(sheet, 8, 3, "Concentrado de Reporte de Avance de Contenido Temtico de Entregados por Programa Educativo"); boolean autotam = true; //informacion de programa educativo tabla setExDat(sheet, 14, 1, "Clave"); setExDat(sheet, 14, 2, "Programa Educativo"); setExDat(sheet, 14, 3, "Plan de Estudios"); setExDat(sheet, 14, 4, "Responsable"); setStyleCell(sheet, headerTabla, 14, 1); setStyleCell(sheet, headerTabla, 14, 2); setStyleCell(sheet, headerTabla, 14, 3); setStyleCell(sheet, headerTabla, 14, 4); //llenado de informacion // se tiene imprimir por cada plan de estudios??? // Responsableprogramaeducativo responsable = filtrosBeanHelper.getConsultaDelegate().getResponsableProgramaEducativoByID(programa.getPedid()); setExDat(sheet, 15, 1, programa.getPedclave()); setExDat(sheet, 15, 2, programa.getPednombre()); setExDat(sheet, 15, 3, ""); setExDat(sheet, 15, 4, ""); setStyleCell(sheet, borderstabla, 15, 1); setStyleCell(sheet, borderstabla, 15, 2); setStyleCell(sheet, borderstabla, 15, 3); setStyleCell(sheet, borderstabla, 15, 4); List<Planestudio> planes = filtrosBeanHelper.getConsultaDelegate() .getPlanesByPrograma(programa.getPedid()); boolean uno = true; int currow = 20; ReporteAvanceAux tempAux = new ReporteAvanceAux(); for (ReporteAvanceAux aux : listaAux) { tempAux = aux; break; } //intentaremos usar un solo plan setExDat(sheet, currow, 1, "?reas de conocimiento"); setStyleCell(sheet, headerTabla, currow, 1); currow++; //cada plan de estudios tiene varias areas de conocimiento //List<Areaconocimiento> areasConocimiento = filtrosBeanHelper.getConsultaDelegate().getAreasByPlan( tempAux.getReporteAvance().getUnidadaprendizajeImparteProfesor().getGrupo().getPlanestudio().getPesid() ); List<Areaconocimiento> areasConocimiento = filtrosBeanHelper.getConsultaDelegate() .getAreasByPlanClave(programa.getPedclave(), tempAux.getReporteAvance().getUnidadaprendizajeImparteProfesor().getGrupo() .getPlanestudio().getPesvigenciaPlan()); for (Areaconocimiento areaCon : areasConocimiento) { //clave , area de conocimiento, responsable setExDat(sheet, currow, 1, "Clave"); setExDat(sheet, currow, 2, "?rea de conocimiento"); setExDat(sheet, currow, 3, "Responsable"); setStyleCell(sheet, headerTabla, currow, 1); setStyleCell(sheet, headerTabla, currow, 2); setStyleCell(sheet, headerTabla, currow, 3); currow++; setExDat(sheet, currow, 1, areaCon.getAcoclave()); //clave setExDat(sheet, currow, 2, areaCon.getAconombre()); //area conociminto setExDat(sheet, currow, 3, ""); //responsable currow += 2; //obtenemos las unidades de aprendisake setExDat(sheet, currow, 1, "Clave unidad de aprendizaje"); setExDat(sheet, currow, 2, "Unidad de aprendizaje"); setExDat(sheet, currow, 3, "No. de empleado"); setExDat(sheet, currow, 4, "Nombre del profesor"); setExDat(sheet, currow, 5, "Grupo"); setExDat(sheet, currow, 6, "% Avance 1er reporte"); setExDat(sheet, currow, 7, "Fecha de elaboracin 1er RACT"); setExDat(sheet, currow, 8, "% Avance 2do reporte"); setExDat(sheet, currow, 9, "Fecha de elaboracin 2do RACT"); setExDat(sheet, currow, 10, "% Avance 3er reporte"); setExDat(sheet, currow, 11, "Fecha de elaboracin 3er RACT"); if (autotam) { sheet.autoSizeColumn(2); sheet.autoSizeColumn(4); sheet.autoSizeColumn(11); autotam = false; } // para formatear toda la linea for (int i = 1; i <= 11; i++) { //tenemos qu aajustar texto //tenemos que centrar el texto //cambiar el color de fondo setStyleCell(sheet, headerTabla, currow, i); } currow++; //List<Unidadaprendizaje> unidadesAprendisaje = filtrosBeanHelper.getConsultaDelegate().getUnidadByArea(areaCon.getAcoclave()); //de ls listaAux separamos los los reportes que correspondan a la unidad de aprendisaje acutal ArrayList<ReporteAvanceAux> tempListAux = new ArrayList<ReporteAvanceAux>(); for (ReporteAvanceAux aux : listaAux) { if (aux.getAreaConocimiento() != null && aux.getAreaConocimiento().getAcoclave() == areaCon.getAcoclave() && programa.getPedclave() == aux.getReporteAvance() .getUnidadaprendizajeImparteProfesor().getGrupo().getPlanestudio() .getProgramaeducativo().getPedclave()) { tempListAux.add(aux); } } //List<UnidadaprendizajeImparteProfesor> unidadesProfesor = filtrosBeanHelper.getConsultaDelegate().getUnidadesAprendisajeImparteProf(unidadApren.getUapid()); int uniprofeTemp = 0; for (ReporteAvanceAux auxRacs : tempListAux) { //marcamos bordes for (int i = 1; i <= 11; i++) { setExDat(sheet, currow, i, " "); } for (int i = 1; i <= 11; i++) { setStyleCell(sheet, borderstabla, currow, i); } String nompreP = auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getProfesor().getPronombre(); String apellidoPP = auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getProfesor().getProapellidoPaterno(); String apellidoPM = auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getProfesor().getProapellidoMaterno(); int claveUnidadApren = auxRacs.getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUnidadaprendizaje().getUapclave(); String nombreUnidad = auxRacs.getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUnidadaprendizaje() .getUapnombre(); uniprofeTemp = claveUnidadApren; setExDat(sheet, currow, 1, claveUnidadApren); //clave setExDat(sheet, currow, 2, nombreUnidad); //nombre unidad //reporteAvance.unidadaprendizajeImparteProfesor.profesor.pronumeroEmpleado setExDat(sheet, currow, 3, auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getProfesor().getPronumeroEmpleado()); //numero empleado setExDat(sheet, currow, 4, apellidoPP + " " + apellidoPM + " " + nompreP); //nombre maestro setExDat(sheet, currow, 5, auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor().getGrupo() .getGponumero() + "-" + auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getUiptipoSubgrupo() + "-" + auxRacs.getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUipsubgrupo()); //grupo numero //marcamos bordes for (int i = 1; i <= 5; i++) { setStyleCell(sheet, borderstabla, currow, i); } //tienne que ser entregados/enviados setExDat(sheet, currow, (4 + (1 * 2)), auxRacs.getPorcentAvanceRact1()); //% avance 7** if (auxRacs.getFechaElaboracRact1() != null) { setExDat(sheet, currow, (4 + (1 * 2) + 1), auxRacs.getFechaElaboracRact1().toString()); //fecha elabora 8*** } setStyleCell(sheet, borderstabla, currow, (4 + (1 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (1 * 2) + 1)); setExDat(sheet, currow, (4 + (2 * 2)), auxRacs.getPorcentAvanceRact2()); //% avance 7** if (auxRacs.getFechaElaboracRact2() != null) { setExDat(sheet, currow, (4 + (2 * 2) + 1), auxRacs.getFechaElaboracRact2().toString()); //fecha elabora 8*** } setStyleCell(sheet, borderstabla, currow, (4 + (2 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (2 * 2) + 1)); setExDat(sheet, currow, (4 + (3 * 2)), auxRacs.getPorcentAvanceRact3()); //% avance 7** if (auxRacs.getFechaElaboracRact3() != null) { setExDat(sheet, currow, (4 + (3 * 2) + 1), auxRacs.getFechaElaboracRact3().toString()); //fecha elabora 8*** } setStyleCell(sheet, borderstabla, currow, (4 + (3 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (3 * 2) + 1)); currow++; } currow += 2; } //aqui modifique Jesus Ruelas - Entregados Y No Entregados //aqui cambie headerTabla.setWrapText(true); //aqui cambie //aqui cambie jesus ruelas //for(int i=0;i<28;i++){ // sheet.autoSizeColumn(1); // sheet.autoSizeColumn(2); // sheet.autoSizeColumn(3); // sheet.autoSizeColumn(4); //merge cells de Total de RACT General de + ProgEd // sheet.addMergedRegion(new CellRangeAddress(13,13,1,5)); // sheet.addMergedRegion(new CellRangeAddress(13,13,7,11)); // sheet.addMergedRegion(new CellRangeAddress(13,13,13,17)); // sheet.addMergedRegion(new CellRangeAddress(13,13,19,23)); // sheet.addMergedRegion(new CellRangeAddress(13,13,25,29)); //} //aqui cambie jesus ruelas //mostraremos tabla con // Programas educativos // Total Racs entregados // Total Esperados //preparamos informacion para insertar // List<Programaeducativo> programasByUnidad = getProgramasByUnidad(); List<Planestudio> planesByPrograma = getPlanesByPrograma(); int UACid = 1; ArrayList<String> listaProgEdContar = esperadosDelegate.getSemadoroProgEdValor(UACid); //setCellStyle(style); //setExDat(sheet, 7, 1, "Concentrado de Reporte de Avance de Contenido Temtico General por Programa Educativo"); //imrpiimiremos la tabla // Definimos los encabezados de la tabla int pos = 0; int row = currow + 4; int col = 1; Boolean bandPe = false; //for (Programaeducativo pe : programasByUnidad) { for (String peContar : listaProgEdContar) { String[] contarEntEspProgEd; if (listaProgEdContar.size() >= pos) { if (peContar.contains(programa.getPednombre())) { contarEntEspProgEd = listaProgEdContar.get(pos).split("-"); setExDat(sheet, row, col, "Clave"); setExDat(sheet, row, col + 1, "Programa Educativo"); setExDat(sheet, row, col + 3, "Plan de Estudios"); setExDat(sheet, row, col + 4, "Responsable"); // if (contarEntEspProgEd[0].equalsIgnoreCase(programasByUnidad.get(0).getPednombre())) { setExDat(sheet, row + 1, col, programa.getPedclave()); setExDat(sheet, row + 1, col + 1, programa.getPednombre()); if (!(planesByPrograma.isEmpty())) { setExDat(sheet, row + 1, col + 2, planesByPrograma.get(0).getPesvigenciaPlan()); } setExDat(sheet, row + 1, col + 3, ""); // } // sheet.addMergedRegion(new CellRangeAddress(row+2,col,5,1)); setExDat(sheet, row + 2, col, "Total de RACT General de " + programa.getPednombre()); setExDat(sheet, row + 3, col, "Resumen de totales \r\n por numero de RACT"); setExDat(sheet, row + 3, col + 1, "Total entregados"); setExDat(sheet, row + 3, col + 2, "%"); setExDat(sheet, row + 3, col + 3, "Total esperados"); setExDat(sheet, row + 3, col + 4, "%"); sheet.getRow(row + 3).getCell(1);//aqui modifique jesus ruelas float porcentEnt = (Float.parseFloat(contarEntEspProgEd[1])) / (Float.parseFloat(contarEntEspProgEd[2])) * 100; float porcentEsp = 100 - porcentEnt; // setExDat(sheet, 15, 1, "Todos los RACTS"); // setExDat(sheet, 15, 2, contarEntEspProgEd[1]); // setExDat(sheet, 15, 3, "" + porcentEnt + "%"); // setExDat(sheet, 15, 4, contarEntEspProgEd[2]); // setExDat(sheet, 15, 5, "" + porcentEsp + "%"); //String[] contarEntEspProgEd; String[] contarEntRact1; String[] contarEntRact2; String[] contarEntRact3; int esperadosProgEdUnicoRact = 0; contarEntRact1 = contarEntEspProgEd[3].split(":"); contarEntRact2 = contarEntEspProgEd[4].split(":"); contarEntRact3 = contarEntEspProgEd[5].split(":"); esperadosProgEdUnicoRact = (Integer.parseInt(contarEntEspProgEd[2])) / 3; float contarPorcentEntRact1 = (Float.parseFloat(contarEntRact1[1])) / ((float) esperadosProgEdUnicoRact) * 100; float contarPorcentEntRact2 = (Float.parseFloat(contarEntRact2[1])) / ((float) esperadosProgEdUnicoRact) * 100; float contarPorcentEntRact3 = (Float.parseFloat(contarEntRact3[1])) / ((float) esperadosProgEdUnicoRact) * 100; float contarPorcentEspRact1 = 100 - contarPorcentEntRact1; float contarPorcentEspRact2 = 100 - contarPorcentEntRact2; float contarPorcentEspRact3 = 100 - contarPorcentEntRact3; setExDat(sheet, row + 4, col, "RACT 1"); setExDat(sheet, row + 4, col + 1, contarEntRact1[1]); setExDat(sheet, row + 4, col + 2, "" + contarPorcentEntRact1 + "%"); setExDat(sheet, row + 4, col + 3, esperadosProgEdUnicoRact); setExDat(sheet, row + 4, col + 4, "" + contarPorcentEspRact1 + "%"); setExDat(sheet, row + 5, col, "RACT 2"); setExDat(sheet, row + 5, col + 1, contarEntRact2[1]); setExDat(sheet, row + 5, col + 2, "" + contarPorcentEntRact2 + "%"); setExDat(sheet, row + 5, col + 3, esperadosProgEdUnicoRact); setExDat(sheet, row + 5, col + 4, "" + contarPorcentEspRact2 + "%"); setExDat(sheet, row + 6, col, "RACT 3"); setExDat(sheet, row + 6, col + 1, contarEntRact3[1]); setExDat(sheet, row + 6, col + 2, "" + contarPorcentEntRact3 + "%"); setExDat(sheet, row + 6, col + 3, esperadosProgEdUnicoRact); setExDat(sheet, row + 6, col + 4, "" + contarPorcentEspRact3 + "%"); setExDat(sheet, row + 7, col, "Todos los RACTS"); setExDat(sheet, row + 7, col + 1, contarEntEspProgEd[1]); setExDat(sheet, row + 7, col + 2, "" + porcentEnt + "%"); setExDat(sheet, row + 7, col + 3, contarEntEspProgEd[2]); setExDat(sheet, row + 7, col + 4, "" + porcentEsp + "%"); setStyleCell(sheet, headerTabla, row, col); setStyleCell(sheet, headerTabla, row, col + 1); setStyleCell(sheet, headerTabla, row, col + 2); setStyleCell(sheet, headerTabla, row, col + 3); setStyleCell(sheet, headerTabla, row, col + 4); setStyleCell(sheet, headerTabla, row + 2, col); setStyleCell(sheet, headerTabla, row + 2, col + 1); setStyleCell(sheet, headerTabla, row + 2, col + 2); setStyleCell(sheet, headerTabla, row + 2, col + 3); setStyleCell(sheet, headerTabla, row + 3, col); setStyleCell(sheet, headerTabla, row + 3, col + 1); setStyleCell(sheet, headerTabla, row + 3, col + 2); setStyleCell(sheet, headerTabla, row + 3, col + 3); setStyleCell(sheet, headerTabla, row + 3, col + 4); bandPe = true; pos++; } } // if (bandPe == true) { // if (col >= 25) { // row = row + 14; // col = -5; // } // col = col + 6; // bandPe = false; // } pos++; } //} //aqui modifique Jesus Ruelas } // fin del la comparacon de plan } // fin del for por plan de estudios } // fin de si es por entregados y no entregados if(entregados) if (reporte.equalsIgnoreCase("entregadosatiempo") || reporte.equalsIgnoreCase("entregadosenfechalimite") || reporte.equalsIgnoreCase("entregadosdespueslimite") || reporte.equalsIgnoreCase("entregadosdespueslimite") || reporte.equalsIgnoreCase("entregadosatiempoenfechalimiteydespues")) { if (reporte.equalsIgnoreCase("entregadosatiempo")) { System.out.println("Entro a entregados a tiempo"); nombreLibro = "Entregados a Tiempo " + nombreLibro; } if (reporte.equalsIgnoreCase("entregadosenfechalimite")) { System.out.println("Entro a entregados en fecha"); nombreLibro = "Entregados en Fecha Limite " + nombreLibro; } if (reporte.equalsIgnoreCase("entregadosdespueslimite")) { System.out.println("Entro a entregados despues"); nombreLibro = "Entregados despues Fecha Limite" + nombreLibro; } if (reporte.equalsIgnoreCase("entregadosatiempoenfechalimiteydespues")) { System.out.println("Entro a entregados a tiempo"); nombreLibro = "Entregados a Tiempo-Limite-Despues de Fecha " + nombreLibro; } //mostraremos tabla con // Programas educativos // Total Racs entregados // Total Esperados //preparamos informacion para insertar List<Programaeducativo> programasByUnidad = getProgramasByUnidad(); //imrpiimiremos la tabla // Definimos los encabezados de la tabla setExDat(sheet, 13, 17, "Programa Educativo"); setExDat(sheet, 13, 18, "Total de RACT Entregados"); setExDat(sheet, 13, 19, "Total de RACT Esperados"); setExDat(sheet, 13, 20, " "); setStyleCell(sheet, headerTabla, 13, 17); setStyleCell(sheet, headerTabla, 13, 18); setStyleCell(sheet, headerTabla, 13, 19); /*sheet.autoSizeColumn(2); sheet.autoSizeColumn(3); sheet.autoSizeColumn(4);*/ //imprimimos la informacion en su lugar int prow = 14; for (Programaeducativo programa : programasByUnidad) { if (selectProgramEducativo.contains(programa.getPedid().toString())) { setExDat(sheet, prow, 17, programa.getPednombre()); setExDat(sheet, prow, 18, 75); setExDat(sheet, prow, 19, 100); prow++; } } //Se obtiene consulta con los parametros seleccionados ArrayList<String> entregados = esperadosDelegate.getFullProgramaEdu(unidadacademica.getUacid(), Plan, Programa, Ciclo); //agregamos programacion de grafico if (tipografico.equalsIgnoreCase("barras")) { BarChartCL demo1 = new BarChartCL("Estadsticas de Reportes Entregados por Programa Educativo", entregados, "ExcelPOIGrafica1"); demo1.addChartToExcel(demo1.createChart(demo1.createDataset()), workbook); } if (tipografico.equalsIgnoreCase("linea")) { LineChartDemo1 demo1 = new LineChartDemo1( "Estadsticas de Reportes Entregados por Programa Educativo", entregados, "ExcelPOIGrafica1"); demo1.addChartToExcel(demo1.createChart(demo1.createDataset()), workbook); } if (tipografico.equalsIgnoreCase("pastel")) { PieChartDemo1 demo1 = new PieChartDemo1( "Estadsticas de Reportes Entregados por Programa Educativo", entregados, "ExcelPOIGrafica1"); demo1.addChartToExcel(demo1.createChart(demo1.createDataset()), workbook); } //Se acaba grafico //recorreremos todo el list aux para revisar los programas educativos que utiliza //aun que podemos usar el selectProgramaeducativo /* Programacion de Hojas*/ for (Programaeducativo programa : programasByUnidad) { if (selectProgramEducativo.contains(Integer.toString(programa.getPedclave()))) { sheet = workbook.createSheet(programa.getPednombre()); sheet = cabezeraGeneralExcel(sheet, uabcLogo, style); setExDat(sheet, 8, 3, "Concentrado de Reporte de Avance de Contenido Temtico de Entregados por Programa Educativo"); boolean autotam = true; //informacion de programa educativo tabla setExDat(sheet, 14, 1, "Clave"); setExDat(sheet, 14, 2, "Programa Educativo"); setExDat(sheet, 14, 3, "Plan de Estudios"); setExDat(sheet, 14, 4, "Responsable"); setStyleCell(sheet, headerTabla, 14, 1); setStyleCell(sheet, headerTabla, 14, 2); setStyleCell(sheet, headerTabla, 14, 3); setStyleCell(sheet, headerTabla, 14, 4); //llenado de informacion // se tiene imprimir por cada plan de estudios??? // Responsableprogramaeducativo responsable = filtrosBeanHelper.getConsultaDelegate().getResponsableProgramaEducativoByID(programa.getPedid()); setExDat(sheet, 15, 1, programa.getPedclave()); setExDat(sheet, 15, 2, programa.getPednombre()); setExDat(sheet, 15, 3, ""); setExDat(sheet, 15, 4, ""); setStyleCell(sheet, borderstabla, 15, 1); setStyleCell(sheet, borderstabla, 15, 2); setStyleCell(sheet, borderstabla, 15, 3); setStyleCell(sheet, borderstabla, 15, 4); List<Planestudio> planes = filtrosBeanHelper.getConsultaDelegate() .getPlanesByPrograma(programa.getPedid()); boolean uno = true; int currow = 20; ReporteAvanceAux tempAux = new ReporteAvanceAux(); for (ReporteAvanceAux aux : listaAux) { tempAux = aux; break; } //intentaremos usar un solo plan setExDat(sheet, currow, 1, "?reas de conocimiento"); setStyleCell(sheet, headerTabla, currow, 1); currow++; //cada plan de estudios tiene varias areas de conocimiento //List<Areaconocimiento> areasConocimiento = filtrosBeanHelper.getConsultaDelegate().getAreasByPlan( tempAux.getReporteAvance().getUnidadaprendizajeImparteProfesor().getGrupo().getPlanestudio().getPesid() ); List<Areaconocimiento> areasConocimiento = filtrosBeanHelper.getConsultaDelegate() .getAreasByPlanClave(programa.getPedclave(), tempAux.getReporteAvance().getUnidadaprendizajeImparteProfesor().getGrupo() .getPlanestudio().getPesvigenciaPlan()); for (Areaconocimiento areaCon : areasConocimiento) { //clave , area de conocimiento, responsable setExDat(sheet, currow, 1, "Clave"); setExDat(sheet, currow, 2, "?rea de conocimiento"); setExDat(sheet, currow, 3, "Responsable"); setStyleCell(sheet, headerTabla, currow, 1); setStyleCell(sheet, headerTabla, currow, 2); setStyleCell(sheet, headerTabla, currow, 3); currow++; setExDat(sheet, currow, 1, areaCon.getAcoclave()); //clave setExDat(sheet, currow, 2, areaCon.getAconombre()); //area conociminto setExDat(sheet, currow, 3, ""); //responsable currow += 2; //obtenemos las unidades de aprendisake setExDat(sheet, currow, 1, "Clave unidad de aprendizaje"); setExDat(sheet, currow, 2, "Unidad de aprendizaje"); setExDat(sheet, currow, 3, "No. de empleado"); setExDat(sheet, currow, 4, "Nombre del profesor"); setExDat(sheet, currow, 5, "Grupo"); setExDat(sheet, currow, 6, "% Avance 1er reporte"); setExDat(sheet, currow, 7, "Fecha de elaboracin 1er RACT"); setExDat(sheet, currow, 8, "% Avance 2do reporte"); setExDat(sheet, currow, 9, "Fecha de elaboracin 2do RACT"); setExDat(sheet, currow, 10, "% Avance 3er reporte"); setExDat(sheet, currow, 11, "Fecha de elaboracin 3er RACT"); if (autotam) { sheet.autoSizeColumn(2); sheet.autoSizeColumn(4); sheet.autoSizeColumn(11); autotam = false; } // para formatear toda la linea for (int i = 1; i <= 11; i++) { //tenemos qu aajustar texto //tenemos que centrar el texto //cambiar el color de fondo setStyleCell(sheet, headerTabla, currow, i); } currow++; //List<Unidadaprendizaje> unidadesAprendisaje = filtrosBeanHelper.getConsultaDelegate().getUnidadByArea(areaCon.getAcoclave()); //de ls listaAux separamos los los reportes que correspondan a la unidad de aprendisaje acutal ArrayList<ReporteAvanceAux> tempListAux = new ArrayList<ReporteAvanceAux>(); for (ReporteAvanceAux aux : listaAux) { if (aux.getAreaConocimiento() != null && aux.getAreaConocimiento().getAcoclave() == areaCon.getAcoclave() && programa.getPedclave() == aux.getReporteAvance() .getUnidadaprendizajeImparteProfesor().getGrupo().getPlanestudio() .getProgramaeducativo().getPedclave()) { tempListAux.add(aux); } } //List<UnidadaprendizajeImparteProfesor> unidadesProfesor = filtrosBeanHelper.getConsultaDelegate().getUnidadesAprendisajeImparteProf(unidadApren.getUapid()); int uniprofeTemp = 0; for (ReporteAvanceAux auxRacs : tempListAux) { //marcamos bordes for (int i = 1; i <= 11; i++) { setExDat(sheet, currow, i, " "); } for (int i = 1; i <= 11; i++) { setStyleCell(sheet, borderstabla, currow, i); } String nompreP = auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getProfesor().getPronombre(); String apellidoPP = auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getProfesor().getProapellidoPaterno(); String apellidoPM = auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getProfesor().getProapellidoMaterno(); int claveUnidadApren = auxRacs.getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUnidadaprendizaje().getUapclave(); String nombreUnidad = auxRacs.getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUnidadaprendizaje() .getUapnombre(); uniprofeTemp = claveUnidadApren; setExDat(sheet, currow, 1, claveUnidadApren); //clave setExDat(sheet, currow, 2, nombreUnidad); //nombre unidad //reporteAvance.unidadaprendizajeImparteProfesor.profesor.pronumeroEmpleado setExDat(sheet, currow, 3, auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getProfesor().getPronumeroEmpleado()); //numero empleado setExDat(sheet, currow, 4, apellidoPP + " " + apellidoPM + " " + nompreP); //nombre maestro setExDat(sheet, currow, 5, auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor().getGrupo() .getGponumero() + "-" + auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getUiptipoSubgrupo() + "-" + auxRacs.getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUipsubgrupo()); //grupo numero //marcamos bordes for (int i = 1; i <= 5; i++) { setStyleCell(sheet, borderstabla, currow, i); } //tienne que ser entregados/enviados setExDat(sheet, currow, (4 + (1 * 2)), auxRacs.getPorcentAvanceRact1()); //% avance 7** if (auxRacs.getFechaElaboracRact1() != null) { setExDat(sheet, currow, (4 + (1 * 2) + 1), auxRacs.getFechaElaboracRact1().toString()); //fecha elabora 8*** } setStyleCell(sheet, borderstabla, currow, (4 + (1 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (1 * 2) + 1)); setExDat(sheet, currow, (4 + (2 * 2)), auxRacs.getPorcentAvanceRact2()); //% avance 7** if (auxRacs.getFechaElaboracRact2() != null) { setExDat(sheet, currow, (4 + (2 * 2) + 1), auxRacs.getFechaElaboracRact2().toString()); //fecha elabora 8*** } setStyleCell(sheet, borderstabla, currow, (4 + (2 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (2 * 2) + 1)); setExDat(sheet, currow, (4 + (3 * 2)), auxRacs.getPorcentAvanceRact3()); //% avance 7** if (auxRacs.getFechaElaboracRact3() != null) { setExDat(sheet, currow, (4 + (3 * 2) + 1), auxRacs.getFechaElaboracRact3().toString()); //fecha elabora 8*** } setStyleCell(sheet, borderstabla, currow, (4 + (3 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (3 * 2) + 1)); currow++; } currow += 2; } //aqui modifique Jesus Ruelas - Entregados A Tiempo //aqui cambie headerTabla.setWrapText(true); //aqui cambie //aqui cambie jesus ruelas //for(int i=0;i<28;i++){ // sheet.autoSizeColumn(1); // sheet.autoSizeColumn(2); // sheet.autoSizeColumn(3); // sheet.autoSizeColumn(4); //merge cells de Total de RACT General de + ProgEd // sheet.addMergedRegion(new CellRangeAddress(13,13,1,5)); // sheet.addMergedRegion(new CellRangeAddress(13,13,7,11)); // sheet.addMergedRegion(new CellRangeAddress(13,13,13,17)); // sheet.addMergedRegion(new CellRangeAddress(13,13,19,23)); // sheet.addMergedRegion(new CellRangeAddress(13,13,25,29)); //} //aqui cambie jesus ruelas //mostraremos tabla con // Programas educativos // Total Racs entregados // Total Esperados //preparamos informacion para insertar // List<Programaeducativo> programasByUnidad = getProgramasByUnidad(); List<Planestudio> planesByPrograma = getPlanesByPrograma(); int UACid = 1; ArrayList<String> listaProgEdContar = esperadosDelegate.getSemadoroProgEdValor(UACid); //setCellStyle(style); //setExDat(sheet, 7, 1, "Concentrado de Reporte de Avance de Contenido Temtico General por Programa Educativo"); //imrpiimiremos la tabla // Definimos los encabezados de la tabla int pos = 0; int row = currow + 4; int col = 1; Boolean bandPe = false; //for (Programaeducativo pe : programasByUnidad) { for (String peContar : listaProgEdContar) { String[] contarEntEspProgEd; if (listaProgEdContar.size() >= pos) { if (peContar.contains(programa.getPednombre())) { contarEntEspProgEd = listaProgEdContar.get(pos).split("-"); setExDat(sheet, row, col, "Clave"); setExDat(sheet, row, col + 1, "Programa Educativo"); setExDat(sheet, row, col + 3, "Plan de Estudios"); setExDat(sheet, row, col + 4, "Responsable"); // if (contarEntEspProgEd[0].equalsIgnoreCase(programasByUnidad.get(0).getPednombre())) { setExDat(sheet, row + 1, col, programa.getPedclave()); setExDat(sheet, row + 1, col + 1, programa.getPednombre()); if (!(planesByPrograma.isEmpty())) { setExDat(sheet, row + 1, col + 2, planesByPrograma.get(0).getPesvigenciaPlan()); } setExDat(sheet, row + 1, col + 3, ""); // } // sheet.addMergedRegion(new CellRangeAddress(row+2,col,5,1)); setExDat(sheet, row + 2, col, "Total de RACT General de " + programa.getPednombre()); setExDat(sheet, row + 3, col, "Resumen de totales \r\n por numero de RACT"); setExDat(sheet, row + 3, col + 1, "Total entregados"); setExDat(sheet, row + 3, col + 2, "%"); setExDat(sheet, row + 3, col + 3, "Total esperados"); setExDat(sheet, row + 3, col + 4, "%"); sheet.getRow(row + 3).getCell(1);//aqui modifique jesus ruelas float porcentEnt = (Float.parseFloat(contarEntEspProgEd[1])) / (Float.parseFloat(contarEntEspProgEd[2])) * 100; float porcentEsp = 100 - porcentEnt; // setExDat(sheet, 15, 1, "Todos los RACTS"); // setExDat(sheet, 15, 2, contarEntEspProgEd[1]); // setExDat(sheet, 15, 3, "" + porcentEnt + "%"); // setExDat(sheet, 15, 4, contarEntEspProgEd[2]); // setExDat(sheet, 15, 5, "" + porcentEsp + "%"); //String[] contarEntEspProgEd; String[] contarEntRact1; String[] contarEntRact2; String[] contarEntRact3; int esperadosProgEdUnicoRact = 0; contarEntRact1 = contarEntEspProgEd[3].split(":"); contarEntRact2 = contarEntEspProgEd[4].split(":"); contarEntRact3 = contarEntEspProgEd[5].split(":"); esperadosProgEdUnicoRact = (Integer.parseInt(contarEntEspProgEd[2])) / 3; float contarPorcentEntRact1 = (Float.parseFloat(contarEntRact1[1])) / ((float) esperadosProgEdUnicoRact) * 100; float contarPorcentEntRact2 = (Float.parseFloat(contarEntRact2[1])) / ((float) esperadosProgEdUnicoRact) * 100; float contarPorcentEntRact3 = (Float.parseFloat(contarEntRact3[1])) / ((float) esperadosProgEdUnicoRact) * 100; float contarPorcentEspRact1 = 100 - contarPorcentEntRact1; float contarPorcentEspRact2 = 100 - contarPorcentEntRact2; float contarPorcentEspRact3 = 100 - contarPorcentEntRact3; setExDat(sheet, row + 4, col, "RACT 1"); setExDat(sheet, row + 4, col + 1, contarEntRact1[1]); setExDat(sheet, row + 4, col + 2, "" + contarPorcentEntRact1 + "%"); setExDat(sheet, row + 4, col + 3, esperadosProgEdUnicoRact); setExDat(sheet, row + 4, col + 4, "" + contarPorcentEspRact1 + "%"); setExDat(sheet, row + 5, col, "RACT 2"); setExDat(sheet, row + 5, col + 1, contarEntRact2[1]); setExDat(sheet, row + 5, col + 2, "" + contarPorcentEntRact2 + "%"); setExDat(sheet, row + 5, col + 3, esperadosProgEdUnicoRact); setExDat(sheet, row + 5, col + 4, "" + contarPorcentEspRact2 + "%"); setExDat(sheet, row + 6, col, "RACT 3"); setExDat(sheet, row + 6, col + 1, contarEntRact3[1]); setExDat(sheet, row + 6, col + 2, "" + contarPorcentEntRact3 + "%"); setExDat(sheet, row + 6, col + 3, esperadosProgEdUnicoRact); setExDat(sheet, row + 6, col + 4, "" + contarPorcentEspRact3 + "%"); setExDat(sheet, row + 7, col, "Todos los RACTS"); setExDat(sheet, row + 7, col + 1, contarEntEspProgEd[1]); setExDat(sheet, row + 7, col + 2, "" + porcentEnt + "%"); setExDat(sheet, row + 7, col + 3, contarEntEspProgEd[2]); setExDat(sheet, row + 7, col + 4, "" + porcentEsp + "%"); setStyleCell(sheet, headerTabla, row, col); setStyleCell(sheet, headerTabla, row, col + 1); setStyleCell(sheet, headerTabla, row, col + 2); setStyleCell(sheet, headerTabla, row, col + 3); setStyleCell(sheet, headerTabla, row, col + 4); setStyleCell(sheet, headerTabla, row + 2, col); setStyleCell(sheet, headerTabla, row + 2, col + 1); setStyleCell(sheet, headerTabla, row + 2, col + 2); setStyleCell(sheet, headerTabla, row + 2, col + 3); setStyleCell(sheet, headerTabla, row + 3, col); setStyleCell(sheet, headerTabla, row + 3, col + 1); setStyleCell(sheet, headerTabla, row + 3, col + 2); setStyleCell(sheet, headerTabla, row + 3, col + 3); setStyleCell(sheet, headerTabla, row + 3, col + 4); bandPe = true; pos++; } } // if (bandPe == true) { // if (col >= 25) { // row = row + 14; // col = -5; // } // col = col + 6; // bandPe = false; // } pos++; } //} //aqui modifique Jesus Ruelas } // fin del la comparacon de plan } // fin del for por plan de estudios //definimos encabezado sheet = cabezeraGeneralExcel(sheet, uabcLogo, style); } // fin de si es por entregados a tiempoif(entregados) //Porcentaje de Avance Global Incompleto if (reporte.equalsIgnoreCase("Porcentaje de Avance Global Completo") || reporte.equalsIgnoreCase("Porcentaje de Avance Global Incompleto") || reporte.equalsIgnoreCase("Porcentaje de Avance Global Completo e Incompleto")) { nombreLibro = reporte + nombreLibro; //definimos encabezado sheet = cabezeraGeneralExcel(sheet, uabcLogo, style); //mostraremos tabla con // Programas educativos // Total Racs entregados // Total Esperados //preparamos informacion para insertar List<Programaeducativo> programasByUnidad = getProgramasByUnidad(); //imrpiimiremos la tabla // Definimos los encabezados de la tabla setExDat(sheet, 13, 17, "Programa Educativo"); setExDat(sheet, 13, 18, "Total de RACT Entregados"); setExDat(sheet, 13, 19, "Total de RACT Esperados"); setExDat(sheet, 13, 20, " "); setStyleCell(sheet, headerTabla, 13, 17); setStyleCell(sheet, headerTabla, 13, 18); setStyleCell(sheet, headerTabla, 13, 19); /*sheet.autoSizeColumn(2); sheet.autoSizeColumn(3); sheet.autoSizeColumn(4);*/ //imprimimos la informacion en su lugar int prow = 14; for (Programaeducativo programa : programasByUnidad) { if (selectProgramEducativo.contains(programa.getPedid().toString())) { setExDat(sheet, prow, 17, programa.getPednombre()); setExDat(sheet, prow, 18, 75); setExDat(sheet, prow, 19, 100); prow++; } } //Se obtiene consulta con los parametros seleccionados ArrayList<String> entregados = esperadosDelegate.getFullProgramaEdu(unidadacademica.getUacid(), Plan, Programa, Ciclo); //agregamos programacion de grafico if (tipografico.equalsIgnoreCase("barras")) { BarChartCL demo1 = new BarChartCL("Estadsticas de Reportes Entregados por Programa Educativo", entregados, "ExcelPOIGrafica1"); demo1.addChartToExcel(demo1.createChart(demo1.createDataset()), workbook); } if (tipografico.equalsIgnoreCase("linea")) { LineChartDemo1 demo1 = new LineChartDemo1( "Estadsticas de Reportes Entregados por Programa Educativo", entregados, "ExcelPOIGrafica1"); demo1.addChartToExcel(demo1.createChart(demo1.createDataset()), workbook); } if (tipografico.equalsIgnoreCase("pastel")) { PieChartDemo1 demo1 = new PieChartDemo1( "Estadsticas de Reportes Entregados por Programa Educativo", entregados, "ExcelPOIGrafica1"); demo1.addChartToExcel(demo1.createChart(demo1.createDataset()), workbook); } //Se acaba grafico //recorreremos todo el list aux para revisar los programas educativos que utiliza //aun que podemos usar el selectProgramaeducativo /* Programacion de Hojas*/ for (Programaeducativo programa : programasByUnidad) { if (selectProgramEducativo.contains(Integer.toString(programa.getPedclave()))) { //sheet = cabezeraGeneralExcel(sheet,uabcLogo,style); lo moveremos al final de la hoja para que no sea mdoficada la imagen sheet = workbook.createSheet(programa.getPednombre()); sheet = cabezeraGeneralExcel(sheet, uabcLogo, style); //setExDat(sheet, 8, 3, "Concentrado de Reporte de Avance de Contenido Temtico de Entregados por Programa Educativo"); boolean autotam = true; //informacion de programa educativo tabla setExDat(sheet, 14, 1, "Clave"); setExDat(sheet, 14, 2, "Programa Educativo"); setExDat(sheet, 14, 3, "Plan de Estudios"); setExDat(sheet, 14, 4, "Responsable"); setStyleCell(sheet, headerTabla, 14, 1); setStyleCell(sheet, headerTabla, 14, 2); setStyleCell(sheet, headerTabla, 14, 3); setStyleCell(sheet, headerTabla, 14, 4); //llenado de informacion // se tiene imprimir por cada plan de estudios??? // Responsableprogramaeducativo responsable = filtrosBeanHelper.getConsultaDelegate().getResponsableProgramaEducativoByID(programa.getPedid()); setExDat(sheet, 15, 1, programa.getPedclave()); setExDat(sheet, 15, 2, programa.getPednombre()); setExDat(sheet, 15, 3, ""); setExDat(sheet, 15, 4, ""); setStyleCell(sheet, borderstabla, 15, 1); setStyleCell(sheet, borderstabla, 15, 2); setStyleCell(sheet, borderstabla, 15, 3); setStyleCell(sheet, borderstabla, 15, 4); List<Planestudio> planes = filtrosBeanHelper.getConsultaDelegate() .getPlanesByPrograma(programa.getPedid()); boolean uno = true; int currow = 20; Planestudio planeact = new Planestudio(); for (Planestudio planeactn : planes) { planeact = planeactn; break; } ReporteAvanceAux tempAux = new ReporteAvanceAux(); for (ReporteAvanceAux aux : listaAux) { tempAux = aux; break; } //intentaremos usar un solo plan setExDat(sheet, currow, 1, "?reas de conocimiento"); setStyleCell(sheet, headerTabla, currow, 1); currow++; List<Areaconocimiento> areasConocimiento = filtrosBeanHelper.getConsultaDelegate() .getAreasByPlanClave(programa.getPedclave(), tempAux.getReporteAvance().getUnidadaprendizajeImparteProfesor().getGrupo() .getPlanestudio().getPesvigenciaPlan()); for (Areaconocimiento areaCon : areasConocimiento) { //clave , area de conocimiento, responsable setExDat(sheet, currow, 1, "Clave"); setExDat(sheet, currow, 2, "?rea de conocimiento"); setExDat(sheet, currow, 3, "Responsable"); setStyleCell(sheet, headerTabla, currow, 1); setStyleCell(sheet, headerTabla, currow, 2); setStyleCell(sheet, headerTabla, currow, 3); currow++; setExDat(sheet, currow, 1, areaCon.getAcoclave()); //clave setExDat(sheet, currow, 2, areaCon.getAconombre()); //area conociminto setExDat(sheet, currow, 3, ""); //responsable currow += 2; //obtenemos las unidades de aprendisake setExDat(sheet, currow, 1, "Clave unidad de aprendizaje"); setExDat(sheet, currow, 2, "Unidad de aprendizaje"); setExDat(sheet, currow, 3, "No. de empleado"); setExDat(sheet, currow, 4, "Nombre del profesor"); setExDat(sheet, currow, 5, "Grupo"); setExDat(sheet, currow, 6, "% Avance 1er reporte"); setExDat(sheet, currow, 7, "Fecha de elaboracin 1er RACT"); setExDat(sheet, currow, 8, "% Avance 2do reporte"); setExDat(sheet, currow, 9, "Fecha de elaboracin 2do RACT"); setExDat(sheet, currow, 10, "% Avance 3er reporte"); setExDat(sheet, currow, 11, "Fecha de elaboracin 3er RACT"); //autosize para la columna if (autotam) { sheet.autoSizeColumn(2); sheet.autoSizeColumn(4); sheet.autoSizeColumn(11); autotam = false; } // para formatear toda la linea for (int i = 1; i <= 11; i++) { //tenemos qu aajustar texto //tenemos que centrar el texto //cambiar el color de fondo setStyleCell(sheet, headerTabla, currow, i); } currow++; //List<Unidadaprendizaje> unidadesAprendisaje = filtrosBeanHelper.getConsultaDelegate().getUnidadByArea(areaCon.getAcoclave()); //de ls listaAux separamos los los reportes que correspondan a la unidad de aprendisaje acutal ArrayList<ReporteAvanceAux> tempListAux = new ArrayList<ReporteAvanceAux>(); for (ReporteAvanceAux aux : listaAux) { if (aux.getAreaConocimiento().getAcoclave() == areaCon.getAcoclave() && programa.getPedclave() == aux.getReporteAvance() .getUnidadaprendizajeImparteProfesor().getGrupo().getPlanestudio() .getProgramaeducativo().getPedclave()) { tempListAux.add(aux); } } //List<UnidadaprendizajeImparteProfesor> unidadesProfesor = filtrosBeanHelper.getConsultaDelegate().getUnidadesAprendisajeImparteProf(unidadApren.getUapid()); int uniprofeTemp = 0; for (ReporteAvanceAux auxRacs : tempListAux) { String nompreP = auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getProfesor().getPronombre(); String apellidoPP = auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getProfesor().getProapellidoPaterno(); String apellidoPM = auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getProfesor().getProapellidoMaterno(); int claveUnidadApren = auxRacs.getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUnidadaprendizaje().getUapclave(); String nombreUnidad = auxRacs.getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUnidadaprendizaje() .getUapnombre(); uniprofeTemp = claveUnidadApren; setExDat(sheet, currow, 1, claveUnidadApren); //clave setExDat(sheet, currow, 2, nombreUnidad); //nombre unidad //reporteAvance.unidadaprendizajeImparteProfesor.profesor.pronumeroEmpleado setExDat(sheet, currow, 3, auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getProfesor().getPronumeroEmpleado()); //numero empleado setExDat(sheet, currow, 4, apellidoPP + " " + apellidoPM + " " + nompreP); //nombre maestro setExDat(sheet, currow, 5, auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor().getGrupo() .getGponumero() + "-" + auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getUiptipoSubgrupo() + "-" + auxRacs.getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUipsubgrupo()); //grupo numero //marcamos bordes for (int i = 1; i <= 5; i++) { setStyleCell(sheet, borderstabla, currow, i); } float totalpor = 0; if (auxRacs.getStatusRact1() != null && auxRacs.getStatusRact2() != null && auxRacs.getStatusRact3() != null) { totalpor = auxRacs.getPorcentAvanceRact1() + auxRacs.getPorcentAvanceRact2() + auxRacs.getPorcentAvanceRact3(); } //tienne que ser entregados/enviados if (auxRacs.getStatusRact1() != null) { System.out.println("Entro a enviados 1"); setExDat(sheet, currow, (4 + (1 * 2)), auxRacs.getPorcentAvanceRact1()); //% avance 7** if (auxRacs.getFechaElaboracRact1() != null) { setExDat(sheet, currow, (4 + (1 * 2) + 1), auxRacs.getFechaElaboracRact1().toString()); //fecha elabora 8*** } else { setExDat(sheet, currow, (4 + (1 * 2) + 1), " "); //fecha elabora 8*** } setStyleCell(sheet, borderstabla, currow, (4 + (1 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (1 * 2) + 1)); } else { setExDat(sheet, currow, (4 + (1 * 2)), " "); //% avance 7** setExDat(sheet, currow, (4 + (1 * 2) + 1), " "); //fecha elabora 8*** setStyleCell(sheet, borderstabla, currow, (4 + (1 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (1 * 2) + 1)); } if (auxRacs.getStatusRact2() != null) { setExDat(sheet, currow, (4 + (2 * 2)), auxRacs.getPorcentAvanceRact2()); //% avance 7** if (auxRacs.getFechaElaboracRact2() != null) { setExDat(sheet, currow, (4 + (2 * 2) + 1), auxRacs.getFechaElaboracRact2().toString()); //fecha elabora 8*** } else { setExDat(sheet, currow, (4 + (2 * 2) + 1), " "); //fecha elabora 8*** } setStyleCell(sheet, borderstabla, currow, (4 + (2 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (2 * 2) + 1)); } else { setExDat(sheet, currow, (4 + (2 * 2)), " "); //% avance 7** setExDat(sheet, currow, (4 + (2 * 2) + 1), " "); //fecha elabora 8*** setStyleCell(sheet, borderstabla, currow, (4 + (2 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (2 * 2) + 1)); } if (auxRacs.getStatusRact3() != null) { setExDat(sheet, currow, (4 + (3 * 2)), auxRacs.getPorcentAvanceRact3()); //% avance 7** if (auxRacs.getFechaElaboracRact3() != null) { setExDat(sheet, currow, (4 + (3 * 2) + 1), auxRacs.getFechaElaboracRact3().toString()); //fecha elabora 8*** } else { setExDat(sheet, currow, (4 + (3 * 2) + 1), " "); //fecha elabora 8*** } setStyleCell(sheet, borderstabla, currow, (4 + (3 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (3 * 2) + 1)); } else { setExDat(sheet, currow, (4 + (3 * 2)), " "); //% avance 7** setExDat(sheet, currow, (4 + (3 * 2) + 1), " "); //fecha elabora 8*** setStyleCell(sheet, borderstabla, currow, (4 + (3 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (3 * 2) + 1)); } currow++; } currow += 2; } //cabeceran excel //sheet = cabezeraGeneralExcel(sheet,uabcLogo,style); } // fin del la comparacon de plan } // fin del for por plan de estudios } // fin de si es por entregados if(entregados) } //fin de programas educativos if (criterio.equalsIgnoreCase("area_conocimiento")) { HSSFSheet sheet = workbook.createSheet("Graficos"); nombreLibro = "- Area de conocimiento"; if (reporte.equalsIgnoreCase("entregados") || reporte.equalsIgnoreCase("noentregados") || reporte.equalsIgnoreCase("entregadosynoentregados") || //Aqui modifique Jesus Ruelas 26 oct 2015 reporte.equalsIgnoreCase("entregadosatiempo") || reporte.equalsIgnoreCase("entregadosdespueslimite") || reporte.equalsIgnoreCase("entregadosatiempoenfechalimiteydespues") || //Aqui modifique Jesus Ruelas 26 oct 2015 reporte.equalsIgnoreCase("Porcentaje de Avance Global Completo") || reporte.equalsIgnoreCase("Porcentaje de Avance Global Incompleto") || reporte.equalsIgnoreCase("Porcentaje de Avance Global Completo e Incompleto")) { if (reporte.equalsIgnoreCase("entregados")) nombreLibro = "Entregados " + nombreLibro; if (reporte.equalsIgnoreCase("noentregados")) nombreLibro = "No Entregados " + nombreLibro; if (reporte.equalsIgnoreCase("entregadosynoentregados")) nombreLibro = "Entregados Y No Entregados " + nombreLibro; //Aqui modifique Jesus Ruelas 26 oct 2015 if (reporte.equalsIgnoreCase("entregadosatiempo")) nombreLibro = "Entregados a tiempo " + nombreLibro; if (reporte.equalsIgnoreCase("entregadosdespueslimite")) nombreLibro = "Entregados despus de fecha lmite " + nombreLibro; if (reporte.equalsIgnoreCase("entregadosatiempoenfechalimiteydespues")) nombreLibro = "Entregados a tiempo y despues de fecha lmite " + nombreLibro; //Aqui modifique Jesus Ruelas 26 oct 2015 if (reporte.equalsIgnoreCase("Porcentaje de Avance Global Completo")) nombreLibro = "Porcentaje de Avance Global Completo " + nombreLibro; if (reporte.equalsIgnoreCase("Porcentaje de Avance Global Incompleto")) nombreLibro = "Porcentaje de Avance Global Incompleto " + nombreLibro; if (reporte.equalsIgnoreCase("Porcentaje de Avance Global Completo e Incompleto")) nombreLibro = "Porcentaje de Avance Global Completo e Incompleto " + nombreLibro; //definimos encabezado //sheet = cabezeraGeneralExcel(sheet,uabcLogo,style); //mostraremos tabla con // Programas educativos // Total Racs entregados // Total Esperados //preparamos informacion para insertar List<Programaeducativo> programasByUnidad = getProgramasByUnidad(); List<Areaconocimiento> areasByUnidad = getAreasByPlanesEstudioClave(); /* Programacion de Hojas*/ for (Areaconocimiento areaConocimiento : areasByUnidad) { if (selectAreaConocimiento.contains(Integer.toString(areaConocimiento.getAcoclave()))) { //aqui creamos la hoja para el programa ArrayList<ReporteAvanceAux> tempListAux = new ArrayList<ReporteAvanceAux>(); for (ReporteAvanceAux aux : listaAux) { if (aux.getAreaConocimiento().getAcoclave() == areaConocimiento.getAcoclave()) { tempListAux.add(aux); } } //// de este for // para no imprirmir hojas vacias if (tempListAux.size() > 0) { sheet = workbook.createSheet(areaConocimiento.getAconombre()); //sheet = cabezeraGeneralExcel(sheet,uabcLogo,style); lo moveremos al final de la hoja para que no sea mdoficada la imagen setExDat(sheet, 8, 3, "Concentrado de Reporte de Avance de Contenido Temtico de Entregados por Area de conocimiento"); boolean autotam = true; int currow = 18; //Aqui modifique Jesus Ruelas 26 oct 2015 if (reporte.equalsIgnoreCase("entregadosatiempo") || reporte.equalsIgnoreCase("entregadosdespueslimite") || reporte.equalsIgnoreCase("entregadosatiempoenfechalimiteydespues")) { if (ract.equalsIgnoreCase("1") || ract.equalsIgnoreCase("2") || ract.equalsIgnoreCase("3")) { setExDat(sheet, currow, 1, "Fecha Corte"); setExDat(sheet, currow, 2, "Fecha Lmite"); setStyleCell(sheet, headerTabla, currow, 1); setStyleCell(sheet, headerTabla, currow, 2); currow++; setExDat(sheet, currow, 1, tempListAux.get(0).getFechaCorte().toString()); setExDat(sheet, currow, 2, tempListAux.get(0).getFechaLimite().toString()); currow++; currow++; } else { ReporteAux rep1 = new ReporteAux(); ReporteAux rep2 = new ReporteAux(); ReporteAux rep3 = new ReporteAux(); rep1.setNumRact(1); rep2.setNumRact(2); rep3.setNumRact(3); rep1.setCescicloEscolar(tempListAux.get(0).getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUnidadaprendizaje() .getCicloescolar().getCescicloEscolar()); rep2.setCescicloEscolar(tempListAux.get(0).getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUnidadaprendizaje() .getCicloescolar().getCescicloEscolar()); rep3.setCescicloEscolar(tempListAux.get(0).getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUnidadaprendizaje() .getCicloescolar().getCescicloEscolar()); ReporteAvanceAux ra1 = new ReporteAvanceAux(); ReporteAvanceAux ra2 = new ReporteAvanceAux(); ReporteAvanceAux ra3 = new ReporteAvanceAux(); ra1 = filtrosBeanHelper.TiempoLimiteYCorte(rep1); ra2 = filtrosBeanHelper.TiempoLimiteYCorte(rep2); ra3 = filtrosBeanHelper.TiempoLimiteYCorte(rep3); setExDat(sheet, currow, 1, "Fecha Corte Ract 1"); setExDat(sheet, currow, 2, "Fecha Lmite Ract 1"); setStyleCell(sheet, headerTabla, currow, 1); setStyleCell(sheet, headerTabla, currow, 2); currow++; setExDat(sheet, currow, 1, ra1.getFechaCorte().toString()); setExDat(sheet, currow, 2, ra1.getFechaLimite().toString()); //currow++; setExDat(sheet, currow - 1, 4, "Fecha Corte Ract 2"); setExDat(sheet, currow - 1, 5, "Fecha Lmite Ract 2"); setStyleCell(sheet, headerTabla, currow - 1, 4); setStyleCell(sheet, headerTabla, currow - 1, 5); //currow++; setExDat(sheet, currow, 4, ra2.getFechaCorte().toString()); setExDat(sheet, currow, 5, ra2.getFechaLimite().toString()); //currow++; setExDat(sheet, currow - 1, 7, "Fecha Corte Ract 3"); setExDat(sheet, currow - 1, 8, "Fecha Lmite Ract 3"); setStyleCell(sheet, headerTabla, currow - 1, 7); setStyleCell(sheet, headerTabla, currow - 1, 8); //currow++; setExDat(sheet, currow, 7, ra3.getFechaCorte().toString()); setExDat(sheet, currow, 8, ra3.getFechaLimite().toString()); currow++; currow++; } } //Aqui modifique Jesus Ruelas 26 oct 2015 setExDat(sheet, currow, 1, "Clave unidad de aprendizaje"); setExDat(sheet, currow, 2, "Unidad de aprendizaje"); setExDat(sheet, currow, 3, "No. de empleado"); setExDat(sheet, currow, 4, "Nombre del profesor"); setExDat(sheet, currow, 5, "Grupo"); setExDat(sheet, currow, 6, "% Avance 1er reporte"); setExDat(sheet, currow, 7, "Fecha de elaboracin 1er RACT"); setExDat(sheet, currow, 8, "% Avance 2do reporte"); setExDat(sheet, currow, 9, "Fecha de elaboracin 2do RACT"); setExDat(sheet, currow, 10, "% Avance 3er reporte"); setExDat(sheet, currow, 11, "Fecha de elaboracin 3er RACT"); //autosize para la columna if (autotam) { sheet.autoSizeColumn(2); sheet.autoSizeColumn(4); sheet.autoSizeColumn(11); autotam = false; } // para formatear toda la linea for (int i = 1; i <= 11; i++) { setStyleCell(sheet, headerTabla, currow, i); } currow++; int uniprofeTemp = 0; for (ReporteAvanceAux auxRacs : tempListAux) { //marcamos bordes for (int i = 1; i <= 11; i++) { setExDat(sheet, currow, i, " "); } for (int i = 1; i <= 11; i++) { setStyleCell(sheet, borderstabla, currow, i); } String nompreP = auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getProfesor().getPronombre(); String apellidoPP = auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getProfesor().getProapellidoPaterno(); String apellidoPM = auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getProfesor().getProapellidoMaterno(); int claveUnidadApren = auxRacs.getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUnidadaprendizaje().getUapclave(); String nombreUnidad = auxRacs.getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUnidadaprendizaje() .getUapnombre(); uniprofeTemp = claveUnidadApren; setExDat(sheet, currow, 1, claveUnidadApren); //clave setExDat(sheet, currow, 2, nombreUnidad); //nombre unidad //reporteAvance.unidadaprendizajeImparteProfesor.profesor.pronumeroEmpleado setExDat(sheet, currow, 3, auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getProfesor().getPronumeroEmpleado()); //numero empleado setExDat(sheet, currow, 4, apellidoPP + " " + apellidoPM + " " + nompreP); //nombre maestro setExDat(sheet, currow, 5, auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor().getGrupo() .getGponumero() + "-" + auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getUiptipoSubgrupo() + "-" + auxRacs.getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUipsubgrupo()); //grupo numero //marcamos bordes for (int i = 1; i <= 5; i++) { setStyleCell(sheet, borderstabla, currow, i); } //tienne que ser entregados/enviados if (auxRacs.getStatusRact1() != null) { System.out.println("Entro a enviados 1"); setExDat(sheet, currow, (4 + (1 * 2)), auxRacs.getPorcentAvanceRact1()); //% avance 7** if (auxRacs.getFechaElaboracRact1() != null) { setExDat(sheet, currow, (4 + (1 * 2) + 1), auxRacs.getFechaElaboracRact1().toString()); //fecha elabora 8*** } else { setExDat(sheet, currow, (4 + (1 * 2) + 1), " "); //fecha elabora 8*** } setStyleCell(sheet, borderstabla, currow, (4 + (1 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (1 * 2) + 1)); } else { setExDat(sheet, currow, (4 + (1 * 2)), " "); //% avance 7** setExDat(sheet, currow, (4 + (1 * 2) + 1), " "); //fecha elabora 8*** setStyleCell(sheet, borderstabla, currow, (4 + (1 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (1 * 2) + 1)); } if (auxRacs.getStatusRact2() != null) { setExDat(sheet, currow, (4 + (2 * 2)), auxRacs.getPorcentAvanceRact2()); //% avance 7** if (auxRacs.getFechaElaboracRact2() != null) { setExDat(sheet, currow, (4 + (2 * 2) + 1), auxRacs.getFechaElaboracRact2().toString()); //fecha elabora 8*** } else { setExDat(sheet, currow, (4 + (2 * 2) + 1), " "); //fecha elabora 8*** } setStyleCell(sheet, borderstabla, currow, (4 + (2 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (2 * 2) + 1)); } else { setExDat(sheet, currow, (4 + (2 * 2)), " "); //% avance 7** setExDat(sheet, currow, (4 + (2 * 2) + 1), " "); //fecha elabora 8*** setStyleCell(sheet, borderstabla, currow, (4 + (2 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (2 * 2) + 1)); } if (auxRacs.getStatusRact3() != null) { setExDat(sheet, currow, (4 + (3 * 2)), auxRacs.getPorcentAvanceRact3()); //% avance 7** if (auxRacs.getFechaElaboracRact3() != null) { setExDat(sheet, currow, (4 + (3 * 2) + 1), auxRacs.getFechaElaboracRact3().toString()); //fecha elabora 8*** } else { setExDat(sheet, currow, (4 + (3 * 2) + 1), " "); //fecha elabora 8*** } setStyleCell(sheet, borderstabla, currow, (4 + (3 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (3 * 2) + 1)); } else { setExDat(sheet, currow, (4 + (3 * 2)), " "); //% avance 7** setExDat(sheet, currow, (4 + (3 * 2) + 1), " "); //fecha elabora 8*** setStyleCell(sheet, borderstabla, currow, (4 + (3 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (3 * 2) + 1)); } currow++; } } // delfindelfor //fin de codigo e imrpesion de cabezera sheet = cabezeraGeneralExcel(sheet, uabcLogo, style); } } // fin del for por plan de estudios } // fin de si es por entregados if(entregados) } //fin de areas de conocimiento if (criterio.equalsIgnoreCase("unidad_aprendizaje")) { HSSFSheet sheet = workbook.createSheet("Graficos"); nombreLibro = "- Unidad de aprendizaje"; if (reporte.equalsIgnoreCase("entregados") || reporte.equalsIgnoreCase("noentregados") || reporte.equalsIgnoreCase("entregadosynoentregados") || //Aqui modifique Jesus Ruelas 26 oct 2015 reporte.equalsIgnoreCase("entregadosatiempo") || reporte.equalsIgnoreCase("entregadosdespueslimite") || reporte.equalsIgnoreCase("entregadosatiempoenfechalimiteydespues") || //Aqui modifique Jesus Ruelas 26 oct 2015 reporte.equalsIgnoreCase("Porcentaje de Avance Global Completo") || reporte.equalsIgnoreCase("Porcentaje de Avance Global Incompleto") || reporte.equalsIgnoreCase("Porcentaje de Avance Global Completo e Incompleto")) { if (reporte.equalsIgnoreCase("entregados")) nombreLibro = "Entregados " + nombreLibro; if (reporte.equalsIgnoreCase("noentregados")) nombreLibro = "No Entregados " + nombreLibro; if (reporte.equalsIgnoreCase("entregadosynoentregados")) nombreLibro = "Entregados Y No Entregados " + nombreLibro; //Aqui modifique Jesus Ruelas 26 oct 2015 if (reporte.equalsIgnoreCase("entregadosatiempo")) nombreLibro = "Entregados a tiempo " + nombreLibro; if (reporte.equalsIgnoreCase("entregadosdespueslimite")) nombreLibro = "Entregados despus de fecha lmite " + nombreLibro; if (reporte.equalsIgnoreCase("entregadosatiempoenfechalimiteydespues")) nombreLibro = "Entregados a tiempo y despues de fecha lmite " + nombreLibro; //Aqui modifique Jesus Ruelas 26 oct 2015 if (reporte.equalsIgnoreCase("Porcentaje de Avance Global Completo")) nombreLibro = "Porcentaje de Avance Global Completo " + nombreLibro; if (reporte.equalsIgnoreCase("Porcentaje de Avance Global Incompleto")) nombreLibro = "Porcentaje de Avance Global Incompleto " + nombreLibro; if (reporte.equalsIgnoreCase("Porcentaje de Avance Global Completo e Incompleto")) nombreLibro = "Porcentaje de Avance Global Completo e Incompleto " + nombreLibro; //definimos encabezado //sheet = cabezeraGeneralExcel(sheet,uabcLogo,style); //mostraremos tabla con // Programas educativos // Total Racs entregados // Total Esperados //preparamos informacion para insertar List<Programaeducativo> programasByUnidad = getProgramasByUnidad(); List<Unidadaprendizaje> unidadesByArea = getUnidadesByAreaAconocimientoClave(); /* Programacion de Hojas*/ for (Unidadaprendizaje unidadAprend : unidadesByArea) { if (selectUnidadAprendisaje.contains(Integer.toString(unidadAprend.getUapclave()))) { //aqui creamos la hoja para el programa ArrayList<ReporteAvanceAux> tempListAux = new ArrayList<ReporteAvanceAux>(); for (ReporteAvanceAux aux : listaAux) { if (aux.reporteAvance.getUnidadaprendizajeImparteProfesor().getUnidadaprendizaje() .getUapclave() == unidadAprend.getUapclave()) { tempListAux.add(aux); } } //// de este for // para no imprirmir hojas vacias if (tempListAux.size() > 0) { sheet = workbook.createSheet(unidadAprend.getUapnombre()); //sheet = cabezeraGeneralExcel(sheet,uabcLogo,style); lo moveremos al final de la hoja para que no sea mdoficada la imagen setExDat(sheet, 8, 3, "Concentrado de Reporte de Avance de Contenido Temtico de Entregados por Unidad de aprendizaje"); boolean autotam = true; int currow = 18; //Aqui modifique Jesus Ruelas 26 oct 2015 if (reporte.equalsIgnoreCase("entregadosatiempo") || reporte.equalsIgnoreCase("entregadosdespueslimite") || reporte.equalsIgnoreCase("entregadosatiempoenfechalimiteydespues")) { if (ract.equalsIgnoreCase("1") || ract.equalsIgnoreCase("2") || ract.equalsIgnoreCase("3")) { setExDat(sheet, currow, 1, "Fecha Corte"); setExDat(sheet, currow, 2, "Fecha Lmite"); setStyleCell(sheet, headerTabla, currow, 1); setStyleCell(sheet, headerTabla, currow, 2); currow++; setExDat(sheet, currow, 1, tempListAux.get(0).getFechaCorte().toString()); setExDat(sheet, currow, 2, tempListAux.get(0).getFechaLimite().toString()); currow++; currow++; } else { ReporteAux rep1 = new ReporteAux(); ReporteAux rep2 = new ReporteAux(); ReporteAux rep3 = new ReporteAux(); rep1.setNumRact(1); rep2.setNumRact(2); rep3.setNumRact(3); rep1.setCescicloEscolar(tempListAux.get(0).getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUnidadaprendizaje() .getCicloescolar().getCescicloEscolar()); rep2.setCescicloEscolar(tempListAux.get(0).getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUnidadaprendizaje() .getCicloescolar().getCescicloEscolar()); rep3.setCescicloEscolar(tempListAux.get(0).getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUnidadaprendizaje() .getCicloescolar().getCescicloEscolar()); ReporteAvanceAux ra1 = new ReporteAvanceAux(); ReporteAvanceAux ra2 = new ReporteAvanceAux(); ReporteAvanceAux ra3 = new ReporteAvanceAux(); ra1 = filtrosBeanHelper.TiempoLimiteYCorte(rep1); ra2 = filtrosBeanHelper.TiempoLimiteYCorte(rep2); ra3 = filtrosBeanHelper.TiempoLimiteYCorte(rep3); setExDat(sheet, currow, 1, "Fecha Corte Ract 1"); setExDat(sheet, currow, 2, "Fecha Lmite Ract 1"); setStyleCell(sheet, headerTabla, currow, 1); setStyleCell(sheet, headerTabla, currow, 2); currow++; setExDat(sheet, currow, 1, ra1.getFechaCorte().toString()); setExDat(sheet, currow, 2, ra1.getFechaLimite().toString()); //currow++; setExDat(sheet, currow - 1, 4, "Fecha Corte Ract 2"); setExDat(sheet, currow - 1, 5, "Fecha Lmite Ract 2"); setStyleCell(sheet, headerTabla, currow - 1, 4); setStyleCell(sheet, headerTabla, currow - 1, 5); //currow++; setExDat(sheet, currow, 4, ra2.getFechaCorte().toString()); setExDat(sheet, currow, 5, ra2.getFechaLimite().toString()); //currow++; setExDat(sheet, currow - 1, 7, "Fecha Corte Ract 3"); setExDat(sheet, currow - 1, 8, "Fecha Lmite Ract 3"); setStyleCell(sheet, headerTabla, currow - 1, 7); setStyleCell(sheet, headerTabla, currow - 1, 8); //currow++; setExDat(sheet, currow, 7, ra3.getFechaCorte().toString()); setExDat(sheet, currow, 8, ra3.getFechaLimite().toString()); currow++; currow++; } } //Aqui modifique Jesus Ruelas 26 oct 2015 setExDat(sheet, currow, 1, "Clave unidad de aprendizaje"); setExDat(sheet, currow, 2, "Unidad de aprendizaje"); setStyleCell(sheet, headerTabla, currow, 1); setStyleCell(sheet, headerTabla, currow, 2); currow++; int claveUnidadApren = tempListAux.get(0).getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUnidadaprendizaje().getUapclave(); String nombreUnidad = tempListAux.get(0).getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUnidadaprendizaje().getUapnombre(); int uniprofeTemp = 0; uniprofeTemp = claveUnidadApren; setExDat(sheet, currow, 1, claveUnidadApren); //clave setExDat(sheet, currow, 2, nombreUnidad); //nombre unidad currow++; //autosize para la columna if (autotam) { sheet.autoSizeColumn(2); sheet.autoSizeColumn(4); sheet.autoSizeColumn(11); autotam = false; } setExDat(sheet, currow, 1, "No. de empleado"); setExDat(sheet, currow, 2, "Nombre del profesor"); setExDat(sheet, currow, 3, "Grupo"); setExDat(sheet, currow, 4, "% Avance 1er reporte"); setExDat(sheet, currow, 5, "Fecha de elaboracin 1er RACT"); setExDat(sheet, currow, 6, "% Avance 2do reporte"); setExDat(sheet, currow, 7, "Fecha de elaboracin 2do RACT"); setExDat(sheet, currow, 8, "% Avance 3er reporte"); setExDat(sheet, currow, 9, "Fecha de elaboracin 3er RACT"); // para formatear toda la linea for (int i = 1; i <= 9; i++) { setStyleCell(sheet, headerTabla, currow, i); } //currow++; currow++; for (ReporteAvanceAux auxRacs : tempListAux) { //marcamos bordes for (int i = 1; i <= 9; i++) { setExDat(sheet, currow, i, " "); } for (int i = 1; i <= 9; i++) { setStyleCell(sheet, borderstabla, currow, i); } String nompreP = auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getProfesor().getPronombre(); String apellidoPP = auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getProfesor().getProapellidoPaterno(); String apellidoPM = auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getProfesor().getProapellidoMaterno(); //reporteAvance.unidadaprendizajeImparteProfesor.profesor.pronumeroEmpleado setExDat(sheet, currow, 1, auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getProfesor().getPronumeroEmpleado()); //numero empleado setExDat(sheet, currow, 2, apellidoPP + " " + apellidoPM + " " + nompreP); //nombre maestro setExDat(sheet, currow, 3, auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor().getGrupo() .getGponumero() + "-" + auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getUiptipoSubgrupo() + "-" + auxRacs.getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUipsubgrupo()); //grupo numero //marcamos bordes for (int i = 1; i <= 5; i++) { setStyleCell(sheet, borderstabla, currow, i); } //tienne que ser entregados/enviados if (auxRacs.getStatusRact1() != null) { System.out.println("Entro a enviados 1"); setExDat(sheet, currow, (2 + (1 * 2)), auxRacs.getPorcentAvanceRact1()); //% avance 7** if (auxRacs.getFechaElaboracRact1() != null) { setExDat(sheet, currow, (2 + (1 * 2) + 1), auxRacs.getFechaElaboracRact1().toString()); //fecha elabora 8*** } else { setExDat(sheet, currow, (2 + (1 * 2) + 1), " "); //fecha elabora 8*** } setStyleCell(sheet, borderstabla, currow, (2 + (1 * 2))); setStyleCell(sheet, borderstabla, currow, (2 + (1 * 2) + 1)); } else { setExDat(sheet, currow, (2 + (1 * 2)), " "); //% avance 7** setExDat(sheet, currow, (2 + (1 * 2) + 1), " "); //fecha elabora 8*** setStyleCell(sheet, borderstabla, currow, (2 + (1 * 2))); setStyleCell(sheet, borderstabla, currow, (2 + (1 * 2) + 1)); } if (auxRacs.getStatusRact2() != null) { setExDat(sheet, currow, (2 + (2 * 2)), auxRacs.getPorcentAvanceRact2()); //% avance 7** if (auxRacs.getFechaElaboracRact2() != null) { setExDat(sheet, currow, (2 + (2 * 2) + 1), auxRacs.getFechaElaboracRact2().toString()); //fecha elabora 8*** } else { setExDat(sheet, currow, (2 + (2 * 2) + 1), " "); //fecha elabora 8*** } setStyleCell(sheet, borderstabla, currow, (2 + (2 * 2))); setStyleCell(sheet, borderstabla, currow, (2 + (2 * 2) + 1)); } else { setExDat(sheet, currow, (2 + (2 * 2)), " "); //% avance 7** setExDat(sheet, currow, (2 + (2 * 2) + 1), " "); //fecha elabora 8*** setStyleCell(sheet, borderstabla, currow, (2 + (2 * 2))); setStyleCell(sheet, borderstabla, currow, (2 + (2 * 2) + 1)); } if (auxRacs.getStatusRact3() != null) { setExDat(sheet, currow, (2 + (3 * 2)), auxRacs.getPorcentAvanceRact3()); //% avance 7** if (auxRacs.getFechaElaboracRact3() != null) { setExDat(sheet, currow, (2 + (3 * 2) + 1), auxRacs.getFechaElaboracRact3().toString()); //fecha elabora 8*** } else { setExDat(sheet, currow, (2 + (3 * 2) + 1), " "); //fecha elabora 8*** } setStyleCell(sheet, borderstabla, currow, (2 + (3 * 2))); setStyleCell(sheet, borderstabla, currow, (2 + (3 * 2) + 1)); } else { setExDat(sheet, currow, (2 + (3 * 2)), " "); //% avance 7** setExDat(sheet, currow, (2 + (3 * 2) + 1), " "); //fecha elabora 8*** setStyleCell(sheet, borderstabla, currow, (2 + (3 * 2))); setStyleCell(sheet, borderstabla, currow, (2 + (3 * 2) + 1)); } currow++; } } // delfindelfor //fin de codigo e imrpesion de cabezera sheet = cabezeraGeneralExcel(sheet, uabcLogo, style); } } // fin del for por plan de estudios } // fin de si es por entregados if(entregados) } //fin de unidad de aprendizaje if (criterio.equalsIgnoreCase("profesor")) { HSSFSheet sheet = workbook.createSheet("Graficos"); nombreLibro = "- Profesor"; if (reporte.equalsIgnoreCase("entregados") || reporte.equalsIgnoreCase("noentregados") || reporte.equalsIgnoreCase("entregadosynoentregados") || //Aqui modifique Jesus Ruelas 26 oct 2015 reporte.equalsIgnoreCase("entregadosatiempo") || reporte.equalsIgnoreCase("entregadosdespueslimite") || reporte.equalsIgnoreCase("entregadosatiempoenfechalimiteydespues") || //Aqui modifique Jesus Ruelas 26 oct 2015 reporte.equalsIgnoreCase("Porcentaje de Avance Global Completo") || reporte.equalsIgnoreCase("Porcentaje de Avance Global Incompleto") || reporte.equalsIgnoreCase("Porcentaje de Avance Global Completo e Incompleto")) { if (reporte.equalsIgnoreCase("entregados")) nombreLibro = "Entregados " + nombreLibro; if (reporte.equalsIgnoreCase("noentregados")) nombreLibro = "No Entregados " + nombreLibro; if (reporte.equalsIgnoreCase("entregadosynoentregados")) nombreLibro = "Entregados Y No Entregados " + nombreLibro; //Aqui modifique Jesus Ruelas 26 oct 2015 if (reporte.equalsIgnoreCase("entregadosatiempo")) nombreLibro = "Entregados a tiempo " + nombreLibro; if (reporte.equalsIgnoreCase("entregadosdespueslimite")) nombreLibro = "Entregados despus de fecha lmite " + nombreLibro; if (reporte.equalsIgnoreCase("entregadosatiempoenfechalimiteydespues")) nombreLibro = "Entregados a tiempo y despues de fecha lmite " + nombreLibro; //Aqui modifique Jesus Ruelas 26 oct 2015 if (reporte.equalsIgnoreCase("Porcentaje de Avance Global Completo")) nombreLibro = "Porcentaje de Avance Global Completo " + nombreLibro; if (reporte.equalsIgnoreCase("Porcentaje de Avance Global Incompleto")) nombreLibro = "Porcentaje de Avance Global Incompleto " + nombreLibro; if (reporte.equalsIgnoreCase("Porcentaje de Avance Global Completo e Incompleto")) nombreLibro = "Porcentaje de Avance Global Completo e Incompleto " + nombreLibro; //definimos encabezado //sheet = cabezeraGeneralExcel(sheet,uabcLogo,style); //mostraremos tabla con // Programas educativos // Total Racs entregados // Total Esperados //preparamos informacion para insertar List<Programaeducativo> programasByUnidad = getProgramasByUnidad(); List<Profesor> profesoresByUAprend = getProfesoresByUnidadAprendisajeClave(); /* Programacion de Hojas*/ for (Profesor prof : profesoresByUAprend) { if (selectProfesor.contains(Integer.toString(prof.getPronumeroEmpleado()))) { //aqui creamos la hoja para el programa ArrayList<ReporteAvanceAux> tempListAux = new ArrayList<ReporteAvanceAux>(); for (ReporteAvanceAux aux : listaAux) { if (aux.reporteAvance.getUnidadaprendizajeImparteProfesor().getProfesor() .getPronumeroEmpleado() == prof.getPronumeroEmpleado()) { tempListAux.add(aux); } } //// de este for // para no imprirmir hojas vacias if (tempListAux.size() > 0) { String nompreP = tempListAux.get(0).getReporteAvance() .getUnidadaprendizajeImparteProfesor().getProfesor().getPronombre(); String apellidoPP = tempListAux.get(0).getReporteAvance() .getUnidadaprendizajeImparteProfesor().getProfesor().getProapellidoPaterno(); String apellidoPM = tempListAux.get(0).getReporteAvance() .getUnidadaprendizajeImparteProfesor().getProfesor().getProapellidoMaterno(); sheet = workbook.createSheet(String.valueOf(prof.getPronumeroEmpleado())); //sheet = cabezeraGeneralExcel(sheet,uabcLogo,style); lo moveremos al final de la hoja para que no sea mdoficada la imagen setExDat(sheet, 8, 3, "Concentrado de Reporte de Avance de Contenido Temtico de Entregados por Profesor " + prof.getPronumeroEmpleado() + " " + apellidoPP + " " + apellidoPM + " " + nompreP); boolean autotam = true; int currow = 18; //Aqui modifique Jesus Ruelas 26 oct 2015 if (reporte.equalsIgnoreCase("entregadosatiempo") || reporte.equalsIgnoreCase("entregadosdespueslimite") || reporte.equalsIgnoreCase("entregadosatiempoenfechalimiteydespues")) { if (ract.equalsIgnoreCase("1") || ract.equalsIgnoreCase("2") || ract.equalsIgnoreCase("3")) { setExDat(sheet, currow, 1, "Fecha Corte"); setExDat(sheet, currow, 2, "Fecha Lmite"); setStyleCell(sheet, headerTabla, currow, 1); setStyleCell(sheet, headerTabla, currow, 2); currow++; setExDat(sheet, currow, 1, tempListAux.get(0).getFechaCorte().toString()); setExDat(sheet, currow, 2, tempListAux.get(0).getFechaLimite().toString()); currow++; currow++; } else { ReporteAux rep1 = new ReporteAux(); ReporteAux rep2 = new ReporteAux(); ReporteAux rep3 = new ReporteAux(); rep1.setNumRact(1); rep2.setNumRact(2); rep3.setNumRact(3); rep1.setCescicloEscolar(tempListAux.get(0).getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUnidadaprendizaje() .getCicloescolar().getCescicloEscolar()); rep2.setCescicloEscolar(tempListAux.get(0).getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUnidadaprendizaje() .getCicloescolar().getCescicloEscolar()); rep3.setCescicloEscolar(tempListAux.get(0).getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUnidadaprendizaje() .getCicloescolar().getCescicloEscolar()); ReporteAvanceAux ra1 = new ReporteAvanceAux(); ReporteAvanceAux ra2 = new ReporteAvanceAux(); ReporteAvanceAux ra3 = new ReporteAvanceAux(); ra1 = filtrosBeanHelper.TiempoLimiteYCorte(rep1); ra2 = filtrosBeanHelper.TiempoLimiteYCorte(rep2); ra3 = filtrosBeanHelper.TiempoLimiteYCorte(rep3); setExDat(sheet, currow, 1, "Fecha Corte Ract 1"); setExDat(sheet, currow, 2, "Fecha Lmite Ract 1"); setStyleCell(sheet, headerTabla, currow, 1); setStyleCell(sheet, headerTabla, currow, 2); currow++; setExDat(sheet, currow, 1, ra1.getFechaCorte().toString()); setExDat(sheet, currow, 2, ra1.getFechaLimite().toString()); //currow++; setExDat(sheet, currow - 1, 4, "Fecha Corte Ract 2"); setExDat(sheet, currow - 1, 5, "Fecha Lmite Ract 2"); setStyleCell(sheet, headerTabla, currow - 1, 4); setStyleCell(sheet, headerTabla, currow - 1, 5); //currow++; setExDat(sheet, currow, 4, ra2.getFechaCorte().toString()); setExDat(sheet, currow, 5, ra2.getFechaLimite().toString()); //currow++; setExDat(sheet, currow - 1, 7, "Fecha Corte Ract 3"); setExDat(sheet, currow - 1, 8, "Fecha Lmite Ract 3"); setStyleCell(sheet, headerTabla, currow - 1, 7); setStyleCell(sheet, headerTabla, currow - 1, 8); //currow++; setExDat(sheet, currow, 7, ra3.getFechaCorte().toString()); setExDat(sheet, currow, 8, ra3.getFechaLimite().toString()); currow++; currow++; } } //Aqui modifique Jesus Ruelas 26 oct 2015 //autosize para la columna if (autotam) { sheet.autoSizeColumn(2); sheet.autoSizeColumn(4); sheet.autoSizeColumn(11); autotam = false; } //reporteAvance.unidadaprendizajeImparteProfesor.profesor.pronumeroEmpleado // setExDat(sheet, currow,3, tempListAux.get(0).getReporteAvance().getUnidadaprendizajeImparteProfesor().getProfesor().getPronumeroEmpleado() ); //numero empleado // setExDat(sheet, currow,4, apellidoPP+ " "+apellidoPM+ " "+nompreP ); //nombre maestro // setExDat(sheet, currow,5, tempListAux.get(0).getReporteAvance().getUnidadaprendizajeImparteProfesor().getGrupo().getGponumero() + "-"+ auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor().getUiptipoSubgrupo() + "-"+ auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor().getUipsubgrupo() ); //grupo numero setExDat(sheet, currow, 1, "Clave unidad de aprendizaje"); setExDat(sheet, currow, 2, "Unidad de aprendizaje"); setExDat(sheet, currow, 3, "Area de conocimiento"); setExDat(sheet, currow, 4, "Grupo"); setExDat(sheet, currow, 5, "% Avance 1er reporte"); setExDat(sheet, currow, 6, "Fecha de elaboracin 1er RACT"); setExDat(sheet, currow, 7, "% Avance 2do reporte"); setExDat(sheet, currow, 8, "Fecha de elaboracin 2do RACT"); setExDat(sheet, currow, 9, "% Avance 3er reporte"); setExDat(sheet, currow, 10, "Fecha de elaboracin 3er RACT"); // para formatear toda la linea for (int i = 1; i <= 10; i++) { setStyleCell(sheet, headerTabla, currow, i); } //currow++; currow++; for (ReporteAvanceAux auxRacs : tempListAux) { //marcamos bordes for (int i = 1; i <= 10; i++) { setExDat(sheet, currow, i, " "); } for (int i = 1; i <= 10; i++) { setStyleCell(sheet, borderstabla, currow, i); } //currow++; int claveUnidadApren = auxRacs.getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUnidadaprendizaje().getUapclave(); String nombreUnidad = auxRacs.getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUnidadaprendizaje() .getUapnombre(); String areaCon = auxRacs.getAreaConocimiento().getAconombre(); //String grupo = auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor().getGrupo().getAreaConocimiento(); int uniprofeTemp = 0; uniprofeTemp = claveUnidadApren; setExDat(sheet, currow, 1, claveUnidadApren); //clave setExDat(sheet, currow, 2, nombreUnidad); //clave setExDat(sheet, currow, 3, areaCon); //nombre unidad setExDat(sheet, currow, 4, auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor().getGrupo() .getGponumero() + "-" + auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getUiptipoSubgrupo() + "-" + auxRacs.getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUipsubgrupo()); //grupo numero //marcamos bordes for (int i = 1; i <= 5; i++) { setStyleCell(sheet, borderstabla, currow, i); } //tienne que ser entregados/enviados if (auxRacs.getStatusRact1() != null) { System.out.println("Entro a enviados 1"); setExDat(sheet, currow, (3 + (1 * 2)), auxRacs.getPorcentAvanceRact1()); //% avance 7** if (auxRacs.getFechaElaboracRact1() != null) { setExDat(sheet, currow, (3 + (1 * 2) + 1), auxRacs.getFechaElaboracRact1().toString()); //fecha elabora 8*** } else { setExDat(sheet, currow, (3 + (1 * 2) + 1), " "); //fecha elabora 8*** } setStyleCell(sheet, borderstabla, currow, (3 + (1 * 2))); setStyleCell(sheet, borderstabla, currow, (3 + (1 * 2) + 1)); } else { setExDat(sheet, currow, (3 + (1 * 2)), " "); //% avance 7** setExDat(sheet, currow, (3 + (1 * 2) + 1), " "); //fecha elabora 8*** setStyleCell(sheet, borderstabla, currow, (3 + (1 * 2))); setStyleCell(sheet, borderstabla, currow, (3 + (1 * 2) + 1)); } if (auxRacs.getStatusRact2() != null) { setExDat(sheet, currow, (3 + (2 * 2)), auxRacs.getPorcentAvanceRact2()); //% avance 7** if (auxRacs.getFechaElaboracRact2() != null) { setExDat(sheet, currow, (3 + (2 * 2) + 1), auxRacs.getFechaElaboracRact2().toString()); //fecha elabora 8*** } else { setExDat(sheet, currow, (3 + (2 * 2) + 1), " "); //fecha elabora 8*** } setStyleCell(sheet, borderstabla, currow, (3 + (2 * 2))); setStyleCell(sheet, borderstabla, currow, (3 + (2 * 2) + 1)); } else { setExDat(sheet, currow, (3 + (2 * 2)), " "); //% avance 7** setExDat(sheet, currow, (3 + (2 * 2) + 1), " "); //fecha elabora 8*** setStyleCell(sheet, borderstabla, currow, (3 + (2 * 2))); setStyleCell(sheet, borderstabla, currow, (3 + (2 * 2) + 1)); } if (auxRacs.getStatusRact3() != null) { setExDat(sheet, currow, (3 + (3 * 2)), auxRacs.getPorcentAvanceRact3()); //% avance 7** if (auxRacs.getFechaElaboracRact3() != null) { setExDat(sheet, currow, (3 + (3 * 2) + 1), auxRacs.getFechaElaboracRact3().toString()); //fecha elabora 8*** } else { setExDat(sheet, currow, (3 + (3 * 2) + 1), " "); //fecha elabora 8*** } setStyleCell(sheet, borderstabla, currow, (3 + (3 * 2))); setStyleCell(sheet, borderstabla, currow, (3 + (3 * 2) + 1)); } else { setExDat(sheet, currow, (3 + (3 * 2)), " "); //% avance 7** setExDat(sheet, currow, (3 + (3 * 2) + 1), " "); //fecha elabora 8*** setStyleCell(sheet, borderstabla, currow, (3 + (3 * 2))); setStyleCell(sheet, borderstabla, currow, (3 + (3 * 2) + 1)); } currow++; } } // delfindelfor //fin de codigo e imrpesion de cabezera sheet = cabezeraGeneralExcel(sheet, uabcLogo, style); } } // fin del for por plan de estudios } // fin de si es por entregados if(entregados) } //fin de profesor if (criterio.equalsIgnoreCase("area_administrativa")) { HSSFSheet sheet = workbook.createSheet("Graficos"); nombreLibro = "- Area administrativa"; if (reporte.equalsIgnoreCase("entregados") || reporte.equalsIgnoreCase("noentregados") || reporte.equalsIgnoreCase("entregadosynoentregados") || //Aqui modifique Jesus Ruelas 26 oct 2015 reporte.equalsIgnoreCase("entregadosatiempo") || reporte.equalsIgnoreCase("entregadosdespueslimite") || reporte.equalsIgnoreCase("entregadosatiempoenfechalimiteydespues") || //Aqui modifique Jesus Ruelas 26 oct 2015 reporte.equalsIgnoreCase("Porcentaje de Avance Global Completo") || reporte.equalsIgnoreCase("Porcentaje de Avance Global Incompleto") || reporte.equalsIgnoreCase("Porcentaje de Avance Global Completo e Incompleto")) { if (reporte.equalsIgnoreCase("entregados")) nombreLibro = "Entregados " + nombreLibro; if (reporte.equalsIgnoreCase("noentregados")) nombreLibro = "No Entregados " + nombreLibro; if (reporte.equalsIgnoreCase("entregadosynoentregados")) nombreLibro = "Entregados Y No Entregados " + nombreLibro; //Aqui modifique Jesus Ruelas 26 oct 2015 if (reporte.equalsIgnoreCase("entregadosatiempo")) nombreLibro = "Entregados a tiempo " + nombreLibro; if (reporte.equalsIgnoreCase("entregadosdespueslimite")) nombreLibro = "Entregados despus de fecha lmite " + nombreLibro; if (reporte.equalsIgnoreCase("entregadosatiempoenfechalimiteydespues")) nombreLibro = "Entregados a tiempo y despues de fecha lmite " + nombreLibro; //Aqui modifique Jesus Ruelas 26 oct 2015 if (reporte.equalsIgnoreCase("Porcentaje de Avance Global Completo")) nombreLibro = "Porcentaje de Avance Global Completo " + nombreLibro; if (reporte.equalsIgnoreCase("Porcentaje de Avance Global Incompleto")) nombreLibro = "Porcentaje de Avance Global Incompleto " + nombreLibro; if (reporte.equalsIgnoreCase("Porcentaje de Avance Global Completo e Incompleto")) nombreLibro = "Porcentaje de Avance Global Completo e Incompleto " + nombreLibro; //definimos encabezado //sheet = cabezeraGeneralExcel(sheet,uabcLogo,style); //mostraremos tabla con // Programas educativos // Total Racs entregados // Total Esperados //preparamos informacion para insertar List<Programaeducativo> programasByUnidad = getProgramasByUnidad(); List<Areaadministrativa> areasByProgEd = getAreasAdminByProgEdClave(); //Aqui modifique Jesus Ruelas 28 oct 2015 sheet = workbook.createSheet("Hoja 1"); int currow = 15; //Aqui modifique Jesus Ruelas 28 oct 2015 /* Programacion de Hojas*/ for (Areaadministrativa areaAdmin : areasByProgEd) { if (selectAreaAdministrativa.contains(Integer.toString(areaAdmin.getAadid()))) { //aqui creamos la hoja para el programa ArrayList<ReporteAvanceAux> tempListAux = new ArrayList<ReporteAvanceAux>(); for (ReporteAvanceAux aux : listaAux) { if (aux.CAAnombre.equalsIgnoreCase(areaAdmin.getAadnombre())) { tempListAux.add(aux); } } //// de este for // para no imprirmir hojas vacias if (tempListAux.size() > 0) { //comente esta linea de abajo Jesus Ruelas - 28 oct 2015 //sheet = workbook.createSheet(areaConocimiento.getAconombre()); //sheet = cabezeraGeneralExcel(sheet,uabcLogo,style); lo moveremos al final de la hoja para que no sea mdoficada la imagen setExDat(sheet, 8, 3, "Concentrado de Reporte de Avance de Contenido Temtico de Entregados por Area administrativa"); boolean autotam = true; //currow = currow + 3; //Aqui modifique Jesus Ruelas 26 oct 2015 if (reporte.equalsIgnoreCase("entregadosatiempo") || reporte.equalsIgnoreCase("entregadosdespueslimite") || reporte.equalsIgnoreCase("entregadosatiempoenfechalimiteydespues")) { if (ract.equalsIgnoreCase("1") || ract.equalsIgnoreCase("2") || ract.equalsIgnoreCase("3")) { setExDat(sheet, currow, 1, "Fecha Corte"); setExDat(sheet, currow, 2, "Fecha Lmite"); setStyleCell(sheet, headerTabla, currow, 1); setStyleCell(sheet, headerTabla, currow, 2); currow++; setExDat(sheet, currow, 1, tempListAux.get(0).getFechaCorte().toString()); setExDat(sheet, currow, 2, tempListAux.get(0).getFechaLimite().toString()); currow++; currow++; } else { ReporteAux rep1 = new ReporteAux(); ReporteAux rep2 = new ReporteAux(); ReporteAux rep3 = new ReporteAux(); rep1.setNumRact(1); rep2.setNumRact(2); rep3.setNumRact(3); rep1.setCescicloEscolar(tempListAux.get(0).getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUnidadaprendizaje() .getCicloescolar().getCescicloEscolar()); rep2.setCescicloEscolar(tempListAux.get(0).getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUnidadaprendizaje() .getCicloescolar().getCescicloEscolar()); rep3.setCescicloEscolar(tempListAux.get(0).getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUnidadaprendizaje() .getCicloescolar().getCescicloEscolar()); ReporteAvanceAux ra1 = new ReporteAvanceAux(); ReporteAvanceAux ra2 = new ReporteAvanceAux(); ReporteAvanceAux ra3 = new ReporteAvanceAux(); ra1 = filtrosBeanHelper.TiempoLimiteYCorte(rep1); ra2 = filtrosBeanHelper.TiempoLimiteYCorte(rep2); ra3 = filtrosBeanHelper.TiempoLimiteYCorte(rep3); setExDat(sheet, currow, 1, "Fecha Corte Ract 1"); setExDat(sheet, currow, 2, "Fecha Lmite Ract 1"); setStyleCell(sheet, headerTabla, currow, 1); setStyleCell(sheet, headerTabla, currow, 2); currow++; setExDat(sheet, currow, 1, ra1.getFechaCorte().toString()); setExDat(sheet, currow, 2, ra1.getFechaLimite().toString()); //currow++; setExDat(sheet, currow - 1, 4, "Fecha Corte Ract 2"); setExDat(sheet, currow - 1, 5, "Fecha Lmite Ract 2"); setStyleCell(sheet, headerTabla, currow - 1, 4); setStyleCell(sheet, headerTabla, currow - 1, 5); //currow++; setExDat(sheet, currow, 4, ra2.getFechaCorte().toString()); setExDat(sheet, currow, 5, ra2.getFechaLimite().toString()); //currow++; setExDat(sheet, currow - 1, 7, "Fecha Corte Ract 3"); setExDat(sheet, currow - 1, 8, "Fecha Lmite Ract 3"); setStyleCell(sheet, headerTabla, currow - 1, 7); setStyleCell(sheet, headerTabla, currow - 1, 8); //currow++; setExDat(sheet, currow, 7, ra3.getFechaCorte().toString()); setExDat(sheet, currow, 8, ra3.getFechaLimite().toString()); currow++; currow++; } } //Aqui modifique Jesus Ruelas 26 oct 2015 //autosize para la columna if (autotam) { sheet.autoSizeColumn(2); sheet.autoSizeColumn(4); sheet.autoSizeColumn(11); autotam = false; } // para formatear toda la linea for (int i = 1; i <= 11; i++) { setStyleCell(sheet, headerTabla, currow, i); } currow++; //Boolean siguienteAreaAdmin = true; String CAAnombreActual = ""; int uniprofeTemp = 0; for (ReporteAvanceAux auxRacs : tempListAux) { if (!(CAAnombreActual.equalsIgnoreCase(auxRacs.getCAAnombre()))) { CAAnombreActual = auxRacs.getCAAnombre(); // currow=currow+2; setExDat(sheet, currow, 1, "Area de conocimiento"); setExDat(sheet, currow, 2, "Area administrativa"); setStyleCell(sheet, headerTabla, currow, 1); setStyleCell(sheet, headerTabla, currow, 2); currow++; setExDat(sheet, currow, 1, auxRacs.getAreaConocimiento().getAconombre()); //clave setExDat(sheet, currow, 2, auxRacs.getCAAnombre()); //nombre unidad currow = currow + 2; //marcamos bordes for (int i = 1; i <= 11; i++) { setExDat(sheet, currow, i, " "); } for (int i = 1; i <= 11; i++) { setStyleCell(sheet, borderstabla, currow, i); } setExDat(sheet, currow, 1, "Clave unidad de aprendizaje"); setExDat(sheet, currow, 2, "Unidad de aprendizaje"); setExDat(sheet, currow, 3, "No. de empleado"); setExDat(sheet, currow, 4, "Nombre del profesor"); setExDat(sheet, currow, 5, "Grupo"); setExDat(sheet, currow, 6, "% Avance 1er reporte"); setExDat(sheet, currow, 7, "Fecha de elaboracin 1er RACT"); setExDat(sheet, currow, 8, "% Avance 2do reporte"); setExDat(sheet, currow, 9, "Fecha de elaboracin 2do RACT"); setExDat(sheet, currow, 10, "% Avance 3er reporte"); setExDat(sheet, currow, 11, "Fecha de elaboracin 3er RACT"); // para formatear toda la linea for (int i = 1; i <= 11; i++) { //tenemos qu aajustar texto //tenemos que centrar el texto //cambiar el color de fondo setStyleCell(sheet, headerTabla, currow, i); } currow++; } String nompreP = auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getProfesor().getPronombre(); String apellidoPP = auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getProfesor().getProapellidoPaterno(); String apellidoPM = auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getProfesor().getProapellidoMaterno(); int claveUnidadApren = auxRacs.getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUnidadaprendizaje().getUapclave(); String nombreUnidad = auxRacs.getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUnidadaprendizaje() .getUapnombre(); uniprofeTemp = claveUnidadApren; setExDat(sheet, currow, 1, claveUnidadApren); //clave setExDat(sheet, currow, 2, nombreUnidad); //nombre unidad //reporteAvance.unidadaprendizajeImparteProfesor.profesor.pronumeroEmpleado setExDat(sheet, currow, 3, auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getProfesor().getPronumeroEmpleado()); //numero empleado setExDat(sheet, currow, 4, apellidoPP + " " + apellidoPM + " " + nompreP); //nombre maestro setExDat(sheet, currow, 5, auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor().getGrupo() .getGponumero() + "-" + auxRacs.getReporteAvance().getUnidadaprendizajeImparteProfesor() .getUiptipoSubgrupo() + "-" + auxRacs.getReporteAvance() .getUnidadaprendizajeImparteProfesor().getUipsubgrupo()); //grupo numero //marcamos bordes for (int i = 1; i <= 5; i++) { setStyleCell(sheet, borderstabla, currow, i); } //tienne que ser entregados/enviados if (auxRacs.getStatusRact1() != null) { System.out.println("Entro a enviados 1"); setExDat(sheet, currow, (4 + (1 * 2)), auxRacs.getPorcentAvanceRact1()); //% avance 7** if (auxRacs.getFechaElaboracRact1() != null) { setExDat(sheet, currow, (4 + (1 * 2) + 1), auxRacs.getFechaElaboracRact1().toString()); //fecha elabora 8*** } else { setExDat(sheet, currow, (4 + (1 * 2) + 1), " "); //fecha elabora 8*** } setStyleCell(sheet, borderstabla, currow, (4 + (1 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (1 * 2) + 1)); } else { setExDat(sheet, currow, (4 + (1 * 2)), " "); //% avance 7** setExDat(sheet, currow, (4 + (1 * 2) + 1), " "); //fecha elabora 8*** setStyleCell(sheet, borderstabla, currow, (4 + (1 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (1 * 2) + 1)); } if (auxRacs.getStatusRact2() != null) { setExDat(sheet, currow, (4 + (2 * 2)), auxRacs.getPorcentAvanceRact2()); //% avance 7** if (auxRacs.getFechaElaboracRact2() != null) { setExDat(sheet, currow, (4 + (2 * 2) + 1), auxRacs.getFechaElaboracRact2().toString()); //fecha elabora 8*** } else { setExDat(sheet, currow, (4 + (2 * 2) + 1), " "); //fecha elabora 8*** } setStyleCell(sheet, borderstabla, currow, (4 + (2 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (2 * 2) + 1)); } else { setExDat(sheet, currow, (4 + (2 * 2)), " "); //% avance 7** setExDat(sheet, currow, (4 + (2 * 2) + 1), " "); //fecha elabora 8*** setStyleCell(sheet, borderstabla, currow, (4 + (2 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (2 * 2) + 1)); } if (auxRacs.getStatusRact3() != null) { setExDat(sheet, currow, (4 + (3 * 2)), auxRacs.getPorcentAvanceRact3()); //% avance 7** if (auxRacs.getFechaElaboracRact3() != null) { setExDat(sheet, currow, (4 + (3 * 2) + 1), auxRacs.getFechaElaboracRact3().toString()); //fecha elabora 8*** } else { setExDat(sheet, currow, (4 + (3 * 2) + 1), " "); //fecha elabora 8*** } setStyleCell(sheet, borderstabla, currow, (4 + (3 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (3 * 2) + 1)); } else { setExDat(sheet, currow, (4 + (3 * 2)), " "); //% avance 7** setExDat(sheet, currow, (4 + (3 * 2) + 1), " "); //fecha elabora 8*** setStyleCell(sheet, borderstabla, currow, (4 + (3 * 2))); setStyleCell(sheet, borderstabla, currow, (4 + (3 * 2) + 1)); } currow++; } } // delfindelfor //fin de codigo e imrpesion de cabezera sheet = cabezeraGeneralExcel(sheet, uabcLogo, style); } } // fin del for por plan de estudios } // fin de si es por entregados if(entregados) } //fin de areas administrativas //finalizamos con //metodo para descargar el objeto System.out.println("Generando Excel"); if (criterio != "" && reporte != "") { FacesContext facesContext = FacesContext.getCurrentInstance(); ExternalContext externalContext = facesContext.getExternalContext(); externalContext.setResponseContentType("application/vnd.ms-excel"); externalContext.setResponseHeader("Content-Disposition", "attachment; filename=\"" + nombreLibro + ".xls\""); //genera libro workbook.write(externalContext.getResponseOutputStream()); facesContext.responseComplete(); } else { System.out.println("No se Genero por: Criterio->" + criterio + " , Reporte->" + reporte); } }
From source file:mx.avanti.siract.ui.FiltrosBeanUI.java
public HSSFSheet setExDat(HSSFSheet sheet, int prow, int pcol, String valor) { if (sheet.getRow(prow) != null) { HSSFRow row = sheet.getRow(prow); HSSFCell cell = row.createCell(pcol); cell.setCellValue(valor);/*w w w.j a va2s . c om*/ } else { HSSFRow row = sheet.createRow(prow); HSSFCell cell = row.createCell(pcol); cell.setCellValue(valor); } return sheet; }