Hi,
I am providing you my previous code snippet which I had been using to read the excel sheet and return the data.
@Override
public Map<String, WorkSheetData> readData (InputStream inputStream, String sheetName, BulkUploadInput uploadInput) throws IOException {
String methodName = "readData";
Map<String, WorkSheetData> resultMap= new LinkedHashMap<>();
Workbook workbook = null;
try {
logger.step(LoggerEnum.INFO, methodName,
String.format("Aspose file counter: %d", currentCount));
workbook = new Workbook(inputStream);
WorksheetCollection worksheets = workbook.getWorksheets();
if (sheetName != null && ! sheetName.isEmpty()) {
WorkSheetData mtslExcelResultSet = readSheet(worksheets, sheetName, uploadInput);
resultMap.put(sheetName, mtslExcelResultSet);
} else {
worksheets.forEach(o -> {
Worksheet sheet = (Worksheet) o;
resultMap.put(sheet.getName(), readSheet(worksheets, sheet.getName(), uploadInput));
sheet.dispose();
});
}
return resultMap;
} catch (CoreException e) {
this.logger.error(methodName, e.getMessage());
throw e;
} catch (Exception e) {
// Check if it's an Aspose evaluation exception
if (asposeException(e)) {
asposeLimitReached(methodName, e);
}
this.logger.error(methodName, e.getMessage());
throw new RuntimeException(e.getMessage(), e);
} finally {
if (workbook != null) {
workbook.dispose();
}
inputStream.close();
}
}
private WorkSheetData readSheet(WorksheetCollection worksheets, String sheetName, BulkUploadInput uploadInput) {
String methodName= "readSheet";
List<Map<String, Object>> tableData = new ArrayList<>();
WorkSheetData mtslExcelResultSet = new WorkSheetData();
Worksheet worksheet = worksheets.get(sheetName);
try {
if(worksheet == null){
logger.error(methodName, FILE_EXCEL_SHEET_NOT_FOUND.getMessage()+sheetName);
throw new ResourceNotFoundException(FILE_EXCEL_SHEET_NOT_FOUND);
}
setTablePoints(worksheet, uploadInput);
int startRow = uploadInput.getStartRow();
int endRow = uploadInput.getEndRow();
int startColumn = uploadInput.getStartColumn();
int endColumn = uploadInput.getEndColumn();
logger.step(LoggerEnum.INFO, methodName, String.format("Reading worksheet data: %s from row: %s to row: %s, and column: %s to column: %s", worksheet.getName(), startRow, endRow, startColumn, endColumn));
// Get the column names from the first row
Row headerRow = worksheet.getCells().getRows().get(startRow++);
Map<String, Integer> columnNames = new LinkedHashMap<>();
for (int colIdx = startColumn; colIdx <= endColumn; colIdx++) {
Cell headerCell = headerRow.get(colIdx);
String columnName = (String) headerCell.getValue();
if (columnName == null || columnName.trim().isEmpty()) {
break;
// it was used before stop after null column (in header) name requirement- 02/06/2025
//columnName = BulkUploadUtility.getColumnName(colIdx);
}
if(!columnNames.containsKey(columnName)){
columnNames.put(columnName, colIdx);
}
}
/*
validate headers
*/
List<String> headers = uploadInput.getHeaders();
List<String> tempListColumns = new ArrayList<>(columnNames.keySet());
if ( ! headers.isEmpty() && ! tempListColumns.isEmpty() ) {
if( headers.size() <= tempListColumns.size() ){
for (int i = 0; i < headers.size(); i++) {
String header = headers.get(i);
String excelHeader = tempListColumns.get(i);
if( ! header.equalsIgnoreCase(excelHeader) ) {
String errorMessage = String.format("Header mismatch at index %s: expected '%s', found '%s' in sheet: %s", i, header, excelHeader, worksheet.getName());
logger.error(methodName, errorMessage);
throw new InvalidInputException(FILE_EXCEL_HEADERS_INVALID);
}
}
}else {
throw new InvalidInputException(FILE_EXCEL_HEADERS_INVALID);
}
}
int rowsSkipped= 0;
int emptyRowsToSkip = uploadInput.getEmptyRowsToSkip();
// Read the table data
for (int rowIdx = startRow; rowIdx <= endRow; rowIdx++) {
Row row = worksheet.getCells().getRows().get(rowIdx);
Map<String, Object> tableRow = new LinkedHashMap<>();
boolean isRowBlank = true;
for (String columnName: columnNames.keySet()) {
int colIdx = columnNames.get(columnName);
Cell cell = row.get(colIdx);
Object value = cell.getValue();
int type = cell.getType();
switch ( type ){
case CellValueType.IS_NULL -> {
tableRow.put(columnName, null);
}
case CellValueType.IS_NUMERIC -> {
isRowBlank = false;
BigDecimal bigDecimal= new BigDecimal(value.toString());
tableRow.put(columnName, bigDecimal.toPlainString());
}
default -> {
isRowBlank = false;
if (value instanceof String str) {
tableRow.put(columnName, str.trim());
} else {
tableRow.put(columnName, value);
}
}
}
if (value != null) {
isRowBlank = false;
}
}
if (isRowBlank && uploadInput.isSkipAfterEmptyRow()) {
logger.step(LoggerEnum.INFO, methodName, String.format("Row %s is blank", rowIdx));
rowsSkipped++;
if(rowsSkipped >= emptyRowsToSkip){
break;
}
} else {
if(!isRowBlank){
tableData.add(tableRow);
}
}
}
mtslExcelResultSet.setSheetData(tableData);
return mtslExcelResultSet;
}catch (ResourceNotFoundException | IllegalArgumentException exception){
logger.error(methodName, exception.getMessage());
return mtslExcelResultSet;
} finally {
if(worksheet!= null){
worksheet.dispose();
}
}
}
private void setTablePoints (Worksheet worksheet, BulkUploadInput uploadInput) {
String methodName = "setTablePoints";
int rowStart = uploadInput.getStartRow();
int rowEnd = uploadInput.getEndRow();
int colStart = uploadInput.getStartColumn();
int colEnd = uploadInput.getEndColumn();
Cells cells = worksheet.getCells();
int maxDataRow = cells.getMaxDataRow();
int maxColumn = cells.getMaxColumn();
if (maxColumn == - 1 || maxDataRow == - 1) {
logger.step(LoggerEnum.INFO, methodName, String.format("No data to read in the worksheet: %s", worksheet.getName()));
throw new ResourceNotFoundException(FILE_EXCEL_SHEET_EMPTY);
}
if (rowStart < 0) {
rowStart = 0;
rowEnd = maxDataRow;
}
if (rowStart >= rowEnd) {
String errorMessage = String.format("start row can not be greater than or equals to end row: %s , %s for worksheet: %s", rowStart, rowEnd, worksheet.getName());
logger.step(LoggerEnum.INFO, methodName, errorMessage);
throw new IllegalArgumentException(errorMessage);
}
if (colStart < 0) {
colStart = 0;
colEnd = maxColumn;
}
if (colStart >= colEnd) {
String errorMessage = String.format("start column can not be greater than or equals to end column: %s , %s for worksheet: %s", colStart, colEnd, worksheet.getName());
logger.step(LoggerEnum.INFO, methodName, errorMessage);
throw new IllegalArgumentException(errorMessage);
}
uploadInput.setStartColumn(colStart);
uploadInput.setEndColumn(colEnd);
uploadInput.setStartRow(rowStart);
uploadInput.setEndRow(rowEnd);
}
@Getter@Setter
public class BulkUploadInput {
private int startRow = - 1;
private int endRow = - 1;
private int startColumn = - 1;
private int endColumn = - 1;
private boolean skipAfterEmptyRow = true;
private int emptyRowsToSkip = 1;
private boolean skipAfterEmptyColumn = false;
private List<String> headers= new ArrayList<>();
}
This is my current code:
public Map<String, Object> readExcelLightCells(InputStream inputStream) throws Exception {
long startTime = System.nanoTime();
CustomLightCellsDataHandler handler = new CustomLightCellsDataHandler();
LoadOptions options = new LoadOptions();
options.setLightCellsDataHandler(handler);
options.setMemorySetting(MemorySetting.MEMORY_PREFERENCE);
Workbook workbook = new Workbook(inputStream, options);
long endTime = System.nanoTime();
double durationSeconds = (endTime - startTime) / 1_000_000_000.0;
System.out.println(String.format("Time taken: %.3f seconds", durationSeconds));
System.out.println("workbook licensed: " + workbook.isLicensed() + " || excel read successfully with " + handler.getTotalRows() + " rows");
Map<String, Object> response = new HashMap<>();
response.put("data", handler.getData());
response.put("totalRows", handler.getTotalRows());
return response;
}
public class CustomLightCellsDataHandler implements LightCellsDataHandler {
private List<List<Object>> data = new ArrayList<>();
private List<Object> currentRow;
@Override
public boolean startSheet(Worksheet sheet) {
System.out.println("Processing sheet: " + sheet.getName());
return true;
}
@Override
public boolean startRow(int rowIndex) {
currentRow = new ArrayList<>();
return true;
}
@Override
public boolean processCell(Cell cell) {
currentRow.add(cell.getValue());
return true; // continue processing
}
@Override
public boolean processRow(Row row) {
data.add(currentRow);
return true;
}
public boolean startCell(int column) {
return true;
}
public List<List<Object>> getData() {
return data;
}
public int getTotalRows() {
return data.size();
}
}