时间戳

首页 > 解决方案 > EPPlus 无法设置公式。相反，它会破坏公式 XML

vb.net - EPPlus 无法设置公式。相反，它会破坏公式 XML

问题描述

当我尝试通过 EPPlus 在现有 Excel 文件中设置单元格公式时，excel 文档已损坏。Excel 抛出“我们发现 'Excel.xlsx' 中的某些内容存在问题。您希望我们尽可能多地恢复吗？如果您信任此工作簿的来源，请单击是。” 对话框，然后显示“已删除记录：来自 /xl/worksheets/sheet1.xml 部分的公式”

如果我注释掉公式集操作，错误就会消失，但它会填充我在代码中任何地方都没有要求的公式。

我有一个带有几张纸的 excel 文件。在一张纸上，我想在 L 列中设置一个值，并在 I、J 和 M 列中设置公式。如果我在 Excel 中手动执行此操作，则一切正常。但是当我尝试使其自动化时，我会收到错误消息并丢失公式。

预期的公式：

第 I 列的公式：=IFNA(VLOOKUP([@[SQL Server]],SqlVersions!$C:$R,12, TRUE),"--")

J列的公式：=IFNA(VLOOKUP([@[SQL Server]],SqlVersions!$C:$S,17,TRUE),"--")

M列的公式：=IFNA(VLOOKUP([@[SQL Server]],SqlVersions!$C:$R,8,TRUE), "--")

   Dim Hdr As String = ""
   dim serverData as New List (of string) 'a list of data like A1||ServerName 
   '  SNIP   <get list data from database.> /SNIP
   Dim fInfo As New FileInfo(excelFile)
   Using ePack As New ExcelPackage(fInfo)
     Dim mySheet As ExcelWorksheet = Nothing 
     'find the sheet we need.
     For Each sheet As ExcelWorksheet In ePack.Workbook.Worksheets
         If sheet.Name = ExcelServers Then
            mySheet = sheet
            Exit For
         End If
     Next
     If IsNothing(mySheet) Then Throw New Exception("Server sheet not found.")
     For Each serverRow in ServerData
         If IsNothing(serverRow) OrElse InStr(serverRow, "||") = 0 Then Continue For 'skip "blank" rows
         Dim Cell() As String = Split(serverRow, "||")
         Dim CellAddress As String = Cell(0)   'A1..A50
         Dim CellValue As String = Trim(Cell(1))  'ServerName or table header
         Dim CellAddressCol As String = Left(CellAddress, 1) ' Will always be A
         Dim CellAddressRow As Integer = CellAddress.Substring(1)  'number, 1-50
         If CellValue = "Oracle Server" Then
            Hdr = "Ora" 'we've found a list of Oracle servers
            Continue For 'skip ahead to the next value
         ElseIf CellValue = "SQL Server" Then
            Hdr = "Sql" 'we're done with Oracle, moving on to SQL Server servers
            Continue For 'skip ahead to the next value
         ElseIf CellValue = "Non-DB Servers" Then
            Exit For 'we're done with all of our work.
         End If
         If Hdr = "Ora" Then
             If Len(CellValue) < 2 Then
                mySheet.Cells("L" & CellAddressRow).Value = ""
             Else
                mySheet.Cells("L" & CellAddressRow).Value = "P"
             End If
         ElseIf Hdr = "Sql" Then
             If Len(CellValue) < 2 Then
                mySheet.Cells("I" & CellAddressRow).Value = ""
                mySheet.Cells("J" & CellAddressRow).Value = ""
                mySheet.Cells("L" & CellAddressRow).Value = ""
                mySheet.Cells("M" & CellAddressRow).Value = ""
             ElseIf CellValue = "Cluster1" Or CellValue = "Cluster2" Then   
                mySheet.Cells("I" & CellAddressRow).Value = ""
                mySheet.Cells("J" & CellAddressRow).Value = ""
                mySheet.Cells("L" & CellAddressRow).Value = "C"
                mySheet.Cells("M" & CellAddressRow).Value = ""
             Else   'data row.
                mySheet.Cells("I" & CellAddressRow).Formula = "IFNA(VLOOKUP([@[SQL Server]],SqlVersions!$C:$R,12, TRUE),""--"")"
                mySheet.Cells("J" & CellAddressRow).Formula = "IFNA(VLOOKUP([@[SQL Server]],SqlVersions!$C:$S,17,TRUE),""--"")"
                mySheet.Cells("L" & CellAddressRow).Value = "V"
                mySheet.Cells("M" & CellAddressRow).Formula = "ifNA(VLOOKUP([@[SQL Server]],SqlVersions!$C:$R,8,FALSE),""--"")"
             End If  ' /empty row? Cluster row? other server row?
          End If ' /Oracle or SQL?
     Next
  ePack.Save()
