How to Read Excel File in C# Web Application

This tutorial explains how to read an Excel file in C# and using libraries for everyday tasks similar 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 piece of cake using the IronXL software library.

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

Overview

Read Excel files in C# past 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 .Internet library that facilitates reading and editing Microsoft Excel documents with C#. This tutorial will walk yous through using C abrupt code to read the Excel file.

1. Install the IronXL Excel Library. Nosotros can practice this using our NuGet package or by downloading the .Cyberspace Excel DLL.
2. Apply the WorkBook.Load method to read whatsoever XLS, XLSX or CSV document.
3. Become Prison cell values using intuitive syntax: sheet["A11"].DecimalValue

IronXL Includes:

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

We will meet how piece of cake information technology is to read Excel files in C# or VB.Net using the IronXL library. The samples comprise three Excel sheets.

Read XLS or XLSX Files: Quick Lawmaking

In this instance nosotros tin see how to read the excel file efficiently without Interop in C#. The final Avant-garde Operations evidence 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("exam.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("Jail cell {0} has value '{1}'", cell.AddressString, prison 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 uniform
24. decimal max = sheet["A2:A10"].Max(c => c.DecimalValue);

Re-create code to clipboardVB C#

Step 1

one. Download the IronXL C# Library for Complimentary

Download DLL

Manually install into your project

Install with NuGet

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

The first thing we demand to practice is install the IronXL.Excel library, adding Excel functionality to the .Cyberspace framework.

Installing IronXL.Excel, is virtually easily achieved using our NuGet package, although you may also choose to manually install the DLL to your project or to your global assembly enshroud.

Installing the IronXL Nuget Bundle

1. In Visual Studio, correct-click on the project select "Manage Nuget Packages …"
2. Search for the IronXL.Excel bundle and install

Another way to install is:

1. Enter the Bundle Managing director Console
2. Type > Install-Packet IronXL.Excel

PM > Install-Parcel IronXL.Excel

Direct Download Installation

Alternatively, we tin can start by 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 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\Gross domestic product.xlsx");

Copy code to clipboardVB C#

Sample: ExcelToDBProcessor

Each WorkBook can accept multiple WorkSheet objects. These stand for worksheets in the Excel certificate. If the sheet contains worksheets, recall them by proper noun WorkBook.GetWorkSheet.

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

Copy lawmaking to clipboardVB C#

3. Create a WorkBook

To create a new WorkBook in retentiveness, 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: Apply ExcelFileFormat.XLS for legacy for Microsoft Excel spreadsheets (95 and earlier).

4. Create a WorkSheet

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

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

1. var worksheet = workbook.CreateWorkSheet("Countries");

Re-create lawmaking to clipboardVB C#

5. Become Cell Range

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

The argument text is either the coordinate of a cell (e.chiliad. "A1") or a span of cells from left to right top to bottom (east.g. "B2:E5"). Information technology is as well possible to call GetRange on a WorkSheet.

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

Copy code to clipboardVB C#

six. Edit Cell Values Inside a Range

There are several means 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. ballast-edit-cell-values-within-a-range
4. **/
5. //Iterate through the rows
6. for (var y = two; y <= 101; y++)< li>
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 telephone number (1 = B)
15. var phoneNumber = cells[ane].Value;
16. issue.PhoneNumberErrorMessage = ValidatePhoneNumber(phoneNumberUtil, (string)phoneNumber);
18. //Validate the email address (3 = D)
19. upshot.EmailErrorMessage = ValidateEmailAddress((string)cells[3].Value);
21. //Become the raw date in the format of Calendar month Twenty-four hour period[suffix], Year (4 = E)
22. var rawDate = (cord)cells[4].Value;
23. result.DateErrorMessage = ValidateDate(rawDate);
24. }

Copy lawmaking to clipboardVB C#

7. Validate Spreadsheet Data

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

1. /**
2. Validate Spreadsheet Data
3. anchor-validate-spreadsheet-data
4. **/
5. //Iterate through the rows
6. for (var i = two; i <= 101; i++)< li>
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}:East{i}"].ToList();
14. //Validate the phone number (one = B)
15. var phoneNumber = cells[ane].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 appointment in the format of Calendar month Day[suffix], Twelvemonth (4 = E)
22. var rawDate = (string)cells[4].Value;
23. result.DateErrorMessage = ValidateDate(rawDate);
24. }

Copy lawmaking to clipboardVB C#

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

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

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

Copy lawmaking to clipboardVB C#

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 and then exports that data to an SQLite.

It uses EntityFramework to build the database and then consign 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 form Country
2. {
3. [Central]
4. public Guid Central { go; set; }
5. public string Proper noun { get; set; }
6. public decimal Gross domestic product { go; ready; }
7. }

Re-create code to clipboardVB C#

To use a unlike 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 course CountryContext : DbContext
6. {
7. public DbSet Countries { get; set; }
9. public CountryContext()
10. {
11. //TODO: Brand async
12. Database.EnsureCreated();
13. }
15. ///
16. /// Configure context to apply Sqlite
17. ///
18. ///
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. }

Re-create code to clipboardVB C#

Create a CountryContext, iterate through the range to create each record, so 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++)< li>
12. {
13. //Go the range from A-B
14. var range = worksheet[$"A{i}:B{i}"].ToList();
16. //Create a State entity to be saved to the database
17. var country = new State
18. {
19. Name = (string)range[0].Value,
20. GDP = (decimal)(double)range[ane].Value
21. };
23. //Add together the entity
24. wait countryContext.Countries.AddAsync(state);
25. }
27. //Commit changes to the database
28. look countryContext.SaveChangesAsync();
29. }
30. }

Copy lawmaking to clipboardVB C#

ix. Add together Formula to a Spreadsheet

The code beneath iterates through each land and puts a per centum full in column C.

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

Re-create code to clipboardVB C#

Sample: AddFormulaeProcessor

10. Download Data from an API to Spreadsheet

The following telephone call makes a Remainder call with RestClient.Net. It downloads JSON and converts it into a "List" of the type RestCountry. Information technology is then easy to iterate through each state and save the information from the Remainder API to an Excel spreadsheet.

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

Copy lawmaking to clipboardVB C#

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 iii Languages in the spreadsheet.

1. for (var i = ii; i < countries.Count; i++)
2. {
3. var country = countries[i];
5. //Set up the basic values
6. worksheet[$"A{i}"].Value = country.name;
7. worksheet[$"B{i}"].Value = state.population;
8. worksheet[$"Grand{i}"].Value = country.region;
9. worksheet[$"H{i}"].Value = country.numericCode;
11. //Iterate through languages
12. for (var x = 0; x < three; x++)
13. {
14. if (x > (country.languages.Count – ane)) suspension;
16. var language = land.languages[x];
18. //Get the alphabetic character for the column
19. var columnLetter = GetColumnLetter(4 + x);
21. //Gear up the language proper noun
22. worksheet[$"{columnLetter}{i}"].Value = language.name;
23. }
24. }

Copy lawmaking to clipboardVB C#

Object Reference and Resources

Summary

IronXL.Excel is lone .Internet software library for reading a wide variety of spreadsheet formats. It does not crave Microsoft Excel to exist installed, and is non dependant on Interop.

xi. Watch the Read Excel File C# Tutorial Video

Information independent on this page is provided past an independent third-party content provider. Frankly and this Site make no warranties or representations in connection therewith. If you are affiliated with this page and would like information technology removed please contact pressreleases@franklymedia.com

murraytheld1983.blogspot.com

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

Share this post