While performing a number of subtotals (two groups using two different ConsolidationFunctions for a total of 4 subtotals), I discovered that the formula used in the subtotals was using the wrong row range. I’ve attached a sample of what happens on just the first grouping after two ConsolidationFunctions (because the application crashed before it could generate the third subtotal).
The first ConsolidationFunction being used is Max, which correctly gives the row range of the rows in question. Then, when doing the Min consolidation on the same grouping, it is including the Max subtotal row in each Min row formula. Upon further testing, Excel isn’t including the results of previous subtotal formulae, which is good, but the issue I’m having is that, by the time the second subtotal is done, I have a circular reference (the subtotal formula is including its own cell in the Grand total), then the next attempt to subtotal generates an ArgumentException with the message “Invalid end row index.” I think the problem is that the formula is trying to reference cells outside the given range.
Attached is a workbook with a sheet of the results of two consolidation functions (the “actual” sheet), highlighting the problem functions; a sheet with the corrected formulae; a sheet showing the results of subtotaling done by Excel; and a sheet showing the end goal of all four subtotals, as done by Excel.
Hi,
Thanks for using Aspose.Cells.
Can you please provide us your sample source code to replicate this issue. This will help us to further investigate and reproduce the issue as you are doing. We appreciate your cooperation in this regard.
Attached is a console app that demonstrates the problem. It will save an Excel workbook after each step and should throw an Exception on the third subtotal.
The previous project used the 7.2.1.4 API. This project shows the 7.2.1.6 API, which is causing a circular reference.
Hi,
I ran your code (Attachment: multiple consolidation functions subtotaling.zip) and found that exception is occurring.
Could you please provide me a screenshot explaining your problem thoroughly?
We have logged this issue in our database. We will look into your issue and once the issue is resolved or we have some other update for you, we will let you know asap.
This issue has been logged as CELLSNET-40721.
Below is the exception I got.
Exception:
System.ArgumentException was unhandled
Message=Invalid end row index.
Source=Aspose.Cells
StackTrace:
at ..(Int32 , Int32 )
at Aspose.Cells.Cells.GroupRows(Int32 firstIndex, Int32 lastIndex, Boolean isHidden)
at .a.()
at .a.Subtotal(CellArea , Int32 , ConsolidationFunction , Int32[] , Boolean , Boolean , Boolean )
at Aspose.Cells.Cells.Subtotal(CellArea ca, Int32 groupBy, ConsolidationFunction function, Int32[] totalList, Boolean replace, Boolean pageBreaks, Boolean summaryBelowData)
at ConsoleApplication1.Program.Main(String[] args) in F:\Shak-Data-RW\Downloads\ConsoleApplication1\ConsoleApplication1\Program.cs:line 36
As taken from the attachment in the original post, there is a circular reference going on in the second subtotal that is causing problems. The third subtotal runs into the exception because it is then trying to reference cells outside the given CellArea.
This issue is seen to a lesser degree in other subtotals but is worst in the grand total of the second subtotal column. This issue did not appear to be present in an earlier version of the API.
One other issue I found was that the ordering of the subtotal rows is inconsistent with Excel's results. (See attachment on first post, sheet "actual" and sheet "expected (via Excel)"). This is a minor issue, but a bit bigger issue is that the Grand Min row at the bottom appears BEFORE the group Min of the last group, which is what I believe is causing the circular reference. My guess is that the Grand Min row is being inserted at the end of the data, but it is only checking for 1 subtotal row at the end of the data, not looking for the first row after the data that is not a subtotal row (hence why the Grand Min appears after NC Max but not after NC Min in my screenshot).
Going on that assumption, if you were to simply search for the first row after the data that is not a subtotal row, then insert the grand subtotal row, it would fix the improper ordering issue. The final order of the rows would then be:
- NC Max
- NC Min
- Grand Max
- Grand Min
That would immediately solve the circular reference problem (assuming it's the same formula just a row or two lower), but it doesn't solve the issue with the subtotal having the wrong rows for the group (the second subtotal row formulae include the previous subtotal rows).
Hi,
Thanks for your detailed feedback and screenshots.
We have fixed your issue and will soon release a fix after some testing in coming few days.
Hi,
Please download and try the latest fix: Aspose.Cells for .NET v7.2.1.7
The issues you have found earlier (filed as CELLSNET-40721) have been fixed in this update.
This message was posted using Notification2Forum from Downloads module by aspose.notifier.