We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Calculate the cells using formula and data present in other sheet using Aspose C#

Aspose.Cells Product Family
#1

Hello Aspose,
I have a source workbook where I have two sheets named ‘Data’ and ‘Calculations’. using Aspose I will copy these, calculate and export the output excel. In ‘Data’ sheet I will add dynamic data using code and in sheet ‘Calculations’ I will set the static complex formulas for a row, that row cells formula contains the data from ‘Data’ sheet for calculation. For example if the Data Sheet contains 1000 dynamic rows then I need to calculate 1000 rows in ‘Calculations’ sheet. How can I Roll out the formula from each cell to 1000 rows in Calculations Sheet? (Note: the rows are not consistent to 1000)
Any example related to this greatly appreciated.

Thanks in Advance

#2

@kiran5388,

I am not sure about your requirements fully. What’s wrong with your existing approach? I mean, you may easily insert data into “Data” worksheet using Aspose.Cells APIs (e.g. using Cell.PutValue() method or Cells.ImportData() method, etc.), see the topics in the Data section for your reference. In “Calculations” sheet, you may set formulas using Cell.Formula attribute in your desired range of cells accordingly, see the document on setting formulas or calculating formulas for your reference. If you need to set a shared formula (common formula) to your desired range of cells, you may see the document for your reference.

Could you elaborate if you still have any confusion or issue after reading the paragraph (above). You may also share sample Excel files (input file and your expected output file, etc.) to make us understand your needs.

PS. please zip the files prior attaching.

#3

I understand your point, but the calculation sheet has a lot of static formulas, its just to get the value from Data sheet and do some math in Calculation Sheet give the result. Do not want to add each and every formula through code. Please find the attached Excel file.

Thanks.Sample.zip (21.6 KB)

#4

@kiran5388,

I checked your output sheet a bit. I am afraid, there is no other way but to insert formulas manually for each row category (Mean, Standard Deviation, Minimum, Maximum, etc.). Here you may try to use shared formulas feature (if appropriate).

In short, you have implement formulas manually (via Aspose.Cells APIs) for your desired category accordingly by yourselves. To get results dynamically against formulas, you got to first call Workbook.CalculateFormula() before getting values from different range of cells.

#5

hi,
Is there any way to get all the formulas from work sheet with respective NameBox value or cell index or row. I want to get the each and every cell formula from the source file and modify the formula and update the sheet to calculate.
Please

#6

@kiran5388,

You have to loop through your worksheet cells to retrieve formulas or modify these for your requirements. See the sample code segment which will work efficiently for the purpose:
e.g.
Sample code:

  string filePath = @"e:\test\Book1.xlsx";

            //Load a source workbook
            Workbook wb = new Workbook(filePath);

            int cnt = 0;
            string cellname = null;
            string ftext = null; //you may also define some arrays or list to store formulas

            //Iterate all worksheets
            for (int i = 0; i < wb.Worksheets.Count; i++)
            {
                Cells cells = wb.Worksheets[i].Cells;

                //iterate all the cells.
                for (IEnumerator ie = cells.GetEnumerator(); ie.MoveNext(); )
                {
                    Cell cell = (Cell)ie.Current;

                    if (cell.IsFormula)
                    {

                        cellname = cell.Name;
                        ftext = cell.Formula;
                        //..........
                        //Your code goes here.	
                        //..............	
                        cnt++;
                    }//if
                }//for
            }//for

Hope, this helps a bit.

#7

I am facing an issue with the formulas where "_xlws. " getting added to the Filter formulas after generating excel output and the output result is wrong. For example :

Input formula is =IFERROR(SUM(FILTER($Q$16:Q25/$R$16:R25,$R$16:R25<>0)),0) and Output Formula is changed to =IFERROR(SUM(_xlws.FILTER(@$Q$16:Q25/@$R$16:R25,@$R$16:R25<>0))/100,0)

as a result answer is 0 instead of 18.43.

Please find attached snip. Any help will be greatly appreciated. Thanks
image.png (17.3 KB)

#8

@kiran5388,

Please try our latest version/fix (Aspose.Cells v21.8) if you are not already using it. If you still find the issue with latest version, kindly create a standalone console application (runnable), zip the project to post us with template Excel files. We will check your issue soon.

#9

I am using version 17.03. I just want to make sure that this kind of issue happened with earlier versions? If that is true, then I need to talk to my company to upgrade the version.

#10

@kiran5388,

Please note, FILTER is newer function (Office Excel 365 / 2019), so surely this was not supported in older versions of the product that you are using. FILTER formula/function is supported in latest versions of the APIs, so you are recommended you upgrade to latest APIs set. Also, see the document on what functions/formulas are supported in newer Aspose.Cells version(s).

#11

Sure, thank you

#12

@kiran5388,

You are welcome.

#13

Instead of filter formula if I managed to use SUMPRODUCT and put some conditions like IF condition or IFERROR inside it or for eg:
Template formula: =SUMPRODUCT(IFERROR(Data!$AK2:Data!AK11/Data!$Q2:Data!Q11,0))/100

Output looks like this, adding @ symbols
=SUMPRODUCT(IFERROR(@Data!$AK2:Data!AK11/@Data!$Q2:Data!Q11,0))/100

And do you think the @ symbols adding because of my old version v17.03

#14

@kiran5388,
It is because of the version of ms excel instead of versions of Aspose.Cells product. In newer versions of ms excel such as 2016, excel365, there is one new kind of formula “dynamic array formula”. For your formula, if you create it manually in lower version ms excel, it was taken as normal formula just like using our api such as Cell.Formula=…; If you save the workbook in ms excel to a file and then open the file with newer version of ms excel, you will see that ‘@’ symbols have been added automatically.

To void the ‘@’, please set your formula as array formula(using Cell.SetArrayFormula() instead).

For more detail about ‘@’ symbol, please check related topics on office’s support site, for example the page: https://support.microsoft.com/en-us/office/implicit-intersection-operator-ce3be07b-0101-4450-a24e-c1c999be2b34.

#15

All I am doing is this, I am storing the formula in the template and access it and make the range as dynamic and setting the formula. So where do I need to use Cell.SetArrayFormula() ?

Here is the sample code
here in the below code snip I am using the XYZ1 as variable to set the dynamic range

Cells cells3 = _SourceWorkbook.Worksheets[3].Cells;
string cellName = “”;
int cnt = 0;
string ftext = null;
try
{
for (IEnumerator ie = cells3.GetEnumerator(); ie.MoveNext():wink:
{
Cell cell = (Cell)ie.Current;

                    if (cell.IsFormula)
                    {

                        ftext = cell.Formula.Replace("XYZ1", (rows.Count + 1).ToString());
                        // Apply the formula to particular cell
                        cells3[cell.Name].Formula = ftext3;
                        cnt++;
                    }
                }
            }
#16

@kiran5388,

Please try to change the code:

cells3[cell.Name].Formula = ftext3

to:

cells3[cell.Name].SetArrayFormula(ftext3,1,1);

#17

Thank you very its working like charm!

#18

@kiran5388
Good to know the solution works for you. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.