0

I have an Excel.xls. There are many cells with complex formulas. When I got the results of several formulas, an exception occurred.

        var a = wk.GetCreationHelper().CreateFormulaEvaluator();
    a.ClearAllCachedResultValues();
    var cellv = a.EvaluateInCell(cell);

The evaluation function EvaluateInCell returned IndexOutOfRangeException: Index was outside the bounds of the array.

It is worth noting that the formula in this cell is very long and has multiple levels of nesting. Its formula is this:=IF(SUM(D22:D10000)+SUM(M28:M193)+SUM(M22:M23)+SUM(D22:D10000)+SUM(E22:E10000)*1.2=0,"",SUM(D22:D10000)+SUM(M28:M193)+SUM(M22:M23)+SUM(E22:E10000)*1.2)

First of all, I don't think it is a problem with the formula, because Excel can calculate the result by itself, but it returns an error through Npoi.

I looked through the source code and found that the error may be caused by a negative number in getHashCode. This is my guess, but I still don't know how to solve this problem. The source code is as follows

  int startIx = cce.GetHashCode() % arr.Length;

        for (int i = startIx; i < arr.Length; i++)
        {
            CellCacheEntry item = arr[i];
            if (item == cce)
            {
                // already present
                return false;
            }
            if (item == null)
            {
                arr[i] = cce;
                return true;
            }
        }
        for (int i = 0; i < startIx; i++)
        {
            CellCacheEntry item = arr[i];
            if (item == cce)
            {
                // already present
                return false;
            }
            if (item == null)
            {
                arr[i] = cce;
                return true;
            }
        }
xujie liu
  • 1
  • 1
  • 2
    The POI formula evaluations can be useful but they are not complete and it is not guaranteed that even the functions that are implemented will support every use case. When you read Excel files, the calculated values are cached in the Excel cell data - so you don't need to recalculate the values. When you write Excel files, you can use setForceFormulaRecalculation(true) on the sheet instance and Excel will recalculate the values when it loads up the file. If you can provide a reproducible test case, can you log an issue at https://bz.apache.org/bugzilla/ ? – PJ Fanning Nov 03 '21 at 08:13
  • 1
    For me exactly that formula gets properly evaluated using `apache poi 5.0.0`. What version are you using? And what programming language is used in you sample code? Btw.: `NPOI` != `apache poi`. – Axel Richter Nov 03 '21 at 13:15
  • @AxelRichter The language I use is c# and the API version is NPOI 2.5.5 – xujie liu Nov 04 '21 at 01:26
  • @PJFanning I'm trying to make a demo that can reproduce the bug, but I don't have time now, so I can only skip formulas that cannot be calculated at the moment. I will upload the demo later when I have time – xujie liu Nov 04 '21 at 01:30
  • 1
    As Alex pointed out, NPOI is not POI - so please don't post anything to https://bz.apache.org/bugzilla (POI's bug management system). – PJ Fanning Nov 04 '21 at 07:06
  • It may be that the cell formula is too complicated, and NPOI cannot handle formulas of this magnitude. – xujie liu Nov 04 '21 at 09:51

0 Answers0