Expand my Community achievements bar.

Guidelines for the Responsible Use of Generative AI in the Experience Cloud Community.

How to read excel file data

Avatar

Level 2

Hello,

        Is there any way to read the data of excel file from the content/dam.. I am trying to do it but i am not able to find the my pdf file from the dam.

  So please help me for this issue along with the code..

9 Replies

Avatar

Employee Advisor

I think you need to use binary text extraction. Check [1] for more details.

[1] CQ-OPS - How to Extract Content and Metadata from PDFs...

Avatar

Level 2

Sorry but it's not helpful for this issue.. I am trying ti get the value

from the csv file which is available in my content/dam but when i hiting

the servlet so it is showing me not found.. So i want all data from the csv

file and display it on my page. below i have attached my csv file info

On Thu, Jun 20, 2019 at 7:50 PM JaideepBrar <forums_noreply@adobe.com>

Avatar

Community Advisor

Shashank

    I think you can follow the below algorithm

1) Read the file as an asset from DAM

2) Once you get the asset , get the original rendition

3) from the original , get the input stream

A sample code is given below assuming , you are passing the DAM path resource

1776153_pastedImage_0.png

4)convert the inputstream to file

5)once you have the file , treat it as a normal JAVA code , where you will read a CSV file data

an example here https://www.geeksforgeeks.org/reading-csv-file-java-using-opencv/

PS:- If you want to read this as an XLSX , I have some samples which I can share if needed

Thanks

Veena

Hi Veena, 

How to read the data from excel file uploaded in dam?

could you please give solution for this?

Avatar

Level 2

public interface ExcelService {

    List<JsonObject> getExcelInformation (String ExcelData);

}

@Component(service=ExcelService.class)

public class ExcelServiceImpl implements ExcelService{

@Reference

ResourceResolverFactory resourceResolverFactory;

@Override

public List<JsonObject> getExcelInformation(String ExcelData) {

Map<String, Object> map = new HashMap<>();

map.put(ResourceResolverFactory.SUBSERVICE, Constants.User.AEMSysUser);

     InputStream inputstream=null;

try {

ResourceResolver resourceResolver = resourceResolverFactory.getServiceResourceResolver(map);

Session session = resourceResolver.adaptTo(Session.class);

if (session.itemExists("/content/dam/balic/C2ImportCalEventSample.csv")) {

Node getExcel = session.getNode("/content/dam/balic/C2ImportCalEventSample.csv");

Node rootnode=session.getRootNode();

                     if(getExcel!=null) {

                    inputstream=(InputStream) rootnode.getSession();

                     }

              

}

return (List<JsonObject>) inputstream;

}catch(Exception ex) {

ex.printStackTrace();

// TODO Auto-generated method stub

}

return null;

}

}

@Component(service = Servlet.class, property = {

Constants.SERVICE_DESCRIPTION + "=Get Excel Demo Servlet",

    "sling.servlet.methods="+ HttpConstants.METHOD_POST,

    "sling.servlet.methods="+ HttpConstants.METHOD_GET,

    "sling.servlet.paths="+ "/bin/csvfile",

    "sling.servlet.extension=" + "json" })

public class AddExeclInfo extends SlingAllMethodsServlet {

private static final String ExcelData = null;

@Reference

ExcelService excelService;

    @Override

    protected void doGet(SlingHttpServletRequest request, SlingHttpServletResponse response)

    throws ServletException, IOException {

           PrintWriter pr=response.getWriter();

         

    List<JsonObject> getExcelInformation=excelService.getExcelInformation(ExcelData);

             if(getExcelInformation.isEmpty()) {

            pr.println(getExcelInformation);

             }else {

            pr.println("something went wrong");

             }

      

    }

    }

This is what i am trying to get my Excel data but

Resource at '/bin/csvfile' not found: No resource found (404)

The requested URL /bin/csvfile resulted in an error in org.apache.sling.servlets.resolver.internal.defaults.DefaultErrorHandlerServlet.

Request Progress:

      0 TIMER_START{Request Processing} 1 COMMENT timer_end format is 
{<elapsed microseconds>,<timer name>} <optional message> 7 LOG Method=GET,
PathInfo=null 7 TIMER_START{handleSecurity} 3487 TIMER_END{3478,handleSecurity}
authenticator org.apache.sling.auth.core.impl.SlingAuthenticator@4426f55c returns
true 3895 TIMER_START{ResourceResolution} 4233 TIMER_END{337,ResourceResolution}
URI=/bin/csvfile resolves to Resource=NonExistingResource, path=/bin/csvfile 4238
LOG Resource Path Info: SlingRequestPathInfo: path='/bin/csvfile', selectorString='null',
extension='null', suffix='null' 4238 TIMER_START{ServletResolution} 4241
TIMER_START{resolveServlet(/bin/csvfile)} 5226 LOG {0}: no servlet found 5233
TIMER_END{990,resolveServlet(/bin/csvfile)} Using
servlet org.apache.sling.servlets.get.DefaultGetServlet 5237
TIMER_END{998,ServletResolution} URI=/bin/csvfile handled by
Servlet=org.apache.sling.servlets.get.DefaultGetServlet 5240 LOG Applying Requestfilters

Avatar

Community Advisor

From the error it is clear that your servlet is not registered. Please do below checks

1) Check if the bundle is active ?

2) If Bundle is active , then go to /system/console/components and look for your servlet ? You can search your servlet name (AddExeclInfo) , and check if the servlet is active

Your servlet should be in active state. Foe ex ..

1777129_pastedImage_1.png

3) If your servlet is showing unsatisfied , just click it and check what is causing the issue. For example ..

1777298_pastedImage_2.png

1777299_pastedImage_3.png

  Here this servlet is unsatisfied because the Referencing service in the servlet is not registered properly.

If you are not able to solve this still , please follow the above steps and comment the screenshots so that we can check

Thanks

Veena

Avatar

Level 2
Reference excelServiceSatisfied
Service Name: mybalic.core.services.search.ExcelService
Cardinality: 1..1
Policy: static
Policy Option: reluctant
Bound Service ID 49915 (mybalic.core.services.search.impl.ExcelServiceImpl)

It is showing me satishfied

@Component(service = Servlet.class, property = {

Constants.SERVICE_DESCRIPTION + "=Get Excel Demo Servlet",

    "sling.servlet.methods="+ HttpConstants.METHOD_POST,

    "sling.servlet.methods="+ HttpConstants.METHOD_GET,

    "sling.servlet.paths="+ "/bin/csvfile",

    "sling.servlet.extension=" + "json" })

public class AddExeclInfo extends SlingAllMethodsServlet {

@Reference

ExcelService excelService;

    @Override

    protected void doGet(SlingHttpServletRequest request, SlingHttpServletResponse response)

    throws ServletException, IOException {

           PrintWriter pr=response.getWriter();

           String getExcelInformation = "";

    String folderName = request.getParameter("folderName");

TreeSet<String> yearSet = excelService.getExcelInfo(folderName);

    if(Objects.nonNull(yearSet)) {

    pr.println(yearSet);

    }else {

    pr.println("something is wrong");

    }

    }

when i hit the servlet it is showing me else condition..

Avatar

Community Advisor

I believe this is because your service is delayed activation. Do you have immediate=true property in your excelService ? a sample below !

1781238_pastedImage_0.png