How to Open Excel Files in C#

What Are the Key Features of IronXL?

• Open, read, and search data in XLS/XLSX/CSV/TSV formats using LoadSpreadsheets.
• Export Excel Worksheets to multiple formats with Save & Export.
• Encrypt and decrypt files with passwords using secure features.
• Work with Excel sheets as DataSet and DataTable objects through DataSet integration.
• Excel formulas recalculate automatically, supporting Math Functions.
• Edit spreadsheet data with intuitive range syntax like WorkSheet["A1:B10"].
• Sort Cell Ranges, Columns, and Rows.
• Style Cells with Font, Background, Border, Alignment, and Number formats.

What Do I Need Before Starting?

To use IronXL in C# applications, install the following components on your local computer:

1. Visual Studio - The official IDE for developing C# .NET applications. You can download and install Visual Studio from the Microsoft website. You can also use JetBrains ReSharper & Rider. For additional setup guidance, refer to the Get Started Overview.
2. IronXL - The Excel library that helps work with Excel sheets in C#. It must be installed in your C# application before using it. You can download it from the NuGet website or from Manage NuGet packages in Visual Studio. You can also download the .NET Excel DLL file directly. For licensing implementation, see Using License Keys.

Which Namespaces Should I Import?

Once Visual Studio and IronXL are installed, add the necessary IronXL namespaces by including the following line at the top of your C# file:

// Add reference to the IronXL library
using IronXL;

// Add reference to the IronXL library
using IronXL;

For working with specific Excel formats or advanced features, you might also need:

using IronXL.Formatting;  // For cell styling
using IronXL.Drawing;     // For images and charts
using System.Data;        // For DataSet/DataTable operations

using IronXL.Formatting;  // For cell styling
using IronXL.Drawing;     // For images and charts
using System.Data;        // For DataSet/DataTable operations

How Do I Load an Existing Excel File?

Excel files, also known as workbooks, consist of multiple worksheets, each containing cell values. To open and read an Excel file, load it using the WorkBook class's Load method. The LoadSpreadsheets functionality supports various formats.

// Supported Excel spreadsheet formats for reading include: XLSX, XLS, CSV, and TSV
WorkBook workbook = WorkBook.Load("test.xlsx");

// You can also load from streams for web applications
// using (var stream = File.OpenRead("test.xlsx"))
// {
//     WorkBook workbook = WorkBook.Load(stream);
// }

// Supported Excel spreadsheet formats for reading include: XLSX, XLS, CSV, and TSV
WorkBook workbook = WorkBook.Load("test.xlsx");

// You can also load from streams for web applications
// using (var stream = File.OpenRead("test.xlsx"))
// {
//     WorkBook workbook = WorkBook.Load(stream);
// }

This initializes the workbook as a WorkBook instance. To open a specific WorkSheet, retrieve it from the WorkSheets collection. The Manage Worksheet guide provides more details on worksheet operations:

// Access the first worksheet in the workbook
WorkSheet sheet = workbook.WorkSheets.First();

// Alternative ways to access worksheets
WorkSheet sheetByIndex = workbook.WorkSheets[0];  // By index
WorkSheet sheetByName = workbook.GetWorkSheet("Sheet1");  // By name

// Access the first worksheet in the workbook
WorkSheet sheet = workbook.WorkSheets.First();

// Alternative ways to access worksheets
WorkSheet sheetByIndex = workbook.WorkSheets[0];  // By index
WorkSheet sheetByName = workbook.GetWorkSheet("Sheet1");  // By name

This accesses the first sheet in the Excel file, ready for reading and writing.

Excel File

How Do I Read Data from Excel Cells?

Once the Excel file is opened, it's ready for reading data. Reading data from Excel files in C# using IronXL is straightforward. You can read cell values by specifying the cell reference using the Select Range functionality.

The following code retrieves the value of a cell:

// Select the cell using Excel notation and retrieve its integer value
int cellValue = sheet["C2"].IntValue;

// You can also retrieve values in different formats
string textValue = sheet["C2"].StringValue;
decimal decimalValue = sheet["C2"].DecimalValue;
DateTime dateValue = sheet["C2"].DateTimeValue;
bool boolValue = sheet["C2"].BoolValue;

