List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getRow
@Override public HSSFRow getRow(int rowIndex)
From source file:com.sammyun.service.impl.ExcelServiceImpl.java
License:Open Source License
@Override public List<Member> getMembers(InputStream inputStream, DictSchool dictSchool, MemberType memberType, HttpServletRequest request) throws Exception { HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream); List<Member> members = new ArrayList<Member>(); // start/*from w w w .j a va 2s . c om*/ for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } // start Row for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow memberInfo = hssfSheet.getRow(rowNum); if (memberInfo == null) { continue; } Member member = new Member(); member.setMemberType(memberType); member.setCreateDate(new Date()); member.setModifyDate(new Date()); member.setIsEnabled(true); member.setIsLocked(false); member.setLoginFailureCount(0); Setting setting = SettingUtils.get(); member.setPassword(DigestUtils.md5Hex(setting.getInitPassword())); member.setPoint(0L); member.setRegisterIp(EduUtil.getAddr(request)); member.setSignature(""); member.setValidateCodeNumber(0); member.setDictSchool(dictSchool); // member.setIsUpdate(true); member.setIsAcceptLeaveInfo(true); String mobile = null; String realName = null; // start?? for (int i = 0; i < memberInfo.getLastCellNum(); i++) { HSSFCell memberCell = memberInfo.getCell(i); if (memberCell == null) { continue; } // if (i == 0) // { // // ?? // username = memberCell.toString(); // username = username.trim(); // member.setUsername(username); // } if (i == 0) { // mobile = memberCell.toString(); member.setMobile(mobile); member.setUsername(mobile); } if (i == 1) { // ?? realName = memberCell.toString(); realName = realName.trim(); member.setRealName(realName); } } // end?? members.add(member); } // endrow } // end return members; }
From source file:com.sammyun.service.impl.ExcelServiceImpl.java
License:Open Source License
/** * ?week <??>// w ww . j a va 2 s . com * * @param in * @return * @throws Exception * @see [?#?#?] */ @Override public ExcelMessage validateCourseWeekLessions(InputStream is) throws Exception { courseWorkbook = new HSSFWorkbook(is); ExcelMessage excelMessage = new ExcelMessage(); excelMessage.setStatus(Status.success); // start for (int numSheet = 0; numSheet < courseWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = courseWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } // ? HSSFRow titleInfo = hssfSheet.getRow(0); ExcelMessage titleValidate = new ExcelMessage(); titleValidate.setStatus(Status.fail); titleValidate.setError("???"); if ((titleInfo.getCell(0) == null) || (!titleInfo.getCell(0).toString().equals(""))) { return titleValidate; } if ((titleInfo.getCell(1) == null) || (!titleInfo.getCell(1).toString().equals("?"))) { return titleValidate; } // start Row System.out.println(hssfSheet.getLastRowNum()); if (hssfSheet.getLastRowNum() == 0) { excelMessage.setStatus(Status.fail); excelMessage.setError("?"); is.close(); return excelMessage; } // week for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow courseInfo = hssfSheet.getRow(rowNum); if (courseInfo == null) { continue; } if (courseInfo.getCell(0) == null) { excelMessage.setStatus(Status.fail); excelMessage.setError("" + rowNum + "?"); is.close(); return excelMessage; } String weekString = courseInfo.getCell(0).toString(); try { int week = Integer.parseInt(weekString); if (week == 1) { continue; } else if (week == 2) { continue; } else if (week == 3) { continue; } else if (week == 4) { continue; } else if (week == 5) { continue; } else if (week == 6) { continue; } else if (week == 7) { continue; } else { excelMessage.setStatus(Status.fail); excelMessage.setError( "" + rowNum + "?1-7"); return excelMessage; } } catch (Exception e) { logger.error("" + rowNum + "?" + e.getMessage()); excelMessage.setStatus(Status.fail); excelMessage .setError("" + rowNum + "?1-7"); is.close(); return excelMessage; } } // lessons for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow courseInfo = hssfSheet.getRow(rowNum); if (courseInfo == null) { continue; } if (courseInfo.getCell(3) == null) { excelMessage.setStatus(Status.fail); excelMessage.setError("" + rowNum + "?"); is.close(); return excelMessage; } String lessonString = courseInfo.getCell(3).toString(); try { int lesson = Integer.parseInt(lessonString); if (lesson == 1) { continue; } else if (lesson == 2) { continue; } else if (lesson == 3) { continue; } else if (lesson == 4) { continue; } else if (lesson == 5) { continue; } else if (lesson == 6) { continue; } else if (lesson == 7) { continue; } else if (lesson == 8) { continue; } else { excelMessage.setStatus(Status.fail); excelMessage.setError( "" + rowNum + "?1-8"); is.close(); return excelMessage; } } catch (Exception e) { logger.error("" + rowNum + "?" + e.getMessage()); excelMessage.setStatus(Status.fail); excelMessage .setError("" + rowNum + "?1-8"); is.close(); return excelMessage; } } // start ??? for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow courseInfo = hssfSheet.getRow(rowNum); if (courseInfo == null) { continue; } if (courseInfo.getCell(2) == null) { excelMessage.setStatus(Status.fail); excelMessage.setError("" + rowNum + "???"); is.close(); return excelMessage; } } // end ??? } return excelMessage; }
From source file:com.sammyun.service.impl.ExcelServiceImpl.java
License:Open Source License
@Override public List<Member> getTeachers(InputStream inputStream, DictSchool dictSchool, MemberType memberType, HttpServletRequest request) throws Exception { HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream); List<Member> members = new ArrayList<Member>(); // start//from w w w . j av a 2 s. com for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } int infoSize = hssfSheet.getRow(0).getLastCellNum();// ? int rowSize = hssfSheet.getLastRowNum();// ??? // start Row for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow memberInfo = hssfSheet.getRow(rowNum); if (memberInfo == null) { continue; } Member member = new Member(); member.setMemberType(memberType); member.setCreateDate(new Date()); member.setModifyDate(new Date()); member.setIsEnabled(true); member.setIsLocked(false); member.setLoginFailureCount(0); Setting setting = SettingUtils.get(); member.setPassword(DigestUtils.md5Hex(setting.getInitPassword())); member.setPoint(0L); member.setRegisterIp(EduUtil.getAddr(request)); member.setSignature(""); member.setValidateCodeNumber(0); member.setDictSchool(dictSchool); // member.s(true); member.setIsAcceptLeaveInfo(true); String mobile = null; String realName = null; // start?? for (int i = 0; i < memberInfo.getLastCellNum(); i++) { HSSFCell memberCell = memberInfo.getCell(i); if (memberCell == null) { continue; } // if (i == 0) // { // // ?? // username = memberCell.toString(); // username = username.trim(); // member.setUsername(username); // } if (i == 0) { // mobile = memberCell.toString(); member.setMobile(mobile); member.setUsername(mobile); } if (i == 1) { // ?? realName = memberCell.toString(); realName = realName.trim(); member.setRealName(realName); } if (i == 2) { // ??-? String classTeacherMap = memberCell.toString(); classTeacherMap = classTeacherMap.trim(); member.setAddress(classTeacherMap); } } // end?? members.add(member); } // endrow } // end return members; }
From source file:com.sammyun.service.impl.ExcelServiceImpl.java
License:Open Source License
@Override public List<DictStudent> getDictStudents(InputStream inputStream, DictSchool dictSchool, HttpServletRequest request) throws Exception { HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream); List<DictStudent> dictStudents = new ArrayList<DictStudent>(); // start--/*w ww . j a v a 2 s. c o m*/ for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } int infoSize = hssfSheet.getRow(0).getLastCellNum();// ? int rowSize = hssfSheet.getLastRowNum();// ??? // start Row for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow studentInfo = hssfSheet.getRow(rowNum); if (studentInfo == null) { continue; } DictStudent dictStudent = new DictStudent(); dictStudent.setCreateDate(new Date()); dictStudent.setModifyDate(new Date()); dictStudent.setStudentStatus(StudentStatus.active); String studentName = null; String studentNo = null; DictClass dictClass = new DictClass(); // start_?? for (int i = 0; i < studentInfo.getLastCellNum(); i++) { HSSFCell studentCell = studentInfo.getCell(i); if (studentCell == null) { continue; } if (i == 0) { // ? studentNo = studentCell.toString(); studentNo = studentNo.trim(); dictStudent.setStudentNo(studentNo); } if (i == 1) { // ?? studentName = studentCell.toString(); studentName = studentName.trim(); dictStudent.setStudentName(studentName); } if (i == 2) { // ? String className = studentCell.toString(); className = className.trim(); List<DictClass> classes = dictClassDao.getClassByName(className, dictSchool); if (classes != null) { if (classes.size() > 0) { dictClass = classes.get(0); dictStudent.setDictClass(dictClass); } } } if (i == 3) { // ? String memberString = studentCell.toString(); memberString = memberString.trim(); memberString = memberString.replaceAll("", ","); dictStudent.setStuRmark(memberString); } } // end___?? dictStudents.add(dictStudent); } } // end return dictStudents; }
From source file:com.schneider.tsm.process.addXPRDataManager.java
private void add2ExistingReport() throws InterruptedException { final SwingWorker worker = new SwingWorker() { @Override//from w w w . j a va 2 s . com protected Object doInBackground() throws Exception { try { FileInputStream file = new FileInputStream(new File( "C:\\softwaretest\\FileOutput\\Manager\\NPR_CPR_Report_" + requestorManager + ".xls")); HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet sheet = workbook.getSheetAt(0); int sheetsize = sheet.getPhysicalNumberOfRows(); Cell cell = null; int numm = 1; for (int i = 7; i < sheetsize; i++) { cell = sheet.getRow(i).getCell(1); if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) { cell.setCellValue("" + numm); cell = sheet.getRow(i).getCell(2); cell.setCellValue(requestID); cell = sheet.getRow(i).getCell(3); cell.setCellValue(requestorID); cell = sheet.getRow(i).getCell(4); cell.setCellValue(dayFromSubmit); cell = sheet.getRow(i).getCell(5); cell.setCellValue(requestStatus); cell = sheet.getRow(i).getCell(6); cell.setCellValue(submitDate); cell = sheet.getRow(i).getCell(7); cell.setCellValue(requestType); i = sheetsize; } numm++; } file.close(); FileOutputStream outFile = new FileOutputStream(new File( "C:\\softwaretest\\FileOutput\\Manager\\NPR_CPR_Report_" + requestorManager + ".xls")); workbook.write(outFile); outFile.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return null; } }; worker.execute(); Thread.sleep(160); }
From source file:com.schneider.tsm.process.addXPRDataManager.java
private void createNewFileReport() throws InterruptedException { final SwingWorker worker = new SwingWorker() { @Override/*from w w w . j a v a2 s.co m*/ protected Object doInBackground() throws Exception { try { FileInputStream file = new FileInputStream(new File("C:\\softwaretest\\template.xls")); HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet sheet = workbook.getSheetAt(0); Cell cell = null; cell = sheet.getRow(7).getCell(1); cell.setCellValue("1"); cell = sheet.getRow(7).getCell(2); cell.setCellValue(requestID); cell = sheet.getRow(7).getCell(3); cell.setCellValue(requestorID); cell = sheet.getRow(7).getCell(4); cell.setCellValue(dayFromSubmit); cell = sheet.getRow(7).getCell(5); cell.setCellValue(requestStatus); cell = sheet.getRow(7).getCell(6); cell.setCellValue(submitDate); cell = sheet.getRow(7).getCell(7); cell.setCellValue(requestType); file.close(); FileOutputStream outFile = new FileOutputStream(new File( "C:\\softwaretest\\FileOutput\\Manager\\NPR_CPR_Report_" + requestorManager + ".xls")); workbook.write(outFile); outFile.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return null; } }; worker.execute(); Thread.sleep(160); }
From source file:com.schneider.tsm.process.addXPRDataManager.java
private String getManager() { String manager = "No_Manager_Assigned"; try {/*from w w w . j ava2 s . c om*/ FileInputStream file = new FileInputStream(new File("C:\\softwaretest\\LibraryTest.xls")); HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet sheet = workbook.getSheetAt(0); Cell cell = null; int sheetsize = sheet.getPhysicalNumberOfRows(); for (int i = 1; i < sheetsize; i++) { cell = sheet.getRow(i).getCell(0); if (cell.getStringCellValue().equals(requestorID)) { cell = sheet.getRow(i).getCell(2); manager = cell.getStringCellValue(); } } file.close(); FileOutputStream outFile = new FileOutputStream(new File("C:\\softwaretest\\LibraryTest.xls")); workbook.write(outFile); outFile.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return manager; }
From source file:com.schneider.tsm.process.addXPRDataQuality.java
private void add2ExistingReport() throws InterruptedException { final SwingWorker worker = new SwingWorker() { @Override/* ww w .jav a2s .c o m*/ protected Object doInBackground() throws Exception { try { FileInputStream file = new FileInputStream(new File( "C:\\softwaretest\\FileOutput\\Quality\\NPR_CPR_Report_" + requestorQuality + ".xls")); HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet sheet = workbook.getSheetAt(0); int sheetsize = sheet.getPhysicalNumberOfRows(); Cell cell = null; int numm = 1; for (int i = 7; i < sheetsize; i++) { cell = sheet.getRow(i).getCell(1); if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) { cell.setCellValue("" + numm); cell = sheet.getRow(i).getCell(2); cell.setCellValue(requestID); cell = sheet.getRow(i).getCell(3); cell.setCellValue(requestorID); cell = sheet.getRow(i).getCell(4); cell.setCellValue(dayFromSubmit); cell = sheet.getRow(i).getCell(5); cell.setCellValue(requestStatus); cell = sheet.getRow(i).getCell(6); cell.setCellValue(submitDate); cell = sheet.getRow(i).getCell(7); cell.setCellValue(requestType); i = sheetsize; } numm++; } file.close(); FileOutputStream outFile = new FileOutputStream(new File( "C:\\softwaretest\\FileOutput\\Quality\\NPR_CPR_Report_" + requestorQuality + ".xls")); workbook.write(outFile); outFile.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return null; } }; worker.execute(); Thread.sleep(160); }
From source file:com.schneider.tsm.process.addXPRDataQuality.java
private void createNewFileReport() throws InterruptedException { final SwingWorker worker = new SwingWorker() { @Override//from w w w .j av a 2 s . co m protected Object doInBackground() throws Exception { try { FileInputStream file = new FileInputStream(new File("C:\\softwaretest\\template.xls")); HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet sheet = workbook.getSheetAt(0); Cell cell = null; cell = sheet.getRow(7).getCell(1); cell.setCellValue("1"); cell = sheet.getRow(7).getCell(2); cell.setCellValue(requestID); cell = sheet.getRow(7).getCell(3); cell.setCellValue(requestorID); cell = sheet.getRow(7).getCell(4); cell.setCellValue(dayFromSubmit); cell = sheet.getRow(7).getCell(5); cell.setCellValue(requestStatus); cell = sheet.getRow(7).getCell(6); cell.setCellValue(submitDate); cell = sheet.getRow(7).getCell(7); cell.setCellValue(requestType); file.close(); FileOutputStream outFile = new FileOutputStream(new File( "C:\\softwaretest\\FileOutput\\Quality\\NPR_CPR_Report_" + requestorQuality + ".xls")); workbook.write(outFile); outFile.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return null; } }; worker.execute(); Thread.sleep(160); }
From source file:com.schneider.tsm.process.addXPRDataQuality.java
private String getManager() { String manager = null;/*from w ww . j av a2 s.co m*/ try { FileInputStream file = new FileInputStream(new File("C:\\softwaretest\\LibraryTest.xls")); HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet sheet = workbook.getSheetAt(0); Cell cell = null; int sheetsize = sheet.getPhysicalNumberOfRows(); for (int i = 1; i < sheetsize; i++) { cell = sheet.getRow(i).getCell(0); if (cell.getStringCellValue().equals(requestorID)) { cell = sheet.getRow(i).getCell(2); manager = cell.getStringCellValue(); } } file.close(); FileOutputStream outFile = new FileOutputStream(new File("C:\\softwaretest\\LibraryTest.xls")); workbook.write(outFile); outFile.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return manager; }