List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getRow
@Override public XSSFRow getRow(int rownum)
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 {//www .jav a2 s .c o m 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 {//ww w . j av a2 s .c o m 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:data.pkg.ReadWriteExcelFile.java
public static void readXLSXFile(String path) throws IOException { File excel = new File(path); FileInputStream fis = new FileInputStream(excel); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet ws = wb.getSheet("Sheet1"); int rowNum = ws.getLastRowNum() + 1; int colNum = ws.getRow(0).getLastCellNum(); for (int i = 0; i < rowNum; i++) { XSSFRow row = ws.getRow(i);/*from w ww . j a v a 2 s. c om*/ for (int j = 0; j < colNum; j++) { XSSFCell cell = row.getCell(j); if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { long l = (new Double(cell.getNumericCellValue())).longValue(); //long value = Long.parseLong(cell.toString()); if (j == 0) { dataOpen.addDeslocamento(l); System.out.println(dataOpen.getDeslocamento(i - 1)); } if (j == 1) { dataOpen.addForca(l); System.out.println(dataOpen.getForca(i - 1)); } } } } }
From source file:dataaccess.SourceDataReader.java
public static Map readRawData(File f) { Map<String, ArrayList<String>> map = new HashMap<String, ArrayList<String>>(); try {/* ww w .ja v a 2 s.co m*/ FileInputStream is = new FileInputStream(f); XSSFWorkbook wb = new XSSFWorkbook(is); XSSFSheet dataSheet = wb.getSheetAt(0); int rowID = 0; XSSFRow currRow = dataSheet.getRow(rowID); while (currRow != null) { if (rowID == 0) { // rowID = rowID + 1; currRow = dataSheet.getRow(rowID); } else { //?? String serialNumber = currRow.getCell(1).toString(); //??? String supplierName = currRow.getCell(2).toString(); //? String price = currRow.getCell(4).toString(); //? String time = currRow.getCell(6).toString(); ArrayList<String> info = new ArrayList<String>(); info.add(supplierName); info.add(price); info.add(time); map.put(serialNumber, info); rowID = rowID + 1; currRow = dataSheet.getRow(rowID); } } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return map; }
From source file:dataaccess.SourceDataReader.java
public static Map readPurchasingSupplierInformationSheet(XSSFWorkbook wb, int sheetNum) { Map<String, ArrayList<String>> map = new HashMap<String, ArrayList<String>>(); //?//from w ww . j a v a2 s.c o m XSSFSheet dataSheet = wb.getSheetAt(sheetNum); int rowID = 0; XSSFRow currRow = dataSheet.getRow(rowID); while (currRow != null) { if (rowID == 0) { // rowID = rowID + 1; currRow = dataSheet.getRow(rowID); } else { //?ID String recordID; if (currRow.getCell(0) != null) { recordID = currRow.getCell(0).toString(); } else { recordID = "null"; } //???? String companyName; if (currRow.getCell(1) != null) { companyName = currRow.getCell(1).toString(); } else { companyName = null; } //?? String companyID; if (currRow.getCell(2) != null) { companyID = currRow.getCell(2).toString(); } else { companyID = "null"; } //?? String supplierPackageNumber; if (currRow.getCell(3) != null) { supplierPackageNumber = currRow.getCell(3).toString(); } else { supplierPackageNumber = "null"; } //??? String supplierPackageName; if (currRow.getCell(4) != null) { supplierPackageName = currRow.getCell(4).toString(); } else { supplierPackageName = "null"; } //?PurchaseHeadID String PHID; if (currRow.getCell(6) != null) { PHID = currRow.getCell(6).toString(); } else { PHID = "null"; } //? String receivedTime; if (currRow.getCell(7) != null) { receivedTime = currRow.getCell(7).toString(); } else { receivedTime = "null"; } ArrayList<String> info = new ArrayList<String>(); info.add(companyName); info.add(companyID); info.add(supplierPackageNumber); info.add(supplierPackageName); info.add(PHID); info.add(receivedTime); map.put(recordID, info); rowID = rowID + 1; currRow = dataSheet.getRow(rowID); } } return map; }
From source file:dataaccess.SourceDataReader.java
public static Map readBasicProcurementPlan(XSSFWorkbook wb, int sheetNum) { Map<String, Double> procurementPlan = new HashMap<String, Double>(); XSSFSheet sheet = wb.getSheetAt(sheetNum); int rowID = 0; XSSFRow currRow = sheet.getRow(rowID); while (currRow != null) { if ((rowID == 0) || (rowID == 1)) { //?// w ww. j av a2 s. com rowID = rowID + 1; currRow = sheet.getRow(rowID); } else { //?PHID String PHID = currRow.getCell(1).toString(); //? Double price = Double.valueOf(currRow.getCell(7).toString()); procurementPlan.put(PHID, price); rowID = rowID + 1; currRow = sheet.getRow(rowID); } } return procurementPlan; }
From source file:db.pj.util.excel.ApplicationImporter.java
@Override public List<String> importExcel() { // get workbook FileInputStream inputStream = null; XSSFWorkbook workbook = null;//from w ww . j a v a 2s . 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 . ja va 2 s . 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(); 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 w ww .j a va 2 s .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;/*from w w w .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(); 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; }