如何使用VB.net保留Excel中的公式并清除内容？

Question

要实现的目标：

• 我想用新数据替换一系列数据。

• 所以，我想清除旧数据并用新数据替换它。

• 但是旧数据中的公式也需要适用于新数据。

我做了以下操作：

Private Sub updateData(ByRef sheet As Excel.Worksheet, ByVal dataRow As String, ByRef data As Object)
    Dim range As Excel.Range
    downRange(sheet, dataRow).ClearContents()  // Note this !!
    // Both data and formulas are lost due to this.
    // What shall I use instead of this to retain the formulas in the first row.
    range = sheet.Range(dataRow.Substring(0, dataRow.Length - 1) + (data.GetLength(0) + 1).ToString())
    range.Value2 = data
    // If the 1st row still has the formulas, then I can do AutoFill for this new data.
End Sub

Private Function downRange(ByRef sheet As Excel.Worksheet, ByVal rangeString As String)
    Dim range As Excel.Range
    range = sheet.Range(rangeString)
    range = sheet.Range(range, range.End(Excel.XlDirection.xlDown))
    Return range
End Function

但是，问题是：

• 公式正在丢失。（显然）
• 我想保留第一行中的公式，以便我可以进行自动填充
• 您能提出一些解决方案吗？

所需结果的演示：

旧数据：

  |   A      |  B  |  C  |
1 |   H1     |  H2 |  H3 |
2 |   =B2+C2 |  5  |  9  |   Therefore, A2 = 14
3 |   =B3+C3 |  7  |  2  |   Therefore, A3 = 9

我想替换数据并保留公式，以便（新数据）：

  |   A      |  B  |  C  |
1 |   H1     |  H2 |  H3 |
2 |   =B2+C2 |  4  |  6  |   Therefore, A2 = 10
3 |   =B3+C3 |  3  |  5  |   Therefore, A3 = 8

给定的调用是updateData(sheet, "A2:C2", dataFromDB)

如何保存公式但更改数据而不更改函数调用？

Answer 1

我遇到了类似的问题，并在清除单元格时使用了这个逻辑。下面的代码片段清除列 AC 和列中的单元格； F 来自第 2-1000 行（第 5 行除外）。当您想要对要清除的内容进行细粒度控制时，请使用此选项。 ：

Const clearCells = "A?:C?,F?"

Sub clearCells()
For i = 2 To 1000
If i <> 5 Then
Range(Replace(clearCells, "?", i)).ClearContents
End If
Next i
End Sub

更简单的方法是清除第 2-2000 行中的 AC 和 F 列：

    Range ("A2:C2000,F2:F2000").ClearContents

最后，要重写程序，您可以循环遍历范围，实例化一个新范围，并仅在单元格没有公式时将单元格添加到该范围像这样：

Private Function downRange(ByRef sheet As Excel.Worksheet, ByVal rangeString As String)
Dim inRange As Excel.range
Dim outRange As range
Set inRange = sheet.range(rangeString)
Set inRange = sheet.range(inRange, inRange.End(Excel.XlDirection.xlDown))
Dim r As range
For Each r In inRange
  If Left(r.Formula, 1) <> "=" Then
    If outRange Is Nothing Then
    Set outRange = r
    Else
    Set outRange = Application.Union(outRange, r)
    End If
  End If
Next r
downRange = outRange
End Function

Answer 2

我想你已经快到了 - 看起来你只需要从顶部开始清理一排即可。

假设您可以访问 Range.Offset 方法（我没有一种简单的方法可以从 Vb.Net 测试此方法，但我认为没有理由它不可用），那么您应该能够移动起始单元格:

Private Function downRange(ByRef sheet As Excel.Worksheet, ByVal rangeString As String)
    Dim range As Excel.Range
    range = sheet.Range(rangeString).Offset(1, 0) // Here's the extra piece
    range = sheet.Range(range, range.End(Excel.XlDirection.xlDown))
    Return range
End Function

Answer 3

如果要保留第一行中的公式，请不要选择删除第一行。将 "A3:F3" 传递给该函数。

对于自动填充，请使用 Range.FormulaR1C1 属性。从第一个单元格读取 FormulaR1C1 并将相同的值写入其余单元格中的 FormulaR1C1。