How to read an excel file from aem dam and covert it into a json file

[AkkiSwami]

How to read excel from AEM Dam and covert it to a json file

[Sady_Rifat]

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>

[AkkiSwami]

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

[BrianKasingli]

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);
        }
    }
}

[AkkiSwami]

Hi BraianKasingli,

At line 

InputStream xmlInputStream = xmlResource.adaptTo(InputStream.class);

I am getting null for xmlInputStream

[Nikita_Mazire]

It works for me, Thanks!

[kautuk_sahni]

@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.