Level 2

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

Forum|Forum|2 years ago
November 1, 2023
3 replies
2738 views

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

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

Sady_Rifat

Community Advisor

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>

A

AkkiSwamiAuthor

Level 2

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

Community Advisor and Adobe Champion

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

A

AkkiSwamiAuthor

Level 2

Hi BraianKasingli,

At line

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

I am getting null for xmlInputStream

kautuk_sahni

Community Manager

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

Kautuk Sahni

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded