Custom logic to stop workbook creation

Aspose.Cells Product Family
1

I use Aspose cells java for parsing spreadsheets. I would like to abort the workbook creation when there are too many long strings in the workbook. (threshold for length and count will be set in the application)
For example, if there 10k character length strings in 30 cells I want to throw an exception.

How to add this custom logic?

2

@bkmmxxiii
There is no good idea.
You can try parse the file with light cells : Using LightCells API|Documentation
then you can check the value of the cell to find when to abort.

3

@bkmmxxiii,

Although it is not performance oriented and efficient way to validate the workbook in normal mode (if the workbook has huge data) and you should use LightCells API (light weight mode) as suggested. But if you still persist to validate in normal way, here is the sample code or Java program to accomplish your task as per your description.
e.g.,
Sample code:

import com.aspose.cells.*;

public class WorkbookValidator {
    private static final int STRING_LENGTH_THRESHOLD = 10000; // e.g., 10,000 characters
    private static final int MAX_COUNT_THRESHOLD = 30; // e.g., max 30 such cells
    private int longStringCount = 0;

    public void validateAndLoadWorkbook(String filePath) throws Exception {
        // Define LoadOptions to optimize loading
        LoadOptions loadOptions = new LoadOptions();
        loadOptions.setLoadFilter(new LoadFilter(LoadDataFilterOptions.CELL_DATA)); // Load only cell values
        
        // Load Workbook
        Workbook workbook = new Workbook(filePath, loadOptions);
        
        // Scan workbook for long strings
        for (int i = 0; i < workbook.getWorksheets().getCount(); i++) {
            Worksheet sheet = workbook.getWorksheets().get(i);
            scanWorksheet(sheet);
        }
        
        // Proceed if validation passed
        System.out.println("Workbook loaded successfully without exceeding limits.");
    }

    private void scanWorksheet(Worksheet sheet) throws Exception {
    	Cells cells = sheet.getCells();
        
        // Get the maximum row and column indexes where data exists
        int maxRow = cells.getMaxDataRow();  // Last row index with data
        int maxCol = cells.getMaxDataColumn();  // Last column index with data

        for (int row = 0; row <= maxRow; row++) {
            for (int col = 0; col <= maxCol; col++) {
                Cell cell = cells.get(row, col);  // Access cell using row and column index

                if (cell != null && cell.getType() == CellValueType.IS_STRING) {
                    String cellValue = cell.getStringValue();                         
                    if (cellValue.length() >= STRING_LENGTH_THRESHOLD) {
                        longStringCount++;

                        if (longStringCount >= MAX_COUNT_THRESHOLD) {
                            throw new Exception("Too many long strings detected! Aborting workbook processing.");
                        }
                    }
                }
            }
        }
    }

    public static void main(String[] args) {
        try {
            new WorkbookValidator().validateAndLoadWorkbook("d:\\files\\Bk_validator1.xlsx");
            System.out.println("ok");
        } catch (Exception e) {
            System.err.println("Validation Failed: " + e.getMessage());
        }
    }
}

Hope, this helps a bit.

4

Thank you for your reply. I will make use of these suggestions.

5

@bkmmxxiii,

You are welcome. If you have any additional questions or feedback, don’t hesitate to reach out to us at any time.