How to parse excel and save data in node

[Ronnie09]

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

[chetan001]

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

[SantoshSai · Accepted solution]

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;
 }
}