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:in.expertsoftware.colorcheck.VerifyWorkbook.java

/**
 * @param args the command line arguments
 *//*from  ww  w .ja  v  a 2 s.  com*/
public static void main(String[] args) throws FileNotFoundException, IOException {
    XSSFWorkbook workbook = null;
    FileInputStream DIMT_Sheet = new FileInputStream(new File(args[0]));
    try {
        workbook = new XSSFWorkbook(DIMT_Sheet);
    } catch (IOException ex) {
        ex.printStackTrace();
    }
    VerifyTokens verifytokens = new VerifyTokens();
    ArrayList get_List = verifytokens.start(workbook);
    ArrayList<ErrorModel> get_errormodelList = (ArrayList) get_List.get(0);
    ArrayList<TokenModel> get_tokenmodelList = (ArrayList) get_List.get(1);

    ///////////////// /*verify the metadata poaition of Reporting_Qtr*///////////////////
    Metadata_Position_Of_Reporting_Qtr metadataposition_qtr = new Metadata_Position_Of_Reporting_Qtr();
    int Reporting_Qtr_index = workbook.getSheetIndex("Reporting_Qtr");
    if (!(metadataposition_qtr.verify_SORQ_metadata_posiition(workbook.getSheetAt(Reporting_Qtr_index),
            get_tokenmodelList))) {
        ErrorModel errmodel = new ErrorModel();
        errmodel.setError_desc("SORQ Token location is not correct");
        errmodel.setSheet_name("Reporting_Qtr");
        errmodel.setError_level("Error");
        get_errormodelList.add(errmodel);
    }
    if (!(metadataposition_qtr.verify_EORQ_metadata_posiition(workbook.getSheetAt(Reporting_Qtr_index),
            get_tokenmodelList))) {
        ErrorModel errmodel = new ErrorModel();
        errmodel.setError_desc("EORQ Token location is not correct");
        errmodel.setSheet_name("Reporting_Qtr");
        errmodel.setError_level("Error");
        get_errormodelList.add(errmodel);
    }

    /////////////////////// /*verify the metadata poaition of Reporting_Year*///////////////////
    Metadata_Position_Of_Reporting_Year metadataposition_year = new Metadata_Position_Of_Reporting_Year();
    int Reporting_Year_index = workbook.getSheetIndex("Reporting_Year");
    if (!(metadataposition_year.verify_SORY_metadata_posiition(workbook.getSheetAt(Reporting_Year_index),
            get_tokenmodelList))) {
        ErrorModel errmodel = new ErrorModel();
        errmodel.setError_desc("SORY Token location is not correct");
        errmodel.setSheet_name("Reporting_Year");
        errmodel.setError_level("Error");
        get_errormodelList.add(errmodel);
    }
    if (!(metadataposition_year.verify_EORY_metadata_posiition(workbook.getSheetAt(Reporting_Year_index),
            get_tokenmodelList))) {
        ErrorModel errmodel = new ErrorModel();
        errmodel.setError_desc("EORY Token location is not correct");
        errmodel.setSheet_name("Reporting_Year");
        errmodel.setError_level("Error");
        get_errormodelList.add(errmodel);
    }
    int SORQtokenRow = 0;
    int EORQtokenRow = 0;
    int SORYtokenRow = 0;
    int EORYtokenRow = 0;
    int SOOWDtokenRow = 0;
    int EOOWDtokenRow = 0;
    int SOFWDtokenRow = 0;
    int EOFWDtokenRow = 0;
    for (int i = 0; i < get_tokenmodelList.size(); i++) {
        System.out.print(
                get_tokenmodelList.get(i).token_name + "row index=" + get_tokenmodelList.get(i).row_no + "\n");
        if (get_tokenmodelList.get(i).token_name.equals("SORQ"))
            SORQtokenRow = (get_tokenmodelList.get(i).row_no);
        else if (get_tokenmodelList.get(i).token_name.equals("EORQ"))
            EORQtokenRow = (get_tokenmodelList.get(i).row_no);

        else if (get_tokenmodelList.get(i).token_name.equals("SORY"))
            SORYtokenRow = (get_tokenmodelList.get(i).row_no);
        else if (get_tokenmodelList.get(i).token_name.equals("EORY"))
            EORYtokenRow = (get_tokenmodelList.get(i).row_no);

        else if (get_tokenmodelList.get(i).token_name.equals("SOOWD"))
            SOOWDtokenRow = (get_tokenmodelList.get(i).row_no);
        else if (get_tokenmodelList.get(i).token_name.equals("EOOWD"))
            EOOWDtokenRow = (get_tokenmodelList.get(i).row_no);
        else if (get_tokenmodelList.get(i).token_name.equals("SOFWD"))
            SOFWDtokenRow = (get_tokenmodelList.get(i).row_no);
        else if (get_tokenmodelList.get(i).token_name.equals("EOFWD"))
            EOFWDtokenRow = (get_tokenmodelList.get(i).row_no);
    }
    ArrayList<String> opeartion_standard_workingSectionList = new Operation_Standard_WorkingSection_count()
            .working_Section(SOOWDtokenRow, EOOWDtokenRow, workbook);
    ArrayList<String> financial_standard_workingSectionList = new Financial_Standard_WorkingSection_count()
            .working_Section(SOFWDtokenRow, EOFWDtokenRow, workbook);

    /*ArrayList<ErrorModel> ls=(new Reporting_Qtr_Verification()).startReporting_QtrVerification(SORQtokenRow,EORQtokenRow,opeartion_standard_workingSectionList,financial_standard_workingSectionList ,workbook);
    ls.stream().forEach((errormodel) -> {
     System.out.println("ccelref "+errormodel.cell_ref+" sheetname "+errormodel.sheet_name+" dis "+errormodel.error_desc);
        });  */
    ArrayList<ErrorModel> ls = (new Reporting_Year_Verification()).startReporting_YearVerification(SORYtokenRow,
            EORYtokenRow, opeartion_standard_workingSectionList, financial_standard_workingSectionList,
            workbook);
    ls.stream().forEach((errormodel) -> {
        System.out.println("ccelref " + errormodel.cell_ref + " sheetname " + errormodel.sheet_name + " dis "
                + errormodel.error_desc);
    });
    ///error infomation on log
    get_errormodelList.stream().forEach((errormodel) -> {
        if (errormodel.row == -1) {
            System.out.println(errormodel.error_desc + " On sheet " + errormodel.sheet_name);
        } else if (errormodel.row == -2) {
            System.out.println(errormodel.error_desc + errormodel.sheet_name);
        } else {
            System.out.println("In " + errormodel.sheet_name + errormodel.error_desc + " at row"
                    + errormodel.row + " and at colum" + errormodel.col);
        }
    });
    //finally dump the error report to the Exxcel file 
    //new FormatvarificationErrorList().dumpFormatErrorToExcelFile(ls);    
}

From source file:info.informationsea.tableio.excel.test.ExcelSheetWriterTest.java

License:Open Source License

@Test
public void testWriter() throws Exception {
    XSSFWorkbook workbook = new XSSFWorkbook();
    ExcelSheetWriter excelSheetWriter = new ExcelSheetWriter(workbook.createSheet());
    excelSheetWriter.printAll(Arrays.asList(data));

    ExcelSheetReader excelSheetReader = new ExcelSheetReader(workbook.getSheetAt(0));
    commonAssert(excelSheetReader);//  www. j av  a  2  s  . com
}

From source file:info.toegepaste.controller.UploadController.java

public void fileUploadListener(FileUploadEvent e) {

    // Get uploaded file from the FileUploadEvent to use with primefaces
    this.file = e.getFile();
    // Get uploaded file to use with Apache POI
    InputStream POIFile;//w w w.j a v  a 2s . co m
    XSSFWorkbook workbook = null;
    try {
        POIFile = e.getFile().getInputstream();
        //Create workbook
        workbook = new XSSFWorkbook(POIFile);
    } catch (IOException ex) {

    }
    // Print out the information of the file
    System.out.println(
            "Uploaded File Name Is :: " + file.getFileName() + " :: Uploaded File Size :: " + file.getSize());
    //Create a worksheet (needed to get rows)
    XSSFSheet worksheet = workbook.getSheetAt(0);
    //Divide worksheet into rows
    Iterator<Row> rowIterator = worksheet.iterator();
    //Get Classgroup from line 1 cell 2
    XSSFRow currentRow = (XSSFRow) rowIterator.next();
    Iterator<Cell> klasIter = currentRow.cellIterator();
    XSSFCell klasCell = currentRow.getCell(1);
    //Get Course from line 2 cell 2
    currentRow = (XSSFRow) rowIterator.next();
    Iterator<Cell> courseIter = currentRow.cellIterator();
    XSSFCell courseCell = currentRow.getCell(1);
    //Get subject from line 3 cell 2
    System.out.println("Stuff");
    currentRow = (XSSFRow) rowIterator.next();
    Iterator<Cell> subjectIter = currentRow.cellIterator();
    XSSFCell subjectCell = currentRow.getCell(1);
    System.out.println("Subject:" + subjectCell.toString());
    System.out.println("Subject:" + subjectCell.getStringCellValue());
    //Get total possible score from line 4 cell 2
    currentRow = (XSSFRow) rowIterator.next();
    Iterator<Cell> totalScoreIter = currentRow.cellIterator();
    XSSFCell totalScoreCell = currentRow.getCell(1);
    //Skip line 5 & 6
    currentRow = (XSSFRow) rowIterator.next();
    currentRow = (XSSFRow) rowIterator.next();
    currentRow = (XSSFRow) rowIterator.next();
    List<Classgroup> group = classgroupService.getWithName(klasCell.getStringCellValue());

    //Persist new  exam to database
    Exam newExam = new Exam();
    Classgroup newGroup = new Classgroup();
    //Check if classgroup already exists, create if it doesnt
    if (group.isEmpty()) {

        newGroup.setName(klasCell.getStringCellValue());
        newGroup.setCourses(null);
        classgroupService.insert(newGroup);

    } else {
        newGroup = group.get(0);

    }

    newExam.setClassgroup(newGroup);
    List<Course> course = courseService.getWithName(courseCell.getStringCellValue());
    Course newCourse = new Course();
    //Check if course exists, if not create
    if (course.isEmpty()) {
        newCourse.setName(courseCell.getStringCellValue());
        int year = Calendar.getInstance().get(Calendar.YEAR);
        newCourse.setYear(year);
        newCourse.setClassgroup(newGroup);
        int maand = Calendar.getInstance().get(Calendar.MONTH);
        if (maand <= 6 && maand >= 1) {
            newCourse.setSemester(2);
        } else {
            newCourse.setSemester(1);
        }
        courseService.insert(newCourse);
    } else {
        newCourse = course.get(0);
    }

    newExam.setCourse(newCourse);

    newExam.setName(subjectCell.getStringCellValue());
    // double totalScoreValue = ;
    // String totalScoreWorkaround =String.valueOf(totalScoreValue);
    newExam.setTotal((int) totalScoreCell.getNumericCellValue());
    examService.insert(newExam);

    //Read file to end, cell 0 student number, cell 1 name, cell 2 score
    while (rowIterator.hasNext()) {
        XSSFCell userNrCell = currentRow.getCell(0);
        System.out.println(userNrCell.toString());
        int StudentNumber = 0;
        // String StudentNumberWorkaround =userNrCell.getStringCellValue();
        StudentNumber = (int) userNrCell.getNumericCellValue();
        List<Student> currentStudent = studentsService.getStudentInListByNumber(StudentNumber);
        Student newStudent = new Student();
        XSSFCell userNameCell = currentRow.getCell(1);
        //Check if student exists, else create
        if (currentStudent.isEmpty()) {

            String fullName = userNameCell.getStringCellValue();
            String nameArray[] = fullName.split(" ");
            newStudent.setFirstname(nameArray[0]);
            newStudent.setLastname(nameArray[1]);
            newStudent.setNumber(StudentNumber);
            newStudent.setEmail("r0" + StudentNumber + "@student.thomasmore.be");
            newStudent.setClassgroup(newGroup);
            newStudent.setPassword(null);
            studentsService.insert(newStudent);

        } else {
            newStudent = currentStudent.get(0);
        }
        //Add score to student
        List<Score> currentScore = scoreService.checkIfScoreExists(newStudent, newExam);
        XSSFCell scoreCell = currentRow.getCell(2);
        Score scoreEntry = new Score();
        if (currentScore.isEmpty()) {

            scoreEntry.setExam(newExam);
            scoreEntry.setScore((int) scoreCell.getNumericCellValue());
            if (currentStudent.isEmpty()) {
                scoreEntry.setStudent(newStudent);
            } else {
                scoreEntry.setStudent(currentStudent.get(0));
            }

            scoreService.insert(scoreEntry);
        } else {
            scoreEntry = currentScore.get(0);
        }

        currentRow = (XSSFRow) rowIterator.next();
    }

}

