List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getPhysicalNumberOfRows
@Override public int getPhysicalNumberOfRows()
From source file:com.sandstone_tech.lendfastselenium2.util.PoiFileReader.java
/** * Extracts the content of the excel file to be converted as * {@link TestDataModel} object usable by the data provider * /*from w w w. j a v a 2s . c om*/ * @param fileName * @return */ protected Object[][] readExcelFileAsTDM(String fileName) { try { XSSFWorkbook wb = new XSSFWorkbook(FileUtils.openInputStream(new File(fileName))); // CONFIGURATION // XSSFSheet sheet = wb.getSheetAt(0); // DATA XSSFSheet dataSheet = wb.getSheetAt(1); if (dataSheet != null && dataSheet.getPhysicalNumberOfRows() > 1) { Object[][] testData = new Object[dataSheet.getPhysicalNumberOfRows() - 2][1]; for (int rowCount = 2; rowCount < dataSheet.getPhysicalNumberOfRows(); rowCount++) { XSSFRow row = dataSheet.getRow(rowCount); TestDataModel testDataModel = new TestDataModel(); testDataModel.setUsername(getStringValue(row.getCell(0))); testDataModel.setPassword(getStringValue(row.getCell(1))); testDataModel.setApplicationType(getStringValue(row.getCell(2))); testDataModel.setOrginationFormModel(this.getOrigination(row)); testDataModel.setPersonalDetailsModel(this.getPersonalDetail(row)); testDataModel.setSignedDeclaration(this.getSignedDeclaration(row)); testDataModel.setCurrentAddress(this.getCurrentAddress(row)); testDataModel.setEmploymentDetails(this.getEmploymentDetails(row)); testDataModel.setPreviousEmploymentDetails(this.getPreviousEmploymentDetails(row)); testDataModel.setPurchaseProperty( new PurchaseProperty(this.getPropertyContract(row), this.getPropertyPurpose(row))); testData[rowCount - 2][0] = testDataModel; } wb.close(); return testData; } wb.close(); } catch (IOException e) { e.printStackTrace(); } return null; }
From source file:controller.ExcelConsultant.java
static ArrayList<String> getUnrepeatableDataOfACollum(File inputFile, int col) { ArrayList<String> collection = new ArrayList<>(); try {/* w w w .ja va2s . c o m*/ FileInputStream fis = new FileInputStream(inputFile); XSSFWorkbook myWorkBook = new XSSFWorkbook(fis); XSSFSheet sheet = myWorkBook.getSheetAt(0); int nRows = sheet.getPhysicalNumberOfRows(); for (int i = 1; i < nRows; i++) { String item = sheet.getRow(i).getCell(col).getStringCellValue(); String delim = " "; String[] separatedWords = item.split(delim); for (String word : separatedWords) { word = word.replaceAll(",", ""); word = word.replaceAll(" ", ""); if (!collection.contains(word)) { collection.add(word); } } } } catch (Exception e) { e.printStackTrace(); } collection.sort(null); return collection; }
From source file:controller.ExcelConsultant.java
public static ArrayList<Result> makeConsult(File inputFile, String trade, String area) { ArrayList<Result> result = new ArrayList<>(); try {/*w ww . j a v a 2 s . c om*/ FileInputStream fis = new FileInputStream(inputFile); XSSFWorkbook myWorkBook = new XSSFWorkbook(fis); XSSFSheet sheet = myWorkBook.getSheetAt(0); int nRows = sheet.getPhysicalNumberOfRows(); for (int i = 1; i < nRows; i++) { Row actualRow = sheet.getRow(i); if (actualRow.getCell(1).getStringCellValue().contains(trade) && actualRow.getCell(5).getStringCellValue().contains(area)) { String name = actualRow.getCell(0).getStringCellValue(); String tradeName = actualRow.getCell(2).getStringCellValue(); String person = actualRow.getCell(3).getStringCellValue(); String number = actualRow.getCell(4).getStringCellValue(); String notes = actualRow.getCell(6).getStringCellValue(); Result r = new Result(name, tradeName, person, number, notes); result.add(r); } } } catch (Exception e) { e.printStackTrace(); } return result; }
From source file:controller.ExcelConsultant.java
static void logResult(ArrayList<Result> result, File outputFile, String name) { try {// w w w . java 2s.c om FileInputStream fis; fis = new FileInputStream(outputFile); XSSFWorkbook myWorkBook = new XSSFWorkbook(fis); XSSFSheet sheet = myWorkBook.getSheetAt(0); int nRow = sheet.getPhysicalNumberOfRows(); if (nRow == 0) { sheet.createRow(nRow); sheet.getRow(nRow).createCell(0).setCellValue("Timestamp"); sheet.getRow(nRow).createCell(1).setCellValue("Name"); sheet.getRow(nRow).createCell(2).setCellValue("Trade Name"); sheet.getRow(nRow).createCell(3).setCellValue("Number"); sheet.getRow(nRow).createCell(4).setCellValue("Person"); sheet.getRow(nRow).createCell(5).setCellValue("Notes"); nRow++; } for (Result res : result) { sheet.createRow(nRow); Date date = new Date(); sheet.getRow(nRow).createCell(0).setCellValue(date.toString()); sheet.getRow(nRow).createCell(1).setCellValue(name); sheet.getRow(nRow).createCell(2).setCellValue(res.getTradeName()); sheet.getRow(nRow).createCell(3).setCellValue(res.getNumber()); sheet.getRow(nRow).createCell(4).setCellValue(res.getPerson()); sheet.getRow(nRow).createCell(5).setCellValue(res.getNotes()); nRow++; } //important to close InputStream fis.close(); //Open FileOutputStream to write updates FileOutputStream output_file = new FileOutputStream(outputFile); //write changes myWorkBook.write(output_file); //close the stream output_file.close(); } catch (Exception ex) { ex.printStackTrace(); } }
From source file:Creator.TaskManagerPanel.java
private void loadDefaultTasks() { String path = "/Creator/textFiles/tasks.xlsx"; InputStream loc = this.getClass().getResourceAsStream(path); importedTasks = new ArrayList<>(); try {/*w w w. j av a 2 s. c om*/ XSSFWorkbook wb = new XSSFWorkbook(loc); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row; XSSFCell cell; String[] rowData; int rows, cols; // No of rows rows = sheet.getPhysicalNumberOfRows(); for (int i = 1; i < rows; i++) { row = sheet.getRow(i); if (row != null) { cols = row.getPhysicalNumberOfCells(); rowData = new String[cols]; for (int j = 0; j < cols; j++) { cell = row.getCell(j); if (cell != null) { switch (cell.getCellType()) { case 1: // string rowData[j] = cell.getStringCellValue(); break; case 2: // int rowData[j] = String.valueOf(cell.getNumericCellValue()); break; case 3: // blank System.out.println("Blank data @ [" + i + "][" + j + "]"); rowData[j] = "no data @ [" + i + "][" + j + "]"; break; case 4: // boolean rowData[j] = String.valueOf(cell.getBooleanCellValue()); break; case 5: // error rowData[j] = String.valueOf(cell.getErrorCellString()); break; default: System.out.println("default @ [" + i + "][" + j + "]"); rowData[j] = "default @ [" + i + "][" + j + "]"; break; } } else { System.out.println("null @ [" + i + "][" + j + "]"); rowData[j] = "nullValue @ [" + i + "][" + j + "]"; } } rowData[5] = "'" + rowData[5] + "'"; importedTasks.add(rowData); } } wb.close(); } catch (Exception e) { System.out.println("Error reading excel file " + e.getMessage()); } }
From source file:Creator.TaskManagerPanel.java
private void loadDefaultAlerts() { String path = "/Creator/textFiles/alerts.xlsx"; InputStream loc = this.getClass().getResourceAsStream(path); importedAlerts = new ArrayList<>(); try {/*from w w w . java 2 s .c om*/ XSSFWorkbook wb = new XSSFWorkbook(loc); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row; XSSFCell cell; String[] rowData; int rows, cols; // No of rows rows = sheet.getPhysicalNumberOfRows(); for (int i = 1; i < rows; i++) { row = sheet.getRow(i); if (row != null) { cols = row.getPhysicalNumberOfCells(); rowData = new String[cols]; for (int j = 0; j < cols; j++) { cell = row.getCell(j); if (cell != null) { switch (cell.getCellType()) { case 1: // string rowData[j] = cell.getStringCellValue(); break; case 2: // int rowData[j] = String.valueOf(cell.getNumericCellValue()); break; case 3: // blank System.out.println("Blank data @ [" + i + "][" + j + "]"); rowData[j] = "no data @ [" + i + "][" + j + "]"; break; case 4: // boolean rowData[j] = String.valueOf(cell.getBooleanCellValue()); break; case 5: // error rowData[j] = String.valueOf(cell.getErrorCellString()); break; default: //System.out.println("default @ [" + i + "][" + j + "] = " + String.valueOf(cell.getRawValue())); rowData[j] = String.valueOf(cell.getRawValue()); break; } } else { System.out.println("null @ [" + i + "][" + j + "]"); rowData[j] = "nullValue @ [" + i + "][" + j + "]"; } } importedAlerts.add(rowData); } } wb.close(); } catch (Exception e) { System.out.println("Error reading excel file " + e.getMessage()); } }
From source file:db.pj.util.excel.ApplicationImporter.java
@Override public List<String> importExcel() { // get workbook FileInputStream inputStream = null; XSSFWorkbook workbook = null;//from www . j a va 2 s .c o m List<String> result = new ArrayList<String>(); result.add(""); try { inputStream = new FileInputStream(file); workbook = new XSSFWorkbook(inputStream); } catch (Exception e) { result.set(0, ""); return result; } int success = 0, fail = 0; // number of tuples UserDao userDao = DaoFactory.getUserDao(); LicenseDao licenseDao = DaoFactory.getLicenseDao(); XSSFSheet sheet = workbook.getSheetAt(0); XSSFRow row = null; XSSFCell cell = null; int rows = sheet.getPhysicalNumberOfRows(); for (int i = 1; i < rows; i++) { row = sheet.getRow(i); if (row == null) continue; int cells = row.getPhysicalNumberOfCells(); String[] values = new String[cells]; // read data to an array of strings for (short j = 0; j < cells; j++) { cell = row.getCell(j); if (cell == null) values[j] = null; else { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { double d = cell.getNumericCellValue(); Date date = new Date(DateUtil.getJavaDate(d).getTime()); values[j] = date.toString(); } else { values[j] = (int) (cell.getNumericCellValue()) + ""; } break; default: values[j] = cell.getStringCellValue(); break; } } } License license = null; User user = userDao.queryUserByIC(Integer.parseInt(values[0]), values[1]); if (user != null) license = licenseDao.queryLicenseByUserID(user.getUserID()); if (user == null) { user = new User(); user.setUserID(IDGenerator.generateUserID(values[0] + values[1])); user.setUserPwd(StringUtil.MD5(user.getUserID())); user.setUserName(values[2]); user.setUserGender(XMLRepertory.genderStr2Num(values[3])); user.setUserIctype(Integer.parseInt(values[0])); user.setUserIcno(values[1]); user.setUserBdate(MyDateUtil.str2Date(values[4])); user.setUserPhone("00000000"); user.setUserNation(values[5]); user.setUserAddr(values[6]); user.setUserHead("user/default.png"); user.setUserCensor(values[9]); user.setUserHealth(Integer.parseInt(values[7])); user.setUserPdate(MyDateUtil.str2Date(values[8])); // check health and age boolean ok = (user.getUserHealth() == 0) && (user.getAge(user.getUserPdate()) >= 18) && (user.getAge(user.getUserPdate()) <= 70); user.setUserState(ok ? 1 : 0); boolean re = userDao.addUser(user); if (re) success++; else { fail++; String ic = (user.getUserIctype() == 1 ? "" : "") + user.getUserIcno(); result.add("" + ic + ""); } } else if (license == null) { user.setUserCensor(values[9]); user.setUserHealth(Integer.parseInt(values[7])); user.setUserPdate(MyDateUtil.str2Date(values[8])); // check health and age boolean ok = (user.getUserHealth() == 0) && (user.getAge(user.getUserPdate()) >= 18) && (user.getAge(user.getUserPdate()) <= 70); user.setUserState(ok ? 1 : 0); boolean re = userDao.updateUser(user); if (re) success++; else { fail++; String ic = (user.getUserIctype() == 1 ? "" : "") + user.getUserIcno(); result.add("" + ic + ""); } } else { // user has a license if (license.getLicnValid() == 0) { boolean nolimit = license.getLicnVinfo() == 12 && license.getLicnLimit() == null; boolean outlimit = license.getLicnLimit() != null && (license.getLicnLimit().getTime() - MyDateUtil.str2Date(values[8]).getTime() <= 0); if (nolimit || outlimit) { user.setUserCensor(values[9]); user.setUserHealth(Integer.parseInt(values[7])); user.setUserPdate(MyDateUtil.str2Date(values[8])); // check health and age boolean ok = (user.getUserHealth() == 0) && (user.getAge(user.getUserPdate()) >= 18) && (user.getAge(user.getUserPdate()) <= 70); user.setUserState(ok ? 1 : 0); boolean re = userDao.updateUser(user); if (re) success++; else { fail++; String ic = (user.getUserIctype() == 1 ? "" : "") + user.getUserIcno(); result.add("" + ic + ""); } } else { fail++; String ic = (user.getUserIctype() == 1 ? "" : "") + user.getUserIcno(); result.add(ic + ""); } } else { fail++; String ic = (user.getUserIctype() == 1 ? "" : "") + user.getUserIcno(); result.add(ic + ""); } } } result.set(0, "" + success + "" + fail + ""); return result; }
From source file:db.pj.util.excel.LicenseImporter.java
License:Open Source License
@Override public List<String> importExcel() { // get workbook FileInputStream inputStream = null; XSSFWorkbook workbook = null;/* ww w . j a va2s .c o m*/ List<String> result = new ArrayList<String>(); result.add(""); try { inputStream = new FileInputStream(file); workbook = new XSSFWorkbook(inputStream); } catch (Exception e) { result.set(0, ""); return result; } int success = 0, fail = 0; // number of tuples UserDao userDao = DaoFactory.getUserDao(); LicenseDao licenseDao = DaoFactory.getLicenseDao(); AdministrationDao administrationDao = DaoFactory.getAdministrationDao(); XSSFSheet sheet = workbook.getSheetAt(0); XSSFRow row = null; XSSFCell cell = null; int rows = sheet.getPhysicalNumberOfRows(); for (int i = 1; i < rows; i++) { row = sheet.getRow(i); if (row == null) continue; int cells = row.getPhysicalNumberOfCells(); String[] values = new String[cells]; // read data to an array of strings for (short j = 0; j < cells; j++) { cell = row.getCell(j); if (cell == null) values[j] = null; else { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { double d = cell.getNumericCellValue(); Date date = new Date(DateUtil.getJavaDate(d).getTime()); values[j] = date.toString(); } else { values[j] = (int) (cell.getNumericCellValue()) + ""; } break; default: values[j] = cell.getStringCellValue(); break; } } } User user = new User(); user.setUserID(IDGenerator.generateUserID(values[5] + values[6])); user.setUserPwd(StringUtil.MD5(user.getUserID())); user.setUserName(values[0]); user.setUserGender(XMLRepertory.genderStr2Num(values[1])); user.setUserIctype(Integer.parseInt(values[5])); user.setUserIcno(values[6]); user.setUserBdate(MyDateUtil.str2Date(values[2])); user.setUserPhone("00000000"); user.setUserNation(values[3]); user.setUserAddr(values[4]); user.setUserHead("user/default.png"); user.setUserHealth(0); user.setUserState(5); user.setUserPdate(new Date(System.currentTimeMillis())); License license = new License(); license.setLicnID(values[11]); license.setLicnType(values[8]); license.setLicnGdate(MyDateUtil.str2Date(values[7])); license.setLicnVdlen(Integer.parseInt(values[9])); // if point>=12, set invalid license.setLicnVinfo(Integer.parseInt(values[12])); license.setLicnValid((license.getLicnVinfo() < 12) ? 1 : 0); // license is not valid, reset health and state if (license.getLicnValid() == 0) { user.setUserHealth(-1); user.setUserState(0); } user.setLicense(license); license.setUser(user); // get administration and check whether it is valid Administration administration = administrationDao.queryAdministrationByID(values[10]); if (administration == null) { fail++; result.add("" + values[11] + ""); } else { license.setAdministration(administration); boolean re1 = userDao.addUser(user); boolean re2 = licenseDao.addLicense(license); if (re1 || re2) success++; else { fail++; result.add("" + values[11] + ""); } } } result.set(0, "" + success + "" + fail + ""); return result; }
From source file:db.pj.util.excel.PenaltyImporter.java
@Override public List<String> importExcel() { // get workbook FileInputStream inputStream = null; XSSFWorkbook workbook = null;//from www .j a v a 2s . c om List<String> result = new ArrayList<String>(); result.add(""); try { inputStream = new FileInputStream(file); workbook = new XSSFWorkbook(inputStream); } catch (Exception e) { result.set(0, ""); return result; } int success = 0, fail = 0; // number of tuples LicenseDao licenseDao = DaoFactory.getLicenseDao(); PenaltyDao penaltyDao = DaoFactory.getPenaltyDao(); XSSFSheet sheet = workbook.getSheetAt(0); XSSFRow row = null; XSSFCell cell = null; int rows = sheet.getPhysicalNumberOfRows(); int cells = sheet.getRow(0).getPhysicalNumberOfCells(); for (int i = 1; i < rows; i++) { row = sheet.getRow(i); if (row == null) continue; String[] values = new String[cells]; // read data to an array of strings for (short j = 0; j < cells; j++) { cell = row.getCell(j); if (cell == null) values[j] = null; else { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { double d = cell.getNumericCellValue(); Date date = new Date(DateUtil.getJavaDate(d).getTime()); values[j] = date.toString(); } else { values[j] = (int) (cell.getNumericCellValue()) + ""; } break; default: values[j] = cell.getStringCellValue(); break; } } } License license = licenseDao.queryLicenseByUserID(values[0]); if (license == null) { fail++; result.add("" + values[0] + "," + values[1] + "," + values[2] + ""); } else { Penalty penalty = new Penalty(); penalty.setPnltID(IDGenerator.generateUUID()); penalty.setPnltType(Integer.parseInt(values[3])); penalty.setPnltTime(MyDateUtil.str2Date(values[1])); penalty.setPnltInfo(values[2]); // set penalty point and limit if (penalty.getPnltType() == 5) { penalty.setPnltPoint(Integer.parseInt(values[5])); license.addPnltPoint(penalty.getPnltPoint()); } else { penalty.setPnltPoint(0); license.setLicnValid(0); if (penalty.getPnltType() == 3) license.setLicnLimit(MyDateUtil.getLimitDate(penalty.getPnltTime(), 100)); else license.setLicnLimit( MyDateUtil.getLimitDate(penalty.getPnltTime(), Integer.parseInt(values[4]))); } boolean re1 = licenseDao.updateLicense(license); penalty.setLicense(license); boolean re2 = penaltyDao.addPenalty(penalty); if (re1 && re2) success++; else { fail++; result.add("" + values[0] + "," + values[1] + "," + values[2] + ""); } } } result.set(0, "" + success + "" + fail + ""); return result; }
From source file:db.pj.util.excel.Phase4GradeImporter.java
@Override public List<String> importExcel() { // get workbook FileInputStream inputStream = null; XSSFWorkbook workbook = null;/*www .j a v a2s. c om*/ List<String> result = new ArrayList<String>(); result.add(""); try { inputStream = new FileInputStream(file); workbook = new XSSFWorkbook(inputStream); } catch (Exception e) { result.set(0, ""); return result; } int success = 0, fail = 0; // number of tuples UserDao userDao = DaoFactory.getUserDao(); StaffDao staffDao = DaoFactory.getStaffDao(); ScheduleDao scheduleDao = DaoFactory.getScheduleDao(); ExaminationDao examinationDao = DaoFactory.getExaminationDao(); LicenseDao licenseDao = DaoFactory.getLicenseDao(); WarningDao warningDao = DaoFactory.getWarningDao(); XSSFSheet sheet = workbook.getSheetAt(0); XSSFRow row = null; XSSFCell cell = null; int rows = sheet.getPhysicalNumberOfRows(); int cells = sheet.getRow(0).getPhysicalNumberOfCells(); for (int i = 1; i < rows; i++) { row = sheet.getRow(i); if (row == null) continue; String[] values = new String[cells]; // read data to an array of strings for (short j = 0; j < cells; j++) { cell = row.getCell(j); if (cell == null) values[j] = null; else { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: values[j] = (int) (cell.getNumericCellValue()) + ""; break; default: values[j] = cell.getStringCellValue(); break; } } } User user = userDao.queryUserByIC(Integer.parseInt(values[0]), values[1]); Schedule schedule = scheduleDao.queryScheduleByID(values[3]); Examination examination = null; if (user == null) { fail++; String ic = (Integer.parseInt(values[0]) == 1 ? "" : "") + values[1] + ""; result.add("" + ic + "" + values[3] + ""); } else if (schedule == null) { fail++; String ic = (Integer.parseInt(values[0]) == 1 ? "" : "") + values[1] + ""; result.add( "" + ic + "" + values[3] + ""); } else { examination = examinationDao.queryExaminationByUserAndSchedule(user, schedule); if (examination == null) { fail++; String ic = (Integer.parseInt(values[0]) == 1 ? "" : "") + values[1] + ""; result.add("" + ic + "" + values[3] + ""); Warning warning = new Warning(); warning.setWarnID(IDGenerator.generateUUID()); warning.setUser(user); warning.setSchedule(schedule); warning.setWarnCont(StringUtil.buildPhase14Warning(values)); warningDao.addWarning(warning); } else { examination.setStaff(staffDao.queryStaffByStaffID(values[2])); examination.setExamScore1(Integer.parseInt(values[4])); examination.setExamPass(examination.getExamScore1() >= 60 ? 1 : 0); examination.setExamFinfo(StringUtil.buildGradeFinfo(values[5])); if (examination.getExamPass() == 1 && user.getUserState() == 4) { boolean re = examinationDao.updateExamination(examination); if (re) success++; else { fail++; String ic = (Integer.parseInt(values[0]) == 1 ? "" : "") + values[1] + ""; result.add("" + ic + "" + values[3] + ""); } user.setUserState(5); License license = licenseDao.queryLicenseByUserID(user.getUserID()); if (license == null) { License newLicn = new License(); newLicn.setLicnID(IDGenerator.generateLicenseID(user.getUserID())); newLicn.setLicnType("C1"); newLicn.setLicnGdate(schedule.getSchdDate()); newLicn.setLicnVdlen(10); newLicn.setLicnValid(1); newLicn.setLicnVinfo(0); newLicn.setAdministration(schedule.getAdministration()); newLicn.setLicnLimit(null); newLicn.setUser(user); user.setLicense(license); userDao.updateUser(user); licenseDao.addLicense(newLicn); } else { license.setLicnValid(1); license.setLicnGdate(schedule.getSchdDate()); license.setLicnLimit(null); license.setLicnVinfo(0); licenseDao.updateLicense(license); } } else if (user.getUserState() < 4) { fail++; String ic = (Integer.parseInt(values[0]) == 1 ? "" : "") + values[1] + ""; result.add("" + ic + "" + values[3] + ""); } } } } result.set(0, "" + success + "" + fail + ""); return result; }