Example usage for org.apache.poi.ss.usermodel Workbook getSheetAt

List of usage examples for org.apache.poi.ss.usermodel Workbook getSheetAt

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. getSheetAt

Introduction

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

Prototype

Sheet getSheetAt(int index);

Source Link

Document

Get the Sheet object at the given index.

Usage

From source file:com.hust.zsuper.DealWithPatent.ExcelToMySQL.java

License:Open Source License

public void addWorkbook(Connection conn, Workbook workbook) throws SQLException {
    final int numberOfSheets = workbook.getNumberOfSheets();
    for (int sheetIndex = 0; sheetIndex < numberOfSheets; sheetIndex++) {
        final Sheet sheet = workbook.getSheetAt(sheetIndex);
        if (filter.accept(sheet)) {
            addTableFromSheet(conn, sheet);
        }/*from   ww  w  .j a  v  a2 s . c om*/
    }
}

From source file:com.ibm.db2j.GExcel.java

License:Open Source License

/**
 * Finds the spreadsheet defined by the name given in parameter and return it if found.
 * Else return null./*ww w . j  a v a  2  s  . c  o  m*/
 * 
 * @param workbook
 * @param spreadsheetName
 * @return the spreadsheet
 * @throws SQLException
 */
private Sheet findSpreadsheet(Workbook workbook, String spreadsheetName) throws SQLException {
    boolean sheetFound = false;
    Sheet sheetTmp = null;
    for (int i = 0; i < workbook.getNumberOfSheets() && !sheetFound; ++i) {
        sheetTmp = workbook.getSheetAt(i);

        if (sheetTmp.getSheetName().equals(spreadsheetName))
            sheetFound = true;
    }

    if (sheetFound == false && sheet == null) {
        throw new SQLException("The file does not contain a spreadsheet named : " + spreadsheetName);
    }

    return sheetTmp;
}

From source file:com.ifeng.vdn.ip.repository.app.IPCheckApp.java

License:Apache License

