Example usage for org.apache.poi.ss.usermodel Sheet getLastRowNum

Introduction

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

Prototype

int getLastRowNum();

Source Link

Document

Gets the last row on the sheet Note: rows which had content before and were set to empty later might still be counted as rows by Excel and Apache POI, so the result of this method will include such rows and thus the returned value might be higher than expected!

Usage

From source file:org.wso2.carbon.dataservices.sql.driver.query.insert.ExcelInsertQuery.java

License:Open Source License

private synchronized int executeSQL() throws SQLException {
    int rowCount = 0;
    if (!(getConnection() instanceof TExcelConnection)) {
        throw new SQLException("Connection does not refer to a Excel connection");
    }/*www  . j  a v a  2s .  c om*/
    TExcelConnection excelConnection = (TExcelConnection) this.getConnection();
    //begin transaction,
    excelConnection.beginExcelTransaction();
    Workbook workbook = excelConnection.getWorkbook();
    Sheet sheet = workbook.getSheet(getTargetTableName());
    if (sheet == null) {
        throw new SQLException("Excel sheet named '" + this.getTargetTableName() + "' does not exist");
    }
    int lastRowNo = sheet.getLastRowNum();

    if (getParameters() != null) {
        Row row = sheet.createRow(lastRowNo + 1);
        for (ParamInfo param : getParameters()) {
            Cell cell = row.createCell(param.getOrdinal());
            switch (param.getSqlType()) {
            case Types.VARCHAR:
                cell.setCellValue((String) param.getValue());
                break;
            case Types.INTEGER:
                cell.setCellValue((Integer) param.getValue());
                break;
            case Types.DOUBLE:
                cell.setCellValue((Double) param.getValue());
                break;
            case Types.BOOLEAN:
                cell.setCellValue((Boolean) param.getValue());
                break;
            case Types.DATE:
                cell.setCellValue((Date) param.getValue());
                break;
            default:
                cell.setCellValue((String) param.getValue());
                break;
            }
        }
        rowCount++;
    }
    TDriverUtil.writeRecords(workbook, ((TExcelConnection) getConnection()).getPath());
    return rowCount;
}

From source file:org.wso2.carbon.registry.samples.populator.Main.java

License:Open Source License

private static void addReports(ConfigurationContext configContext) throws Exception {
    File reportsDirectory = new File(CommandHandler.getReportsLocation());
    if (reportsDirectory.exists()) {
        ReportGeneratorServiceClient client = new ReportGeneratorServiceClient(cookie,
                CommandHandler.getServiceURL(), configContext);
        Workbook[] workbooks = PopulatorUtil.getWorkbooks(reportsDirectory, "list");
        for (Workbook workbook : workbooks) {
            Sheet sheet = workbook.getSheet(workbook.getSheetName(0));
            if (sheet == null || sheet.getLastRowNum() == -1) {
                throw new RuntimeException("The first sheet is empty");
            }/*w  w  w.  ja  va  2  s  .c  o m*/
            int limit = sheet.getLastRowNum();
            for (int i = 0; i <= limit; i++) {
                Row row = sheet.getRow(i);
                if (row == null || row.getCell(0) == null) {
                    break;
                }
                client.saveReport(PopulatorUtil.getReportConfigurationBean(row.getCell(0).getStringCellValue(),
                        row.getCell(1).getStringCellValue(), row.getCell(2).getStringCellValue(),
                        row.getCell(3).getStringCellValue()));
            }
        }
    }
}

From source file:org.wso2.carbon.registry.samples.populator.Main.java

License:Open Source License

private static void addPermissions(ConfigurationContext configContext, File dataDirectory) throws Exception {
    UserManagementClient userManager = new UserManagementClient(cookie, CommandHandler.getServiceURL(),
            configContext);//from  w  ww .  ja  v a 2  s. com
    Workbook[] workbooks = PopulatorUtil.getWorkbooks(dataDirectory, "permission");
    for (Workbook workbook : workbooks) {
        Sheet sheet = workbook.getSheet(workbook.getSheetName(0));
        if (sheet == null || sheet.getLastRowNum() == -1) {
            throw new RuntimeException("The first sheet is empty");
        }
        int limit = sheet.getLastRowNum();
        for (int i = 0; i <= limit; i++) {
            Row row = sheet.getRow(i);
            if (row == null || row.getCell(0) == null) {
                break;
            }
            userManager.setRoleResourcePermission(getCellValue(row.getCell(0), null),
                    getCellValue(row.getCell(1), null), splitAndTrim(getCellValue(row.getCell(2), null), ","));
        }
    }
}

From source file:org.wso2.carbon.registry.samples.populator.Main.java

License:Open Source License

