I hope this issue finds you well. I am writing to bring to your attention an issue I have encountered while using the Aspose functionality.
In our template file we have written one formula to filter some records. This formula works fine when we use it directly in excel file and display correct number of rows. When we use this formula through the code(using Aspose Function- CalculateFormula() internally) to calculate the resultant rows, this doesn’t work properly and hides some records unexpectedly.
I have created a sample Project in which we have “DataToTest.xlsx” file that have Tab “CAS” which have all the records and another Tab “Report” which have formula that is calculating and showing valid records.
In CAS Tab we have total 46 records and as per formula only 19 records are valid. But currently we are getting only 8 records.
As per our analysis Aspose method “CalculateFormula()“ is creating issue and hiding the records. This method works fine when we have records in sorted order.
Below are the valid records that must be displayed.
Aimen Media tech Aquaguard Financer Banor Pop Resistant BTIGTM Cloudreached High Finish Resistant Cam German Resistant Hudson Struct Street India NPL IPL Internat Mark Cendee Metro tele North American Bacardi NRP MRP CAM Primar Realistic Establish Trillion Trent TringMark Go Vivian Annexure Vivian Capstal Lim
Below are the records that we are getting currently.
Aimen Media tech Banor Pop Resistant Finish Resistant Cam Hudson Struct Street Internat Mark Cendee Metro tele Primar Vivian Annexure
I have tested using your sample file and sample application and it works fine. I am getting 19 records in the output Excel file sheet as per your expectations.
Let us know if you still find the issue with Aspose.Cells for .NET v24.7
I hope this issue finds you well. I am writing to bring to your attention an issue I have encountered while using the Aspose functionality.
The previous usecase that we mentioned in the above trail was fixed by using a new version of Aspose. However, after testing further we found the below issue.
In our template file we have written one formula to filter some records in “Report” tab. This formula works fine when we use Aspose version 23.3 but breaks when we use Aspose version 24.7.
In one cell we have formula to calculate valid number of records to display and in just next cell we have some hardcoded text. We are calculating the number of rows required to display valid records and then create that many blank rows to fill with valid records. With Aspose version 23.3 it is working perfectly fine but with version 24.7 it is not working and giving “#VALUE!” error in cell. When there is sufficient number of blank rows are available to display valid deals then as well it is also working fine with Version 24.7.
I have created a sample Project in which we have “TestData_DRM.xlsx” and “TestTemplate_DRM.xlsx“ files. In TestTemplate_DRM.xlsx we have Tab “Report” which have formula that is calculating and showing valid records. In “TestData_DRM.xlsx” we have Tab “Data” which have all the records.
As per our analysis Aspose method “CalculateFormula()“ is creating issue. In Test Project We have one method “ExpandSelectedRange” in which we are using this “CalculateFormula()“ method to calculate valid records and create empty rows and looks like this is creating issue.
Would be glad if this can be looked into urgently. Also let us know if you need anything else from us.
After an initial test, I am able to reproduce the issue as you mentioned by using your files with sample app. I found the formula in the cell is evaluated as “#VALUE!” error.
We require thorough evaluation of the issue. 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-56350
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.
We evaluated your issue using your sample files in detail.
We got a “#VALUE!” error as the cell value in the output file when running your sample project with the sample data file and template file. Please note that for the Report!A2 cell, there is a dynamic array formula which needs to be spilled into the range: A2:A9. Since the A3 cell of the Report worksheet is not empty in the template, the spilling failed, so A2 will be shown as “#SPILL!” and the corresponding cell value will be saved as “#VALUE!”. You may check/confirm this by evaluating it in MS Excel manually, and you will get the same results. I have now updated your template file by removing the A3 cell value and then re-saving the file in MS Excel manually. Now, when I used the updated file (having Report!A3 cell empty) using your sample project, it works as expected, and the correct cell values are pasted into the A2:A9 range of the Report when the dynamic (array) formula is calculated. Please find the updated template file and output Report file attached for your reference. files1.zip (55.3 KB)
In short, since it is not an issue with Aspose.Cells APIs so we won’t fix it.
Hi Team,
We already know that. However, as mentioned in the mail above . This functionality use to work correctly with Aspose version 23.3. However, stopped working with Apose 24.7. Hence this request.
Aspose.Cells behaves similarly to Microsoft Excel. You may obtain comparable results when manually testing your task in Microsoft Excel (by setting the A3 cell of the Report worksheet not empty). The older version may have performed differently than Microsoft Excel. Aspose.Cells adheres to Microsoft Excel standards, so the latest version accurately calculates formulas.