Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Fri Apr 13, 2018 10:08 am

In our application, we would like to use Spire.XLS to let user input parameters and formula, and calculate the result using the library; while user does not need to view/save the result in MS Excel application.

Hence we tried to use named range, where the range name is the parameter's name , e.g.:

Code: Select all
Workbook workBook = new Workbook();
Worksheet workSheet = workBook.Worksheets[0];


/*add parameter 1 (PRICE = 12.5) into Cell A1*/
INamedRange namedRange = workBook.NameRanges.Add("PRICE");
namedRange.RefersToRange = workSheet.Range["A1:A1"];
workBook.NameRanges.GetByName("PRICE").RefersToRange.Cells[0].Value2 = 12.5;


/*add parameter 2 (QTY = 2) into Cell A2*/
namedRange = workBook.NameRanges.Add("QTY");
namedRange.RefersToRange = workSheet.Range["A2:A2"];
workBook.NameRanges.GetByName("QTY").RefersToRange.Cells[0].Value2 = 2;

/*Put the formula (PRICE * QTY) in Cell B1*/
workSheet.Range["B1"].Formula = "=PRICE*QTY";


But we have difficulty in getting the calculated result stored in Cell B1 directly.
We noted that there is a workBook.CalculateFormulaValue() that can let us input a formula directly; but it returned an exception if we put the formula "=PRICE*QTY" directly.

Please note that the above code is for illustration only, in reality the parameters are dynamically input by users; therefore we used name range.

Are there any method (similar as CalculateFormulaValue()) that can allow us to calculate the formula that is stored inside a range, and get the result?

thanks !!

sxlsgs
 
Posts: 7
Joined: Fri Apr 13, 2018 2:06 am

Mon Apr 16, 2018 3:28 am

Hello,

Sorry for late reply as we were off work at weekend.
I did face an exception while executing workBook.CalculateFormulaValue("=PRICE*QTY") using free version, sorry for that and we have no plan to maintain free version at present. I can confirm the issue doesn't exist in our latest commercial version (Spire.XLS Pack(Hotfix) Version:8.3.5), please upgrade to this one and have a try. On the other hand, in the commercial version, you could add below code to get the calculated result stored in Cell B1 directly. If there is any question, welcome to write back.
Code: Select all
workSheet.Range["B1"].Formula = "=PRICE*QTY";
workBook.CalculateAllValue();
object objResult = workSheet.Range["B1"].FormulaValue;

Sincerely,
Nina
E-iceblue support team
User avatar

Nina.Tang
 
Posts: 1182
Joined: Tue Sep 27, 2016 1:06 am

Mon Apr 16, 2018 8:47 am

Thanks.

In your suggestion of using workBook.CalculateAllValue();

could i know if we can specify it to calculate only a specific range/cell, instead of calculating from all workbooks? I wonder if the performance would better if I can just specify it to calculate a specific range/cell.
As this CalculateAllValue() seems suppose to calculate all formula in a workbook.

sxlsgs
 
Posts: 7
Joined: Fri Apr 13, 2018 2:06 am

Mon Apr 16, 2018 9:58 am

Hi,

Thanks for your prompt reply.
Please refer to following sample code.
Code: Select all
workSheet.Range["B1"].Formula = "=PRICE*QTY";
object obj=workBook.CaculateFormulaValue(workSheet.Range["B1"].Formula);

Sincerely,
Nina
E-iceblue support team
User avatar

Nina.Tang
 
Posts: 1182
Joined: Tue Sep 27, 2016 1:06 am

Wed Apr 18, 2018 8:43 am

Hello,

Greetings from E-iceblue!
Has your issue been resolved?
Your feedback will be greatly appreciated.

Sincerely,
Nina
E-iceblue support team
User avatar

Nina.Tang
 
Posts: 1182
Joined: Tue Sep 27, 2016 1:06 am

Sat Apr 21, 2018 2:51 am

We have tried the solution and it is ok now. Thanks.

sxlsgs
 
Posts: 7
Joined: Fri Apr 13, 2018 2:06 am

Mon Apr 23, 2018 1:11 am

Hi,

Thanks for your feedback.
Just feel free to contact us if you need further assistance.
Have a nice day!

Sincerely,
Nina
E-iceblue support team
User avatar

Nina.Tang
 
Posts: 1182
Joined: Tue Sep 27, 2016 1:06 am

Tue Jun 23, 2020 11:29 am

Hello everyone !

For my appplication I am editing some cells(let's call them cells A) in my excel worksheet but then some cells(let's call them cells B) are depending on cells A.
So I need cells B to be recalculated.
I tried the previous answers but without any success.
I am working with C#.
Here is the function I am using to recaculate cells B (not working).

Code: Select all
recalculateValues(workbook, sheet, new string[] { "C5", "C14", "C15", "C16", "C17", "C18", "C19", "C20" });

private static void recalculateValues(Workbook wb, Worksheet ws, string[] cells)
        {
            for (int i = 0; i < cells.Length; i++)
                wb.CaculateFormulaValue(ws.Range[cells[i]].Formula);
        }


