CODEWITHSUNDEEP
c#excelexcel-interop
Borysław Bobulski
Borysław Bobulski

Reputation: 471

Saving recalculated Excel file

I need to remove all Excel formulas from my worksheet (replace formulas with its results) then save file. For example - instead B3+13 I want to have 2013-04-11 in cell when I open file using OpenXML

I have found this article http://office.microsoft.com/en-us/excel-help/replace-a-formula-with-its-result-HP010066258.aspx

It works - after saving there is no formula. I need the same effect in C#. I've tried many solutions in Internet but still no hope.

                string source = @"C:\Source\doc.xlsx";
            object Unknown = Type.Missing;

            Workbook doc = newApp.Workbooks.Open(
                source,
                 Unknown, 
                 Unknown, 
                 Unknown, 
                 Unknown, 
                 Unknown, 
                 Unknown, 
                 Unknown, 
                 Unknown, 
                 Unknown, 
                 Unknown, 
                 Unknown, 
                 Unknown, 
                 Unknown, 
                 Unknown);

            newApp.Calculation = Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationManual;

            doc.ForceFullCalculation = true;
            newApp.CalculateFull();
            newApp.CalculateBeforeSave = true;

            doc.SaveAs("c:\\temp\\recalc.xlsx", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
            false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            doc.Close();

So what is the problem? Why I can see "=DY3+1" instead "2013-04-11"?

Upvotes: 1

Views: 1496

Answers (2)

Yuri Kopylovski
Yuri Kopylovski

Reputation: 380

Select the range that contains your data, then copy and paste the values in place:

using Excel = Microsoft.Office.Interop.Excel;
foreach (Excel.Worksheet ws in doc.Worksheets)
{
    Excel.Range targetRange = (Excel.Range)ws.UsedRange;
    targetRange.Copy(Type.Missing);
    targetRange.PasteSpecial(Excel.XlPasteType.xlPasteValues, 
        Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
}

This duplicates the "Replace formulas with their calculated values" section of the document you posted. It's possible to select all the sheets and copy/paste special in Excel itself to get the results rather than the formulas, but I have had some issues working with grouped sheets through code.

Upvotes: 3

Charles Williams
Charles Williams

Reputation: 23505

Calculating a formula retains both the formula and its result. If you want to overwrite the formula with its result value then you need to either:
- set its value to its value: Range.Value2=Range.value2
or use Copy and paste-special values

Upvotes: 0

Related Questions