From source file:info.toegepaste.www.service.ExcelServiceImpl.java

@Override
@TransactionAttribute(REQUIRES_NEW)/*from   www  . ja v  a  2  s.c  o m*/
public String upload(Part file) {
    try {
        //declaratie variabelen
        //Variabelen voor tijdelijke gegevens
        int cellInt;
        String cellString;
        int cellNr;
        String infoCel = " ";
        int scoresTeller = 1;
        String fout = " ";

        //debug of overzetvariabelen
        String klasDebug = " ";
        String vakDebug = " ";
        String testDebug = " ";
        int totaalDebug = 0;
        List<Integer> studentennrDebug = new ArrayList<Integer>();
        List<String> naamDebug = new ArrayList<String>();
        List<Integer> scoreDebug = new ArrayList<Integer>();

        Iterator<Row> rowIterator = null;

        if (file.getSubmittedFileName().contains("xlsx")) {
            //lees de content stream in naar de hssfworkbook
            XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
            //kies de juiste pagina (eerste)
            XSSFSheet sheet = workbook.getSheetAt(0);
            rowIterator = sheet.iterator();
        } else if (file.getSubmittedFileName().contains("xls")) {
            //lees de content stream in naar de hssfworkbook
            HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
            //kies de juiste pagina (eerste)
            HSSFSheet sheet = workbook.getSheetAt(0);
            rowIterator = sheet.iterator();
        } else {
            fout = "Het geuploadde bestand heeft niet de juiste indeling";

        }
        if (rowIterator != null) {
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                //Leest elke horizontale lijn van links naar rechts uit in de console
                Iterator<Cell> cellIterator = row.cellIterator();

                cellNr = 0;
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    cellNr++;
                    //Check the cell type and format accordingly
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.print(cell.getNumericCellValue());
                        cellInt = (int) cell.getNumericCellValue();

                        if (infoCel.equals("totaal")) {
                            totaalDebug = cellInt;
                        } else {
                            if (scoresTeller == 1) {
                                studentennrDebug.add(cellInt);
                                scoresTeller++;
                            } else {
                                scoreDebug.add(cellInt);
                                scoresTeller = 1;
                            }
                        }
                        break;
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell.getStringCellValue());
                        cellString = cell.getStringCellValue();

                        //Als er een titel in de cel zit en steek in infoCel
                        if (cellString.toLowerCase().equals("vak") || cellString.toLowerCase().equals("klas")
                                || cellString.toLowerCase().equals("test")
                                || cellString.toLowerCase().equals("totaal")
                                || cellString.toLowerCase().equals("score")) {
                            infoCel = cellString.toLowerCase();
                        } else {
                            //inhoud in de cell
                            switch (infoCel) {
                            case "klas":
                                klasDebug = cellString;
                                break;
                            case "vak":
                                vakDebug = cellString;
                                break;
                            case "test":
                                testDebug = cellString;
                                break;
                            case "score":
                                naamDebug.add(cellString);
                                scoresTeller++;
                                break;
                            default:
                                break;
                            }
                        }
                        break;
                    }
                }
            }
        }
        System.out.println("");
        int testId = 0;
        int vakId = 0;
        int studentId = 0;

        try {
            //als er al een test is aangemaakt met die naam, voeg gewoon punten toe
            testId = getTestId(testDebug);
        } catch (Exception e) {
            try {
                //als er al een vak is aangemaakt met die naam, voeg gewoon een vak toe
                vakId = getVakId(vakDebug);
            } catch (Exception e1) {
                insertVak(vakDebug);
                vakId = getVakId(vakDebug);
            }
            //maak een nieuwe test aan
            insertTest(vakId, testDebug, totaalDebug);
            testId = getTestId(testDebug);
        }
        //Scores toevoegen
        int index = 0;
        for (int studentenNr : studentennrDebug) {
            studentId = getStudentId(studentenNr);
            insertScore(studentId, testId, scoreDebug.get(index), totaalDebug);
            index++;
        }

        return fout;
    } catch (IOException e) {
        // Error handling
        return "Er is iets misgelopen bij het inlezen van het bestand.";
    }
}

