Force Recalculation of Workbook / Worksheet using SpreadsheetML

If you change values in cells in a worksheet, you may want to force recalculation upon open.  The following code will do it.

This operates on an XLSX (attached to this post) that contains a number in cell A1, and presupposes that there is a formula that relies on A1, so therefore Excel should run the calculation engine upon opening.  The code then uses Excel automation to load and save the XLSX, causing the calculation engine to run.

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using Ex = Microsoft.Office.Interop.Excel;
using System.Reflection;

class Program
{
    static void Main(string[] args)
    {
        FileInfo src = new FileInfo("../../Test.xlsx");
        FileInfo dest = new FileInfo("../../Test-Out.xlsx");
        FileInfo dest2 = new FileInfo("../../Test-Out2.xlsx");
        if (dest.Exists) dest.Delete();
        File.Copy(src.FullName, dest.FullName);

        using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(dest.FullName, true))
        {
            // This is arbitrary code that adds 10 to the upper-left cell of the first worksheet.
            // It is used in a calculation, so must force recalculation upon open.

            WorkbookPart partWorkbook = spreadSheet.WorkbookPart;
            Sheet firstSheet = (Sheet)partWorkbook.Workbook.Sheets.First();
            WorksheetPart worksheetPart = (WorksheetPart)partWorkbook.GetPartById(firstSheet.Id);
            var sheetData = worksheetPart.Worksheet.Elements<SheetData>().FirstOrDefault();
            if (sheetData != null)
            {
                var row = sheetData.Elements<Row>().FirstOrDefault();
                if (row != null)
                {
                    var cell = row.Elements<Cell>().FirstOrDefault();
                    if (cell != null)
                    {
                        var cellValue = cell.CellValue.Text;
                        double d;
                        if (double.TryParse(cellValue, out d))
                        {
                            cell.CellValue.Text = (d + 10).ToString();
                        }
                    }
                }
            }

            var calculationProperties = spreadSheet.WorkbookPart.Workbook.CalculationProperties;
            calculationProperties.ForceFullCalculation = true;
            calculationProperties.FullCalculationOnLoad = true;
        }

        // Use Excel automation to open and save the workbook, thereby running the calculation engine.
        Ex.Application app = new Ex.Application();
        string execPath =
          Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase);
        Ex.Workbook book = app.Workbooks.Open(dest.FullName);
        book.SaveAs(dest2.FullName);  // or book.Save();
        book.Close();
        app.Quit();
    }
}

Download – Sample Excel

Cheers, Eric