How to Read Excel File in C# Console Application

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 inside the spreadsheet. This article references the IronXL code samples, which is a .Internet Core Panel 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 exist installed on your server or Interop. IronXL provides a faster and more intuitive API than Microsoft.Office.Interop.Excel.

Overview

Read Excel files in C# by following these steps (CSV XLSX & XLS)

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

Read Information from Excel file using IronXL

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

1. Install the IronXL Excel Library. We tin do this using our NuGet bundle or by downloading the .Net Excel DLL.
2. Utilise the WorkBook.Load method to read any XLS, XLSX or CSV document.
3. Go Cell values using intuitive syntax: sheet["A11"].DecimalValue

IronXL Includes:

• Defended production support from our .NET engineers
• Easy installation via Microsoft Visual Studio
• 30 twenty-four hour period free trial examination for development. Licenses from $399.

We will encounter how piece of cake it is to read Excel files in C# or VB.Internet using the IronXL library. The samples comprise 3 Excel sheets.

Read XLS or XLSX Files: Quick Code

In this example nosotros tin run across how to read the excel file efficiently without Interop in C#. The final Avant-garde Operations prove 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("examination.xlsx");
10. WorkSheet sheet = workbook.WorkSheets.Beginning();
11. //Select cells hands in Excel notation and render the calculated value
12. int cellValue = sheet["A2"].IntValue;
13. // Read from Ranges of cells elegantly.
14. foreach (var cell in canvass["A2:A10"])
15. {
16. Console.WriteLine("Jail cell {0} has value '{1}'", cell.AddressString, cell.Text);
17. }
19. ///Avant-garde 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#

Step 1

1. Download the IronXL C# Library for Gratuitous

Download DLL

Manually install into your project

Install with NuGet

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

The get-go thing we need to do is install the IronXL.Excel library, calculation Excel functionality to the .Internet framework.

Installing IronXL.Excel, is well-nigh easily achieved using our NuGet packet, although you may as well choose to manually install the DLL to your projection or to your global assembly enshroud.

Installing the IronXL Nuget Packet

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-Packet IronXL.Excel

PM > Install-Bundle IronXL.Excel

Direct Download Installation

Alternatively, we tin beginning past downloading the IronXL .NET Excel DLL and manually installing into Visual Studio.

How To Tutorials

2. Load a WorkBook

The WorkBook class represents an Excel canvas. To open an Excel File using C#, nosotros 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 sail contains worksheets, call up them by proper noun WorkBook.GetWorkSheet.

1. var worksheet = workbook.GetWorkSheet("GDPByCountry");

Copy code to clipboardVB C#

3. Create a WorkBook

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

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

Copy code to clipboardVB C#

Sample: ApiToExcelProcessor

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

iv. Create a WorkSheet

Each "WorkBook" can take multiple WorkSheets. A "WorkSheet" is a sheet of data, while a WorkBook represents a collection of WorkSheets. This is how ane workbook with 2 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");

Re-create lawmaking to clipboardVB C#

5. Get Prison cell Range

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

The statement text is either the coordinate of a cell (e.m. "A1") or a bridge of cells from left to right superlative to lesser (east.thousand. "B2:E5"). It is also possible to call GetRange on a WorkSheet.

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

Re-create lawmaking to clipboardVB C#

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, apply 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++)< li>
7. {
8. var outcome = new PersonValidationResult { Row = y };
9. results.Add together(result);
11. //Get all cells for the person
12. var cells = worksheet[$"A{y}:Due east{y}"].ToList();
14. //Validate the phone number (1 = B)
15. var phoneNumber = cells[one].Value;
16. result.PhoneNumberErrorMessage = ValidatePhoneNumber(phoneNumberUtil, (string)phoneNumber);
18. //Validate the e-mail address (3 = D)
19. outcome.EmailErrorMessage = ValidateEmailAddress((string)cells[3].Value);
21. //Go the raw date in the format of Month Day[suffix], Year (four = Eastward)
22. var rawDate = (string)cells[four].Value;
23. result.DateErrorMessage = ValidateDate(rawDate);
24. }

Copy lawmaking to clipboardVB C#

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.

