Apache POI (http://poi.apache.org/index.html) is a set of APIs providing access to many Microsoft products including Excel and Word. It consists of a series of components designed to access a specific Microsoft product. An overview of these components is found at http://poi.apache.org/overview.html.

In this section we will demonstrate how to read a simple Excel spreadsheet using the XSSF component to access Excel 2007+ spreadsheets. The Javadocs for the Apache POI API is found at https://poi.apache.org/apidocs/index.html.

We will use a simple Excel spreadsheet consisting of a series of rows containing an ID along with minimum, maximum, and average values. These numbers are not intended to represent any specific type of data. The spreadsheet follows:

We start with a try-with-resources block to handle any IOExceptions that may occur:

try (FileInputStream file = new FileInputStream( 
        new File("Sample.xlsx"))) { 
    ... 
    } 
} catch (IOException e) { 
    // Handle exceptions 
} 

An instance of a XSSFWorkbook class is created using the spreadsheet. Since a workbook may consists of multiple spreadsheets, we select the first one using the getSheetAt method.

XSSFWorkbook workbook = new XSSFWorkbook(file); 
XSSFSheet sheet = workbook.getSheetAt(0); 

The next step is to iterate through the rows, and then each column, of the spreadsheet:

for(Row row : sheet) { 
    for (Cell cell : row) { 
        ... 
    } 
out.println(); 

Each cell of the spreadsheet may use a different format. We use the getCellType method to determine its type and then use the appropriate method to extract the data in the cell. In this example we are only working with numeric and text data.

switch (cell.getCellType()) { 
    case Cell.CELL_TYPE_NUMERIC: 
        out.print(cell.getNumericCellValue() + "	"); 
        break; 
    case Cell.CELL_TYPE_STRING: 
        out.print(cell.getStringCellValue() + "	"); 
        break; 
   } 

When executed we get the following output:

ID Minimum Maximum Average 
12345.0 45.0 89.0 65.55
23456.0 78.0 96.0 86.75
34567.0 56.0 89.0 67.44
45678.0 86.0 99.0 95.67

POI supports other more sophisticated classes and methods to extract data.

We will illustrate the first two approaches. The first approach is more efficient and is used when a large amount of data is processed. The second technique is convenient but the data must not be too large. The third technique is useful when it is more convenient to use specific Java classes to process data. For example, if the JSON data represent an address then a specific Java address class cane be defined to hold and process the data.

There are several Java libraries that support JSON processing including:

We will use the Jackson Project (https://github.com/FasterXML/jackson). Documentation is found at https://github.com/FasterXML/jackson-docs. We will use two JSON files to demonstrate how it can be used. The first file, Person.json, is shown next where a single person data is stored. It consists of four fields where the last field is an array of location information.

{  
   "firstname":"Smith", 
   "lastname":"Peter",  
   "phone":8475552222, 
   "address":["100 Main Street","Corpus","Oklahoma"]  
} 

The code sequence that follows shows how to extract the values for each of the fields. Within the try-catch block a JsonFactory instance is created which then creates a JsonParser instance based on the Person.json file.

try { 
    JsonFactory jsonfactory = new JsonFactory(); 
    JsonParser parser = jsonfactory.createParser(new File("Person.json")); 
    ... 
    parser.close(); 
} catch (IOException ex) { 
    // Handle exceptions 
} 

The nextToken method returns a token. However, the JsonParser object keeps track of the current token. In the while loop the nextToken method returns and advances the parser to the next token. The getCurrentName method returns the field name for the token. The while loop terminates when the last token is reached.

while (parser.nextToken() != JsonToken.END_OBJECT) { 
    String token = parser.getCurrentName(); 
    ... 
} 

The body of the loop consists of a series of if statements that processes the field by its name. Since the address field is an array, another loop will extract each of its elements until the ending array token is reached.

if ("firstname".equals(token)) { 
    parser.nextToken(); 
    String fname = parser.getText(); 
    out.println("firstname : " + fname); 
} 
if ("lastname".equals(token)) { 
    parser.nextToken(); 
    String lname = parser.getText(); 
    out.println("lastname : " + lname); 
} 
if ("phone".equals(token)) { 
    parser.nextToken(); 
    long phone = parser.getLongValue(); 
    out.println("phone : " + phone); 
} 
if ("address".equals(token)) { 
    out.println("address :"); 
    parser.nextToken(); 
    while (parser.nextToken() != JsonToken.END_ARRAY) { 
        out.println(parser.getText()); 
    } 
} 

The output of this example follows:

firstname : Smith
lastname : Peter
phone : 8475552222
address :
100 Main Street
Corpus
Oklahoma

However, JSON objects are frequently more complex than the previous example. Here a Persons.json file consists of an array of three persons:

{ 
   "persons": { 
      "groupname": "school", 
      "person": 
         [  
            {"firstname":"Smith", 
              "lastname":"Peter",  
              "phone":8475552222, 
              "address":["100 Main Street","Corpus","Oklahoma"] }, 
           {"firstname":"King", 
              "lastname":"Sarah",  
              "phone":8475551111, 
              "address":["200 Main Street","Corpus","Oklahoma"] }, 
           {"firstname":"Frost", 
              "lastname":"Nathan",  
              "phone":8475553333, 
              "address":["300 Main Street","Corpus","Oklahoma"] } 
         ] 
   } 
} 

To process this file, we use a similar set of code as shown previously. We create the parser and then enter a loop as before:

try { 
    JsonFactory jsonfactory = new JsonFactory(); 
    JsonParser parser = jsonfactory.createParser(new File("Person.json")); 
    while (parser.nextToken() != JsonToken.END_OBJECT) { 
        String token = parser.getCurrentName(); 
        ... 
    } 
    parser.close(); 
} catch (IOException ex) { 
    // Handle exceptions 
} 

However, we need to find the persons field and then extract each of its elements. The groupname field is extracted and displayed as shown here:

if ("persons".equals(token)) { 
    JsonToken jsonToken = parser.nextToken(); 
    jsonToken = parser.nextToken(); 
    token = parser.getCurrentName(); 
    if ("groupname".equals(token)) { 
        parser.nextToken(); 
        String groupname = parser.getText(); 
        out.println("Group : " + groupname); 
        ... 
    } 
} 

Next, we find the person field and call a parsePerson method to better organize the code:

parser.nextToken(); 
token = parser.getCurrentName(); 
if ("person".equals(token)) { 
    out.println("Found person"); 
    parsePerson(parser); 
} 

The parsePerson method follows which is very similar to the process used in the first example.

public void parsePerson(JsonParser parser) throws IOException { 
    while (parser.nextToken() != JsonToken.END_ARRAY) { 
        String token = parser.getCurrentName(); 
        if ("firstname".equals(token)) { 
            parser.nextToken(); 
            String fname = parser.getText(); 
            out.println("firstname : " + fname); 
        } 
        if ("lastname".equals(token)) { 
            parser.nextToken(); 
            String lname = parser.getText(); 
            out.println("lastname : " + lname); 
        } 
        if ("phone".equals(token)) { 
            parser.nextToken(); 
            long phone = parser.getLongValue(); 
            out.println("phone : " + phone); 
        } 
        if ("address".equals(token)) { 
            out.println("address :"); 
            parser.nextToken(); 
            while (parser.nextToken() != JsonToken.END_ARRAY) { 
                out.println(parser.getText()); 
            } 
        } 
    } 
} 

The output follows:

Group : school
Found person
firstname : Smith
lastname : Peter
phone : 8475552222
address :
100 Main Street
Corpus
Oklahoma
firstname : King
lastname : Sarah
phone : 8475551111
address :
200 Main Street
Corpus
Oklahoma
firstname : Frost
lastname : Nathan
phone : 8475553333address :
300 Main Street
Corpus
Oklahoma

The second approach is to use the tree model. An ObjectMapper instance is used to create a JsonNode instance using the Persons.json file. The fieldNames method returns Iterator allowing us to process each element of the file.

try { 
    ObjectMapper mapper = new ObjectMapper(); 
    JsonNode node = mapper.readTree(new File("Persons.json")); 
    Iterator<String> fieldNames = node.fieldNames(); 
    while (fieldNames.hasNext()) { 
        ... 
        fieldNames.next(); 
    } 
} catch (IOException ex) { 
    // Handle exceptions 
} 

Since the JSON file contains a persons field, we will obtain a JsonNode instance representing the field and then iterate over each of its elements.

JsonNode personsNode = node.get("persons"); 
Iterator<JsonNode> elements = personsNode.iterator(); 
while (elements.hasNext()) { 
    ... 
} 

Each element is processed one at a time. If the element type is a string, we assume that this is the groupname field.

JsonNode element = elements.next(); 
JsonNodeType nodeType = element.getNodeType(); 
 
if (nodeType == JsonNodeType.STRING) { 
    out.println("Group: " + element.textValue()); 
} 

If the element is an array, we assume it contains a series of persons where each person is processed by the parsePerson method:

if (nodeType == JsonNodeType.ARRAY) { 
    Iterator<JsonNode> fields = element.iterator(); 
    while (fields.hasNext()) { 
        parsePerson(fields.next()); 
    } 
}

The parsePerson method is shown next:

public void parsePerson(JsonNode node) { 
    Iterator<JsonNode> fields = node.iterator(); 
    while(fields.hasNext()) { 
        JsonNode subNode = fields.next(); 
        out.println(subNode.asText()); 
    } 
}

The output follows:

Group: school
Smith
Peter
8475552222
King
Sarah
8475551111
Frost
Nathan
8475553333

There is much more to JSON than we are able to illustrate here. However, this should give you an idea of how this type of data can be handled.