1.
2.
3.
4.
5.
6.
7.
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 XLS or XLSX File
How to
Read an Excel
File in C#
Tutorial
1
Download the IronXL C# Library for FREE
Load a WorkBook
Create a WorkBook
Create a WorkSheet
Get Cell Range
Edit Cell Values Within a Range
Validate Spreadsheet Data
Export Data using Entity Framework
Add Formulae to a Spreadsheet
Download Data from an API to Spreadsheet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
Table of Contents
This tutorial explains how to read data from Excel in C# and use the library 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.
Interact with the tutorial: https://ironsoftware.com/csharp/excel/tutorials/how-to-read-excel-file-csharp/
Share the tutorial:
C# Read Excel Files
with Examples
by Christian Findlay
In this example we can see that Excel files can be read efficiently without Interop in C#. The final Advanced
Operations show Linq compatibility and aggregate range mathematics.
Read XLS or XLSX Files: Quick Code
We will see how easy it is to read Excel files in C# or VB.Net using the IronXL library. The samples contain
three Excel spreadsheets.
Dedicated product support from our .NET engineers
Easy installation via Microsoft Visual Studio
FREE for development. Licenses from $399.
IronXL Includes:
Install the IronXL Excel Library. We can do this using our NuGet package or by downloading the
.Net Excel DLL.
Use the WorkBook.Load method to read any XLS, XLSX or CSV document.
Get Cell values using intuitive syntax:
1.
2.
3.
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 Excel files.
sheet["A11"].DecimalValue
Watch the Tutorial
How To
Read Excel Files in C#
Read Data from Excel in .NET using IronXL
2
3
1. Download the IronXL C# Library for FREE
Step 1
3
Manually install into your project
Download DLL
or
nuget.org/packages/IronXL.Excel/
Install with NuGet
In Visual Studio, right-click on the project select "Manage Nuget Packages ..."
Search for the IronXL.Excel package and install
1.
2.
Installing the IronXL Nuget Package
The first thing we need to do is install the IronXL.Excel library, adding Excel functionality to the .NET
framework.
1. Download the IronXL C# Library for FREE
Step 1
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
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)
Note: Use ExcelFileFormat.XLS for legacy for Excel spreadsheets (95 and earlier)
Sample: ApiToExcelProcessor
1.
Dim workbook = New WorkBook(ExcelFileFormat.XLSX)
To create a new WorkBook in memory, construct a new WorkBook with the spreadsheet type.
3. Create a WorkBook
Sample: ExcelToDB
1.
Dim worksheet = workbook.GetWorkSheet("GDPByCountry")
WorkSheet
WorkBook.GetWorkSheet
Each WorkBook can have multiple objects. These represent worksheets in the Excel document.
If the spreadsheet contains worksheets, retrieve them by name .
Sample: ExcelToDBProcessor
1.
Dim workbook = WorkBook.Load("Spreadsheets\\GDP.xlsx")
The class represents an Excel spreadsheet. To open a WorkBook, use WorkBook.Load and
specify the path of the Excel file (.xlsx)
WorkBook
2. Load a WorkBook
How to Tutorials
Alternatively, we can start by downloading the IronXL .NET Excel DLL and manually installing into Visual
Studio.
Direct Download Installation
Additionally, you can view the package on the NuGet site here.
PM > Install-Package IronXL.Excel
Enter the Package Manager Console
Type > Install-Package IronXL.Excel
1.
2.
Another way to install is:
4
There are several ways to read or edit the values of cells within a Range. If the count is known, use a For
loop.
6. Edit Cell Values Within a Range
Sample: DataValidation
1.
Dim range = worksheet("D2:D101")
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 on a WorkSheet.
5. Get Cell Range
GetRange
1.
Dim worksheet = workbook.CreateWorkSheet("Countries")
To create a new WorkSheet call and pass the name of the worksheet
WorkBook.CreateWorkSheet
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:
4. Create a WorkSheet
5
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 spreadsheet, specifies headers, and outputs the error message results so that there
is a log of invalid data.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
'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
Use IronXL to validate a spreadsheet of data. The DataValidation sample uses to
validate phone numbers and uses standard C# APIs to validate email addresses and dates.
7. Validate Spreadsheet Data
libphonenumber-csharp
Sample: DataValidation
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
'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
6
1.
2.
3.
4.
5.
6.
Public Class Country
<Key>
Public Property Key() As Guid
Public Property Name() As String
Public Property GDP() As Decimal
End Class
EntityFramework allows you to create a model object that can export data to the database.
Use IronXL to export data to a database or convert an Excel spreadsheet to a database. The
sample reads a spreadsheet with GDP by country and then exports that data to an SQLite database. It uses
EntityFramework to build the database and then export the data line by line.
Add the SQLite Entity Framework NuGet packages.
8. Export Data using Entity Framework
ExcelToDB
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
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")
7
CountryContext SaveAsync
Create a , iterate through the range to create each record, and then to
commit data to the database
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
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
This code configures the database context. To use a different database, install the corresponding NuGet
package and find the equivalent of
UseSqLite()
8
Sample: AddFormulaeProcessor
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
'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
The code below iterates through each state and puts a percentage total in column C.
Set formula of 's with the property.
9. Add Formulae to a Spreadsheet
Cell Formula
Sample: ExcelToDB
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
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 = 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 = 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
9
The following code iterates through the countries and sets the Name, Population, Region, NumericCode, and
Top 3 Languages in the spreadsheet.
This is what the API JSON data looks like.
Sample: ApiToExcel
1.
2.
Dim client = New Client(New Uri("https://restcountries.eu/rest/v2/"))
Dim countries As List(Of RestCountry) = Await client.GetAsync(Of List(Of RestCountry))()
The following call makes a REST call with RestClient.Net. It downloads JSON and converts it into a "List" of
the type . It is then easy to iterate through each country and save the data from the REST
API to an Excel spreadsheet.
10. Download Data from an API to Spreadsheet
RestCountry
10
11
Download
The full free C# for Excel Source Code for this tutorial is available to download
as a zipped Visual Studio 2017 project file.
Download this Tutorial as C# Source Code
Tutorial Quick Access
You may also find the IronXL class documentation within the Object Reference of great value.
In addition, there are other tutorials which may shed light in other aspects of IronXL.Excel including
Creating, Opening, Writing Editing, Saving and Exporting XLS, XLSX and CSV files without using Excel
Interop.
Object Reference and Resources
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
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
12
Download
Get set up in 5 minutes.
Try IronPDF Free
See Licenses
Free for development.
License from $399.
Licensing
Get Started
View code examples
and tutorials.
Documentation
Ask a Question
Open a support ticket
with our development team.
Support
The C# PDF solution you've been looking for.
View the Object Reference
Explore the Object Reference for IronXL, outlining the details of all of IronXL’s
features, namespaces, classes, methods fields and enums.
View the Object Reference
How to Read Excel File in C# on GitHub
The source code for this project is available in C# and VB.NET on GitHub.
Use this code as an easy way to get up and running in just a few minutes. The
project is saved as a Microsoft Visual Studio 2017 project, but is compatible with
any .NET IDE.
Explore this Tutorial on GitHub
