List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet iterator
@Override
public Iterator<Row> iterator()
From source file:TestCaseReaderTest.java
public void testSimple() throws Exception { //public List<TestScriptTemplate> readExcel(){ FileInputStream file = new FileInputStream( new File("C:\\Users\\nbret00\\Documents\\SeleniumSmokeTest\\TestCases.xlsx")); List<TestScriptTemplate> tstList = new ArrayList<TestScriptTemplate>(); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); //Iterate through each rows one by one Iterator<Row> rowIterator = sheet.iterator(); List<TestScriptTemplate> TestScriptTemplateList = new ArrayList(); while (rowIterator.hasNext()) { Row row = rowIterator.next();/*from w ww . j a va 2s. c om*/ //skip until row 5 if (row.getRowNum() > 4) { TestScriptTemplate tscripttemp = new TestScriptTemplate(); //Cell appcode = row.getCell(0); //this should be the item # on the list //System.out.println("application name #: "+itemnum.getStringCellValue()); tscripttemp.setAppCode(getCellValueStr(row.getCell(1))); tscripttemp.setAppURL(getCellValueStr(row.getCell(2))); tscripttemp.setAppUserID(getCellValueStr(row.getCell(3))); tscripttemp.setAppPassword(getCellValueStr(row.getCell(4))); tscripttemp.setHomePageTitle(getCellValueStr(row.getCell(5))); tscripttemp.setHomePageElement(getCellValueStr(row.getCell(6))); tscripttemp.setLevel1URL(getCellValueStr(row.getCell(7))); tscripttemp.setLevel1PageTitle(getCellValueStr(row.getCell(8))); tscripttemp.setLevel1Element(getCellValueStr(row.getCell(9))); //tscripttemp.setAppUserID(row.getCell(3).getStringCellValue()); //tscripttemp.setAppPassword(row.getCell(3).getStringCellValue()); TestScriptTemplateList.add(tscripttemp); System.out.println("this to string: " + tscripttemp.toString()); } } file.close(); }
From source file:ExcelConverter.java
public List<ScheduleClass> Converter() throws FileNotFoundException, IOException { ArrayList<ScheduleClass> scheduleList = new ArrayList<>(); FileInputStream fis = new FileInputStream(pathFile); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet sheet = wb.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); CellRangeAddress add;//w w w . j av a2 s .c o m int colNoIdx = 0; ArrayList<String> dosen = new ArrayList<>(); ArrayList<Integer> idxDosen = new ArrayList<>(); ArrayList<Integer> colDosen = new ArrayList<>(); ArrayList<String> location = new ArrayList<>(); int idxNumber = 0; ArrayList<Integer> locationIdx = new ArrayList<>(); outerloop: for (int j = 0; j < sheet.getLastRowNum(); j++) { row = sheet.getRow(j); for (int f = 0; f < row.getLastCellNum(); f++) { Cell cell = row.getCell(j); if (cell.getStringCellValue().contains("No.")) { rowNoIdx = j; colNoIdx = cell.getColumnIndex(); break outerloop; } } } outerloop2: for (int i = 0; i < sheet.getLastRowNum(); i++) { row = sheet.getRow(i); outerloop: for (int j = 0; j < row.getLastCellNum(); j++) { Cell cell = row.getCell(j); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); if (cell.getColumnIndex() == colNoIdx && i > rowNoIdx + 3 && evaluator.evaluate(cell).getCellType() != Cell.CELL_TYPE_NUMERIC) { i = sheet.getLastRowNum(); break outerloop2; } if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 1)) { String delims = "[,. ]"; String[] sumary = cell.getStringCellValue().split(delims); for (int l = 0; l < sumary.length; l++) { if (sumary[l].equalsIgnoreCase("Mrt")) { sumary[l] = "3"; } } lc = LocalDate.of(Integer.parseInt(sumary[5]), Integer.parseInt(sumary[3]), Integer.parseInt(sumary[2])); } if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 2)) { if (cell.getStringCellValue().equalsIgnoreCase("LIBUR")) { i = i + 1; break outerloop; } else { String delimsJam = "[-]"; String[] arrJam = cell.getStringCellValue().split(delimsJam); for (int k = 0; k < arrJam.length; k++) { arrJam[k] = arrJam[k].replace('.', ':'); } lt = LocalTime.parse(arrJam[0]); } } if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 5)) { subject = cell.getStringCellValue(); } if (cell.getRowIndex() > rowNoIdx && cell.getColumnIndex() >= colNoIdx + 6 && cell.getColumnIndex() < row.getLastCellNum()) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { } if (cell.getCellType() == Cell.CELL_TYPE_STRING) { if (cell.getStringCellValue().contains(":")) { String[] splt = cell.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { dosen.add(splt2[l].trim()); location.add("Lab"); } } else { CellReference cr = new CellReference(1, cell.getColumnIndex()); Row row2 = sheet.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); if (!cell.getStringCellValue().isEmpty()) { dosen.add(cell.getStringCellValue().trim()); location.add(String.valueOf((int) c.getNumericCellValue()).trim()); } } } if (cell.getCellType() == Cell.CELL_TYPE_BLANK && cell.getRowIndex() > 2) { CellReference cr = new CellReference(cell.getRowIndex() - 1, cell.getColumnIndex()); Row row2 = sheet.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); CellReference cr2 = new CellReference(1, cell.getColumnIndex()); Row row3 = sheet.getRow(cr2.getRow()); Cell c2 = row3.getCell(cr2.getCol()); if (c.getStringCellValue().contains(":")) { String[] splt = c.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { dosen.add("".trim()); location.add(""); } } else { if (!c.getStringCellValue().isEmpty()) { dosen.add(""); location.add(""); } } } } } for (int j = 0; j < dosen.size(); j++) { scheduleList .add(new ScheduleClass(lc, lt, lt.plusHours(2), subject, dosen.get(j), location.get(j))); } dosen.clear(); location.clear(); } return Mergering(scheduleList); }
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 {/*from w w w . ja va 2 s. c om*/ 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.ja v a 2 s.c om 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. java 2 s . c om*/ 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:xlsxtocsv.java
static void convertToXlsx(File inputFile, File outputFile) { // For storing data into CSV files StringBuffer cellValue = new StringBuffer(); try {/*from w w w.jav a 2 s .co 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:TaskFetcher.java
static HashMap<String, String> fetchActiveTasks(String username, String excel_path) { HashMap<String, String> activetasks = new HashMap<String, String>(); FileInputStream file = null;/* ww w . j a v a 2 s. com*/ try { file = new FileInputStream(new File(excel_path)); } catch (FileNotFoundException ex) { Logger.getLogger(TaskFetcher.class.getName()).log(Level.SEVERE, null, ex); } //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = null; try { workbook = new XSSFWorkbook(file); } catch (IOException ex) { Logger.getLogger(TaskFetcher.class.getName()).log(Level.SEVERE, null, ex); } //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); if (rowIterator.hasNext()) rowIterator.next(); //skipping the first row of heading while (rowIterator.hasNext()) //loop over all entries in the excel sheet { Row row = rowIterator.next(); Cell cell = row.getCell(0); if (username.equalsIgnoreCase(cell.getStringCellValue())) { String task; String status; String totTimeString = null; String latestTimeStamp; double totTime; task = row.getCell(1).getStringCellValue(); status = row.getCell(2).getStringCellValue(); latestTimeStamp = row.getCell(3).getStringCellValue(); if (status.equalsIgnoreCase("Paused") || status.equalsIgnoreCase("Deferred")) { if (row.getCell(4) != null) totTimeString = row.getCell(4).getStringCellValue(); } else if (status.equalsIgnoreCase("In-Progress")) { if (row.getCell(4) != null) totTimeString = row.getCell(4).getStringCellValue(); if (totTimeString != null) { totTime = Double.parseDouble(totTimeString); } else { totTime = 0; } DateFormat df = new SimpleDateFormat("dd/MM/yy HH:mm:ss"); Date currentTimestamp = new Date(); Date latestTimeStampObj = null; df.format(currentTimestamp); try { latestTimeStampObj = df.parse(latestTimeStamp); } catch (ParseException ex) { Logger.getLogger(TaskFetcher.class.getName()).log(Level.SEVERE, null, ex); } long timeDifference = currentTimestamp.getTime() - latestTimeStampObj.getTime(); long divisor = 60 * 60 * 1000; double diffHours = ((double) timeDifference / (double) divisor); totTime += diffHours; totTimeString = String.valueOf(totTime); } if (status != null && !status.equalsIgnoreCase("completed") && task != null && totTimeString != null) { activetasks.put(task, status + ":" + totTimeString); } } } try { workbook.close(); } catch (IOException ex) { Logger.getLogger(TaskFetcher.class.getName()).log(Level.SEVERE, null, ex); } return activetasks; }
From source file:TaskFetcher.java
static HashMap<String, String> fetchAllTasks(String username, String excel_path) { HashMap<String, String> alltasks = new HashMap<String, String>(); FileInputStream file = null;//w w w . j a v a 2 s.com try { file = new FileInputStream(new File(excel_path)); } catch (FileNotFoundException ex) { Logger.getLogger(TaskFetcher.class.getName()).log(Level.SEVERE, null, ex); } //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = null; try { workbook = new XSSFWorkbook(file); } catch (IOException ex) { Logger.getLogger(TaskFetcher.class.getName()).log(Level.SEVERE, null, ex); } //Get first sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(1); Iterator<Row> rowIterator = sheet.iterator(); if (rowIterator.hasNext()) rowIterator.next(); //skipping the first row of heading while (rowIterator.hasNext()) //loop over all entries in the excel sheet { Row row = rowIterator.next(); Cell cell = row.getCell(0); if (username.equalsIgnoreCase(cell.getStringCellValue())) { String task; String status; String timestamp; String comments; task = row.getCell(1).getStringCellValue(); status = row.getCell(2).getStringCellValue(); timestamp = row.getCell(3).getStringCellValue(); comments = row.getCell(4).getStringCellValue(); alltasks.put(timestamp, task + ":" + status + ":" + comments); } } try { workbook.close(); } catch (IOException ex) { Logger.getLogger(TaskFetcher.class.getName()).log(Level.SEVERE, null, ex); } return alltasks; }
From source file:accounts.ExcelUtils.java
License:Apache License
public Map<String, Map<TRId, TR>> processAllSheets(String filename) throws IOException { Map<String, Map<TRId, TR>> excelTrMap = new TreeMap<>(); FileInputStream file = new FileInputStream(new File(filename)); // Get the workbook instance for XLS file XSSFWorkbook workbook = new XSSFWorkbook(file); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { XSSFSheet sheet = workbook.getSheetAt(i); String sheetName = workbook.getSheetName(i); Map<TRId, TR> mapTr = new HashMap<>(); excelTrMap.put(sheetName, mapTr); System.out.println("Processing sheet: " + sheetName); // Get iterator to all the rows in current sheet Iterator<Row> rowIterator = sheet.iterator(); for (int rownum = 1; rownum <= sheet.getLastRowNum(); rownum++) { Row row = sheet.getRow(rownum); // Get iterator to all cells of current row TR tr = new TRNonDB(); tr.setDate(row.getCell(0).getDateCellValue()); tr.setDescription(row.getCell(1).getStringCellValue()); tr.setDebit((float) row.getCell(2).getNumericCellValue()); tr.setComment(row.getCell(3).getStringCellValue()); tr.setTrType(row.getCell(4).getStringCellValue()); tr.setTaxCategory(row.getCell(5).getStringCellValue()); tr.setProperty(row.getCell(6).getStringCellValue()); tr.setOtherEntity(row.getCell(7).getStringCellValue()); String lockedStr = row.getCell(7).getStringCellValue(); if ("YES".equalsIgnoreCase(lockedStr) || "TRUE".equalsIgnoreCase(lockedStr)) { tr.setLocked(true);/*from w w w. java 2 s. com*/ } tr.setTrId(); mapTr.put(tr.getTrId(), tr); } } return excelTrMap; }
From source file:action.FacultyAction.java
public String callAddFacultyExcel() { System.out.println("we are in Faculty Action excel"); try {//w w w .ja va 2s .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(); // 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; }