2
Vote

Renaming a sheet does not update cross-sheet references (sample code + steps to reproduce)

description

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 C4 to Inventory!C2 does not get updated to 'Sheet 1'!C2 when the Inventory sheet is renamed to Sheet 1, resulting in an undesired #REF! error.

Steps to reproduce:
  1. Download the latest version of EPPlus.
  2. Replace the RunSample1 method in SampleApp\Sample1.cs with the following RunSample1 method:
    /// <summary>
    /// Modified version of "Sample 1" that demonstrates an error 
    /// with cross-sheet references and sheet renaming.
    /// </summary>
    public static string RunSample1(DirectoryInfo outputDir)
    {
        FileInfo newFile = new FileInfo(outputDir.FullName + @"\sheetRenameBugReproduction.xlsx");
        if (newFile.Exists)
        {
            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";
            package.Save();
        }
        return newFile.FullName;
    }
  3. 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.
  4. Build and run the EPPlusSamples project.
  5. Open the sheetRenameBugReproduction.xlsx file that is generated.
  6. Observe that cell C4 possesses a #REF! value.
    • Cell C4 should possess a value of "Hello World!".

comments

zippy1981 wrote Aug 26, 2015 at 2:38 PM

This will probably take a lot of work to fix.

ValeraT1982 wrote Jun 22, 2016 at 11:52 PM

Is it possible to update all references to worksheet manually?

bibujakera wrote Mar 17 at 7:16 AM

Good day,
Is there a solution for this issue yet?

Regards,
Bibujakera

bibujakera wrote Mar 17 at 7:17 AM

Good day,
Is there a solution for this issue yet?