// Display the value in the console
Console.WriteLine($"Cell C2 contains: {cellValue}");

// Check if cell is empty before reading
if (!sheet["C2"].IsEmpty)
{
    Console.WriteLine($"Cell value: {sheet["C2"].Value}");
}

// Select the cell using Excel notation and retrieve its integer value
int cellValue = sheet["C2"].IntValue;

// You can also retrieve values in different formats
string textValue = sheet["C2"].StringValue;
decimal decimalValue = sheet["C2"].DecimalValue;
DateTime dateValue = sheet["C2"].DateTimeValue;
bool boolValue = sheet["C2"].BoolValue;

// Display the value in the console
Console.WriteLine($"Cell C2 contains: {cellValue}");

// Check if cell is empty before reading
if (!sheet["C2"].IsEmpty)
{
    Console.WriteLine($"Cell value: {sheet["C2"].Value}");
}

The output is as follows:

Read Excel

To read data from a range of cells, use a loop to iterate through the specified range. The Select Excel Range example provides more patterns:

// Iterate through a range of cells and display their address and text content
foreach (var cell in sheet["A2:A6"])
{
    Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
}

// Read an entire column
foreach (var cell in sheet.GetColumn(0))  // Column A
{
    if (!cell.IsEmpty)
    {
        Console.WriteLine($"Column A value: {cell.Text}");
    }
}

// Read an entire row
foreach (var cell in sheet.GetRow(1))  // Row 2
{
    Console.WriteLine($"Row 2 value: {cell.Text}");
}

// Iterate through a range of cells and display their address and text content
foreach (var cell in sheet["A2:A6"])
{
    Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
}

// Read an entire column
foreach (var cell in sheet.GetColumn(0))  // Column A
{
    if (!cell.IsEmpty)
    {
        Console.WriteLine($"Column A value: {cell.Text}");
    }
}

// Read an entire row
foreach (var cell in sheet.GetRow(1))  // Row 2
{
    Console.WriteLine($"Row 2 value: {cell.Text}");
}

Each value in the cell range A2:A6 is accessed and printed to the console.

Read Range of Cells

For more detailed reading and writing examples, check the Excel reading tutorial in C#. You can also convert Excel data to DataTables for easier manipulation:

// Convert worksheet to DataTable for easier data manipulation
DataTable dataTable = sheet.ToDataTable(true);  // true = first row contains headers

// Access data using DataTable methods
foreach (DataRow row in dataTable.Rows)
{
    Console.WriteLine($"Employee: {row["Name"]}, Salary: {row["Salary"]}");
}

// Convert worksheet to DataTable for easier data manipulation
DataTable dataTable = sheet.ToDataTable(true);  // true = first row contains headers

// Access data using DataTable methods
foreach (DataRow row in dataTable.Rows)
{
    Console.WriteLine($"Employee: {row["Name"]}, Salary: {row["Salary"]}");
}

How Can I Create a New Excel File?

IronXL also facilitates creating new workbooks for data saving and retrieval. The Create Spreadsheets guide provides comprehensive examples.

You can create a new Excel file with a single line of code:

// Create a new workbook with the XLSX format
WorkBook workBook = new WorkBook(ExcelFileFormat.XLSX);

// Alternative: Create with XLS format for compatibility
WorkBook xlsWorkBook = new WorkBook(ExcelFileFormat.XLS);

// Set workbook metadata
workBook.Metadata.Title = "Employee Data";
workBook.Metadata.Author = "Your Name";
workBook.Metadata.Keywords = "employees, salary, data";

// Create a new workbook with the XLSX format
WorkBook workBook = new WorkBook(ExcelFileFormat.XLSX);

// Alternative: Create with XLS format for compatibility
WorkBook xlsWorkBook = new WorkBook(ExcelFileFormat.XLS);

// Set workbook metadata
workBook.Metadata.Title = "Employee Data";
workBook.Metadata.Author = "Your Name";
workBook.Metadata.Keywords = "employees, salary, data";

Next, create a worksheet and add data to it. For more advanced creation patterns, see Create a new Excel File.

How Do I Add Worksheets to a Workbook?

// Create a worksheet named "GDPByCountry" in the workbook
WorkSheet workSheet = workBook.CreateWorkSheet("GDPByCountry");

