1. IronPDF
  2. IronPDF Blog
  3. Using IronPDF
  4. ASP PDF Creator

Published April 19, 2023

How to Create PDF in ASP .NET

MS Excel is a spreadsheet software developed by Microsoft. It is a data handling software which helps store and organize data in meaningful tabular form. It also helps present and analyze data in the form of charts, graphs, and tables. Many businesses use MS Excel files to store their financial data as it provides a bunch of useful financial formulas. Editing Microsoft Excel documents is also easy and it provides numerous editing options to visualize and conceptualize data. Working with Excel workbook files programmatically can be a challenging task in C#. Sometimes the businesses need to integrate Excel capabilities in their software applications. Here, we will discuss the IronXL software library to import and read excel files in C#.

IronXL - Excel Library

IronXL is a .NET Excel library that prioritizes ease of use, accuracy and speed for its users. It helps you to import and read Excel documents, create and edit an Excel file efficiently with lightning fast performance. It works without MS Office Interop. This means without excel installed, it provides all functionalities to read Excel files. This makes IronXL a powerful tool for developers to import and read excel files in C#.

IronXL is available on all platforms like Windows, Linux, MacOS, Docker, Azure and AWS. It is compatible with all .NET Framework. IronXL is a versatile library which can be integrated into Console Desktop, Web ASP.NET applications. It supports different workbook formats like XLS and XLSX file, XSLT and XLSM, CSV and TSV.

Some Important Features

  • Open, read excel file and search data from different spreadsheet formats like XLS/CSV/TSV/XLSX files.
  • Exporting Excel Worksheets to XLS/XLSX/CSV/TSV/JSON.
  • Encrypting and decrypting XLSM/XLTX/XLSX files with passwords.
  • Import Excel sheet as System.Data.DataSet and System.Data.DataTable objects.
  • Excel file formulas recalculated every time a sheet is edited.
  • Intuitive cell range settings with a WorkSheet["A1:B10"] easy syntax.
  • Sort Cell Ranges, Columns and Rows.
  • Styling Cells - Font, Font Size, Background color, Border, Alignment and Numbering formats.

How to Import Excel Workbook in C#?

Prerequisites

To use IronXL in C# to read excel file, we need to ensure the following components are installed on computer:

  1. Visual Studio - It is the official IDE for developing C#.NET applications. You can download and install Visual Studio from Microsoft website.
  2. IronXL - It is the library which helps to work with Excel sheets at a given path in C#. It must be installed in C# program before using it. IronXL can be downloaded from NuGet website or from Manage NuGet packages in Visual Studio tools. You can also download the .NET Excel DLL file directly.

Adding Necessary Namespaces

Once Visual Studio and IronXL is installed, we need to use IronXL assembly reference for using IronXL in the source code. Add the following line of code on top of the file within new project where IronXL functions will be used:

using IronXL;  
using IronXL;  
Imports IronXL
VB   C#

Open an Existing Excel file in C#

Microsoft Excel Spreadsheets are also referred to as Excel Workbook. Each workbook contains multiple worksheets and a single worksheet contains tabular cells with its value. To open and read an Excel file, it should be loaded using the WorkBook class and Load method present in the IronXL library. The code goes as follows:

//Supported Excel spreadsheet formats for reading include: XLSX, XLS, CSV and TSV 
WorkBook workbook = WorkBook.Load("test.xlsx");  
//Supported Excel spreadsheet formats for reading include: XLSX, XLS, CSV and TSV 
WorkBook workbook = WorkBook.Load("test.xlsx");  
'Supported Excel spreadsheet formats for reading include: XLSX, XLS, CSV and TSV 
Dim workbook As WorkBook = WorkBook.Load("test.xlsx")
VB   C#

This opens the Excel file in the workbook instance reference variable. As it can have multiple worksheets, it can be used to open a specific worksheet or all at once. The following code opens the first worksheet in sheet instance variable:

WorkSheet sheet = workbook.WorkSheets.First();  
WorkSheet sheet = workbook.WorkSheets.First();  
Dim sheet As WorkSheet = workbook.WorkSheets.First()
VB   C#

This will open the first sheet in the Excel file and now excel data can be read and written to this sheet.