End Using

我希望得到一系列行，其中“Oracle”之后的行在 L 列中获得“P”，“SQL Server”之后的行在 I、J 和 M 列中具有查找公式，列中带有“V” L.

如果我将 .Formula = 代码留在原处，我会收到错误消息。如果我注释掉 .Formula 行，我会得到 Oracle 行的公式“=70+65”和 SQL Server 行的“=159+799”。

最终结果应如下所示：（请注意，Oracle 行和两个标题行只是文本，不会被此代码修改。）

Oracle 服务器,,,,,,,,版本,补丁,,PV 或 C, 生命周期结束
Oracle1,,,,,,,,12.2.0.1,27937914,,P,
甲骨文,,,,,,,,12.2.0.1,27937914,,P,
,,,,,,,,
Sql Server,,,,,,,,Version,Patch,,PV or C,End of Life
Cluster1,,,,,,,,,,,C,7/14/2026
Cluster2,,,,,,,,,,C,
Sql1,,,,,,,2016 Ent 13.0.5337,SP2 CU7 Up,,V,10/12/2027
Sql2,,,,,,,2017 Ent 14.0.3223,CU16,,V,7/14/2026
[...]
sql32,,,,,,,2016 Ent 13.0.5426,SP2 CU8,,V,7/14/2016
,,,,,,,,
非数据库服务器,,,,,,,,,

但是，在我接受错误消息请求修复后，我得到的是：

Oracle 服务器,,,,,,,,版本,补丁,,PV 或 C, 生命周期结束
Oracle1,,,,,,,,12.2.0.1,27937914,,135,
甲骨文,,,,,,,,12.2.0.1,27937914,,135,
,,,,,,,,,,,135
Sql Server,,,,,,,,Version,Patch,,PV or C,End of Life
Cluster1,,,,,,,,,,958,#N/A
Cluster2,,,,,,,,,,958,#N/A
Sql1,,,,,,,,,,958,10/12/2027
Sql2,,,,,,,,,,958,7/14/2026
[...]
sql32,,,,,,,,,,958,7/14/2016
,,,,,,,,,,958,#N/A
非数据库服务器,,,,,,,,,

我根本不知道这些公式是从哪里来的，因为它们在我的代码中从来没有出现过。

编辑

这是应有的Excel 文件（清除了实际的服务器名称）。

这是该代码留下的Excel 文件（也已清理）。

标签： vb.netepplus

解决方案

当我打开save()您提供的示例时，没有进行任何编辑，它总是向我显示problem with some content错误（在带有 Office 365 和 .NET 4.7.2 的 Windows 10 上尝试过）。因此，我无法确定您具体情况的原因。

但是，我注意到 EPPlus 在 Excel 中使用表格时“翻译”公式时出现问题。作为一个简单的 vlookup 示例：

=VLOOKUP(A2,Data!A:B,2,FALSE)

在表格中使用上述公式时，它在使用 EPPlus 保存后会发生更改，现在是错误的（显示#NAME?），因为A:B:B：

=VLOOKUP(A2,Data!A:B:B,2,FALSE)

找出这是否是 EPPlus 中的错误将需要进行一些广泛的调试，该ExcelCellBase.Translate方法是一个好的开始。此外，其他人可能已经找到它（EPPlus 有一个相对较大的未解决问题列表。

抱歉，如果这没有帮助。我认为我展示的是 EPPlus 中的一个错误，但我现在知道它是否是您的问题的原因。

推荐阅读