By CaM


2019-08-12 15:21:10 8 Comments

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   <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

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.

1 comments

@JorisJ1 2019-08-15 18:33:08

When I open and save() the sample you provided, without doing any edit whatsoever, it always shows me the problem with some content error (tried on Windows 10 with Office 365 and .NET 4.7.2). Because of this I cannot be certain of the cause in your specific situation.

However, I notice EPPlus has a problem when 'translating' formulae when using a Table within Excel. As an example a simple vlookup:

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

When the above formula is used in a Table, it is changed after being saved with EPPlus and is now erroneous (showing #NAME?) because of the A:B:B:

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

Finding out if this is a bug in EPPlus will require some extensive debugging, with the ExcelCellBase.Translate method being a good start. Also, someone else might already have found it (EPPlus has an relatively large list of open issues.

Sorry if this does not help. I think what I showed is a bug in EPPlus, but I do now know if it it the cause for your problem.

Related Questions

Sponsored Content

5 Answered Questions

[SOLVED] How to set Column Type when using EPPlus

  • 2012-03-25 10:53:02
  • Michael
  • 51972 View
  • 61 Score
  • 5 Answer
  • Tags:   epplus

1 Answered Questions

Use names of named ranges in formula in Epplus

  • 2018-09-08 15:10:41
  • Time Rider
  • 258 View
  • 0 Score
  • 1 Answer
  • Tags:   epplus

1 Answered Questions

3 Answered Questions

[SOLVED] How to set XLSX cell width with EPPlus?

  • 2012-02-01 12:55:25
  • themis
  • 72501 View
  • 66 Score
  • 3 Answer
  • Tags:   c# .net epplus

1 Answered Questions

[SOLVED] EPPlus doesn't properly range a formula calling on a different worksheet

  • 2017-11-06 20:11:04
  • Isaac Little
  • 1370 View
  • 1 Score
  • 1 Answer
  • Tags:   c# .net excel epplus

1 Answered Questions

[SOLVED] Epplus formula error

  • 2016-10-14 07:23:43
  • Pedro
  • 575 View
  • 1 Score
  • 1 Answer
  • Tags:   c# vb.net epplus

1 Answered Questions

[SOLVED] Setting *both* Value & Formula in EPPlus

  • 2014-01-06 09:38:52
  • Andrew Backer
  • 4162 View
  • 2 Score
  • 1 Answer
  • Tags:   excel epplus

1 Answered Questions

[SOLVED] EPPlus formula not running

  • 2013-08-01 13:30:59
  • LRFalk01
  • 3908 View
  • 4 Score
  • 1 Answer
  • Tags:   c# excel epplus

1 Answered Questions

[SOLVED] Locking Cells that have formulas using EPPlus

  • 2013-02-26 12:28:13
  • user2111042
  • 2020 View
  • 1 Score
  • 1 Answer
  • Tags:   epplus

Sponsored Content