Aspose.Cells - Index was out of range on FindFormula

Aspose.Cells Product Family
1

When calling Cells.FindFormula(“Destination”, null)

Exception message: Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index

Stacktrace:
at System.ThrowHelper.ThrowArgumentOutOfRangeException(ExceptionArgument argument, ExceptionResource resource)
at Aspose.Cells.NameCollection.get_Item(Int32 index)
at .(StringBuilder )
at .()
at .( )
at Aspose.Cells.Cell. ()
at . ( , Int32 )
at Aspose.Cells.Cells.( , , , Boolean )
at Aspose.Cells.Cells.(Int32 , Int32 , , Boolean , Boolean )
at Aspose.Cells.Cells.(Cell , , Boolean )
at Aspose.Cells.Cells.FindFormula(String formula, Cell previousCell)
at AsposeTest6.Program.Main(String[] args) in C:\Users\bjuliao\source\repos\AsposeTest\AsposeTest6\Program.cs:line 14

AsposeTest6.zip (4.1 MB)

2

@brunojuliao,

Thank you for your query.

I checked your sample XLS file by opening it in MS Excel 2016 but could not find any formula named Destination. Please provide some image and more detail about the formula in the sample XLS file.

3

@brunojuliao,

I guess you need to find the “Destination” string in the worksheet cells. If so, kindly do use Cells.Find method instead, see the following sample code (you may specify/update relevant FindOptions accordingly) for your needs:
e.g
Sample code:

 var filename = "E:\\test2\\AsposeTest6\\[20180621135043721][RUS_ROST-MYS_TM][PL][USD][ROSTELECOM-TMBG-UKS]OriginalPL.xls";
                var temp = new Workbook(filename, new LoadOptions() { LoadFilter = new LoadFilter(LoadDataFilterOptions.CellData), CheckExcelRestriction = true });
                var options = new FindOptions();
                options.LookInType = LookInType.OriginalValues;
                options.LookAtType = LookAtType.EntireContent;
                options.CaseSensitive = true;
                var cell = temp.Worksheets[0].Cells.Find("Destination",null, options);
                Console.WriteLine(cell.Name);

Hope, this helps a bit.

4

Thanks for both comments. Indeed I don’t have a formula “Destination”. Although, in my main application, I have a method that is meant to look up for a given value. It can be a cell value or a formula, so I built it in a way that first it tries Formula, then cell content. It was working fine on previous version (18.6). It returned null when found no matching formula. Has this behavior changed?

A snippet of the code that I use in main application:
// Search within formulas before
var cell = _worksheet.Cells.FindFormula(value, null);
if (cell == null)
cell = _worksheet.Cells.FindFormulaContains(value, null);
// If no success, then search within values now
if (cell == null)
cell = _worksheet.Cells.Find(value, null, new FindOptions() { LookAtType = partialMatch ? LookAtType.Contains : LookAtType.EntireContent });

        if (cell == null)
            return null;
5

@brunojuliao,

We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-46224 - Exception raised while calling Cells.FindFormula for non-existing formula

1 Like
6

@brunojuliao

Please use the following code and let us know if you still face this problem on your end.

var temp = new Workbook(filename, new Aspose.Cells.LoadOptions() { LoadFilter = new LoadFilter(LoadDataFilterOptions.CellData | LoadDataFilterOptions.DefinedNames), CheckExcelRestriction = true });
7

As you suggested, it worked this way. Thanks.

8

@brunojuliao,

Thank you for the feedback.