List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getNumericCellValue
public double getNumericCellValue()
From source file:LoadExcels.loadPMTCT_FO_OLD.java
@Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {/* w ww . j a va 2s . c o m*/ session = request.getSession(); dbConn conn = new dbConn(); nextpage = "loadExcel.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 = "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.getSheet("PMTCT-FO"); Iterator rowIterator = worksheet.iterator(); int i = 2, y = 0; while (rowIterator.hasNext()) { HSSFRow rowi = worksheet.getRow(i); if (rowi == null) { nextpage = "loadExcel.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 = Integer.parseInt(cellMFLCODE.getStringCellValue()); HSSFCell cellNumerator = rowi.getCell((short) 7); Numerator = (int) cellNumerator.getNumericCellValue(); HSSFCell cellDenominator = rowi.getCell((short) 8); Denominator = (int) cellDenominator.getNumericCellValue(); //linked_art //not_linked_art //unknown_link //not_breastfeeding //breastfeeding //breastfeeding_unknown //care_no_test //ltfu //died //transferred_out facilityID = ""; checker = 0; String get_id = "SELECT SubPartnerID 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); } 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 pmtct_fo WHERE id='" + id + "'"; conn.rs = conn.st.executeQuery(checkerExisting); if (conn.rs.next() == true) { checker++; } // // // if (checker == 0) { String inserter = "INSERT INTO pmtct_fo (id,SubPartnerID,year,quarter,numerator,denominator) " + "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, Numerator); conn.pst.setInt(6, Denominator); conn.pst.executeUpdate(); added++; } else { String inserter = "UPDATE pmtct_fo SET SubPartnerID=?,year=?,quarter=?,numerator=?,denominator=? 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, Numerator); conn.pst.setInt(5, Denominator); conn.pst.setString(6, 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++; } //a code to loop through all synced records without a last month //the affected tables are "eid_datim","viral_load","pmtct_fo","tb_stat_art" AddLastMonth am = new AddLastMonth(); am.addfirstmonth(); //end of sync last month } } catch (SQLException ex) { Logger.getLogger(loadPMTCT_FO.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.loadTBExcel.java
@Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {// w ww .java 2 s. co m session = request.getSession(); dbConn conn = new dbConn(); nextpage = "loadTBExcel.jsp"; String female_stat, male_stat; String less1, stat_1to4, stat_5to9, stat_10to14, stat_15to19, stat_20above, positive, negative; String art_numerator, art_denominator, art_female; String art_male, art_less1, art_1to4, art_5to9, art_10to14, art_15to19, art_20above; female_stat = male_stat = ""; less1 = stat_1to4 = stat_5to9 = stat_10to14 = stat_15to19 = stat_20above = positive = negative = ""; art_numerator = art_denominator = art_female = ""; art_male = art_less1 = art_1to4 = art_5to9 = art_10to14 = art_15to19 = art_20above = ""; 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 " + i); HSSFRow rowi = worksheet.getRow(i); if (rowi == null) { nextpage = "loadTBExcel.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); if (cellMFLCODE.getCellType() == 0) { //numeric mflcode = "" + (int) cellMFLCODE.getNumericCellValue(); } else if (cellMFLCODE.getCellType() == 1) { mflcode = cellMFLCODE.getStringCellValue(); } HSSFCell cellstype = rowi.getCell((short) 6); String supporttype = cellstype.getStringCellValue(); HSSFCell cellNumerator = rowi.getCell((short) 7); Numerator = "" + (int) cellNumerator.getNumericCellValue(); HSSFCell cellDenominator = rowi.getCell((short) 8); Denominator = (int) cellDenominator.getNumericCellValue(); HSSFCell cellfemale = rowi.getCell((short) 9); female_stat = "" + (int) cellfemale.getNumericCellValue(); HSSFCell cellmale = rowi.getCell((short) 10); male_stat = "" + (int) cellmale.getNumericCellValue(); HSSFCell cellless1 = rowi.getCell((short) 11); less1 = "" + (int) cellless1.getNumericCellValue(); HSSFCell cell1to4 = rowi.getCell((short) 12); stat_1to4 = "" + (int) cell1to4.getNumericCellValue(); HSSFCell cell5to9 = rowi.getCell((short) 13); stat_5to9 = "" + (int) cell5to9.getNumericCellValue(); HSSFCell cell10to14 = rowi.getCell((short) 14); stat_10to14 = "" + (int) cell10to14.getNumericCellValue(); HSSFCell cell15to19 = rowi.getCell((short) 15); stat_15to19 = "" + (int) cell15to19.getNumericCellValue(); HSSFCell cell20above = rowi.getCell((short) 16); stat_20above = "" + (int) cell20above.getNumericCellValue(); HSSFCell cellpositive = rowi.getCell((short) 17); positive = "" + (int) cellpositive.getNumericCellValue(); HSSFCell cellnegative = rowi.getCell((short) 18); negative = "" + (int) cellnegative.getNumericCellValue(); HSSFCell cellart_numerator = rowi.getCell((short) 19); art_numerator = "" + (int) cellart_numerator.getNumericCellValue(); HSSFCell cellart_denominator = rowi.getCell((short) 20); art_denominator = "" + (int) cellart_denominator.getNumericCellValue(); HSSFCell cellart_female = rowi.getCell((short) 21); art_female = "" + (int) cellart_female.getNumericCellValue(); HSSFCell cellart_male = rowi.getCell((short) 22); art_male = "" + (int) cellart_male.getNumericCellValue(); HSSFCell cellart_less1 = rowi.getCell((short) 23); art_less1 = "" + (int) cellart_less1.getNumericCellValue(); HSSFCell cellart_1to4 = rowi.getCell((short) 24); art_1to4 = "" + (int) cellart_1to4.getNumericCellValue(); HSSFCell cellart_5to9 = rowi.getCell((short) 25); art_5to9 = "" + (int) cellart_5to9.getNumericCellValue(); HSSFCell cellart_10to14 = rowi.getCell((short) 26); art_10to14 = "" + (int) cellart_10to14.getNumericCellValue(); HSSFCell cellart_15to19 = rowi.getCell((short) 27); art_15to19 = "" + (int) cellart_15to19.getNumericCellValue(); HSSFCell cellart_20above = rowi.getCell((short) 28); art_20above = "" + (int) cellart_20above.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 tb_stat_art WHERE id='" + id + "'"; conn.rs = conn.st.executeQuery(checkerExisting); if (conn.rs.next() == true) { checker++; } // // // if (checker == 0) { String inserter = "INSERT INTO tb_stat_art (id,SubPartnerID,year,quarter,numerator,denominator,female,male,less1,1to4,5to9,10to14,15to19,20above,positive,negative,art_numerator,art_denominator,art_female," + "art_male,art_less1,art_1to4,art_5to9,art_10to14,art_15to19,art_20above,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); conn.pst.setInt(6, Denominator); conn.pst.setString(7, female_stat); conn.pst.setString(8, male_stat); conn.pst.setString(9, less1); conn.pst.setString(10, stat_1to4); conn.pst.setString(11, stat_5to9); conn.pst.setString(12, stat_10to14); conn.pst.setString(13, stat_15to19); conn.pst.setString(14, stat_20above); conn.pst.setString(15, positive); conn.pst.setString(16, negative); conn.pst.setString(17, art_numerator); conn.pst.setString(18, art_denominator); conn.pst.setString(19, art_female); conn.pst.setString(20, art_male); conn.pst.setString(21, art_less1); conn.pst.setString(22, art_1to4); conn.pst.setString(23, art_5to9); conn.pst.setString(24, art_10to14); conn.pst.setString(25, art_15to19); conn.pst.setString(26, art_20above); conn.pst.setString(27, supporttype); conn.pst.executeUpdate(); added++; } else { String inserter = "UPDATE tb_stat_art SET SubPartnerID=?,year=?,quarter=?,numerator=?,denominator=?, " + "female=?,male=?,less1=?,1to4=?,5to9=?,10to14=?,15to19=?,20above=?,positive=?,negative=?,art_numerator=?,art_denominator=?," + "art_female=?,art_male=?,art_less1=?,art_1to4=?,art_5to9=?,art_10to14=?,art_15to19=?,art_20above=?,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); conn.pst.setInt(5, Denominator); conn.pst.setString(6, female_stat); conn.pst.setString(7, male_stat); conn.pst.setString(8, less1); conn.pst.setString(9, stat_1to4); conn.pst.setString(10, stat_5to9); conn.pst.setString(11, stat_10to14); conn.pst.setString(12, stat_15to19); conn.pst.setString(13, stat_20above); conn.pst.setString(14, positive); conn.pst.setString(15, negative); conn.pst.setString(16, art_numerator); conn.pst.setString(17, art_denominator); conn.pst.setString(18, art_female); conn.pst.setString(19, art_male); conn.pst.setString(20, art_less1); conn.pst.setString(21, art_1to4); conn.pst.setString(22, art_5to9); conn.pst.setString(23, art_10to14); conn.pst.setString(24, art_15to19); conn.pst.setString(25, art_20above); conn.pst.setString(26, supporttype); conn.pst.setString(27, 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"; session.setAttribute("upload_success", sessionText); response.sendRedirect(nextpage); }
From source file:LoadExcels.loadViralLoad.java
@Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {/*from w w w . j ava2 s. 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;//from w w w.j a v a 2 s . c om 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 www .j a v a2s . c o 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:mnb.io.tabular.xls.HSSFPreparsedSheet.java
License:Open Source License
@Override public PreparsedEntry getEntry(int i, List<TableDescription> cols) { PreparsedEntry entry = null;// w ww .j a v a2s .c o m // otherwise we can't cast if (dataType == ReactionColumn.class) { entry = new PreparsedReaction(); } else if (dataType == EntityColumn.class) { entry = new PreparsedMetabolite(); } // add others HSSFRow row = worksheet.getRow(i); ExcelModelProperties properties = super.getProperties(); // for the described columns add them to the preparsed entry for (TableDescription col : cols) { int index = properties.getColumnIndex(col); HSSFCell cell = row.getCell(index); if (cell != null) { int type = cell.getCellType(); if (type == HSSFCell.CELL_TYPE_NUMERIC) { entry.addValue(col, Double.toString(cell.getNumericCellValue())); } else if (type != HSSFCell.CELL_TYPE_BLANK) { cell.setCellType(HSSFCell.CELL_TYPE_STRING); entry.addValue(col, cell.getStringCellValue()); } } } return entry; }
From source file:model.bank.BankTransDAO.java
/** * Metodo que permiete importar las transacciones o movimientos de una cuenta bancaria * desde un archivo de excel emitido por el Banco Internacional * @param idBankAccount numero de id de la cuenta a la que se importaran los datos * @param fileXls <b>File</b> del archivo excel con los datos a importar * @param hacerArchivoLog/*from w ww . j av a 2 s .co m*/ * @return el numero de registros agregados * @throws java.lang.ClassNotFoundException * @throws java.sql.SQLException */ public static int importExcel(int idBankAccount, File fileXls, boolean hacerArchivoLog) throws ClassNotFoundException, SQLException { int rowsImported = 0; DknConsole.msg(Thread.currentThread().getStackTrace()[1].toString(), "Importando datos desde: " + fileXls.getAbsolutePath() + " - a idcuenta: " + idBankAccount); if (idBankAccount > 0 && fileXls != null) { int numRegImportados = 0; FileTxt archLog = null; if (fileXls.exists()) { // if (hacerArchivoLog) { // String nombreArchLog = fileXls.getAbsolutePath(); // nombreArchLog.replace(".xlsx", ".log"); // nombreArchLog.replace(".xls", ".log"); // archLog = new FileTxt(nombreArchLog); // DknConsole.msg(Thread.currentThread().getStackTrace()[1].toString(), "Importando desde Excel. Archivo: " + archLog.getFile().getAbsolutePath() + "*******************"); // } try { // crear un stream POIFSFileSystem poiFS; poiFS = new POIFSFileSystem(new FileInputStream(fileXls)); // if (hacerArchivoLog) { // archLog.open(FileTxt.OpenMode.WRITE); // } // crear una hoja de excel HSSFWorkbook libro = new HSSFWorkbook(poiFS); HSSFSheet sheet = libro.getSheetAt(0); HSSFRow row; HSSFCell cell; Iterator itr = sheet.rowIterator(); // extraer la informacion a un arrayList int rowsCount = 0; BankTrans trans = new BankTrans(); while (itr.hasNext()) { // reviso fila por fila row = (HSSFRow) itr.next(); if (rowsCount >= 4) { // si la fila es la 4 o mayor importo los datos Iterator itc = row.cellIterator(); trans.setIdBankAccount(idBankAccount); trans.setValue(0.0); trans.setIdRegType(2); // <2> = registro importado int colCount = 0; double value = 0; while (itc.hasNext()) { // reviso celda por celda cell = (HSSFCell) itc.next(); // leo la informacion de la celda if (cell != null) { // si la celda no es nula switch (colCount) { case 0: // columna 0, nada break; case 1: // columna 1, fecha trans.setDate(DateTime.getStringToDateUtil(cell.getStringCellValue(), AppGlobal.getFormatDate())); break; case 2: // columna 2, tipo de transaccion String codTipo = cell.getStringCellValue().trim(); trans.setType(codTipo); Type btt = TypeDAO.get("banktranstypes", codTipo); if (btt != null) { trans.setIdType(btt.getId()); } else { String texto = "Tipo de transanccion bancaria no encontrada: " + codTipo + ". Creandola"; DknConsole.warning(Thread.currentThread().getStackTrace()[1].toString(), texto); VMessage.show(texto); btt = new Type(); btt.setCode(codTipo); btt.setName(codTipo); btt.setDescription(codTipo); btt.setActive(true); if (TypeDAO.update("banktranstypes", btt) > 0) { Type btt1 = TypeDAO.get("banktranstypes", codTipo); if (btt1 != null) { trans.setIdType(btt1.getId()); DknConsole.msg( Thread.currentThread().getStackTrace()[1].toString(), "Creada y utilizando el Tipo de transaccin bancaria id: " + btt1.getId() + " trans.id: " + trans.getIdType()); } } else { DknConsole.error( Thread.currentThread().getStackTrace()[1].toString(), "Tipo de transaccin bancaria No se pudo crear. Cdigo: " + codTipo); } } break; case 3: // columna 3, numero de transaccin trans.setNumber(cell.getStringCellValue().trim()); break; case 4: // columna 4, nada break; case 5: // columna 5, observaciones trans.setObservations(cell.getStringCellValue().trim()); break; case 6: // columna 6, nada break; case 7: // columna 7, valor debito if (cell.getNumericCellValue() > 0) { value = cell.getNumericCellValue(); value *= -1; trans.setValue(value); } break; case 8: // columna 8, valor credito if (cell.getNumericCellValue() > 0) { value = cell.getNumericCellValue(); trans.setValue(value); } break; default: break; } // fin del switch } // Fin celda nula colCount++; } // Fin while de celdas // System.out.println("Agregando: " + trans); String error = BankTransDAO.validate(trans); if (error == null) { if (BankTransDAO.update(trans) >= 0) { numRegImportados++; DknConsole.msg(Thread.currentThread().getStackTrace()[1].toString(), "Agregado: " + trans.getNumber()); if (hacerArchivoLog) { archLog.writeLine("Agregado: " + trans); } } else { DknConsole.warning(Thread.currentThread().getStackTrace()[1].toString(), "NO agregado: " + trans.getNumber()); if (hacerArchivoLog) { archLog.writeLine("NO agregado: " + trans); } } } else { DknConsole.error(Thread.currentThread().getStackTrace()[1].toString(), "NO paso la validacion: " + error + " - " + trans.getNumber()); if (hacerArchivoLog) { archLog.writeLine("NO paso la validacion: " + error + " - " + trans); } } } rowsCount++; } // Fin while de filas rowsImported = numRegImportados; DknConsole.msg(Thread.currentThread().getStackTrace()[1].toString(), "Importacion completa. Total " + numRegImportados + " registros importados."); // grabar los datos en la base de datos } catch (FileNotFoundException ex) { Logger.getLogger(BankTransDAO.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException | ParseException ex) { Logger.getLogger(BankTransDAO.class.getName()).log(Level.SEVERE, null, ex); } finally { if (hacerArchivoLog) { try { archLog.close(); } catch (IOException ex) { Logger.getLogger(BankTransDAO.class.getName()).log(Level.SEVERE, null, ex); } } } // JOptionPane.showMessageDialog(this, AppConfig.IMPORTACION_TEXTO, AppConfig.IMPORTACION_TITULO, JOptionPane.INFORMATION_MESSAGE); } else { DknConsole.error(Thread.currentThread().getStackTrace()[1].toString(), "Archivo no existe."); } } else { DknConsole.error(Thread.currentThread().getStackTrace()[1].toString(), "Archivo o cuenta no indicadas."); } return rowsImported; }
From source file:net.ceos.project.poi.annotated.core.TestUtils.java
License:Apache License
public static void validationNumeric(Integer integer, XlsElement xlsAnnotation, HSSFCell headerCell, HSSFCell contentCell) { assertEquals(xlsAnnotation.title(), headerCell.getStringCellValue()); Double d = contentCell.getNumericCellValue(); assertEquals(integer, Integer.valueOf(d.intValue())); }
From source file:net.intelliant.marketing.ContactListServices.java
License:Open Source License
@SuppressWarnings("unchecked") private static GenericValue insertIntoConfiguredCustomEntity(GenericDelegator delegator, Locale locale, String userLoginId, String entityName, HSSFRow excelRowData, Map<String, Object> columnMapper, Map<Integer, String> errorDetails) throws GenericEntityException, ParseException { ModelEntity modelEntity = delegator.getModelEntity(entityName); String entityPrimaryKeyField = modelEntity.getFirstPkFieldName(); String entityPrimaryKey = delegator.getNextSeqId(entityName); GenericValue rowToInsertGV = delegator.makeValue(entityName); rowToInsertGV.put(entityPrimaryKeyField, entityPrimaryKey); rowToInsertGV.put("importedOnDateTime", UtilDateTime.nowTimestamp()); rowToInsertGV.put("importedByUserLogin", userLoginId); boolean isErrorFound = false; Set<Entry<String, Object>> entries = columnMapper.entrySet(); for (Map.Entry<String, Object> entry : entries) { String columnName = entry.getKey(); ModelField modelField = modelEntity.getField(columnName); HSSFCell excelCell = null; Object cellValue = null;//from w w w . j a v a 2 s . c o m short columnIndex = -1; try { columnIndex = Short.parseShort(String.valueOf(entry.getValue())); excelCell = excelRowData.getCell(columnIndex); cellValue = (excelCell != null) ? excelCell.toString() : ""; } catch (NumberFormatException nfe) { cellValue = ""; } if (Debug.infoOn()) { Debug.logInfo( "[insertIntoConfiguredCustomEntity] Checking excel row No. >> " + excelRowData.getRowNum(), MODULE); Debug.logInfo("[insertIntoConfiguredCustomEntity] Checking excel columnIndex >> " + columnIndex, MODULE); if (excelCell != null) { Debug.logInfo("[insertIntoConfiguredCustomEntity] Checking excel column type >> " + excelCell.getCellType(), MODULE); } else { Debug.logInfo("[insertIntoConfiguredCustomEntity] excelCell found NULL for columnIndex >> " + columnIndex, MODULE); } Debug.logInfo("[insertIntoConfiguredCustomEntity] Checking model field >> " + modelField.getName(), MODULE); Debug.logInfo("[insertIntoConfiguredCustomEntity] Initial cellValue >> " + cellValue, MODULE); } if (modelField.getIsNotNull()) { if (!UtilValidate.isNotEmpty(cellValue)) { Map<String, Object> messageMap = UtilMisc.toMap("columnName", modelField.getDescription()); errorDetails.put((int) columnIndex, UtilProperties.getMessage(resource, "ErrorImportMapperIsEmpty", messageMap, locale)); isErrorFound = true; } } if (modelField.getType().equals("email")) { if (!(UtilValidate.isNotEmpty(cellValue) && UtilCommon.isValidEmailAddress(String.valueOf(cellValue)))) { Map<String, Object> messageMap = UtilMisc.toMap("columnName", cellValue); errorDetails.put((int) columnIndex, UtilProperties.getMessage(resource, "ErrorImportMapperNotValidEmail", messageMap, locale)); isErrorFound = true; } } else if (modelField.getType().equals("tel-number")) { if (UtilValidate.isNotEmpty(cellValue)) { Map<String, Object> messageMap = UtilMisc.toMap("columnName", cellValue); if (excelCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { if (Debug.infoOn()) { Debug.logInfo("[insertIntoConfiguredCustomEntity] Cell type is numeric", MODULE); } NumberFormat testNumberFormat = NumberFormat.getNumberInstance(); try { cellValue = (pattern.parse(testNumberFormat.format(excelCell.getNumericCellValue()))) .longValue(); } catch (ParseException e) { errorDetails.put((int) columnIndex, UtilProperties.getMessage(resource, "ErrorImportMapperNotValidPhoneNO", messageMap, locale)); isErrorFound = true; } } if (!UtilValidate.isInternationalPhoneNumber(String.valueOf(cellValue))) { errorDetails.put((int) columnIndex, UtilProperties.getMessage(resource, "ErrorImportMapperNotValidPhoneNO", messageMap, locale)); isErrorFound = true; } } } else if (modelField.getType().equals("date")) { try { cellValue = new java.sql.Date(excelCell.getDateCellValue().getTime()); } catch (Exception e) { cellValue = excelCell.toString(); Map<String, Object> messageMap = UtilMisc.toMap("columnName", cellValue); errorDetails.put((int) columnIndex, UtilProperties.getMessage(resource, "ErrorImportMapperNotValidDate", messageMap, locale)); isErrorFound = true; } if (!UtilValidate.isNotEmpty(cellValue)) { Map<String, Object> messageMap = UtilMisc.toMap("columnName", cellValue); errorDetails.put((int) columnIndex, UtilProperties.getMessage(resource, "ErrorImportMapperNotValidDate", messageMap, locale)); isErrorFound = true; } } if (Debug.infoOn()) { Debug.logInfo("[insertIntoConfiguredCustomEntity] Final cellValue >> " + cellValue, MODULE); } rowToInsertGV.put(columnName, cellValue); } if (isErrorFound) { throw new GenericEntityException("Errors found in spread sheet data"); } else { delegator.storeAll(UtilMisc.toList(rowToInsertGV)); } return rowToInsertGV; }
From source file:net.intelliant.tests.ContactListImportTest.java
License:Open Source License
@SuppressWarnings("unchecked") private void imortedDataTest(String importMapperId, String contactListId, String excelPath) throws GenericEntityException, FileNotFoundException, IOException { GenericValue importMapper = delegator.findByPrimaryKey("MailerImportMapper", UtilMisc.toMap("importMapperId", importMapperId)); List<GenericValue> importMapperColumns = delegator.findByAnd("MailerImportColumnMapper", UtilMisc.toMap("importMapperId", importMapperId)); List<GenericValue> mailerRecipients = delegator.findByAnd("MailerRecipientAndContactListView", UtilMisc.toMap("contactListId", contactListId), UtilMisc.toList("recipientListId")); HSSFWorkbook excelWorkbook = new HSSFWorkbook(new FileInputStream(excelPath)); HSSFSheet excelSheet = excelWorkbook.getSheetAt(0); Iterator<?> rows = excelSheet.rowIterator(); HSSFRow row = null;/* www .j a v a 2s . co m*/ if (importMapper.get("isFirstRowHeader").equals("Y")) { rows.next(); } int counter = 0; Timestamp testTimeStamp = null; Timestamp getTimestamp = null; String testString = null; String getString = null; int testNumber = 0; int getNumber = 0; ModelReader reader = delegator.getModelReader(); ModelEntity modelEntity = reader.getModelEntity("MailerRecipient"); ModelField modelField = null; while (rows.hasNext()) { row = (HSSFRow) rows.next(); if (validateRow(row, importMapperColumns, modelEntity)) { GenericValue mailerRecipient = mailerRecipients.get(counter++); Debug.log("##### [Row] : " + toStringHssfRow(row)); Debug.log("##### [Entity] : " + mailerRecipient); for (GenericValue importMapperColumn : importMapperColumns) { short columnIndex = Short.valueOf(String.valueOf(importMapperColumn.get("importFileColIdx"))); String columnName = (String) importMapperColumn.get("entityColName"); modelField = modelEntity.getField(columnName); String columnType = modelField.getType(); HSSFCell cell = row.getCell(columnIndex); if (columnType.equals("date") || columnType.equals("date-time")) { testTimeStamp = new Timestamp(mailerRecipient.getDate(columnName).getTime()); getTimestamp = new Timestamp(cell.getDateCellValue().getTime()); assertEquals(testTimeStamp, getTimestamp); } else if (columnName.equals("numeric")) { testNumber = mailerRecipient.getInteger(columnName); getNumber = (int) cell.getNumericCellValue(); assertEquals(testNumber, getNumber); } else { testString = mailerRecipient.getString(columnName); getString = getStringData(cell); Debug.log(testString + " # " + getString); assertEquals(testString, getString); } } } } }