Using the WebCenter Sites REST API – Part 2

This is the second part in our introductory tutorial to the WebCenter Sites REST API.

Part one can be found here.

We’ll be carrying on where we left off last time and you can either clone the updated project from GitHub or follow along yourself.

Last time we created a generic rest client for talking to the WCS Rest API.

This time we’re going to use it to do something useful. We’re going to use it to maintain our WCS users in a Excel Spreadsheet.

Getting Started

To start we need to add one new dependency to our POM

<!-- poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.9</version>
</dependency>

POI is an Apache project for reading and writing Microsoft Office documents.

Today we’re going to use it to read a spreadsheet with data about our users.

You can read more about POI here.

We’ve chosen to include the dependency that works with the XLSX file format.

So lets start by looking at the data in our sample spreadsheet (you can find the spreadsheet as part of the GitHub project in src/main/resources)

Our table has four columns:

• Name – name of the user to create
• Password – password of the user to create
• Acls – acls of the user to create
• Sites[Roles] – a mapping of the sites this user should have access to and the roles they have in that site

Reading Data from Excel

Now we have our sample data we need to try and read and parse it.

Lets have a look at the field where we store our spreadsheet data and the method we’ll use to load it from the filesystem

private Workbook workBook;

private void loadWorkbook(File spreadsheet) {
    try {
        OPCPackage pkg = OPCPackage.open(spreadsheet);
        workBook = new XSSFWorkbook(pkg);
        pkg.close();
    } catch (InvalidFormatException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

The OPCPackage class provides a less memory hungry way of working with spreadsheets in POI than an InputStream although in our case it’s probably arguable that three rows in a spreadsheet doesn’t constitute a particularly big resource drain.

Next we’ll look to parse the data out of the spreadsheet and create some UserBeans from it

private static final int USER_SITES = 3;
private static final int ACLS = 2;
private static final int PASSWORD = 1;
private static final int NAME = 0;

private List<UserBean> getUsersFromWorkbook() {
    List<UserBean> users = new ArrayList<UserBean>();
    Sheet sheet = workBook.getSheetAt(0);
    for (int i = 1; i <= sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);

        String name = row.getCell(NAME).getStringCellValue();
        String password = row.getCell(PASSWORD).getStringCellValue();
        List<String> acls = getAcls(row.getCell(ACLS).getStringCellValue());
        List<UserSite> userSites = getUserSites(row.getCell(USER_SITES).getStringCellValue()); 

        UserBean newUser = buildUser(name, password, acls, userSites);
        users.add(newUser);
    }
    return users;
}

There are a couple of methods here that we should look at in more detail.

The first two are helper methods for deserialising the format that we’ve stored our acls and site->role mappings in the spreadsheet.

The method getUserSites splits the string it receives to find the site and the list of comma delimited roles and then converts that string into a list of strings that describe each role.

The method getAcls simply parses our comma delimited list of acls and returns it as a list.

private List<UserSite> getUserSites(String stringCellValue) {
    List<UserSite> userSitesToReturn = new ArrayList<UserSite>();
    String[] siteRole = stringCellValue.split("=>");
    UserSite userSite = new UserSite();
    userSite.setSite(siteRole[0]);
    userSite.getRoles().addAll(Lists.newArrayList(Splitter.on(',').split(siteRole[1])));
    userSitesToReturn.add(userSite);
    return userSitesToReturn;
}

private List<String> getAcls(String stringCellValue) {
    return Lists.newArrayList(Splitter.on(',').split(stringCellValue));
} 

Creating User Beans

Next we have a simple method for creating our UserBeans – buildUser

private UserBean buildUser(String name, String password, List<String> acls, List<UserSite> siteUsers) {
    UserBean newUser = new UserBean();
    newUser.setName(name);
    newUser.setPassword(password);
    newUser.getAcls().addAll(acls);
    newUser.getSites().addAll(siteUsers);
    return newUser;
}

Updating users in the system

And now that we have our list of users we can use our RestClient to create them in the system.

public void createUsersFromSpreadsheet(File spreadsheet) throws RestConnectionException {
    loadWorkbook(spreadsheet);
    for (UserBean user : getUsersFromWorkbook()) {
        ClientResponse userPut = restClient.put(user, "/users/"+user.getName());
        if (userPut.getStatus() == 200) {
            System.out.println(String.format("The user: %s was successfully added",user.getName()));
        } else if (userPut.getStatus() == 500){
            ErrorBean error = userPut.getEntity(ErrorBean.class);
            System.out.println(String.format("The user: %s could not be added because %s",user.getName(), error.getMessage()));
        }
    }
}

Of interest here is what happens when there’s a problem.

If you run the ExcelUserManagerRunner more than once you’ll get a message saying that the user already exists. This is where the value of using ClientResponse comes in to play.

We can look at the response status and if we get a 500 (error code) we can try and parse the error into the WCS supplied ErrorBean.

That’s all for now but there are many things you could do to extend this project – for example:

• POI can write Excel files as well as read them so perhaps you could create an output function as well
• Perhaps you could look at storing other WebCenter Sites resources in the spreadsheet – assets for example. This could give you a good way to manage test data as part of your WCS development process.

Let me know how you get on in the comments below.