private static void addAssociations(Registry registry, File dataDirectory) throws Exception {
    Workbook[] workbooks = PopulatorUtil.getWorkbooks(dataDirectory, "association");
    for (Workbook workbook : workbooks) {
        Sheet sheet = workbook.getSheet(workbook.getSheetName(0));
        if (sheet == null || sheet.getLastRowNum() == -1) {
            throw new RuntimeException("The first sheet is empty");
        }//w  w  w. j  a v a 2  s .  c  o m
        int limit = sheet.getLastRowNum();
        for (int i = 0; i <= limit; i++) {
            Row row = sheet.getRow(i);
            if (row == null || row.getCell(0) == null) {
                break;
            }
            registry.addAssociation(getCellValue(row.getCell(0), null), getCellValue(row.getCell(1), null),
                    getCellValue(row.getCell(2), null));
        }
    }
}

From source file:org.wso2.carbon.registry.samples.populator.Main.java

License:Open Source License

private static void addCommentsRatingsAndTags(Registry registry, File dataDirectory) throws Exception {
    Workbook[] workbooks = PopulatorUtil.getWorkbooks(dataDirectory, "community");
    for (Workbook workbook : workbooks) {
        Sheet sheet = workbook.getSheet(workbook.getSheetName(0));
        if (sheet == null || sheet.getLastRowNum() == -1) {
            throw new RuntimeException("The first sheet is empty");
        }//from  ww  w . j  a va  2  s.com
        int limit = sheet.getLastRowNum();
        for (int i = 0; i <= limit; i++) {
            Row row = sheet.getRow(i);
            if (row == null || row.getCell(0) == null) {
                break;
            }
            String type = getCellValue(row.getCell(0), null).toLowerCase();
            if (type.contains("tag")) {
                String tag = getCellValue(row.getCell(2), null);
                if (tag == null) {
                    String[] parts = getCellValue(row.getCell(1), null).split(";");
                    registry.removeTag(parts[0], parts[1].substring(5));
                } else {
                    registry.applyTag(getCellValue(row.getCell(1), null), tag);
                }
            } else if (type.contains("comment")) {
                String path = getCellValue(row.getCell(1), null);
                if (path.contains(";")) {
                    String comment = getCellValue(row.getCell(2), null);
                    if (comment == null) {
                        registry.removeComment(path);
                    } else {
                        registry.editComment(path, comment);
                    }
                } else {
                    registry.addComment(path, new Comment(getCellValue(row.getCell(2), null)));
                }
            } else if (type.contains("rate") || type.contains("rating")) {
                registry.rateResource(getCellValue(row.getCell(1), null),
                        Integer.parseInt(getCellValue(row.getCell(2), "0")));
            }
        }
    }
}

From source file:org.wso2.carbon.registry.samples.populator.Main.java

License:Open Source License

private static void importAndUploadResources(Registry registry, File dataDirectory) throws Exception {
    Workbook[] workbooks = PopulatorUtil.getWorkbooks(dataDirectory, "import");
    for (Workbook workbook : workbooks) {
        Sheet sheet = workbook.getSheet(workbook.getSheetName(0));
        if (sheet == null || sheet.getLastRowNum() == -1) {
            throw new RuntimeException("The first sheet is empty");
        }// www .j a  v a2s . c  o m
        int limit = sheet.getLastRowNum();
        for (int i = 0; i <= limit; i++) {
            Row row = sheet.getRow(i);
            if (row == null || row.getCell(0) == null) {
                break;
            }
            String path = row.getCell(0).getStringCellValue();
            String url = row.getCell(1).getStringCellValue();
            Resource resource = registry.newResource();
            resource.setMediaType(getCellValue(row.getCell(2), null));
            resource.setDescription(getCellValue(row.getCell(3),
                    "This resource was added using the " + "WSO2 Governance Registry Sample Data Populator"));
            if (url.startsWith("file:")) {
                resource.setContentStream(new BufferedInputStream(new FileInputStream(new File(new URI(url)))));
                registry.put(path, resource);
            } else {
                registry.importResource(path, url, resource);
            }
        }
    }
}

From source file:org.wso2.carbon.registry.samples.populator.Main.java

License:Open Source License

