EPPlus fails to set formulas. Instead, it corrupts the formula XML

Question

When I try to set cell formulas in an existing Excel file via EPPlus, the excel document is corrupted. Excel throws "We found a problem with some content in 'Excel.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes." dialog box, then says, "Removed Records: Formula from /xl/worksheets/sheet1.xml part"

If I comment out the formula set operations, the error goes away, but it fills in with formulas I didn't ask for anywhere in my code.

I have an excel file with several sheets. On one sheet, I want to set a value in column L and set formulas in columns I, J, and M. If I do this manually within Excel, everything works without error. But when I try to automate it, I Get the error messages and lose the formulas.

intended formulas:

Formula for column I: =IFNA(VLOOKUP([@[SQL Server]],SqlVersions!$C:$R,12, TRUE),"--")

Formula for column J: =IFNA(VLOOKUP([@[SQL Server]],SqlVersions!$C:$S,17,TRUE),"--")

Formula for column 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    /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

I expect to get a series of rows where the rows after "Oracle" get a "P" in column L and the rows after "SQL Server" have lookup formulas in columns I, J, and M, with a "V" in column L.

If I leave the .Formula = code in place, I get the errors. If I comment out the .Formula lines, I instead get the formula "=70+65" for Oracle rows and "=159+799" for SQL Server rows.

The end result should look something like this: (Note that the Oracle rows and two header rows are just text and aren't modified by this code.)

Oracle Server,,,,,,,,Version,Patch,,P V or C, End of Life
Oracle1,,,,,,,,12.2.0.1,27937914,,P,
Oracle,,,,,,,,12.2.0.1,27937914,,P,
,,,,,,,,
Sql Server,,,,,,,,Version,Patch,,P V 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
,,,,,,,,
Non-DB Servers,,,,,,,,

But what I'm getting, after I accept the error message request to repair is:

Oracle Server,,,,,,,,Version,Patch,,P V or C, End of Life
Oracle1,,,,,,,,12.2.0.1,27937914,,135,
Oracle,,,,,,,,12.2.0.1,27937914,,135,
,,,,,,,,,,,135
Sql Server,,,,,,,,Version,Patch,,P V 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
Non-DB Servers,,,,,,,,

I have no idea where those formulas are coming from at all, as they are nowhere in my code, ever.

Edit

Here is the Excel file (scrubbed of actual server names) as it should be.

And here is the Excel file (also scrubbed) as this code leaves it.

EPPlus fails to set formulas. Instead, it corrupts the formula XML

Answers (1)

Related Questions