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 !!