如何使用Open XML在Excel 2010工作表中插入公式？

Question

我正在使用Visual Studio 2010 (VB.Net)和Open XML SDK 2.0。如何在Excel 2010工作表中插入公式？当我这样做时，我还希望将单元格的CellValue属性设置为DBNull或EmptyString，以便在用户打开工作簿时强制Excel重新计算该单元格。

- vbasic321

Answer 1

只需将 CellValue 设置为 null，然后实例化新的 CellFormula，如下所示：

Cell cell = new Cell()
{
  CellReference = "A3",
  DataType = new EnumValue<CellValues>(CellValues.Number),
  CellFormula = "SUM(A1:A2)"
};

单元格的值将在Excel中打开文档时计算。

- JonoW

---

我们如何在不打开Excel的情况下完成操作。 - AjayR

Answer 2

这段内容来自于附加在Microsoft Office帮助文件中的Open XML SDK 2.0的帮助文档，经过一些修改以添加公式。 Main()函数会找到一个空白的Excel文档，并在一个工作表上将SUM()公式添加到单元格A3。

Sub Main()
    Dim outputFilePath = "C:\Book1.xlsx"
    Dim doc As SpreadsheetDocument = SpreadsheetDocument.Open(outputFilePath, True)
    Dim workbookPart As WorkbookPart = doc.WorkbookPart
    Dim worksheetPart As WorksheetPart = workbookPart.WorksheetParts.First()

    InsertCellInWorksheet("A", 3, worksheetPart)
End Sub

' Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
' If the cell already exists, return it. 
Private Function InsertCellInWorksheet(ByVal columnName As String, ByVal rowIndex As     UInteger, ByVal worksheetPart As WorksheetPart) As Cell
    Dim worksheet As Worksheet = worksheetPart.Worksheet
    Dim sheetData As SheetData = worksheet.GetFirstChild(Of SheetData)()
    Dim cellReference As String = (columnName + rowIndex.ToString())

    ' If the worksheet does not contain a row with the specified row index, insert one.
    Dim row As Row
    If (sheetData.Elements(Of Row).Where(Function(r) r.RowIndex.Value = rowIndex).Count() <> 0) Then
        row = sheetData.Elements(Of Row).Where(Function(r) r.RowIndex.Value = rowIndex).First()
    Else
        row = New Row()
        row.RowIndex = rowIndex
        sheetData.Append(row)
    End If

    ' If there is not a cell with the specified column name, insert one.  
    If (row.Elements(Of Cell).Where(Function(c) c.CellReference.Value = columnName + rowIndex.ToString()).Count() > 0) Then
        Return row.Elements(Of Cell).Where(Function(c) c.CellReference.Value = cellReference).First()
    Else
        ' Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
        Dim refCell As Cell = Nothing
        For Each cell As Cell In row.Elements(Of Cell)()
            If (String.Compare(cell.CellReference.Value, cellReference, True) > 0) Then
                refCell = cell
                Exit For
            End If
        Next

        Dim newCell As Cell = New Cell
        newCell.CellReference = cellReference
        newCell.CellFormula = New CellFormula("SUM(A1:A2)")

        row.InsertBefore(newCell, refCell)
        worksheet.Save()

        Return newCell
    End If
    End Function

请注意，此方法假定您在公式中引用的每个单元格都具有正确标记的引用。

- Joe Masilotti

---

我在这段代码中遇到了构建错误。CellValues.[Formula]和newCell.Formula不包含Formula方法/属性。您是否正在使用OpenXML2.0？ - user117499

我正在使用版本：2.0.5022.0 - user117499

@eschneider 已更新数据类型和公式，请现在尝试。 - Joe Masilotti

@eschneider 实际上，所有的问题都归结为：newCell.CellFormula = New CellFormula("SUM(A1:A2)") - Joe Masilotti

@eschneider 我添加了我的从 Main() 调用的代码。请确保您在 C:\Book1.xlsx 有一个有效的 Excel 文档，其中只有 一个 工作表。 - Joe Masilotti

Answer 3

您可以在模板Excel中设置公式，并编写以下代码进行重新计算：

spreadSheet.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = True spreadSheet.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = True

- leyla azari

---

我将所有公式都写在Excel单元格中，然后使用这段代码，就可以运行了。 - leyla azari