How to read excel file data

[shashanks526115]

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

[Jaideep_Brar]

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

[shashanks526115]

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>

[VeenaVikraman]

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

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

[Maneesha_Thallapaneni]

Hi Veena, 

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

could you please give solution for this?

[Ravi_Pampana]

Hi,

For extracting data from csv

acs-aem-tools/CsvAssetImporterServlet.java at master · Adobe-Consulting-Services/acs-aem-tools · Git...

[shashanks526115]

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

[VeenaVikraman]

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

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

  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

[shashanks526115]

Reference excelService

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

[VeenaVikraman]

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