1. /**
2. Validate Spreadsheet Data
3. anchor-validate-spreadsheet-data
4. **/
5. //Iterate through the rows
6. for (var i = ii; i <= 101; i++)< li>
7. {
8. var effect = new PersonValidationResult { Row = i };
9. results.Add(issue);
11. //Become 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 accost (3 = D)
19. result.EmailErrorMessage = ValidateEmailAddress((cord)cells[3].Value);
21. //Get the raw date in the format of Calendar 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 lawmaking loops through each row in the spreadsheet and grabs the cells every bit a list. Each validates method checks the value of a cell and returns an fault bulletin if the value is invalid.

This code creates a new sheet, specifies headers, and outputs the mistake bulletin 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 Mistake";
6. resultsSheet["D1"].Value = "Email Mistake";
7. resultsSheet["E1"].Value = "Engagement Error";
9. for (var i = 0; i < results.Count; i++)
10. {
11. var issue = results[i];
12. resultsSheet[$"A{i + ii}"].Value = result.Row;
13. resultsSheet[$"B{i + two}"].Value = outcome.IsValid ? "Yes" : "No";
14. resultsSheet[$"C{i + 2}"].Value = result.PhoneNumberErrorMessage;
15. resultsSheet[$"D{i + two}"].Value = event.EmailErrorMessage;
16. resultsSheet[$"E{i + 2}"].Value = result.DateErrorMessage;
17. }
19. workbook.SaveAs(@"Spreadsheets\PeopleValidated.xlsx");

Copy code to clipboardVB C#

8. Export Data using Entity Framework

Use IronXL to export data to a database or catechumen an Excel spreadsheet to a database. The ExcelToDB sample reads a spreadsheet with Gdp by country and so exports that data to an SQLite.

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

Add the SQLite Entity Framework NuGet packages.

EntityFramework allows you lot 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; gear up; }
7. }

Copy lawmaking to clipboardVB C#

To use a different database, install the corresponding NuGet parcel and notice 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 Countries { get; set; }
9. public CountryContext()
10. {
11. //TODO: Make async
12. Database.EnsureCreated();
13. }
15. ///
16. /// Configure context to use Sqlite
17. ///
18. ///
19. protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
20. {
21. var connection = new SqliteConnection($"Information Source=Country.db");
22. connection.Open();
24. var control = connection.CreateCommand();
26. //Create the database if it doesn't already be
27. command.CommandText = $"PRAGMA foreign_keys = ON;";
28. command.ExecuteNonQuery();
30. optionsBuilder.UseSqlite(connection);
32. base.OnConfiguring(optionsBuilder);
33. }
35. }

Re-create code to clipboardVB C#

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

1. public async Task ProcessAsync()
2. {
3. //Go the start 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 = ii; i <= 213; i++)< li>
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. Proper noun = (string)range[0].Value,
20. GDP = (decimal)(double)range[one].Value
21. };
23. //Add the entity
24. expect countryContext.Countries.AddAsync(land);
25. }
27. //Commit changes to the database
28. await countryContext.SaveChangesAsync();
29. }
30. }

Copy code to clipboardVB C#

9. Add together Formula to a Spreadsheet

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 jail cell
9. var jail cell = canvass[$"C{y}"].First();
11. //Fix the formula for the Percentage of Total column
12. cell.Formula = $"=B{y}/B{i}";
13. }

Copy code to clipboardVB C#

Sample: AddFormulaeProcessor

ten. Download Data from an API to Spreadsheet

The following call makes a Residual telephone call with RestClient.Internet. It downloads JSON and converts it into a "List" of the blazon RestCountry. It is then easy to iterate through each country and save the information 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. Listing countries = await client.GetAsync>();

Re-create lawmaking to clipboardVB C#

This is what the API JSON information looks like.

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

1. for (var i = two; i < countries.Count; i++)
2. {
3. var country = countries[i];
5. //Gear up 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 > (land.languages.Count – ane)) intermission;
16. var linguistic communication = country.languages[10];
18. //Get the letter for the column
19. var columnLetter = GetColumnLetter(4 + x);
21. //Fix the language name
22. worksheet[$"{columnLetter}{i}"].Value = language.name;
23. }
24. }

Copy code to clipboardVB C#

Object Reference and Resources

Summary

IronXL.Excel is lone .Net software library for reading a broad variety of spreadsheet formats. Information technology does not require Microsoft Excel to be installed, and is non dependant on Interop.

xi. Spotter the Read Excel File C# Tutorial Video

Data contained on this page is provided past an independent 3rd-party content provider. Frankly and this Site make no warranties or representations in connection therewith. If yous are affiliated with this page and would like information technology removed delight contact pressreleases@franklymedia.com

gentileconstainey.blogspot.com

Source: https://www.kten.com/story/45047198/c-read-excel-file-with-examples

Share :