Expand my Community achievements bar.

July 31st AEM Gems Webinar: Elevate your AEM development to master the integration of private GitHub repositories within AEM Cloud Manager.

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

Avatar

Level 1

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

5 Replies

Avatar

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>

Avatar

Level 1

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

Avatar

Community Advisor

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

 

 

Avatar

Level 1

Hi BraianKasingli,

 

At line 

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

I am getting null for xmlInputStream

Avatar

Administrator

@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