using IronXL; using System; using System.Linq; // Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV WorkBook workBook = WorkBook.Load("sample.xlsx"); // Select worksheet at index 0 WorkSheet workSheet = workBook.WorkSheets[0]; // Get any existing worksheet WorkSheet firstSheet = workBook.DefaultWorkSheet; // Select a cell and return the converted value int cellValue = workSheet["A2"].IntValue; // Read from ranges of cells elegantly. foreach (var cell in workSheet["A2:A10"]) { Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text); } // Calculate aggregate values such as Min, Max and Sum decimal sum = workSheet["A2:A10"].Sum(); // Linq compatible decimal max = workSheet["A2:A10"].Max(c => c.DecimalValue);

using IronSoftware.Drawing; using IronXL; using IronXL.Styles; using System.Linq; WorkBook workBook = WorkBook.Load("sample.xls"); WorkSheet workSheet = workBook.WorkSheets.First(); var range = workSheet["A1:H10"]; var cell = range.First(); // Set background color of the cell with an rgb string cell.Style.SetBackgroundColor("#428D65"); // Apply styling to the whole range. // Set underline property to the font // FontUnderlineType is enum that stands for different types of font underlying range.Style.Font.Underline = FontUnderlineType.SingleAccounting; // Define whether to use horizontal line through the text or not range.Style.Font.Strikeout = false; // Define whether the font is bold or not range.Style.Font.Bold = true; // Define whether the font is italic or not range.Style.Font.Italic = false; // Get or set script property of a font // Font script enum stands for available options range.Style.Font.FontScript = FontScript.Super; // Set the type of the border line // There are also TopBorder,LeftBorder,RightBorder,DiagonalBorder properties // BorderType enum indicates the line style of a border in a cell range.Style.BottomBorder.Type = BorderType.MediumDashed; // Indicate whether the cell should be auto-sized range.Style.ShrinkToFit = true; // Set alignment of the cell range.Style.VerticalAlignment = VerticalAlignment.Bottom; // Set border color range.Style.DiagonalBorder.SetColor("#20C96F"); // Define border type and border direction as well range.Style.DiagonalBorder.Type = BorderType.Thick; // DiagonalBorderDirection enum stands for direction of diagonal border inside cell range.Style.DiagonalBorderDirection = DiagonalBorderDirection.Forward; // Set background color of cells range.Style.SetBackgroundColor(Color.Aquamarine); // Set fill pattern of the cell // FillPattern enum indicates the style of fill pattern range.Style.FillPattern = FillPattern.Diamonds; // Set the number of spaces to intend the text range.Style.Indention = 5; // Indicate if the text is wrapped range.Style.WrapText = true; workBook.SaveAs("stylingOptions.xls");

Published March 20, 2023

How to Open Excel File in C#

Microsoft Excel is a spreadsheet application software developed by Microsoft. Its sole purpose is to organize and sort data into meaningful format. It helps visualizing the data with charts and also allows calculations on this data object. It helps businesses store all their financial details in the form of a workbook which can have multiple sheets. Opening, reading, and editing Excel files programmatically is an essential part of modern business applications. Here, we are going to have a look at IronXL, and how it can help us open and read Excel files in C# projects.

IronXL Excel Library

IronXL is .NET library that prioritizes ease of use, accuracy and speed for its users. It helps you open, read, create, and edit Excel files with lightning-fast performance and without any errors. It works without MS Office Interop, which makes it a powerful tool for developers.

IronXL is compatible with all .NET Frameworks along with Linux, MacOS, Docker, Azure and AWS. It can be used to create Console, Web and Desktop applications. It supports different workbook formats like XLS and XLSX file, XSLT and XLSM, CSV and TSV.

Some Important IronXL Features

Open, read and search data in different formats (XLS/XLSX file/CSV/TSV).
Export Excel Worksheets to XLS/XLSX/CSV/TSV/JSON.
Encrypting and decrypting XLSX/XLSM/XLTX files with passwords.
Work with Excel sheet as System.Data.DataSet and System.Data.DataTable objects.
Excel file formulas recalculated automatically each time a sheet is edited.
Easy spreadsheet data editing with an intuitive cell-range based syntax (e.g., WorkSheet["A1:B10"]).
Sort Cell Ranges, Columns and Rows.
Styling Cells - Font, Font Size, Background color, Border, Alignment and Numbering formats.

How to open an Excel file in C#?

Prerequisites

To use IronXL in C# applications, we need to ensure the following components are installed on computer:

Visual Studio - It is the official IDE for developing C# .NET applications. You can download and install Visual Studio from Microsoft website. You can also use Jetbrains ReSharper & Rider.
IronXL - It is the Excel library which helps to work with Excel sheets at a given path in C#. It must be installed in your C# application before using it. It can be downloaded from NuGet website or from Manage NuGet packages in Visual Studio tools. You can also download .NET Excel DLL file directly.

Adding Necessary Namespaces

Once Visual Studio and IronXL is installed, we need to use assembly reference for using IronXL in the source code. Add the following line of code on top of the file within new project where IronXL functions will be used:

//add reference
using IronXL;

//add reference
using IronXL;

'add reference
Imports IronXL

VB C#

Open an Existing Excel file in C#

Excel file are also known as workbooks. Each workbook contains multiple worksheets, and each worksheet contains cell values. To open and read Excel file, it should be loaded using the WorkBook class's load Load method.

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

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

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

VB C#

This opens the Excel file in workbook instance reference variable. As it can have multiple worksheets, so it can be used to open a specific worksheet or all at once. The following code opens the first worksheet in sheet instance variable:

WorkSheet sheet = workbook.WorkSheets.First();

WorkSheet sheet = workbook.WorkSheets.First();

Dim sheet As WorkSheet = workbook.WorkSheets.First()

VB C#

This will open the first sheet in Excel file and now data can be read and written to this sheet.

The Excel file opened here is:

Opening an Excel File with IronXL

Read Excel Files in C#

Once the Excel file is opened, it is ready for reading data. Reading data from Excel files in C# using IronXL is very easy and simple. You can read cell values by simply mentioning the cell reference number.

The following code retrieves the value of a cell from the file:

//Select cells easily in Excel-notation and return the value
int cellValue = sheet["C2"].IntValue;

//Display the value
Console.WriteLine(cellValue);

//Select cells easily in Excel-notation and return the value
int cellValue = sheet["C2"].IntValue;

//Display the value
Console.WriteLine(cellValue);

'Select cells easily in Excel-notation and return the value
Dim cellValue As Integer = sheet("C2").IntValue

'Display the value
Console.WriteLine(cellValue)

VB C#

The output is as follows:

Accessing the value within a single cell

Now, let's read data from a range of cells in the opened Excel file. The code goes as follows:

// Read from Range of cells elegantly.
foreach (var cell in sheet["A2:A6"])
{
    Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
}

// Read from Range of cells elegantly.
foreach (var cell in sheet["A2:A6"])
{
    Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
}

' Read from Range of cells elegantly.
For Each cell In sheet("A2:A6")
	Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text)
Next cell

VB C#

The value within the cell within cell range A2 - A6 is accessed using a for loop. Each value is printed to the console.

Accessing the values within the range of Excel spreadsheet cells

For a detailed working of reading and writing to cell values, check the read Excel file in C# example tutorial.

Create a New Workbook

IronXL also helps in creating new workbooks, which can later be used for data saving and retrieving.

Just like loading the Excel files, creating Excel files is also very easy and can be done using only one line of code.

WorkBook workBook = new WorkBook(ExcelFileFormat.XLSX);

WorkBook workBook = new WorkBook(ExcelFileFormat.XLSX);

Dim workBook As New WorkBook(ExcelFileFormat.XLSX)

VB C#

Before we save our workbook, let's create a worksheet and save some data on it for later use.

Create a New Worksheet

WorkSheet workSheet = workBook.CreateWorkSheet("GDPByCountry");

WorkSheet workSheet = workBook.CreateWorkSheet("GDPByCountry");

Dim workSheet As WorkSheet = workBook.CreateWorkSheet("GDPByCountry")

VB C#

The line of code shown above creates a worksheet named "GDPByCountry" in the currently-loaded workbook. We can now add cell values to our worksheet.

The following code adds a value to A1 cell:

workSheet["A1"].Value = "Example";

workSheet["A1"].Value = "Example";

workSheet("A1").Value = "Example"

VB C#

The final output is as follows:

Adding a new value to a new worksheet

Summary

In this article, we learnt how to open and read Excel file such as XLS, XLSX etc in C# without any Microsoft Interop. We also learnt how to create Excel file with ease using IronXL's reference. IronXL does not require Microsoft Excel to be installed on the system to perform all Excel related tasks.

IronXL provides a solution for all Excel related tasks to be done programmatically whether it be formula calculation, string sorting, trimming, find and replace, merge and unmerge, save files etc. You can also set cell data formats.

IronXL is available for a free 30 day trial and can be licensed for commercial use. IronXL's Lite package starts from $499.