Header/Footers

[dbronson4672]

EPPlus usage

Commercial use (I have a commercial license)

Environment

Linux

Epplus version

8.6.3

Spreadsheet application

Excel 2007

Description

EPPlus: copying a worksheet with a header/footer breaks it

Summary

ExcelWorksheets.Add(string name, ExcelWorksheet sourceWorksheet) — copying a worksheet from one
ExcelPackage into another — corrupts the source worksheet's header/footer. Depending on one incidental
detail (whether anything reads a HeaderFooter property on the source worksheet before the copy runs), you
get one of two symptoms:

• Throws. Worksheets.Add throws a NullReferenceException from inside EPPlus's own
  WorksheetCopyHelper.CopyHeaderFooterPictures.
• Silent data loss. Worksheets.Add succeeds with no error at all, but the header/footer content is
  gone once the target package is saved and reopened.

Same call site both times — which symptom you get is not a property of the workbook, it's whatever
incidental code elsewhere happened to touch HeaderFooter first.

Environment

• EPPlus version: 8.6.3
• .NET: 9.0, linux-x64 (provided.al2023)

Reproduction — throws

$xlsxFile = "/path/to/any-workbook-with-a-footer.xlsx"
$epplusDll = "/opt/EPPlus/EPPlus.dll"

$ErrorActionPreference = "Stop"
Add-Type -Path $epplusDll
[OfficeOpenXml.ExcelPackage]::License.SetNonCommercialPersonal("Repro")

$source = [OfficeOpenXml.ExcelPackage]::new([IO.FileInfo]::new($xlsxFile))
$sourceSheet = $source.Workbook.Worksheets[0]

$sourceSheet.HeaderFooter.OddFooter.CenteredText | Out-Null   # <-- the trigger

$target = [OfficeOpenXml.ExcelPackage]::new()
$target.Workbook.Worksheets.Add("Copied", $sourceSheet)

System.Management.Automation.MethodInvocationException: Exception calling "Add" with "2" argument(s): "Object reference not set to an instance of an object."
 ---> System.NullReferenceException: Object reference not set to an instance of an object.
   at OfficeOpenXml.Core.Worksheet.WorksheetCopyHelper.CopyHeaderFooterPictures(ExcelWorksheet Copy, ExcelWorksheet added)
   at OfficeOpenXml.Core.Worksheet.WorksheetCopyHelper.Copy(ExcelWorksheets targetWorksheets, String name, ExcelWorksheet sourceWorksheet)
   at OfficeOpenXml.ExcelWorksheets.Add(String Name, ExcelWorksheet Copy)

Reproduction — silent data loss

Same copy, but nothing reads HeaderFooter on the source first:

$source = [OfficeOpenXml.ExcelPackage]::new([IO.FileInfo]::new($xlsxFile))
$sourceSheet = $source.Workbook.Worksheets[0]
# no HeaderFooter read here

$target = [OfficeOpenXml.ExcelPackage]::new()
$copiedSheet = $target.Workbook.Worksheets.Add("Copied", $sourceSheet)
Write-Host "Right after copy: [$($copiedSheet.HeaderFooter.OddFooter.CenteredText)]"

$target.SaveAs([IO.FileInfo]::new("/tmp/output.xlsx"))
$target.Dispose(); $source.Dispose()

$reopen = [OfficeOpenXml.ExcelPackage]::new([IO.FileInfo]::new("/tmp/output.xlsx"))
Write-Host "After save+reopen: [$($reopen.Workbook.Worksheets["Copied"].HeaderFooter.OddFooter.CenteredText)]"

Right after copy: [&C&"-,Bold"{FORMID}-{VERSION}&"-,Regular"]
After save+reopen: []

Worksheets.Add returns normally, the header/footer looks correct immediately after the copy — then
disappears once the file is saved and reopened. No error at any point.

What rules this out as anything else

• Very similar to a previous bug I opened awhile back : EPPlusSoftware/EPPlus#1146.

Point $xlsxFile at any .xlsx with a header/footer picture, and $epplusDll at your EPPlus.dll (its
directory must also contain its dependency DLLs — .NET auto-resolves them from that same folder).

[swmal]

Thanks for reporting! See the above fix for this issue. Will be included in next release of EPPlus.

[dbronson4672]

FYI - the demo code is incorrect but I still have issue , I will update better demo code fo this issue later this morning.

[dbronson4672]

Copy/save: header/footer text is lost after Worksheets.Add + SaveAs

Behavior

ExcelWorksheets.Add(string name, ExcelWorksheet sourceWorksheet) copies a worksheet whose header/footer
contains a picture. The copy itself does not throw and the header/footer text reads back correctly
in memory right after the copy. But once the target package is saved (SaveAs) and reopened, the
header/footer text is gone.

Confirmed on EPPlus 8.6.3.0, against both sample-workbook-with-footer-image.xlsx (attached) and a real
production template (data/forms/Common/SCHEDULE.xlsx):

Immediately after copy: [&CSample footer text -- &G]
After SaveAs + reopen:  []

Reproduce

$xlsxFile = "sample-workbook-with-footer-image.xlsx"
Add-Type -Path "/opt/EPPlus/EPPlus.dll"
[OfficeOpenXml.ExcelPackage]::License.SetNonCommercialPersonal("Repro")

$src = [OfficeOpenXml.ExcelPackage]::new([IO.FileInfo]::new($xlsxFile))
Write-Host "Sheet count: $($src.Workbook.Worksheets.Count)"   # confirms the sheet is really there
$srcSheet = $src.Workbook.Worksheets[0]

$target = [OfficeOpenXml.ExcelPackage]::new()
$copied = $target.Workbook.Worksheets.Add("Copied", $srcSheet)
Write-Host "Immediately after copy: [$($copied.HeaderFooter.OddFooter.CenteredText)]"

$target.SaveAs([IO.FileInfo]::new("/tmp/output.xlsx"))
$target.Dispose(); $src.Dispose()

$reopen = [OfficeOpenXml.ExcelPackage]::new([IO.FileInfo]::new("/tmp/output.xlsx"))
Write-Host "After SaveAs + reopen:  [$($reopen.Workbook.Worksheets['Copied'].HeaderFooter.OddFooter.CenteredText)]"

Sheet count: 1
Immediately after copy: [&CSample footer text -- &G]
After SaveAs + reopen:  []

Related

EPPlusSoftware/EPPlus#1146 — describes image loss
on cross-package sheet copy, not the text loss confirmed here.

[dbronson4672]

Thanks diddn't see the comment, all good