How to Retrieve Excel Sheet Names Using C

If you're dealing with Microsoft Excel files in your application and need to programmatically access or list the sheet names, you'll find that while Excel's GUI makes it straightforward, the backend operations require a bit more finesse. This blog post will guide you through several methods to retrieve sheet names in an Excel workbook using C#. We'll explore different approaches suited for various scenarios, from using libraries to direct COM interop.

Why Retrieve Sheet Names?

Before diving into the coding specifics, understanding why someone might need to retrieve Excel sheet names is essential:

• Data Analysis: Knowing sheet names allows developers to automate data processing or extraction from specific sheets.
• Reporting: When automating report generation, you might need to dynamically select sheets based on certain criteria.
• Integration: For applications integrating with Excel, knowing the sheet names upfront can facilitate seamless data transfer.

Using EPPlus Library

EPPlus is a robust, feature-rich .NET library for managing Excel files without Excel installed. Here’s how you can retrieve sheet names using EPPlus:

using OfficeOpenXml;

// Open the workbook
FileInfo file = new FileInfo(“path/to/yourfile.xlsx”);
using (ExcelPackage package = new ExcelPackage(file))
{
    // Access the workbook
    ExcelWorkbook workbook = package.Workbook;

// Iterate through worksheets
foreach (ExcelWorksheet worksheet in workbook.Worksheets)
{
    Console.WriteLine(worksheet.Name);
}


}

💡 Note: Remember to add EPPlus to your project via NuGet for this code to work.

With Interop Services

If you need to interact with Excel in a live environment, using COM interop services can be beneficial. Here’s how to retrieve sheet names:

using Excel = Microsoft.Office.Interop.Excel;

// Create an instance of Excel
Excel.Application excelApp = new Excel.Application();
Excel.Workbook workbook = excelApp.Workbooks.Open(“path/to/yourfile.xlsx”);

// Loop through each worksheet
foreach (Excel.Worksheet sheet in workbook.Worksheets)
{
    Console.WriteLine(sheet.Name);
}

// Clean up
workbook.Close();
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);

💡 Note: Using Interop Services requires Excel to be installed on the user's machine.

Using OleDb to Query Excel File

For applications where you do not want to install Excel or EPPlus, you can use OleDb to read from Excel files like a database:

using System.Data;
using System.Data.OleDb;

string connectionString = @“Provider=Microsoft.ACE.OLEDB.12.0;Data Source=path/to/yourfile.xlsx;Extended Properties=‘Excel 12.0 Xml;HDR=YES;’”;

using (OleDbConnection conn = new OleDbConnection(connectionString))
{
    conn.Open();
    DataTable sheets = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    if (sheets != null)
    {
        foreach (DataRow row in sheets.Rows)
        {
            Console.WriteLine(row[“TABLE_NAME”].ToString().Replace(“$”, “”));
        }
    }
    conn.Close();
}

💡 Note: This method assumes the Excel file is in the .xlsx format, and you'll need the Microsoft.ACE.OLEDB provider.

Direct File Reading

If you’re dealing with Excel files in older formats like .xls, you might use OpenXML SDK or directly read the binary file. Here’s how to list sheets using C# with direct file reading:

using System.IO;

// Load file into memory
byte[] xlsBytes = File.ReadAllBytes(“path/to/yourfile.xls”);

// Locate Workbook Globals sub-stream in BIFF8
int workbookGlobalStart = 0x20C + 4; // BOF Record + Record Header Size
for (int i = workbookGlobalStart; i < xlsBytes.Length;)
{
    // BOF record
    if (xlsBytes[i] == 0x08 && xlsBytes[i + 1] == 0x00)
    {
        ushort recordType = BitConverter.ToUInt16(xlsBytes, i);
        ushort recordSize = BitConverter.ToUInt16(xlsBytes, i + 2);

    // Move to next record
    i += recordSize + 4; // +4 for Record header
    if (recordType == 0x0015) // BoundSheet8 Record
    {
        string sheetName = GetStringFromBIFF8(xlsBytes, i, recordSize);
        Console.WriteLine(sheetName);
    }
}
else
{
    break;
}


}

// Helper method to extract sheet names from BIFF8
private string GetStringFromBIFF8(byte[] data, int offset, int size)
{
    // Implement your string extraction logic here
    // This is a simplified version and might need tweaking for edge cases
    byte[] stringBytes = new byte[size];
    Array.Copy(data, offset, stringBytes, 0, size);
    return System.Text.Encoding.Unicode.GetString(stringBytes).TrimEnd(‘\0’);
}

In conclusion, retrieving sheet names from an Excel file using C# can be achieved through various methods tailored to different environments or user requirements. Whether you’re automating Excel tasks, integrating with systems, or simply need to list sheets for reference, the choice of method depends on factors like Excel installation, file format, and system compatibility. Each approach has its pros and cons:

• EPPlus and Interop Services are great when you have control over the environment, allowing for deep Excel interaction.
• OleDb provides a lightweight, Excel-independent way to query the workbook structure.
• Direct file reading is complex but versatile, suitable for accessing old Excel files or when fine control over the file structure is needed.

Remember to choose the method that best aligns with your project’s needs, considering installation dependencies, performance, and the complexity of your task.

Can I retrieve sheet names from a protected workbook?

+

Yes, if the workbook is password-protected, you can still retrieve the sheet names using methods like EPPlus or Interop Services, provided you supply the password in your code. However, accessing protected sheets might require additional permissions or might not be possible if the protection restricts this operation.

Do I need Microsoft Excel installed to read Excel files in C#?

+

No, not necessarily. Libraries like EPPlus and OleDb allow you to work with Excel files without Excel installed. However, for COM Interop, you do need Excel on the machine running the code.

What are the limitations when using OleDb to query Excel files?

+

OleDb provides a way to read Excel files as databases, but it has limitations:

• Assumes headers for data reading which might not always be the case.
• Doesn’t support all Excel features like formulas or complex data types.
• Performance can be slower with large datasets.

How can I handle .xlsx files with different Excel versions?

+

Using libraries like EPPlus, you can handle .xlsx files without worrying about the Excel version, as long as your project targets a .NET framework version that supports the library. Ensure your library version supports the .xlsx file format.