From source file:Interface.StateBodyEmployee.StateEmployeeWorkAreaJPanel.java

public void readFromExcel() {

    try {// w  w  w .  j  a va  2  s .  com
        FileInputStream file = new FileInputStream(new File("sensorData.xlsx"));
        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        //Get first/desired sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);
        //Iterate through each rows one by one
        Iterator<org.apache.poi.ss.usermodel.Row> rowIterator = sheet.iterator();

        DataFormatter df = new DataFormatter();

        while (rowIterator.hasNext()) {
            sensorCounter++;
            org.apache.poi.ss.usermodel.Row row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();
            for (a = 0; a < 6; a++) {
                i = (a % 6);

                //System.out.print(row.getCell(i)+"\t");
                switch (i) {
                case 0:
                    location = df.formatCellValue(row.getCell(i));
                    //                            System.out.println("location= "+location);
                    break;
                case 1:
                    waterUsage = Double.parseDouble(df.formatCellValue(row.getCell(i)));
                    //                            System.out.println("waterUsage= "+waterUsage);
                    break;
                case 2:
                    waterStorageCapacity = Double.parseDouble(df.formatCellValue(row.getCell(i)));
                    //                            System.out.println("waterStorageCapacity= "+waterStorageCapacity);
                    break;
                case 3:
                    triggerPercentage = Double.parseDouble(df.formatCellValue(row.getCell(i)));
                    //                            System.out.println("triggerPercentage= "+triggerPercentage);
                    break;
                case 4:
                    criticalPercentage = Double.parseDouble(df.formatCellValue(row.getCell(i)));
                    //                            System.out.println("criticalPercentage= "+criticalPercentage);
                    break;
                case 5:
                    soilType = df.formatCellValue(row.getCell(i));
                    //                            System.out.println("location= "+soilType);
                    break;
                }
            }
            if (sensorCounter == 1) {
                s1 = new Sensor(location, waterUsage, waterStorageCapacity, triggerPercentage,
                        criticalPercentage, soilType, enterprise);
            }
            if (sensorCounter == 2) {
                s2 = new Sensor(location, waterUsage, waterStorageCapacity, triggerPercentage,
                        criticalPercentage, soilType, enterprise);
            }
            if (sensorCounter == 3) {
                s3 = new Sensor(location, waterUsage, waterStorageCapacity, triggerPercentage,
                        criticalPercentage, soilType, enterprise);
            }
            if (sensorCounter == 4) {
                s4 = new Sensor(location, waterUsage, waterStorageCapacity, triggerPercentage,
                        criticalPercentage, soilType, enterprise);
            }
            if (sensorCounter == 5) {
                s5 = new Sensor(location, waterUsage, waterStorageCapacity, triggerPercentage,
                        criticalPercentage, soilType, enterprise);
            }

        }

        file.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:it.vige.greenarea.file.ImportaXLSFile.java

License:Apache License

@Override
public List<RichiestaXML> prelevaDati(InputStream inputStream, List<Filtro> filtri) throws Exception {
    if (filtri != null)
        acceptedRoundCodes.addAll(filtri);
    XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
    Sheet sheet = workbook.getSheetAt(0);

    int rowsCount = sheet.getPhysicalNumberOfRows();
    List<RichiestaXML> richiesteXML = new ArrayList<RichiestaXML>();
    for (int i = 1; i < rowsCount; i++) {
        Row row = sheet.getRow(i);// w  ww . j  a  v  a 2  s .  c o m
        int colCounts = row.getLastCellNum();
        RichiestaXML richiestaXML = new RichiestaXML();
        for (int j = 0; j < colCounts; j++) {
            Cell cell = row.getCell(j);
            if (cell != null)
                aggiungiCampoARichiestaXML(richiestaXML, cell, j);
        }
        String roundCode = richiestaXML.getRoundCode();
        if (acceptRoundCode(roundCode))
            richiesteXML.add(richiestaXML);
    }
    workbook.close();
    return richiesteXML;
}

From source file:javaapplication1.BackTrack.java

private void btnBackTrackActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnBackTrackActionPerformed
    // TODO add your handling code here:
    //  Excel ob = null;
    /*   int len[][]=(int[][]) Excel.data.toArray();
               /*  ww w.  j  a v a2s. co  m*/
       for(int i=0 ;i<len.length ;i++)*/
    int i = 1, j = 1;
    float samplesec = Float.parseFloat(txtSamples.getText());
    if (samplesec == 0) {
        samplesec = 1;
    }
    float th = Float.parseFloat(txtThreshold.getText());

    try {
        DefaultTableModel defmodel = new DefaultTableModel();

        tblTH.setModel(defmodel);
        defmodel.setColumnIdentifiers(new Object[] { "Seconds", "Value" });

        String temp;
        temp = txtFilename.getText() + ".xlsx";
        FileInputStream file = new FileInputStream(new File(temp));

        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        //Get first/desired sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);

        //Iterate through each rows one by one
        Iterator<Row> rowIterator = sheet.iterator();
        // int i=1,j=1;
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            //For each row, iterate through all the columns
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                //Check the cell type and format accordingly
                dataTemp.add(cell.getNumericCellValue());
                //  System.out.print(dataTemp.get(0).toString());
                if (cell.getNumericCellValue() > th) {

                    defmodel.addRow(new Object[] { (i / samplesec), cell.getNumericCellValue() });
                }
                i++;

            }
            j++;
            data.add(dataTemp);

        }
        file.close();
    } catch (Exception e) {
        e.printStackTrace();
    }

}

