Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn More

View all

Sign in to view all badges

How to read excel file data

Avatar

Avatar
Validate 1
Level 2
shashanks526115
Level 2

Like

1 like

Total Posts

11 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Ignite 1
Give Back 3
Give Back
Boost 1
View profile

Avatar
Validate 1
Level 2
shashanks526115
Level 2

Like

1 like

Total Posts

11 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Ignite 1
Give Back 3
Give Back
Boost 1
View profile
shashanks526115
Level 2

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

Avatar

Avatar
Coach
Employee
jbrar
Employee

Likes

389 likes

Total Posts

869 posts

Correct Reply

283 solutions
Top badges earned
Coach
Establish
Give Back 50
Give Back 5
Give Back 3
View profile

Avatar
Coach
Employee
jbrar
Employee

Likes

389 likes

Total Posts

869 posts

Correct Reply

283 solutions
Top badges earned
Coach
Establish
Give Back 50
Give Back 5
Give Back 3
View profile
jbrar
Employee

20-06-2019

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

Avatar
Validate 1
Level 2
shashanks526115
Level 2

Like

1 like

Total Posts

11 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Ignite 1
Give Back 3
Give Back
Boost 1
View profile

Avatar
Validate 1
Level 2
shashanks526115
Level 2

Like

1 like

Total Posts

11 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Ignite 1
Give Back 3
Give Back
Boost 1
View profile
shashanks526115
Level 2

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>

Avatar

Avatar
Establish
MVP
Ravi_Pampana
MVP

Likes

192 likes

Total Posts

243 posts

Correct Reply

75 solutions
Top badges earned
Establish
Contributor
Shape 1
Ignite 5
Ignite 3
View profile

Avatar
Establish
MVP
Ravi_Pampana
MVP

Likes

192 likes

Total Posts

243 posts

Correct Reply

75 solutions
Top badges earned
Establish
Contributor
Shape 1
Ignite 5
Ignite 3
View profile
Ravi_Pampana
MVP

20-06-2019

Avatar

Avatar
Establish
MVP
Veena_Vikram
MVP

Likes

484 likes

Total Posts

1,048 posts

Correct Reply

121 solutions
Top badges earned
Establish
Coach
Contributor 2
Seeker
Ignite 5
View profile

Avatar
Establish
MVP
Veena_Vikram
MVP

Likes

484 likes

Total Posts

1,048 posts

Correct Reply

121 solutions
Top badges earned
Establish
Coach
Contributor 2
Seeker
Ignite 5
View profile
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

Avatar

Avatar
Validate 1
Level 2
shashanks526115
Level 2

Like

1 like

Total Posts

11 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Ignite 1
Give Back 3
Give Back
Boost 1
View profile

Avatar
Validate 1
Level 2
shashanks526115
Level 2

Like

1 like

Total Posts

11 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Ignite 1
Give Back 3
Give Back
Boost 1
View profile
shashanks526115
Level 2

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

Avatar

Avatar
Establish
MVP
Veena_Vikram
MVP

Likes

484 likes

Total Posts

1,048 posts

Correct Reply

121 solutions
Top badges earned
Establish
Coach
Contributor 2
Seeker
Ignite 5
View profile

Avatar
Establish
MVP
Veena_Vikram
MVP

Likes

484 likes

Total Posts

1,048 posts

Correct Reply

121 solutions
Top badges earned
Establish
Coach
Contributor 2
Seeker
Ignite 5
View profile
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

Avatar

Avatar
Validate 1
Level 2
shashanks526115
Level 2

Like

1 like

Total Posts

11 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Ignite 1
Give Back 3
Give Back
Boost 1
View profile

Avatar
Validate 1
Level 2
shashanks526115
Level 2

Like

1 like

Total Posts

11 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Ignite 1
Give Back 3
Give Back
Boost 1
View profile
shashanks526115
Level 2

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

Avatar

Avatar
Establish
MVP
Veena_Vikram
MVP

Likes

484 likes

Total Posts

1,048 posts

Correct Reply

121 solutions
Top badges earned
Establish
Coach
Contributor 2
Seeker
Ignite 5
View profile

Avatar
Establish
MVP
Veena_Vikram
MVP

Likes

484 likes

Total Posts

1,048 posts

Correct Reply

121 solutions
Top badges earned
Establish
Coach
Contributor 2
Seeker
Ignite 5
View profile
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