Photo from Unsplash

Originally Posted On: 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.

1. Download the Read CSV Excel File C# Library
2. Load and read an Excel file (workbook)
3. Create an Excel workbook in CSV or XLSX
4. Enable cells and edit cell values in
5. Validate spreadsheet data
6. Export data using Entity Framework

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.

1. Install the IronXL Excel Library. We can do this using our NuGet package or by downloading the .Net Excel DLL.
2. Use the WorkBook.Load method to read any XLS, XLSX or CSV document.
3. Get Cell values using intuitive syntax: sheet[“A11”].DecimalValue

• Dedicated product support from our .NET engineers
• Easy installation via Microsoft Visual Studio
• 30 day free trial test for development. Licenses from $399.

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 sheets.

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

1. /**
2. Read XLS or XLSX File
3. anchor-read-an-xls-or-xlsx-file
4. **/
5. using IronXL;
6. using System.Linq;
8. //Supported spreadsheet formats for reading include: XLSX, XLS, CSV and TSV
9. WorkBook workbook = WorkBook.Load(“test.xlsx”);
10. WorkSheet sheet = workbook.WorkSheets.First();
11. //Select cells easily in Excel notation and return the calculated value
12. int cellValue = sheet[“A2”].IntValue;
13. // Read from Ranges of cells elegantly.
14. foreach (var cell in sheet[“A2:A10”])
15. {
16. Console.WriteLine(“Cell {0} has value ‘{1}'”, cell.AddressString, cell.Text);
17. }
19. ///Advanced Operations
21. //Calculate aggregate values such as Min, Max and Sum
22. decimal sum = sheet[“A2:A10”].Sum();
23. //Linq compatible
24. decimal max = sheet[“A2:A10”].Max(c => c.DecimalValue);

Copy code to clipboardVB  C#

 Download DLL

or

Install-Package IronXL.Excelnuget.org/packages/IronXL.Excel/

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 DLL to your project or to your global assembly cache.

1. In Visual Studio, right-click on the project select “Manage Nuget Packages …”
2. Search for the IronXL.Excel package and install 

Another way to install is:

1. Enter the Package Manager Console
2. Type > Install-Package IronXL.Excel

PM > Install-Package IronXL.Excel

Additionally, you can view the package on the NuGet site here.

Alternatively, we can start by downloading the IronXL .NET Excel DLL and manually installing into Visual Studio.

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).

1. /**
2. Load WorkBook
3. anchor-load-a-workbook
4. **/
5. var workbook = WorkBook.Load(@”Spreadsheets\GDP.xlsx”);

Copy code to clipboardVB  C#

Sample: ExcelToDBProcessor

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.

1. var worksheet = workbook.GetWorkSheet(“GDPByCountry”);

Copy code to clipboardVB  C#

Sample: ExcelToDB

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

1. /**
2. Create WorkBook
3. anchor-create-a-workbook
4. **/
5. var workbook = new WorkBook(ExcelFileFormat.XLSX);

Copy code to clipboardVB  C#

Sample: ApiToExcelProcessor

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

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.

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

1. var worksheet = workbook.CreateWorkSheet(“Countries”);

Copy code to clipboardVB  C#

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.

1. var range = worksheet[“D2:D101”];

Copy code to clipboardVB  C#

Sample: DataValidation

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

1. /**
2. Edit Cell Values in Range
3. anchor-edit-cell-values-within-a-range
4. **/
5. //Iterate through the rows
6. for (var y = 2; y <= 101; y++)
7. {
8. var result = new PersonValidationResult { Row = y };
9. results.Add(result);
11. //Get all cells for the person
12. var cells = worksheet[$”A{y}:E{y}”].ToList();
14. //Validate the phone number (1 = B)
15. var phoneNumber = cells[1].Value;
16. result.PhoneNumberErrorMessage = ValidatePhoneNumber(phoneNumberUtil, (string)phoneNumber);
18. //Validate the email address (3 = D)
19. result.EmailErrorMessage = ValidateEmailAddress((string)cells[3].Value);
21. //Get the raw date in the format of Month Day[suffix], Year (4 = E)
22. var rawDate = (string)cells[4].Value;
23. result.DateErrorMessage = ValidateDate(rawDate);
24. }

Copy code to clipboardVB  C#

Sample: DataValidation

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.

1. /**
2. Validate Spreadsheet Data
3. anchor-validate-spreadsheet-data
4. **/
5. //Iterate through the rows
6. for (var i = 2; i <= 101; i++)
7. {
8. var result = new PersonValidationResult { Row = i };
9. results.Add(result);
11. //Get all cells for the person
12. var cells = worksheet[$”A{i}:E{i}”].ToList();
14. //Validate the phone number (1 = B)
15. var phoneNumber = cells[1].Value;
16. result.PhoneNumberErrorMessage = ValidatePhoneNumber(phoneNumberUtil, (string)phoneNumber);
18. //Validate the email address (3 = D)
19. result.EmailErrorMessage = ValidateEmailAddress((string)cells[3].Value);
21. //Get the raw date in the format of Month Day[suffix], Year (4 = E)
22. var rawDate = (string)cells[4].Value;
23. result.DateErrorMessage = ValidateDate(rawDate);
24. }

Copy code to clipboardVB  C#

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.

