Range of AutoFilter is not valid

Aspose.Cells Product Family
1

When I try to upload an .xls/.xlsx file, it throws the exception: Range of AutoFilter is not valid. When I open the exact same file, save as WITHOUT changing anything, and upload the newly saved file, it can read the file no problem.

Here is my .NET code to read the file as a stream when uploading (file is type IFormFile):

var stream = new MemoryStream();
file.CopyTo(stream);

LoadOptions loadOptions = new LoadOptions(GetLoadFormat(file.FileName));
stream.Position = 0;
Workbook workbook = new Workbook(stream, loadOptions);

public LoadFormat GetLoadFormat(string fileName)
{
var fileExt = fileName.Split(’.’).Last();

        LoadFormat loadFormat;
        switch(fileExt)
        {
            case "xls":
                loadFormat = LoadFormat.Excel97To2003;
                break;
            case "xlsx":
                loadFormat = LoadFormat.Xlsx;
                break;
            case "csv":
                loadFormat = LoadFormat.CSV;
                break;
            default:
                loadFormat = LoadFormat.Auto;
                break;
        }

        return loadFormat;
    }

It appears that opening the file and saving it adds some metadata that is read by Aspose, allowing it to process the file.

Any help would be appreciated!
Thanks!

2

@svnguyen,

Thanks for your query.

Please share the sample XLS/XLSX file for our analysis here. We will try to reproduce the issue and provide our feedback here.

3

@ahsaniqbalsidiqui
Thanks for the quick reply. I’m in the process of trying to get a spreadsheet for you that doesn’t contain sensitive info. Are there any properties within the LoadOptions/LoadFormat that can alter the AutoFilter?

4

@svnguyen,

AutoFilter is worksheet feature which cannot be used while loading the workbook. You may please try following code to apply other type of filters while loading the workbook to avoid loading unnecessary data. Try different enumerators in LoadDataFilterOptions for testing the behavior.

// Opening File with Data only
// Load only specific sheets with data and formulas
// Other objects, items etc. would be discarded

// Instantiate LoadOptions specified by the LoadFormat
LoadOptions loadOptions7 = new LoadOptions(LoadFormat.Xlsx);

// Load only cell data, formulas & formatting
loadOptions7.LoadFilter = new LoadFilter(LoadDataFilterOptions.CellData);

// Create a Workbook object and opening the file from its path
Workbook wb = new Workbook(dataDir + "Book1.xlsx", loadOptions7);

If your issue is not resolved, please share your sample file with us for testing.

5

@ahsaniqbalsidiqui
For the purpose of my app, adding LoadDataFilterOptions.CellData before creating the workbook has resolved my issue:

loadOptions.LoadFilter = new LoadFilter(LoadDataFilterOptions.CellData);
Workbook workbook = new Workbook(stream, loadOptions);

Thank you for your help!

6

@svnguyen,

Thank you for the feedback.