Expand my Community achievements bar.

Guidelines for the Responsible Use of Generative AI in the Experience Cloud Community.
SOLVED

while reading xlsx file from aem dam.

Avatar

Level 2

Hi Team,

 

I am reading xlsx file from aem dam. while reading i am getting "invalid block type" error. 

aem 6.5 cloud

Tried with max all the versions of poi.

org.apache.poi

 

Thanks

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

@Naresh536 I have tried below pom dependency and it worked for me 

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>

View solution in original post

4 Replies

Avatar

Community Advisor

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

Avatar

Level 2

Thanks for quick response @Jagadeesh_Prakash ,

 

I have tried same and different Apis, but facing the compiler error.

 "class file for org.apache.poi.ooxml.POIXMLDocumentPart not found"

updated the pom with different versions. but no luck.

<dependency>

    <groupId>org.apache.poi</groupId>

    <artifactId>poi</artifactId>

    <version>4.1.0</version>

</dependency>

 

Avatar

Correct answer by
Community Advisor

@Naresh536 I have tried below pom dependency and it worked for me 

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>