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#

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

@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.

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)

@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.

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

@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.

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)

@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.

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.

@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).

Sure, thank you

@kiran5388,

You are welcome.