When copying a selected worksheet, the new worksheet copy is also selected

[gdereese]

EPPlus usage

Commercial use (I have a commercial license)

Environment

Windows

Epplus version

7.2.2

Spreadsheet application

Excel

Description

Scenario

1. An Excel workbook file named Template.xlsx exists that contains 2 sheets: Sheet 1 and Sheet 2. Sheet 1 contains a chart.
2. Program loads the workbook from file into an ExcelPackage.
3. Program copies Sheet 1 and names the new sheet copy Sheet 1a.
4. Program saves the in-memory workbook object as a new file on disk named Report.xlsx.
5. User opens Report.xlsx in Excel on the machine.
6. The tabs for both Sheet 1 and Sheet 2 are active in the workbook, indicating that both sheets are selected. When multiple sheets are selected, user cannot interact with the charts on Sheet 1 or Sheet 1a until the user changes their tab/sheet selection to a single sheet (i.e. click the tab for Sheet 2). After returning to Sheet 1 or Sheet 1a, the chart can be interacted with.

Expected behavior

When copying a single worksheet from within Excel, the new sheet copy is NOT selected; the original sheet being copied remains the selected sheet.

Workaround

There are 2 workarounds:

1. If the program copying the selected worksheet ensures only a single worksheet is selected before saving the new workbook, the issue does not appear. For example, by forcing selection of the first sheet in the workbook:

workbook.Worksheets[0].Select();

2. In the Excel workbook being used as the template, ensure that any sheet that will be copied by the program IS NOT selected, then re-save the file.

Sample code

The following C# console program code reproduces the issue:

using System;
using System.IO;
using OfficeOpenXml;

// load workbook from template
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using var package = new ExcelPackage("./Template.xlsx");
var workbook = package.Workbook;

// copy sheet
var sheetCopy = workbook.Worksheets.Copy("Sheet 1", "Sheet 1a");

// save workbook to new file
var outputPath = "./Report.xlsx";
package.SaveAs(outputPath);
Console.WriteLine($"Excel report saved to {Path.GetFullPath(outputPath)}");

Sample Excel template

Template.xlsx

[JanKallman]

EPPlus copies the workbook internal worksheet xml, so the selected flag will also be copied to the new worksheet. I can agree that this is likely not the intended behaviour, so we will change this. We will look at adding an argument to the copy worksheet function to either keep the selection or set the new worksheet as the selected. As this will be a breaking change, the change will go into 7.4 or 8.0

[JanKallman]

Fix added in EPPlus 7.3.1 - Adds a parameter to ExcelWorksheet.Add to select the copied worksheet or leave the selection as is.