Conditional formatting getting lost while copying sheets...

[ykgw-shashikumar]

EPPlus usage

Commercial use (I have a commercial license)

Environment

Windows 11, Ubuntu 16

Epplus version

7.1.2, 7.5.0, 8.6.0, Latest also tested with

Spreadsheet application

Excel from "C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE"

Description

Hi EPPlus Team,

We are writing to report a bug related to conditional formatting (CF) being lost when copying worksheets using the Worksheets.Copy() API.

PROBLEM SUMMARY
When a workbook containing multiple sheets is opened and Worksheets.Copy() is called to duplicate a sheet, the conditional formatting on the DIP_SWITCH_SETTINGS sheet is lost in the copied sheet. The same operation works correctly on other sheets in the same workbook.
Additional Info:

1. When the template has only 1 sheet(DIP_SWITCH_SETTINGS), then in the final report the conditional formatting is maintained.
2. Similar bug was raised earlier . Conditional formatting not getting copied with Copy funciton · Issue #2381 · EPPlusSoftware/EPPlus

CODE TO REPRODUCE
for (int i = 1; i <= copyCount; i++)
{
package.Workbook.Worksheets.Copy(
sourceSheet.Name, $"{sheetName}_{i}");
}

We have a production release nearing and this is a blocking issue for us. We would greatly appreciate a patch release or workaround guidance at the earliest.

Please find the template file and the copied output file attached for your reference.

Thank you for your time and support.

Output_EPPlus.xlsx
template.xlsx

[OssianEPPlus]

This is a bug and we are working on a fix.

Note that much like the similar bug #2381 this bug has nothing to do with copying nor having more than one worksheet. Simply re-saving your file in Epplus triggers the bug. And it is once again only the StyleIds that are missmatched.

If possible, next time you report an issue it would be highly appreciated if you could provide a Minimal, reproducible case..

Your example in this case contains 345 DxfIds for conditionalformattings and the reporting of the issue is somewhat misleading in the same way as in #2381. Since the ConditionalFormattings in all copied sheets still exist (You can see this in your Output file, enter DIP_SWITCH_SETTINGS_Copy1, select all cells, Conditional-Formatting > Manage Rules. You'll see that all conditional formattings remain but their formatting settings are incorrect.)

These aspects would have been clear and the fix could have been made considerably faster had the issue been isolated from the start. We understand that this can be difficult in some cases and will of course try our best to figure out what's going wrong either way but in the future it would benifit us both to have the issue Isolated.

[ykgw-shashikumar]

Sure we will provide steps to repoduce. Hope your team working on the bug and it is not closed?

[OssianEPPlus]

Correct. There was a minor misshap with githubs "close with comment button" while I was in the middle of writing our response. I have also edited and finalized the initial response.

[OssianEPPlus]

We've made a fix for this bug and it's under review. We will let you know when it's available in our Develop Branch and when it is released.

[ykgw-shashikumar]

Good to know. Thanks a lot. Just as a context, in our tool, we take an Template.xlsx(multiple sheets) and generate data specific Configuration.xslx(with many more sheets). Thus input Template sheets are repeated to multiple output sheets. The issue identified was what we have in Template seems to be missing in the generated/created Configuration.xlsx sheets.

Anyways, as soon as we receive word from you, we will do testing with the fix.

Thanks

[OssianEPPlus]

Good info to have! Explains why it's been discovered when copying.

The fix is now available for testing using our NuGet Develop Feed