private static void addResourcesAndCollections(Registry registry, File dataDirectory) throws Exception {
    Workbook[] workbooks = PopulatorUtil.getWorkbooks(dataDirectory, "resource");
    for (Workbook workbook : workbooks) {
        Sheet sheet = workbook.getSheet(workbook.getSheetName(0));
        if (sheet == null || sheet.getLastRowNum() == -1) {
            throw new RuntimeException("The first sheet is empty");
        }//from  w  w  w.  j  a  v  a  2  s.  co  m
        int limit = sheet.getLastRowNum();
        for (int i = 0; i <= limit; i++) {
            Row row = sheet.getRow(i);
            if (row == null || row.getCell(0) == null) {
                break;
            }
            String path = row.getCell(0).getStringCellValue();
            Resource resource;
            if (registry.resourceExists(path)) {
                resource = registry.get(path);
                String key = getCellValue(row.getCell(1), null);
                String value = getCellValue(row.getCell(2), null);
                if (value == null) {
                    resource.removeProperty(key);
                } else {
                    resource.setProperty(key, value);
                }
            } else {
                String value = getCellValue(row.getCell(1), null);
                if (value == null) {
                    resource = registry.newCollection();
                } else {
                    resource = registry.newResource();
                    resource.setMediaType("text/plain");
                    resource.setContent(value);
                }
                resource.setDescription(getCellValue(row.getCell(2), "This resource was added using the "
                        + "WSO2 Governance Registry Sample Data Populator"));
            }
            registry.put(path, resource);
        }
    }
}

From source file:org.wso2.carbon.registry.samples.populator.Main.java

License:Open Source License

private static void addAssets(Registry registry, File dataDirectory) throws Exception {
    Workbook[] workbooks = PopulatorUtil.getWorkbooks(dataDirectory, "asset");
    for (Workbook workbook : workbooks) {
        Registry governanceRegistry = GovernanceUtils.getGovernanceUserRegistry(registry,
                CommandHandler.getUsername());
        Sheet sheet = workbook.getSheet(workbook.getSheetName(0));
        if (sheet == null || sheet.getLastRowNum() == -1) {
            throw new RuntimeException("The first sheet is empty");
        }//  www . j  av  a2 s  .  c  om
        int limit = sheet.getLastRowNum();
        if (limit < 1) {
            throw new RuntimeException("Column headers were not specified in Asset Data Spreadsheet");
        }
        Row row = sheet.getRow(0);
        int key = -1;
        List<String> temp = new LinkedList<String>();
        String value;
        int count = 0;
        while ((value = getCellValue(row.getCell(count++), null)) != null) {
            if (value.equals("key")) {
                key = count - 1;
            } else {
                temp.add(value);
            }
        }
        String[] headers = temp.toArray(new String[temp.size()]);
        if (key == -1) {
            throw new RuntimeException("Asset Key was not specified");
        }
        for (int i = 1; i <= limit; i++) {
            row = sheet.getRow(i);
            if (row == null || row.getCell(0) == null) {
                break;
            }
            String type = row.getCell(key).getStringCellValue();
            String nameAttribute = GovernanceUtils.findGovernanceArtifactConfiguration(type, governanceRegistry)
                    .getArtifactNameAttribute();
            String namespaceAttribute = GovernanceUtils
                    .findGovernanceArtifactConfiguration(type, governanceRegistry)
                    .getArtifactNamespaceAttribute();
            GenericArtifactManager manager = new GenericArtifactManager(governanceRegistry, type);
            Map<String, String> attributeMap = new HashMap<String, String>();
            for (int j = 0; j < headers.length; j++) {
                attributeMap.put(headers[j], row.getCell(j > key ? j + 1 : j).getStringCellValue());
            }
            GenericArtifact artifact = manager.newGovernanceArtifact(new QName(
                    attributeMap.containsKey(namespaceAttribute) ? attributeMap.get(namespaceAttribute) : null,
                    attributeMap.containsKey(nameAttribute) ? attributeMap.get(nameAttribute)
                            : UUIDGenerator.generateUUID()));
            for (Map.Entry<String, String> e : attributeMap.entrySet()) {
                artifact.setAttribute(e.getKey(), e.getValue());
            }
            manager.addGenericArtifact(artifact);
        }
    }
}

From source file:org.wso2.carbon.registry.samples.populator.Main.java

License:Open Source License

private static void addSubscriptions(ConfigurationContext configContext) throws Exception {
    File subscriptionsDirectory = new File(CommandHandler.getSubscriptionsLocation());
    if (subscriptionsDirectory.exists()) {
        SubscriberClient manager = new SubscriberClient(cookie, CommandHandler.getServiceURL(), configContext);
        Workbook workbook = PopulatorUtil.getWorkbook(subscriptionsDirectory, "list");
        if (workbook != null) {
            Sheet sheet = workbook.getSheet(workbook.getSheetName(0));
            if (sheet == null || sheet.getLastRowNum() == -1) {
                throw new RuntimeException("The first sheet is empty");
            }//from   w  w w .  j a  v a2 s  .com
            int limit = sheet.getLastRowNum();
            for (int i = 0; i <= limit; i++) {
                Row row = sheet.getRow(i);
                if (row == null || row.getCell(0) == null) {
                    break;
                }
                manager.subscribe(row.getCell(0).getStringCellValue(), row.getCell(1).getStringCellValue(),
                        row.getCell(2).getStringCellValue());
            }
        }
    }
}