List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getStringCellValue
public String getStringCellValue()
From source file:ispyb.common.util.upload.UploadShipmentUtils.java
License:Open Source License
/** * Converts from Excel Cell contents to a String * //w ww . j a v a 2 s . c o m * @param cell * The Cell to convert * @return A String value of the contents of the cell */ public static String cellToString(HSSFCell cell) { String retVal = ""; if (cell == null) { return retVal; } if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { retVal = cell.getStringCellValue(); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { retVal = String.valueOf(new Double(cell.getNumericCellValue()).intValue()); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) { if (new Boolean(cell.getBooleanCellValue()) == Boolean.TRUE) { retVal = "true"; } else { retVal = "false"; } } return retVal; }
From source file:ispyb.common.util.upload.XLSParser.java
License:Open Source License
/** * Converts from Excel Cell contents to a String * //from w w w .j a v a 2s . c om * @param cell * The Cell to convert * @return A String value of the contents of the cell */ public String cellToString(HSSFCell cell) { String retVal = ""; if (cell == null) { return retVal; } if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { retVal = cell.getStringCellValue(); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { retVal = String.valueOf(new Double(cell.getNumericCellValue()).intValue()); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) { if (new Boolean(cell.getBooleanCellValue()) == Boolean.TRUE) { retVal = "true"; } else { retVal = "false"; } } return retVal; }
From source file:it.filippovitale.fineco2qif.logic.ExcelSheetAnalysisLogic.java
License:Apache License
public static List<String[]> getCells(HSSFSheet sheet, Short[] columnAbsolutePositions, boolean looseConstraint) { List<String[]> cells = new ArrayList<String[]>(); for (int rowAbsolutePosition = sheet.getFirstRowNum() + 1; rowAbsolutePosition <= sheet .getLastRowNum(); rowAbsolutePosition++) { HSSFRow row = sheet.getRow(rowAbsolutePosition); if (row == null) { if (looseConstraint) { log.debug("empty row in the absolute position \"" + rowAbsolutePosition + "\""); } else { log.warn("row in the absolute position \"" + rowAbsolutePosition + "\" has problem"); }//from w ww.j a v a 2 s . c o m continue; } String cellStringValues[] = new String[5]; for (int columnRelativePosition = 0; columnRelativePosition < cellStringValues.length; columnRelativePosition++) { HSSFCell cell = row.getCell(columnAbsolutePositions[columnRelativePosition]); String cellStringValue = cell != null ? cell.getStringCellValue() : null; if (cellStringValue != null && cellStringValue.trim().equals("")) { cellStringValue = null; } cellStringValues[columnRelativePosition] = cellStringValue; } if (cellStringValues[0] == null && cellStringValues[1] == null && cellStringValues[2] == null && cellStringValues[3] == null && cellStringValues[4] == null) { if (looseConstraint) { log.debug("empty row in the absolute position \"" + rowAbsolutePosition + "\""); } else { log.warn("row in the absolute position \"" + rowAbsolutePosition + "\" has problem, it's {null, null, null, null, null}"); } } else { cells.add(cellStringValues); } } dumpCells(cells); validateCells(cells, looseConstraint); return cells; }
From source file:it.filippovitale.fineco2qif.logic.ExcelSheetAnalysisLogic.java
License:Apache License
public static String getCellValue(HSSFCell cell) { switch (cell != null ? cell.getCellType() : Integer.MIN_VALUE) { case HSSFCell.CELL_TYPE_STRING: return cell.getStringCellValue(); case HSSFCell.CELL_TYPE_NUMERIC: return String.valueOf(cell.getNumericCellValue()); default://from ww w . j av a 2s. c o m return NULL_RAPPRESENTATION; } }
From source file:javaexecelprocess.JavaExecelProcess.java
public long[] getFieldsLen() { // Integer dat = new Integer(7); int iCols = getColumns(); long[] fieldsLen = new long[iCols]; for (int i = 0; i < iCols; i++) { fieldsLen[i] = -1;//from w ww . j av a 2 s.com } HSSFSheet activeSheet = wb.getSheetAt(0); int iFirstRow = activeSheet.getFirstRowNum(); int iLastRow = activeSheet.getLastRowNum(); for (int i = iFirstRow + 1; i <= iLastRow; i++) { HSSFRow row = activeSheet.getRow(i); int iFirstCol = row.getFirstCellNum(); int iLastCol = row.getLastCellNum(); for (int j = iFirstCol; j < iLastCol; j++) { HSSFCell cell = row.getCell(j); int cellType = cell.getCellType(); if (HSSFCell.CELL_TYPE_STRING == cellType) { long tmpLen = cell.getStringCellValue().length(); if (fieldsLen[j - iFirstCol] < tmpLen) { fieldsLen[j - iFirstCol] = tmpLen; } } else if (HSSFCell.CELL_TYPE_NUMERIC == cellType) { fieldsLen[j - iFirstCol] = -1; } else { } } } return fieldsLen; }
From source file:LoadExcels.loadPMTCT_FO.java
@Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {/*from w w w . ja v a2 s. c o m*/ year = quarter = mflcode = Numerator = Denominator = checker = missing = added = updated = 0; String linked_art = "0"; String not_linked_art = "0"; String unknown_link = "0"; String not_breastfeeding = "0"; String breastfeeding = "0"; String breastfeeding_unknown = "0"; String care_no_test = "0"; String ltfu = "0"; String died = "0"; String transferred_out = "0"; 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 = 1, y = 0; while (rowIterator.hasNext()) { HSSFRow rowi = worksheet.getRow(i); if (rowi == null) { nextpage = "loadExcel.jsp"; break; } //Year Quarter County Sub County Health Facility MFL Code Type of support Numerator Denominator HIV-infected:Linked to ART HIV-infected: Not linked to ART HIV-infected : Unknown link HIV-uninfected:Not beastfeeding HIV-uninfected: Still breastfeeeding HIV-uninfected:Breastfeeding unknown Other outcomes: In care but not test done Other outcomes:Lost to follow up Other outcomes : Died Other outcomes:Transferred out int rowcount = 0; HSSFCell cellYear = rowi.getCell((short) rowcount); rowcount++; year = (int) cellYear.getNumericCellValue(); HSSFCell cellQuarter = rowi.getCell((short) rowcount); rowcount = rowcount + 3; quarterName = cellQuarter.getStringCellValue(); HSSFCell cellFacilityName = rowi.getCell((short) rowcount); facilityName = cellFacilityName.getStringCellValue(); rowcount++; //HSSFCell cellMFLCODE = rowi.getCell((short) rowcount); //mflcode = Integer.parseInt(cellMFLCODE.getStringCellValue()); if (1 == 1) { HSSFCell cellrow = rowi.getCell((short) rowcount); rowcount = rowcount + 2; if (cellrow.getCellType() == 0) { //numeric mflcode = (int) cellrow.getNumericCellValue(); } else if (cellrow.getCellType() == 1) { mflcode = new Integer(cellrow.getStringCellValue()); } } HSSFCell cellNumerator = rowi.getCell((short) rowcount); rowcount++; Numerator = (int) cellNumerator.getNumericCellValue(); HSSFCell cellDenominator = rowi.getCell((short) rowcount); rowcount++; Denominator = (int) cellDenominator.getNumericCellValue(); //linked_art if (1 == 1) { HSSFCell cellrow = rowi.getCell((short) rowcount); rowcount++; if (cellrow.getCellType() == 0) { //numeric linked_art = "" + (int) cellrow.getNumericCellValue(); } else if (cellrow.getCellType() == 1) { linked_art = cellrow.getStringCellValue(); } } //not_linked_art if (1 == 1) { HSSFCell cellrow = rowi.getCell((short) rowcount); rowcount++; if (cellrow.getCellType() == 0) { //numeric not_linked_art = "" + (int) cellrow.getNumericCellValue(); } else if (cellrow.getCellType() == 1) { not_linked_art = cellrow.getStringCellValue(); } } //unknown_link if (1 == 1) { HSSFCell cellrow = rowi.getCell((short) rowcount); rowcount++; if (cellrow.getCellType() == 0) { //numeric unknown_link = "" + (int) cellrow.getNumericCellValue(); } else if (cellrow.getCellType() == 1) { unknown_link = cellrow.getStringCellValue(); } } // //not_breastfeeding if (1 == 1) { HSSFCell cellrow = rowi.getCell((short) rowcount); rowcount++; if (cellrow.getCellType() == 0) { //numeric not_breastfeeding = "" + (int) cellrow.getNumericCellValue(); } else if (cellrow.getCellType() == 1) { not_breastfeeding = cellrow.getStringCellValue(); } } //breastfeeding if (1 == 1) { HSSFCell cellrow = rowi.getCell((short) rowcount); rowcount++; if (cellrow.getCellType() == 0) { //numeric breastfeeding = "" + (int) cellrow.getNumericCellValue(); } else if (cellrow.getCellType() == 1) { breastfeeding = cellrow.getStringCellValue(); } } //breastfeeding_unknown if (1 == 1) { HSSFCell cellrow = rowi.getCell((short) rowcount); rowcount++; if (cellrow.getCellType() == 0) { //numeric breastfeeding_unknown = "" + (int) cellrow.getNumericCellValue(); } else if (cellrow.getCellType() == 1) { breastfeeding_unknown = cellrow.getStringCellValue(); } } //care_no_test if (1 == 1) { HSSFCell cellrow = rowi.getCell((short) rowcount); rowcount++; if (cellrow.getCellType() == 0) { //numeric care_no_test = "" + (int) cellrow.getNumericCellValue(); } else if (cellrow.getCellType() == 1) { care_no_test = cellrow.getStringCellValue(); } } //ltfu if (1 == 1) { HSSFCell cellrow = rowi.getCell((short) rowcount); rowcount++; if (cellrow.getCellType() == 0) { //numeric ltfu = "" + (int) cellrow.getNumericCellValue(); } else if (cellrow.getCellType() == 1) { ltfu = cellrow.getStringCellValue(); } } //died if (1 == 1) { HSSFCell cellrow = rowi.getCell((short) rowcount); rowcount++; if (cellrow.getCellType() == 0) { //numeric died = "" + (int) cellrow.getNumericCellValue(); } else if (cellrow.getCellType() == 1) { died = cellrow.getStringCellValue(); } } //transferred_out if (1 == 1) { HSSFCell cellrow = rowi.getCell((short) rowcount); rowcount++; if (cellrow.getCellType() == 0) { //numeric transferred_out = "" + (int) cellrow.getNumericCellValue(); } else if (cellrow.getCellType() == 1) { transferred_out = cellrow.getStringCellValue(); } } 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,linked_art,not_linked_art,unknown_link,not_breastfeeding,breastfeeding,breastfeeding_unknown,care_no_test,ltfu,died,transferred_out) " + "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.setString(7, linked_art); conn.pst.setString(8, not_linked_art); conn.pst.setString(9, unknown_link); conn.pst.setString(10, not_breastfeeding); conn.pst.setString(11, breastfeeding); conn.pst.setString(12, breastfeeding_unknown); conn.pst.setString(13, care_no_test); conn.pst.setString(14, ltfu); conn.pst.setString(15, died); conn.pst.setString(16, transferred_out); conn.pst.executeUpdate(); added++; } else { String inserter = "UPDATE pmtct_fo SET SubPartnerID=?,year=?,quarter=?,numerator=?,denominator=? ,linked_art=? ,not_linked_art = ? ,unknown_link = ? ,not_breastfeeding = ? ,breastfeeding = ? ,breastfeeding_unknown = ? ,care_no_test = ? ,ltfu = ? ,died = ? , transferred_out = ? 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, linked_art); conn.pst.setString(7, not_linked_art); conn.pst.setString(8, unknown_link); conn.pst.setString(9, not_breastfeeding); conn.pst.setString(10, breastfeeding); conn.pst.setString(11, breastfeeding_unknown); conn.pst.setString(12, care_no_test); conn.pst.setString(13, ltfu); conn.pst.setString(14, died); conn.pst.setString(15, transferred_out); conn.pst.setString(16, 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 = "Data for <b>" + added + "</b> sites newly added. <br/> Data for <b>" + updated + "</b> updated. <br/>Data for <b>" + missing + "</b> sites skipped because they are missing in IMIS"; session.setAttribute("pmtctresponse", sessionText); response.sendRedirect(nextpage); }
From source file:LoadExcels.loadPMTCT_FO_OLD.java
@Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {// ww w . j a v a 2s. com 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 {/*from w ww. ja v a 2s .c om*/ 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 {// www . jav a 2 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 . jav a2 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); }