@braddixonorigin
Would you like to provide your sample file and executable Console project? We will check it soon.
Thanks John, literally, all I do is open up the file, run the calculations and save it, nothing special. The workbook has all of the formulas already.
I can see it doesn’t work because when I open and recalculate the workbook in Excel, the data changes and goes back to what I expect it to be.
using (var workbook = new Workbook(inputStream))
{
var worksheet = workbook.Worksheets["WFSOutput"];
for (int i = 1; i <= 100; i++)
{
_logger.LogWarning($"{column}{i} Before = {worksheet.Cells[$"{column}{i}"].Value}");
}
workbook.RefreshDynamicArrayFormulas(true, new CalculationOptions());
var executionStartTimeTicks = DateTime.UtcNow.Ticks;
workbook.CalculateFormula();
_logger.LogWarning($"Total Execution Runtime (2) = {new TimeSpan(DateTime.UtcNow.Ticks - executionStartTimeTicks).TotalSeconds} seconds.");
for (int i = 1; i <= 100; i++)
{
_logger.LogWarning($"{column}{i} After = {worksheet.Cells[$"{column}{i}"].Value}");
}
workbook.Save(saveFilePath);
I also strongly suggest that you change out the COUNTA() formulas for ROWS() as I specified as that will show the other issue I highlighted.
@braddixonorigin
By using the following sample code for testing on the latest version v24.5, we found that the output results are consistent with the Excel calculation results. Please refer to the attachment. result.zip (1.4 MB)
The sample code as follows:
using (var workbook = new Workbook(filePath + "WFS Output Formula (Split Out).xlsx"))
{
int column = 0;
var worksheet = workbook.Worksheets["WFSOutput"];
for (int i = 1; i <= 100; i++)
{
Console.WriteLine(CellsHelper.CellIndexToName(i, column) + $" Before = {worksheet.Cells[i, column].Value}");
}
workbook.RefreshDynamicArrayFormulas(true, new CalculationOptions());
var executionStartTimeTicks = DateTime.UtcNow.Ticks;
workbook.CalculateFormula();
Console.WriteLine($"Total Execution Runtime (2) = {new TimeSpan(DateTime.UtcNow.Ticks - executionStartTimeTicks).TotalSeconds} seconds.");
for (int i = 1; i <= 100; i++)
{
Console.WriteLine(CellsHelper.CellIndexToName(i, column) + $" After = {worksheet.Cells[i, column].Value}");
}
workbook.Save(filePath + "out_net.xlsx");
If you still have any questions, please provide more detailed information. If you could take a screenshot and highlight the errors, it would be very helpful for us to locate the issue. We will check it soon.
Thanks for checking up on it John, I, unfortunately, do not get the same result.
I too am on 24.5 so we can rule that out. Check out the screenshots I have attached, one shows the result from Aspose, the other the result from Excel when I recalculate the worksheet after opening the workbook that Aspose calculated and saved.
Calculation Results.zip (267.1 KB)
Calculation Log.png (221.4 KB)
I’m not sure if it matters but this is running on .NET 8.0 through an Azure Function.
@braddixonorigin
Hi, I was able to reproduce your issue with version 24.5 (.NET 8). However, I found that the new version, 24.6, resolves the problem. Therefore, please use version 24.6.
24.6(.net8).7z (676.1 KB)
Ok, thanks, I’ll check it out.
Ok, so that has fixed the issue overall but it hasn’t fixed the use of ROWS vs COUNTA. COUNTA works, ROWS does not, so if you try this …
=LET(
COMMENT1, " --- The following formulas are used for initialisation purposes and set the scene for the remaining calculations ---",
_dataMatrix, LSLCalculation[[#All],[VarianceWithAccrualBankWeeks]:[VarianceWithOSWBankHours]],
_dataMatrixWoHeaders, DROP(_dataMatrix, 1),
COMMENT2, " --- We now need to select the datasets so we can unpivot the data by date, by variance group (i.e. pay code) ---",
_allDates, B1 + SEQUENCE(B2, 1, 0, 1),
_varianceGroups, TRANSPOSE(CHOOSEROWS(_dataMatrix, 1)),
_numberOfExplodedRows, ROWS(_allDates) * ROWS(_varianceGroups),
COMMENT3, " --- This is where we start building out each column for the resulting data set ---",
COMMENT4, " --- Firstly, build up the unpivoted references by row and column for each resulting record ---",
_allRowsByWorkDate, MAKEARRAY(_numberOfExplodedRows, 1, LAMBDA(r,c, MOD(r - 1, ROWS(_allDates)) + 1)),
_allRowsByVarianceColumns, MAKEARRAY(_numberOfExplodedRows, 1, LAMBDA(r,c, ROUNDUP(r / ROWS(_allDates), 0))),
_allRowsByLookupMatrix, HSTACK(_allRowsByWorkDate, _allRowsByVarianceColumns),
_allRowsByLookupMatrix)
… over this …
=LET(
COMMENT1, " --- The following formulas are used for initialisation purposes and set the scene for the remaining calculations ---",
_dataMatrix, LSLCalculation[[#All],[VarianceWithAccrualBankWeeks]:[VarianceWithOSWBankHours]],
_dataMatrixWoHeaders, DROP(_dataMatrix, 1),
COMMENT2, " --- We now need to select the datasets so we can unpivot the data by date, by variance group (i.e. pay code) ---",
_allDates, B1 + SEQUENCE(B2, 1, 0, 1),
_varianceGroups, TRANSPOSE(CHOOSEROWS(_dataMatrix, 1)),
_numberOfExplodedRows, COUNTA(_allDates) * COUNTA(_varianceGroups),
COMMENT3, " --- This is where we start building out each column for the resulting data set ---",
COMMENT4, " --- Firstly, build up the unpivoted references by row and column for each resulting record ---",
_allRowsByWorkDate, MAKEARRAY(_numberOfExplodedRows, 1, LAMBDA(r,c, MOD(r - 1, COUNTA(_allDates)) + 1)),
_allRowsByVarianceColumns, MAKEARRAY(_numberOfExplodedRows, 1, LAMBDA(r,c, ROUNDUP(r / COUNTA(_allDates), 0))),
_allRowsByLookupMatrix, HSTACK(_allRowsByWorkDate, _allRowsByVarianceColumns),
_allRowsByLookupMatrix)
… if it’s anything like I’m seeing, you’ll see differences.
The problem appears to be here …
_numberOfExplodedRows, ROWS(_allDates) * ROWS(_varianceGroups)`
I’ve attached an image when I use ROWS. You can see, it’s vastly incorrect.
Using ROWS.png (23.8 KB)
Sorry to be throwing all of this at you but if I extend on the formula again (because what I’ve given you thus far is not the entire outcome) then I get another issue.
Not sure why but I’m getting #NAME? on this formula …
=LET(
COMMENT1, " --- The following formulas are used for initialisation purposes and set the scene for the remaining calculations ---",
_startDate, DATEVALUE(XLOOKUP("ExecutionDate",CurrentPersonAndWorkDetails[Key],CurrentPersonAndWorkDetails[Value],,0,-1)),
_daysOut, DATEDIF(_startDate, MAX(LSLCalculation[Date]), "d") + 1,
_employeeID, XLOOKUP("EmployeeID",CurrentPersonAndWorkDetails[Key],CurrentPersonAndWorkDetails[Value],,0,1),
_dataMatrix, LSLCalculation[[#All],[VarianceWithAccrualBankWeeks]:[VarianceWithOSWBankHours]],
_dataMatrixWoHeaders, DROP(_dataMatrix, 1),
COMMENT2, " --- We now need to select the datasets so we can unpivot the data by date, by variance group (i.e. pay code) ---",
_allDates, _startDate + SEQUENCE(_daysOut, 1, 0, 1),
_varianceGroups, TRANSPOSE(CHOOSEROWS(_dataMatrix, 1)),
_numberOfExplodedRows, COUNTA(_allDates) * COUNTA(_varianceGroups),
COMMENT3, " --- This is where we start building out each column for the resulting data set ---",
COMMENT4, " --- Firstly, build up the unpivoted references by row and column for each resulting record ---",
_allRowsByWorkDate, MAKEARRAY(_numberOfExplodedRows, 1, LAMBDA(r,c, INDEX(_allDates, MOD(r - 1, COUNTA(_allDates)) + 1))),
_allRowsByVarianceColumns, MAKEARRAY(_numberOfExplodedRows, 1, LAMBDA(r,c, CHOOSEROWS(_varianceGroups, ROUNDUP(r / COUNTA(_allDates), 0)))),
_allRowsByLookupMatrix, HSTACK(_allRowsByWorkDate, _allRowsByVarianceColumns),
_allRowsByLookupMatrixRC, MAKEARRAY(_numberOfExplodedRows, 2, LAMBDA(r,c, IF(c = 1, MATCH(INDEX(_allRowsByLookupMatrix, r, 1), LSLCalculation[Date], 0), MATCH(INDEX(_allRowsByLookupMatrix, r, 2), _varianceGroups, 0)))),
COMMENT5, " --- Build out each column and then HSTACK them all up to produce the file structure for OFlow ---",
_allRowsByBlankCell, IF(SEQUENCE(_numberOfExplodedRows, 1), ""),
_allRowsByEmployeeID, MAKEARRAY(_numberOfExplodedRows, 1, LAMBDA(r,c, _employeeID)),
_allRowsHoursLookup, MAKEARRAY(_numberOfExplodedRows, 1, LAMBDA(r,c, INDEX(_dataMatrixWoHeaders, INDEX(_allRowsByLookupMatrixRC, r, 1), INDEX(_allRowsByLookupMatrixRC, r, 2)))),
_allRowsByPayCodes, MAKEARRAY(_numberOfExplodedRows, 1, LAMBDA(r,c, XLOOKUP(CHOOSEROWS(_varianceGroups, ROUNDUP(r / COUNTA(_allDates), 0)), VarianceToAdjPayCodeLookup[VarianceColumnName], VarianceToAdjPayCodeLookup[AssociatedPayCode]))),
_unfilteredResult, HSTACK(_allRowsByEmployeeID, _allRowsByWorkDate, _allRowsByPayCodes, _allRowsByBlankCell, _allRowsByBlankCell, _allRowsHoursLookup),
COMMENT6, " --- Filter the result to remove any rows where the variance is 0 ---",
_result, FILTER(_unfilteredResult, ABS(INDEX(_unfilteredResult,, 6)) >= 0.000001),
_allRowsHoursLookup)
… that (at this stage) is the entire formula that will generate the outcome I am after.
This row …
_allRowsHoursLookup, MAKEARRAY(_numberOfExplodedRows, 1, LAMBDA(r,c, INDEX(_dataMatrixWoHeaders, INDEX(_allRowsByLookupMatrixRC, r, 1), INDEX(_allRowsByLookupMatrixRC, r, 2))))
… is the one giving me grief. Eventually, I need to get down to the point where _result is the final variable being returned from the LET() function.
@braddixonorigin
By using sample files and code for testing, we can reproduce the problem. When using ROWS to replace COUNTA, the calculation result is incorrect. Please refer to the attachment. result.zip (1.3 MB)
The sample code as follows:
using (var workbook = new Workbook(filePath + "WFS Output Formula (Split Out)-ROWS.xlsx"))
{
int column = 0;
var worksheet = workbook.Worksheets["WFSOutput"];
for (int i = 1; i <= 100; i++)
{
Console.WriteLine(CellsHelper.CellIndexToName(i, column) + $" Before = {worksheet.Cells[i, column].Value}");
}
workbook.RefreshDynamicArrayFormulas(true, new CalculationOptions());
var executionStartTimeTicks = DateTime.UtcNow.Ticks;
workbook.CalculateFormula();
Console.WriteLine($"Total Execution Runtime (2) = {new TimeSpan(DateTime.UtcNow.Ticks - executionStartTimeTicks).TotalSeconds} seconds.");
for (int i = 1; i <= 100; i++)
{
Console.WriteLine(CellsHelper.CellIndexToName(i, column) + $" After = {worksheet.Cells[i, column].Value}");
}
workbook.Save(filePath + "out_net.xlsx");
}
The output:
A2 Before = Days Out
A3 Before = Employee ID
A4 Before =
A5 Before =
A6 Before =
A7 Before =
A8 Before = ROW_IDX
A9 Before = 1
A10 Before = 2
A11 Before = 3
A12 Before = 4
A13 Before = 5
A14 Before = 6
A15 Before = 7
A16 Before = 8
A17 Before = 9
A18 Before = 10
A19 Before = 11
A20 Before = 12
A21 Before = 13
A22 Before = 14
A23 Before = 15
A24 Before = 16
A25 Before = 17
A26 Before = 18
A27 Before = 19
A28 Before = 20
A29 Before = 21
A30 Before = 22
A31 Before = 23
A32 Before = 24
A33 Before = 25
A34 Before = 26
A35 Before = 27
A36 Before = 28
A37 Before = 29
A38 Before = 30
A39 Before = 31
A40 Before = 32
A41 Before = 33
A42 Before = 34
A43 Before = 35
A44 Before = 36
A45 Before = 37
A46 Before = 38
A47 Before = 39
A48 Before = 40
A49 Before = 41
A50 Before = 42
A51 Before = 43
A52 Before = 44
A53 Before = 45
A54 Before = 46
A55 Before = 47
A56 Before = 48
A57 Before = 49
A58 Before = 50
A59 Before = 51
A60 Before = 52
A61 Before = 53
A62 Before = 54
A63 Before = 55
A64 Before = 56
A65 Before = 57
A66 Before = 58
A67 Before = 59
A68 Before = 60
A69 Before = 61
A70 Before = 62
A71 Before = 63
A72 Before = 64
A73 Before = 65
A74 Before = 66
A75 Before = 67
A76 Before = 68
A77 Before = 69
A78 Before = 70
A79 Before = 71
A80 Before = 72
A81 Before = 73
A82 Before = 74
A83 Before = 75
A84 Before = 76
A85 Before = 77
A86 Before = 78
A87 Before = 79
A88 Before = 80
A89 Before = 81
A90 Before = 82
A91 Before = 83
A92 Before = 84
A93 Before = 85
A94 Before = 86
A95 Before = 87
A96 Before = 88
A97 Before = 89
A98 Before = 90
A99 Before = 91
A100 Before = 92
A101 Before = 93
Total Execution Runtime (2) = 0.0468756 seconds.
A2 After = Days Out
A3 After = Employee ID
A4 After =
A5 After =
A6 After =
A7 After =
A8 After = ROW_IDX
A9 After = 1
A10 After = 2
A11 After = 3
A12 After = 4
A13 After = 5
A14 After = 6
A15 After =
A16 After =
A17 After =
A18 After =
A19 After =
A20 After =
A21 After =
A22 After =
A23 After =
A24 After =
A25 After =
A26 After =
A27 After =
A28 After =
A29 After =
A30 After =
A31 After =
A32 After =
A33 After =
A34 After =
A35 After =
A36 After =
A37 After =
A38 After =
A39 After =
A40 After =
A41 After =
A42 After =
A43 After =
A44 After =
A45 After =
A46 After =
A47 After =
A48 After =
A49 After =
A50 After =
A51 After =
A52 After =
A53 After =
A54 After =
A55 After =
A56 After =
A57 After =
A58 After =
A59 After =
A60 After =
A61 After =
A62 After =
A63 After =
A64 After =
A65 After =
A66 After =
A67 After =
A68 After =
A69 After =
A70 After =
A71 After =
A72 After =
A73 After =
A74 After =
A75 After =
A76 After =
A77 After =
A78 After =
A79 After =
A80 After =
A81 After =
A82 After =
A83 After =
A84 After =
A85 After =
A86 After =
A87 After =
A88 After =
A89 After =
A90 After =
A91 After =
A92 After =
A93 After =
A94 After =
A95 After =
A96 After =
A97 After =
A98 After =
A99 After =
A100 After =
A101 After =
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSNET-55998
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
Regarding obtaining #NAME? after calculation, We were unable to reproduce the issue. Would you like to share your test file? We will check it soon.
Yeah, sure … I should’ve done that already.
WFS_Aspose.zip (643.8 KB)
Also, it was #VALUE sorry, not #NAME
@braddixonorigin
By using sample files and code for testing, we can reproduce the issue. Found complex array function formula returns #VALUE when calling CalculateFormula method. Please refer to the attachment. result.png (9.8 KB)
The sample code as follows:
using (var workbook = new Workbook(filePath + "WFS_Aspose.xlsx"))
{
int column = 0;
var worksheet = workbook.Worksheets["WFSOutput"];
for (int i = 1; i <= 100; i++)
{
Console.WriteLine(CellsHelper.CellIndexToName(i, column) + $" Before = {worksheet.Cells[i, column].Value}");
}
workbook.RefreshDynamicArrayFormulas(true, new CalculationOptions());
var executionStartTimeTicks = DateTime.UtcNow.Ticks;
workbook.CalculateFormula();
Console.WriteLine($"Total Execution Runtime (2) = {new TimeSpan(DateTime.UtcNow.Ticks - executionStartTimeTicks).TotalSeconds} seconds.");
for (int i = 1; i <= 100; i++)
{
Console.WriteLine(CellsHelper.CellIndexToName(i, column) + $" After = {worksheet.Cells[i, column].Value}");
}
workbook.Save(filePath + "out_net.xlsx");
}
The output:
A2 Before = DUMMY
A3 Before = DUMMY
A4 Before = DUMMY
A5 Before = DUMMY
A6 Before = DUMMY
A7 Before = DUMMY
A8 Before = DUMMY
A9 Before = DUMMY
A10 Before = DUMMY
A11 Before = DUMMY
A12 Before = DUMMY
A13 Before = DUMMY
A14 Before = DUMMY
A15 Before = DUMMY
A16 Before = DUMMY
A17 Before = DUMMY
A18 Before = DUMMY
A19 Before = DUMMY
A20 Before = DUMMY
A21 Before = DUMMY
A22 Before = DUMMY
A23 Before = DUMMY
A24 Before = DUMMY
A25 Before = DUMMY
A26 Before = DUMMY
A27 Before = DUMMY
A28 Before = DUMMY
A29 Before = DUMMY
A30 Before = DUMMY
A31 Before = DUMMY
A32 Before = DUMMY
A33 Before = DUMMY
A34 Before = DUMMY
A35 Before = DUMMY
A36 Before = DUMMY
A37 Before = DUMMY
A38 Before = DUMMY
A39 Before = DUMMY
A40 Before = DUMMY
A41 Before = DUMMY
A42 Before = DUMMY
A43 Before = DUMMY
A44 Before = DUMMY
A45 Before = DUMMY
A46 Before = DUMMY
A47 Before = DUMMY
A48 Before = DUMMY
A49 Before = DUMMY
A50 Before = DUMMY
A51 Before = DUMMY
A52 Before = DUMMY
A53 Before = DUMMY
A54 Before = DUMMY
A55 Before = DUMMY
A56 Before = DUMMY
A57 Before = DUMMY
A58 Before = DUMMY
A59 Before = DUMMY
A60 Before = DUMMY
A61 Before = DUMMY
A62 Before = DUMMY
A63 Before = DUMMY
A64 Before = DUMMY
A65 Before = DUMMY
A66 Before = DUMMY
A67 Before = DUMMY
A68 Before = DUMMY
A69 Before = DUMMY
A70 Before = DUMMY
A71 Before = DUMMY
A72 Before = DUMMY
A73 Before = DUMMY
A74 Before = DUMMY
A75 Before = DUMMY
A76 Before = DUMMY
A77 Before = DUMMY
A78 Before = DUMMY
A79 Before = DUMMY
A80 Before = DUMMY
A81 Before = DUMMY
A82 Before = DUMMY
A83 Before = DUMMY
A84 Before = DUMMY
A85 Before = DUMMY
A86 Before = DUMMY
A87 Before = DUMMY
A88 Before = DUMMY
A89 Before = DUMMY
A90 Before = DUMMY
A91 Before = DUMMY
A92 Before = DUMMY
A93 Before = DUMMY
A94 Before = DUMMY
A95 Before = DUMMY
A96 Before = DUMMY
A97 Before = DUMMY
A98 Before = DUMMY
A99 Before = DUMMY
A100 Before = DUMMY
A101 Before = DUMMY
Total Execution Runtime (2) = 0.1877724 seconds.
A2 After = #VALUE!
A3 After =
A4 After =
A5 After =
A6 After =
A7 After =
A8 After =
A9 After =
A10 After =
A11 After =
A12 After =
A13 After =
A14 After =
A15 After =
A16 After =
A17 After =
A18 After =
A19 After =
A20 After =
A21 After =
A22 After =
A23 After =
A24 After =
A25 After =
A26 After =
A27 After =
A28 After =
A29 After =
A30 After =
A31 After =
A32 After =
A33 After =
A34 After =
A35 After =
A36 After =
A37 After =
A38 After =
A39 After =
A40 After =
A41 After =
A42 After =
A43 After =
A44 After =
A45 After =
A46 After =
A47 After =
A48 After =
A49 After =
A50 After =
A51 After =
A52 After =
A53 After =
A54 After =
A55 After =
A56 After =
A57 After =
A58 After =
A59 After =
A60 After =
A61 After =
A62 After =
A63 After =
A64 After =
A65 After =
A66 After =
A67 After =
A68 After =
A69 After =
A70 After =
A71 After =
A72 After =
A73 After =
A74 After =
A75 After =
A76 After =
A77 After =
A78 After =
A79 After =
A80 After =
A81 After =
A82 After =
A83 After =
A84 After =
A85 After =
A86 After =
A87 After =
A88 After =
A89 After =
A90 After =
A91 After =
A92 After =
A93 After =
A94 After =
A95 After =
A96 After =
A97 After =
A98 After =
A99 After =
A100 After =
A101 After =
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSNET-55999
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
Thanks John, that’s a good outcome.
Based on the policies, I know there are no promises but any thoughts on when you think a fix will be supplied?
@braddixonorigin
Since your issue was logged today (just a few hours ago), please allow us some time to evaluate and analyze it in detail. If it is not complex, we should be able to fix it soon, and the fix may be included in our upcoming release (Aspose.Cells v24.7 - planned for publication in the second week of July next month). If the issue is complex, it may take a few more weeks or even a month to resolve.
Additionally, if you purchase paid support, we will prioritize your issue so that it can be resolved earlier.
Thanks John, I completely understand.
Question though, if you guys manage to work out the issue and get a fix prior to the official release of 24.7, is there any way to get a beta copy for testing purposes?
I get that paying for support would prioritise but regardless, we’re on a fairly tight project timeline and I need to cover all bases here. Thanks!
We cannot provide interim fixes/releases in normal (free) support here. You can obtain Paid Support Services if you need to support your issue on a priority basis and to get quick fix.
@braddixonorigin
The issue of calculating this complex array function is a bit complicated and we need some time to solve it. With free support we are afraid we cannot provide hot fix. However, if it is possible, you may provide us your cases(runnable project with required resources such as template files), once we fix the issue, we will test it and provide you the result for verification. Hope this way can help you to manage your timeline.
Yeah, ok, understood. Also, I’ve provided a workbook and the basic code so it’s really that simple.
There are other worksheets and 1000’s of other formulas that need to be resolved but I can’t provide that easily for a number of reasons. I think you guys have enough to work with for now so I’ll just wait to hear back when you have something to share.
@braddixonorigin
Thank you for your feedback. Once there are updates, we will notify you promptly.
1 Like
We are pleased to inform you that your issue (“CELLSNET-55999”) has been resolved. The fix/enhancement will be included in our upcoming release (Aspose.Cells v24.7) which is due in the second week of July this month. You will be notified when the next version is released.
We have resolved your reported issue (previously logged as “CELLSNET-55998”) as well. The fix/enhancement will be incorporated in the upcoming release (Aspose.Cells v24.7) scheduled for this week. You will receive a notification upon the release of the next version.