using IronXL; using System; using System.Linq; // Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV WorkBook workBook = WorkBook.Load("sample.xlsx"); // Select worksheet at index 0 WorkSheet workSheet = workBook.WorkSheets[0]; // Get any existing worksheet WorkSheet firstSheet = workBook.DefaultWorkSheet; // Select a cell and return the converted value int cellValue = workSheet["A2"].IntValue; // Read from ranges of cells elegantly. foreach (var cell in workSheet["A2:A10"]) { Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text); } // Calculate aggregate values such as Min, Max and Sum decimal sum = workSheet["A2:A10"].Sum(); // Linq compatible decimal max = workSheet["A2:A10"].Max(c => c.DecimalValue);

using IronSoftware.Drawing; using IronXL; using IronXL.Styles; using System.Linq; WorkBook workBook = WorkBook.Load("sample.xls"); WorkSheet workSheet = workBook.WorkSheets.First(); var range = workSheet["A1:H10"]; var cell = range.First(); // Set background color of the cell with an rgb string cell.Style.SetBackgroundColor("#428D65"); // Apply styling to the whole range. // Set underline property to the font // FontUnderlineType is enum that stands for different types of font underlying range.Style.Font.Underline = FontUnderlineType.SingleAccounting; // Define whether to use horizontal line through the text or not range.Style.Font.Strikeout = false; // Define whether the font is bold or not range.Style.Font.Bold = true; // Define whether the font is italic or not range.Style.Font.Italic = false; // Get or set script property of a font // Font script enum stands for available options range.Style.Font.FontScript = FontScript.Super; // Set the type of the border line // There are also TopBorder,LeftBorder,RightBorder,DiagonalBorder properties // BorderType enum indicates the line style of a border in a cell range.Style.BottomBorder.Type = BorderType.MediumDashed; // Indicate whether the cell should be auto-sized range.Style.ShrinkToFit = true; // Set alignment of the cell range.Style.VerticalAlignment = VerticalAlignment.Bottom; // Set border color range.Style.DiagonalBorder.SetColor("#20C96F"); // Define border type and border direction as well range.Style.DiagonalBorder.Type = BorderType.Thick; // DiagonalBorderDirection enum stands for direction of diagonal border inside cell range.Style.DiagonalBorderDirection = DiagonalBorderDirection.Forward; // Set background color of cells range.Style.SetBackgroundColor(Color.Aquamarine); // Set fill pattern of the cell // FillPattern enum indicates the style of fill pattern range.Style.FillPattern = FillPattern.Diamonds; // Set the number of spaces to intend the text range.Style.Indention = 5; // Indicate if the text is wrapped range.Style.WrapText = true; workBook.SaveAs("stylingOptions.xls");

Published November 23, 2023

How to Load An Excel File in C#

C# Load Excel File

1.0 Introduction

Spreadsheet programs that resemble Microsoft Office Excel are available for Windows, macOS, iOS, Android, and iPadOS. A wide range of subjects are covered in the curriculum, such as conditional formatting rules, mathematical procedures, pivot tables, graphing tools, and Visual Basic for Applications (VBA) code, a macroprogramming language. Excel is one of the applications that comes with the Microsoft 365 software suite.

To organize operations like arithmetic calculations and other data manipulations, spreadsheets such as Excel use a grid of cells arranged in numbered rows and letter-named columns. In the areas of finance, statistics, and technology, it offers integrated capabilities to meet domain requirements. It may also display data as charts, line graphs, pivot tables, picture formats, and histograms. Furthermore, it provides a limited three-dimensional graphical presentation. Segmenting data can be used to illustrate how different things impact it from different angles.

1.1 How to Load Excel Files

Open Visual Studio and create a new Visual Studio project.
Download the IronXL NuGet package library.
Make an Excel workbook in XLSX file or CSV file format.
Now load the Excel file using the IronXL Library.
Process the Excel values using the cell range.

2.0 IronXL and Features

2.1 What is IronXL?

The C# .NET Excel library, IronXL, can be used to read data from and convert Microsoft Excel documents to CSV files. No Microsoft Office, Microsoft.Office.Interop.Excel, or Excel Interop installation is required for IronXL. It is a completely independent .NET Excel applications library. Numerous spreadsheet formats can be read by it. IronXL is not just a static library.

Excel spreadsheets in a .NET context may be easily generated, modified, and viewed thanks to IronXL's straightforward C# API. Core .NET apps, Linux, macOS, Azure, and Xamarin are all fully supported by IronXL. Below is a bulleted list of IronXL's key features that set it apart from other applications for reading Excel files.

2.1 Key Features of IronXL:

