I am trying to parse excel(xlsx/csv) and save data in nodes. How should I proceed?
Solved! Go to Solution.
Views
Replies
Total Likes
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; } }
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
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; } }
Views
Likes
Replies
Views
Likes
Replies