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

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
Level 9

@Narendra1 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

Level 9

Hi @Narendra1 

 

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
Level 9

@Narendra1 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>