List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getSheet
@Override
public HSSFSheet getSheet(String name)
From source file:it.redev.parco.job.AnagraficaParserJob.java
License:Open Source License
@Override public void execute() throws DatabaseException, RemoteSafeException { try {/*from w ww . ja v a2 s .com*/ service = new ImportService(getEntityManager(), getUser()); addInfoMessage("Caricamento file " + getFilePath()); InputStream file = super.openStream(); HSSFWorkbook workbook = new HSSFWorkbook(file); // POSTAZIONI Integer parsed = 0; int rows, tot; Sheet sheet = workbook.getSheet(POSTAZIONI_SHEET); if (sheet != null) { rows = sheet.getLastRowNum(); addInfoMessage("Trovate " + rows + " righe nel file postazioni"); for (int i = 1; i <= rows; i++) { Row row = sheet.getRow(i); if (row != null && parsePostazioni(row)) ; parsed++; } tot = saveOc(); if (tot > 0) addInfoMessage("Create " + tot + " nuove postazioni e/o aree e/o province"); if ((parsed - tot) > 0) addInfoMessage("Aggiornate " + (parsed - tot) + " postazioni e/o aree e/o province"); commit(); } // PERSONE parsed = 0; sheet = workbook.getSheet(PERSONE_SHEET); if (sheet != null) { rows = sheet.getLastRowNum(); addInfoMessage("Trovate " + rows + " persone nel file"); for (int i = 1; i <= rows; i++) { Row row = sheet.getRow(i); if (row != null && parsePersona(row)) ; parsed++; } tot = savePersone(); if (tot > 0) addInfoMessage("Create " + tot + " nuove persone"); if ((parsed - tot) > 0) addInfoMessage("Aggiornate " + (parsed - tot) + " persone"); commit(); } // TIPO MEZZO parsed = 0; sheet = workbook.getSheet(TIPO_MEZZI_SHEET); if (sheet != null) { rows = sheet.getLastRowNum(); addInfoMessage("Trovati " + rows + " tipi mezzo nel file"); for (int i = 1; i <= rows; i++) { Row row = sheet.getRow(i); if (row != null && parseTipoMezzo(row)) ; parsed++; } tot = saveTipoMezzo(); if (tot > 0) addInfoMessage("Creati " + tot + " nuovi tipi mezzo"); if ((parsed - tot) > 0) addInfoMessage("Aggiornati " + (parsed - tot) + " tipi mezzo"); commit(); } // MEZZO parsed = 0; sheet = workbook.getSheet(MEZZI_SHEET); if (sheet != null) { rows = sheet.getLastRowNum(); addInfoMessage("Trovati " + rows + " mezzi"); for (int i = 1; i <= rows; i++) { Row row = sheet.getRow(i); if (row != null && parseMezzo(row)) parsed++; } tot = saveMezzi(); if (tot > 0) addInfoMessage("Creati " + tot + " nuovi mezzi"); if ((parsed - tot) > 0) addInfoMessage("Aggiornati " + (parsed - tot) + " mezzi"); commit(); } // CARTE parsed = 0; sheet = workbook.getSheet(CARTE_SHEET); if (sheet != null) { rows = sheet.getLastRowNum(); addInfoMessage("Trovate " + rows + " carte"); for (int i = 1; i <= rows; i++) { Row row = sheet.getRow(i); if (row != null && parseCarta(row)) parsed++; } tot = saveCarte(false); if (tot > 0) addInfoMessage("Create " + tot + " nuove carte"); if ((parsed - tot) > 0) addInfoMessage("Aggiornate " + (parsed - tot) + " carte"); commit(); } // POLIZZE parsed = 0; sheet = workbook.getSheet(POLIZZE_SHEET); if (sheet != null) { rows = sheet.getLastRowNum(); addInfoMessage("Trovate " + rows + " polizze"); for (int i = 1; i <= rows; i++) { Row row = sheet.getRow(i); if (row != null && parsePolizza(row)) parsed++; } saveCompagnie(); tot = savePolizze(); if (tot > 0) addInfoMessage("Create " + tot + " nuove polizze"); if ((parsed - tot) > 0) addInfoMessage("Aggiornate " + (parsed - tot) + " polizze"); commit(); } // DOTAZIONI parsed = 0; sheet = workbook.getSheet(DOTAZIONI_SHEET); if (sheet != null) { rows = sheet.getLastRowNum(); addInfoMessage("Trovate " + rows + " dotazioni"); for (int i = 1; i <= rows; i++) { Row row = sheet.getRow(i); if (row != null && parseDotazione(row)) parsed++; } tot = saveDotazioni(); if (tot > 0) addInfoMessage("Create " + tot + " nuove dotazioni"); if ((parsed - tot) > 0) addInfoMessage("Aggiornate " + (parsed - tot) + " dotazioni"); commit(); } // PIN CARD parsed = 0; sheet = workbook.getSheet(PIN_SHEET); if (sheet != null) { rows = sheet.getLastRowNum(); addInfoMessage("Trovati " + rows + " pin card"); for (int i = 1; i <= rows; i++) { Row row = sheet.getRow(i); if (row != null && parsePincard(row)) parsed++; } tot = savePincard(true); if (tot > 0) addInfoMessage("Create " + tot + " nuove pin card"); if ((parsed - tot) > 0) addInfoMessage("Aggiornate " + (parsed - tot) + " pin card"); commit(); } file.close(); } catch (FileNotFoundException e) { throw new DatabaseException(e); } catch (IOException e) { throw new DatabaseException(e); } catch (Exception e) { throw new DatabaseException(e); } finally { super.removeFileQuietly(); } }
From source file:jp.dbcls.bp3d.kaorif.visiblebody.nerves.VisibleBodyNerves.java
License:Open Source License
/** * ???(manuallyMapped.txt)?//from www . j a va2s . com * @throws Exception */ public void readManuallyMapped() throws Exception { POIFSFileSystem filein = new POIFSFileSystem(new FileInputStream(this.MANUALLY_MAPPED)); HSSFWorkbook wb = new HSSFWorkbook(filein); HSSFSheet sheet = wb.getSheet("manuallyMapped"); for (int i = 1; i < sheet.getLastRowNum() + 1; i++) { HSSFRow row = sheet.getRow(i); HSSFCell cell = null; String original = ""; cell = row.getCell(0); if (cell != null) { original = cell.getRichStringCellValue().toString().trim(); } String av = ""; cell = row.getCell(1); if (cell != null) { av = cell.getRichStringCellValue().toString().trim(); } String renamed = ""; cell = row.getCell(2); if (cell != null) { renamed = cell.getRichStringCellValue().toString().trim(); } String remark = ""; for (int j = 3; j < row.getLastCellNum(); j++) { cell = row.getCell(j); if (cell != null) { remark += cell.getRichStringCellValue().toString().trim() + "\t"; } } VisibleBodyManuallyMapped mm = new VisibleBodyManuallyMapped(); mm.setOriginal(original); mm.setAv(av); mm.setRenamed(renamed); mm.setRemark(remark); this.manuallyMapped.add(mm); } }
From source file:jp.dbcls.bp3d.kaorif.visiblebody.vessels.VisibleBodyVessels.java
License:Open Source License
/** * this.ORDERED_BY_KAORIF???/*from w w w. ja v a2s .c om*/ * * @throws Exception */ public void readOrderedByKaorif() throws Exception { POIFSFileSystem filein = new POIFSFileSystem(new FileInputStream(this.ORDERED_BY_KAORIF)); HSSFWorkbook wb = new HSSFWorkbook(filein); HSSFSheet sheet = wb.getSheet("CirculatorySystem"); for (int i = 1; i < sheet.getLastRowNum() + 1; i++) { HSSFRow row = sheet.getRow(i); boolean isRequired = row.getCell(0).getBooleanCellValue(); String en = ""; HSSFCell cell = row.getCell(1); if (cell != null) { en = cell.getRichStringCellValue().toString().trim(); } String av = ""; cell = row.getCell(2); if (cell != null) { av = cell.getRichStringCellValue().toString().trim(); } VisibleBodyEntry vbe = getEntry(en, av); if (vbe != null) { vbe.setOrderedByKaorif(isRequired); } else { System.out.println("readOrderedByKaorif not found=" + en + " " + av); } } }
From source file:jp.dbcls.bp3d.ta.bits.TABits.java
License:Open Source License
/** * o101_TAJwFMA.xls?/*from w ww. j a va 2 s. co m*/ * @throws Exception */ public void readXls() throws Exception { POIFSFileSystem filein = new POIFSFileSystem(new FileInputStream(INFILE)); HSSFWorkbook wb = new HSSFWorkbook(filein); HSSFSheet sheet = wb.getSheet(SHEET); for (int i = 1; i < sheet.getLastRowNum() + 1; i++) { HSSFRow row = sheet.getRow(i); int j = 0; String edit = ""; if (row.getCell(j) != null) { edit = row.getCell(j).getRichStringCellValue().toString().trim(); } j++; /** edit=DELETE????? **/ if (edit.equals(TABitsEntry.DELETE)) { continue; } String taId = row.getCell(j++).getRichStringCellValue().toString().trim(); double taTab = 0.0; int cellType = row.getCell(j).getCellType(); if (cellType == Cell.CELL_TYPE_NUMERIC) { taTab = row.getCell(j++).getNumericCellValue(); } else if (cellType == Cell.CELL_TYPE_STRING) { taTab = Double.parseDouble( row.getCell(j++).getRichStringCellValue().toString().trim().replaceAll(">", "")); } else { j++; System.out.println("[Error]@readXls.TA:Unknown CellType" + cellType); } String taKanji = row.getCell(j++).getRichStringCellValue().toString().trim(); String taEn = row.getCell(j++).getRichStringCellValue().toString().trim().replace("[*]", ""); String taKana = ""; if (japaneseDisambiguatedForm2kana.containsKey(taKanji)) { taKana = japaneseDisambiguatedForm2kana.get(taKanji); } else if (japaneseEquivalentForm2kana.containsKey(taKanji)) { taKana = japaneseEquivalentForm2kana.get(taKanji); } else if (en2kana.containsKey(taEn)) { taKana = en2kana.get(taEn); } List<String> fmaIds = Arrays.asList( row.getCell(j++).getRichStringCellValue().toString().replaceAll(":", "").trim().split("[|]")); String fmaOBOName = row.getCell(j++).getRichStringCellValue().toString().trim().replaceAll(":", ""); TABitsEntry template = createTAEntry(); template.setTaId(taId); template.setTaTab(taTab); template.setTaEn(taEn); template.setTaKanji(taKanji); template.setTaKana(taKana); if (fmaOBOName.contains("NONE")) { // FMAOBONAME="NONE"????TA?fmaobo2?? Set<FMAOBOEntry> hits = new HashSet<FMAOBOEntry>(); for (String en : Arrays.asList(taEn.split("[;]"))) { en = en.replaceAll("[*]", "").trim(); if (fmaobo.contains(en)) { //TA?fmaobo????? hits.add(fmaobo.getByName(en)); } } if (hits.size() == 0) { // FMA????? TABitsEntry ent = (TABitsEntry) template.clone(); ent.setClassification(TABitsEntry.NOFMA); entries.add(ent); } else { for (FMAOBOEntry fmaEnt : hits) { TABitsEntry ent = (TABitsEntry) template.clone(); ent.setFma(fmaEnt); if (fmaIds.contains(fmaEnt.getId())) { ent.setClassification(TABitsEntry.IDENTICAL); // FMA???????? } else { ent.setClassification(TABitsEntry.NOTIDENTICAL); // FMA??????? } entries.add(ent); } } } else { // FMAOBONAME="NONE"?????????? for (String fmaId : fmaIds) { TABitsEntry ent = (TABitsEntry) template.clone(); ent.setClassification(TABitsEntry.ORIGINAL); if (fmaobo.contains(fmaId)) { ent.setFma(fmaobo.getById(fmaId)); ent.setClassification(TABitsEntry.ORIGINAL); } else { ent.setClassification(TABitsEntry.NOFMAOBO2); System.out.println("[Warning]@TABits.readXLs:" + fmaId + ":" + ent.getTaEn() + " is not found in fmaobo2"); } if (!edit.isEmpty()) { ent.setEdit(edit); } entries.add(ent); } } } }
From source file:LoadExcels.loadPMTCT_FO.java
@Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {//from w ww.jav a 2 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 {/*www.jav a 2 s.c om*/ 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:net.chaosserver.timelord.data.ExcelDataReaderWriter.java
License:Open Source License
/** * This creates all of the sheets for the workbook. The sheets * have names to associated with the proper dates, but have no data * filled into them.//from w ww.j ava 2 s . c om * * This also builds out the sheetToNotes map by associating all the * notes data in the application with the sheet that the data will * eventually be written out to. * * @param workbook the workbook to create sheets on * @param timelordData the timelord data used for the sheets. * @param sheetToNotes the map of sheets to the notes associated with it * @param styleMap the map of styles */ protected void preCreateAllSheets(HSSFWorkbook workbook, TimelordData timelordData, Map<String, List<String>> sheetToNotes, Map<String, HSSFCellStyle> styleMap) { // This holds the most recent date in the entire data file. // This date starts out as null, and any time a task if found // with a more recent date it replaces this value. Date mostRecentDate = null; // This holds the oldest date in the entire data file. // This date starts out null and any time a task is found // with an older date it is replaced with this one. Date oldestDate = null; List<TimelordTask> taskCollection = timelordData.getTaskCollection(); Iterator<TimelordTask> taskIterator = taskCollection.iterator(); // Iterator through all the tasks in the data to create the // output sheet while (taskIterator.hasNext()) { TimelordTask timelordTask = (TimelordTask) taskIterator.next(); // Only exportable tasks should be considered. Tasks that aren't // exportable must be skipped if (timelordTask.isExportable()) { List<TimelordTaskDay> taskDayList = timelordTask.getTaskDayList(); if (!taskDayList.isEmpty()) { // Since the days associated with this task aren't empty, // pull out the first task. This is the most recent day // that has time tracked to it. TimelordTaskDay firstDay = (TimelordTaskDay) taskDayList.get(0); // If the first date in this task is more recent than the // most recent temporary date, replace it with this one. if (mostRecentDate == null || mostRecentDate.before(firstDay.getDate())) { if (logger.isTraceEnabled()) { logger.trace("Updating mostRecentDate from [" + mostRecentDate + "] to [" + firstDay.getDate() + "]"); } mostRecentDate = firstDay.getDate(); } // Just as a double check see if the earliest date is // the oldest and do the replacement. if (oldestDate == null || oldestDate.after(firstDay.getDate())) { if (logger.isTraceEnabled()) { logger.trace("Updating oldestDate from [" + oldestDate + "] to [" + firstDay.getDate() + "]"); } oldestDate = firstDay.getDate(); } // Grab the very last item in the list. This should // be the oldest date associated with the task. TimelordTaskDay lastDay = (TimelordTaskDay) taskDayList.get(taskDayList.size() - 1); if (mostRecentDate == null || mostRecentDate.before(lastDay.getDate())) { if (logger.isTraceEnabled()) { logger.trace("Updating mostRecentDate from [" + mostRecentDate + "] to [" + lastDay.getDate() + "]"); } mostRecentDate = lastDay.getDate(); } if (oldestDate == null || oldestDate.after(lastDay.getDate())) { if (logger.isTraceEnabled()) { logger.trace( "Updating oldestDate from [" + oldestDate + "] to [" + lastDay.getDate() + "]"); } oldestDate = lastDay.getDate(); } } } } if (logger.isDebugEnabled()) { logger.debug( "Found the oldestDate [" + oldestDate + "] to the mostRecentDate [" + mostRecentDate + "]"); } Calendar createSheetsCalendar = Calendar.getInstance(); createSheetsCalendar.setTime(mostRecentDate); // Start at the most recent date in the system and roll back // the dates creating the sheets until all the dates have been // covered. while (createSheetsCalendar.getTime().after(oldestDate)) { Date weekStartDate = convertToWeekStart(createSheetsCalendar.getTime()); String sheetName = sheetNameFormat.format(weekStartDate); HSSFSheet sheet = workbook.getSheet(sheetName); if (sheet == null) { if (logger.isDebugEnabled()) { logger.debug("Creating HSSFSheet named [" + sheetName + "]"); } sheet = workbook.createSheet(sheetName); sheetToNotes.put(sheetName, new ArrayList<String>()); createHeaderRows(sheet, weekStartDate, styleMap); } createSheetsCalendar.add(Calendar.DAY_OF_YEAR, -1); } }
From source file:net.chaosserver.timelord.data.ExcelDataReaderWriter.java
License:Open Source License
/** * Adds all of the tasks to sheets that have already been created inside * the workbook and adds any notes associated with the tasks to the * sheetToNotes map./*from w w w .j av a 2 s .c om*/ * * @param workbook the workbook to create sheets on * @param timelordData the timelord data used for the sheets. * @param sheetToNotes the map of sheets to the notes associated with it * @param styleMap the map of styles * * @return the total rows added to Excel after adding all the tasks */ protected int addAllTasks(HSSFWorkbook workbook, TimelordData timelordData, Map<String, List<String>> sheetToNotes, Map<String, HSSFCellStyle> styleMap) { // Start on row number two. The first row contains the header data. int rowNum = 2; List<TimelordTask> taskCollection = timelordData.getTaskCollection(); Iterator<TimelordTask> taskIterator = taskCollection.iterator(); while (taskIterator.hasNext()) { TimelordTask timelordTask = (TimelordTask) taskIterator.next(); if (timelordTask.isExportable()) { if (logger.isDebugEnabled()) { logger.debug("Processing exportable task named [" + timelordTask.getTaskName() + "]"); } String taskName = timelordTask.getTaskName(); List<TimelordTaskDay> taskDayList = timelordTask.getTaskDayList(); Iterator<TimelordTaskDay> taskDayIterator = taskDayList.iterator(); while (taskDayIterator.hasNext()) { TimelordTaskDay timelordTaskDay = (TimelordTaskDay) taskDayIterator.next(); Date timelordDate = timelordTaskDay.getDate(); double hours = timelordTaskDay.getHours(); if (logger.isDebugEnabled()) { logger.debug("Processing task named [" + timelordTask.getTaskName() + "] for date [" + timelordDate + "] with hours [" + hours + "]"); } if (hours > 0) { Date weekStartDate = convertToWeekStart(timelordDate); String sheetName = sheetNameFormat.format(weekStartDate); HSSFSheet sheet = workbook.getSheet(sheetName); if (sheet == null) { throw new NullPointerException( "Failed to find " + "sheet with name [" + sheetName + "]"); } List<String> noteList = sheetToNotes.get(sheetName); HSSFRow row = sheet.getRow(rowNum); if (row == null) { row = sheet.createRow(rowNum); // First create the left column "header" with the // name of the task on column 0. HSSFCell cell = row.createCell((short) 0); cell.setCellStyle((HSSFCellStyle) styleMap.get("taskNameStyle")); cell.setCellValue(taskName); // Over in the far right column create the sum // column cell = row.createCell(MAX_COLUMN); cell.setCellStyle((HSSFCellStyle) styleMap.get("totalColumnStyle")); cell.setCellFormula("SUM(B" + (rowNum + 1) + ":H" + (rowNum + 1) + ")"); } // Process the task day and add the hours into the // given row. addTaskDay(row, taskName, timelordTaskDay, noteList, styleMap); } } rowNum++; } else { if (logger.isDebugEnabled()) { logger.debug("Skipping non-exportable task named [" + timelordTask.getTaskName() + "]"); } } } return rowNum; }
From source file:ngr.KiKi.appreciator.data.XLSHelper.java
public boolean loadSecondaryAppreciations(File file, ArrayList<Student> list) { HSSFWorkbook sbook = null; Sheet ssheet;/*from w w w.j a va 2 s.c o m*/ if (file != null && file.isFile()) try (FileInputStream input = new FileInputStream(file)) { sbook = new HSSFWorkbook(input); } catch (IOException e) { System.out.println(e.getMessage()); return false; } if (sbook == null) return false; ssheet = sbook.getSheet(Indices.SHEET); if (ssheet == null) return false; Iterator<Student> it = list.iterator(); while (it.hasNext()) { Student student = it.next(); String appreciation = ""; for (int i = 0; i < 150 && appreciation.isEmpty(); i++) if (rowExists(ssheet, i + Indices.FIRST_ROW)) { Row r = ssheet.getRow(i + Indices.FIRST_ROW); if (r.getCell(Indices.STUDENT_COLUMN).getStringCellValue().equals(student.getfullname())) appreciation = r.getCell(Indices.APPRECIATION_COLUMN).getStringCellValue(); } if (!appreciation.isEmpty()) student.setPreviousAppreciation(appreciation); } return true; }
From source file:ngr.KiKi.appreciator.data.XLSHelper.java
public ArrayList<Appreciation> loadAppreciations(String path) { ArrayList<Appreciation> list = new ArrayList<>(); HSSFWorkbook abook = null; Sheet asheet;//from w w w . j a va 2s .c o m File aFile = new File(path); if (aFile.isFile()) try (FileInputStream input = new FileInputStream(aFile)) { abook = new HSSFWorkbook(input); } catch (IOException e) { System.out.println(e.getMessage()); return list; } if (abook == null) return list; asheet = abook.getSheet(Indices.APPRECIATIONS_FILE_SHEET); if (asheet == null) return list; for (int i = 0; i < Utils.MAX_APPRECIATIONS; i++) if (rowExists(asheet, i)) { Row r = asheet.getRow(i); Cell c = r.getCell(Indices.APPRECIATIONS_FILE_COLUMN); if (c != null && c.getCellType() == Cell.CELL_TYPE_STRING) { String s = c.getStringCellValue(); if (s != null && s.startsWith(Utils.LEADING_STUFF)) { Appreciation a = new Appreciation(Utils.removeLead(s)); Double T1inf = r.getCell(Indices.APPRECIATIONS_FILE_T[0]) != null && r .getCell(Indices.APPRECIATIONS_FILE_T[0]).getCellType() == Cell.CELL_TYPE_NUMERIC ? r.getCell(Indices.APPRECIATIONS_FILE_T[0]).getNumericCellValue() : 0.; Double T1sup = r.getCell(Indices.APPRECIATIONS_FILE_T[0] + 1) != null && r.getCell(Indices.APPRECIATIONS_FILE_T[0] + 1) .getCellType() == Cell.CELL_TYPE_NUMERIC ? r.getCell(Indices.APPRECIATIONS_FILE_T[0] + 1) .getNumericCellValue() : 20.; Double T2inf = r.getCell(Indices.APPRECIATIONS_FILE_T[1]) != null && r .getCell(Indices.APPRECIATIONS_FILE_T[1]).getCellType() == Cell.CELL_TYPE_NUMERIC ? r.getCell(Indices.APPRECIATIONS_FILE_T[1]).getNumericCellValue() : 0.; Double T2sup = r.getCell(Indices.APPRECIATIONS_FILE_T[1] + 1) != null && r.getCell(Indices.APPRECIATIONS_FILE_T[1] + 1) .getCellType() == Cell.CELL_TYPE_NUMERIC ? r.getCell(Indices.APPRECIATIONS_FILE_T[1] + 1) .getNumericCellValue() : 20.; Double T3inf = r.getCell(Indices.APPRECIATIONS_FILE_T[2]) != null && r .getCell(Indices.APPRECIATIONS_FILE_T[2]).getCellType() == Cell.CELL_TYPE_NUMERIC ? r.getCell(Indices.APPRECIATIONS_FILE_T[2]).getNumericCellValue() : 0.; Double T3sup = r.getCell(Indices.APPRECIATIONS_FILE_T[2] + 2) != null && r.getCell(Indices.APPRECIATIONS_FILE_T[2] + 1) .getCellType() == Cell.CELL_TYPE_NUMERIC ? r.getCell(Indices.APPRECIATIONS_FILE_T[2] + 1) .getNumericCellValue() : 20.; Double Ainf = r.getCell(Indices.APPRECIATIONS_FILE_A) != null && r.getCell(Indices.APPRECIATIONS_FILE_A).getCellType() == Cell.CELL_TYPE_NUMERIC ? r.getCell(Indices.APPRECIATIONS_FILE_A).getNumericCellValue() : 0.; Double Asup = r.getCell(Indices.APPRECIATIONS_FILE_A + 1) != null && r .getCell(Indices.APPRECIATIONS_FILE_A + 1).getCellType() == Cell.CELL_TYPE_NUMERIC ? r.getCell(Indices.APPRECIATIONS_FILE_A + 1).getNumericCellValue() : 20.; Double Cinf = r.getCell(Indices.APPRECIATIONS_FILE_C) != null && r.getCell(Indices.APPRECIATIONS_FILE_C).getCellType() == Cell.CELL_TYPE_NUMERIC ? r.getCell(Indices.APPRECIATIONS_FILE_C).getNumericCellValue() : 0.; Double Csup = r.getCell(Indices.APPRECIATIONS_FILE_C + 1) != null && r .getCell(Indices.APPRECIATIONS_FILE_C + 1).getCellType() == Cell.CELL_TYPE_NUMERIC ? r.getCell(Indices.APPRECIATIONS_FILE_C + 1).getNumericCellValue() : 20.; Double Iinf = r.getCell(Indices.APPRECIATIONS_FILE_I) != null && r.getCell(Indices.APPRECIATIONS_FILE_I).getCellType() == Cell.CELL_TYPE_NUMERIC ? r.getCell(Indices.APPRECIATIONS_FILE_I).getNumericCellValue() : 0.; Double Isup = r.getCell(Indices.APPRECIATIONS_FILE_I + 1) != null && r .getCell(Indices.APPRECIATIONS_FILE_I + 1).getCellType() == Cell.CELL_TYPE_NUMERIC ? r.getCell(Indices.APPRECIATIONS_FILE_I + 1).getNumericCellValue() : 20.; a.setConstraint(Indices.APPRECIATIONS_FILE_T[0], T1inf, T1sup); a.setConstraint(Indices.APPRECIATIONS_FILE_T[1], T2inf, T2sup); a.setConstraint(Indices.APPRECIATIONS_FILE_T[2], T3inf, T3sup); a.setConstraint(Indices.APPRECIATIONS_FILE_A, Ainf, Asup); a.setConstraint(Indices.APPRECIATIONS_FILE_C, Cinf, Csup); a.setConstraint(Indices.APPRECIATIONS_FILE_I, Iinf, Isup); list.add(a); } } } return list; }