C# and Microsoft.Office.Interop.Excel - How to replace a formula with its value?

I am trying to replace formulas on column D with their values.开发者_如何学Python

eg. currently D1 = C1 / 2

If C1 = 10, I want D1 to be 5

I need to do this because I need to delete the column C.

I tried changing the format to Text like below but it doesn't seem to replace formulas with their values

  Excel.Style style = workbook.Styles.Add("styleText", Missing.Value);
  style.NumberFormat = "@";

  Excel.Range range = (Excel.Range)sheet.get_Range("D1", Missing.Value);
  range.Style = style;

---

Here's a macro in VBA that does what you need... It's VB code but I dont think woould be a problem to translate it in C#

Sub ValuesOnly()
    Dim rRange As Range
    On Error Resume Next
    Set rRange = Application.InputBox(Prompt:="Select the formulas", Title:="VALUES ONLY", Type:=8)
    If rRange Is Nothing Then Exit Sub
    rRange = rRange.Value
End Sub

Another way to do it is to simply mimic the command Paste Special -> Values. I have just recorded a macro that does it (C5 in my sample is a cell that contains a function)

Sub Macro1()
    Range("C5").Select
    Selection.Copy
    Range("D5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

---

How do you format a comment??

Here is the solution I got, thanks to Lorenzo

private static void ReplaceFormulasWithValues(ref Excel.Worksheet sheet, char column)
{
  Excel.Range range = (Excel.Range)sheet.get_Range(column + "1", Missing.Value).EntireColumn;
  range.Copy(Missing.Value);
  range.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues,
    Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
}

This is another way to do it (C1 contains a formula, the result is placed in D1)

static void Main( string[] args ) {
    string fileName = @"D:\devprj\Temp\TempProject\bin\Debug\Cartel1.xlsx";
    Application ac = new Application();
    Workbook wb = ac.Workbooks.Open( fileName );
    Worksheet ws = wb.Sheets[1];

    Range rangeOrigin = ws.get_Range( "C1" );
    Range rangeDestination = ws.get_Range( "D1" );
    rangeDestination.Value = rangeOrigin.Value2;

    wb.Save();
}

---

You can't just change the display style, because that doesn't change the content of the cell. When you're doing this manually in Excel you need to copy the column and then choose Paste Special -> Values to paste the values rather than the formulae. I imagine there's a programatic way to do the same operation.