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.anritsu.mcrepositorymanager.packageinfoparser.RSSParser.java

public void parseRSS() {
    try {//from  w ww . ja  v  a  2 s .  c o  m
        FileInputStream file = new FileInputStream(new File(this.filePath));

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

        //Get package list sheet from the workbook
        String sheetName = "MC " + mcVersion;
        XSSFSheet sheet = workbook.getSheet(sheetName);

        //Iterate through each rows one by one
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            if (row.getRowNum() < 3) {
                continue;
            }
            McPackage p = new McPackage();
            p.setMcVersion(mcVersion);
            p.setName(row.getCell(2).getStringCellValue());
            HashSet<String> downloadLinks = new HashSet<>();
            try {
                String link = row.getCell(7).getHyperlink().getAddress();
                downloadLinks.add(link);
                int urlIndex = link.split("/").length;
                String fileName = link.split("/")[urlIndex - 1];
                p.setFileName(fileName);
            } catch (NullPointerException exp) {
                exp.printStackTrace();
            } finally {
                p.setDownloadLinks(downloadLinks);
            }

            p.setPackageVersion(row.getCell(3).getStringCellValue());
            p.setAvailability(row.getCell(4).getStringCellValue());
            availability.add(row.getCell(4).getStringCellValue());

            // Set customers list
            ArrayList<String> cusList = new ArrayList<>();
            String[] customerCell = row.getCell(5).getStringCellValue().split(",");
            for (int i = 0; i < customerCell.length; i++) {
                customers.add(customerCell[i]);
                cusList = new ArrayList<>(Arrays.asList(customerCell));
            }
            //System.out.println("Parsing line " + row.getRowNum());
            LOGGER.log(Level.INFO, "Parsing " + this.filePath + "/" + row.getRowNum());

            p.setCustomerList(new HashSet<>(cusList));
            packageList.add(p);
        }
        file.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:com.anritsu.mcrepositorymanager.utils.GenerateRSS.java

public String getRSS() {
    FileInputStream file = null;//from w w w.  jav  a 2s.  co  m
    String rssFileName = rssTemplateFileName.replaceAll("template", mcVersion);
    try {
        file = new FileInputStream(
                new File(Configuration.getInstance().getRssTemplatePath() + rssTemplateFileName));
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        workbook.setSheetName(workbook.getSheetIndex("MC X.X.X"), "MC " + mcVersion);
        XSSFSheet sheet = workbook.getSheet("MC " + mcVersion);
        CreationHelper createHelper = workbook.getCreationHelper();

        Cell cell = null;

        // Update the sheet title
        cell = sheet.getRow(0).getCell(0);
        cell.setCellValue(cell.getStringCellValue().replaceAll("template", mcVersion));

        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);

        XSSFCellStyle hlinkstyle = workbook.createCellStyle();
        XSSFFont hlinkfont = workbook.createFont();
        hlinkfont.setUnderline(XSSFFont.U_SINGLE);
        hlinkfont.setColor(HSSFColor.BLUE.index);
        hlinkstyle.setFont(hlinkfont);
        hlinkstyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        hlinkstyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
        hlinkstyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
        hlinkstyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);

        XSSFCellStyle dateCellStyle = workbook.createCellStyle();
        dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd-MMMM-yyyy"));
        dateCellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        dateCellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
        dateCellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
        dateCellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);

        // Populate the table
        int rowCount = 1;
        for (RecommendedMcPackage rmcp : sortedMcPackages) {
            if (rmcp.getRecommendedVersion() != null && rmcp.isShowInTable()) {
                Row row = sheet.createRow(rowCount + 1);
                rowCount++;

                cell = row.createCell(0);
                cell.setCellValue(rmcp.getTier().replaceAll("Anritsu/MasterClaw/", ""));
                cell.setCellStyle(cellStyle);

                cell = row.createCell(1);
                cell.setCellValue(rmcp.getGroup());
                cell.setCellStyle(cellStyle);

                cell = row.createCell(2);
                cell.setCellValue(rmcp.getPackageName());

                UrlValidator defaultValidator = new UrlValidator(UrlValidator.ALLOW_LOCAL_URLS);

                if (rmcp.getRecommendedVersion().getReleaseNote() != null
                        && defaultValidator.isValid(rmcp.getRecommendedVersion().getReleaseNote())) {
                    XSSFHyperlink releaseNotelink = (XSSFHyperlink) createHelper
                            .createHyperlink(Hyperlink.LINK_URL);
                    releaseNotelink.setAddress(rmcp.getRecommendedVersion().getReleaseNote());
                    //System.out.println("Inside(if) RN: " + rmcp.getRecommendedVersion().getReleaseNote() + " Valid: " + defaultValidator.isValid(rmcp.getRecommendedVersion().getReleaseNote()));

                    cell.setHyperlink(releaseNotelink);
                }
                cell.setCellStyle(hlinkstyle);

                cell = row.createCell(3);
                cell.setCellValue(rmcp.getRecommendedVersion().getPackageVersion());
                cell.setCellStyle(cellStyle);

                cell = row.createCell(4);
                cell.setCellValue(rmcp.getAvailability());
                cell.setCellStyle(cellStyle);

                cell = row.createCell(5);
                String customers = Arrays.asList(rmcp.getRecommendedVersion().getCustomerList().toArray())
                        .toString();
                if (customers.equalsIgnoreCase("[All]")) {
                    customers = "";
                }
                cell.setCellValue(customers);
                cell.setCellStyle(cellStyle);

                cell = row.createCell(6);
                cell.setCellValue(rmcp.getRecommendedVersion().getRisk());
                cell.setCellStyle(cellStyle);

                cell = row.createCell(7);
                cell.setCellValue(rmcp.getPackageName());
                XSSFHyperlink link = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL);
                link.setAddress(rmcp.getRecommendedVersion().getDownloadLinks().iterator().next());
                cell.setHyperlink((XSSFHyperlink) link);
                cell.setCellStyle(hlinkstyle);

                cell = row.createCell(8);
                cell.setCellValue((rmcp.getRecommendedVersion() != null
                        && rmcp.getRecommendedVersion().isLessRecommended()) ? "#" : "");
                cell.setCellStyle(cellStyle);

                cell = row.createCell(9);
                cell.setCellValue(rmcp.getRecommendedVersion().getNotes());
                cell.setCellStyle(cellStyle);

                StringBuilder newFeatures = new StringBuilder();
                for (MCPackageActivities mcpa : rmcp.getRecommendedVersion().getActivities()) {
                    if (!mcpa.getActivityType().equalsIgnoreCase("epr")) {
                        newFeatures.append(mcpa.getActivityType() + " " + mcpa.getActivityId() + "; ");
                    }
                }
                cell = row.createCell(10);
                cell.setCellValue(newFeatures.toString());
                cell.setCellStyle(cellStyle);

                cell = row.createCell(11);
                cell.setCellValue(rmcp.getRecommendedVersion().getReleaseDate());
                cell.setCellStyle(dateCellStyle);
            }
            sheet.autoSizeColumn(0);
            sheet.autoSizeColumn(1);
            sheet.autoSizeColumn(2);
            sheet.autoSizeColumn(3);
            sheet.autoSizeColumn(4);
            sheet.autoSizeColumn(6);
            sheet.autoSizeColumn(7);
            sheet.autoSizeColumn(8);
            sheet.autoSizeColumn(11);

        }

        FileOutputStream outFile = new FileOutputStream(
                new File(Configuration.getInstance().getRssTemplatePath() + rssFileName));
        workbook.write(outFile);
        outFile.close();
        return Configuration.getInstance().getRssTemplatePath() + rssFileName;

    } catch (FileNotFoundException ex) {
        Logger.getLogger(GenerateRSS.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(GenerateRSS.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        try {
            file.close();
        } catch (IOException ex) {
            Logger.getLogger(GenerateRSS.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    return "";
}

From source file:com.centurylink.mdw.workflow.drools.Excel2007Parser.java

License:Apache License

public void parseFile(InputStream inStream) {
    try {/* ww  w  .  j  a  va2  s  .  c  om*/
        XSSFWorkbook workbook = new XSSFWorkbook(inStream);
        if (useFirstSheet) {
            XSSFSheet sheet = workbook.getSheetAt(0);
            processSheet(sheet, listeners.get(DEFAULT_RULESHEET_NAME));
        } else {
            for (String sheetName : listeners.keySet()) {
                XSSFSheet sheet = workbook.getSheet(sheetName);
                processSheet(sheet, listeners.get(sheetName));
            }
        }
    } catch (IOException ex) {
        throw new DecisionTableParseException(ex.getMessage(), ex);
    }
}

From source file:com.consensus.qa.framework.ExcelOperations.java

private XSSFSheet GetSheetFromWorkBook(XSSFWorkbook workbook, String sheetName) {
    int numOfWorkBooks = 0;
    String sheet = null;//from   w w  w  .ja  v  a2s.c  o m
    numOfWorkBooks = workBook.getNumberOfSheets();
    for (int count = 0; count < numOfWorkBooks; count++) {
        if (sheetName.toString().toLowerCase().contains(workBook.getSheetName(count).toLowerCase())) {
            sheet = workBook.getSheetName(count);
            break;
        }
    }
    if (sheet != null) {
        worksheet = workBook.getSheet(sheet);
    }
    return worksheet;
}

From source file:com.dao.DatabaseDao.java

public static void insertFromFile(MainForm mf, String sect, String destpath, String fname, Statement st,
        Connection c, long maxRecID, ArrayList<Long> arl) throws Exception {

    FileInputStream fin = new FileInputStream(destpath);
    XSSFWorkbook wb = new XSSFWorkbook(fin);
    XSSFSheet sheet = wb.getSheet("EmployeeInfo");
    long lastrowno = sheet.getLastRowNum();
    XSSFRow row;//from  w  w w.j a  v  a2 s . c om

    long i = 1;
    while (i <= lastrowno) {
        row = sheet.getRow((int) i);

        long id = (long) row.getCell(0).getNumericCellValue();//ID
        String name = row.getCell(1).getStringCellValue();//NAME
        long recp_no = (long) row.getCell(2).getNumericCellValue();//RECEIPT_NO
        Date edate = (Date) row.getCell(3).getDateCellValue();//ENTRY_DATE
        int subrate = (int) row.getCell(4).getNumericCellValue();//SUB_RATE
        int jan = (int) row.getCell(5).getNumericCellValue();//JAN
        int feb = (int) row.getCell(6).getNumericCellValue();//FEB
        int mar = (int) row.getCell(7).getNumericCellValue();//MAR
        int apr = (int) row.getCell(8).getNumericCellValue();//APR
        int may = (int) row.getCell(9).getNumericCellValue();//MAY
        int jun = (int) row.getCell(10).getNumericCellValue();//JUN
        int jul = (int) row.getCell(11).getNumericCellValue();//JUL
        int aug = (int) row.getCell(12).getNumericCellValue();//AUG
        int sep = (int) row.getCell(13).getNumericCellValue();//SEP
        int oct = (int) row.getCell(14).getNumericCellValue();//OCT
        int nov = (int) row.getCell(15).getNumericCellValue();//NOV
        int dec = (int) row.getCell(16).getNumericCellValue();//DECB
        long tot = (long) row.getCell(17).getNumericCellValue();//TOTAL
        String remark = row.getCell(18).getStringCellValue();//REMARK
        String sector = row.getCell(19).getStringCellValue();//SECTOR
        String sub_frm = row.getCell(20).getStringCellValue();//SUB_FROM
        String sub_to = row.getCell(21).getStringCellValue();//SUB_TO
        String place = row.getCell(22).getStringCellValue();//PLACE
        boolean isAlready = arl.contains(recp_no);

        Employee emp = new Employee();
        emp.setName(name);
        emp.setEntry_date(edate);
        emp.setSub_rate(subrate);
        emp.setJan(jan);
        emp.setFeb(feb);
        emp.setMar(mar);
        emp.setApr(apr);
        emp.setMay(may);
        emp.setJun(jun);
        emp.setJul(jul);
        emp.setAug(aug);
        emp.setSep(sep);
        emp.setOct(oct);
        emp.setNov(nov);
        emp.setDecb(dec);
        emp.setTotal(tot);
        emp.setRemark(remark);
        emp.setSector(sector);
        emp.setSub_from(sub_frm);
        emp.setSub_to(sub_to);
        emp.setPlace(place);
        if (isAlready) {
            emp.setReceipt_no(maxRecID);
            maxRecID++;
        } else {
            emp.setReceipt_no(recp_no);
        }
        EmployeeDao.save(emp);

        i++;
    }

    st = c.createStatement();
    st.execute("INSERT INTO IMPORTFILE_INFO(NAME) VALUES('" + fname + "')");

    mf.initData(sect);
    JOptionPane.showMessageDialog(null, "Database Import Successfully...!!");
}

From source file:com.ddt.driver.EXCELDriver.java

private XSSFSheet getSheetName(String sheetName, XSSFWorkbook workbook) {
    if (sheetName == null) {
        return workbook.getSheetAt(0);
    } else {/*from ww  w  .ja  v a  2  s.c  o m*/
        return workbook.getSheet(sheetName);
    }
}

From source file:com.excel.javafx.frames.MainFrame.java

private void CompareBtnActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_CompareBtnActionPerformed
    try {/*from  w ww.  j a v  a  2 s . c  o  m*/
        // get input excel files
        FileInputStream sourceFile = new FileInputStream(sourceFileName);
        FileInputStream destFile = new FileInputStream(destFileName);

        // Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook sourcebook = new XSSFWorkbook(sourceFile);
        XSSFWorkbook destbook = new XSSFWorkbook(destFile);

        // Get first/desired sheet from the workbook
        XSSFSheet sourceSheet = sourcebook.getSheet(sourceSheetSelector.getSelectedItem().toString());
        XSSFSheet destSheet = destbook.getSheet(destSheetSelector.getSelectedItem().toString());

        // Compare sheets
        if (compareTwoSheets(sourceSheet, destSheet)) {
            System.out.println("\n\nThe two excel sheets are Equal");
        } else {
            System.out.println("\n\nThe two excel sheets are Not Equal");
        }

        //close files
        sourceFile.close();
        destFile.close();

        //update table values
        int emptyrow = 0;
        for (int rownum = 0; rownum < rowcount; rownum++) {
            if (!tableModel.getValueAt(rownum, 1).toString().equals("")) {
                emptyrow++;
            }
        }
        for (int rowno = 0; rowno <= expectedValue.size(); rowno++) {
            tableModel.setValueAt(sourceFileName.getName(), emptyrow, 1);
            jTable1.setValueAt(destFileName.getName(), emptyrow, 2);
            jTable1.setValueAt(sourceRow.get(rowno), emptyrow, 3);
            jTable1.setValueAt(destRow.get(rowno), emptyrow, 4);
            jTable1.setValueAt(sourceColumnList.getSelectedValue(), emptyrow, 5);
            jTable1.setValueAt(destColumnList.getSelectedValue(), emptyrow, 6);
            jTable1.setValueAt(expectedValue.get(rowno), emptyrow, 7);
            jTable1.setValueAt(actualValue.get(rowno), emptyrow, 8);
        }

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

}

From source file:com.excel.javafx.frames.MainFrame.java

private void sourceColumnSelector() {
    FileInputStream sourceFile1 = null;
    final DefaultListModel model = new DefaultListModel();

    try {//from   w ww .  j  ava 2  s  .c  o m
        sourceFile1 = new FileInputStream(sourceFileName);
        XSSFWorkbook workbook1 = new XSSFWorkbook(sourceFile1);
        XSSFSheet sheet = workbook1.getSheet(sourceSheetSelector.getSelectedItem().toString());
        int columncount = sheet.getRow(0).getLastCellNum();

        for (int columnno = 0; columnno < columncount; columnno++) {
            model.addElement(sheet.getRow(0).getCell(columnno).toString());
        }
        sourceColumnList.setModel(model);

    } catch (FileNotFoundException ex) {
        Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        try {
            sourceFile1.close();
        } catch (IOException ex) {
            Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

From source file:com.excel.javafx.frames.MainFrame.java

private void destinationColumnSelector() {
    FileInputStream destFile1 = null;
    final DefaultListModel model = new DefaultListModel();

    try {//from  w w  w .ja  va  2 s. c  o m
        destFile1 = new FileInputStream(destFileName);
        XSSFWorkbook workbook1 = new XSSFWorkbook(destFile1);
        XSSFSheet sheet = workbook1.getSheet(destSheetSelector.getSelectedItem().toString());
        int columncount = sheet.getRow(0).getLastCellNum();

        for (int columnno = 0; columnno < columncount; columnno++) {
            model.addElement(sheet.getRow(0).getCell(columnno).toString());
        }
        destColumnList.setModel(model);

    } catch (FileNotFoundException ex) {
        Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        try {
            destFile1.close();
        } catch (IOException ex) {
            Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

From source file:com.knsi.PerformanceFrame.java

public void createUI() {
    JDialog pf = new JDialog(this, "Performance", true);
    pf.setIconImage(Toolkit.getDefaultToolkit().getImage(getClass().getResource("/resources/logo.png")));

    JTabbedPane perpanel = new JTabbedPane();

    JPanel addp = new JPanel();

    JPanel viewp = new JPanel();
    perpanel.addTab("          Add New Test Details          ", addp);
    //perpanel.addTab("          View Student Performance            ", viewp);
    addp.setBackground(colorSecondary);/*from  ww  w. j av  a 2  s. c om*/
    viewp.setBackground(colorSecondary);

    setaddTab(addp);

    submitBtn.addActionListener(new ActionListener() {

        @Override
        public void actionPerformed(ActionEvent e) {
            try {
                System.out.println(t4.getSelectedItem());
                hasStudentmarks.setVisible(false);
                if (t4.getSelectedItem().equals("")) {
                    hasStudents.setText(
                            "<html><div style=\"color: red;\">" + "No batch has been selected" + "</html>");
                    hasStudents.setVisible(true);

                } else if (tName.getText().equalsIgnoreCase("")) {
                    hasStudents.setText("<html><div style=\"color: red;\">" + "Invalid or No Test Name Entered"
                            + "</html>");
                    hasStudents.setVisible(true);
                } else if (!maxMarks.getText().matches("^[1-9]\\d*$")) {
                    hasStudents.setText("<html><div style=\"color: red;\">"
                            + "Marks should only consist of digits" + "</html>");
                    hasStudents.setVisible(true);
                } else if (isValid) {
                    hasStudents.setText("<html><div style=\"color: red;\">"
                            + "Cannot simultaneously create two performances" + "</html>");
                    hasStudents.setVisible(true);
                } else if (fileHasValues((String) t4.getSelectedItem())) {
                    hasStudents.setText(
                            "<html><div style=\"color: red;\">" + "No Students in this batch" + "</html>");
                    hasStudents.setVisible(true);
                    System.out.println("");
                } else {
                    isValid = true;
                    hasStudents.setText("<html><div style=\"color: green;\">"
                            + "Performance created successfully" + "</html>");
                    hasStudents.setVisible(true);
                    outof.setText(maxMarks.getText());
                    try {
                        WritePerformance();
                    } catch (Exception ee) {
                        ee.printStackTrace();
                    }
                }
            } catch (IOException ex) {
                Logger.getLogger(PerformanceFrame.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        private boolean fileHasValues(String string) throws IOException {
            XSSFWorkbook details = new XSSFWorkbook(new FileInputStream(new File(string + ".xlsx")));
            XSSFSheet spreadsheet1 = details.getSheet("details");
            return spreadsheet1.getLastRowNum() == 0;
        }
    });

    NexttBtn.addActionListener(new ActionListener() {

        @Override
        public void actionPerformed(ActionEvent e) {
            System.out.println("the next student is:::");

            if (!isValid) {
                hasStudentmarks.setText(
                        "<html><div style=\"color: red;\">" + "Please Create a Performance" + "</html>");
                hasStudentmarks.setVisible(true);

            }

            else if (!studentmarks.getText().matches("^[1-9]\\d*$")
                    || Integer.parseInt(studentmarks.getText()) > Integer.parseInt(maxMarks.getText())) {
                hasStudentmarks.setText("<html><div style=\"color: red;\">" + "Invalid Entry" + "</html>");
                hasStudentmarks.setVisible(true);
            } else if (itercount == nameList.size()) {
                hasStudentmarks.setText("<html><div style=\"color: green;\">"
                        + "All Student\'s Performance has been updated" + "</html>");
                hasStudentmarks.setVisible(true);
                isValid = false;
                itercount = 0;
            } else {
                NexttBtn.setText("<html><div style=\"color: white;\">" + "Next Student" + "</html>");
                studentname.setText(nameList.get(itercount).getCell(0).getStringCellValue());
                studentid.setText(nameList.get(itercount).getCell(1).getStringCellValue());
                marksList.add(studentmarks.getText());
                itercount++;
            }

            /* for (XSSFRow row : nameList) {
            System.out.println(row.getCell(0).getStringCellValue()+"     "+row.getCell(1).getStringCellValue());
            System.out.println(nameList.get(itercount).getCell(0).getStringCellValue()+""+nameList.get(itercount).getCell(1).getStringCellValue());
             }*/

            WritePerformanceDB.WriteTo(marksList);

        }
    });

    pf.add(perpanel, BorderLayout.CENTER);
    pf.setSize(960, 680);
    //pf.setBounds(0,0,1060,720);
    pf.setLocationRelativeTo(null);
    pf.setVisible(true);
}