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

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

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

Introduction

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

Prototype

Sheet getSheet(String name);

Source Link

Document

Get sheet with the given name

Usage

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");
        }/*  w w  w.  j  ava 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;
            }
            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");
        }//w  w  w  .  ja  v a2  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;
            }
            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");
        }/*from  w  w w . j  a va2s  .c  o m*/
        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  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;
                }
                manager.subscribe(row.getCell(0).getStringCellValue(), row.getCell(1).getStringCellValue(),
                        row.getCell(2).getStringCellValue());
            }
        }
    }
}

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

License:Open Source License

private static void performLifecycleOperations(ConfigurationContext configContext, Registry registry)
        throws Exception {
    if (CommandHandler.getLifecycleConfigLocation() != null) {
        File lifecycleOperationsLocation = new File(CommandHandler.getLifecycleOperationsLocation());
        LifeCycleManagementClient client = new LifeCycleManagementClient(cookie, CommandHandler.getServiceURL(),
                configContext);/*from w w  w.  j  av  a2  s  .c  o  m*/
        if (lifecycleOperationsLocation.exists()) {
            Workbook[] workbooks = PopulatorUtil.getWorkbooks(lifecycleOperationsLocation, "actions");
            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;
                    }
                    String path = getCellValue(row.getCell(0), null);
                    String aspect = getCellValue(row.getCell(1), null);
                    String action = getCellValue(row.getCell(2), null);
                    if (action == null) {
                        if (aspect.equals(registry.get(path).getProperty("registry.LC.name"))) {
                            client.removeAspect(path, aspect);
                        } else {
                            client.addAspect(path, aspect);
                        }
                    } else {
                        String[] items = splitAndTrim(getCellValue(row.getCell(3), null), ",");
                        String temp = getCellValue(row.getCell(4), null);
                        if (temp == null) {
                            client.invokeAspect(path, aspect, action, items,
                                    Collections.<String, String>emptyMap());
                        } else {
                            Map<String, String> params = new LinkedHashMap<String, String>();
                            String[] pairs = splitAndTrim(temp, ",");
                            for (String pair : pairs) {
                                String[] keyAndValue = splitAndTrim(pair, "[|]");
                                params.put(keyAndValue[0], keyAndValue[1]);
                            }
                            client.invokeAspect(path, aspect, action, items, params);
                        }
                    }
                }
            }
        }
    }
}

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

License:Open Source License

private static void addUsers(File usersAndRolesDirectory, UserManagementClient userManager) throws Exception {
    Workbook[] workbooks = PopulatorUtil.getWorkbooks(usersAndRolesDirectory, "users");
    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");
        }//ww w .  j  a v  a2s. c  om
        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 name = row.getCell(0).getStringCellValue();
            String password = getCellValue(row.getCell(1), name + "123");
            String roles = getCellValue(row.getCell(2), null);
            userManager.addUser(name, password, splitAndTrim(roles == null ? null : roles, ","),
                    new ClaimValue[0], null);
            if (row.getCell(3) != null && row.getCell(3).getCellType() != Cell.CELL_TYPE_BLANK) {
                UserProfileDTO profile = userManager.getUserProfile(name, "default");
                UserFieldDTO[] fieldValues = profile.getFieldValues();
                String email = row.getCell(3).getStringCellValue();
                String firstName = getCellValue(row.getCell(4), name);
                String lastName = getCellValue(row.getCell(5), name);
                for (UserFieldDTO fieldValue : fieldValues) {
                    if (fieldValue.getClaimUri().equals("http://wso2.org/claims/emailaddress")) {
                        fieldValue.setFieldValue(email);
                    } else if (fieldValue.getClaimUri().equals("http://wso2.org/claims/givenname")) {
                        fieldValue.setFieldValue(firstName);
                    } else if (fieldValue.getClaimUri().equals("http://wso2.org/claims/lastname")) {
                        fieldValue.setFieldValue(lastName);
                    } else {
                        fieldValue.setFieldValue("");
                    }
                }
                userManager.setUserProfile(name, profile);
            }
        }
    }
}

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

