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 14, 2023

.NET Excel Library (Developer Tutorial)

1.0 Introduction

Spreadsheet programs like Microsoft Office Excel are available for Windows, macOS, iOS, Android, and iPadOS. Pivot tables, graphing tools, mathematical approaches, conditional formatting rules, and the macroprogramming language Visual Basic for Applications (VBA code) are all covered in the curriculum. Excel is one of the applications included in the Microsoft 365 software suite. A grid of cells arranged in numbered rows and letter-named columns is used by spreadsheets such as Excel to organize data manipulations, and Excel operations including arithmetic calculations. It offers integrated capability to meet financial, statistical, and technological domain requirements. It can also display data as charts, line graphs, pivot tables, image formats, and histograms and has a limited three-dimensional graphical presentation. Data can be segmented to show how various factors affect it from various perspectives.

In this article, one of the most popular libraries, IronXL, an Excel library will be used to compare and contrast how to interact with Microsoft Excel documents programmatically in .NET technologies and it will create an Excel spreadsheet environment to read Excel worksheets, write Excel tables, and export spreadsheet files to CSV.

2.0 IronXL- Fast Excel Library Features

Documents created in Microsoft Excel can be read and converted to CSV files using the C# IronXL, .NET Excel library. IronXL is a standalone .NET Excel software library that doesn't need Microsoft Office or Microsoft.Office.Interop.Excel or Excel Interop to be installed. It is able to read several spreadsheet formats. It is not just a static library.

IronXL's simple C# API makes it simple to read multiple worksheets, and modify, and generate Excel spreadsheets in a .NET environment. IronXL completely supports Xamarin, Linux, macOS, Azure, .NET Core applications, and .NET Framework.

IronXL, a C# library compatible with both .NET Core and .NET Framework, is among the best for Excel spreadsheets.
IronXL supports almost every .NET Framework, including Web apps, Windows Forms, and Console.
IronXL can be used with Linux, macOS, and Windows operating systems.
IronXL has quick and easy access to Excel files.
IronXL is capable of reading a wide range of Excel file types, including XLSX, CSV, XLS, XLST, TSV, XLSM, and more. The functions for importing, updating, and exporting data tables and datasets are only a few of our numerous options.
For the Excel spreadsheet, IronXL is capable of producing calculations.
IronXL supports numerous data types for Excel columns, including text, integers, dates, currencies, formulae, and percentages.
IronXL can handle multiple values in the form of dates, currencies, percentages, text, numbers, formulas, and more Excel column data types.

3.0 Creating a .NET Core 6 Project

The next sections of this newsletter will show you how simple it is to Create/Read an Excel file with the IronXL library.

Step 1: Launching a new endeavor to create Excel files.

After starting Visual Studio, select "New Project" from the "File" menu.

In the resulting dialogue box, select the "Console App" .NET Project templates, then click "Next."

.NET Excel Library (Developer Tutorial): Figure 1 - New Project

Once the new project's location has been entered in the Location field, you may enter any project name you choose for the Project. Click the Next button to proceed.

.NET Excel Library (Developer Tutorial): Figure 2 - Project Configuration

A .NET Framework can be selected using the Framework drop-down option. Here, we're using .NET 6.0, which is the long-term supported version. Then press Create.

.NET Excel Library (Developer Tutorial): Figure 3 - Framework Selection

Get the IronXL library, which is required for the following solution. To do this, enter the following code into the Package Manager:


    Install-Package IronXL.Excel


    Install-Package IronXL.Excel

'INSTANT VB TODO TASK: The following line uses invalid syntax:
'Install-Package IronXL.Excel

VB C#

.NET Excel Library (Developer Tutorial): Figure 4 - IronXL Installation

A different option is to use the NuGet Package Manager to look for the package "IronXL." We can choose the necessary package to download from this list of all the NuGet packages connected to IronXL.

.NET Excel Library (Developer Tutorial): Figure 5 - NuGet Package Manager

4.0 Create Excel Using IronXL

