Hi. I think that there is a problem when copying the formulas (PasteType) of a range where the copied formula is a dynamic range type (eg SEQUENCE) i.e. where would need to use Cell.SetDynamicArrayFormula if setting formula for a single cell.
Could you please zip and attach your template Excel files (input Excel file (if any) and output file) and sample (runnable) code to reproduce the issue on our end, we will check it soon.
Unfortunately the code is within a large wrapper class and performing the function on a proprietary workbook which I cannot share.
I am afraid, without a sample Excel file(s) and sample code we might not trace or figure out your issue. Please do replace your confidential data with dummy data for the template and do create a demo (version) of your large application and provide us (if you have reservations on sharing your sample code and data). We will check your issue soon.
@Martin_Sher
We tested the copy function with simple dynamic array formula:
A1’s dynamic array formula is “=SEQUENCE(5)”, with below code:
cells.CreateRange("B1:B3").Copy(cells.CreateRange("A1:A3"));
cells.CreateRange("C1:C3").Copy(cells.CreateRange("A2:A4"));
In the resultant file we found the dynamic array formula can be copied to B1:B3 and when you open the file in ms excel the formula will be spilled to B1:B5 automatically. It is same with the behavior of ms excel.
For C1:C3, we copied only the calculated values of A2:A4 without the formula. But when we do the operation in ms excel, we found it copies neither formula nor formula’s calculated values. Is it the issue you found? If not, we are afraid we need you sample data and code to reproduce the issue so we can look into it.
Hi
Using Range.Copy(range, pasteOptions) Range.Copy | Aspose.Cells for .NET API Reference
with pasteOptions = new PasteOptions().PasteType = PasteType.Formulas
As want to copy the formulae only
example more like cells.CreateRange(“B1”).Copy(cells.CreateRange(“A1”));
Given that A1 = “=SEQUENCE(5)” and hence have values in A2, A3 to A5 was expecting to have values in B2, B3…B5 but these are null. B1 does have a value of 1 and the correct formula in it. But values for B2…B5 not calculate even after .CalculateFormulae().
Hopefully this makes sense
Thanks for providing more detaills.
We will try to evaluate your scenario/case based on your provided information and get back to you with our findings/details.
@Martin_Sher
When you copy only A1 to B1, the dynamic array formula needs to be refreshed for B1 to spill it into correct range. So, after the copy operation, please call RefreshDynamicArrayFormulas(bool, CalculationOptions). For example:
Workbook wb = new Workbook(...);
Cells cells = ...;
cells.CreateRange(“B1”).Copy(cells.CreateRange(“A1”));
wb.RefreshDynamicArrayFormulas(true, new CalculationOptions());
...
Hi. Thanks but still having problems.
The formula in A1 is “=SEQUENCE(7)”. This does give values in A1 = 1, A2 = 2 etc.
After the
cells.CreateRange(“B1”).Copy(cells.CreateRange(“A1”));
wb.RefreshDynamicArrayFormulas(true, new CalculationOptions());
There is a value of 1 in B1 but B2 is null.
On a second issue, am I correct that you cannot copy a formula from a single cell (eg A1) to multiple cells (eg B1: D1)
@Martin_Sher
By using the following sample code for testing, we can reproduce the issue. Found the SEQUENCE formula cannot obtain the correct values after copying the range. Please refer to the attachment (40.0 KB).
The sample code as follows:
Workbook wb = new Workbook();
Cells cells = wb.Worksheets[0].Cells;
cells["A1"].SetDynamicArrayFormula("=SEQUENCE(7)", new FormulaParseOptions(), true);
wb.RefreshDynamicArrayFormulas(true, new CalculationOptions());
wb.CalculateFormula();
wb.Save(filePath + "out_net1.pdf");
cells.CreateRange("B1").Copy(cells.CreateRange("A1"));
wb.RefreshDynamicArrayFormulas(true, new CalculationOptions());
wb.CalculateFormula();
wb.Save(filePath + "out_net2.pdf");
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-54848
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.
This is to inform you that your issue (logged earlier as “CELLSNET-54848”) has been resolved. The fix/enhancement will be included in our upcoming release (Aspose.Cells v24.1) that we plan to release in the first half of this month. You will be notified when the next version is published.
The issues you have found earlier (filed as CELLSNET-54848) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi