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

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

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

Introduction

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

Prototype

@Override
public XSSFSheet getSheet(String name)

Source Link

Document

Get sheet with the given name (case insensitive match)

Usage

From source file:com.knsi.PerformanceFrame.java

public void WritePerformance() throws Exception {
    /*just create a new cell at the end of each row at to that add values of the list*/

    XSSFWorkbook details = new XSSFWorkbook(
            new FileInputStream(new File(t4.getSelectedItem().toString() + ".xlsx")));
    XSSFSheet spreadsheet1 = details.getSheet("Performance");
    XSSFRow row;/* ww w .  ja va2 s  .c o  m*/

    Iterator<Row> rowIterator = spreadsheet1.iterator();

    while (rowIterator.hasNext()) {
        row = (XSSFRow) rowIterator.next();
        //Iterator < Cell > cellIterator = row.cellIterator();
        //System.out.println("the last column in this sheet is "+row.getLastCellNum());
        nameList.add(row);
        /*for(int i=0;i<2;i++)
        {
        Cell cell = cellIterator.next();
        if(i==0)
        {
            studentid.setText(cell.getStringCellValue());
        }
        else
        {
            studentname.setText(cell.getStringCellValue());
        }
        System .out.print(cell.getStringCellValue() + " \t\t " );
                
        }*/

        //System .out.println();
    }

}

From source file:com.knsi.WriteDB.java

public static void writeTo(JTextField labels[], String pof) {
    try {/*from w  w  w. java 2 s  .co m*/
        File db = new File(labels[4].getText() + ".xlsx");
        System.out.println(db.getAbsolutePath());

        Date d = new Date();
        System.out.println(d.toString());
        FileInputStream dbStream = new FileInputStream(db);
        XSSFWorkbook details = new XSSFWorkbook(dbStream);
        XSSFSheet spreadsheet1 = details.getSheet("details");
        XSSFSheet spreadsheet2 = details.getSheet("Fees");
        XSSFSheet spreadsheet3 = details.getSheet("Performance");
        XSSFSheet spreadsheet4 = details.getSheet("Attendence");

        System.out.println(spreadsheet1.getLastRowNum() + "------------" + spreadsheet2.getLastRowNum());
        //Create row object
        XSSFRow row1, row2, row3, row4;
        //This data needs to be written (Object[])

        String obj1[] = new String[14];
        int j = 1;
        for (int i = 0; i <= 12; i++) {
            if (i == 4) {
                continue;
            }
            if (labels[i].getText().equalsIgnoreCase("")) {
                obj1[j] = "N/A";
            } else {
                obj1[j] = labels[i].getText();
            }
            j++;
        }
        //obj1[0]=Integer.toString(spreadsheet1.getLastRowNum()+1);
        obj1[0] = getRegId(obj1[0], Integer.toString(spreadsheet1.getLastRowNum() + 1), labels[4].getText(),
                pof);
        obj1[13] = d.toString();
        System.out.println("The details for the details sheet is:");
        for (int i = 0; i < 14; i++) {
            System.out.println(obj1[i]);
        }

        String obj2[] = new String[4];
        obj2[0] = obj1[0];
        obj2[1] = labels[13].getText();
        obj2[2] = labels[14].getText();
        obj2[3] = labels[13].getText();

        System.out.println("The details for the fees sheet is:");
        for (int i = 0; i < 4; i++) {
            System.out.println(obj2[i]);
        }

        String obj3[] = new String[2];
        obj3[0] = obj1[0];
        obj3[1] = obj1[1];

        int rowid1 = spreadsheet1.getLastRowNum() + 1;
        int rowid2 = spreadsheet2.getLastRowNum() + 1;
        int rowid3 = spreadsheet3.getLastRowNum() + 1;
        int rowid4 = spreadsheet4.getLastRowNum() + 1;

        row1 = spreadsheet1.createRow(rowid1);
        row2 = spreadsheet2.createRow(rowid2);
        row3 = spreadsheet3.createRow(rowid3);
        row4 = spreadsheet4.createRow(rowid4);

        int cellid = 0;
        for (String obj : obj1) {
            Cell cell = row1.createCell(cellid++);
            cell.setCellValue(obj);
        }

        cellid = 0;
        for (String obj : obj2) {
            Cell cell = row2.createCell(cellid++);
            cell.setCellValue(obj);
        }

        cellid = 0;
        for (String obj : obj3) {
            Cell cell = row3.createCell(cellid);
            cell.setCellValue(obj);
            Cell cell2 = row4.createCell(cellid);
            cell2.setCellValue(obj);
            cellid++;
        }

        //Write the workbook in file system
        FileOutputStream out = new FileOutputStream(db);
        details.write(out);
        out.close();
        System.out.println(db + " written successfully");
        s = db.getAbsolutePath();
    } catch (Exception ee) {
        s = ee.getMessage();
        System.out.println(s);

    }

}