1. var resultsSheet = workbook.CreateWorkSheet(“Results”);
3. resultsSheet[“A1”].Value = “Row”;
4. resultsSheet[“B1”].Value = “Valid”;
5. resultsSheet[“C1”].Value = “Phone Error”;
6. resultsSheet[“D1”].Value = “Email Error”;
7. resultsSheet[“E1”].Value = “Date Error”;
9. for (var i = 0; i < results.Count; i++)
10. {
11. var result = results[i];
12. resultsSheet[$”A{i + 2}”].Value = result.Row;
13. resultsSheet[$”B{i + 2}”].Value = result.IsValid ? “Yes” : “No”;
14. resultsSheet[$”C{i + 2}”].Value = result.PhoneNumberErrorMessage;
15. resultsSheet[$”D{i + 2}”].Value = result.EmailErrorMessage;
16. resultsSheet[$”E{i + 2}”].Value = result.DateErrorMessage;
17. }
19. workbook.SaveAs(@”Spreadsheets\PeopleValidated.xlsx”);

Copy code to clipboardVB  C#

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.

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

1. public class Country
2. {
3. [Key]
4. public Guid Key { get; set; }
5. public string Name { get; set; }
6. public decimal GDP { get; set; }
7. }

Copy code to clipboardVB  C#

To use a different database, install the corresponding NuGet package and find the equivalent of UseSqLite()

1. /**
2. Export Data using Entity Framework
3. anchor-export-data-using-entity-framework
4. **/
5. public class CountryContext : DbContext
6. {
7. public DbSet<Country> Countries { get; set; }
9. public CountryContext()
10. {
11. //TODO: Make async
12. Database.EnsureCreated();
13. }
15. /// <summary>
16. /// Configure context to use Sqlite
17. /// </summary>
18. /// <param name=”optionsBuilder”></param>
19. protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
20. {
21. var connection = new SqliteConnection($”Data Source=Country.db”);
22. connection.Open();
24. var command = connection.CreateCommand();
26. //Create the database if it doesn’t already exist
27. command.CommandText = $”PRAGMA foreign_keys = ON;”;
28. command.ExecuteNonQuery();
30. optionsBuilder.UseSqlite(connection);
32. base.OnConfiguring(optionsBuilder);
33. }
35. }

Copy code to clipboardVB  C#

Create a CountryContext, iterate through the range to create each record, and then SaveAsync to commit data to the database

1. public async Task ProcessAsync()
2. {
3. //Get the first worksheet
4. var workbook = WorkBook.Load(@”Spreadsheets\GDP.xlsx”);
5. var worksheet = workbook.GetWorkSheet(“GDPByCountry”);
7. //Create the database connection
8. using (var countryContext = new CountryContext())
9. {
10. //Iterate through all the cells
11. for (var i = 2; i <= 213; i++)
12. {
13. //Get the range from A-B
14. var range = worksheet[$”A{i}:B{i}”].ToList();
16. //Create a Country entity to be saved to the database
17. var country = new Country
18. {
19. Name = (string)range[0].Value,
20. GDP = (decimal)(double)range[1].Value
21. };
23. //Add the entity
24. await countryContext.Countries.AddAsync(country);
25. }
27. //Commit changes to the database
28. await countryContext.SaveChangesAsync();
29. }
30. }

Copy code to clipboardVB  C#

Sample: ExcelToDB

Set formula of Cell‘s with the Formula property.

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

1. /**
2. Add Spreadsheet Formulae
3. anchor-add-formulae-to-a-spreadsheet
4. **/
5. //Iterate through all rows with a value
6. for (var y = 2; y < i; y++)
7. {
8. //Get the C cell
9. var cell = sheet[$”C{y}”].First();
11. //Set the formula for the Percentage of Total column
12. cell.Formula = $”=B{y}/B{i}”;
13. }

Copy code to clipboardVB  C#

Sample: AddFormulaeProcessor

The following call makes a REST call with RestClient.Net. 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.

1. /**
2. Data API to Spreadsheet
3. anchor-download-data-from-an-api-to-spreadsheet
4. **/
5. var client = new Client(new Uri(“https://restcountries.eu/rest/v2/”));
6. List<RestCountry> countries = await client.GetAsync<List<RestCountry>>();

Copy code to clipboardVB  C#

Sample: ApiToExcel

This is what the API JSON data looks like.

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

1. for (var i = 2; i < countries.Count; i++)
2. {
3. var country = countries[i];
5. //Set the basic values
6. worksheet[$”A{i}”].Value = country.name;
7. worksheet[$”B{i}”].Value = country.population;
8. worksheet[$”G{i}”].Value = country.region;
9. worksheet[$”H{i}”].Value = country.numericCode;
11. //Iterate through languages
12. for (var x = 0; x < 3; x++)
13. {
14. if (x > (country.languages.Count – 1)) break;
16. var language = country.languages[x];
18. //Get the letter for the column
19. var columnLetter = GetColumnLetter(4 + x);
21. //Set the language name
22. worksheet[$”{columnLetter}{i}”].Value = language.name;
23. }
24. }

Copy code to clipboardVB  C#

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..

IronXL.Excel is alone .Net software library for reading a wide variety of spreadsheet formats. It does not require Microsoft Excel to be installed, and is not dependant on Interop.