Expand my Community achievements bar.

SOLVED

create a node and put text properties and then read the text from excel or any file

Avatar

Level 2

Once page is created add text to node and within that node add properties and then the value of this text properties you going to read it from some file or excel or csv

 

(All the data for text coming into an json or xml file. we have to read it from there.

if page path matches to page node path then get the text and then you will have to inject)

 

please anyone explain step by step process how to do

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Hi @AEM1234567890 ,
Your question has 3 requirements. Let me start with first one. Did not understand later two.
1. You wand to add a node on page creation.
2. Properties of these node should be fetch from some source(ex file, excel).

 

Let me propose a solution for this.

1. Create a workflow or Event Handler. I will prefer workflow with custom workflow process. Trigger this workflow on page creation. 
   Create a node inside this custom workflow process.

   Fetch property/value form excel. Assuming excel file is in dam and having two columns. first column has properties and second columns has values agains those properties. 
We will use apache poi api to read excel. These API are available in AEM, SO you don't need to add additional API. 

  Assuming this code is written in custom process. Please note this is pseudo code. Update node path and name as per your project setup.

 

            final ResourceResolver resourceResolver = workflowSession.adaptTo(ResourceResolver.class);;
            String filePath = "/content/dam/excel-file-path";
            Asset fileAsset=resourceResolver.getResource(filePath).adaptTo(Asset.class);
            InputStream inputStream=fileAsset.getRendition("original").getStream();
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            XSSFSheet sheet = workbook.getSheetAt(0);
            Iterator<Row> rowIterator = sheet.iterator();
            Session session =workflowSession.adaptTo(Session.class);
            Node node =session.getNode("node path where you want to create child node");
            Node childNode=node.addNode("newly created node name");
            session.save();
            while (rowIterator.hasNext())
            {
                Row row = rowIterator.next();
                //For each row, iterate through all the columns
                Iterator<Cell> cellIterator = row.cellIterator();
                String prop=StringUtils.EMPTY;
                String val=StringUtils.EMPTY;

                while (cellIterator.hasNext())
                {
                    Cell cell = cellIterator.next();
                    int colIndex=cell.getColumnIndex();
                    if(colIndex%2==0){
                        prop=cell.getStringCellValue();
                    }else{
                        val=cell.getStringCellValue();
                    }

                }
                childNode.setProperty(prop,val);
                session.save();
            }

 

  I will try to answer your later two requirement, if you add some more details.

View solution in original post

5 Replies

Avatar

Community Advisor

Hi @AEM1234567890   If I understood your requirement correctly

 

1. You are creating pages programmatically through service.

2. You have data in CSV file which you want to read and then insert data as properties values.

 

Now, you can follow below steps-

1. To create pages programmatically, first you need to get ResourceResolver, which you will get using sub-service


try {
Map<String, Object> param = new HashMap<>();
param.put(ResourceResolverFactory.SUBSERVICE, "datawrite");
resourceResolver = resolverFactory.getServiceResourceResolver(param);
} catch (...) {
...
}
You can check below explanation for detail

Create system user and sub-service 


2. Now, you need to read CSV file, you can use Apache POI for that

https://howtodoinjava.com/java/library/readingwriting-excel-files-in-java-poi-tutorial/
https://www.geeksforgeeks.org/reading-writing-data-excel-file-using-apache-poi/

3. Now to save properties, you need to use above resource resolver

 

Resource metadataResource = resourceResolver
.getResource("<RESOURCE_PATH>");
ModifiableValueMap metadata = metadataResource.adaptTo(ModifiableValueMap.class);
metadata.put("<PROPERTY_NAME>", "<PROPERTY_VALUE>");
resourceResolver.commit();

 

Hope it helps! Cheers!

 

Avatar

Level 2

no need to create pages programatically

 

1. create node and put text properties

2.read that text from excell or csv file

 

Avatar

Correct answer by
Community Advisor

Hi @AEM1234567890 ,
Your question has 3 requirements. Let me start with first one. Did not understand later two.
1. You wand to add a node on page creation.
2. Properties of these node should be fetch from some source(ex file, excel).

 

Let me propose a solution for this.

1. Create a workflow or Event Handler. I will prefer workflow with custom workflow process. Trigger this workflow on page creation. 
   Create a node inside this custom workflow process.

   Fetch property/value form excel. Assuming excel file is in dam and having two columns. first column has properties and second columns has values agains those properties. 
We will use apache poi api to read excel. These API are available in AEM, SO you don't need to add additional API. 

  Assuming this code is written in custom process. Please note this is pseudo code. Update node path and name as per your project setup.

 

            final ResourceResolver resourceResolver = workflowSession.adaptTo(ResourceResolver.class);;
            String filePath = "/content/dam/excel-file-path";
            Asset fileAsset=resourceResolver.getResource(filePath).adaptTo(Asset.class);
            InputStream inputStream=fileAsset.getRendition("original").getStream();
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            XSSFSheet sheet = workbook.getSheetAt(0);
            Iterator<Row> rowIterator = sheet.iterator();
            Session session =workflowSession.adaptTo(Session.class);
            Node node =session.getNode("node path where you want to create child node");
            Node childNode=node.addNode("newly created node name");
            session.save();
            while (rowIterator.hasNext())
            {
                Row row = rowIterator.next();
                //For each row, iterate through all the columns
                Iterator<Cell> cellIterator = row.cellIterator();
                String prop=StringUtils.EMPTY;
                String val=StringUtils.EMPTY;

                while (cellIterator.hasNext())
                {
                    Cell cell = cellIterator.next();
                    int colIndex=cell.getColumnIndex();
                    if(colIndex%2==0){
                        prop=cell.getStringCellValue();
                    }else{
                        val=cell.getStringCellValue();
                    }

                }
                childNode.setProperty(prop,val);
                session.save();
            }

 

  I will try to answer your later two requirement, if you add some more details.

Avatar

Level 2

1.creating a node and set properties through json and read that json file using servlet 

2.give the node path in excel and read excel from servlet then after it will add a node in aem