IronXL is one of the greatest C# libraries for Excel spreadsheets; it works with both .NET Core and .NET Framework.
Web applications, Windows Forms, Console, and practically every other .NET Framework are supported by IronXL.
Operating systems that IronXL is compatible with include Windows, macOS, and Linux.
Accessing Excel files is quick and simple with IronXL.
A large number of Excel file types, including XLSX, CSV, XLS, XLST, TSV, XLSM, and others, can be read by IronXL.
Among our many possibilities are the import, update, and export functions for data tables and datasets.
IronXL allows you to import information into Excel files from a variety of sources, including databases, data tables, arrays, and collections. Data from Excel can then be exported to CSV, HTML, PDF, and other formats.
IronXL can generate computations for the Excel spreadsheet.
Text, integers, dates, currencies, formulas, and percentages are just a few of the data types that IronXL can handle for Excel columns.
Performance for large Excel files and datasets is optimized to manage substantial amounts of data effectively, with enhanced performance in multi-threaded scenarios due to multithreading capability

With these features, IronXL becomes a flexible and strong library for working with Excel files, giving developers more convenience and flexibility when managing their Excel document-related tasks programmatically. To know more about the IronXL library, refer here.

3.0 Creating a .NET Core 6 Project

The IronXL library makes it easy to write data and create and read Excel files, as you will see in the upcoming sections of this newsletter.

First step, starting a fresh project to make Excel files:

3.1 Create a new project

Choose "New Project" from the "File" menu once Visual Studio has launched.

The .NET "Console App" project templates will appear in the dialog box that follows; select them and double click on "Next."

New Project #### 3.2 Configuring your Project ####

You can type any project name you want for the Project after entering the new project's location in the Location area. Press the Next button to continue.

Project Configuration #### 3.3 Selecting a Framework ####

With the Framework drop-down menu, you can choose a .NET Framework. The long-term supported version of .NET, 6.0, is being used here. Hit "Create" after that.

Framework Selection #### 3.4 Installing IronXL Library ####

The following solution requires the IronXL library, which you should obtain. To accomplish this, input the below source code, into the Package Manager:

Install-Package IronXL.Excel

IronXL Installation An alternative to this, would be to use the NuGet Package Manager to search for the package "IronXL".

Browsing should result in a list of all NuGet packages linked to IronXL. Of which we can select the one we need to download and click the install button.

NuGet Package Manager ## 4.0 Using IronXL to Read Excel Files ##

We can read Excel files in C# with the help of the IronXL. The following code allows us to load and read Excel files using C#.

using IronXL;
//Supported spreadsheet formats for reading include: XLSX, XLS, CSV and TSV
WorkBook workbook = WorkBook.Load("Demo.xlsx");
WorkSheet sheet = workbook.WorkSheets.First();
//Read from Ranges of cells elegantly.
foreach (var cell in sheet["A2:B10"])
{
    Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
}

using IronXL;
//Supported spreadsheet formats for reading include: XLSX, XLS, CSV and TSV
WorkBook workbook = WorkBook.Load("Demo.xlsx");
WorkSheet sheet = workbook.WorkSheets.First();
//Read from Ranges of cells elegantly.
foreach (var cell in sheet["A2:B10"])
{
    Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
}

Imports IronXL
'Supported spreadsheet formats for reading include: XLSX, XLS, CSV and TSV
Private workbook As WorkBook = WorkBook.Load("Demo.xlsx")
Private sheet As WorkSheet = workbook.WorkSheets.First()
'Read from Ranges of cells elegantly.
For Each cell In sheet("A2:B10")
	Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text)
Next cell

VB C#

4.1 Above Code Explained

The first line of code, 'using IronXL;' is called to implement the library into our project. This allows us to use the IronXL functions that are provided within the library for ease of use.

We then load the existing Excel file, named "Demo.xlsx", with the help of the method 'Load' which is available in the workbook object.

In the code, we are able to load the Excel file with three different methods; using the file name with the path, stream, and bytes. In the above example, we are use the file name with path method to load the file.

To perform the read Excel file operation, we need to specifically select the Excel sheet with the help of the worksheet collection object.

Then we select the Excel worksheet range with the worksheet object by passing the sheet cell range. Then it will read each of the cell values one by one (using 'foreach') and display the value with the cell address.

Output of Previous Code Example The above example demonstrates an Excel file in C# and the resulting output, which is loaded using IronXL. It utilizes Excel sheets with the assistance of the row and column index.

To know more about coding with IronXL, refer here.

5.0 Conclusion

IronXL is a well-liked Excel add-on that works without the need for any extra external libraries. It has several uses and doesn't need to have Microsoft Excel installed.

All programmable duties pertaining to Microsoft Excel documents can be fully resolved with IronXL. You can add and remove data, search and replace, combine and unmerge data tables or cells, sort strings or numbers, visualize a data table, and save files. Formula computations are also possible with IronXL. You can also set cell data types and examine spreadsheet data with it. The ability to read and write CSV files, which is obtainable with IronXL, is a necessity in order to work with Excel data.

5.1 Want to try it yourself?

Although IronXL offers a free trial, users can choose to upgrade to a paid membership for 1 year in order to receive updates and customer assistance. IronXL charges extra for security that allows for unfettered distribution. To find out more about the specifics of the pricing, see this page.