AEM Convert Excel to JSON Simple Servlet Example

In this article, I present a straightforward yet powerful solution that demonstrates how to seamlessly convert Excel data into JSON format using Adobe Experience Manager (AEM). By implementing a custom servlet, I will illustrate the process of converting an Excel sheet named ‘example.xlsx’ into structured JSON data; it can be downloaded below for you following this tutorial. This tutorial is validated on both AEM 6.5 with the latest Service Pack and AEM as a Cloud Service (AEMaaCS), ensuring its compatibility with diverse AEM environments.

Resource File:
example.xlsx

Request URI:
The request URI for accessing the JSON output through the ExampleServlet that we’ve been discussing would typically look like this:

1	http://localhost:4502/bin/exportexcelfortext.json

JSON Output:

1
2
3
4
5
6
7
8

{
"people": [
{ "name": "Brian", "age": "100" },
{ "name": "Doris", "age": "20" },
{ "name": "Stuart", "age": "103" },
{ "name": "David", "age": "5" }
]
}

Servlet Implementation ExcelExampleServlet.java:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109

package com.sourcedcode.core.servlets;

import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.sling.api.SlingHttpServletRequest;
import org.apache.sling.api.SlingHttpServletResponse;
import org.apache.sling.api.resource.Resource;
import org.apache.sling.api.resource.ResourceResolver;
import org.apache.sling.api.servlets.SlingSafeMethodsServlet;
import org.osgi.service.component.annotations.Component;
import javax.servlet.Servlet;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import com.day.cq.dam.api.Asset;

@Component(
service = { Servlet.class },
property = {
"sling.servlet.paths=/bin/exportexcelfortext",
"sling.servlet.extensions=json",
"sling.servlet.methods=GET"
}
)
public class ExcelExampleServlet extends SlingSafeMethodsServlet {
@Override
protected void doGet(SlingHttpServletRequest request, SlingHttpServletResponse response) throws IOException {
try {
// Get the Excel file path in DAM
String excelFilePath = "/content/dam/example/example.xlsx";

// Get the resource resolver
ResourceResolver resourceResolver = request.getResourceResolver();

// Check if the Excel file exists
Resource excelResource = resourceResolver.getResource(excelFilePath);
if (excelResource == null) {
response.setStatus(HttpServletResponse.SC_NOT_FOUND);
return;
}

// Adapt the resource to an Asset
Asset asset = Objects.requireNonNull(excelResource).adaptTo(Asset.class);
if (asset == null) {
response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
return;
}

// Get an InputStream from the Asset's original
InputStream excelInputStream = Objects.requireNonNull(asset).getOriginal().getStream();

// Process the Excel content and convert it to JSON
Workbook workbook = new XSSFWorkbook(excelInputStream);
List<Map<String, String>> jsonData = new ArrayList<>();

// Iterate through the Excel rows and columns
Sheet sheet = workbook.getSheetAt(0); // Assuming it's the first sheet
Row headerRow = sheet.getRow(0); // Assuming the first row is the header row
int totalColumns = headerRow.getLastCellNum();

for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
Row dataRow = sheet.getRow(i);
Map<String, String> rowMap = new LinkedHashMap<>();

for (int j = 0; j < totalColumns; j++) {
Cell headerCell = headerRow.getCell(j);
Cell dataCell = dataRow.getCell(j);

if (headerCell != null && dataCell != null) {
// Check the cell type to handle numeric and string values
if (dataCell.getCellType() == CellType.STRING) {
rowMap.put(headerCell.getStringCellValue(), dataCell.getStringCellValue());
} else if (dataCell.getCellType() == CellType.NUMERIC) {
// Handle numeric values as integers (remove decimals)
int age = (int) dataCell.getNumericCellValue();
rowMap.put(headerCell.getStringCellValue(), String.valueOf(age));
}
}
}

jsonData.add(rowMap);
}

// Prepare the JSON response
List<Map<String, String>> peopleList = new ArrayList<>();
for (Map<String, String> personData : jsonData) {
Map<String, String> person = new LinkedHashMap<>();
person.put("name", personData.get("Name"));
person.put("age", personData.get("Age"));
peopleList.add(person);
}

Map<String, List<Map<String, String>>> jsonResponse = new LinkedHashMap<>();
jsonResponse.put("people", peopleList);

// Set response content type and write JSON content to the response
response.setContentType("application/json");
response.getWriter().write(new ObjectMapper().writeValueAsString(jsonResponse));
} catch (Exception e) {
response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
}
}
}

Servlet Depdendencies:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

<dependencies>

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

<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-core</artifactId>
<version>2.13.0</version>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
<version>2.13.0</version>
</dependency>
</dependencies>

AEM Convert Excel to JSON Simple Servlet Example