预填充公式未刷新问题修复：OpenXML操作Excel技术求助

I've looked over your code and spotted a few key issues that are preventing your pre-filled formulas from refreshing after populating data. Let's walk through the fixes step by step:

1. Correct Cell Reference Mismatch #

First, there's a clear mismatch in your row and cell references: you're fetching row 2 (rowIndex=2) but setting cell references to "A1" and "B1". This means your data is being placed in the wrong cells, so your formulas (which are likely pointing to rows 2+) aren't picking up the new values.

2. Remove Stale Calculation Chain #

Excel's CalculationChain stores cached calculation order, and when you modify the workbook via OpenXML, this chain can become stale. Keeping it will prevent Excel from recalculating formulas properly. We need to delete the existing calculation chain so Excel regenerates it on load.

3. Ensure Workbook Settings Are Saved #

You set ForceFullCalculation and FullCalculationOnLoad on the workbook's CalculationProperties, but you only saved the worksheet. These workbook-level settings need to be persisted by saving the workbook itself.

4. Let using Handle Document Closure #

You don't need to manually call spreadSheet.Close()—the using statement automatically disposes of the document properly when it exits the block, which avoids potential corruption.

Fixed Code #

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using Microsoft.IT.Sales.RelationshipManagement.Segmentation.DataContracts;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading.Tasks;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            PopulateExcel("E:\\Formula.xlsx");
            Console.WriteLine("Data populated successfully! Formulas will refresh on Excel load.");
        }

        private static void PopulateExcel(string path)
        {
            using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(path, true))
            {
                WorkbookPart workbookPart = spreadSheet.WorkbookPart;
                WorksheetPart worksheetPart = workbookPart.WorksheetParts.FirstOrDefault();
                if (worksheetPart == null)
                {
                    Console.WriteLine("No worksheet found in the workbook.");
                    return;
                }

                SheetData sheetData = worksheetPart.Worksheet.Descendants<SheetData>().LastOrDefault();
                if (sheetData == null)
                {
                    Console.WriteLine("No SheetData found in the worksheet.");
                    return;
                }

                // Get or create row 2 (since your formulas start here based on pre-filled rows)
                Row dataRow = GetRow(sheetData, 2);

                // Set cells A2 and B2 (matching the row we're working with)
                Cell cellA = new Cell();
                SetCell(cellA, "A2", 1);
                dataRow.Append(cellA);

                Cell cellB = new Cell();
                SetCell(cellB, "B2", 2);
                dataRow.Append(cellB);

                // Configure workbook to force full calculation on load
                CalculationProperties calcProps = workbookPart.Workbook.CalculationProperties;
                if (calcProps == null)
                {
                    calcProps = new CalculationProperties();
                    workbookPart.Workbook.Append(calcProps);
                }
                calcProps.ForceFullCalculation = true;
                calcProps.FullCalculationOnLoad = true;

                // Delete existing calculation chain to force Excel to regenerate it
                CalculationChainPart calcChainPart = workbookPart.CalculationChainPart;
                if (calcChainPart != null)
                {
                    workbookPart.DeletePart(calcChainPart);
                }

                // Save both worksheet and workbook changes
                worksheetPart.Worksheet.Save();
                workbookPart.Workbook.Save();
            }
        }

        private static Row GetRow(SheetData wsData, uint rowIndex)
        {
            var row = wsData.Elements<Row>().FirstOrDefault(r => r.RowIndex == rowIndex);
            if (row == null)
            {
                row = new Row { RowIndex = rowIndex };
                wsData.Append(row);
            }
            return row;
        }

        private static void SetCell(Cell cell, string reference, int value)
        {
            cell.CellReference = reference;
            cell.DataType = CellValues.Number;
            cell.CellValue = new CellValue(value.ToString());
        }
    }
}

Why This Works #

• Corrected Cell References: Your data is now placed in the correct row (row 2) so formulas referencing these cells will pick up the values.
• Deleted Calculation Chain: Removing the stale calculation chain ensures Excel doesn't rely on cached calculation data and will recalculate all formulas from scratch when the file is opened.
• Persisted Workbook Settings: Saving the workbook ensures the ForceFullCalculation and FullCalculationOnLoad flags are stored, telling Excel to perform a full recalculation on launch.
• Proper Resource Management: Letting the using statement handle document closure prevents potential file corruption that could interfere with formula calculation.

When you open the modified Excel file, it should automatically recalculate all 10000 pre-filled formulas based on the new data you've added.

内容的提问来源于stack exchange，提问作者Manoj