Spreadsheetgear replace formula with it's result

Question

I am using spreadsheetgear 2012 for c# .net excel manipulation purpose. I have some excel formulas in some of the cells. Now I want to replace the formula in the excel with the evaluated number. Is there any particular way to do this? Tried searching the spreadsheetgear forum but didn't help.

score 1 · Accepted Answer · answered Mar 12 '18 at 03:56

This could be accomplished a couple different ways:

Set IRange.Value to itself for the desired range. IRange.Formula contains a cell's formula and IRange.Value the calculated value. Setting IRange.Value to itself will effectively remove the formula from the cell and leave only the value. This will work on a single cell and also on a multi-cell range (since IRange.Value can return a object[,] array and can also be set from an object[,] array).
Use IRange.Copy(...) method with the PasteType.Values option on the desired range, which is the equivalent of doing a Paste Special > Values in Microsoft Excel.

Below are examples of these approaches:

using SpreadsheetGear;
...

// Create workbook and some local variables.
IWorkbook workbook = Factory.GetWorkbook();
IWorksheet worksheet = workbook.ActiveWorksheet;
IRange cells = worksheet.Cells;

// EXAMPLE 1 - IRange.Value with single cell
// Set A1 to a formula and output some info about A1's contents.
IRange a1 = cells["A1"];
a1.Formula = "=ROUND(RAND(),2)";
Console.WriteLine($"{a1.Address}: HasFormula={a1.HasFormula}, Formula='{a1.Formula}', Value={a1.Value}");
// OUTPUT: $A$1: HasFormula=True, Formula='=ROUND(RAND(),2)', Value=0.69
// Set IRange.Value to itself
a1.Value = a1.Value;
Console.WriteLine($"{a1.Address}: HasFormula={a1.HasFormula}, Formula='{a1.Formula}', Value={a1.Value}");
// OUTPUT: $A$1: HasFormula=False, Formula='0.69', Value=0.69


// EXAMPLE 2 - IRange.Value with multiple cells
// Works on multiple cells since IRange.Value will return an object[,]
// array for a range representing multiple cells.
IRange b1b3 = cells["B1:B3"];
b1b3.Formula = "=ROUND(RAND(),2)";
foreach (IRange cell in b1b3)
    Console.WriteLine($"{cell.Address}: HasFormula={cell.HasFormula}, Formula='{cell.Formula}', Value={cell.Value}");
// OUTPUT:
// $B$1: HasFormula=True, Formula='=ROUND(RAND(),2)', Value=0.81
// $B$2: HasFormula=True, Formula='=ROUND(RAND(),2)', Value=0.55
// $B$3: HasFormula=True, Formula='=ROUND(RAND(),2)', Value=0.24

// Set IRange.Value to itself (2D object[,] used)
b1b3.Value = b1b3.Value;
foreach (IRange cell in b1b3)
    Console.WriteLine($"{cell.Address}: HasFormula={cell.HasFormula}, Formula='{cell.Formula}', Value={cell.Value}");
// OUTPUT:
// $B$1: HasFormula=False, Formula='0.81', Value=0.81
// $B$2: HasFormula=False, Formula='0.55', Value=0.55
// $B$3: HasFormula=False, Formula='0.24', Value=0.24


// EXAMPLE 3 - IRange.Copy() with PasteType.Values option
IRange c1c2 = cells["C1:C2"];
c1c2.Formula = "=ROUND(RAND(),2)";
foreach (IRange cell in c1c2)
    Console.WriteLine($"{cell.Address}: HasFormula={cell.HasFormula}, Formula='{cell.Formula}', Value={cell.Value}");
// OUTPUT:
// $C$1: HasFormula=True, Formula='=ROUND(RAND(),2)', Value=0.04
// $C$2: HasFormula=True, Formula='=ROUND(RAND(),2)', Value=0.14

c1c2.Copy(c1c2, PasteType.Values, PasteOperation.None, false, false);
foreach (IRange cell in c1c2)
    Console.WriteLine($"{cell.Address}: HasFormula={cell.HasFormula}, Formula='{cell.Formula}', Value={cell.Value}");
// OUTPUT:
// $C$1: HasFormula=False, Formula='0.04', Value=0.04
// $C$2: HasFormula=False, Formula='0.14', Value=0.14

EXAMPLE 3 - IRange.Copy() works with single cell but does not work with merged cells. Any idea or known bug ? — Viraj Sanghavi, Oct 17 '19 at 07:27
For merged cells, IRange.Copy() throws error InvalidOperationException: Operation is not valid for a partial merged cell. — Viraj Sanghavi, Oct 17 '19 at 08:29
@VirajSanghavi - see my response to your new question at https://stackoverflow.com/a/58434455/233365 — Tim Andersen, Oct 17 '19 at 14:06

Spreadsheetgear replace formula with it's result

1 Answers1