Opened Excel File

Excel file

Read Data from Imported Excel File

Once the Excel file is imported, it is ready for reading data. Reading Excel file data in C# using IronXL is very simple and easy. You can read excel cell values by simply mentioning the cell reference number.

The code below retrieves the value of a cell with reference number "C2":

//Select cells easily in Excel-notation and return the value
int cellValue = sheet["C2"].IntValue; 

//Display the value
Console.WriteLine(cellValue);  
//Select cells easily in Excel-notation and return the value
int cellValue = sheet["C2"].IntValue; 

//Display the value
Console.WriteLine(cellValue);  
'Select cells easily in Excel-notation and return the value
Dim cellValue As Integer = sheet("C2").IntValue

'Display the value
Console.WriteLine(cellValue)
VB   C#

The output is as follows:

Read Excel

Now, let's read data from a range of cells in the opened Excel file. The code goes as follows:

// Read from Range of cells elegantly. 
foreach (var cell in sheet["A2:A6"]) 
{ 
  Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text); 
}  
// Read from Range of cells elegantly. 
foreach (var cell in sheet["A2:A6"]) 
{ 
  Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text); 
}  
' Read from Range of cells elegantly. 
For Each cell In sheet("A2:A6")
  Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text)
Next cell
VB   C#

The code is very simple, clean and clear. The range of cells can be referenced with simple syntax as shown in foreach loop: sheet["A2:A6"] and each cell can be iterated using a for loop to get its value. Here, you will see the names in column A from row 2 to row 6 on the console output:

Read Range of Cells

For more detail on reading and writing to cell values, check this tutorial read Excel file in C# example.

Import all Data from Excel File

IronXL can be used to read excel sheets at once using Rows and Columns indexes. The following IronXL code samples helps to get the entire excel files data in the same format on the console output:

WorkBook workbook = WorkBook.Load("test.xlsx");
WorkSheet sheet = workbook.WorkSheets.First(); 

// Traverse all rows of Excel WorkSheet 
for (int i = 0; i < sheet.Rows.Count(); i++) { 
  //Traverse all columns of specific Row 
  for (int j = 0; j < sheet.Columns.Count(); j++) {
     //Get the values string 
     val = sheet.Rows[i].Columns[j].Value.ToString();
     Console.Write("{0}\t", val);
  } 
  Console.WriteLine(); 
}  
WorkBook workbook = WorkBook.Load("test.xlsx");
WorkSheet sheet = workbook.WorkSheets.First(); 

// Traverse all rows of Excel WorkSheet 
for (int i = 0; i < sheet.Rows.Count(); i++) { 
  //Traverse all columns of specific Row 
  for (int j = 0; j < sheet.Columns.Count(); j++) {
     //Get the values string 
     val = sheet.Rows[i].Columns[j].Value.ToString();
     Console.Write("{0}\t", val);
  } 
  Console.WriteLine(); 
}  
Imports Microsoft.VisualBasic

Dim workbook As WorkBook = WorkBook.Load("test.xlsx")
Dim sheet As WorkSheet = workbook.WorkSheets.First()

' Traverse all rows of Excel WorkSheet 
For i As Integer = 0 To sheet.Rows.Count() - 1
  'Traverse all columns of specific Row 
  For j As Integer = 0 To sheet.Columns.Count() - 1
	 'Get the values string 
	 val = sheet.Rows(i).Columns(j).Value.ToString()
	 Console.Write("{0}" & vbTab, val)
  Next j
  Console.WriteLine()
Next i
VB   C#

Output File

Summary

In this article, we learned how to Import and read an Excel file in C# without any Microsoft Excel installed. Then we considered multiple ways to read data from an excel spreadsheet. IronXL also helps creating excel files in C# without any excel installed.

IronXL provides all in one solution for all MS Excel document related tasks to be implemented programmatically. You can perform formula calculation, string or number sorting, trimming and appending, find and replace, merge and unmerge, save files etc. You can edit cell values and also set cell data formats along with validate spreadsheet data. It also supports csv files and helps you to work like excel data.

IronXL is available for a free 30-day trial and can be licensed for commercial use with its Lite package starting from $749 only.