// Create multiple worksheets at once
WorkSheet sheet2 = workBook.CreateWorkSheet("PopulationData");
WorkSheet sheet3 = workBook.CreateWorkSheet("Summary");

// Copy an existing worksheet
WorkSheet copiedSheet = workSheet.CopySheet("GDPByCountryCopy");

// Create a worksheet named "GDPByCountry" in the workbook
WorkSheet workSheet = workBook.CreateWorkSheet("GDPByCountry");

// Create multiple worksheets at once
WorkSheet sheet2 = workBook.CreateWorkSheet("PopulationData");
WorkSheet sheet3 = workBook.CreateWorkSheet("Summary");

// Copy an existing worksheet
WorkSheet copiedSheet = workSheet.CopySheet("GDPByCountryCopy");

This code adds a worksheet named "GDPByCountry" to the workbook, allowing you to add cell values. Learn more about managing worksheets and copying worksheets.

To set a value for a specific cell, use the code below:

// Set the value of cell A1 to "Example"
workSheet["A1"].Value = "Example";

// Add different types of data
workSheet["A2"].Value = 12345;  // Integer
workSheet["A3"].Value = 99.99m;  // Decimal
workSheet["A4"].Value = DateTime.Now;  // Date
workSheet["A5"].Value = true;  // Boolean

// Add formulas
workSheet["B1"].Formula = "=SUM(A2:A3)";

// Set multiple cells at once using a range
workSheet["C1:C5"].Value = "Bulk Value";

// Save the workbook
workBook.SaveAs("output.xlsx");

// Set the value of cell A1 to "Example"
workSheet["A1"].Value = "Example";

// Add different types of data
workSheet["A2"].Value = 12345;  // Integer
workSheet["A3"].Value = 99.99m;  // Decimal
workSheet["A4"].Value = DateTime.Now;  // Date
workSheet["A5"].Value = true;  // Boolean

// Add formulas
workSheet["B1"].Formula = "=SUM(A2:A3)";

// Set multiple cells at once using a range
workSheet["C1:C5"].Value = "Bulk Value";

// Save the workbook
workBook.SaveAs("output.xlsx");

The final output is:

Add Value to Cell

Working with Different Excel Formats

IronXL supports multiple Excel formats. Here's how to handle different file types:

// Convert between formats
WorkBook workbook = WorkBook.Load("data.csv");
workbook.SaveAs("data.xlsx");  // Convert CSV to XLSX

// Export to different formats
workbook.SaveAsCsv("output.csv", ";");  // CSV with semicolon delimiter
workbook.SaveAsJson("output.json");     // Export as JSON
workbook.SaveAsXml("output.xml");       // Export as XML

// Convert between formats
WorkBook workbook = WorkBook.Load("data.csv");
workbook.SaveAs("data.xlsx");  // Convert CSV to XLSX

// Export to different formats
workbook.SaveAsCsv("output.csv", ";");  // CSV with semicolon delimiter
workbook.SaveAsJson("output.json");     // Export as JSON
workbook.SaveAsXml("output.xml");       // Export as XML

Learn more about converting spreadsheet file types and converting XLSX to CSV, JSON, XML.

Error Handling and Best Practices

When working with Excel files, implement proper error handling:

try
{
    WorkBook workbook = WorkBook.Load("test.xlsx");
    WorkSheet sheet = workbook.GetWorkSheet("Sheet1");

    // Check if sheet exists
    if (sheet == null)
    {
        Console.WriteLine("Worksheet not found!");
        return;
    }

    // Process data
    var value = sheet["A1"].Value;
}
catch (Exception ex)
{
    Console.WriteLine($"Error reading Excel file: {ex.Message}");
}

try
{
    WorkBook workbook = WorkBook.Load("test.xlsx");
    WorkSheet sheet = workbook.GetWorkSheet("Sheet1");

    // Check if sheet exists
    if (sheet == null)
    {
        Console.WriteLine("Worksheet not found!");
        return;
    }

    // Process data
    var value = sheet["A1"].Value;
}
catch (Exception ex)
{
    Console.WriteLine($"Error reading Excel file: {ex.Message}");
}

For production applications, consider setting up logging and implementing proper error handling patterns.