List of usage examples for org.apache.poi.xssf.usermodel XSSFRow getCell
@Override public XSSFCell getCell(int cellnum)
From source file:data.control.dataSheet.java
private void newPatient(Patient newPatient) { XSSFRow newRow; // creating a new row if (firstSheet.getRow(newPatient.getId()) == null) { newRow = firstSheet.createRow(newPatient.getId()); } else {/*from www . j a v a 2 s . c o m*/ newRow = firstSheet.getRow(newPatient.getId()); } // creating a new cell XSSFCell cell = newRow.getCell(0); if (cell == null) { cell = newRow.createCell(0); } // adding the patient ID cell.setCellValue(newPatient.getId()); // adding the patient Name // creating a new cell cell = newRow.getCell(1); if (cell == null) { cell = newRow.createCell(1); } cell.setCellValue(newPatient.getName()); // adding BPM if (!newPatient.getBPMArray().isEmpty()) { int cellID = 2; for (Iterator itr = newPatient.getBPMArray().iterator(); itr.hasNext();) { cell = newRow.getCell(cellID); if (cell == null) { cell = newRow.createCell(cellID); } cell.setCellValue((Double) itr.next()); cellID++; } } // adding Tempreature if (!newPatient.getTempArray().isEmpty()) { int cellID = 7; for (Iterator itr = newPatient.getTempArray().iterator(); itr.hasNext();) { cell = newRow.getCell(cellID); if (cell == null) { cell = newRow.createCell(cellID); } cell.setCellValue((Double) itr.next()); cellID++; } } // adding the patient blood type cell = newRow.getCell(12); if (cell == null) { cell = newRow.createCell(12); } cell.setCellValue(newPatient.getBloodType()); // adding the patient sex cell = newRow.getCell(13); if (cell == null) { cell = newRow.createCell(13); } cell.setCellValue(newPatient.getSex()); // adding the patient age cell = newRow.getCell(14); if (cell == null) { cell = newRow.createCell(14); } cell.setCellValue(newPatient.getAge()); // setting up the date format XSSFCellStyle cellStyle = theWorkbook.createCellStyle(); CreationHelper createHelper = theWorkbook.getCreationHelper(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("d/m/yy hh:mm:ss")); // adding the patient dateAdded cell = newRow.getCell(15); if (cell == null) { cell = newRow.createCell(15); } if (newPatient.getDateCreated() != null) { cell.setCellValue(newPatient.getDateCreated()); } else { cell.setCellValue(new Date()); } cell.setCellStyle(cellStyle); // adding the date modified if (newPatient.getLastModified() != null) { cell = newRow.getCell(16); if (cell == null) { cell = newRow.createCell(16); } cell.setCellValue(newPatient.getLastModified()); cell.setCellStyle(cellStyle); } // adding the lastAlarm if (newPatient.getLastAlarmed() != null) { cell = newRow.getCell(17); if (cell == null) { cell = newRow.createCell(17); } cell.setCellValue(newPatient.getLastAlarmed()); cell.setCellStyle(cellStyle); } }
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); 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)); }//w w w .j a va2s . co m 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 {/*from www. j a va 2 s.com*/ 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 ww w .j ava 2s. co 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)) { //?// ww w .ja va 2 s .co m 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:database.scanners.DailyWOScanner.java
public WOData[] ScanDatabase(String databaseName) { int woRowNumber = 0; //Licznik wierszy klasy WOData int numericCellInt; //Licznik komrek z wartoci numeryczn WOData[] dataArray = null;/* w w w .j av a 2 s .c om*/ Iterator rows = null; try { dataArray = GetBlankDatabase(databaseName); rows = GetSheetIterator(databaseName); } catch (IOException ex) { System.out.println(ex.getMessage()); } while (rows.hasNext()) { numericCellInt = 0; XSSFRow row = (XSSFRow) rows.next(); Iterator cells = row.cellIterator(); while (cells.hasNext()) { XSSFCell cell = (XSSFCell) cells.next(); if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) numericCellInt++; } if (numericCellInt == 0) continue; XSSFCell cell; cell = row.getCell(config.getDailyWOMinTimeCell()); dataArray[woRowNumber].setMinTime((int) cell.getNumericCellValue()); cell = row.getCell(config.getDailyWOMaxTimeCell()); dataArray[woRowNumber].setMaxTime((int) cell.getNumericCellValue()); cell = row.getCell(config.getDailyWOClientNumberCell()); dataArray[woRowNumber].setClientNumber((int) cell.getNumericCellValue()); cell = row.getCell(config.getDailyWOAddressCell()); dataArray[woRowNumber].setClientAddress(cell.getStringCellValue()); woRowNumber++; } return dataArray; }
From source file:db.pj.util.excel.ApplicationImporter.java
@Override public List<String> importExcel() { // get workbook FileInputStream inputStream = null; XSSFWorkbook workbook = null;/*from ww w.ja v a2 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;// w w w . ja 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(); 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 ww w . j ava2 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;/* ww w . j a v a 2 s. co 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; }