From source file:com.MainGui.java

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {
    // JOptionPane.showMessageDialog(null, evt.getActionCommand());
    if (evt.getActionCommand().equals("Save Details")) {
        try {//www.  ja v  a 2 s. c o m
            FuntionLibrary fb = new FuntionLibrary();
            File file = new File(excelFileName);
            XSSFWorkbook wb;
            XSSFSheet sheet;
            if (file.exists()) {
                FileInputStream fis = new FileInputStream(file);
                wb = new XSSFWorkbook(fis);
                sheet = wb.getSheet(sheetName);
            } else {
                wb = new XSSFWorkbook();
                sheet = wb.createSheet(sheetName);
            }
            mailId = fb.validateEmail(jTextField1.getText(), sheet);
            pass = fb.validatePassword(jTextField3.getText());
            sheet = wb.getSheet(sheetName);
            mob = fb.validateMobileNO(jFormattedTextField1.getText(), sheet);
            if (mob != null && mailId != null && FuntionLibrary.Pflag == true && FuntionLibrary.Mflag == true
                    && FuntionLibrary.Eflag == true) {
                fb.writetoExcel(jTextField4.getText(), mailId, pass, mob, sheet, jTextField2.getText());
                FileOutputStream fileOut = new FileOutputStream(excelFileName);
                //write this workbook to an Outputstream.
                wb.write(fileOut);
                fileOut.flush();
                fileOut.close();
                System.out.println("Your excel file has been generated!");
                JOptionPane.showMessageDialog(null,
                        " Data Base Updated Successfully !!\n Path of data file is " + excelFileName);
            } else {
                JOptionPane.showMessageDialog(null, "Please enter required fields correctly. Thanks !!");
            }
            jTextField1.setText("");
            jTextField3.setText("");
            jTextField2.setText("");
            jTextField4.setText("");
            jFormattedTextField1.setText("");
        } catch (InvalidFormatException | IOException ex) {
            Logger.getLogger(MainGui.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(null, "Error in Saving Data");
        }
    }
}

From source file:com.MainGui.java

private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {
    EditExistingButtonClicked = true;//from  ww w .  ja v  a  2 s .  co  m
    jButton1.setVisible(false);
    jButton3.setVisible(true);
    jButton4.setVisible(true);
    FileInputStream fis = null;
    try {
        File file = new File(FuntionLibrary.excelFileName);
        if (!(file.exists())) {
            JOptionPane.showMessageDialog(null, "Please Create the DataBase !! DataBase not found.");
        }
        fis = new FileInputStream(file);
        XSSFWorkbook wb = new XSSFWorkbook(fis);
        Mob_Number = JOptionPane.showInputDialog(null, "Enter Mobile Number to find details");
        FuntionLibrary fb = new FuntionLibrary();
        fb.validateMobileNO(Mob_Number, wb.getSheet(sheetName));
        rowNum = FuntionLibrary.findRow(wb.getSheet(FuntionLibrary.sheetName), Mob_Number);
        String arr[] = FuntionLibrary.getData(rowNum, wb.getSheet(FuntionLibrary.sheetName));
        jTextField4.setText(arr[0]);
        jTextField1.setText(arr[1]);
        jTextField3.setText(arr[2]);
        jFormattedTextField1.setText(arr[3]);
        jTextField2.setText(arr[4]);
    } catch (FileNotFoundException ex) {
        Logger.getLogger(MainGui.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(MainGui.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        try {
            fis.close();
        } catch (IOException ex) {
            Logger.getLogger(MainGui.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

From source file:com.MainGui.java

private void jButton4ActionPerformed(java.awt.event.ActionEvent evt) {
    SaveEditedButtonClicked = true;//  w  ww .j a v  a  2  s.c  om
    jButton1.setVisible(false);
    jButton2.setVisible(false);
    FileInputStream fis = null;
    try {
        File file = new File(FuntionLibrary.excelFileName);
        fis = new FileInputStream(file);
        XSSFWorkbook wb = new XSSFWorkbook(fis);
        FuntionLibrary fb = new FuntionLibrary();
        mailId = fb.validateEmail(jTextField1.getText(), wb.getSheet(FuntionLibrary.sheetName));
        pass = fb.validatePassword(jTextField3.getText());
        mob = fb.validateMobileNO(jFormattedTextField1.getText(), wb.getSheet(FuntionLibrary.sheetName));
        FuntionLibrary.updateExcel(rowNum, wb.getSheet(FuntionLibrary.sheetName), jTextField4.getText(), mailId,
                pass, mob, jTextField2.getText());
        FileOutputStream fileOut = new FileOutputStream(excelFileName);
        //write this workbook to an Outputstream.
        wb.write(fileOut);
        fileOut.flush();
        fileOut.close();
    } catch (FileNotFoundException ex) {
        Logger.getLogger(MainGui.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(MainGui.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        try {
            fis.close();
            JOptionPane.showMessageDialog(null,
                    " Data Base Updated Successfully !!\n Path of data file is " + excelFileName);
        } catch (IOException ex) {
            Logger.getLogger(MainGui.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

From source file:com.mycompany.chartproject.ExcelReader.java

public Map<String, Double> getPieChartData(String repo) {
    Map<String, Double> map = new HashMap<>();
    try {//from   w  w w  .j av  a  2  s .c  o  m
        String fileName = "src/main/resources/Stabilityfinal.xlsx";
        String test = fileName;
        //String fileName2 = "src/main/resources/Series.xlsx";
        //String test2 = fileName2;
        FileInputStream file = new FileInputStream(new File(test));

        //Get the workbook instance for XLS file 
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        //Get first sheet from the workbook
        XSSFSheet sheet = workbook.getSheet(repo);

        //Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();
        int total = -1;
        int success = 0;
        int failure = 0;
        int unstable = 0;
        int aborted = 0;

        while (rowIterator.hasNext()) {
            ++total;
            Row row = rowIterator.next();

            //For each row, iterate through each columns
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {

                Cell cell = cellIterator.next();

                switch (cell.getCellType()) {

                case Cell.CELL_TYPE_NUMERIC:

                    if (DateUtil.isCellDateFormatted(cell)) {

                        System.out.println(cell.getDateCellValue() + "\t\t");

                    } else {
                        System.out.print(cell.getNumericCellValue() + "\t\t");

                    }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    System.out.print(cell.getBooleanCellValue() + "\t\t");
                    break;

                case Cell.CELL_TYPE_STRING:
                    if (cell.getStringCellValue().equalsIgnoreCase("SUCCESS")) {
                        ++success;
                    } else if (cell.getStringCellValue().equalsIgnoreCase("FAILURE")) {
                        ++failure;
                    } else if (cell.getStringCellValue().equalsIgnoreCase("UNSTABLE")) {
                        ++unstable;
                    } else if (cell.getStringCellValue().equalsIgnoreCase("ABORTED")) {
                        ++aborted;
                    }
                    System.out.print(cell.getStringCellValue() + "\t\t");
                    break;

                }
            }
            System.out.println("");

            file.close();
            FileOutputStream out = new FileOutputStream(new File(fileName));
            workbook.write(out);
            out.close();
        }
        System.out.println("Total " + total);
        System.out.println("no. Successful " + success);
        System.out.println("no. Failures " + failure);
        System.out.println("no. Unstable " + unstable);

        int green = ((success * 100 / total));
        double passedPercentage = (double) green / 100;
        System.out.println("Passed: " + passedPercentage);

        int red = ((failure * 100 / total));
        double failedPercentage = (double) red / 100;
        System.out.println("Failed: " + failedPercentage);

        int orange = ((unstable * 100 / total));
        double unstablePercentage = (double) orange / 100;
        System.out.println("Unstable: " + unstablePercentage);

        int abort = ((aborted * 100 / total));
        double abortedPercentage = (double) abort / 100;
        System.out.println("Aborted: " + abortedPercentage);

        map.put("Failed", failedPercentage);

        map.put("Unstable", unstablePercentage);

        map.put("Passed", passedPercentage);

        map.put("Aborted", abortedPercentage);

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return map;

}

From source file:com.mycompany.chartproject.ExcelReader.java

public List<ChartSeries> getSeriesChartData(String repo) {
    List<ChartSeries> cs = new ArrayList<>();
    try {//from www. j  av  a  2s .  c  om
        String fileName = "src/main/resources/Series.xlsx";
        String test = fileName;
        //String fileName2 = "src/main/resources/Series.xlsx";
        //String test2 = fileName2;
        FileInputStream file = new FileInputStream(new File(test));

        //Get the workbook instance for XLS file 
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        //Get first sheet from the workbook
        XSSFSheet sheet = workbook.getSheet(repo);

        //Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();

        ChartSeries chartSeries = null;
        while (rowIterator.hasNext()) {
            chartSeries = new ChartSeries();

            Row row = rowIterator.next();
            if (row.getRowNum() == 0) {
                row = rowIterator.next();
            }

            //For each row, iterate through each columns
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {

                Cell cell = cellIterator.next();

                switch (cell.getCellType()) {

                case Cell.CELL_TYPE_NUMERIC:
                    //System.out.println("numeric");
                    switch (cell.getColumnIndex()) {
                    case 1:
                        chartSeries.setTotal((int) cell.getNumericCellValue());
                        break;
                    case 2:
                        chartSeries.setPassed((int) cell.getNumericCellValue());
                        break;
                    case 3:
                        chartSeries.setFailed((int) cell.getNumericCellValue());
                        break;
                    case 4:
                        chartSeries.setSkipped((int) cell.getNumericCellValue());
                        break;
                    }

                    System.out.println(cell.getDateCellValue() + "\t\t");
                    System.out.print(cell.getNumericCellValue() + "\t\t");

                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    System.out.print(cell.getBooleanCellValue() + "\t\t");
                    break;

                case Cell.CELL_TYPE_STRING:

                    chartSeries.setDate(cell.getStringCellValue());
                    System.out.print(cell.getStringCellValue() + "\t\t");
                    break;

                }
            }
            System.out.println("");
            cs.add(chartSeries);

        }

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return cs;

}

From source file:com.mycompany.exceldatetimetest.test.java

public static void main(String[] args) throws Exception {
    //get date-lime from excel
    File file = new File("Book1.xlsx");
    XSSFWorkbook xssfwb = new XSSFWorkbook(file);
    Sheet sh = xssfwb.getSheet("?1");
    Date date = sh.getRow(1).getCell(0).getDateCellValue();
    System.err.println(date);//from   w  w  w .j  a  v  a 2 s. c om

    //save to db

}

From source file:com.photon.phresco.eshop.service.EShopPublishService.java

License:Apache License

public List<CategoryHBM> getCategoriesFromExcel() throws Exception {
    System.out.println("Inside getCategoriesFromExcel");
    XSSFWorkbook workbook = new XSSFWorkbook(is);
    System.out.println("workbook = " + workbook);
    XSSFSheet sheet = workbook.getSheet(EXCEL_SHEET_CATEGORIES);
    System.out.println("Sheet = " + sheet);
    List<CategoryHBM> categories = new ArrayList<CategoryHBM>(10);
    Iterator<Row> itr = sheet.rowIterator();
    System.out.println("itr " + itr);
    for (int i = 0; i < 1; i++) {
        itr.next();/*from  w w w  .j a v  a 2 s.  co  m*/
    }
    while (itr.hasNext()) {
        try {
            Row row = itr.next();
            System.out.println("Row 0 value" + row.getCell(0));
            int categoryId = (int) row.getCell(0).getNumericCellValue();
            String categoryName = ServiceUtil.getValue(row.getCell(1));
            String categoryImage = ServiceUtil.getValue(row.getCell(2));
            String categoryDetailsImage = ServiceUtil.getValue(row.getCell(3));
            int categoryParentId = 0;
            String categoryDescription = ServiceUtil.getValue(row.getCell(5));

            CategoryHBM category = new CategoryHBM(categoryId, categoryName, categoryImage,
                    categoryDetailsImage, categoryParentId, categoryDescription);
            categories.add(category);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    return categories;
}

From source file:com.photon.phresco.eshop.service.EShopPublishService.java

License:Apache License

public List<ProductHBM> getProductsFromExcel() throws Exception {
    XSSFWorkbook workbook = new XSSFWorkbook(is);
    XSSFSheet sheet = workbook.getSheet(EXCEL_SHEET_PRODUCTS);
    List<ProductHBM> products = new ArrayList<ProductHBM>(10);
    Iterator<Row> itr = sheet.rowIterator();

    for (int i = 0; i < 1; i++) {
        itr.next();//from   w w w  .j  a  v a 2 s.c  om
    }

    while (itr.hasNext()) {
        try {
            Row row = itr.next();
            int productId = (int) row.getCell(0).getNumericCellValue();
            String productName = ServiceUtil.getValue(row.getCell(1));
            System.out.println("productId = " + productId);
            String manufacturer = ServiceUtil.getValue(row.getCell(2));
            int categoryId = (int) row.getCell(3).getNumericCellValue();
            System.out.println("categoryId = " + categoryId);
            String model = ServiceUtil.getValue(row.getCell(4));
            String specialProductStr = ServiceUtil.getValue(row.getCell(5));
            String newProductStr = ServiceUtil.getValue(row.getCell(6));
            Integer specialProduct = (YES.equals(specialProductStr) ? 1 : 0);
            Integer newProduct = (YES.equals(newProductStr) ? 1 : 0);
            Double listPrice = row.getCell(7).getNumericCellValue();
            System.out.println("listPrice = " + listPrice);
            Double sellPrice = row.getCell(8).getNumericCellValue();
            System.out.println("sellPrice = " + sellPrice);
            String productDescription = ServiceUtil.getValue(row.getCell(9));
            String productImage = ServiceUtil.getValue(row.getCell(10));
            String productDetailImage = ServiceUtil.getValue(row.getCell(11));
            Date createdDate = new Date();
            Date lastUpdatedDate = new Date();

            ProductHBM product = new ProductHBM(productId, productName, manufacturer, categoryId, model,
                    specialProduct, newProduct, listPrice, sellPrice, productDescription, productImage,
                    productDetailImage, createdDate, lastUpdatedDate);
            products.add(product);
        } catch (Exception e) {
            System.out.println("Error: " + e.getMessage());
        }
    }

    return products;
}