List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getRow
@Override public HSSFRow getRow(int rowIndex)
From source file:learning.fisshplate.LearningPoiTest.java
License:Apache License
/** * ??// w w w. j a va 2s . co m * * @throws Exception */ public void testInithialize() throws Exception { String filePath = "src/test/resources/LearningPOITest.xls"; HSSFWorkbook input = setupInputWorkbook(filePath); HSSFSheet inputSheet = input.getSheetAt(0); for (int rowNo = 0; rowNo <= inputSheet.getLastRowNum(); rowNo++) { HSSFRow row = inputSheet.getRow(rowNo); if (row == null) { continue; } for (int columnNo = 0; columnNo <= row.getLastCellNum(); columnNo++) { HSSFCell cell = row.getCell(columnNo); if (cell == null) { continue; } HSSFRichTextString richText = new HSSFRichTextString(null); cell.setCellValue(richText); HSSFCellStyle style = input.createCellStyle(); style.setFillPattern(HSSFCellStyle.NO_FILL); cell.setCellStyle(style); } } FileOutputStream fos = new FileOutputStream("target/outLearningTest.xls"); input.write(fos); fos.close(); }
From source file:learning.fisshplate.LearningPoiTest.java
License:Apache License
public void testCreateRowTest() throws Exception { InputStream is = getClass().getResourceAsStream("/MapBuilderTest_template.xls"); HSSFWorkbook wb = new HSSFWorkbook(is); HSSFSheet ws = wb.getSheetAt(0); for (int i = 0; i <= ws.getLastRowNum(); i++) { HSSFRow hssfRow = ws.getRow(i); if (hssfRow != null) { ws.removeRow(hssfRow);//from w w w. j a v a 2 s .c o m } } FileOutputStream os = new FileOutputStream("target/createRowTest.xls"); wb.write(os); os.close(); is.close(); }
From source file:library.restore.java
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed String fileSeparator = System.getProperty("file.separator"); JFileChooser chooser = new JFileChooser(); //set JFileChooser default path chooser.setCurrentDirectory(new java.io.File(".")); //set JFileChooser title chooser.setDialogTitle("open"); //use as you need DIRECTORIES_ONLY, FILES_ONLY etc chooser.setFileSelectionMode(JFileChooser.FILES_ONLY); //false, if you want to view all kind of file type in JFileChooser //true, if you want to create FileFilter and open only file type you want to be view in JFileChooser chooser.setAcceptAllFileFilterUsed(false); if (chooser.showOpenDialog(this) == JFileChooser.APPROVE_OPTION) { //write selected path inside JTextField jtfFileLocation.setText(chooser.getSelectedFile().getPath() + fileSeparator); //students=getText(chooser.getSelectedFile().getPath()+fileSeparator); students = jtfFileLocation.getText(); System.out.println("" + students + ""); } else {//from w w w .j a v a 2 s .c o m System.out.println("No Selection"); } try { Class.forName("com.mysql.jdbc.Driver"); Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/library", "root", "123ERYcog."); con.setAutoCommit(false); PreparedStatement pstm = null; // this.fileurlp = this.student.replace("\\", "\\\\"); FileInputStream input = new FileInputStream("" + students + ""); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); Row row; for (int i = 1; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); String sid = row.getCell(0).getStringCellValue(); String fname = row.getCell(1).getStringCellValue(); String lastname = row.getCell(2).getStringCellValue(); String form = row.getCell(3).getStringCellValue(); String classs = row.getCell(4).getStringCellValue(); String imgurlK = row.getCell(5).getStringCellValue(); String imgurl = imgurlK.replace("\\", "\\\\"); String sql = "INSERT INTO students VALUES('" + sid + "','" + fname + "','" + lastname + "','" + form + "','" + classs + "','" + imgurl + "')"; pstm = (PreparedStatement) con.prepareStatement(sql); pstm.execute(); System.out.println("Import rows " + i); } con.commit(); pstm.close(); con.close(); input.close(); JOptionPane.showMessageDialog(null, "Success import excel to mysql table"); System.out.println("Success import excel to mysql table"); } catch (ClassNotFoundException e) { System.out.println(e); } catch (SQLException ex) { JOptionPane.showMessageDialog(null, "error import excel to mysql table"); System.out.println(ex); } catch (IOException ioe) { System.out.println(ioe); } }
From source file:library.restore.java
private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton2ActionPerformed String fileSeparator = System.getProperty("file.separator"); JFileChooser chooser = new JFileChooser(); //set JFileChooser default path chooser.setCurrentDirectory(new java.io.File(".")); //set JFileChooser title chooser.setDialogTitle("open"); //use as you need DIRECTORIES_ONLY, FILES_ONLY etc chooser.setFileSelectionMode(JFileChooser.FILES_ONLY); //false, if you want to view all kind of file type in JFileChooser //true, if you want to create FileFilter and open only file type you want to be view in JFileChooser chooser.setAcceptAllFileFilterUsed(false); if (chooser.showOpenDialog(this) == JFileChooser.APPROVE_OPTION) { //write selected path inside JTextField jtfFileLocation1.setText(chooser.getSelectedFile().getPath() + fileSeparator); //students=getText(chooser.getSelectedFile().getPath()+fileSeparator); books = jtfFileLocation1.getText(); System.out.println("" + books + ""); } else {//from ww w. j a va2 s. c o m System.out.println("No Selection"); } try { Class.forName("com.mysql.jdbc.Driver"); Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/library", "root", "123ERYcog."); con.setAutoCommit(false); PreparedStatement pstm = null; // this.fileurlp = this.student.replace("\\", "\\\\"); FileInputStream input = new FileInputStream("" + books + ""); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); Row row; for (int i = 1; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); String id = row.getCell(0).getStringCellValue(); String title = row.getCell(1).getStringCellValue(); String ediion = row.getCell(2).getStringCellValue(); String status = row.getCell(3).getStringCellValue(); //String imgurl =imgurlK.replace("\\", "\\\\"); String sql = "INSERT INTO books VALUES('" + id + "','" + title + "','" + ediion + "','" + status + "')"; pstm = (PreparedStatement) con.prepareStatement(sql); pstm.execute(); System.out.println("Import rows " + i); } con.commit(); pstm.close(); con.close(); input.close(); JOptionPane.showMessageDialog(null, "Success import excel to mysql table"); System.out.println("Success import excel to mysql table"); } catch (ClassNotFoundException e) { System.out.println(e); } catch (SQLException ex) { JOptionPane.showMessageDialog(null, "error import excel to mysql table"); System.out.println(ex); } catch (IOException ioe) { System.out.println(ioe); } }
From source file:library.restore.java
private void jButton5ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton5ActionPerformed String fileSeparator = System.getProperty("file.separator"); JFileChooser chooser = new JFileChooser(); //set JFileChooser default path chooser.setCurrentDirectory(new java.io.File(".")); //set JFileChooser title chooser.setDialogTitle("open"); //use as you need DIRECTORIES_ONLY, FILES_ONLY etc chooser.setFileSelectionMode(JFileChooser.FILES_ONLY); //false, if you want to view all kind of file type in JFileChooser //true, if you want to create FileFilter and open only file type you want to be view in JFileChooser chooser.setAcceptAllFileFilterUsed(false); if (chooser.showOpenDialog(this) == JFileChooser.APPROVE_OPTION) { //write selected path inside JTextField jtfFileLocation2.setText(chooser.getSelectedFile().getPath() + fileSeparator); //students=getText(chooser.getSelectedFile().getPath()+fileSeparator); loaned = jtfFileLocation2.getText(); System.out.println("" + loaned + ""); } else {/*from w w w .ja v a2 s. c o m*/ System.out.println("No Selection"); } try { Class.forName("com.mysql.jdbc.Driver"); Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/library", "root", "123ERYcog."); con.setAutoCommit(false); PreparedStatement pstm = null; // this.fileurlp = this.student.replace("\\", "\\\\"); FileInputStream input = new FileInputStream("" + loaned + ""); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); Row row; for (int i = 1; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); String no = row.getCell(0).getStringCellValue(); String bid = row.getCell(1).getStringCellValue(); String sid = row.getCell(2).getStringCellValue(); String title = row.getCell(3).getStringCellValue(); String fname = row.getCell(4).getStringCellValue(); String lname = row.getCell(5).getStringCellValue(); String form = row.getCell(6).getStringCellValue(); String classs = row.getCell(7).getStringCellValue(); String edition = row.getCell(8).getStringCellValue(); String updated_at = row.getCell(9).getStringCellValue(); //String title = row.getCell(3).getStringCellValue(); String sql = "INSERT INTO loaned VALUES('" + no + "','" + bid + "','" + sid + "','" + title + "','" + fname + "','" + lname + "','" + form + "','" + classs + "','" + edition + "','" + updated_at + "')"; pstm = (PreparedStatement) con.prepareStatement(sql); pstm.execute(); System.out.println("Import rows " + i); } con.commit(); pstm.close(); con.close(); input.close(); JOptionPane.showMessageDialog(null, "Success import excel to mysql table"); System.out.println("Success import excel to mysql table"); } catch (ClassNotFoundException e) { System.out.println(e); } catch (SQLException ex) { JOptionPane.showMessageDialog(null, "error import excel to mysql table"); System.out.println(ex); } catch (IOException ioe) { System.out.println(ioe); } }
From source file:library.restore.java
private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton3ActionPerformed String fileSeparator = System.getProperty("file.separator"); JFileChooser chooser = new JFileChooser(); //set JFileChooser default path chooser.setCurrentDirectory(new java.io.File(".")); //set JFileChooser title chooser.setDialogTitle("open"); //use as you need DIRECTORIES_ONLY, FILES_ONLY etc chooser.setFileSelectionMode(JFileChooser.FILES_ONLY); //false, if you want to view all kind of file type in JFileChooser //true, if you want to create FileFilter and open only file type you want to be view in JFileChooser chooser.setAcceptAllFileFilterUsed(false); if (chooser.showOpenDialog(this) == JFileChooser.APPROVE_OPTION) { //write selected path inside JTextField jtfFileLocation3.setText(chooser.getSelectedFile().getPath() + fileSeparator); //students=getText(chooser.getSelectedFile().getPath()+fileSeparator); loanedcourse = jtfFileLocation3.getText(); System.out.println("" + loanedcourse + ""); } else {//from w w w .j a v a 2s .c om System.out.println("No Selection"); } try { Class.forName("com.mysql.jdbc.Driver"); Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/library", "root", "123ERYcog."); con.setAutoCommit(false); PreparedStatement pstm = null; // this.fileurlp = this.student.replace("\\", "\\\\"); FileInputStream input = new FileInputStream("" + loanedcourse + ""); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); Row row; for (int i = 1; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); String no = row.getCell(0).getStringCellValue(); String bid = row.getCell(1).getStringCellValue(); String sid = row.getCell(2).getStringCellValue(); String title = row.getCell(3).getStringCellValue(); String fname = row.getCell(4).getStringCellValue(); String lname = row.getCell(5).getStringCellValue(); String form = row.getCell(6).getStringCellValue(); String classs = row.getCell(7).getStringCellValue(); String edition = row.getCell(8).getStringCellValue(); String updated_at = row.getCell(9).getStringCellValue(); //String title = row.getCell(3).getStringCellValue(); String sql = "INSERT INTO loanedcourse VALUES('" + no + "','" + bid + "','" + sid + "','" + title + "','" + fname + "','" + lname + "','" + form + "','" + classs + "','" + edition + "','" + updated_at + "')"; pstm = (PreparedStatement) con.prepareStatement(sql); pstm.execute(); System.out.println("Import rows " + i); } con.commit(); pstm.close(); con.close(); input.close(); JOptionPane.showMessageDialog(null, "Success import excel to mysql table"); System.out.println("Success import excel to mysql table"); } catch (ClassNotFoundException e) { System.out.println(e); } catch (SQLException ex) { JOptionPane.showMessageDialog(null, "error import excel to mysql table"); System.out.println(ex); } catch (IOException ioe) { System.out.println(ioe); } }
From source file:library.restore.java
private void jButton4ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton4ActionPerformed String fileSeparator = System.getProperty("file.separator"); JFileChooser chooser = new JFileChooser(); //set JFileChooser default path chooser.setCurrentDirectory(new java.io.File(".")); //set JFileChooser title chooser.setDialogTitle("open"); //use as you need DIRECTORIES_ONLY, FILES_ONLY etc chooser.setFileSelectionMode(JFileChooser.FILES_ONLY); //false, if you want to view all kind of file type in JFileChooser //true, if you want to create FileFilter and open only file type you want to be view in JFileChooser chooser.setAcceptAllFileFilterUsed(false); if (chooser.showOpenDialog(this) == JFileChooser.APPROVE_OPTION) { //write selected path inside JTextField jtfFileLocation4.setText(chooser.getSelectedFile().getPath() + fileSeparator); //students=getText(chooser.getSelectedFile().getPath()+fileSeparator); libfines = jtfFileLocation4.getText(); System.out.println("" + libfines + ""); } else {/*from w ww .j a v a 2 s. c o m*/ System.out.println("No Selection"); } try { Class.forName("com.mysql.jdbc.Driver"); Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/library", "root", "123ERYcog."); con.setAutoCommit(false); PreparedStatement pstm = null; // this.fileurlp = this.student.replace("\\", "\\\\"); FileInputStream input = new FileInputStream("" + libfines + ""); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); Row row; for (int i = 1; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); String pid = row.getCell(0).getStringCellValue(); String adm = row.getCell(1).getStringCellValue(); String amount = row.getCell(2).getStringCellValue(); String day = row.getCell(3).getStringCellValue(); String status = row.getCell(4).getStringCellValue(); //String imgurl =imgurlK.replace("\\", "\\\\"); String sql = "INSERT INTO libfines VALUES('" + pid + "','" + adm + "','" + amount + "','" + day + "','" + status + "')"; pstm = (PreparedStatement) con.prepareStatement(sql); pstm.execute(); System.out.println("Import rows " + i); } con.commit(); pstm.close(); con.close(); input.close(); JOptionPane.showMessageDialog(null, "Success import excel to mysql table"); System.out.println("Success import excel to mysql table"); } catch (ClassNotFoundException e) { System.out.println(e); } catch (SQLException ex) { JOptionPane.showMessageDialog(null, "error import excel to mysql table"); System.out.println(ex); } catch (IOException ioe) { System.out.println(ioe); } }
From source file:LoadExcels.loadPMTCT_FO.java
@Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {/*from ww w . ja v a2 s .c om*/ 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 {/*from 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:LoadExcels.loadTBExcel.java
@Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {/*from w ww.ja v a2 s .co m*/ session = request.getSession(); dbConn conn = new dbConn(); nextpage = "loadTBExcel.jsp"; String female_stat, male_stat; String less1, stat_1to4, stat_5to9, stat_10to14, stat_15to19, stat_20above, positive, negative; String art_numerator, art_denominator, art_female; String art_male, art_less1, art_1to4, art_5to9, art_10to14, art_15to19, art_20above; female_stat = male_stat = ""; less1 = stat_1to4 = stat_5to9 = stat_10to14 = stat_15to19 = stat_20above = positive = negative = ""; art_numerator = art_denominator = art_female = ""; art_male = art_less1 = art_1to4 = art_5to9 = art_10to14 = art_15to19 = art_20above = ""; String applicationPath = request.getServletContext().getRealPath(""); String uploadFilePath = applicationPath + File.separator + UPLOAD_DIR; session = request.getSession(); File fileSaveDir = new File(uploadFilePath); if (!fileSaveDir.exists()) { fileSaveDir.mkdirs(); } System.out.println("Upload File Directory=" + fileSaveDir.getAbsolutePath()); for (Part part : request.getParts()) { fileName = getFileName(part); part.write(uploadFilePath + File.separator + fileName); System.out.println("file name is : " + fileName); } if (!fileName.endsWith(".xls")) { nextpage = "loadExcel.jsp"; session.setAttribute("upload_success", "<font color=\"red\">Failed to load the excel file. Please choose the correct File.</font>"); } else { full_path = fileSaveDir.getAbsolutePath() + "\\" + fileName; System.out.println("the saved file directory is : " + full_path); // GET DATA FROM THE EXCEL AND AND OUTPUT IT ON THE CONSOLE.................................. FileInputStream fileInputStream = new FileInputStream(full_path); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet worksheet = workbook.getSheetAt(0); Iterator rowIterator = worksheet.iterator(); int i = 2, y = 0; while (rowIterator.hasNext()) { System.out.println(" in while " + i); HSSFRow rowi = worksheet.getRow(i); if (rowi == null) { nextpage = "loadTBExcel.jsp"; break; } HSSFCell cellYear = rowi.getCell((short) 0); year = (int) cellYear.getNumericCellValue(); HSSFCell cellQuarter = rowi.getCell((short) 1); quarterName = cellQuarter.getStringCellValue(); HSSFCell cellFacilityName = rowi.getCell((short) 4); facilityName = cellFacilityName.getStringCellValue(); HSSFCell cellMFLCODE = rowi.getCell((short) 5); if (cellMFLCODE.getCellType() == 0) { //numeric mflcode = "" + (int) cellMFLCODE.getNumericCellValue(); } else if (cellMFLCODE.getCellType() == 1) { mflcode = cellMFLCODE.getStringCellValue(); } HSSFCell cellstype = rowi.getCell((short) 6); String supporttype = cellstype.getStringCellValue(); HSSFCell cellNumerator = rowi.getCell((short) 7); Numerator = "" + (int) cellNumerator.getNumericCellValue(); HSSFCell cellDenominator = rowi.getCell((short) 8); Denominator = (int) cellDenominator.getNumericCellValue(); HSSFCell cellfemale = rowi.getCell((short) 9); female_stat = "" + (int) cellfemale.getNumericCellValue(); HSSFCell cellmale = rowi.getCell((short) 10); male_stat = "" + (int) cellmale.getNumericCellValue(); HSSFCell cellless1 = rowi.getCell((short) 11); less1 = "" + (int) cellless1.getNumericCellValue(); HSSFCell cell1to4 = rowi.getCell((short) 12); stat_1to4 = "" + (int) cell1to4.getNumericCellValue(); HSSFCell cell5to9 = rowi.getCell((short) 13); stat_5to9 = "" + (int) cell5to9.getNumericCellValue(); HSSFCell cell10to14 = rowi.getCell((short) 14); stat_10to14 = "" + (int) cell10to14.getNumericCellValue(); HSSFCell cell15to19 = rowi.getCell((short) 15); stat_15to19 = "" + (int) cell15to19.getNumericCellValue(); HSSFCell cell20above = rowi.getCell((short) 16); stat_20above = "" + (int) cell20above.getNumericCellValue(); HSSFCell cellpositive = rowi.getCell((short) 17); positive = "" + (int) cellpositive.getNumericCellValue(); HSSFCell cellnegative = rowi.getCell((short) 18); negative = "" + (int) cellnegative.getNumericCellValue(); HSSFCell cellart_numerator = rowi.getCell((short) 19); art_numerator = "" + (int) cellart_numerator.getNumericCellValue(); HSSFCell cellart_denominator = rowi.getCell((short) 20); art_denominator = "" + (int) cellart_denominator.getNumericCellValue(); HSSFCell cellart_female = rowi.getCell((short) 21); art_female = "" + (int) cellart_female.getNumericCellValue(); HSSFCell cellart_male = rowi.getCell((short) 22); art_male = "" + (int) cellart_male.getNumericCellValue(); HSSFCell cellart_less1 = rowi.getCell((short) 23); art_less1 = "" + (int) cellart_less1.getNumericCellValue(); HSSFCell cellart_1to4 = rowi.getCell((short) 24); art_1to4 = "" + (int) cellart_1to4.getNumericCellValue(); HSSFCell cellart_5to9 = rowi.getCell((short) 25); art_5to9 = "" + (int) cellart_5to9.getNumericCellValue(); HSSFCell cellart_10to14 = rowi.getCell((short) 26); art_10to14 = "" + (int) cellart_10to14.getNumericCellValue(); HSSFCell cellart_15to19 = rowi.getCell((short) 27); art_15to19 = "" + (int) cellart_15to19.getNumericCellValue(); HSSFCell cellart_20above = rowi.getCell((short) 28); art_20above = "" + (int) cellart_20above.getNumericCellValue(); // // int female_stat,male_stat; //int less1, stat_1to4,stat_5to9,stat_10to14,stat_15to19,stat_20above,positive,negative; // //int art_numerator, art_denominator, art_female; //int art_male,art_less1,art_1to4,art_5to9,art_10to14,art_15to19,art_20above; facilityID = ""; checker = 0; String get_id = "SELECT SubPartnerID FROM subpartnera WHERE CentreSanteId=?"; conn.pst = conn.conn.prepareStatement(get_id); conn.pst.setString(1, mflcode); conn.rs = conn.pst.executeQuery(); if (conn.rs.next() == true) { facilityID = conn.rs.getString(1); } if (facilityID.length() > 0) { // DISTRICT FOUND ADD THE HF TO THE SYSTEM......................... String getQuarterID = "SELECT id FROM quarter WHERE pmtct_fo_name=?"; conn.pst = conn.conn.prepareStatement(getQuarterID); conn.pst.setString(1, quarterName); conn.rs = conn.pst.executeQuery(); if (conn.rs.next() == true) { quarter = conn.rs.getInt(1); } checker = 0; // CHECK IF ALREADY ADDED TO PMTCT_FO TABLE id = year + "_" + quarter + "_" + facilityID; // System.out.println("to add data : "+facilityName+" id : "+facilityID+"mfl code "+mflcode+" year : "+year+" quarter : "+quarter+" numerator : "+Numerator+" denominator : "+Denominator); String checkerExisting = "SELECT id FROM tb_stat_art WHERE id='" + id + "'"; conn.rs = conn.st.executeQuery(checkerExisting); if (conn.rs.next() == true) { checker++; } // // // if (checker == 0) { String inserter = "INSERT INTO tb_stat_art (id,SubPartnerID,year,quarter,numerator,denominator,female,male,less1,1to4,5to9,10to14,15to19,20above,positive,negative,art_numerator,art_denominator,art_female," + "art_male,art_less1,art_1to4,art_5to9,art_10to14,art_15to19,art_20above,supporttype) " + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; conn.pst = conn.conn.prepareStatement(inserter); conn.pst.setString(1, id); conn.pst.setString(2, facilityID); conn.pst.setInt(3, year); conn.pst.setInt(4, quarter); conn.pst.setString(5, Numerator); conn.pst.setInt(6, Denominator); conn.pst.setString(7, female_stat); conn.pst.setString(8, male_stat); conn.pst.setString(9, less1); conn.pst.setString(10, stat_1to4); conn.pst.setString(11, stat_5to9); conn.pst.setString(12, stat_10to14); conn.pst.setString(13, stat_15to19); conn.pst.setString(14, stat_20above); conn.pst.setString(15, positive); conn.pst.setString(16, negative); conn.pst.setString(17, art_numerator); conn.pst.setString(18, art_denominator); conn.pst.setString(19, art_female); conn.pst.setString(20, art_male); conn.pst.setString(21, art_less1); conn.pst.setString(22, art_1to4); conn.pst.setString(23, art_5to9); conn.pst.setString(24, art_10to14); conn.pst.setString(25, art_15to19); conn.pst.setString(26, art_20above); conn.pst.setString(27, supporttype); conn.pst.executeUpdate(); added++; } else { String inserter = "UPDATE tb_stat_art SET SubPartnerID=?,year=?,quarter=?,numerator=?,denominator=?, " + "female=?,male=?,less1=?,1to4=?,5to9=?,10to14=?,15to19=?,20above=?,positive=?,negative=?,art_numerator=?,art_denominator=?," + "art_female=?,art_male=?,art_less1=?,art_1to4=?,art_5to9=?,art_10to14=?,art_15to19=?,art_20above=?,supporttype=?" + "WHERE id=?"; conn.pst = conn.conn.prepareStatement(inserter); conn.pst.setString(1, facilityID); conn.pst.setInt(2, year); conn.pst.setInt(3, quarter); conn.pst.setString(4, Numerator); conn.pst.setInt(5, Denominator); conn.pst.setString(6, female_stat); conn.pst.setString(7, male_stat); conn.pst.setString(8, less1); conn.pst.setString(9, stat_1to4); conn.pst.setString(10, stat_5to9); conn.pst.setString(11, stat_10to14); conn.pst.setString(12, stat_15to19); conn.pst.setString(13, stat_20above); conn.pst.setString(14, positive); conn.pst.setString(15, negative); conn.pst.setString(16, art_numerator); conn.pst.setString(17, art_denominator); conn.pst.setString(18, art_female); conn.pst.setString(19, art_male); conn.pst.setString(20, art_less1); conn.pst.setString(21, art_1to4); conn.pst.setString(22, art_5to9); conn.pst.setString(23, art_10to14); conn.pst.setString(24, art_15to19); conn.pst.setString(25, art_20above); conn.pst.setString(26, supporttype); conn.pst.setString(27, id); conn.pst.executeUpdate(); updated++; } } else { missing++; // missing facilities missingFacility += "facility name : " + facilityName + " mfl code : " + mflcode + " excel row num : " + i + "<br>"; System.out.println(facilityName + "facility is missing mflcode :" + mflcode); } i++; } } } catch (SQLException ex) { Logger.getLogger(loadTBExcel.class.getName()).log(Level.SEVERE, null, ex); } String sessionText = added + "New data added <> " + updated + " updated facilities<br> and " + missing + " missing facilities"; session.setAttribute("upload_success", sessionText); response.sendRedirect(nextpage); }