Highlighted

How to read excel file data

shashanks526115

20-06-2019

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

Replies

Highlighted

shashanks526115

20-06-2019

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>

Highlighted

Veena_Vikram

MVP

20-06-2019

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

Highlighted

shashanks526115

20-06-2019

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
Highlighted

Veena_Vikram

MVP

21-06-2019

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

Highlighted

shashanks526115

24-06-2019

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

Highlighted

Veena_Vikram

MVP

28-06-2019

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