From source file:javaapplication1.Excel.java

private void btnImportActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnImportActionPerformed
    // TODO add your handling code here:
    try {/* www  . j  av a  2  s  .  c o m*/
        String temp;
        temp = txtfileName.getText() + ".xlsx";
        FileInputStream file = new FileInputStream(new File(temp));

        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        //Get first/desired sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);

        //Iterate through each rows one by one
        Iterator<Row> rowIterator = sheet.iterator();
        int i = 1, j = 1;
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            //For each row, iterate through all the columns
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                //Check the cell type and format accordingly
                dataTemp.add(cell.getNumericCellValue());
                //  System.out.print(dataTemp.get(0).toString());
                if (j == 1) {
                    col.add("S" + i);
                    i++;
                }
            }
            j++;
            data.add(dataTemp);

        }
        file.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
    printData();
}

From source file:javafx.JavaFX.java

public void initialize() throws Exception {
    tree.setEditable(true);/*from  w w  w  .  j  a v a  2 s  .  c o m*/
    tree.setShowRoot(false);
    TreeItem<TRow> root = new TreeItem<>(new TRow("", "", "", "", ""));
    root.setExpanded(true);

    File s = new File("C:\\Users\\Markus\\Desktop\\Prism.xlsx");

    FileInputStream file = new FileInputStream(s);
    XSSFWorkbook workbook = new XSSFWorkbook(file);
    XSSFSheet sheet = workbook.getSheetAt(0);
    Iterator<Row> rowIter = sheet.iterator();
    TreeItem<TRow> level1 = new TreeItem<>();
    TreeItem<TRow> level2 = new TreeItem<>();

    while (rowIter.hasNext()) {
        Row r = rowIter.next();
        String[] values = getValues(r); // 0=Hierarchie, 1=Pruefpunkt, 2=Typ, 3=Einheit, 4=Min, 5=Max

        if (values[0].matches("\\w")) {
            level1 = new TreeItem<>(new TRow(values[1], "", "", "", ""));
            root.getChildren().add(level1);
        }

        if (values[0].matches("\\w[0-9]")) {
            level2 = new TreeItem<>(new TRow(values[1], "", "", "", ""));
            level1.getChildren().add(level2);
        }

        if (values[0].matches("\\w[0-9]\\|[0-9]+")) {
            level2.getChildren().add(new TreeItem<>(
                    new TRow(values[1], "", values[3], formatMinMax(values[4], values[5]), "asd")));
        }

    }

    C1.setCellValueFactory((CellDataFeatures<TRow, String> param) -> new SimpleStringProperty(
            param.getValue().getValue().getPruefpunkt()));

    C2.setCellFactory(TextFieldTreeTableCell.forTreeTableColumn());
    C2.setCellValueFactory((CellDataFeatures<TRow, String> param) -> new SimpleStringProperty(
            param.getValue().getValue().getMesswert()));
    C2.setOnEditCommit((TreeTableColumn.CellEditEvent<TRow, String> evt) -> {
        String nv = evt.getNewValue();
        evt.getRowValue().getValue().setMesswert(nv);
    });

    C3.setCellValueFactory((CellDataFeatures<TRow, String> param) -> new SimpleStringProperty(
            param.getValue().getValue().getEinheit()));
    C4.setCellValueFactory((CellDataFeatures<TRow, String> param) -> new SimpleStringProperty(
            param.getValue().getValue().getMinMax()));

    C4.setCellValueFactory((CellDataFeatures<TRow, String> param) -> new SimpleStringProperty(
            param.getValue().getValue().getMinMax()));

    C5.setCellValueFactory((CellDataFeatures<TRow, String> param) -> new SimpleStringProperty(
            param.getValue().getValue().getStatus()));
    C5.setCellFactory(ComboBoxTreeTableCell.forTreeTableColumn("Pass", "Fail"));
    C5.setOnEditCommit((TreeTableColumn.CellEditEvent<TRow, String> evt) -> {
        String nv = evt.getNewValue();
        evt.getRowValue().getValue().setStatus(nv);
    });
    but.addEventHandler(MouseEvent.MOUSE_CLICKED, (event) -> {
        System.out.println("asdasd231231");
    });
    C6.setCellValueFactory((CellDataFeatures<TRow, String> param) -> new SimpleStringProperty(
            param.getValue().getValue().getBemerkung()));

    Callback<TreeTableColumn<TRow, String>, TreeTableCell<TRow, String>> cellFactory = new Callback<TreeTableColumn<TRow, String>, TreeTableCell<TRow, String>>() {
        public TreeTableCell call(TreeTableColumn p) {
            return new EditTreeTableCell();
        }
    };

    C6.setCellFactory(cellFactory);

    tree.setRoot(root);
}

