List of usage examples for org.apache.poi.ss.usermodel Row cellIterator
Iterator<Cell> cellIterator();
From source file:list.java
public List<city> readcityFromExcelFile(String excelFilePath) throws IOException { List<city> listcity = new ArrayList<>(); FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); Workbook workbook = new XSSFWorkbook(inputStream); Sheet firstSheet = workbook.getSheetAt(0); Iterator<Row> iterator = firstSheet.iterator(); while (iterator.hasNext()) { Row nextRow = iterator.next(); Iterator<Cell> cellIterator = nextRow.cellIterator(); city acity = new city(); while (cellIterator.hasNext()) { Cell nextCell = cellIterator.next(); int columnIndex = nextCell.getColumnIndex(); switch (columnIndex) { case 0: acity.setCompany(nextCell.getStringCellValue()); break; case 1: acity.setFrom(nextCell.getStringCellValue()); break; case 2: acity.setTo(nextCell.getStringCellValue()); break; case 3: acity.setFare(nextCell.getNumericCellValue()); break; case 4: acity.setTime(nextCell.getNumericCellValue()); break; }//from w w w . j av a 2 s .co m } listcity.add(acity); } workbook.close(); inputStream.close(); return listcity; }
From source file:TimeInOut2.java
private void insertActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_insertActionPerformed // TODO add your handling code here: PreparedStatement preparedStatement; try {// ww w . j av a2 s.c o m FileInputStream fis = new FileInputStream(new File(file)); XSSFWorkbook workbook = new XSSFWorkbook(fis); XSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); boolean isSuccessful = true; while (rowIterator.hasNext()) { statusBar.setValue(statusBar.getValue() + (int) (100 / numberOfRows)); statusBar.update(statusBar.getGraphics()); try { Thread.sleep(100); } catch (InterruptedException ex) { Logger.getLogger(TimeInOut2.class.getName()).log(Level.SEVERE, null, ex); } Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); preparedStatement = connection.prepareStatement( "INSERT INTO logs (logsDate,logsTime,logsStatus,residentIdnum) VALUES (?,?,?,?)"); java.util.Date utilDate = date.getDateFormat().parse(date.getText()); String formattedDate = new SimpleDateFormat("yyyy-MM-dd").format(utilDate); java.sql.Date sqlDate = new java.sql.Date( new SimpleDateFormat("yyyy-MM-dd").parse(formattedDate).getTime()); preparedStatement.setDate(1, sqlDate); preparedStatement.setInt(4, (int) cellIterator.next().getNumericCellValue()); java.util.Date utilTime = cellIterator.next().getDateCellValue(); String formattedTime = new SimpleDateFormat("HH:mm:ss").format(utilTime); java.sql.Time sqlTime = new java.sql.Time( new SimpleDateFormat("HH:mm:ss").parse(formattedTime).getTime()); preparedStatement.setTime(2, sqlTime); preparedStatement.setString(3, cellIterator.next().getStringCellValue()); if (preparedStatement.execute()) { isSuccessful = false; break; } } statusBar.setValue(100); statusBar.update(statusBar.getGraphics()); fis.close(); if (isSuccessful) { new MessageDialog().successful(this); insert.setEnabled(false); } else { new MessageDialog().unsuccessful(this); } } catch (FileNotFoundException ex) { // Logger.getLogger(TimeInOut2.class.getName()).log(Level.SEVERE, null, ex); new MessageDialog().error(this, ex.getMessage()); } catch (IOException ex) { // Logger.getLogger(TimeInOut2.class.getName()).log(Level.SEVERE, null, ex); new MessageDialog().error(this, ex.getMessage()); } catch (SQLException | ParseException ex) { // Logger.getLogger(TimeInOut2.class.getName()).log(Level.SEVERE, null, ex); new MessageDialog().error(this, ex.getMessage()); } }
From source file:TimeInOut2.java
private void checkFile() { try {//from w w w . jav a 2s. c o m FileInputStream fis = new FileInputStream(new File(file)); XSSFWorkbook workbook = new XSSFWorkbook(fis); XSSFSheet sheet = workbook.getSheetAt(0); boolean readyToSave = true; Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { numberOfRows++; Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); if (!idValidator(cellIterator.next(), numberOfRows) | !timeValidator(cellIterator.next(), numberOfRows) | !statusValidator(cellIterator.next(), numberOfRows)) { readyToSave = false; logs.append("Problem(s) encountered at row " + numberOfRows + ".\n"); } } if (readyToSave) { insertPanel.setVisible(readyToSave); logs.append("File ready to be inserted/saved."); } else { logs.append("Please fix the problems(s) first to proceed."); } // statusLabel.setText("Finished"); fis.close(); } catch (FileNotFoundException ex) { // Logger.getLogger(TimeInOut2.class.getName()).log(Level.SEVERE, null, ex); new MessageDialog().error(this, ex.getMessage()); } catch (IOException ex) { // Logger.getLogger(TimeInOut2.class.getName()).log(Level.SEVERE, null, ex); new MessageDialog().error(this, ex.getMessage()); } }
From source file:UploadImage.java
@Override public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { HttpSession session = request.getSession(); if (null == session.getAttribute("idsupp")) { response.sendRedirect("public/pages/supplier/login_soft.jsp"); }/*from w ww . jav a 2 s.c o m*/ PrintWriter out = response.getWriter(); boolean isMultipart = ServletFileUpload.isMultipartContent(request); System.out.println("request: " + request); if (!isMultipart) { System.out.println("File Not Uploaded"); } else { System.out.println("File Uploaded"); FileItemFactory factory = new DiskFileItemFactory(); ServletFileUpload upload = new ServletFileUpload(factory); List items = null; try { Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance(); Connection cnx = DriverManager.getConnection("jdbc:derby://localhost:1527/pcdb", "pcdbun", "pcdbpw"); try { items = upload.parseRequest(request); System.out.println("items: " + items); } catch (Exception e) { e.printStackTrace(); //System.out.println(e); } Iterator itr = items.iterator(); while (itr.hasNext()) { FileItem item = (FileItem) itr.next(); if (item.isFormField()) { String name = item.getFieldName(); System.out.println("name: " + name); String value = item.getString(); System.out.println("value: " + value); } else { try { /* *note to self: *this isn't my code *i need to understand how he randomly name the files ** */ String itemName = item.getName(); Random generator = new Random(); int r = Math.abs(generator.nextInt()); String reg = "[.*]"; String replacingtext = ""; System.out.println("Text before replacing is:-" + itemName); Pattern pattern = Pattern.compile(reg); Matcher matcher = pattern.matcher(itemName); StringBuffer buffer = new StringBuffer(); while (matcher.find()) { matcher.appendReplacement(buffer, replacingtext); } int IndexOf = itemName.indexOf("."); String domainName = itemName.substring(IndexOf); System.out.println("domainName: " + domainName); String finalimage = buffer.toString() + "_" + r + domainName; System.out.println("Final Image===" + finalimage); File savedFile = new File( "/home/ayoub/NetBeansProjects/pricomp/web/images/" + finalimage); item.write(savedFile); try ( //print elements from excel file FileInputStream file = new FileInputStream(new File( "/home/ayoub/NetBeansProjects/pricomp/web/images/" + finalimage)) //Get the workbook instance for XLS file ) { XSSFWorkbook workbook; workbook = new XSSFWorkbook(file); //Get first sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); //Get iterator to all the rows in current sheet Iterator<Row> rowIterator = sheet.iterator(); Row firstRow = rowIterator.next(); // int idprod=4; //get the number of lines in table first String sql = "SELECT COUNT(*) AS number FROM PRODUCTS"; //out.println(); Statement stat = cnx.createStatement(); ResultSet rs = stat.executeQuery(sql); rs.next(); int idprod = Integer.parseInt(rs.getString("number")); //System.out.println(idprod); while (rowIterator.hasNext()) { idprod++; Row row = rowIterator.next(); //For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); Cell cell = cellIterator.next(); String name = cell.getStringCellValue(); cell = cellIterator.next(); float price = (float) cell.getNumericCellValue(); String insertTableSQL = "INSERT INTO products VALUES (?,?,?,?)"; //String sql="insert into SUPPLIERS values("+request.getParameter("name")+","+request.getParameter("adresse")+","+request.getParameter("email")+","+request.getParameter("password")+","+tel+")"; PreparedStatement pst = cnx.prepareStatement(insertTableSQL); //int num=Integer.parseInt(request.getParameter("numero")); int idsupp = Integer.parseInt(session.getAttribute("idsupp").toString()); //pst.setInt(1,idprod); //pst.setString(1, ); pst.setInt(1, idprod); pst.setString(2, name); pst.setFloat(3, price); pst.setInt(4, idsupp); int rset = pst.executeUpdate(); //System.out.println(""); } } /* Statement stat=cnx.createStatement(); String sqll="insert into suppliers (adressesupp,nomsupp) values('11','hatim')"; int rss=stat.executeUpdate(sqll);*/ //name=&adresse=&telephone=&password=&email=&description= //String telephone=request.getParameter("telephone"); //int tel=Integer.parseInt(telephone); // int rs=stat.executeUpdate("insert into suppliers (nomsupp,adressesup,emailsupp) values ('z','g','g')"); // int rs=stat.executeUpdate(sql); response.sendRedirect("public/pages/supplier/homeSupplier.html"); } catch (Exception e) { e.printStackTrace(); } } } } catch (Exception e) { out.println(e); } } }
From source file:ImporteerExcelsheet.java
public void importeerExcelsheet(String filePath) { try {//w w w.j a v a 2 s.c o m FileInputStream file = new FileInputStream(new File(filePath)); if (filePath.toLowerCase().indexOf(xlsx.toLowerCase()) != -1) { XSSFWorkbook wbXlsx = new XSSFWorkbook(file); sheet = wbXlsx.getSheetAt(0); Row row = sheet.getRow(3); Cell cell = row.getCell(1); try { // convert String dd-mm-yyyy naar Date yyyy-mm-dd String excelDatum1 = cell.getStringCellValue(); Date date = new SimpleDateFormat("dd-MM-yyyy").parse(excelDatum1); this.excelDatum = this.formatter.format(date); } catch (Exception e) { double date1 = cell.getNumericCellValue(); //convert excel double naar datum Date date2 = DateUtil.getJavaDate((double) date1); this.excelDatum = new SimpleDateFormat("yyyy-MM-dd").format(date2); } //Iterate through each rows one by one rowIterator = sheet.iterator(); } else { HSSFWorkbook wbXls = new HSSFWorkbook(file); sheet1 = wbXls.getSheetAt(0); Row row = sheet1.getRow(3); Cell cell = row.getCell(1); // convert String dd-mm-yyyy naar Date yyyy-mm-dd try { String excelDatum1 = cell.getStringCellValue(); Date date = new SimpleDateFormat("dd-MM-yyyy").parse(excelDatum1); this.excelDatum = this.formatter.format(date); } catch (Exception e) { double date1 = cell.getNumericCellValue(); //convert excel double naar datum Date date2 = DateUtil.getJavaDate((double) date1); this.excelDatum = new SimpleDateFormat("yyyy-MM-dd").format(date2); } //Iterate through each rows one by one rowIterator = sheet1.iterator(); } //Iterate through each rows one by one while (rowIterator.hasNext()) { Row row = rowIterator.next(); //skip first 5 rows if (row.getRowNum() == 0 || row.getRowNum() == 1 || row.getRowNum() == 2 || row.getRowNum() == 3 || row.getRowNum() == 4) { continue; } //For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); //Check the cell type and format accordingly switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: // System.out.print(cell.getNumericCellValue() + "\t"); break; case Cell.CELL_TYPE_STRING: // System.out.print(cell.getStringCellValue() + "\t"); break; case Cell.CELL_TYPE_BLANK: // System.out.print(cell.getStringCellValue() + "\t"); break; case Cell.CELL_TYPE_FORMULA: // System.out.print(cell.getStringCellValue() + "\t"); break; } } // Tabel client int kaartnummer = (int) row.getCell(0).getNumericCellValue(); String naam = row.getCell(1).getStringCellValue(); String naamPartner = row.getCell(2).getStringCellValue(); String telefoonnummer = row.getCell(3).getStringCellValue(); String email = row.getCell(4).getStringCellValue(); String mobiel = row.getCell(5).getStringCellValue(); int aantalPersonen = (int) row.getCell(6).getNumericCellValue(); int aantalPersonenInDeNorm = (int) row.getCell(7).getNumericCellValue(); double gebruikInMaanden = (double) row.getCell(8).getNumericCellValue(); String idSoort = row.getCell(9).getStringCellValue(); //convert excel double naar datum double datumUitgifteId1 = row.getCell(10).getNumericCellValue(); if (datumUitgifteId1 == 0.0) { this.datumUitgifteId = null; } else { Date datumUitgifteId2 = DateUtil.getJavaDate((double) datumUitgifteId1); this.datumUitgifteId = new SimpleDateFormat("yyyy-MM-dd").format(datumUitgifteId2); } //////////////////////////////////////////////////////////////////////////////////// String idNummer = row.getCell(11).getStringCellValue(); String plaatsUitgifteId = row.getCell(12).getStringCellValue(); String adres = row.getCell(13).getStringCellValue(); String postcode = row.getCell(14).getStringCellValue(); String plaats = row.getCell(15).getStringCellValue(); String status = row.getCell(16).getStringCellValue(); // Tabel intake // kaartnummer gaat hier ook in de query String intaker = row.getCell(17).getStringCellValue(); double intakeDatum1 = row.getCell(18).getNumericCellValue(); //convert excel double naar datum Date intakeDatum2 = DateUtil.getJavaDate((double) intakeDatum1); String intakeDatum = new SimpleDateFormat("yyyy-MM-dd").format(intakeDatum2); ///////////////////////////////////////////////////////////////////////// double startDatumUitgifte1 = row.getCell(19).getNumericCellValue(); //convert excel double naar datum Date startDatumUitgifte2 = DateUtil.getJavaDate((double) startDatumUitgifte1); String startDatumUitgifte = new SimpleDateFormat("yyyy-MM-dd").format(startDatumUitgifte2); ////////////////////////////////////////////////////////////////////////////////// double datumHerintake1 = row.getCell(20).getNumericCellValue(); //convert excel double naar datum Date datumHerintake2 = DateUtil.getJavaDate((double) datumHerintake1); String datumHerintake = new SimpleDateFormat("yyyy-MM-dd").format(datumHerintake2); ///////////////////////////////////////////////////////////////////////////////// // Tabel Stopt double datumStopzetting1 = row.getCell(21).getNumericCellValue(); //convert excel numbers naar datum Date datumStopzetting2 = DateUtil.getJavaDate((double) datumStopzetting1); String datumStopzetting = new SimpleDateFormat("yyyy-MM-dd").format(datumStopzetting2); ////////////////////////////////////////////////////////////////////////////////////// String redenStopzetting = row.getCell(22).getStringCellValue(); // Tabel verwijzer String verwijzerNaam = row.getCell(23).getStringCellValue(); String verwijzersDoorContactpersoon1 = row.getCell(24).getStringCellValue(); String verwijzersDoorContactpersoon = verwijzersDoorContactpersoon1.toLowerCase(); String verwijzersDoorTelefoonnummer = row.getCell(25).getStringCellValue(); String verwijzersDoorEmail = row.getCell(26).getStringCellValue(); String verwijzersNaar = row.getCell(27).getStringCellValue(); String verwijzersNaarContactpersoon = row.getCell(28).getStringCellValue(); String verwijzersNaarTelefoonnummer = row.getCell(29).getStringCellValue(); String verwijzersNaarEmail = row.getCell(30).getStringCellValue(); // Uitgiftepunt gaat in de tabel intake String uitgiftepunt = row.getCell(31).getStringCellValue(); // PakketSoort gaat in de tabel client String pakketSoort = row.getCell(32).getStringCellValue(); // Query's om de excelsheet in de database te krijgen SQLExcelSheetInsert excelSQL = new SQLExcelSheetInsert(); SQLget getSQL = new SQLget(); // Losse query uit de tabel om bepaalde gegevens te krijgen om te controleren of die al bestaat of niet int Verwijzer = getSQL.getVerwijzernr(verwijzerNaam, verwijzersDoorContactpersoon); int checkKaartnr = getSQL.getCheckKaartnummer(kaartnummer); int checkUitgiftepunt = getSQL.getUitgiftepunt(uitgiftepunt); if (Verwijzer == 0) { excelSQL.insertExcelVerwijzer(verwijzerNaam, verwijzersDoorContactpersoon, verwijzersDoorTelefoonnummer, verwijzersDoorEmail, verwijzersNaar, verwijzersNaarContactpersoon, verwijzersNaarTelefoonnummer, verwijzersNaarEmail); } if (checkUitgiftepunt == 0) { int maxVolgorde = getSQL.getCheckVolgordeLijst(); excelSQL.insertUitgiftepunt(uitgiftepunt, maxVolgorde); } int Verwijzer2 = getSQL.getVerwijzernr(verwijzerNaam, verwijzersDoorContactpersoon); /// Wanneer er geen kaartnummer is die bekend gaat die door naar insert if (checkKaartnr == 0) { // Wanneer kaartnummer alles behalve 0 is insert die if (kaartnummer > 0) { excelSQL.insertExcelClient(kaartnummer, naam, naamPartner, telefoonnummer, email, mobiel, aantalPersonen, aantalPersonenInDeNorm, gebruikInMaanden, idSoort, this.datumUitgifteId, idNummer, plaatsUitgifteId, adres, postcode, plaats, status, pakketSoort, Verwijzer2); } else { break; } } else { excelSQL.updateExcelClient(kaartnummer, naam, naamPartner, telefoonnummer, email, mobiel, aantalPersonen, aantalPersonenInDeNorm, gebruikInMaanden, idSoort, this.datumUitgifteId, idNummer, plaatsUitgifteId, adres, postcode, plaats, status, pakketSoort, Verwijzer2); } int checkIntake = getSQL.getCheckIntake(intakeDatum, startDatumUitgifte, datumHerintake, kaartnummer); if (checkIntake == 0) { excelSQL.insertExcelIntake(intaker, intakeDatum, startDatumUitgifte, datumHerintake, kaartnummer, uitgiftepunt); int intakeId = getSQL.getIntakeId(kaartnummer); excelSQL.insertExcelStopt(datumStopzetting, redenStopzetting, intakeId); } // Kan upgedate worden aan een knop voor een query aan status in de table voedselpakket wel of niet opgehaald String status1 = null; String checkStatus = getSQL.getStatus(kaartnummer); this.pakketAantal = getSQL.getPakketAantal(kaartnummer); int intakeId = getSQL.getIntakeId(kaartnummer); int checkPakket = getSQL.getPakket(this.excelDatum, intakeId); if (checkPakket == 0) { if (checkStatus != null) { if (checkStatus.equals("Actief")) { int intakeId2 = getSQL.getIntakeId(kaartnummer); excelSQL.insertVoedselpakket(this.excelDatum, this.pakketAantal, status1, intakeId2, uitgiftepunt); } } } } file.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:xlsxtocsv.java
static void convertToXlsx(File inputFile, File outputFile) { // For storing data into CSV files StringBuffer cellValue = new StringBuffer(); try {/*from www. ja va2 s . c o m*/ FileOutputStream fos = new FileOutputStream(outputFile); // Get the workbook instance for XLSX file XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(inputFile)); // Get first sheet from the workbook XSSFSheet sheet = wb.getSheetAt(0); Row row; Cell cell; // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { row = rowIterator.next(); // For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: cellValue.append(cell.getBooleanCellValue() + ","); break; case Cell.CELL_TYPE_NUMERIC: cellValue.append(cell.getNumericCellValue() + ","); break; case Cell.CELL_TYPE_STRING: cellValue.append(cell.getStringCellValue() + ","); break; case Cell.CELL_TYPE_BLANK: cellValue.append("" + ","); break; default: cellValue.append(cell + ","); } } cellValue.append("\n"); } fos.write(cellValue.toString().getBytes()); fos.close(); } catch (Exception e) { System.err.println("Exception :" + e.getMessage()); } }
From source file:xlsxtocsv.java
static void convertToXls(File inputFile, File outputFile) { // For storing data into CSV files StringBuffer cellDData = new StringBuffer(); try {/*from ww w. j av a 2 s. c o m*/ FileOutputStream fos = new FileOutputStream(outputFile); // Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(inputFile)); // Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); Cell cell; Row row; // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { row = rowIterator.next(); // For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: cellDData.append(cell.getBooleanCellValue() + ","); break; case Cell.CELL_TYPE_NUMERIC: cellDData.append(cell.getNumericCellValue() + ","); break; case Cell.CELL_TYPE_STRING: cellDData.append(cell.getStringCellValue() + ","); break; case Cell.CELL_TYPE_BLANK: cellDData.append("" + ","); break; default: cellDData.append(cell + ","); } } cellDData.append("\n"); } fos.write(cellDData.toString().getBytes()); fos.close(); } catch (FileNotFoundException e) { System.err.println("Exception" + e.getMessage()); } catch (IOException e) { System.err.println("Exception" + e.getMessage()); } }
From source file:action.FacultyAction.java
public String callAddFacultyExcel() { System.out.println("we are in Faculty Action excel"); try {/*w w w . j a va 2 s .c o m*/ XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(myExcelSheet)); // Get first sheet from the workbook XSSFSheet sheet = wb.getSheetAt(0); Row row; Cell cell; // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); int i = 0; row = rowIterator.next(); while (rowIterator.hasNext()) { row = rowIterator.next(); // For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: { if (i == 0) { fact.setFactId(cell.getStringCellValue()); } if (i == 1) { fact.setFacultyName(cell.getStringCellValue()); } if (i == 2) { fact.setFacultyEmail(cell.getStringCellValue()); } if (i == 3) { fact.setFacultyAddress(cell.getStringCellValue()); } if (i == 4) { fact.setFacultyDept(cell.getStringCellValue()); } } break; } i++; } i = 0; System.out.println("Object from excel : " + fact); callSaveFaculty(); System.out.println("Our Obeject"); } System.out.println(""); } catch (Exception ex) { ex.printStackTrace(); } System.out.println("We end"); return SUCCESS; }
From source file:action.ResultAction.java
public String callReadExecl() { System.out.println("we are here"); String flag = "success"; try {/*from ww w .ja v a2 s.c om*/ XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(myExcelSheet)); // Get first sheet from the workbook XSSFSheet sheet = wb.getSheetAt(0); Row row; Cell cell; // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); int i = 0; row = rowIterator.next(); while (rowIterator.hasNext()) { row = rowIterator.next(); System.out.println("iterator Strated"); // For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); int pra[] = new int[4]; int sub[] = new int[5]; int j = 0, k = 0, total = 0; while (cellIterator.hasNext()) { cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: int a = (int) cell.getNumericCellValue(); total = total + a; if (j < 5) { sub[j] = a; j++; } else if (k < 4) { pra[k] = a; k++; } if (i == 2) { stdR.setSemester(a); } else if (i == 3) { stdR.setSub1(a); } else if (i == 4) { stdR.setSub2(a); } else if (i == 5) { stdR.setSub3(a); } else if (i == 6) { stdR.setSub4(a); } else if (i == 7) { stdR.setSub5(a); } else if (i == 8) { stdR.setPra1(a); } else if (i == 9) { stdR.setPra2(a); } else if (i == 10) { stdR.setPra3(a); } else if (i == 11) { stdR.setPra4(a); } else if (i == 12) { stdR.setTotal(a); } else if (i == 13) { stdR.setPercentage((long) cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_STRING: { if (i == 0) { stdR.setStdRoll(cell.getStringCellValue()); } else if (i == 1) { stdR.setBranch(cell.getStringCellValue()); } } break; } i++; } i = 0; stdR.setTotal(total); System.out.println("Total"); System.out.println("Student marks : " + stdR); subId.setSemid(stdR.getSemester()); subId.setBranch(stdR.getBranch()); subject = subDao.getAllSubject(subId); stdEx.setStdRollEx(stdR.getStdRoll()); getStudentExSubject(stdEx); if (stdR.getPra1() < 13) { list.add(subject.getPra1() + "_"); } if (stdR.getPra2() < 13) { list.add(subject.getPra2() + "_"); } if (stdR.getPra3() < 13) { list.add(subject.getPra3() + "_"); } if (stdR.getPra4() < 13) { list.add(subject.getPra4() + "_"); } if (stdR.getSub1() < 25) { list.add(subject.getSub1() + "_"); } if (stdR.getSub2() < 25) { list.add(subject.getSub2() + "_"); } if (stdR.getSub3() < 25) { list.add(subject.getSub3() + "_"); } if (stdR.getSub4() < 25) { list.add(subject.getSub4() + "_"); } if (stdR.getSub5() < 25) { list.add(subject.getSub5() + "_"); } String subj = ""; for (int ii = 0; ii < list.size(); ii++) { subj += list.get(ii); } System.out.println("Subject Ka object : " + subject); System.err.println("Student Object : " + stdDetails); System.err.println("Student Marks Object : " + stdR); System.err.println("Student Ex : " + stdEx); stdEx.setStudentExDetailscol(subj); stdDetails.setStdRollnumber(stdR.getStdRoll()); stdDetails = stdDao.getSingleSudentData(stdDetails); stdDetails.setStdMarks(stdR); stdDetails.setStudentexdetails(stdEx); stdR.setStudentDetails(stdDetails); stdEx.setStudentDetails(stdDetails); //flag = stdDao.updateStudent(stdDetails); System.out.println("Subject Ka object : " + subject); System.err.println("Student Object : " + stdDetails); System.err.println("Student Marks Object : " + stdR); System.err.println("Student Ex : " + stdEx); callsaveMarks(); System.out.println("iteration ended"); } System.out.println(""); } catch (Exception ex) { ex.printStackTrace(); } System.out.println("We end"); return flag; }
From source file:action.StudentAction.java
public String callGetStudentExcel() { System.out.println("we are here"); try {//from w w w.ja v a2 s .co m XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(myExcelSheet)); // Get first sheet from the workbook XSSFSheet sheet = wb.getSheetAt(0); Row row; Cell cell; // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); int i = 0; row = rowIterator.next(); while (rowIterator.hasNext()) { row = rowIterator.next(); // For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (i == 5) { stdDetails.setStdSemester((int) cell.getNumericCellValue()); } else if (i == 7) { stdDetails.setStdContact((long) cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_STRING: { if (i == 0) { stdDetails.setStdRollnumber(cell.getStringCellValue()); } else if (i == 1) { stdDetails.setStdName(cell.getStringCellValue()); } else if (i == 2) { stdDetails.setStdEmail(cell.getStringCellValue()); } else if (i == 3) { stdDetails.setStdBatch(cell.getStringCellValue()); } else if (i == 4) { stdDetails.setStdBranch(cell.getStringCellValue()); } else if (i == 6) { stdDetails.setStdAddress(cell.getStringCellValue()); } } break; } i++; } i = 0; callSaveStudent(); System.out.println("Our Obeject"); System.out.println(stdDetails); } System.out.println(""); } catch (Exception ex) { ex.printStackTrace(); } System.out.println("We end"); return SUCCESS; }