List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getDateCellValue
public Date getDateCellValue()
From source file:include.excel_import.Outter.java
License:Open Source License
private void pump(Vector vector, HSSFCell hssfcell, String s) { String s1 = getCellType(hssfcell); if (s1.equals("INT")) { DecimalFormat decimalformat = new DecimalFormat("##"); String s2 = String.valueOf(decimalformat.format(hssfcell.getNumericCellValue())); vector.addElement(s2);/*from w ww . j a v a 2 s .c o m*/ } else if (s1.equals("INT1")) { DecimalFormat decimalformat1 = new DecimalFormat("##"); String s3 = String.valueOf(decimalformat1.format(hssfcell.getNumericCellValue())); vector.addElement(s3); } else if (s1.equals("DOUBLE")) { DecimalFormat decimalformat2 = new DecimalFormat("##.##"); String s4 = String.valueOf(decimalformat2.format(hssfcell.getNumericCellValue())); vector.addElement(s4); } else if (s1.equals("STRING")) vector.addElement(hssfcell.getStringCellValue()); else if (s1.equals("DATE")) { SimpleDateFormat simpledateformat = new SimpleDateFormat("yyyy-MM-dd"); String s5 = String.valueOf(simpledateformat.format(hssfcell.getDateCellValue())); vector.addElement(s5); } else if (s1.equals("BLANK")) if (s.equals("STRING")) vector.addElement(" "); else if (s.equals("INT")) vector.addElement(new Integer(0)); else if (s.equals("DOUBLE")) vector.addElement(new Double(0.0D)); else if (s.equals("DATE")) vector.addElement("0000-00-00"); else if (s.equals("UNKNOWN")) vector.addElement(" "); }
From source file:include.excel_import.XlsInfo.java
License:Open Source License
private void dump(Vector result, HSSFCell cell, String itemType) { String type = getCellDataType(cell); String num, date = null;/*from w ww . j a v a 2s .c o m*/ if (type.equals("INT")) { num = String.valueOf(intFormat.format(cell.getNumericCellValue())); result.addElement(num); } else if (type.equals("STRING")) { result.addElement(cell.getStringCellValue()); } else if (type.equals("DATE")) { date = String.valueOf(yyyymmddFormat.format(cell.getDateCellValue())); result.addElement(date); } else if (type.equals("DOUBLE")) { num = String.valueOf(doubleFormat.format(cell.getNumericCellValue())); result.addElement(num); } else if (type.equals("INT1")) { num = String.valueOf(intFormat.format(cell.getNumericCellValue())); result.addElement(num); } else if (type.equals("BLANK")) { if (itemType.equals("STRING")) { result.addElement(" "); } else if (itemType.equals("INT")) { result.addElement(new Integer(0)); } else if (itemType.equals("DOUBLE")) { result.addElement(new Double(0.00)); } else if (itemType.equals("DATE")) { result.addElement("0000-00-00"); } else if (itemType.equals("UNKNOWN")) { result.addElement(" "); } } type = null; num = null; date = null; }
From source file:info.jtrac.domain.ExcelFile.java
License:Apache License
public void convertSelectedColumnsToDate() { // could not find a better way to convert excel number to date HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet();/*ww w. ja va 2 s. c o m*/ HSSFRow row = sheet.createRow(0); HSSFCell cell = row.createCell((short) 0); for (int i : selectedColumns) { for (List<Cell> cells : rows) { Cell c = cells.get(i); if (c != null && c.value instanceof Double) { cell.setCellValue((Double) c.value); c.value = cell.getDateCellValue(); } } } }
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 ww. j a v a2s. c o m*/ String agestring = ""; String sex = "F"; String treatmentstartdate = ""; String hivtestdate = ""; String hivstatus = ""; String artstatus = ""; String artdate = ""; String treatmentoutcome = ""; String treatmentoutcomedate = ""; int ageinteger = 0; String supporttype = ""; String datacategory = ""; String dbname = "tibu_tb_raw"; String tbtype = ""; String patienttype = ""; String smear0 = ""; String smear2_3 = ""; String smear5 = ""; String smear6_8 = ""; String genexpert = ""; // if(request.getParameter("datacategory")!=null){ // datacategory=request.getParameter("datacategory"); // System.out.println(" data category fetched"); // } if (datacategory.equals("completeoutcome")) { //this is the data for monthly uploads dbname = "tibu_tb_raw_outcome"; } try { session = request.getSession(); dbConn conn = new dbConn(); nextpage = "sync_tb.jsp"; //--------------------------------------------------------------------- String applicationPath = request.getServletContext().getRealPath(""); String uploadFilePath = applicationPath + File.separator + UPLOAD_DIR; session = request.getSession(); File fileSaveDir = new File(uploadFilePath); if (!fileSaveDir.exists()) { fileSaveDir.mkdirs(); } System.out.println("Upload File Directory=" + fileSaveDir.getAbsolutePath()); for (Part part : request.getParts()) { fileName = getFileName(part); part.write(uploadFilePath + File.separator + fileName); System.out.println("file name is : " + fileName); } if (!fileName.endsWith(".xls")) { nextpage = "upload_tb_raw_data.jsp"; session.setAttribute("upload_success", "<font color=\"red\">Failed to load the excel file. Please choose the correct File.</font>"); } else { full_path = fileSaveDir.getAbsolutePath() + "\\" + fileName; System.out.println("the saved file directory is : " + full_path); // GET DATA FROM THE EXCEL AND AND OUTPUT IT ON THE CONSOLE.................................. FileInputStream fileInputStream = new FileInputStream(full_path); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet worksheet = workbook.getSheetAt(0); Iterator rowIterator = worksheet.iterator(); int i = 1, y = 0; while (rowIterator.hasNext()) { // System.out.println(" in while"); HSSFRow rowi = worksheet.getRow(i); if (rowi == null) { nextpage = "sync_tb.jsp"; break; } //([0])Serial Number ([1])Date of Registration ([2])District Registration Number (3)Province (4)County (5)District (6)Zone ([7])Health Facility ([8])Year ([9])Quarter (10)Sector (11)Patient Name ([12])Sex M/F ([13])Age on registration (14)Weight (Kgs) (15)Height (Mtrs) (16)BMI (17)MUAC (18)Physical address (Neighbor,Primary School) Cell Phone (19)DOT by (20)Type of TB P/EP (21)EPTB Sub Type (22)EPTB Others (23)Type of patient (24)CD4 First Date (25)CD4 Last Date (26)Culture S (27)Culture R (28)Culture E (29)Culture H (30)X-ray Y/N (31)Sputum Smear Examination (32)0th Month Result (33)0th Month Serial No and Quantification (34)Sputum Smear Examination 2by3 Month Result (35)2by3 Month Serial No and Quantification (36)Sputum Smear Examination 5th Month Result (37)5th Month Serial No and Quantification (38)Sputum Smear Examination 6by8 Month Result (39)6by8 Month Serial No and Quantification (40)Regimen ([41])Date of treatment started (42)Gen expert (43)Lipa Hain Rifampicin (44)Lipa Hain Isoniazid ([45])HIV Test Date ([46])HIV Status (47)Partner HIV Test Date (48)Partner HIV Status (49)Referred BY: VCT/HCC/STI/HBC/PS/ANC/SR/CI (50)Referred TO VCT/HCC/STI/HBC/PS/ANC (51)Cotrimoxazole Preventive Therapy Y/N (52)Cotrimoxazole Preventive Therapy (Date Started) ([53])ART Y/N ([54])ART (Date Started) (55)Nutrition Support ([56])Treatment Outcome ([57])Treatment Outcome Date ([58])Remarks //<tr><td>(0)Serial Number</td><td>(1)Date of Registration</td><td>(2)District Registration Number</td><td>(3)Province</td><td>(4) County</td><td>(5)District</td><td>(6)Zone</td><td>(7)Health Facility</td><td>(8)Year</td><td>(9)Quarter</td><td>(10)Sector</td><td>(11)Patient Name</td><td>(12)Sex M/F</td><td>(13)Age on registration</td><td>(14)Weight (Kgs)</td><td>(15)Height (Mtrs)</td><td>(16)BMI</td><td>(17)MUAC</td><td>(18)Physical address (Neighbor,Primary School) Cell Phone</td><td>(19)DOT by</td><td>(20)Type of TB P/EP</td><td>(21)EPTB Sub Type</td><td>(22) EPTB Others</td><td>(23)Type of patient </td><td>(24)CD4 First Date</td><td>(25)CD4 Last Date</td><td>(26)Culture S</td><td>(27)Culture R</td><td>(28)Culture E </td><td>(29)Culture H</td><td>(30)X-ray Y/N</td><td>(31)Sputum Smear Examination</td><td>(32) 0th Month Result</td><td>(33)0th Month Serial No and Quantification</td><td>(34)Sputum Smear Examination 2by3 Month Result</td><td>(35)2by3 Month Serial No and Quantification</td><td>(36)Sputum Smear Examination 5th Month Result</td><td>(37)5th Month Serial No and Quantification</td><td>(38)Sputum Smear Examination 6by8 Month Result</td><td>(39)6by8 Month Serial No and Quantification</td><td>(40)Regimen</td><td>(41)Date of treatment started</td><td>(42)Gen expert</td><td>(43)Lipa Hain Rifampicin</td><td>(44)Lipa Hain Isoniazid</td><td>(45)HIV Test Date</td><td>(46)HIV Status</td><td>(47)Partner HIV Test Date</td><td>(48)Partner HIV Status</td><td>(49)Referred BY: VCT/HCC/STI/HBC/PS/ANC/SR/CI</td><td>(50)Referred TO VCT/HCC/STI/HBC/PS/ANC</td><td>(51)Cotrimoxazole Preventive Therapy Y/N</td><td>(52)Cotrimoxazole Preventive Therapy (Date Started)</td><td>(53)ART Y/N</td><td>(54)ART (Date Started)</td><td>(55)Nutrition Support</td><td>(56)Treatment Outcome</td><td>(57)Treatment Outcome Date</td><td>(58)Remarks</td></tr> //______________________________________________________________________ //([0])Serial Number //([1])Date of Registration //([2])District Registration Number //([4])County //([5])District //([7])Health Facility //([8])Year //([9])Quarter //([12])Sex M/F //([13])Age on registration //([40])Date of treatment started //([44])HIV Test Date //([45])HIV Status //([52])ART Y/N //([53])ART (Date Started) //([55])Treatment Outcome //([56])Treatment Outcome Date //([20])Type of TB P/EP //([23])Type of patient //([31])Sputum Smear Examination 0th Month Result //([33])Sputum Smear Examination 2by3 Month Result //([35])Sputum Smear Examination 5th Month Result //([37])Sputum Smear Examination 6by8 Month Result //([41])Gen expert //tbtype //patienttype //smear0 //smear2_3 //smear5 //smear6_8 //genexpert //______________________________________________________________________ HSSFCell cellserialno = rowi.getCell((short) 0); if (cellserialno.getCellType() == 0) { //numeric serialnumber = "" + (int) cellserialno.getNumericCellValue(); } else if (cellserialno.getCellType() == 1) { serialnumber = cellserialno.getStringCellValue(); } { } HSSFCell cellregdate = rowi.getCell((short) 1); // System.out.println("CELLTYPE IS "+cellregdate.getCellType()); if (cellregdate.getCellType() == 1) { //this is a string registrationdate = (String) cellregdate.getStringCellValue(); } else if (cellregdate.getCellType() == 0) { //this is a numeric value registrationdate = "" + (int) cellregdate.getNumericCellValue(); } else { registrationdate = "" + cellregdate.getDateCellValue(); } // System.out.println("Reg Date "+registrationdate); //District Reg number HSSFCell celldistreg = rowi.getCell((short) 2); if (celldistreg.getCellType() == 0) { //numeric distregno = "" + (int) celldistreg.getNumericCellValue(); } else if (celldistreg.getCellType() == 1) { distregno = celldistreg.getStringCellValue(); } { } //dont save county and subcounty directly since they may change HSSFCell cellcounty = rowi.getCell((short) 4); county_name = cellcounty.getStringCellValue(); HSSFCell cellsubcounty = rowi.getCell((short) 5); district_name = cellsubcounty.getStringCellValue(); HSSFCell cellfacil = rowi.getCell((short) 7); facilityName = cellfacil.getStringCellValue(); //HSSFCell cellmfl = rowi.getCell((short) 7); //mflcode = (int) cellmfl.getNumericCellValue(); HSSFCell cellsex = rowi.getCell((short) 12); if (cellsex.getCellType() == 1) { //string sex = (String) cellsex.getStringCellValue(); } else { //numeric sex = "" + (int) cellsex.getNumericCellValue(); } HSSFCell agestringcell = rowi.getCell((short) 13); if (agestringcell.getCellType() == 1) { //string agestring = (String) agestringcell.getStringCellValue(); } else { //numeric agestring = "" + (int) agestringcell.getNumericCellValue(); } if (agestring.contains("M") && agestring.contains("Y")) { //age is in months String agearray[] = agestring.split(" "); //System.out.println("agestring "+agestring+" _ "+age); age = ((new Double(agearray[0].trim().replace("Y", ""))).intValue()) + (int) Math.round((new Double(agearray[1].replace("M", "")) / 12)); System.out.println(" agestring M&Y " + agestring + " _ " + age); } else if (agestring.contains("Y") && !agestring.contains("M")) { //age is in years age = (new Double(agestring.trim().replace("Y", ""))).intValue(); System.out.println(" agestring Y Only " + agestring + " _ " + age); } else if (agestring.contains("M") && !agestring.contains("Y")) { //age is in months //System.out.println("agestring "+agestring+" _ "+age); age = (int) Math.round((new Double(agestring.trim().replace("M", "")) / 12)); // System.out.println("agestring "+agestring+" _ "+age); System.out.println(" agestring M Only " + agestring + " _ " + age); } ageinteger = age; agebracket = getageBracket(ageinteger); //tbtype HSSFCell celltbtype = rowi.getCell((short) 20); if (celltbtype.getCellType() == 1) { //this is a string tbtype = (String) celltbtype.getStringCellValue(); } else if (celltbtype.getCellType() == 0) { //this is a numeric value tbtype = "" + (int) celltbtype.getNumericCellValue(); } else { tbtype = "" + celltbtype.getDateCellValue(); } //type of patient HSSFCell cellpatienttype = rowi.getCell((short) 23); if (cellpatienttype.getCellType() == 1) { //this is a string patienttype = (String) cellpatienttype.getStringCellValue(); } else if (cellpatienttype.getCellType() == 0) { //this is a numeric value patienttype = "" + (int) cellpatienttype.getNumericCellValue(); } else { patienttype = "" + cellpatienttype.getDateCellValue(); } //------------ //smear0 HSSFCell cellsmear0 = rowi.getCell((short) 31); if (cellsmear0.getCellType() == 1) { //this is a string smear0 = (String) cellsmear0.getStringCellValue(); } else if (cellsmear0.getCellType() == 0) { //this is a numeric value smear0 = "" + (int) cellsmear0.getNumericCellValue(); } else { smear0 = "" + cellsmear0.getDateCellValue(); } //------------ HSSFCell cellsmear2_3 = rowi.getCell((short) 33); if (cellsmear2_3.getCellType() == 1) { //this is a string smear2_3 = (String) cellsmear0.getStringCellValue(); } else if (cellsmear2_3.getCellType() == 0) { //this is a numeric value smear2_3 = "" + (int) cellsmear2_3.getNumericCellValue(); } else { smear2_3 = "" + cellsmear0.getDateCellValue(); } //------------smear5------ HSSFCell cellsmear5 = rowi.getCell((short) 35); if (cellsmear5.getCellType() == 1) { //this is a string smear5 = (String) cellsmear0.getStringCellValue(); } else if (cellsmear5.getCellType() == 0) { //this is a numeric value smear5 = "" + (int) cellsmear5.getNumericCellValue(); } else { smear5 = "" + cellsmear5.getDateCellValue(); } //------------ //------------smear5------ HSSFCell cellsmear6_8 = rowi.getCell((short) 37); if (cellsmear6_8.getCellType() == 1) { //this is a string smear6_8 = (String) cellsmear0.getStringCellValue(); } else if (cellsmear6_8.getCellType() == 0) { //this is a numeric value smear6_8 = "" + (int) cellsmear6_8.getNumericCellValue(); } else { smear6_8 = "" + cellsmear6_8.getDateCellValue(); } //------------------- HSSFCell celltreatmentdate = rowi.getCell((short) 40); if (cellregdate.getCellType() == 1) { //this is a string treatmentstartdate = (String) celltreatmentdate.getStringCellValue(); } else if (cellregdate.getCellType() == 0) { //this is a numeric value treatmentstartdate = "" + (int) celltreatmentdate.getNumericCellValue(); } else { treatmentstartdate = "" + celltreatmentdate.getDateCellValue(); } //------------smear5------ HSSFCell cellgenexpert = rowi.getCell((short) 41); if (cellgenexpert.getCellType() == 1) { //this is a string genexpert = (String) cellgenexpert.getStringCellValue(); } else if (cellgenexpert.getCellType() == 0) { //this is a numeric value genexpert = "" + (int) cellgenexpert.getNumericCellValue(); } else { genexpert = "" + cellgenexpert.getDateCellValue(); } //------------------- //treatmentstartdate=""+celltreatmentdate.getDateCellValue(); //Format formatter = new SimpleDateFormat("MM/dd/yyyy"); //registrationdatecopy= new SimpleDateFormat("MM/dd/yyyy").format(registrationdate); // registrationdatecopy = formatter.format(registrationdatecopy); //hiv test date HSSFCell cellhivtestdate = rowi.getCell((short) 44); hivtestdate = "" + cellhivtestdate.getStringCellValue(); //hiv status HSSFCell cellhivstatus = rowi.getCell((short) 45); hivstatus = (String) cellhivstatus.getStringCellValue(); //art status HSSFCell cellartstatus = rowi.getCell((short) 52); artstatus = (String) cellartstatus.getStringCellValue(); //art date HSSFCell cellartdate = rowi.getCell((short) 53); artdate = "" + cellartdate.getStringCellValue(); //treatment outcome HSSFCell celltreatmentoutcome = rowi.getCell((short) 55); treatmentoutcome = (String) celltreatmentoutcome.getStringCellValue(); //treatment outcome date HSSFCell celltreatmentoutcomedate = rowi.getCell((short) 56); treatmentoutcomedate = "" + celltreatmentoutcomedate.getStringCellValue(); //split the date, year and month //raw date is of form dd mmm yyyy eg 08 Jul 2015 String dateparameters[] = registrationdate.split(" "); if (dateparameters.length == 3) { if (!dateparameters[0].equals("")) { String month = ""; month = dateparameters[1]; if (month.equalsIgnoreCase("Jan") || month.equalsIgnoreCase("Feb") || month.equalsIgnoreCase("Mar")) { quarterName = "January-March"; if (dateparameters[2].length() == 4) { year = Integer.parseInt(dateparameters[2]); } } else if (month.equalsIgnoreCase("Apr") || month.equalsIgnoreCase("May") || month.equalsIgnoreCase("Jun")) { quarterName = "April-June"; if (dateparameters[2].length() == 4) { year = Integer.parseInt(dateparameters[2]); } } else if (month.equalsIgnoreCase("Jul") || month.equalsIgnoreCase("Aug") || month.equalsIgnoreCase("Sep")) { quarterName = "July-September"; if (dateparameters[2].length() == 4) { year = Integer.parseInt(dateparameters[2]); } } else if (month.equalsIgnoreCase("Oct") || month.equalsIgnoreCase("Nov") || month.equalsIgnoreCase("Dec")) { quarterName = "October-December"; if (dateparameters[2].length() == 4) { //assume year = Integer.parseInt(dateparameters[2]) + 1; } } } } else { System.out.println("Error in date of testing _ :" + registrationdate); } // System.out.println("Quarter "+quarterName + "Year "+year); facilityID = ""; checker = 0; supporttype = "DSD"; String get_id = "SELECT SubPartnerID,ART_Support,CentreSanteId as mflcode,HTC_Support1,PMTCT_Support FROM subpartnera WHERE SubPartnerNom like ? or tibu_name like ?"; conn.pst = conn.conn.prepareStatement(get_id); conn.pst.setString(1, "%" + facilityName + "%"); conn.pst.setString(2, "%" + facilityName + "%"); conn.rs = conn.pst.executeQuery(); if (conn.rs.next() == true) { facilityID = conn.rs.getString(1); //supporttype=conn.rs.getString("ART_Support"); mflcode = conn.rs.getInt(3); //if(supporttype==null){supporttype=conn.rs.getString("HTC_Support1");} //if(supporttype==null){supporttype=conn.rs.getString("PMTCT_Support");} //if(supporttype==null){supporttype="";} } if (facilityID.length() > 0) { // DISTRICT FOUND ADD THE HF TO THE SYSTEM......................... String getQuarterID = "SELECT id FROM quarter WHERE pmtct_fo_name like ?"; conn.pst = conn.conn.prepareStatement(getQuarterID); conn.pst.setString(1, quarterName); conn.rs = conn.pst.executeQuery(); if (conn.rs.next() == true) { quarter = conn.rs.getInt(1); } checker = 0; // CHECK IF ALREADY ADDED TO PMTCT_FO TABLE id = serialnumber + "_" + registrationdate + "_" + distregno; // System.out.println("to add data : "+facilityName+" id : "+facilityID+"mfl code "+mflcode+" year : "+year+" quarter : "+quarter+" numerator : "+Numerator+" denominator : "+Denominator); String checkerExisting = "SELECT id FROM " + dbname + " WHERE id='" + id + "'"; conn.rs = conn.st.executeQuery(checkerExisting); if (conn.rs.next() == true) { checker++; } //id //SubPartnerID //Quarter //Year //Sex //Mflcode //age //agebracket //SubPartnerNom //dateoftesting //patientccc //batchno //suppression_status if (checker == 0) { String inserter = "INSERT INTO " + dbname + " (id,SubPartnerID,year,quarter,Mflcode,sex ,age,agebracket,SubPartnerNom,registrationdate,treatmentdate,supporttype,hivstatus,hivtestdate, " + " artstatus,artdate,outcomedate,treatmentoutcome,tbtype,patienttype,smear0,smear2_3,smear5,smear6_8,genexpert) " + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; conn.pst = conn.conn.prepareStatement(inserter); conn.pst.setString(1, id); conn.pst.setString(2, facilityID); conn.pst.setInt(3, year); conn.pst.setInt(4, quarter); conn.pst.setInt(5, mflcode); conn.pst.setString(6, sex); conn.pst.setInt(7, age); conn.pst.setString(8, agebracket); conn.pst.setString(9, facilityName); conn.pst.setString(10, registrationdate); conn.pst.setString(11, treatmentstartdate); conn.pst.setString(12, supporttype); conn.pst.setString(13, hivstatus); conn.pst.setString(14, hivtestdate); conn.pst.setString(15, artstatus); conn.pst.setString(16, artdate); conn.pst.setString(17, treatmentoutcomedate); conn.pst.setString(18, treatmentoutcome); conn.pst.setString(19, tbtype); conn.pst.setString(20, patienttype); conn.pst.setString(21, smear0); conn.pst.setString(22, smear2_3); conn.pst.setString(23, smear5); conn.pst.setString(24, smear6_8); conn.pst.setString(25, genexpert); conn.pst.executeUpdate(); added++; } else { //id,SubPartnerID,Year,Quarter,Mflcode,Sex ,age,agebracket,SubPartnerNom,dateoftesting,patientccc,batchno,supporttype String inserter = " UPDATE " + dbname + " SET SubPartnerID=?,year=?,quarter=?,Mflcode=?,sex=? ,age=?,agebracket=?,SubPartnerNom=?,registrationdate=?,treatmentdate=?,supporttype=?," + " hivstatus=?,hivtestdate=?, " + " artstatus=?,artdate=?,outcomedate=?,treatmentoutcome=? ,tbtype=?,patienttype=?,smear0=?,smear2_3=?,smear5=?,smear6_8=?,genexpert=?" + " WHERE id=?"; // conn.pst = conn.conn.prepareStatement(inserter); conn.pst.setString(1, facilityID); conn.pst.setInt(2, year); conn.pst.setInt(3, quarter); conn.pst.setInt(4, mflcode); conn.pst.setString(5, sex); conn.pst.setInt(6, age); conn.pst.setString(7, agebracket); conn.pst.setString(8, facilityName); conn.pst.setString(9, registrationdate); conn.pst.setString(10, treatmentoutcomedate); conn.pst.setString(11, supporttype); conn.pst.setString(12, hivstatus); conn.pst.setString(13, hivtestdate); conn.pst.setString(14, artstatus); conn.pst.setString(15, artdate); conn.pst.setString(16, treatmentoutcomedate); conn.pst.setString(17, treatmentoutcome); conn.pst.setString(18, tbtype); conn.pst.setString(19, patienttype); conn.pst.setString(20, smear0); conn.pst.setString(21, smear2_3); conn.pst.setString(22, smear5); conn.pst.setString(23, smear6_8); conn.pst.setString(24, genexpert); conn.pst.setString(25, id); conn.pst.executeUpdate(); updated++; } } else { missing++; // missing facilities missingFacility += "facility name : " + facilityName + " mfl code : " + mflcode + " excel row num : " + i + "<br>"; System.out.println(facilityName + "_missing"); } i++; } } if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.pst != null) { conn.pst.close(); } } catch (SQLException ex) { Logger.getLogger(Load_tb_raw.class.getName()).log(Level.SEVERE, null, ex); } String sessionText = "<br/><b> " + added + "</b> New data added <br/> <b> " + updated + "</b> updated facilities<br> <br> <b>" + missing + "</b> sites not in Imis Facilities List "; session.setAttribute("upload_success", sessionText); response.sendRedirect(nextpage); }
From source file:net.ceos.project.poi.annotated.core.TestUtils.java
License:Apache License
public static void validationDate(Date date, XlsElement xlsAnnotation, HSSFCell headerCell, HSSFCell contentCell) { assertEquals(xlsAnnotation.title(), headerCell.getStringCellValue()); assertEquals(date, contentCell.getDateCellValue()); }
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 ww w . j a va2 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;// w w w . jav a2 s . c o 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); } } } } }
From source file:no.abmu.common.excel.BaseExcelParserImpl.java
License:Open Source License
/** * Return the value of the cell as a long. * * @return/*from ww w.j a va 2 s .com*/ */ protected Date getDateValue(HSSFRow row, Short columnIndex, DateFormat customDateFormat) { Assert.checkRequiredArgument("row", row); Assert.checkRequiredArgument("columnIndex", columnIndex); HSSFCell cell = row.getCell(columnIndex.shortValue()); if (cell == null) { return null; } switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isValidExcelDate(cell.getNumericCellValue())) { return cell.getDateCellValue(); } else { return null; } case HSSFCell.CELL_TYPE_STRING: String value = cell.getStringCellValue(); if (StringUtil.isEmpty(value)) { return null; } // ISO data format are default date format DateFormat dateFormat = DateUtil.ISO_DATEFORMAT; if (customDateFormat != null) { dateFormat = customDateFormat; } Date returnValue = null; try { returnValue = DateUtil.parseDateString(dateFormat, value); } catch (IllegalArgumentException e) { if (logger.isDebugEnabled()) { logger.debug("[getDateValue] IllegalArgumentException: " + e); } } return returnValue; default: return null; } }
From source file:org.beangle.commons.transfer.excel.ExcelItemReader.java
License:Open Source License
/** * <p>/* w ww . j a v a 2 s.c om*/ * getCellValue. * </p> * * @see ?cell?? * @param cell a {@link org.apache.poi.hssf.usermodel.HSSFCell} object. * @return a {@link java.lang.Object} object. */ public static Object getCellValue(HSSFCell cell) { if ((cell == null)) return null; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: return null; case HSSFCell.CELL_TYPE_STRING: return Strings.trim(cell.getRichStringCellValue().getString()); case HSSFCell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { return cell.getDateCellValue(); } else { return numberFormat.format(cell.getNumericCellValue()); } case HSSFCell.CELL_TYPE_BOOLEAN: return (cell.getBooleanCellValue()) ? Boolean.TRUE : Boolean.FALSE; default: // cannot handle HSSFCell.CELL_TYPE_ERROR,HSSFCell.CELL_TYPE_FORMULA return null; } }
From source file:org.beangle.model.transfer.excel.ExcelItemReader.java
License:Open Source License
/** * @see ?cell??/* www. ja va 2 s.c o m*/ * @param cell * @param objClass * @return */ public static Object getCellValue(HSSFCell cell) { if ((cell == null)) return null; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: return null; case HSSFCell.CELL_TYPE_STRING: return StringUtils.trim(cell.getRichStringCellValue().getString()); case HSSFCell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { return cell.getDateCellValue(); } else { return numberFormat.format(cell.getNumericCellValue()); } case HSSFCell.CELL_TYPE_BOOLEAN: return (cell.getBooleanCellValue()) ? Boolean.TRUE : Boolean.FALSE; default: // cannot handle HSSFCell.CELL_TYPE_ERROR,HSSFCell.CELL_TYPE_FORMULA return null; } }