From source file:javafxapplication3.FXMLDocumentController.java

public static void readXLSXFile() throws IOException {

    InputStream ExcelFileToRead;//  w w  w.j a  va 2 s  . c o m
    ExcelFileToRead = new FileInputStream("C:\\Users\\IBM_ADMIN\\Desktop\\reimbursement.xlsx");
    XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead);
    System.out.println(wb);

    XSSFWorkbook test = new XSSFWorkbook();

    XSSFSheet sheet = wb.getSheetAt(0);
    XSSFRow row;
    XSSFCell cell;

    Iterator rows = sheet.rowIterator();
    ObservableList<ObservableList> csvData = FXCollections.observableArrayList();
    while (rows.hasNext()) {
        ObservableList<String> amzrow = FXCollections.observableArrayList();
        row = (XSSFRow) rows.next();
        Iterator cells = row.cellIterator();
        while (cells.hasNext()) {
            cell = (XSSFCell) cells.next();

            if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                amzrow.add(cell.getStringCellValue());
                //System.out.print(cell.getStringCellValue()+" ");
            } else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                //amzrow.add(cell.getNumericCellValue()); 
                //System.out.print(cell.getNumericCellValue()+" ");
            } else {
                //U Can Handel Boolean, Formula, Errors
            }
        }
        csvData.add(amzrow);
    }
    //tableview.getItems().add(csvData);

}