while reading xlsx file from aem dam.

Hi @naresh536 ,

Please refer similar question along with some articles which may helps you

[0]: https://experienceleaguecommunities.adobe.com/t5/adobe-experience-manager/how-to-read-excel-file-data/td-p/304703

[1]: https://stackoverflow.com/questions/51080314/how-to-read-excel-from-aem-dam-and-make-it-available-to-download-for-user

Regards,

Santosh

Hi @naresh536 

Below is the code snippet. Hope this is useful

public class ExcelReader {

   protected static final Logger logger = LoggerFactory.getLogger(ExcelReader.class);

   public static List<ExcelBean> readFromExcel(InputStream inputStream) {
      List<ExcelBean> beanList = new ArrayList<>();
      try {
//       File file = new File(fileName);
//       FileInputStream inputStream = new FileInputStream(file);
//       logger.info(file.getName());

         XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
         XSSFSheet sheet = workbook.getSheetAt(0);
         for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            XSSFRow row = sheet.getRow(i);
            XSSFCell cellValue1 = row.getCell(0);
            XSSFCell cellValue2 = row.getCell(1);
            XSSFCell cellValue3 = row.getCell(2);
            String path = null != cellValue1 ? cellValue1.getStringCellValue().trim() : "";
            String compModified = null != cellValue2 ? cellValue2.getStringCellValue().trim() : "";
            String compNotModified = null != cellValue3 ? cellValue3.getStringCellValue().trim() : "";
            ExcelBean bean = new ExcelBean(path.trim(), compModified, compNotModified);
            beanList.add(bean);
         }

         workbook.close();

      } 
      catch (IOException ex) {
         logger.error("IOException Caught " + ex);
      }
      return beanList;
   }

   /**
    * This method is to update the page properties in jcr:content
    * 
    * @param fileName
    * @return
    */
   public static List<PathPagePropertiesBean> readPathPagePropertiesFromExcel(InputStream inputStream) {

      List<PathPagePropertiesBean> beanList = new ArrayList<>();
      try {
//       File file = new File(fileName);
//       FileInputStream inputStream = new FileInputStream(file);
//       System.out.println(file.getName());

         Map<String, String> map = new LinkedHashMap<>();
         XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
         XSSFSheet sheet = workbook.getSheetAt(1);

         // Read the excel and form the map
         for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            XSSFRow row = sheet.getRow(i);
            XSSFCell cellValue1 = row.getCell(0);
            XSSFCell cellValue2 = row.getCell(1);
            String path = null != cellValue1 ? cellValue1.getStringCellValue().trim() : "";
            String pageProperties = null != cellValue2 ? cellValue2.getStringCellValue().trim() : "";

            if (map.containsKey(path)) {
               String values = pageProperties;
               values = map.get(path) + "," + values;
               map.put(path, values);
            } else {
               map.put(path, pageProperties);
            }

         }
         workbook.close();
         // Iterating the map and converting it to POJO
         for (Map.Entry<String, String> entry : map.entrySet()) {
            PathPagePropertiesBean bean = null;
            String[] pagePropsArr = entry.getValue().split(",");
            List<String> properties = new ArrayList<>();
            if (null != pagePropsArr && pagePropsArr.length > 0) {
               for (int j = 0; j < pagePropsArr.length; j++) {
                  properties.add(pagePropsArr[j].trim());
               }
               bean = new PathPagePropertiesBean(entry.getKey().trim(), properties);
               beanList.add(bean);
            }
         }
         logger.info("Bean size", beanList.size());

      } 
      catch (IOException ex) {
         logger.error("IOException Caught " + ex);
      }
      return beanList;
   }
}