Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. getSheetAt

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt.

Prototype

@Override
public XSSFSheet getSheetAt(int index)

Source Link

Document

Get the XSSFSheet object at the given index.

Usage

From source file:resources.ministory.MinistoryFormManager.java

public Date updateUsedFormDate(List<Integer> usedMiniFormList) {
    FileInputStream fileInStream = null;
    FileOutputStream fileOutStream = null;
    Date curDate = new Date();
    try {/*w  w w  . j a  va2  s.co  m*/
        System.out.println("Updating Used Ministory Form");
        String filePath = this.filePath;
        fileInStream = checkFileExist(filePath);

        //Get the workbook instance for XLSX file 
        XSSFWorkbook workbook = new XSSFWorkbook(fileInStream);
        XSSFSheet spreadsheet = workbook.getSheetAt(0);

        final int MY_MINIMUM_COLUMN_COUNT = 12;
        int rowStart = 1;
        int rowEnd = spreadsheet.getLastRowNum();//Math.max(1400, spreadsheet.getLastRowNum());
        int writeCount = usedMiniFormList.size();
        for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
            if (writeCount <= 0)// if no more thing to write
            {
                break;
            }

            Row row = spreadsheet.getRow(rowNum);
            if (row == null) {
                // This whole row is empty
                continue;
            }
            Cell cell = row.getCell(0, Row.RETURN_BLANK_AS_NULL);
            //Cell dateCell = row.getCell(11, Row.RETURN_BLANK_AS_NULL);
            if (cell != null) {
                Double numb = cell.getNumericCellValue();
                Integer number = numb.intValue();

                for (int containNumb : usedMiniFormList) {
                    if (containNumb == number) {
                        Cell dateCell = row.getCell(11, Row.RETURN_BLANK_AS_NULL);
                        if (dateCell == null) {
                            dateCell = row.createCell(11);
                        }
                        dateCell.setCellType(CELL_TYPE_NUMERIC);
                        dateCell.setCellValue(curDate);
                        writeCount--;
                    }
                }

                //                    if(usedMiniFormList.contains(number)){// Set Date
                //                        if(dateCell == null)
                //                            dateCell = row.createCell(11);
                //                        dateCell.setCellType(CELL_TYPE_NUMERIC);
                //                        dateCell.setCellValue(curDate);
                //                    }
            } else {// If cell is NULL skip
                continue;
            }

        }

        fileOutStream = new FileOutputStream(filePath);
        workbook.write(fileOutStream);

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    } catch (Exception ex) {
        Logger.getLogger(MinistoryFormItem.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        try {
            fileInStream.close();
            fileOutStream.close();
        } catch (IOException ex) {
            Logger.getLogger(MinistoryFormManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return curDate;
    }
}

From source file:resources.ministory.MinistoryFormManager.java

private List<MinistoryFormItem> loadAllFromExcel(String filePath) {
    System.out.println("Loading MiniStory Form Excel Database");
    List<MinistoryFormItem> miniFormList = new ArrayList();
    boolean cleanSucess = true;
    try {//w w w .  j  a  v  a 2 s .c  om
        System.out.println("Reading Ministory DB");
        FileInputStream fileStream = checkFileExist(filePath);

        //Get the workbook instance for XLSX file 
        XSSFWorkbook workbook = new XSSFWorkbook(fileStream);
        XSSFSheet spreadsheet = workbook.getSheetAt(0);
        //XSSFRow row;

        final int MY_MINIMUM_COLUMN_COUNT = 12;
        int rowStart = 1;
        int rowEnd = Math.max(1400, spreadsheet.getLastRowNum());
        //Startfrom row 1
        for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
            Row r = spreadsheet.getRow(rowNum);
            if (r == null) {
                // This whole row is empty
                continue;
            }
            MinistoryFormItem miniFormTemp = new MinistoryFormItem();
            int lastColumn = Math.max(r.getLastCellNum(), MY_MINIMUM_COLUMN_COUNT);

            for (int cellNum = 0; cellNum < lastColumn; cellNum++) {
                Cell cell = r.getCell(cellNum, Row.RETURN_BLANK_AS_NULL);
                if (cell == null) {
                    // The spreadsheet is empty in this cell
                    miniFormTemp.setEmptyValue(cellNum);
                } else {
                    // Fill the cell's contents to MiniForm Obj
                    miniFormTemp.setValue(cellNum, cell);
                }
            }

            miniFormList.add(miniFormTemp);

        }

        fileStream.close();
    } catch (FileNotFoundException e) {
        cleanSucess = false;
        e.printStackTrace();
    } catch (IOException e) {
        cleanSucess = false;
        e.printStackTrace();
    } catch (Exception ex) {
        cleanSucess = false;
        Logger.getLogger(MinistoryFormItem.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        if (cleanSucess) {
            System.out.println("All Read Without Error");
        } else {
            System.out.println("There were some Error(s)");
        }
    }

    return miniFormList;
}

From source file:rocky.sizecounter.SizeCounterUtil.java

License:Apache License

/**
 * Count size of Excel file./* w ww. j a  v a  2 s . c  o m*/
 * 
 * @param filePath path of Excel file
 * @return SizeMetaData: Unit,Size: SHEET; Unit1,Size1:PAGE
 */
public static SizeMetaData countSpreadSheet(String filePath) {
    InputStream is = null;
    SizeMetaData sizeMD = new SizeMetaData();
    int nmPage = 0;
    int nmSheet;
    try {
        is = CommonUtil.loadResource(filePath);
        if (CommonUtil.getExtension(filePath).equals("xls")) {
            HSSFWorkbook wb = new HSSFWorkbook(is);
            wb.getDocumentSummaryInformation().getLineCount();
            nmSheet = wb.getNumberOfSheets();
            HSSFSheet sheet;

            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                sheet = wb.getSheetAt(i);

                // Count approximately number of page
                nmPage += sheet.getPrintSetup().getFitWidth() + sheet.getPrintSetup().getFitHeight();
            }

            sizeMD.setUnit(UnitType.SHEET);
            sizeMD.setSize(nmSheet);

            sizeMD.setUnit1(UnitType.PAGE);
            sizeMD.setSize1(nmPage);

        } else if (CommonUtil.getExtension(filePath).equals("xlsx")) {
            XSSFWorkbook xwb = new XSSFWorkbook(is);
            nmSheet = xwb.getNumberOfSheets();
            XSSFSheet sheet;

            for (int i = 0; i < xwb.getNumberOfSheets(); i++) {
                sheet = xwb.getSheetAt(i);

                // Count approximately number of page
                nmPage += sheet.getPrintSetup().getFitWidth() + sheet.getPrintSetup().getFitHeight();
            }

            sizeMD.setUnit(UnitType.SHEET);
            sizeMD.setSize(nmSheet);

            sizeMD.setUnit1(UnitType.PAGE);
            sizeMD.setSize1(nmPage);
        }
    } catch (FileNotFoundException ex) {
        LOG.warn("Invalid when reading file.", ex);
    } catch (IOException ex) {
        LOG.warn("Invalid when reading file.", ex);
    } catch (Exception e) {
        LOG.warn("Can not count file " + new File(filePath).getName(), e);
    } finally {
        if (is != null) {
            try {
                is.close();
            } catch (IOException ex) {
                LOG.warn("Close the file input stream", ex);
            }
        }
    }

    return sizeMD;
}

From source file:scoretracker.beans.EJB.UploadService.java

public Boolean upload(Part file) throws IOException {
    Boolean upload = true;//from  w  w  w .  ja  v a 2  s. co  m
    InputStream inputStream = file.getInputStream();
    Query q;
    em = emf.createEntityManager();
    XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
    XSSFSheet firstSheet = workbook.getSheetAt(0);
    Iterator<Row> iterator = firstSheet.iterator();
    List<Teststudent> studenten = new ArrayList<Teststudent>();
    Test test = new Test();

    //Klas ophalen uit Excel
    Row nextRow = iterator.next();
    q = em.createNamedQuery("Klas.findByName");
    List<Klas> klassen = new ArrayList<>();
    q.setParameter("name", nextRow.getCell(1).getStringCellValue());
    klassen = q.getResultList();

    if (klassen.size() != 1) {
        //vak ophalen uit Excel
        nextRow = iterator.next();
        q = em.createNamedQuery("Course.findByName");
        List<Course> courses = new ArrayList<>();
        q.setParameter("name", nextRow.getCell(1).getStringCellValue());
        courses = q.getResultList();

        if (courses.size() != 1) {
            //Test object aanmaken

            nextRow = iterator.next();
            test.setClassId(klassen.get(0));
            test.setCourseId(courses.get(0));
            test.setName(nextRow.getCell(1).getStringCellValue());
            nextRow = iterator.next();
            Double maxScore = nextRow.getCell(1).getNumericCellValue();
            test.setMaxScore(maxScore.intValue());

            nextRow = firstSheet.getRow(5);
            while (iterator.hasNext()) {
                nextRow = iterator.next();
                List<Student> students = new ArrayList<>();
                q = em.createNamedQuery("Student.findByRNr");
                q.setParameter("rNr", nextRow.getCell(0).getStringCellValue());
                students = q.getResultList();

                if (students.size() != 1) {
                    Student student = students.get(0);
                    Teststudent testStudent = new Teststudent();

                    testStudent.setTestId(test);
                    testStudent.setStudentId(student);
                    Double score = nextRow.getCell(2).getNumericCellValue();
                    testStudent.setScore(score.intValue());

                    studenten.add(testStudent);
                } else {
                    upload = false;
                }
            }
        } else {
            upload = false;
        }
    } else {
        upload = false;
    }

    workbook.close();
    inputStream.close();
    //fouthandeling en toevoegen
    if (upload) {

        em.persist(test);

        for (Teststudent t : studenten) {
            em.persist(t);

        }

    } else {
        upload = false;
    }

    return upload;

}

From source file:scoutapp.MainWindow.java

private void importMatches() {
    //Read the file and open it as an Excel workbook with the Apache POI libraries
    File myFile = new File("src/scoutapp/Data/SR_South_Bluford.xlsx");
    InputStream inp = null;//www  .j  a  v  a 2 s  .co  m
    try {
        inp = new FileInputStream(myFile);
    } catch (FileNotFoundException ex) {
        Logger.getLogger(MainWindow.class.getName()).log(Level.SEVERE, null, ex);
    }
    XSSFWorkbook wb = null;
    try {
        wb = new XSSFWorkbook(inp);
    } catch (IOException ex) {
        Logger.getLogger(MainWindow.class.getName()).log(Level.SEVERE, null, ex);
    }

    //Access the match result sheet
    Sheet matchList = wb.getSheetAt(0);
    Row matchNumRow = matchList.getRow(0);
    Cell amount = matchNumRow.getCell(0);

    //get the number of matches
    Double matchNum = amount.getNumericCellValue();

    //import each match and add it to the season
    System.out.println("Importing Matches...");
    for (int i = 2; i < matchNum + 2; i++) {
        //get the teams and score data
        Row row = matchList.getRow(i);
        Cell red1 = row.getCell(1);
        int red1Name = (int) Integer.parseInt(String.valueOf((int) red1.getNumericCellValue()));
        Cell red2 = row.getCell(2);
        int red2Name = (int) Integer.parseInt(String.valueOf((int) red2.getNumericCellValue()));
        Cell blue1 = row.getCell(3);
        int blue1Name = (int) Integer.parseInt(String.valueOf((int) blue1.getNumericCellValue()));
        Cell blue2 = row.getCell(4);
        int blue2Name = (int) Integer.parseInt(String.valueOf((int) blue2.getNumericCellValue()));
        Cell redScore = row.getCell(5);
        int rScore = (int) redScore.getNumericCellValue();
        Cell blueScore = row.getCell(6);
        int bScore = (int) blueScore.getNumericCellValue();
        Match match = new Match(currentComp.getCompetitionID(), i - 1, red1Name, red2Name, blue1Name, blue2Name,
                rScore, bScore, "", "");
        season.addMatch(match);
    }
    try {
        inp.close();
    } catch (IOException ex) {
        Logger.getLogger(MainWindow.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:se.nrm.dina.dina.inventory.logic.dyntaxa.DyntaxaDumpLogic.java

private XSSFSheet getExcelSheet() {
    try (FileInputStream file = new FileInputStream(new File(EXCEL_FILE_PATH))) {
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        return workbook.getSheetAt(0);
    } catch (IOException ex) {
        System.out.println("error: " + ex.getMessage());
        return null;
    }/*from ww  w.  ja v  a  2 s.c o  m*/
}

From source file:se.nrm.dina.dina.inventory.logic.dyntaxa.ExcelLogic.java

public void readInExcelFile(String action) {

    logger.info("readInExcelFile : {}", action);

    timestamp = new Timestamp(System.currentTimeMillis());
    taxonTreeDef = (Taxontreedef) smtpDao.findByReference(TAXON_TREE_DEF_ID, Taxontreedef.class);

    try (FileInputStream file = new FileInputStream(new File(EXCEL_FILE_PATH))) {
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        switch (action) {
        case "correctionsToDyntaxa":
            collectionsToDyntaxa(workbook.getSheetAt(0)); // done corrections to DynTaxa
            break;
        case "highTaxa": // done new higher taxa to add   // this need to run before sheet 3
            uploadNewHighTaxa(workbook.getSheetAt(4));
            break;
        case "oldSynonyms":
            oldSpeciesSynonyms(workbook.getSheetAt(1)); // done Old species synonyms to add
            break;
        case "oldHigherTaxonSynonyms": // done old higher taxon synonym to add
            addOldHigherTaxonSynonyms(workbook.getSheetAt(2));
            break;
        case "newTaxa": // done new species to add
            uploadNewSpecies(workbook.getSheetAt(3));
            break;
        case "newSynonyms": // new species synonyms to add
            uploadNewSynonyms(workbook.getSheetAt(5));
            break;
        default:/*  w ww.  j a va2s .c  o m*/
            break;
        }

    } catch (IOException ex) {
        logger.error(ex.getMessage());
    }
}

From source file:se.nrm.dina.dyntaxa.dump.logic.ExcelReader.java

public void readNewTaxonExcel() {
    try (FileInputStream file = new FileInputStream(new File(NEW_TAXAN_EXCEL_FILE_PATH))) {
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        XSSFSheet sheet = workbook.getSheetAt(6);
        int numOfRows = sheet.getLastRowNum();
        IntStream.range(3, numOfRows + 1).forEach(nbr -> {
            XSSFRow row = sheet.getRow(nbr);
            String scientificName = row.getCell(0).getStringCellValue();
            String rank = row.getCell(1).getStringCellValue();
            String author = row.getCell(2).getStringCellValue();
            String parent = row.getCell(3).getStringCellValue();
            String source = row.getCell(4).getStringCellValue();
            String agent = row.getCell(5).getStringCellValue();
            String isManuscript = row.getCell(7).getStringCellValue();

            System.out.println("isManuscript : " + Boolean.valueOf(isManuscript) + isManuscript);

            if (agent.contains(" ")) {
                int index = agent.indexOf(" ");
                System.out.println("last name : " + agent.substring(index + 1));
                System.out.println("first name : " + agent.split(" ")[0]);
            }// ww w. j  a  v  a2 s .c  om
        });

    } catch (IOException ex) {
        logger.error(ex.getMessage());
    }
}

From source file:se.nrm.dina.dyntaxa.dump.logic.ExcelReader.java

public List<TaxonVO> read() {

    logger.info("read excel");
    List<TaxonVO> list = new ArrayList();
    try (FileInputStream file = new FileInputStream(new File(EXCEL_FILE_PATH))) {
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        XSSFSheet sheet = workbook.getSheetAt(0);
        int rowEndNumber = sheet.getLastRowNum();

        IntStream.range(1, rowEndNumber + 1).forEach(nbr -> {
            XSSFRow row = sheet.getRow(nbr);
            String rank = row.getCell(1).getStringCellValue();
            if (rank.equals("Genus") || rank.equals("Subgenus")) {
                currentParent = row.getCell(2).getStringCellValue();
                currentRank = rank;//from  w  ww .  j a  v a 2 s  .c o m
            } else if (rank.equals("Species")) {

                taxonName = row.getCell(2).getStringCellValue();
                TaxonVO vo = new TaxonVO(taxonName, currentParent, currentRank);
                System.out.println(taxonName + " --- " + currentParent + " --- " + currentRank);
                list.add(vo);
            }
        });
        System.out.println("row number : " + rowEndNumber);
    } catch (IOException ex) {
        System.out.println("error: " + ex.getMessage());
    }
    return list;
}

From source file:se.sll.invoicedata.price.GeneratePriceList.java

License:Open Source License

public XSSFSheet readXLSX(String fileName) throws IOException {
    FileInputStream file = new FileInputStream(new File(fileName));
    XSSFWorkbook workbook = new XSSFWorkbook(file);
    return workbook.getSheetAt(0);
}