public static void main(String[] args) {
    Workbook wb = null;
    PrintWriter pw = null;//ww w  . j a  v a2  s.c  o  m
    try {

        pw = new PrintWriter(new FileOutputStream("src/test/resources/data/CDN_BAD.txt"), true);

        AliIPAddressChecker ipChecker = new AliIPAddressChecker();

        wb = WorkbookFactory.create(new FileInputStream("src/test/resources/data/CDN_BAD.xlsx"));
        Sheet sheet = wb.getSheetAt(0);

        int totalRows = sheet.getPhysicalNumberOfRows();
        Cell ipCell = null;
        //Cell locCell = null;

        List<String> ips = new ArrayList<String>();

        for (int i = 1; i < totalRows; i++) {
            Row row = sheet.getRow(i);
            ipCell = row.getCell(0);

            ips.add(ipCell.getStringCellValue());
        }

        for (String ip : ips) {
            AliIPBean bean = (AliIPBean) ipChecker.ipcheck(ip);
            pw.println(ip + "-" + bean.getIpString());
        }

    } catch (InvalidFormatException | IOException e) {
        e.printStackTrace();
    } finally {
        if (wb != null)
            try {
                wb.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        if (pw != null) {
            pw.flush();
            pw.close();
        }
    }

}

From source file:com.ifeng.vdn.ip.repository.service.impl.AliBatchIPAddressCheckerTest.java

License:Apache License

@Test
public void wrap() {
    Workbook wb = null;
    try {// w ww. ja v  a2s  .c om
        BatchIPAddressChecker<AliIPBean> checker = new AliBatchIPAddressChecker();

        wb = WorkbookFactory.create(new FileInputStream("src/test/resources/data/ip_18.xlsx"));
        Sheet sheet = wb.getSheetAt(0);

        int totalRows = sheet.getPhysicalNumberOfRows();
        Cell ipCell = null;
        Cell locCell = null;

        List<String> ips = new ArrayList<String>();

        for (int i = 1; i < totalRows; i++) {
            Row row = sheet.getRow(i);
            ipCell = row.getCell(1);

            ips.add(ipCell.getStringCellValue());
        }

        List<AliIPBean> locations = checker.check(ips);
        String location = "";

        for (int i = 1; i < totalRows; i++) {
            Row row = sheet.getRow(i);
            locCell = row.getCell(3);

            try {
                location = locations.get(i).getIpString();
            } catch (Exception e) {
                location = "ERROR";
            }

            locCell.setCellValue(location);
        }

        /*if(location.length() == (totalRows - 1)){
                   
        }else {
           log.error("Batch executed error");
           throw new RuntimeException("Batch executed error. Some one IP location not be checked.");
        }*/

        wb.write(new FileOutputStream("src/test/resources/data/ip_18_Alibaba.xlsx"));

    } catch (InvalidFormatException | IOException e) {
        e.printStackTrace();
    } finally {
        if (wb != null)
            try {
                wb.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
    }

}

From source file:com.ifeng.vdn.ip.repository.service.impl.AliDataFactoryTest.java

License:Apache License

@Test
public void importDataFromExcel() {
    Workbook wb = null;
    String input = "src/test/resources/data/ip_18_Alibaba.xlsx";

    try {/*from  w ww.j  a va2s.  c  om*/

        List<IPModel> ipList = new ArrayList<IPModel>();

        wb = WorkbookFactory.create(new FileInputStream(input));
        Sheet sheet = wb.getSheetAt(0);

        int totalRows = sheet.getPhysicalNumberOfRows();

        Cell ipCell = null;
        Cell totalCell = null;
        Cell locCell = null;

        String location = "";
        int total;
        String ipString = "";

        String[] items = null;

        for (int i = 1; i < totalRows; i++) {
            Row row = sheet.getRow(i);

            ipCell = row.getCell(1);
            totalCell = row.getCell(2);
            locCell = row.getCell(3);

            try {
                String country = "";
                String area = "";
                String region = "";
                String city = "";
                String isp = "";
                IPModel model = new IPModel();

                ipString = ipCell.getStringCellValue();
                total = (int) totalCell.getNumericCellValue();
                location = locCell.getStringCellValue();

                items = location.split(" ");
                if (items != null) {
                    if (items[0] != null) {
                        country = items[0];
                    }
                    if (items[1] != null) {
                        area = items[1];
                    }
                    if (items[2] != null) {
                        region = items[2];
                    }
                    if (items[3] != null) {
                        city = items[3];
                    }
                    if (items[4] != null) {
                        isp = items[4];
                    }

                    model.setIp(ipString);
                    model.setCountry(country.trim());
                    model.setArea(area.trim());
                    model.setRegion(region.trim());
                    model.setCity(city.trim());
                    model.setIsp(isp.trim());
                    model.setTotal(total);

                    ipList.add(model);
                }

                log.info("IP: {}, Total{}, location: {}", ipString, total, location);

                if ((i % 1000) == 0) {
                    aliDataFactory.importData(ipList);
                    ipList = new ArrayList<IPModel>();
                }

            } catch (Exception e) {
                e.getMessage();
                //log.error(e.getMessage(), e);

            }

        }

        if (ipList.size() > 0) {
            aliDataFactory.importData(ipList);
        }

    } catch (InvalidFormatException e) {
        e.printStackTrace();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

}

From source file:com.ifeng.vdn.ip.repository.service.impl.AliIPAddressCheckerTest.java

License:Apache License

public void wrap(String input, String output) {
    Workbook wb = null;
    PrintWriter pw = null;//from w w w .ja  va  2 s .  c  om
    try {
        pw = new PrintWriter(output, "UTF-8");

        IPAddressChecker checker = new AliIPAddressChecker();
        wb = WorkbookFactory.create(new FileInputStream(input));
        Sheet sheet = wb.getSheetAt(0);

        int totalRows = sheet.getPhysicalNumberOfRows();

        Cell ipCell = null;
        Cell locCell = null;

        String location = "";
        String ipString = "";

        for (int i = 1; i < totalRows; i++) {
            Row row = sheet.getRow(i);

            ipCell = row.getCell(1);
            locCell = row.getCell(3);

            try {
                ipString = ipCell.getStringCellValue();
                AliIPBean ip = (AliIPBean) checker.ipcheck(ipString);
                location = ip.getIpString();
            } catch (Exception e) {
                log.error(e.getMessage(), e);
                location = "ERROR";
            } finally {
                //locCell.setCellValue(location);

                pw.append(ipString + " " + location);
                pw.println();
            }
        }

        wb.write(new FileOutputStream(output));

    } catch (InvalidFormatException | IOException e) {
        log.error(e.getMessage(), e);
    } finally {
        if (wb != null)
            try {
                wb.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        pw.flush();
        pw.close();
    }
}

From source file:com.ifeng.vdn.ip.repository.service.impl.IPAddressBaiduCheckerTest.java

License:Apache License

@Test
public void wrap() {

    Workbook wb = null;

    try {//from   ww w  . ja v  a2 s . c  o  m
        IPAddressBaiduChecker checker = new IPAddressBaiduChecker();

        wb = WorkbookFactory.create(new FileInputStream("src/test/resources/data/ip_16.xlsx"));
        Sheet sheet = wb.getSheetAt(0);

        int totalRows = sheet.getPhysicalNumberOfRows();

        Cell ipCell = null;
        Cell locCell = null;

        for (int i = 1; i < totalRows; i++) {
            Row row = sheet.getRow(i);

            ipCell = row.getCell(1);
            locCell = row.getCell(3);

            BaiduIPBean ip = (BaiduIPBean) checker.ipcheck(ipCell.getStringCellValue());
            List<IPLocation> locations = ip.getData();

            if (locations != null && locations.size() > 0) {

                try {
                    locCell.setCellValue(locations.get(0).getLocation());
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }

        }

        wb.write(new FileOutputStream("src/test/resources/data/ip_16_loc.xlsx"));

    } catch (InvalidFormatException | IOException e) {
        e.printStackTrace();
    } finally {
        if (wb != null)
            try {
                wb.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
    }

}

From source file:com.ihhira.projects.badgemaker.MainWindow.java

private void loadData() throws IOException, EncryptedDocumentException, InvalidFormatException {

    JFileChooser fileChooser = new JFileChooser(curDir);
    int ok = fileChooser.showOpenDialog(this);
    if (ok != JFileChooser.APPROVE_OPTION) {
        return;//from  w  w w .j av  a 2  s.  co m
    }
    File selectedFile = fileChooser.getSelectedFile();
    curDir = selectedFile.getParentFile();

    //        File selectedFile = new File("jugbd-meetup5-attendee-list.xlsx");

    Workbook wb = WorkbookFactory.create(selectedFile);
    Sheet sheet = wb.getSheetAt(0);
    Iterator<Row> rowIterator = sheet.iterator();
    ArrayList<Row> rows = new ArrayList<>();
    while (rowIterator.hasNext()) {
        rows.add(rowIterator.next());
    }
    this.rows = rows;
    if (rows.size() > 0) {
        showData(0);
    }
}

From source file:com.impetus.kvapps.runner.UserBroker.java

License:Apache License

/**
 * Reads and populate a collection of {@link User} from excel file available at dataFilePath.
 * //from w w  w . jav a 2 s.  co m
 * @param dataFilePath
 * @return  collection of users.
 */
static Set<User> brokeUserList(final String dataFilePath) {
    Set<User> users = new HashSet<User>();
    Workbook workBook = null;
    File file = new File(dataFilePath);
    InputStream excelDocumentStream = null;
    try {
        excelDocumentStream = new FileInputStream(file);
        POIFSFileSystem fsPOI = new POIFSFileSystem(new BufferedInputStream(excelDocumentStream));
        workBook = new HSSFWorkbook(fsPOI);
        UserBroker parser = new UserBroker(workBook.getSheetAt(0));
        User user;
        while ((user = parser.addUser(users)) != null) {
            users.add(user);
        }
        excelDocumentStream.close();

    } catch (Exception e) {
        throw new RuntimeException("Error while processing brokeUserList(), Caused by :", e);
    }

    return users;
}

From source file:com.inspiracode.nowgroup.scspro.xl.source.ExcelFile.java

License:Open Source License

public List<LogMessage> validateFieldNames() {
    List<LogMessage> result = new ArrayList<LogMessage>();
    FileInputStream fis = null;/*from   w  w w .j a  va 2  s.  c  o  m*/
    Workbook wb = null;
    try {
        fis = new FileInputStream(file);
        // Get the workbook instance for XLS file

        if (".xls".equals(file.getName().substring(file.getName().length() - 4))) {
            wb = new HSSFWorkbook(fis);
        } else if ("xlsx".equals(file.getName().substring(file.getName().length() - 4))) {
            wb = new XSSFWorkbook(fis);
        } else {
            throw new IllegalArgumentException("Received file does not have a standard excel extension.");
        }

        // Get each sheet from the workbook
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            result.addAll(validateFieldNames(wb.getSheetAt(i)));
        }

    } catch (Exception e) {
        log.error("Error al validar campos en excel: [" + e.getMessage() + "]", e);
        result.add(new LogMessage("Validacin de encabezados",
                "Error al validar campos en excel: [" + e.getMessage() + "]"));
    } finally {
        try {
            fis.close();
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
        try {
            wb.close();
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
    }

    return result;
}