List of usage examples for org.apache.poi.ss.usermodel Workbook getSheetAt
Sheet getSheetAt(int index);
From source file:com.globalsight.util.ExcelUtil.java
License:Apache License
public static Sheet getDefaultSheet(Workbook workbook) { if (workbook == null) return null; return workbook.getSheetAt(0); }
From source file:com.glodon.tika.UpdateEmbeddedDoc.java
License:Apache License
/** * Called to test whether or not the embedded workbook was correctly * updated. This method simply recovers the first cell from the first row * of the first workbook and tests the value it contains. * <p/>//from www . j a v a 2 s.c o m * Note that execution will not continue up to the assertion as the * embedded workbook is now corrupted and causes an IllegalArgumentException * with the following message * <p/> * <em>java.lang.IllegalArgumentException: Your InputStream was neither an * OLE2 stream, nor an OOXML stream</em> * <p/> * to be thrown when the WorkbookFactory.createWorkbook(InputStream) method * is executed. * * @throws org.apache.poi.openxml4j.exceptions.OpenXML4JException * Rather * than use the specific classes (HSSF/XSSF) to handle the embedded * workbook this method uses those defeined in the SS stream. As * a result, it might be the case that a SpreadsheetML file is * opened for processing, throwing this exception if that file is * invalid. * @throws java.io.IOException Thrown if a problem occurs in the underlying * file system. */ public void checkUpdatedDoc() throws OpenXML4JException, IOException { Workbook workbook = null; Sheet sheet = null; Row row = null; Cell cell = null; PackagePart pPart = null; Iterator<PackagePart> pIter = null; List<PackagePart> embeddedDocs = this.doc.getAllEmbedds(); if (embeddedDocs != null && !embeddedDocs.isEmpty()) { pIter = embeddedDocs.iterator(); while (pIter.hasNext()) { pPart = pIter.next(); if (pPart.getPartName().getExtension().equals(BINARY_EXTENSION) || pPart.getPartName().getExtension().equals(OPENXML_EXTENSION)) { workbook = WorkbookFactory.create(pPart.getInputStream()); sheet = workbook.getSheetAt(SHEET_NUM); row = sheet.getRow(ROW_NUM); cell = row.getCell(CELL_NUM); assertEquals(cell.getNumericCellValue(), NEW_VALUE, 0.0001); } } } }
From source file:com.google.gdt.handler.impl.ExcelHandler.java
License:Open Source License
/** * returns the list of sheets in a workbook * /* w ww . ja va 2 s.c o m*/ * @param wb * @return sheets */ private static List<Sheet> getSheets(Workbook wb) { List<Sheet> sheets = new ArrayList<Sheet>(); for (int i = 0; i < 20; i++) { try { Sheet sheet = wb.getSheetAt(i); sheets.add(sheet); } catch (IllegalArgumentException e) { break; } } return sheets; }
From source file:com.gsecs.GSECSFrame.java
private void importSchoolDataActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_importSchoolDataActionPerformed // TODO add your handling code here: JFileChooser jf = new JFileChooser(); jf.setDialogTitle("Please select a excel File to import"); int result = jf.showOpenDialog(null); if (result == JFileChooser.APPROVE_OPTION) { String filePath = jf.getSelectedFile().getAbsolutePath(); try {/*from w w w. java 2 s .c o m*/ FileInputStream fis = new FileInputStream(filePath); Workbook workbook = new XSSFWorkbook(fis); Sheet firstSheet = workbook.getSheetAt(0); Iterator<Row> iterator = firstSheet.iterator(); List<SchoolBean> schoolBeanList = new ArrayList<SchoolBean>(); while (iterator.hasNext()) { Row nextRow = iterator.next(); if (nextRow.getRowNum() != 0) { Iterator<Cell> cellIterator = nextRow.cellIterator(); SchoolBean school = new SchoolBean(); while (cellIterator.hasNext()) { Cell nextCell = cellIterator.next(); int columnIndex = nextCell.getColumnIndex(); switch (columnIndex) { case 0: school.setSchoolAbbrv((String) getCellValue(nextCell)); break; case 1: school.setSchoolName((String) getCellValue(nextCell)); break; } } schoolBeanList.add(school); } } workbook.close(); fis.close(); mainPanel.removeAll(); mainPanel.repaint(); GradSchoolModel model = new GradSchoolModel(); GradSchoolJInternalFrame schoolFrame = new GradSchoolJInternalFrame(); schoolFrame.getSchoolMsgLabel().setText(""); schoolFrame.getAddSchoolButton().setVisible(true); schoolFrame.getUpdateSchoolButton().setVisible(false); schoolFrame.getDeleteSchoolButton().setVisible(false); //schoolFrame.getSchoolDataTable().setModel(DbUtils.resultSetToTableModel( new GradSchoolModel().getAllSchools())); for (SchoolBean schoolBean : schoolBeanList) { try { model.addSchool(schoolBean); } catch (Exception exp) { System.out.println("Exception Raised....." + exp.getMessage()); } } schoolFrame.loadDataIntoJTable(); mainPanel.add(schoolFrame); schoolFrame.setVisible(true); } catch (Exception exp) { JOptionPane.showMessageDialog(null, exp.getMessage()); } } }
From source file:com.gsecs.GSECSFrame.java
private void facultyImportMenuItemActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_facultyImportMenuItemActionPerformed // TODO add your handling code here: JFileChooser jf = new JFileChooser(); jf.setDialogTitle("Please select a excel File to import"); int result = jf.showOpenDialog(null); if (result == JFileChooser.APPROVE_OPTION) { String filePath = jf.getSelectedFile().getAbsolutePath(); try {/*from w w w. ja va 2 s . c o m*/ FileInputStream fis = new FileInputStream(filePath); Workbook workbook = new XSSFWorkbook(fis); Sheet firstSheet = workbook.getSheetAt(0); Iterator<Row> iterator = firstSheet.iterator(); List<FacultyBean> facultyBeanList = new ArrayList<FacultyBean>(); while (iterator.hasNext()) { Row nextRow = iterator.next(); if (nextRow.getRowNum() != 0) { Iterator<Cell> cellIterator = nextRow.cellIterator(); FacultyBean faculty = new FacultyBean(); while (cellIterator.hasNext()) { Cell nextCell = cellIterator.next(); int columnIndex = nextCell.getColumnIndex(); switch (columnIndex) { case 0: faculty.setLastName((String) getCellValue(nextCell)); break; case 1: faculty.setFirstName((String) getCellValue(nextCell)); break; case 2: faculty.setGradSchool((String) getCellValue(nextCell)); break; case 3: faculty.setDegree((String) getCellValue(nextCell)); break; case 4: faculty.setTitle((String) getCellValue(nextCell)); break; case 5: faculty.setDaysToTeach(getCellValue(nextCell).toString()); break; case 6: faculty.setMaxLoadFall(Double.valueOf(getCellValue(nextCell).toString())); break; case 7: faculty.setMaxLoadSpring(Double.valueOf(getCellValue(nextCell).toString())); break; case 8: faculty.setMaxLoadSummer(Double.valueOf(getCellValue(nextCell).toString())); break; } } facultyBeanList.add(faculty); } } workbook.close(); fis.close(); mainPanel.removeAll(); mainPanel.repaint(); FacultyModel model = new FacultyModel(); FacultyJInternalFrame facultyFrame = new FacultyJInternalFrame(); facultyFrame.getFacultyMsgLabel().setText(""); facultyFrame.getAddFacultyButton().setVisible(true); facultyFrame.getUpdateFacultyButton().setVisible(false); facultyFrame.getDeleteFacultyButton().setVisible(false); //schoolFrame.getSchoolDataTable().setModel(DbUtils.resultSetToTableModel( new GradSchoolModel().getAllSchools())); for (FacultyBean facultyBean : facultyBeanList) { try { model.addFaculty(facultyBean); } catch (Exception exp) { System.out.println("Exception Raised....." + exp.getMessage()); } } facultyFrame.loadDataIntoJTable(); mainPanel.add(facultyFrame); facultyFrame.setVisible(true); } catch (Exception exp) { JOptionPane.showMessageDialog(null, exp.getMessage()); } } }
From source file:com.gsecs.GSECSFrame.java
private void importCourseMenuItemActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_importCourseMenuItemActionPerformed // TODO add your handling code here: JFileChooser jf = new JFileChooser(); jf.setDialogTitle("Please select a excel File to import"); int result = jf.showOpenDialog(null); if (result == JFileChooser.APPROVE_OPTION) { String filePath = jf.getSelectedFile().getAbsolutePath(); try {/*from w w w .j a v a2s . c o m*/ FileInputStream fis = new FileInputStream(filePath); Workbook workbook = new XSSFWorkbook(fis); Sheet firstSheet = workbook.getSheetAt(0); Iterator<Row> iterator = firstSheet.iterator(); List<CourseBean> courseBeanList = new ArrayList<CourseBean>(); while (iterator.hasNext()) { Row nextRow = iterator.next(); if (nextRow.getRowNum() != 0) { Iterator<Cell> cellIterator = nextRow.cellIterator(); CourseBean courseBean = new CourseBean(); while (cellIterator.hasNext()) { Cell nextCell = cellIterator.next(); int columnIndex = nextCell.getColumnIndex(); switch (columnIndex) { case 0: courseBean.setCourseCode((String) getCellValue(nextCell)); break; case 1: courseBean.setCourseName((String) getCellValue(nextCell)); break; case 2: courseBean.setCourseDesc((String) getCellValue(nextCell)); break; case 3: courseBean.setCourseHours(Double.valueOf(getCellValue(nextCell).toString())); break; case 4: courseBean.setCourseCap(Double.valueOf(getCellValue(nextCell).toString())); break; case 5: courseBean.setOfferedFall((String) getCellValue(nextCell)); break; case 6: courseBean.setOfferedSpring((String) getCellValue(nextCell)); break; case 7: courseBean.setOfferedSummer((String) getCellValue(nextCell)); break; case 8: courseBean.setCoursePreReq((String) getCellValue(nextCell)); break; case 9: courseBean.setTeacher((String) getCellValue(nextCell)); break; } } courseBeanList.add(courseBean); } } workbook.close(); fis.close(); mainPanel.removeAll(); mainPanel.repaint(); CourseModel model = new CourseModel(); CourseJInternalFrame courseFrame = new CourseJInternalFrame(); courseFrame.getCourseMsgLabel().setText(""); courseFrame.getAddCourseButton().setVisible(true); courseFrame.getUpdateCourseButton().setVisible(false); courseFrame.getDeleteCourseButton().setVisible(false); //schoolFrame.getSchoolDataTable().setModel(DbUtils.resultSetToTableModel( new GradSchoolModel().getAllSchools())); for (CourseBean courseBean : courseBeanList) { try { model.addCourse(courseBean); } catch (Exception exp) { System.out.println("Exception Raised....." + exp.getMessage()); } } courseFrame.loadDataIntoJTable(); mainPanel.add(courseFrame); courseFrame.setVisible(true); } catch (Exception exp) { JOptionPane.showMessageDialog(null, exp.getMessage()); } } }
From source file:com.gsecs.GSECSFrame.java
private void importDegreePlanMenuItemActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_importDegreePlanMenuItemActionPerformed // TODO add your handling code here: JFileChooser jf = new JFileChooser(); jf.setDialogTitle("Please select a excel File to import"); int result = jf.showOpenDialog(null); if (result == JFileChooser.APPROVE_OPTION) { String filePath = jf.getSelectedFile().getAbsolutePath(); try {/* ww w .j a v a 2 s .co m*/ FileInputStream fis = new FileInputStream(filePath); Workbook workbook = new XSSFWorkbook(fis); Sheet firstSheet = workbook.getSheetAt(0); Iterator<Row> iterator = firstSheet.iterator(); List<DegreePlanBean> degreePlanBeanList = new ArrayList<DegreePlanBean>(); while (iterator.hasNext()) { Row nextRow = iterator.next(); if (nextRow.getRowNum() != 0) { Iterator<Cell> cellIterator = nextRow.cellIterator(); DegreePlanBean degreePlan = new DegreePlanBean(); while (cellIterator.hasNext()) { Cell nextCell = cellIterator.next(); int columnIndex = nextCell.getColumnIndex(); switch (columnIndex) { case 0: degreePlan.setDegreeCode((String) getCellValue(nextCell)); break; case 1: degreePlan.setDeptTrack((String) getCellValue(nextCell)); break; case 2: degreePlan.setDescription((String) getCellValue(nextCell)); break; case 3: degreePlan.setHoursReq(Double.valueOf(getCellValue(nextCell).toString())); break; case 4: degreePlan.setType((String) getCellValue(nextCell)); break; case 5: degreePlan.setCourses((String) getCellValue(nextCell)); break; case 6: degreePlan.setGradSchool((String) getCellValue(nextCell)); break; case 7: degreePlan.setForecast(Double.valueOf(getCellValue(nextCell).toString())); break; } } degreePlanBeanList.add(degreePlan); } } workbook.close(); fis.close(); mainPanel.removeAll(); mainPanel.repaint(); DegreePlanModel model = new DegreePlanModel(); DegreeJInternalFrame degreeFrame = new DegreeJInternalFrame(); degreeFrame.getDegreeMsgLabel().setText(""); degreeFrame.getAddDegreeButton().setVisible(true); degreeFrame.getUpdateDegreeButton().setVisible(false); degreeFrame.getDeleteDegreeButton().setVisible(false); //schoolFrame.getSchoolDataTable().setModel(DbUtils.resultSetToTableModel( new GradSchoolModel().getAllSchools())); for (DegreePlanBean degreePlanBean : degreePlanBeanList) { try { model.addDegree(degreePlanBean); } catch (Exception exp) { System.out.println("Exception Raised....." + exp.getMessage()); } } degreeFrame.loadDataIntoJTable(); mainPanel.add(degreeFrame); degreeFrame.setVisible(true); } catch (Exception exp) { JOptionPane.showMessageDialog(null, exp.getMessage()); } } }
From source file:com.gsecs.GSECSFrame.java
private void importStudentMenuItemActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_importStudentMenuItemActionPerformed // TODO add your handling code here: JFileChooser jf = new JFileChooser(); jf.setDialogTitle("Please select a excel File to import"); int result = jf.showOpenDialog(null); if (result == JFileChooser.APPROVE_OPTION) { String filePath = jf.getSelectedFile().getAbsolutePath(); try {/*from w w w . j a va 2s .com*/ FileInputStream fis = new FileInputStream(filePath); Workbook workbook = new XSSFWorkbook(fis); Sheet firstSheet = workbook.getSheetAt(0); Iterator<Row> iterator = firstSheet.iterator(); List<StudentBean> studentBeansList = new ArrayList<StudentBean>(); while (iterator.hasNext()) { Row nextRow = iterator.next(); if (nextRow.getRowNum() != 0) { Iterator<Cell> cellIterator = nextRow.cellIterator(); StudentBean studentBean = new StudentBean(); while (cellIterator.hasNext()) { Cell nextCell = cellIterator.next(); int columnIndex = nextCell.getColumnIndex(); switch (columnIndex) { case 0: studentBean.setStudentId(getCellValue(nextCell).toString().replace(".0", "")); break; case 1: studentBean.setDegreeCode((String) getCellValue(nextCell)); break; case 2: studentBean.setGraduationDate((String) getCellValue(nextCell)); break; } } studentBeansList.add(studentBean); } } workbook.close(); fis.close(); mainPanel.removeAll(); mainPanel.repaint(); StudentModel model = new StudentModel(); StudentJInternalFrame studentFrame = new StudentJInternalFrame(); studentFrame.getStudentMsgLabel().setText(""); studentFrame.getAddStudentButton().setVisible(true); studentFrame.getUpdateStudentButton().setVisible(false); studentFrame.getDeleteStudentButton().setVisible(false); //schoolFrame.getSchoolDataTable().setModel(DbUtils.resultSetToTableModel( new GradSchoolModel().getAllSchools())); for (StudentBean studentBean : studentBeansList) { try { model.addStudent(studentBean); } catch (Exception exp) { System.out.println("Exception Raised....." + exp.getMessage()); } } studentFrame.loadDataIntoJTable(); mainPanel.add(studentFrame); studentFrame.setVisible(true); } catch (Exception exp) { JOptionPane.showMessageDialog(null, exp.getMessage()); } } }
From source file:com.gsecs.GSECSFrame.java
private void importStudentCourseMenuItemActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_importStudentCourseMenuItemActionPerformed // TODO add your handling code here: JFileChooser jf = new JFileChooser(); jf.setDialogTitle("Please select a excel File to import"); int result = jf.showOpenDialog(null); if (result == JFileChooser.APPROVE_OPTION) { String filePath = jf.getSelectedFile().getAbsolutePath(); try {// w ww .j av a 2s. co m FileInputStream fis = new FileInputStream(filePath); Workbook workbook = new XSSFWorkbook(fis); Sheet firstSheet = workbook.getSheetAt(0); Iterator<Row> iterator = firstSheet.iterator(); List<StudentCourseBean> studentCourseBeansList = new ArrayList<StudentCourseBean>(); while (iterator.hasNext()) { Row nextRow = iterator.next(); if (nextRow.getRowNum() != 0) { Iterator<Cell> cellIterator = nextRow.cellIterator(); StudentCourseBean studentBean = new StudentCourseBean(); while (cellIterator.hasNext()) { Cell nextCell = cellIterator.next(); int columnIndex = nextCell.getColumnIndex(); switch (columnIndex) { case 0: studentBean.setStudentId(getCellValue(nextCell).toString().replace(".0", "")); break; case 1: studentBean.setCourseId((String) getCellValue(nextCell)); break; case 2: studentBean.setCourseDesc((String) getCellValue(nextCell)); break; case 3: studentBean.setTerm((String) getCellValue(nextCell)); break; case 4: studentBean.setGrade((String) getCellValue(nextCell)); break; } } studentCourseBeansList.add(studentBean); } } workbook.close(); fis.close(); mainPanel.removeAll(); mainPanel.repaint(); StudentModel model = new StudentModel(); StudentCourseJInternalFrame studentFrame = new StudentCourseJInternalFrame(); studentFrame.getStudentMsgLabel().setText(""); studentFrame.getAddStudentCourseButton().setVisible(true); studentFrame.getUpdateStudentCourseButton().setVisible(false); studentFrame.getDeleteStudentCourseButton().setVisible(false); //schoolFrame.getSchoolDataTable().setModel(DbUtils.resultSetToTableModel( new GradSchoolModel().getAllSchools())); for (StudentCourseBean studentBean : studentCourseBeansList) { try { model.addStudentCourse(studentBean); } catch (Exception exp) { System.out.println("Exception Raised....." + exp.getMessage()); } } studentFrame.loadDataIntoJTable(); mainPanel.add(studentFrame); studentFrame.setVisible(true); } catch (Exception exp) { JOptionPane.showMessageDialog(null, exp.getMessage()); } } }
From source file:com.gsecs.GSECSFrame.java
private void importcourseSectionMenuItemActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_importcourseSectionMenuItemActionPerformed // TODO add your handling code here: JFileChooser jf = new JFileChooser(); jf.setDialogTitle("Please select a excel File to import"); int result = jf.showOpenDialog(null); if (result == JFileChooser.APPROVE_OPTION) { String filePath = jf.getSelectedFile().getAbsolutePath(); try {/*from w ww .java 2 s. com*/ FileInputStream fis = new FileInputStream(filePath); Workbook workbook = new XSSFWorkbook(fis); Sheet firstSheet = workbook.getSheetAt(0); Iterator<Row> iterator = firstSheet.iterator(); List<CourseSectionBean> courseSectionBeansList = new ArrayList<CourseSectionBean>(); while (iterator.hasNext()) { Row nextRow = iterator.next(); if (nextRow.getRowNum() != 0) { Iterator<Cell> cellIterator = nextRow.cellIterator(); CourseSectionBean courseSectionBean = new CourseSectionBean(); while (cellIterator.hasNext()) { Cell nextCell = cellIterator.next(); int columnIndex = nextCell.getColumnIndex(); switch (columnIndex) { case 0: courseSectionBean .setSectionNumber(getCellValue(nextCell).toString().replace(".0", "")); break; case 1: courseSectionBean.setCourse((String) getCellValue(nextCell)); break; case 2: courseSectionBean.setFaculty((String) getCellValue(nextCell)); break; } } courseSectionBeansList.add(courseSectionBean); } } workbook.close(); fis.close(); mainPanel.removeAll(); mainPanel.repaint(); CourseSectionModel model = new CourseSectionModel(); CourseSectionJInternalFrame courseSectionFrame = new CourseSectionJInternalFrame(); courseSectionFrame.getCourseSectionMsgLabel().setText(""); courseSectionFrame.getAddCourseSectionButton().setVisible(true); courseSectionFrame.getUpdateCourseSectionButton().setVisible(false); courseSectionFrame.getDeleteCourseSectionButton().setVisible(false); //schoolFrame.getSchoolDataTable().setModel(DbUtils.resultSetToTableModel( new GradSchoolModel().getAllSchools())); for (CourseSectionBean courseSectionBean : courseSectionBeansList) { try { model.addCourseSection(courseSectionBean); } catch (Exception exp) { System.out.println("Exception Raised....." + exp.getMessage()); } } courseSectionFrame.loadDataIntoJTable(); mainPanel.add(courseSectionFrame); courseSectionFrame.setVisible(true); } catch (Exception exp) { JOptionPane.showMessageDialog(null, exp.getMessage()); } } }