List of usage examples for org.apache.poi.ss.usermodel Row cellIterator
Iterator<Cell> cellIterator();
From source file:com.dexter.fms.mbean.UserMBean.java
@SuppressWarnings("unchecked") public void BatchLoadPersonels() { if (getPartner() != null && getPersonelsBatchExcel() != null) { String naration = "Batch load staffs: "; GeneralDAO gDAO = new GeneralDAO(); try {/* w w w . ja v a 2 s .c om*/ ByteArrayInputStream byteIn = new ByteArrayInputStream(getPersonelsBatchExcel().getContents()); //Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(byteIn); //Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); //Get iterator to all the rows in current sheet starting from row 2 Iterator<Row> rowIterator = sheet.iterator(); int pos = 1; gDAO.startTransaction(); boolean ret = false; while (rowIterator.hasNext()) { Row row = rowIterator.next(); String staffID = "", firstname = "", lastname = "", department = "", region = "", address = "", phone = "", email = "", isAUser = ""; String username = "", password = "", cpassword = "", role = ""; String isADriver = "", driver_grade = "", driver_license_no = "", driver_license_expiry_date = "", guarantor = ""; if (pos > 1) { //Get iterator to all cells of current row Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String val = ""; switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: val = ""; break; case Cell.CELL_TYPE_BOOLEAN: val = "" + cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_ERROR: val = ""; break; case Cell.CELL_TYPE_NUMERIC: val = "" + cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: val = cell.getStringCellValue(); break; default: { try { val = cell.getStringCellValue(); } catch (Exception ex) { } break; } } switch (cell.getColumnIndex()) { case 0: staffID = val; break; case 1: firstname = val; break; case 2: lastname = val; break; case 3: department = val; break; case 4: region = val; break; case 5: address = val; break; case 6: phone = val; break; case 7: email = val; break; case 8: isAUser = val; break; case 9: username = val; break; case 10: password = val; break; case 11: cpassword = val; break; case 12: role = val; break; case 13: isADriver = val; break; case 14: driver_grade = val; break; case 15: driver_license_no = val; break; case 16: driver_license_expiry_date = val; break; case 17: guarantor = val; break; } } PartnerPersonel pp = new PartnerPersonel(); pp.setPartner(getPartner()); pp.setCreatedBy(dashBean.getUser()); pp.setCrt_dt(new Date()); pp.setStaff_id(staffID); pp.setFirstname(firstname); pp.setLastname(lastname); pp.setAddress(address); pp.setPhone(phone); pp.setEmail(email); if (department != null && department.trim().length() > 0) { Query q = gDAO.createQuery( "Select e from Department e where e.partner = :partner and e.name = :name"); q.setParameter("partner", getPartner()); q.setParameter("name", department); Object objs = gDAO.search(q, 0); if (objs != null) { Vector<Department> objsList = (Vector<Department>) objs; for (Department e : objsList) pp.setDepartment(e); } if (pp.getDepartment() == null && isAutoCreate()) { Department d = new Department(); d.setCreatedBy(dashBean.getUser()); d.setCrt_dt(new Date()); d.setName(department); d.setPartner(getPartner()); ret = gDAO.save(d); if (ret) pp.setDepartment(d); else break; } else if (pp.getDepartment() == null && !isAutoCreate()) { ret = false; gDAO.setMessage( "Department: '" + department + "' does not exist for user: " + username); break; } } if (region != null && region.trim().length() > 0) { Query q = gDAO.createQuery( "Select e from Region e where e.partner = :partner and e.name = :name"); q.setParameter("partner", getPartner()); q.setParameter("name", region); Object objs = gDAO.search(q, 0); if (objs != null) { Vector<Region> objsList = (Vector<Region>) objs; for (Region e : objsList) pp.setRegion(e); } if (pp.getRegion() == null && isAutoCreate()) { Region r = new Region(); r.setCreatedBy(dashBean.getUser()); r.setCrt_dt(new Date()); r.setName(region); r.setPartner(getPartner()); ret = gDAO.save(r); if (ret) pp.setRegion(r); else break; } else if (pp.getRegion() == null && !isAutoCreate()) { ret = false; gDAO.setMessage("Region: '" + region + "' does not exist for user: " + username); break; } } MRole r = null; if (isAUser != null && isAUser.trim().equalsIgnoreCase("true")) { Query q = gDAO.createQuery( "Select e from MRole e where e.partner = :partner and e.name = :name"); q.setParameter("partner", getPartner()); q.setParameter("name", role); Object objs = gDAO.search(q, 0); if (objs != null) { Vector<MRole> objsList = (Vector<MRole>) objs; for (MRole e : objsList) r = e; } if (r == null) { ret = false; gDAO.setMessage("Role: '" + role + "' must exist for user: " + username); break; } } ret = gDAO.save(pp); if (!ret) break; else { if (isAUser != null && isAUser.trim().equalsIgnoreCase("true")) { if (username != null && username.trim().length() > 0 && password != null && password.trim().length() > 0 && role != null && role.trim().length() > 0) { if (password.equals(cpassword)) { PartnerUser pu = new PartnerUser(); pu.setActive(true); pu.setAdmin(false); pu.setCreatedBy(dashBean.getUser()); pu.setCrt_dt(new Date()); pu.setPartner(getPartner()); pu.setPartner_code(getPartner().getCode()); pu.setPassword(Hasher.getHashValue(password)); pu.setPersonel(pp); pu.setUsername(username); ret = gDAO.save(pu); if (ret) { if (r != null) { PartnerUserRole pur = new PartnerUserRole(); pur.setCreatedBy(dashBean.getUser()); pur.setCrt_dt(new Date()); pur.setDefaultRole(false); pur.setRole(r); pur.setUser(pu); ret = gDAO.save(pur); if (!ret) break; } else { ret = false; gDAO.setMessage("Role: '" + role + "' does not exist for user: " + username); break; } pp.setHasUser(true); ret = gDAO.update(pp); if (!ret) break; } else break; } else { ret = false; gDAO.setMessage("Passwords are not the same for user: " + username); break; } } else { ret = false; gDAO.setMessage("All fields are required to create a user account for user: " + username); break; } } if (isADriver != null && isADriver.trim().equalsIgnoreCase("true")) { PartnerDriver driver = new PartnerDriver(); driver.setActive(true); driver.setCreatedBy(dashBean.getUser()); driver.setCrt_dt(new Date()); driver.setPartner(getPartner()); driver.setPersonel(pp); driver.setDrvLicenseNo(driver_license_no); driver.setGuarantor(guarantor); if (driver_grade != null && driver_grade.trim().length() > 0) { Query q = gDAO.createQuery( "Select e from DriverGrade e where e.partner = :partner and e.name = :name"); q.setParameter("partner", getPartner()); q.setParameter("name", driver_grade); Object objs = gDAO.search(q, 0); if (objs != null) { Vector<DriverGrade> objsList = (Vector<DriverGrade>) objs; for (DriverGrade e : objsList) driver.setDriverGrade(e); } if (driver.getDriverGrade() == null && isAutoCreate()) { DriverGrade dg = new DriverGrade(); dg.setCreatedBy(dashBean.getUser()); dg.setCrt_dt(new Date()); dg.setName(region); dg.setPartner(getPartner()); ret = gDAO.save(dg); if (ret) driver.setDriverGrade(dg); else break; } else if (driver.getDriverGrade() == null && !isAutoCreate()) { ret = false; gDAO.setMessage("Driver grade: '" + driver_grade + "' does not exist for user: " + username); break; } } ret = gDAO.save(driver); if (!ret) break; else { pp.setHasDriver(true); ret = gDAO.update(pp); if (!ret) break; } if (driver_license_expiry_date != null) { Date expiryDt = null; SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); try { expiryDt = sdf.parse(driver_license_expiry_date); } catch (Exception ex) { } if (expiryDt != null) { DriverLicense dl = new DriverLicense(); dl.setDrvLicenseNo(driver_license_no); dl.setCreatedBy(dashBean.getUser()); dl.setCrt_dt(new Date()); dl.setLic_end_dt(expiryDt); boolean active = false, expired = false; if (expiryDt.after(new Date())) { active = true; } else { expired = true; } dl.setActive(active); dl.setExpired(expired); dl.setDriver(driver); ret = gDAO.save(dl); if (!ret) break; } } } } } else pos += 1; } if (ret) { gDAO.commit(); naration += " Status: Success"; msg = new FacesMessage(FacesMessage.SEVERITY_INFO, "Success: ", "All personel created successfully."); FacesContext.getCurrentInstance().addMessage(null, msg); setPersonels(null); setPersonelsWithoutUsers(null); } else { gDAO.rollback(); naration += " Status: Failed: " + gDAO.getMessage(); msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Failed: ", "Failed to create all personel. " + gDAO.getMessage()); FacesContext.getCurrentInstance().addMessage(null, msg); } } catch (Exception ex) { ex.printStackTrace(); msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error: ", "Severe error occured. " + ex.getMessage()); FacesContext.getCurrentInstance().addMessage(null, msg); } finally { gDAO.destroy(); dashBean.saveAudit(naration, "", null); } } }
From source file:com.dexter.fuelcard.mbean.UserMBean.java
@SuppressWarnings("unchecked") public void buckLoadVehicles() { AuditTrail audit = new AuditTrail(); audit.setPartner(getActiveUser().getPartner()); audit.setAuditTime(new java.util.Date()); audit.setActionPerformed("Batch loading vehicles..."); audit.setEntity("CAR"); audit.setUsername(getActiveUser().getUsername()); Vector<Car> loadedCars = new Vector<Car>(); try {// ww w . ja va 2 s .c o m //Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(getVehiclesFile().getInputstream()); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { //Get current sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(i); //Get iterator to all the rows in current sheet starting from row 2 Iterator<Row> rowIterator = sheet.iterator(); int pos = 1; // reading the contents // column 1 - zonControlId, 2 - regNumber, 3 - fuelType, 4 - username, 5 - cardPan, 6 - region, 7 - dept, 8 - make, 9 - type, 10 - model, 11 - year, 12 - kmpl, 13 - vehicleCapacity, 14 - calibratedCapacity while (rowIterator.hasNext()) { Row row = rowIterator.next(); String zonControlId = "", regNumber = "", fuelType = "", username = ""; String cardPan = "", region = "", dept = "", make = "", type = ""; String model = "", year = "", kmpl = "", vehicleCapacity = "", calibratedCapacity = ""; if (pos > 1) // skip the first row, should be headers { //Get iterator to all cells of current row Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String val = ""; switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: val = ""; break; case Cell.CELL_TYPE_BOOLEAN: val = "" + cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_ERROR: val = ""; break; case Cell.CELL_TYPE_NUMERIC: val = "" + cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: val = cell.getStringCellValue(); break; default: { try { val = cell.getStringCellValue(); } catch (Exception ex) { } break; } } switch (cell.getColumnIndex()) { case 0: zonControlId = val; break; case 1: regNumber = val; break; case 2: fuelType = val; break; case 3: username = val; break; case 4: cardPan = val; break; case 5: region = val; break; case 6: dept = val; break; case 7: make = val; break; case 8: type = val; break; case 9: model = val; break; case 10: year = val; break; case 11: kmpl = val; break; case 12: vehicleCapacity = val; break; case 13: calibratedCapacity = val; break; } } try { Car e = new Car(); e.setCalibratedCapacity( (calibratedCapacity != null) ? Double.parseDouble(calibratedCapacity) : 0); e.setCardPan(cardPan); e.setFuelType(fuelType); e.setPartner(getActiveUser().getPartner()); e.setRegNumber(regNumber); try { e.setVehicleCapacity( (vehicleCapacity != null) ? Double.parseDouble(vehicleCapacity) : 0); } catch (Exception ex) { } try { e.setZonControlId((zonControlId != null) ? Integer.parseInt(zonControlId) : 0); } catch (Exception ex) { } if (username != null) { User u = new UserDAO().getUserByUsername(username, getActiveUser().getPartner().getCode()); if (u != null) { e.setAssigned(true); e.setAssignedUser(u); } } if (region != null) { RegionDAO regDAO = new RegionDAO(); Region reg = regDAO.getRegionByName(region); if (reg == null) { reg = new Region(); reg.setName(region); reg.setPartner(getActiveUser().getPartner()); regDAO.save(reg); } e.setRegion(reg); } if (dept != null) { DepartmentDAO dDAO = new DepartmentDAO(); Department d = dDAO.getDepartmentByName(dept); if (d == null) { d = new Department(); d.setName(dept); d.setPartner(getActiveUser().getPartner()); dDAO.save(d); } e.setDepartment(d); } if (make != null && type != null && model != null) { VehicleMake m = null; VehicleType t = null; VehicleModel md = null; GeneralDAO gDAO = new GeneralDAO(); Hashtable<String, Object> params = new Hashtable<String, Object>(); params.put("name", make); params.put("partner", getActiveUser().getPartner()); Object retObj = gDAO.search("VehicleMake", params); if (retObj != null) { Vector<VehicleMake> retList = (Vector<VehicleMake>) retObj; for (VehicleMake ret : retList) m = ret; } if (m == null) { m = new VehicleMake(); m.setName(make); m.setPartner(getActiveUser().getPartner()); new PlainDAO().save(m); } params = new Hashtable<String, Object>(); params.put("name", type); params.put("partner", getActiveUser().getPartner()); retObj = gDAO.search("VehicleType", params); if (retObj != null) { Vector<VehicleType> retList = (Vector<VehicleType>) retObj; for (VehicleType ret : retList) t = ret; } if (t == null) { t = new VehicleType(); t.setName(type); t.setPartner(getActiveUser().getPartner()); new PlainDAO().save(t); } params = new Hashtable<String, Object>(); params.put("name", model); params.put("make", m); params.put("type", t); params.put("year", year); params.put("partner", getActiveUser().getPartner()); retObj = gDAO.search("VehicleModel", params); if (retObj != null) { Vector<VehicleModel> retList = (Vector<VehicleModel>) retObj; for (VehicleModel ret : retList) md = ret; } if (md == null) { md = new VehicleModel(); md.setName(model); try { md.setKmpl((kmpl != null && kmpl.trim().length() > 0) ? Double.parseDouble(kmpl) : 0); } catch (Exception ex) { } md.setMake(m); md.setType(t); md.setYear(year); md.setPartner(getActiveUser().getPartner()); new PlainDAO().save(md); } e.setModel(md); } loadedCars.add(e); } catch (Exception ig) { } } else pos += 1; } } setAllusers(null); } catch (Exception ex) { ex.printStackTrace(); } audit.setActionPerformed(audit.getActionPerformed() + " Loaded: " + loadedCars.size()); int success = 0, failed = 0; CarDAO cDAO = new CarDAO(); for (Car e : loadedCars) { if (cDAO.createCar(e)) { success += 1; if (e.getCardPan() != null) { Card card = new Card(); card.setCardPan(getVehicle().getCardPan()); card.setCrt_dt(new Date()); new PlainDAO().save(card); } } else failed += 1; } audit.setActionPerformed(audit.getActionPerformed() + " Success: " + success + ", Failed: " + failed); FacesContext curContext = FacesContextImpl.getCurrentInstance(); curContext.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_INFO, "Done", "Loaded: " + loadedCars.size() + ", Success: " + success + ", Failed: " + failed)); new AuditDAO().save(audit); setVehicles(null); }
From source file:com.dexter.fuelcard.mbean.UserMBean.java
public void buckLoadUsers() { AuditTrail audit = new AuditTrail(); audit.setPartner(getActiveUser().getPartner()); audit.setAuditTime(new java.util.Date()); audit.setActionPerformed("Batch loading users..."); audit.setEntity("USER"); audit.setUsername(getActiveUser().getUsername()); Vector<User> loadedUsers = new Vector<User>(); try {//from w w w .ja v a 2 s.com /*InputStream inputstream = getUploadItem().getInputstream(); ByteArrayOutputStream fos = new ByteArrayOutputStream(); int read = 0; byte[] bytes = new byte[1024]; while ((read = inputstream.read(bytes)) != -1) { fos.write(bytes, 0, read); } fos.close(); ByteArrayInputStream byteIn = new ByteArrayInputStream(fos.toByteArray());*/ //Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(getUploadItem().getInputstream()); RoleDAO rDAO = new RoleDAO(); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { //Get current sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(i); //Get iterator to all the rows in current sheet starting from row 2 Iterator<Row> rowIterator = sheet.iterator(); int pos = 1; // reading the contents // column 1 - username, 2 - password, 3 - full name, 4 - role id, 5 - email, 6 - mobile number, 7 - Vehicle Reg Number while (rowIterator.hasNext()) { Row row = rowIterator.next(); String username = "", password = "", fullname = "", email = "", mobile = "", regNumber = ""; long role_id = 0L; if (pos > 1) // skip the first row, should be headers { //Get iterator to all cells of current row Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String val = ""; switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: val = ""; break; case Cell.CELL_TYPE_BOOLEAN: val = "" + cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_ERROR: val = ""; break; case Cell.CELL_TYPE_NUMERIC: val = "" + cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: val = cell.getStringCellValue(); break; default: { try { val = cell.getStringCellValue(); } catch (Exception ex) { } break; } } switch (cell.getColumnIndex()) { case 0: username = val; break; case 1: password = val; break; case 2: fullname = val; break; case 3: try { role_id = Long.parseLong(val); } catch (Exception ex) { ex.printStackTrace(); } break; case 4: email = val; break; case 5: mobile = val; break; case 6: regNumber = val; break; } } try { User e = new User(); e.setUsername(username); e.setPassword(password); e.setFullname(fullname); e.setEmail(email); e.setMobileNumber(mobile); e.setActive(true); e.setRegNumber(regNumber); Role role = rDAO.getRoleById(role_id); if (role != null) { e.setRole(role); loadedUsers.add(e); } } catch (Exception ig) { } } else pos += 1; } } setAllusers(null); } catch (Exception ex) { ex.printStackTrace(); } audit.setActionPerformed(audit.getActionPerformed() + " Loaded: " + loadedUsers.size()); int success = 0, failed = 0; UserDAO uDAO = new UserDAO(); for (User e : loadedUsers) { String ret = uDAO.createUser(e); if (ret.indexOf("Success") >= 0) { success += 1; if (e.getRegNumber() != null) { CarDAO cDAO = new CarDAO(); Car c = cDAO.getCarByRegNumber(e.getRegNumber()); if (c != null) { c.setAssigned(true); c.setAssignedUser(e); cDAO.updateCar(c); } } } else failed += 1; } audit.setActionPerformed(audit.getActionPerformed() + " Success: " + success + ", Failed: " + failed); FacesContext curContext = FacesContextImpl.getCurrentInstance(); curContext.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_INFO, "Done", "Loaded: " + loadedUsers.size() + ", Success: " + success + ", Failed: " + failed)); new AuditDAO().save(audit); setAllusers(null); }
From source file:com.dfpray.formatter.CardModel.java
/** * Imports a .xlsx file and create a CardModel form it * @param path Path to file//w w w .ja va 2s . com * @throws IOException */ public void importFromExcel(String path) throws IOException { String[] cardInfo = new String[32]; FileInputStream file = new FileInputStream(new File(path)); @SuppressWarnings("resource") XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); Row row; Cell cell; Iterator<Cell> cellIterator; Iterator<Row> rowIterator; int i; //Iterate through each rows one by one rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { row = rowIterator.next(); cellIterator = row.cellIterator(); i = 0; while (cellIterator.hasNext()) { cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: cardInfo[i] = " "; //System.out.println("Nothing"); break; case Cell.CELL_TYPE_NUMERIC: cardInfo[i] = (Double.toString(cell.getNumericCellValue())); //System.out.println(Double.toString(cell.getNumericCellValue())); break; case Cell.CELL_TYPE_STRING: cardInfo[i] = cell.getStringCellValue(); //System.out.println(cell.getStringCellValue()); break; } i++; } //Create card and add it tho this cards.add(new BusinessCard(cardInfo)); } file.close(); }
From source file:com.ebay.xcelite.reader.BeanSheetReader.java
License:Apache License
private boolean isBlankRow(Row row) { Iterator<Cell> cellIterator = row.cellIterator(); boolean blankRow = true; while (cellIterator.hasNext()) { Object value = readValueFromCell(cellIterator.next()); if (blankRow && value != null && !String.valueOf(value).isEmpty()) { blankRow = false;/*from www . jav a 2 s . c o m*/ } } return blankRow; }
From source file:com.ebay.xcelite.reader.BeanSheetReader.java
License:Apache License
private void buildHeader() { header = Sets.newLinkedHashSet();//from w w w . j a va 2 s .c o m rowIterator = sheet.getNativeSheet().rowIterator(); Row row = rowIterator.next(); if (row == null) { throw new XceliteException("First row in sheet is empty. First row must contain header"); } Iterator<Cell> itr = row.cellIterator(); while (itr.hasNext()) { Cell cell = itr.next(); header.add(cell.getStringCellValue()); } }
From source file:com.epitech.oliver_f.astextexls.ReadXLSFiles.java
private List<ResultRow> parseAllFiles(List<Path> paths) { List<ResultRow> resultList = new ArrayList<ResultRow>(); for (Path path : paths) { try {//from w ww.ja va2 s .c o m System.out.println("file : " + path.toAbsolutePath()); FileInputStream file = new FileInputStream(path.toFile()); Workbook wb = WorkbookFactory.create(file); Sheet sheet = wb.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); boolean found = false; while (rowIterator.hasNext()) { Row row = rowIterator.next(); //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 String res = null; if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { double inte = cell.getNumericCellValue(); res = Double.toString(inte); } if (cell.getCellType() == Cell.CELL_TYPE_STRING) { res = cell.getStringCellValue(); } if (res != null && res.trim().toLowerCase().equals("login \nvaluateur")) { found = true; } } if (found) { System.out.println("found ! "); ResultRow rr = new ResultRow(); Row rowFound = rowIterator.next(); Iterator<Cell> c = rowFound.cellIterator(); while (c.hasNext()) { Cell cel = c.next(); String res = null; if (cel.getCellType() == Cell.CELL_TYPE_NUMERIC) { double inte = cel.getNumericCellValue(); res = Double.toString(inte); } if (cel.getCellType() == Cell.CELL_TYPE_STRING) { res = cel.getStringCellValue(); } rr.result.add(res); } resultList.add(rr); found = false; break; } } file.close(); } catch (IOException | InvalidFormatException e) { e.printStackTrace(); } } return resultList; }
From source file:com.epitech.oliver_f.astextexls.WriteXLSFile.java
public void write() { FileInputStream file = null;/* ww w .j a va 2s. co m*/ try { file = new FileInputStream(pathToFile); Workbook wb = WorkbookFactory.create(file); Sheet sheet = wb.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); int i = 0; int listIndex = 0; while (rowIterator.hasNext() && listIndex < results.size()) { Row row = rowIterator.next(); if (i > 1) { Iterator<Cell> cellIterator = row.cellIterator(); int cellIndex = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String r = results.get(listIndex).result.get(cellIndex); try { if (r == null) throw new NumberFormatException(); Double resDouble = Double.parseDouble(r); Integer resInt = resDouble.intValue(); cell.setCellValue(resInt.toString()); } catch (NumberFormatException e) { cell.setCellValue(results.get(listIndex).result.get(cellIndex)); } cellIndex++; } listIndex++; } i++; } System.out.println("listindex " + listIndex); file.close(); FileOutputStream outFile = new FileOutputStream(new File(pathToFile)); wb.write(outFile); outFile.close(); } catch (FileNotFoundException ex) { Logger.getLogger(WriteXLSFile.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException | InvalidFormatException ex) { Logger.getLogger(WriteXLSFile.class.getName()).log(Level.SEVERE, null, ex); } finally { try { file.close(); } catch (IOException ex) { Logger.getLogger(WriteXLSFile.class.getName()).log(Level.SEVERE, null, ex); } } }
From source file:com.Excel.Excel.java
private void leerArchivo(int indiceHoja) { abrirArchivo();// w w w . j ava 2 s.c o m this.datos = new HashMap<>(); HSSFSheet sheet = workbook.getSheetAt(indiceHoja); Iterator<Row> rowIterator = sheet.iterator(); int fila = 0; Row row; while (rowIterator.hasNext()) { List<Object> datosFila = new ArrayList<>(); Cell celda; row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Object dato = null; celda = cellIterator.next(); switch (celda.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(celda)) { System.out.print(celda.getDateCellValue()); dato = celda.getDateCellValue(); } else { System.out.print(celda.getNumericCellValue()); dato = celda.getNumericCellValue(); } break; case Cell.CELL_TYPE_STRING: System.out.print(celda.getStringCellValue()); dato = celda.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: System.out.print(celda.getBooleanCellValue()); dato = celda.getBooleanCellValue(); break; } datosFila.add(dato); } datos.put(fila++, datosFila); System.out.println(""); } cerrarArchivo(); }
From source file:com.Excel.Excel2007.java
private void leerArchivo(int indiceHoja) { abrirArchivo();//from w ww. j a v a2 s . c om this.datos = new HashMap<>(); XSSFSheet sheet = workbook.getSheetAt(indiceHoja); Iterator<Row> rowIterator = sheet.iterator(); int fila = 0; Row row; while (rowIterator.hasNext()) { List<Object> datosFila = new ArrayList<>(); Cell celda; row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Object dato = null; celda = cellIterator.next(); switch (celda.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(celda)) { System.out.print(celda.getDateCellValue()); dato = celda.getDateCellValue(); } else { System.out.print(celda.getNumericCellValue()); dato = celda.getNumericCellValue(); } break; case Cell.CELL_TYPE_STRING: System.out.print(celda.getStringCellValue()); dato = celda.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: System.out.print(celda.getBooleanCellValue()); dato = celda.getBooleanCellValue(); break; } datosFila.add(dato); } datos.put(fila++, datosFila); System.out.println(""); } cerrarArchivo(); }