License:Open Source License

private static void addRoles(File usersAndRolesDirectory, UserManagementClient userManager) throws Exception {
    Workbook[] workbooks = PopulatorUtil.getWorkbooks(usersAndRolesDirectory, "roles");
    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   www .  j  av 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;
            }
            String name = row.getCell(0).getStringCellValue();
            String[] permissions = splitAndTrim(row.getCell(1).getStringCellValue(), ",");
            if (name.equals("everyone") || name.equals("admin")) {
                userManager.setRoleUIPermission(name, permissions);
            } else {
                userManager.addRole(name, new String[0], permissions);
            }
        }
    }
}

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

License:Open Source License

private static void addTenants(File usersAndRolesDirectory, UserManagementClient userManager) throws Exception {
    Workbook[] workbooks = PopulatorUtil.getWorkbooks(usersAndRolesDirectory, "tenants");
    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  av  a2 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;
            }
            userManager.addTenant(getCellValue(row.getCell(0), null), getCellValue(row.getCell(1), null),
                    getCellValue(row.getCell(2), null), getCellValue(row.getCell(3), null),
                    getCellValue(row.getCell(4), null), getCellValue(row.getCell(5), null));
        }
    }
}

From source file:org.wso2.carbon.user.mgt.bulkimport.ExcelUserBulkImport.java

License:Open Source License

public void addUserList(UserStoreManager userStore) throws UserAdminException {
    try {/*  w w w . j a v a 2  s.  c  o  m*/
        Workbook wb = this.createWorkbook();
        Sheet sheet = wb.getSheet(wb.getSheetName(0));
        String password = config.getDefaultPassword();

        if (sheet == null || sheet.getLastRowNum() == -1) {
            throw new UserAdminException("The first sheet is empty");
        }
        int limit = sheet.getLastRowNum();
        boolean isDuplicate = false;
        boolean fail = false;
        boolean success = false;
        String lastError = "UNKNOWN";
        for (int i = 1; i < limit + 1; i++) {
            Row row = sheet.getRow(i);
            Cell cell = row.getCell(0);
            String userName = cell.getStringCellValue();
            if (userName != null && userName.trim().length() > 0) {
                try {
                    if (!userStore.isExistingUser(userName)) {
                        userStore.addUser(userName, password, null, null, null, true);
                        success = true;
                    } else {
                        isDuplicate = true;
                    }
                } catch (Exception e) {
                    if (log.isDebugEnabled()) {
                        log.debug(e);
                    }
                    lastError = e.getMessage();
                    fail = true;
                }
            }
        }

        if (fail && success) {
            throw new UserAdminException("Error occurs while importing user names. "
                    + "Some user names were successfully imported. Some were not. Last error was : "
                    + lastError);
        }

        if (fail && !success) {
            throw new UserAdminException("Error occurs while importing user names. "
                    + "All user names were not imported. Last error was : " + lastError);
        }
        if (isDuplicate) {
            throw new UserAdminException("Detected duplicate user names. "
                    + "Failed to import duplicate users. Non-duplicate user names were successfully imported.");
        }
    } catch (UserAdminException e) {
        throw e;
    }
}

From source file:org.wso2.carbon.user.mgt.bulkImport.JsonConverterTest.java

License:Open Source License

@Test(description = "Test the conversion of XLS to JSON.")
public void testConvertXLSToJSON() throws IOException {
    InputStream inputStream = getInputStreamForFile(XLS_FILENAME);
    Workbook testWorkBook = new HSSFWorkbook(inputStream);
    Sheet sheet = testWorkBook.getSheet(testWorkBook.getSheetName(0));

    String usersJson = jsonConverter.xlsToJSON(sheet);
    Assert.assertEquals(usersJson, USERS_LIST_JSON);
}