How to read excel from AEM Dam and covert it to a json file
Views
Replies
Total Likes
Hi @AkkiSwami ,
You can follow this code, In this code I read excel data from DAM and send servlet response into Json. You can modify by your own.
JsonNode jsonNode = null;
Resource resource = resourceResolver.getResource(damPath);
if (resource == null || resource.adaptTo(Asset.class) == null) {
response.sendError(HttpServletResponse.SC_NOT_FOUND);
return;
}
Asset asset = resource.adaptTo(Asset.class);
InputStream inputStream = asset.getOriginal().getStream();
ObjectNode objectNode = readDataFromExcel(inputStream);
ObjectMapper objectMapper = new ObjectMapper();
jsonNode = objectMapper.valueToTree(objectNode);
response.setContentType("application/json");
response.setStatus(HttpServletResponse.SC_OK);
objectMapper.writeValue(response.getWriter(), jsonNode);
public ObjectNode readDataFromExcel(InputStream is) {
try (Workbook workbook = WorkbookFactory.create(is)) {
Sheet sheet = workbook.getSheetAt(0);
List<String> headers = new ArrayList<>();
ObjectMapper mapper = new ObjectMapper();
ObjectNode excelData = mapper.createObjectNode();
ArrayNode sheetData = mapper.createArrayNode();
for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
Row row = sheet.getRow(rowIndex);
if (rowIndex == 0) {
headers = getHeadersFromSheet(row);
} else {
sheetData.add(getValuesFromSheet(row, headers));
}
}
excelData.set("data", sheetData);
return excelData;
} catch (IOException e) {
log.error("Exception Occurred: {}", e.getMessage(), e);
return null;
}
}
and you need to add the following dependency,
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.0</version>
</dependency>
I have huge data in my excel in row-column format that is stored in dam
filePath - /content/dam/global/coupon/VC_TEST_FILE.xlsx
I have to covert that excel data in Json and have to send that to frontend
Adding to @Sady_Rifat's response.
Updated: please visit my website for the full implementation > https://sourcedcode.com/blog/aem/aem-convert-excel-to-json-simple-servlet-example
GET request
http://localhost:4502/bin/exportexcelastext.json
Here's an example of converted excel to JSON file.
{
"people": [
{"name": "brian", "age": "100"},
{"name": "sukpal", "age": "20"},
{"name": "britney", "age": "500"}
]
}
Here's the example of how the excel file looks like
name,age
brian,100
sukpal,20
britney,500
Here's the implementation of the servlet:
import org.apache.sling.api.SlingHttpServletRequest;
import org.apache.sling.api.SlingHttpServletResponse;
import org.apache.sling.api.servlets.SlingAllMethodsServlet;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.servlet.Servlet;
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 org.osgi.service.component.annotations.Component;
@Component(
service = Servlet.class,
property = {
"sling.servlet.methods=GET",
"sling.servlet.paths=/bin/exportexcelastext"
}
)
public class ExcelToTextServlet extends SlingAllMethodsServlet {
@Override
protected void doGet(SlingHttpServletRequest request, SlingHttpServletResponse response) throws IOException {
try {
// Get the XML file path
String xmlFilePath = "/content/example/excel.xml";
// Get the resource resolver
ResourceResolver resourceResolver = request.getResourceResolver();
// Check if the XML file exists
Resource xmlResource = resourceResolver.getResource(xmlFilePath);
if (xmlResource == null) {
response.setStatus(HttpServletResponse.SC_NOT_FOUND);
return;
}
// Access the XML content as an InputStream
InputStream xmlInputStream = xmlResource.adaptTo(InputStream.class);
// Process the XML content and convert it to JSON
Workbook workbook = new XSSFWorkbook(xmlInputStream);
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) {
rowMap.put(headerCell.getStringCellValue(), dataCell.getStringCellValue());
}
}
jsonData.add(rowMap);
}
// Prepare the JSON response
Map<String, List<Map<String, String>>> jsonResponse = new LinkedHashMap<>();
jsonResponse.put("people", jsonData);
// 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);
}
}
}
Hi BraianKasingli,
At line
InputStream xmlInputStream = xmlResource.adaptTo(InputStream.class);
I am getting null for xmlInputStream
It works for me, Thanks!
Views
Replies
Total Likes
@AkkiSwami Did you find the suggestions from users helpful? Please let us know if more information is required. Otherwise, please mark the answer as correct for posterity. If you have found out solution yourself, please share it with the community.
Views
Replies
Total Likes