I am using ASPOSE Cells to fill in an excel document that I have formatted and added formulas to. All of the formulas are working accepts cells that contain Filters. The cells containing filters are being wrapped in “{}”
Example: When I click on the cell it looks like this
{=INDEX(FILTER(EarningsForCalculation,EarningsForCalculation<>-1),SEQUENCE(IF(C158>120,120,C158),1,1,1))}
The second I hit enter on the cell in the actual document the brackets go away and the filter is applied. Is there any way for this behavior to be applied on opening the document. Or to have the filter apply before I save the document in C# so that when the document opens it is functioning correctly
Please Help
@joeknoedler can you please attach, your current excel file and the expected behavior.
This is not clear to me, are you inserting the formulas using Aspose.Cells API? If this is the case why you are setting the formulas with brackets, you just can format the string to remove the open and close bracket:
string resultFormula = originalFormula.Substring(1, originalFormula.Length - 2);
So the excel document I am filling has two sheets. The first sheet is data input and the second sheet I am using as a helper sheet to manipulate the data from the first sheet to calculate the needed result.
I am using aspose to fill in monthly earnings we have on file on the first sheet and then save the document and use a basic Process.Start(fileName); to open the document.
All of the formulas and data manipulation on the second sheet never need to be edited and are driven by data input to the first sheet by ASPOSE and any data that is then changed by the user.
@joeknoedler can you please attach the document and the expected output please?
It wont allow mt to upload excel documents so I will try this
This is the first sheet data layout, I use aspose to take calculated data from our system and fill these months of earnings out. The user can then enter earnings from reciprocal systems outside of our data control to calculate a final average.
image.png (41.9 KB)
Because of our business rules the earnings can have breaks in service that do not contribute to the persons average. Also because of business rules the pensionable earnings can be capped thus allowing the person to have earnings in the month but non that would contribute to the average, but that month does need to count towards a consecutive month.
The issue is on the helper sheet this column that has the above filter on it to achieve consecutive months loads like this with the above mentioned {}
image.png (37.1 KB)
Sorry didn’t realize I could upload zips
FACHelper.zip (41.6 KB)
1 Like
Should be available now, the cells in question are G2 and C189 on the second sheet
Side Note: I may have way overthought this and or be doing it the hard way, I am certainly not an excel expert.
Because this sheet is shared among systems it needs to stay free of macros and the formulas need to stay relatively simple
Just for more info:
The sheet is calculating the members best 96 consecutive months of pensionable earnings out of the last 120.
The members earnings can be capped in the system after they reach that years Tier 2 max. After the member hits that earnings max their pensionable earnings for that month are $0 but that month counts towards the 96.
However in the case where the member had no base earnings, that is considered a break in service and that does not count as a month in their 96 consecutive months.
I am using calculated columns and filters to essentially create a list of consecutive earnings and then take the top 120. I am then attempting to use helper columns to link those earnings back to the original months of service.
@joeknoedler sorry when I open the Excel file that you attached the formula don’t contain curly brackets.
Its only after aspose inserts data into the first sheet
@joeknoedler Can you please share a sample of code of the insertion of data in order of replicate the issue and provide a fix for it.
@joeknoedler,
For your original issue:
{=INDEX(FILTER(EarningsForCalculation,EarningsForCalculation<>-1),SEQUENCE(IF(C158>120,120,C158),1,1,1))}
Did you set this formula by cells api such as SetArrayFormula(string, int, int)? If so, please set such kind of formulas as dynamic array formula by methods such as SetDynamicArrayFormula(string, FormulaParseOptions, bool) instead.
From the template file FACHelper.xlsx we found those formulas have been set as dynamic array formulas, so we don’t think the “{}” will be appended when viewing the formula in ms excel. Is it the expected result or the original template file which needs to be processed by Aspose.Cells? If it is the expected result, we think it can be achieved by setting dynamic array formula. If it is the original template file and after being processed by Aspose.Cells you got the formula with “{}”, please provide us your sample code to reproduce the issue and we will look into it soon.
1 Like
Thank you, I ended up finding this on another forum. I also had to upgrade my Aspose.Cells dll to the newer version.