Menu
In my following code, the value of c4 is coming out zero. C4 cell has formula SUM(C2:C3). Is EPPlus capable of reading cell with formula? Why is c4 being set to zero instead of 12.
using (var package = new ExcelPackage(existingFile))
{
ExcelWorkbook workBook = package.Workbook;
var currentWorksheet = workBook.Worksheets.First();
currentWorksheet.Cells["C2"].Value = 5;
currentWorksheet.Cells["C3"].Value = 7;
var c4 = Convert.ToDouble(currentWorksheet.Cells["C4"].Value); //c4 is zero. why?
}
463
The best, non-VBA, way to do this is using the TEXT formula. It takes a string as an argument and converts it to a value. For example, =TEXT ("0.4*A1",'##') will return the value of 0.4 * the value that's in cell A1 of that worksheet.
We can get the value of a cell (its content) by using the INDEX Function. The INDEX Function looks up a cell contained within a specified range and returns its value. In the example above, the range specified in the INDEX formula is “A1:J10”, the row is cell “C3” (“3”), and the column is cell “C4” (“5”).
Using Keyboard Shortcut Select the cells for which you want to convert formulas to values. Copy the cells (Control + C). Paste as Values – Keyboard Shortcut – ALT + ESV.
As of EpPlus 4.0.1.1, there is an extension method public static void Calculate(this ExcelRangeBase range). Invoke it prior to accessing C4's Value property:
currentWorksheet.Cells["C4"].Calculate();
and currentWorksheet.Cells["C4"].Value will return 12 after that.
69
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
