C# Read Excel File with Examples
Published: 2021-11-23 08:39:13

---
canonical_url: https://ironsoftware.com/csharp/excel/tutorials/how-to-read-excel-file-csharp/
---

This tutorial explains how to read an Excel file in C# and using libraries for everyday tasks like validation, conversion to a database, saving data from Web APIs, and modifying formulae within the spreadsheet. This article references the IronXL code samples, which is a .NET Core Console App.

Reading and creating Excel files in C# and all other .NET languages is easy using the IronXL software library.

It does not require Excel to be installed on your server or Interop. IronXL provides a faster and more intuitive API than Microsoft.Office.Interop.Excel.

<hr class="separator"></a>

#### Overview

## Read Excel files in C# by following these steps (CSV XLSX & XLS)
<ol>
  <li>Download the Read CSV Excel File C# Library</li>
  <li>Load and read an Excel file (workbook)</li>
  <li>Create an Excel workbook in CSV or XLSX</li>
  <li>Enable cells and edit cell values in CSV or XLSX</li>
  <li>Validate spreadsheet data</li>
  <li>Export data using Entity Framework</li>
</ol>

## Read Data from Excel file using IronXL

IronXL is a .NET library that facilitates reading and editing Microsoft Excel documents with C#. This tutorial will walk you through using C sharp code to read the Excel file.

<ol>
  <li>Install the IronXL Excel Library. We can do this using our <a href="https://www.nuget.org/packages/IronXL.Excel/">NuGet package</a> or by downloading the <a href="https://ironsoftware.com/csharp/excel/packages/IronXL.zip">.Net Excel DLL.</a></li>
  <li>Use the WorkBook.Load method to read any XLS, XLSX or CSV document.</li>
  <li>Get Cell values using intuitive syntax:</li>
</ol> 

`sheet["A11"].DecimalValue`

#### IronXL Includes:
<ul>
  <li>Dedicated product support from our .NET engineers</li>
  <li>Easy installation via Microsoft Visual Studio</li>
  <li>30 day free trial test for development. Licenses from $499</li>
</ul>
We will see how easy it is to read <strong>Excel</strong> files in C# or VB.Net using the IronXL library. The samples contain three Excel sheets.

![Showing three Excel sheets](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8vagsgdp3a2ci0sfyyuq.png)

##### Read XLS or XLSX Files: Quick Code

In this example we can see how to read the excel file efficiently without Interop in C#. The final Advanced Operations show <strong>Linq</strong> compatibility and aggregate range mathematics.

<strong>C#:</strong>
```cs
/**
Read XLS or XLSX File
anchor-read-an-xls-or-xlsx-file
**/
using IronXL;
using System.Linq;
//Supported spreadsheet formats for reading include: XLSX, XLS, CSV and TSV
WorkBook workbook = WorkBook.Load("test.xlsx");
WorkSheet sheet = workbook.WorkSheets.First();
//Select cells easily in Excel notation and return the calculated value
int cellValue = sheet["A2"].IntValue;
// Read from Ranges of cells elegantly.
foreach (var cell in sheet["A2:A10"])
{
    Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
}
///Advanced Operations
//Calculate aggregate values such as Min, Max and Sum
decimal sum = sheet["A2:A10"].Sum();
//Linq compatible
decimal max = sheet["A2:A10"].Max(c => c.DecimalValue);
```

<strong>VB:</strong>
```cs
'''
'''Read XLS or XLSX File
'''anchor-read-an-xls-or-xlsx-file
'''*
Imports IronXL
Imports System.Linq
'Supported spreadsheet formats for reading include: XLSX, XLS, CSV and TSV
Private workbook As WorkBook = WorkBook.Load("test.xlsx")
Private sheet As WorkSheet = workbook.WorkSheets.First()
'Select cells easily in Excel notation and return the calculated value
Private cellValue As Integer = sheet("A2").IntValue
' Read from Ranges of cells elegantly.
For Each cell In sheet("A2:A10")
	Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text)
Next cell
'''Advanced Operations
'Calculate aggregate values such as Min, Max and Sum
Dim sum As Decimal = sheet("A2:A10").Sum()
'Linq compatible
Dim max As Decimal = sheet("A2:A10").Max(Function(c) c.DecimalValue)
```

<hr class=""separator"></hr>

#### Step 1

## 1. Download the IronXL C# Library for FREE

The first thing we need to do is install the IronXL Excel library, adding Excel functionality to the .NET framework.

Installing IronXL Excel, is most easily achieved using our NuGet package, although you may also choose  to manually install the <a href="https://ironsoftware.com/csharp/excel/packages/IronXL.zip">DLL</a> to your project or to your global assembly cache.

### Installing the IronXL Nuget Package

<ol>
  <li>In Visual Studio, right-click on the project select "Manage Nuget Packages ..."</li>
  <li>Search for the IronXL Excel package and install</li>
</ol>

![installing-ironxl](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wgycp0vb9jhc2m3ynyw7.png)

Another way to install is:
<ol>
  <li>Enter the Package Manager Console</li>
  <li>Type > Install-Package IronXL.Excel</li>
</ol>

```cs
PM > Install-Package IronXL.Excel
```

Additionally, you can <a href="https://www.nuget.org/packages/IronXL.Excel/" target="_blank">view the package on the NuGet site here</a>

### Direct Download Installation

Alternatively, we can start by downloading the IronXL <a href="https://ironsoftware.com/csharp/excel/packages/IronXL.zip">.NET Excel DLL</a> and manually installing into Visual Studio.

<hr class="separator"></hr>

#### How To Tutorials

## 2. Load a WorkBook

The `WorkBook` class represents an Excel sheet. To open an Excel File using C#, we use WorkBook.Load and specify the path of the Excel file (.xlsx).

<strong>C#:</strong>
```cs
/**
Load WorkBook
anchor-load-a-workbook
**/
var workbook = WorkBook.Load(@"Spreadsheets\\GDP.xlsx");
```

<strong>VB:</strong>
```cs
'''
'''Load WorkBook
'''anchor-load-a-workbook
'''*
Dim workbook = WorkBook.Load("Spreadsheets\\GDP.xlsx")
```

Sample: <em>ExcelToDBProcessor</em>

Each WorkBook can have multiple `WorkSheet` objects. These represent worksheets in the Excel document. If the sheet contains worksheets, retrieve them by name `WorkBook.GetWorkSheet`.

<strong>C#:</strong>
```cs
var worksheet = workbook.GetWorkSheet("GDPByCountry");
```

<strong>VB:</strong>
```cs
Dim worksheet = workbook.GetWorkSheet("GDPByCountry")
```

Sample: <em>ExcelToDB</em>

<hr class="separator"></hr>

## 3. Create a WorkBook

To create a new WorkBook in memory, construct a new WorkBook with the sheet type.

<strong>C#:</strong>
```cs
/**
Create WorkBook
anchor-create-a-workbook
**/
var workbook = new WorkBook(ExcelFileFormat.XLSX);
```

<strong>VB:</strong>
```cs
'''
'''Create WorkBook
'''anchor-create-a-workbook
'''*
Dim workbook As New WorkBook(ExcelFileFormat.XLSX)
```
Sample: <em>ApiToExcelProcessor</em>

Note: Use ExcelFileFormat.XLS for legacy for Microsoft Excel spreadsheets (95 and earlier).

<hr class="separator"></hr>

## 4. Create a WorkSheet

Each "WorkBook" can have multiple WorkSheets. A "WorkSheet" is a sheet of data, while a WorkBook represents a collection of WorkSheets. This is how one workbook with two worksheets looks in Excel.

![excel multiple workbooks](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kxr56f4t01mq2hc66pme.png)

To create a new WorkSheet call `WorkBook.CreateWorkSheet` and pass the name of the worksheet.

<strong>C#:</strong>
```cs
var worksheet = workbook.CreateWorkSheet("Countries");
```

<strong>VB:</strong>
```cs
Dim worksheet = workbook.CreateWorkSheet("Countries")
```

<hr class="separator"></hr>

## 5. Get Cell Range

The "Range" class represents a two-dimensional collection of "Cell" objects. It represents a literal range of Excel cells. Obtain ranges by using the string indexer on a WorkSheet object.

The argument text is either the coordinate of a cell (e.g. "A1") or a span of cells from left to right top to bottom (e.g. "B2:E5"). It is also possible to call `GetRange` on a WorkSheet.

<strong>C#:</strong>
```cs
var range = worksheet["D2:D101"];
```

<strong>VB:</strong>
```cs
Dim range = worksheet("D2:D101")
```
Sample: <em>DataValidation</em>

<hr class="separator"</hr>

## 6. Edit Cell Values Within a Range

There are several ways to read or edit the values of cells within a Range. If the count is known, use a For loop.

<strong>C#:</strong>
```cs
/**
Edit Cell Values in Range
anchor-edit-cell-values-within-a-range
**/
//Iterate through the rows
for (var y = 2; y <= 101; y++)
{
    var result = new PersonValidationResult { Row = y };
    results.Add(result);
    //Get all cells for the person
    var cells = worksheet[$"A{y}:E{y}"].ToList();
    //Validate the phone number (1 = B)
    var phoneNumber = cells[1].Value;
    result.PhoneNumberErrorMessage = ValidatePhoneNumber(phoneNumberUtil, (string)phoneNumber);
    //Validate the email address (3 = D)
    result.EmailErrorMessage = ValidateEmailAddress((string)cells[3].Value);
    //Get the raw date in the format of Month Day[suffix], Year (4 = E)
    var rawDate = (string)cells[4].Value;
    result.DateErrorMessage = ValidateDate(rawDate);
}
```

<strong>VB:</strong>
```cs
'''
'''Edit Cell Values in Range
'''anchor-edit-cell-values-within-a-range
'''*
'Iterate through the rows
For y = 2 To 101
	Dim result = New PersonValidationResult With {.Row = y}
	results.Add(result)
	'Get all cells for the person
	Dim cells = worksheet($"A{y}:E{y}").ToList()
	'Validate the phone number (1 = B)
	Dim phoneNumber = cells(1).Value
	result.PhoneNumberErrorMessage = ValidatePhoneNumber(phoneNumberUtil, CStr(phoneNumber))
	'Validate the email address (3 = D)
	result.EmailErrorMessage = ValidateEmailAddress(CStr(cells(3).Value))
	'Get the raw date in the format of Month Day[suffix], Year (4 = E)
	Dim rawDate = CStr(cells(4).Value)
	result.DateErrorMessage = ValidateDate(rawDate)
Next y
```
Sample: <em>DataValidation</em>

<hr class="separator"></hr>

## 7. Validate Spreadsheet Data

Use IronXL to validate a sheet of data. The DataValidation sample uses `libphonenumber-csharp` to validate phone numbers and uses standard C# APIs to validate email addresses and dates.

<strong>C#:</strong>
```cs
/**
Validate Spreadsheet Data
anchor-validate-spreadsheet-data
**/
//Iterate through the rows
for (var i = 2; i <= 101; i++)
{
    var result = new PersonValidationResult { Row = i };
    results.Add(result);
    //Get all cells for the person
    var cells = worksheet[$"A{i}:E{i}"].ToList();
    //Validate the phone number (1 = B)
    var phoneNumber = cells[1].Value;
    result.PhoneNumberErrorMessage = ValidatePhoneNumber(phoneNumberUtil, (string)phoneNumber);
    //Validate the email address (3 = D)
    result.EmailErrorMessage = ValidateEmailAddress((string)cells[3].Value);
    //Get the raw date in the format of Month Day[suffix], Year (4 = E)
    var rawDate = (string)cells[4].Value;
    result.DateErrorMessage = ValidateDate(rawDate);
}
```

<strong>VB:</strong>
```cs
'''
'''Validate Spreadsheet Data
'''anchor-validate-spreadsheet-data
'''*
'Iterate through the rows
For i = 2 To 101
	Dim result = New PersonValidationResult With {.Row = i}
	results.Add(result)
	'Get all cells for the person
	Dim cells = worksheet($"A{i}:E{i}").ToList()
	'Validate the phone number (1 = B)
	Dim phoneNumber = cells(1).Value
	result.PhoneNumberErrorMessage = ValidatePhoneNumber(phoneNumberUtil, CStr(phoneNumber))
	'Validate the email address (3 = D)
	result.EmailErrorMessage = ValidateEmailAddress(CStr(cells(3).Value))
	'Get the raw date in the format of Month Day[suffix], Year (4 = E)
	Dim rawDate = CStr(cells(4).Value)
	result.DateErrorMessage = ValidateDate(rawDate)
Next i
```

The above code loops through each row in the spreadsheet and grabs the cells as a list. Each validates method checks the value of a cell and returns an error message if the value is invalid.

This code creates a new sheet, specifies headers, and outputs the error message results so that there is a log of invalid data.

<strong>C#:</strong>
```cs
var resultsSheet = workbook.CreateWorkSheet("Results");
resultsSheet["A1"].Value = "Row";
resultsSheet["B1"].Value = "Valid";
resultsSheet["C1"].Value = "Phone Error";
resultsSheet["D1"].Value = "Email Error";
resultsSheet["E1"].Value = "Date Error";
for (var i = 0; i < results.Count; i++)
{
    var result = results[i];
    resultsSheet[$"A{i + 2}"].Value = result.Row;
    resultsSheet[$"B{i + 2}"].Value = result.IsValid ? "Yes" : "No";
    resultsSheet[$"C{i + 2}"].Value = result.PhoneNumberErrorMessage;
    resultsSheet[$"D{i + 2}"].Value = result.EmailErrorMessage;
    resultsSheet[$"E{i + 2}"].Value = result.DateErrorMessage;
}
workbook.SaveAs(@"Spreadsheets\\PeopleValidated.xlsx");
```

<strong>VB:</strong>
```cs
Dim resultsSheet = workbook.CreateWorkSheet("Results")
resultsSheet("A1").Value = "Row"
resultsSheet("B1").Value = "Valid"
resultsSheet("C1").Value = "Phone Error"
resultsSheet("D1").Value = "Email Error"
resultsSheet("E1").Value = "Date Error"
For i = 0 To results.Count - 1
	Dim result = results(i)
	resultsSheet($"A{i + 2}").Value = result.Row
	resultsSheet($"B{i + 2}").Value = If(result.IsValid, "Yes", "No")
	resultsSheet($"C{i + 2}").Value = result.PhoneNumberErrorMessage
	resultsSheet($"D{i + 2}").Value = result.EmailErrorMessage
	resultsSheet($"E{i + 2}").Value = result.DateErrorMessage
Next i
workbook.SaveAs("Spreadsheets\\PeopleValidated.xlsx")
```

<hr class="separator"></hr>

## 8. Export Data using Entity Framework

Use IronXL to export data to a database or convert an Excel spreadsheet to a database. The `ExcelToDB` sample reads a spreadsheet with GDP by country and then exports that data to an SQLite.

It uses EntityFramework to build the database and then export the data line by line.

Add the SQLite Entity Framework NuGet packages.

![Add the SQLite Entity packages](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nlncrp1461kbya9o1mun.png)

EntityFramework allows you to create a model object that can export data to the database.

<strong>C#:</strong>
```cs
public class Country
{
    [Key]
    public Guid Key { get; set; }
    public string Name { get; set; }
    public decimal GDP { get; set; }
}
```

<strong>VB:</strong>
```cs
Public Class Country
	<Key>
	Public Property Key() As Guid
	Public Property Name() As String
	Public Property GDP() As Decimal
End Class
```
To use a different database, install the corresponding NuGet package and find the equivalent of `UseSqLite()`

<strong>C#:</strong>
```cs
/**
Export Data using Entity Framework
anchor-export-data-using-entity-framework
**/
public class CountryContext : DbContext
{
    public DbSet<Country> Countries { get; set; }
    public CountryContext()
    {
        //TODO: Make async
        Database.EnsureCreated();
    }
    /// <summary>
    /// Configure context to use Sqlite
    /// </summary>
    /// <param name="optionsBuilder"></param>
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var connection = new SqliteConnection($"Data Source=Country.db");
        connection.Open();
        var command = connection.CreateCommand();
        //Create the database if it doesn't already exist
        command.CommandText = $"PRAGMA foreign_keys = ON;";
        command.ExecuteNonQuery();
        optionsBuilder.UseSqlite(connection);
        base.OnConfiguring(optionsBuilder);
    }
}
```

<strong>VB:</strong>
```cs
'''
'''Export Data using Entity Framework
'''anchor-export-data-using-entity-framework
'''*
Public Class CountryContext
	Inherits DbContext
	Public Property Countries() As DbSet(Of Country)
	Public Sub New()
		'TODO: Make async
		Database.EnsureCreated()
	End Sub
	''' <summary>
	''' Configure context to use Sqlite
	''' </summary>
	''' <param name="optionsBuilder"></param>
	Protected Overrides Sub OnConfiguring(ByVal optionsBuilder As DbContextOptionsBuilder)
		Dim connection = New SqliteConnection($"Data Source=Country.db")
		connection.Open()
		Dim command = connection.CreateCommand()
		'Create the database if it doesn't already exist
		command.CommandText = $"PRAGMA foreign_keys = ON;"
		command.ExecuteNonQuery()
		optionsBuilder.UseSqlite(connection)
		MyBase.OnConfiguring(optionsBuilder)
	End Sub
End Class
```
Create a `CountryContext`, iterate through the range to create each record, and then `SaveAsync` to commit data to the database

<strong>C#:</strong>
```cs
public async Task ProcessAsync()
{
    //Get the first worksheet
    var workbook = WorkBook.Load(@"Spreadsheets\\GDP.xlsx");
    var worksheet = workbook.GetWorkSheet("GDPByCountry");
    //Create the database connection
    using (var countryContext = new CountryContext())
    {
        //Iterate through all the cells
        for (var i = 2; i <= 213; i++)
        {
            //Get the range from A-B
            var range = worksheet[$"A{i}:B{i}"].ToList();
            //Create a Country entity to be saved to the database
            var country = new Country
            {
                Name = (string)range[0].Value,
                GDP = (decimal)(double)range[1].Value
            };
            //Add the entity 
            await countryContext.Countries.AddAsync(country);
        }
        //Commit changes to the database
        await countryContext.SaveChangesAsync();
    }
}
```

<strong>VB:</strong>
```cs
Public Async Function ProcessAsync() As Task
	'Get the first worksheet
	Dim workbook = WorkBook.Load("Spreadsheets\\GDP.xlsx")
	Dim worksheet = workbook.GetWorkSheet("GDPByCountry")
	'Create the database connection
	Using countryContext As New CountryContext()
		'Iterate through all the cells
		For i = 2 To 213
			'Get the range from A-B
			Dim range = worksheet($"A{i}:B{i}").ToList()
			'Create a Country entity to be saved to the database
			Dim country As New Country With {
				.Name = CStr(range(0).Value),
				.GDP = CDec(CDbl(range(1).Value))
			}
			'Add the entity 
			Await countryContext.Countries.AddAsync(country)
		Next i
		'Commit changes to the database
		Await countryContext.SaveChangesAsync()
	End Using
End Function
```
Sample: <em>ExcelToDB</em>

<hr class="separator"></hr>

## 9. Add Formula to a Spreadsheet

Set formula of `Cell`'s with the `Formula` property.

The code below iterates through each state and puts a percentage total in column C.

<strong>C#:</strong>
```cs
/**
Add Spreadsheet Formulae
anchor-add-formulae-to-a-spreadsheet
**/
//Iterate through all rows with a value
for (var y = 2; y < i; y++)
{
    //Get the C cell
    var cell = sheet[$"C{y}"].First();
    //Set the formula for the Percentage of Total column
    cell.Formula = $"=B{y}/B{i}";
}
```

<strong>VB:</strong>
```cs
'''
'''Add Spreadsheet Formulae
'''anchor-add-formulae-to-a-spreadsheet
'''*
'Iterate through all rows with a value
Dim y = 2
Do While y < i
	'Get the C cell
	Dim cell = sheet($"C{y}").First()
	'Set the formula for the Percentage of Total column
	cell.Formula = $"=B{y}/B{i}"
	y += 1
Loop
```
Sample: <em>AddFormulaProcessor</em>

<hr class="seperator"></hr>

## 10. Download Data from an API to Spreadsheet 

The following call makes a REST call with <a href="" target="_blank" rel="nofollow">RestClient.NET.</a> It downloads JSON and converts it into a "List" of the type `RestCountry`. It is then easy to iterate through each country and save the data from the REST API to an Excel spreadsheet.

<strong>C#:</strong>
```cs
/**
Data API to Spreadsheet
anchor-download-data-from-an-api-to-spreadsheet
**/
var client = new Client(new Uri("https://restcountries.eu/rest/v2/"));
List<RestCountry> countries = await client.GetAsync<List<RestCountry>>();
```

<strong>VB:</strong>
```cs
'''
'''Data API to Spreadsheet
'''anchor-download-data-from-an-api-to-spreadsheet
'''*
Dim client As New Client(New Uri("https://restcountries.eu/rest/v2/"))
Dim countries As List(Of RestCountry) = Await client.GetAsync(Of List(Of RestCountry))()
```
Sample: <em>ApiToExcel</em>

This is what the API JSON data looks like.

![API JSON File](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6z40x87y21kql4g41787.png)

The following code iterates through the countries and sets the Name, Population, Region, NumericCode, and Top 3 Languages in the spreadsheet.

<strong>C#:</strong>
```cs
for (var i = 2; i < countries.Count; i++)
{
    var country = countries[i];
    //Set the basic values
    worksheet[$"A{i}"].Value = country.name;
    worksheet[$"B{i}"].Value = country.population;
    worksheet[$"G{i}"].Value = country.region;
    worksheet[$"H{i}"].Value = country.numericCode;
    //Iterate through languages
    for (var x = 0; x < 3; x++)
    {
        if (x > (country.languages.Count - 1)) break;
        var language = country.languages[x];
        //Get the letter for the column
        var columnLetter = GetColumnLetter(4 + x);
        //Set the language name
        worksheet[$"{columnLetter}{i}"].Value = language.name;
    }
}
```

<strong>VB:</strong>
```cs
For i = 2 To countries.Count - 1
	Dim country = countries(i)
	'Set the basic values
	worksheet($"A{i}").Value = country.name
	worksheet($"B{i}").Value = country.population
	worksheet($"G{i}").Value = country.region
	worksheet($"H{i}").Value = country.numericCode
	'Iterate through languages
	For x = 0 To 2
		If x > (country.languages.Count - 1) Then
			Exit For
		End If
		Dim language = country.languages(x)
		'Get the letter for the column
		Dim columnLetter = GetColumnLetter(4 + x)
		'Set the language name
		worksheet($"{columnLetter}{i}").Value = language.name
	Next x
Next i
```
<hr class="separator"></hr>

## Object Reference and Resources

You may also find the <a href="https://ironsoftware.com/csharp/excel/object-reference/api/" target="_blank">IronXL class documentation</a> within the Object Reference of great value.

In addition, there are other tutorials which may shed light in other aspects of IronXL.Excel including <a href="https://ironsoftware.com/csharp/excel/tutorials/csharp-open-write-excel-file/" target="_blank">Creating, Opening, Writing, Editing, Saving, and Exporting</a> XLS, XLSX and CSV files without using Excel Interop.

## Summary

IronXL.Excel is alone .Net software library for reading a wide variety of spreadsheet formats. It does not require <a href="https://products.office.com/en-us/excel" target="_blank" rel="nofollow">Microsoft Excel</a> to be installed, and is not dependant on Interop.

<hr class="separator"></hr>

## 11. Watch the Read Excel File C# Tutorial Video

{% youtube sXkcdZWUcWI %}

<hr class="separator"></hr>