WorkSheet.Add Errors occur when the template Sheet is used, such as formulas, definition names, conditional formats, and external workbook links

[minren118]

EPPlus usage

Noncommercial use

Environment

Windows

Epplus version

7.4.2.1079

Spreadsheet application

Excel365

Description

When I use the WorkSheet.Add interface, I find that the definition name is copied, missing the workbook name is not copied, and it also raises an error if there is an external workbook link.

I have two workbooks, src1 and src2. In sheet1 of src1, there are formulas that reference src1's sheet2 worksheets and reference the workbook names. In sheet1 of src1, there are conditional format cells that reference src1's sheet2 worksheets. In the dst workbook, Using the WorkSheets.Add method, src1's sheet1 was passed in as a template and ended up with an error in the dst workbook's newSht.

            using (ExcelPackage excelPackage = new ExcelPackage("src1.xlsx"))
            {
                var copiedSht=excelPackage.Workbook.Worksheets[0];
                using (ExcelPackage newPackage = new ExcelPackage("dst.xlsx"))
                {
                    newPackage.Workbook.Worksheets.Add("newSht", copiedSht);
                    newPackage.Save();
                }
            }

dst.xlsx src1.xlsx src2.xlsx

[JanKallman]

When copying a worksheet from one workbook to another EPPlus will not copy referenced defined names in the workbook scope of the source workbook. You will have to copy them yourself or if they are not present in the destination workbook. Copying them could be an enhancement for the future.

EPPlus will not update formulas that reference other worksheets within the source workbook. These formulas will assume the worksheet is within the destination workbook. Changing this behavior to create an external like link back to the source workbook. would be a breaking change, but we might consider adding this an option in the future.