Any idea how to solve this ?

Thank you,
Ryan

RyanVnx_76
 
Posts: 2
Joined: Tue Jun 23, 2020 11:23 am

Wed Jun 24, 2020 2:52 am

Hi Ryan,

Thanks for your inquiry.
Please refer to the following modified code. If there is any question, please provide your input file as well as your desired output for further investigation.
Code: Select all
        private static void recalculateValues(Workbook wb, Worksheet ws, string[] cells)
        {
            for (int i = 0; i < cells.Length; i++)
            {
                Object obj = wb.CaculateFormulaValue(ws.Range[cells[i]].Formula);
                //Set the formula number value
                ws.Range[cells[i]].FormulaNumberValue = (double)obj;
            }             
        }


Sincerely,
Rachel
E-iceblue support team
User avatar

rachel.lei
 
Posts: 1571
Joined: Tue Jul 09, 2019 2:22 am

Wed Jun 24, 2020 10:18 am

Hi Rachel,

I used the code you gave me. Now I think I am having trouble to retrieve the recalculated values of cells B (see previous post).

Here is the code I am using :

Code: Select all
recalculateValues(workbook, sheet, new string[] { "C5", "C13", "C14", "C15", "C16", "C17",
                    "C18", "C19", "C20" });
excel_data["taux_restitution"] = Convert.ToDouble(sheet.Range["C7"].Value);
excel_data["taux_gestion"] = Convert.ToDouble(sheet.Range["C8"].Value);
excel_data["CA_HT"] = sheet.Range["C13"].FormulaNumberValue;
excel_data["Salaire_brut"] = Convert.ToDouble(sheet.Range["C18"].FormulaNumberValue;
excel_data["Salaire_net"] = Convert.ToDouble(sheet.Range["C19"].FormulaNumberValue;
excel_data["Net_Frais"] = Convert.ToDouble(sheet.Range["C20"].FormulaNumberValue;


It's working fine with C7 and C8 because these have fixed values and are not being recalculated.
All others are returning 0. I would like to retrieve the newly calculated values for each cells.

Is there something I am did wrong here ?

Thank you again for you help,
Ryan.

RyanVnx_76
 
Posts: 2
Joined: Tue Jun 23, 2020 11:23 am

Thu Jun 25, 2020 2:49 am

Hi Ryan,

Thanks for your feedback.
I tested the following code with the latest Spire.XLS Pack(Hotfix) Version:10.5.7, but did not encounter the issue you mentioned, it could get the correct updated value. Attached is my test file for your reference. If you are using an older version, I'd suggest you download the latest version and try again. If the issue still occurs, please share your input file with us. You could send it to us(support@e-iceblue.com) via email.
Code: Select all
    static void Main(string[] args)
    {
        Workbook wb = new Workbook();
        wb.LoadFromFile("test.xlsx");

        Worksheet sheet = wb.Worksheets[0];
        //Update the value of cells A
        for (int i = 1; i < 21; i++)
        {
            sheet.Range[i, 1].Value = (i + 2).ToString();
        }

        //Calculate the value of cells B
        recalculateValues(wb, wb.Worksheets[0], new string[] { "C5", "C14", "C15", "C16", "C17", "C18", "C19", "C20" });
        double v1 = Convert.ToDouble(sheet.Range["C7"].Value);
        double v2 = Convert.ToDouble(sheet.Range["C8"].Value);
        //Get the updated values
        double v3 = Convert.ToDouble(sheet.Range["C18"].FormulaNumberValue);
        double v4 = Convert.ToDouble(sheet.Range["C19"].FormulaNumberValue);
        double v5 = Convert.ToDouble(sheet.Range["C20"].FormulaNumberValue);
        wb.SaveToFile("out.xlsx");
    }

    private static void recalculateValues(Workbook wb, Worksheet ws, string[] cells)
    {
        for (int i = 0; i < cells.Length; i++)
        {
            Object obj = wb.CaculateFormulaValue(ws.Range[cells[i]].Formula);
            //Set the formula number value
            ws.Range[cells[i]].FormulaNumberValue = (double)obj;
        }
    }


Sincerely,
Rachel
E-iceblue support team
Attachments
test.zip
(6.34 KiB) Downloaded 185 times
User avatar

rachel.lei
 
Posts: 1571
Joined: Tue Jul 09, 2019 2:22 am

Fri Jun 26, 2020 5:02 am

Hi Ryan,

Thanks for providing more information via email.
I tested your file and indeed found the formula values of the generated XML file are incorrect, see the attached picture. This issue has been logged into our bug tracking system with the ticket SPIREXLS-2459 for further investigation and fixing.
We will let you know if there is any update, sorry for the inconvenience caused.

Sincerely,
Rachel
E-iceblue support team
Attachments
error.png
error.png (38.71 KiB) Viewed 2093 times
User avatar

rachel.lei
 
Posts: 1571
Joined: Tue Jul 09, 2019 2:22 am

Return to Spire.XLS