5 Ways to Rename Excel Sheets in C

When working with spreadsheets, especially in a professional environment, the ability to manage Excel sheets effectively becomes crucial. Renaming sheets in Excel can help streamline workflows, improve organization, and make data navigation more intuitive. In this post, we'll explore five different methods to rename Excel sheets programmatically using C#.

Rename Excel Sheets with EPPlus Library

The EPPlus library is a very popular choice for Excel manipulation in .NET applications due to its simplicity and wide range of features. Here’s how you can rename sheets using EPPlus:

• Install EPPlus via NuGet Package Manager.
• Create or open an Excel workbook.
• Select the worksheet you want to rename and use the Rename method.

using OfficeOpenXml;

ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var package = new ExcelPackage(new FileInfo("sample.xlsx")))
{
    var workbook = package.Workbook;
    workbook.Worksheets["Sheet1"].Name = "NewSheetName";
    package.Save();
}

💡 Note: Ensure you have the appropriate license for commercial use of EPPlus, as it is free for non-commercial purposes.

Using Interop Services

Microsoft Office Interop Services can also be used to automate Excel tasks from within C# applications. Here is how you can rename sheets with Interop:

• Include necessary Interop references.
• Create an instance of Excel Application.
• Open the workbook, locate the sheet, and rename it.

using Excel = Microsoft.Office.Interop.Excel;

var excelApp = new Excel.Application();
var workbook = excelApp.Workbooks.Open(@"C:\sample.xlsx");
var sheet = workbook.Sheets["Sheet1"];
sheet.Name = "RenamedSheet";
workbook.Save();
workbook.Close();
excelApp.Quit();

With ClosedXML Library

ClosedXML offers another straightforward option for Excel file manipulation in .NET. Here’s how you rename sheets:

• Install ClosedXML through NuGet.
• Load or create an Excel file.
• Navigate to the desired worksheet and rename it.

using ClosedXML.Excel;

var workbook = new XLWorkbook("sample.xlsx");
workbook.Worksheet("Sheet1").Name = "RenamedSheet";
workbook.Save();

Using OpenXML SDK

OpenXML SDK provides low-level access to Excel files. Though it’s more complex, it’s versatile for those needing fine control over the document structure:

• Install Open XML SDK via NuGet.
• Load the workbook.
• Find and rename the sheet using XML manipulation.

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

string filePath = "sample.xlsx";
using (SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, true))
{
    WorkbookPart workbookPart = document.WorkbookPart;
    WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
    Sheet sheet = workbookPart.Workbook.Sheets.Elements<Sheet>().First();
    sheet.Name = "NewName";
    workbookPart.Workbook.Save();
}

Manual Approach without External Libraries

In some cases, using external libraries might be unnecessary or undesirable. Here’s how you can rename sheets manually:

• Create or modify an Excel file directly with C# without dependencies.
• Modify the workbook’s XML to change the sheet name.

using System.IO;
using System.Xml;

string filePath = "sample.xlsx";
using (var zip = new ZipFile(filePath))
{
    ZipEntry entry = zip.GetEntry("xl/workbook.xml");
    using (var stream = entry.Open())
    {
        XmlDocument xmlDoc = new XmlDocument();
        xmlDoc.Load(stream);
        XmlNamespaceManager nsmgr = new XmlNamespaceManager(xmlDoc.NameTable);
        nsmgr.AddNamespace("x", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
        XmlNode sheetNode = xmlDoc.SelectSingleNode("//x:sheet[@name='Sheet1']", nsmgr);
        if (sheetNode != null)
        {
            sheetNode.Attributes["name"].Value = "NewSheetName";
        }
        stream.Close();
        using (var newStream = new MemoryStream())
        {
            xmlDoc.Save(newStream);
            newStream.Seek(0, SeekOrigin.Begin);
            entry.PutNextEntry();
            newStream.CopyTo(zip.GetOutputStream(entry));
        }
    }
    zip.Close();
}

Summing up the various methods to rename Excel sheets in C#, each offers its own advantages:

• EPPlus and ClosedXML are user-friendly, particularly for developers new to Excel manipulation.
• Interop Services are useful if the application environment supports running Excel directly.
• OpenXML SDK provides granular control but requires deeper XML knowledge.
• Manual XML manipulation can be an option when avoiding external dependencies is a priority.

Selecting the appropriate method depends on your project's complexity, performance needs, licensing constraints, and your comfort level with manipulating XML. With these tools, you can programmatically rename sheets, thereby optimizing your workflow and data management in Excel files.

Can I use these methods to rename sheets in all versions of Excel?

+

Yes, the libraries and methods discussed work with Excel files (.xlsx) compatible with Excel 2007 and later versions.

What if I need to rename multiple sheets at once?

+

All methods can be extended to rename multiple sheets by iterating through the sheets collection and applying the rename operation to each one.

Are there any performance concerns with these methods?

+

Manual XML manipulation can be the slowest due to the overhead of handling zip files and XML. EPPlus and ClosedXML are generally faster for simple operations, while OpenXML SDK might be slower but offers more control.