Using IronXL building a new Excel Workbook couldn't be easier! There is just one code line required for writing Excel files. IronXL can be used to create files in the XLS (an older version of Excel) and XLSX (a current and newer version) file formats as shown in the code samples below.


    using IronXL;
    WorkBook wb = WorkBook.Create();
    WorkSheet ws = wb.CreateWorkSheet("Sheet1");
    ws["A1"].Value = "test";
    wb.SaveAs("sample1.xlsx");


    using IronXL;
    WorkBook wb = WorkBook.Create();
    WorkSheet ws = wb.CreateWorkSheet("Sheet1");
    ws["A1"].Value = "test";
    wb.SaveAs("sample1.xlsx");

Imports IronXL
	Private wb As WorkBook = WorkBook.Create()
	Private ws As WorkSheet = wb.CreateWorkSheet("Sheet1")
	Private ws("A1").Value = "test"
	wb.SaveAs("sample1.xlsx")

VB C#

To access all functions, the code above calls the IronXL package first. The WorkBook is then used to generate a workbook using the Create() method. The name inside the brackets is used to create a worksheet tab using the wb.CreateWorkSheet("Sheet1") function after the workbook has been created. Next, a password is specified using the Protect Sheet function for that particular worksheet tab. Following the aforementioned procedures, the modifications are stored in a designated area.

.NET Excel Library (Developer Tutorial): Figure 6 - Excel Output

To learn more about creating Excel check here.

5.0 Read Data From Excel and Export to an Excel File

Exporting data to the .XLSX or .XLS formats only requires a few lines of code. Below is an example of source code that can be used to export data from an Excel file into a simple tabular format:


    var workbook=IronXL.WorkBook.LoadExcel("Demo file.xlsx");
    WorkSheet ws = workbook.GetWorkSheet("Sheet1");
    string address_val = ws["A1"].ToString();
    console.writeline(address_val);
    ws["A2"].Value = "test";
    workbook.SaveAs("export.xlsx");
    //or
    workbook.SaveAs("export.xls");
    //or
    workbook.WorkSheets[0].SaveAs("export.xls");


    var workbook=IronXL.WorkBook.LoadExcel("Demo file.xlsx");
    WorkSheet ws = workbook.GetWorkSheet("Sheet1");
    string address_val = ws["A1"].ToString();
    console.writeline(address_val);
    ws["A2"].Value = "test";
    workbook.SaveAs("export.xlsx");
    //or
    workbook.SaveAs("export.xls");
    //or
    workbook.WorkSheets[0].SaveAs("export.xls");

Dim workbook=IronXL.WorkBook.LoadExcel("Demo file.xlsx")
	Dim ws As WorkSheet = workbook.GetWorkSheet("Sheet1")
	Dim address_val As String = ws("A1").ToString()
	console.writeline(address_val)
	ws("A2").Value = "test"
	workbook.SaveAs("export.xlsx")
	'or
	workbook.SaveAs("export.xls")
	'or
	workbook.WorkSheets(0).SaveAs("export.xls")

VB C#

First, an Excel file that already exists is loaded in the preceding example using the 'LoadExcel' method, which allows us to specify the file name and file path as an argument. The file has now been imported into the 'Workbook' object. Then we load the Excel worksheets with the help of the GetWorkSheet which allows us to load the worksheet using the sheetname. Then we read the value using the Excel address. To know more about reading Excel files click here.

Also, we can modify the Excel sheet values by using the same Excel address. We may use the 'SaveAs' function offered by the 'Workbook' object to save the Excel document as an XLSX or XLS file. This method saves the full file in the chosen format.

.NET Excel Library (Developer Tutorial): Figure 7 - Console Output

Furthermore, we can select a certain Excel worksheet by referring to it by name or by using its index value. Next, we can use the 'SaveAs' option to export the data from the Excel spreadsheet to a separate file. To learn more about export Excel files click here.

Conclusion

IronXL is one of the most widely used Excel add-ons that doesn't require any other external libraries. It can be used in several ways and doesn't need to have Microsoft Excel installed.

For every programmable activity pertaining to MS Excel documents, IronXL offers a comprehensive solution. It is possible to perform formula calculations, sort strings or numbers, cut and add data, search and replace, merge and unmerge cells, visualize data, and save files. With it, you can also set cell data types and validate spreadsheet data. The ability to read and write CSV files enables interaction with Excel data.

IronXL costs $499 upon launch, but customers can opt to pay a one-year membership fee for improvements and product support. IronXL levies an additional fee for security that permits unrestricted redistribution. Click this link to learn more about the specifics of the pricing.