Renaming a worksheet does not appear to update cell references to the renamed sheet.
In the code below (a modified version of the "Sample 1" EPPlus sample code), the reference in cell
does not get updated to
sheet is renamed to
, resulting in an undesired
Steps to reproduce:
- Download the latest version of EPPlus.
- Replace the RunSample1 method in SampleApp\Sample1.cs with the following RunSample1 method:
/// Modified version of "Sample 1" that demonstrates an error
/// with cross-sheet references and sheet renaming.
public static string RunSample1(DirectoryInfo outputDir)
FileInfo newFile = new FileInfo(outputDir.FullName + @"\sheetRenameBugReproduction.xlsx");
newFile.Delete(); // ensures we create a new workbook
newFile = new FileInfo(outputDir.FullName + @"\sheetRenameBugReproduction.xlsx");
using (ExcelPackage package = new ExcelPackage(newFile))
OfficeOpenXml.ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Inventory");
// Add some text to cell C1.
worksheet.Cells[2, 3].Value = "Hello World!";
// Cell references without a sheet specified still work as expected.
worksheet.Cells[3, 3].Formula = "C2";
// BUG: Cells that reference a specific sheet have a value of #REF! after
// the referenced sheet is renamed.
worksheet.Cells[4, 3].Formula = "Inventory!C2";
// Cell references that are manually updated to the correct sheet work correctly.
worksheet.Cells[5, 3].Formula = @"'Sheet 1'!C2";
// Rename sheet "Inventory" to "Sheet 1".
worksheet.Name = "Sheet 1";
// Add some descriptive text to the workbook
worksheet.Cells[2, 4].Value = "Raw Text";
worksheet.Cells[3, 4].Value = "Local Reference, Sheet Unspecified";
worksheet.Cells[4, 4].Value = "Local Reference, Sheet Specified Before Rename";
worksheet.Cells[5, 4].Value = "Local Reference, Sheet Specified After Rename";
- Configure the EPPlusSamples project to save files to a valid directory by updating the
outputDir variable in the
Main method of Sample_Main.cs to an existing directory.
- Build and run the EPPlusSamples project.
- Open the
sheetRenameBugReproduction.xlsx file that is generated.
- Observe that cell C4 possesses a
- Cell C4 should possess a value of "Hello World!".