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
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

How to parse excel and save data in node

Avatar

Level 5

I am trying to parse excel(xlsx/csv) and save data in nodes. How should I proceed?

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Hi @Ronnie09 

 

Reading Excel - https://www.geeksforgeeks.org/reading-csv-file-java-using-opencsv/

Refer below code for writing in JCR

@Component(immediate = true, service = XMLWriterService.class)
public class CSVWriterServiceImpl implements CSVWriterService {

 // Logger
 private final Logger log = LoggerFactory.getLogger(this.getClass());

 // Injecting ResourceResolverFactory
 @Reference
 private ResourceResolverFactory resourceResolverFactory;

 // JCR session
 private Session session;

 /**
  * This method writes CSV data to the JCR repository
  */
 @Override
 public void writeCSVToJCR(ProductList productList, String jcrPath, String from) {

  log.info("Writing CSV data to nodes from: {}", from);

  try {

   // Getting the session
   session = getSession();

   // Getting root node of the CRX repository
   Node root = session.getRootNode();

   // Getting the reference of the node under which we need to create our nodes
   Node xmlNode = root.getNode(jcrPath);

   // Parent node of all the children nodes which are represented by individual
   // CSV items
   Node xmlDataNode = null;

   // Checks if the source is from CSV file and the node is already present
   if (from.equalsIgnoreCase("file") && !csvNode.hasNode("csv_file_products")) {
    csvDataNode = csvNode.addNode("csv_file_products", "sling:OrderedFolder");
   }

   // Checks if the source is from URL and the node is already present
   if (from.equalsIgnoreCase("url") && !csvNode.hasNode("csv_url_products")) {
    csvDataNode = csvNode.addNode("csv_url_products", "sling:OrderedFolder");
   }

   // Setting the title of the node
   if (csvDataNode != null) {
    csvDataNode.setProperty("jcr:title", "Products");
   } else {
    return;
   }

   // Getting the products from ProductList
   List<Product> products = productList.getProduct();

   // Iterate for each item present in the CSV file
   for (Product product : products) {
    
    Node currentNode = null;

    if (!csvDataNode.hasNode("product_" + product.getProductId())) {
     currentNode = csvDataNode.addNode("product_" + product.getProductId(), "nt:unstructured");
    } else {
     currentNode = csvDataNode.getNode("product_" + product.getProductId());
    }

    // Setting properties of the node
    currentNode.setProperty("Product_ID", product.getProductId());
    currentNode.setProperty("Name", product.getName());
   }

   // Saving the changes to JCR
   session.save();

  } catch (RepositoryException e) {
   log.error(e.getMessage(), e);
  }
 }

 private Session getSession() {
  try {
   // Map for service user details
   Map<String, Object> csvReaderMap = new HashMap<>();
   csvReaderMap.put(ResourceResolverFactory.SUBSERVICE, "csvReaderSubservice");

   // Getting ResourceResovler
   ResourceResolver resourceResolver = resourceResolverFactory.getServiceResourceResolver(csvReaderMap);

   // Getting the session by adapting the resourceResolver
   session = resourceResolver.adaptTo(Session.class);

  } catch (LoginException e) {
   log.error(e.getMessage(), e);
  }
  return session;
 }
}

 

View solution in original post

2 Replies

Avatar

Level 4

Hi,

 

read/parse excel data using apache POI

https://poi.apache.org/apidocs/5.0/

 

then save data to JCR

 

1. Create a Session 

2. Create a node

3. set excel object values to created node.

 

Thanks

Chetan

Avatar

Correct answer by
Community Advisor

Hi @Ronnie09 

 

Reading Excel - https://www.geeksforgeeks.org/reading-csv-file-java-using-opencsv/

Refer below code for writing in JCR

@Component(immediate = true, service = XMLWriterService.class)
public class CSVWriterServiceImpl implements CSVWriterService {

 // Logger
 private final Logger log = LoggerFactory.getLogger(this.getClass());

 // Injecting ResourceResolverFactory
 @Reference
 private ResourceResolverFactory resourceResolverFactory;

 // JCR session
 private Session session;

 /**
  * This method writes CSV data to the JCR repository
  */
 @Override
 public void writeCSVToJCR(ProductList productList, String jcrPath, String from) {

  log.info("Writing CSV data to nodes from: {}", from);

  try {

   // Getting the session
   session = getSession();

   // Getting root node of the CRX repository
   Node root = session.getRootNode();

   // Getting the reference of the node under which we need to create our nodes
   Node xmlNode = root.getNode(jcrPath);

   // Parent node of all the children nodes which are represented by individual
   // CSV items
   Node xmlDataNode = null;

   // Checks if the source is from CSV file and the node is already present
   if (from.equalsIgnoreCase("file") && !csvNode.hasNode("csv_file_products")) {
    csvDataNode = csvNode.addNode("csv_file_products", "sling:OrderedFolder");
   }

   // Checks if the source is from URL and the node is already present
   if (from.equalsIgnoreCase("url") && !csvNode.hasNode("csv_url_products")) {
    csvDataNode = csvNode.addNode("csv_url_products", "sling:OrderedFolder");
   }

   // Setting the title of the node
   if (csvDataNode != null) {
    csvDataNode.setProperty("jcr:title", "Products");
   } else {
    return;
   }

   // Getting the products from ProductList
   List<Product> products = productList.getProduct();

   // Iterate for each item present in the CSV file
   for (Product product : products) {
    
    Node currentNode = null;

    if (!csvDataNode.hasNode("product_" + product.getProductId())) {
     currentNode = csvDataNode.addNode("product_" + product.getProductId(), "nt:unstructured");
    } else {
     currentNode = csvDataNode.getNode("product_" + product.getProductId());
    }

    // Setting properties of the node
    currentNode.setProperty("Product_ID", product.getProductId());
    currentNode.setProperty("Name", product.getName());
   }

   // Saving the changes to JCR
   session.save();

  } catch (RepositoryException e) {
   log.error(e.getMessage(), e);
  }
 }

 private Session getSession() {
  try {
   // Map for service user details
   Map<String, Object> csvReaderMap = new HashMap<>();
   csvReaderMap.put(ResourceResolverFactory.SUBSERVICE, "csvReaderSubservice");

   // Getting ResourceResovler
   ResourceResolver resourceResolver = resourceResolverFactory.getServiceResourceResolver(csvReaderMap);

   // Getting the session by adapting the resourceResolver
   session = resourceResolver.adaptTo(Session.class);

  } catch (LoginException e) {
   log.error(e.getMessage(), e);
  }
  return session;
 }
}