1. IronXL
2. IronXL Blog
3. Compare to Other Components
4. Gembox Spreadsheet

Published April 4, 2024

How to Read an Excel File in a C# Console Application

The ability to work with Excel files to generate reports and build databases has become essential for today's software applications. There are many libraries available that allow users to do this without the need for Microsoft Excel.

In this article, we are going to discuss how to work with Microsoft Excel documents programmatically in C#.NET using two of the most popular Excel Spreadsheet libraries: IronXL and GemBox.Spreadsheet.

IronXL and GemBox.Spreadsheet both provide methods to create, edit, and read Excel documents in .NET applications. So, how do you decide which one is best suited to your project? This article will compare both libraries and help you decide on the best option for your projects.

Let's first look at what both libraries have to offer, and then move on to the comparison itself.

IronXL Library

IronXL is a .NET C# library that facilitates reading and editing a wide range of spreadsheet formats. It does not require that Microsoft Excel be installed and it does not require Interop. IronXL fully supports .NET Core, .NET Framework, Xamarin, Mobile, Linux, macOS, and Azure.

IronXL Feature Set

• Loading, reading, and editing data — from XLS/XLSX/CSV/TSV
• Exporting and saving in different formats — to XLS/XLSX/CSV/TSV/JSON
• System.Data Objects — it works with Excel WorkBook Spreadsheets as System.Data.DataSet and System.Data.DataTable objects.
• Works with Formulas — works with Excel formulas. Formulas are recalculated every time a sheet is edited.
• Works with Ranges — its easy-to-use WorkSheet syntax allows you to create and combine ranges intuitively.
• Filtering and Sorting — it can sort ranges, columns, and rows.
• Styling Cells — visual styles, font, size, background pattern, border, alignment, and number formats.

GemBox.Spreadsheet

GemBox.Spreadsheet is a library that allows you to create, read, write, edit, convert, and print spreadsheet files in your .NET C# applications. It’s up to 250 times faster than Microsoft Excel automation!

GemBox.Spreadsheet API Features

• Read Excel files (XLSX, XLS, ODS), text files (CSV, TXT), and HTML files.
• Create spreadsheets and convert them to XLSX, XLS, ODS, CSV, TXT, HTML, PDF, PDF/A, XPS, and image formats.
• View and edit spreadsheets in WPF, ASP.NET, ASP.NET Core, and Windows.Forms applications.
• Apply cell formulas using its calculation engine.
• Create and edit sheets, rows, columns, cells, formulas, images, charts, shapes, text boxes, tables, cell styles and formatting, and pivot tables.
• Manipulate headers, footers, and document properties
• Edit cell groups, data outlining, data validation, sheet protection, and print/view options.
• Import and export spreadsheet data from/to a DataTable.
• Print Excel spreadsheets

The rest of this article continues as follows:

1. Create a Console Application
2. IronXL C# Library Installation
3. GemBox.Spreadsheet Installation
4. Create a New Excel Workbook
5. Reading Excel Files
6. Working with Excel Formulas
7. Inter Convert Files
8. Licensing
9. Conclusion

1. Create a Console Application

The following steps will help you create a console application:

• Download and install the latest Visual Studio IDE.
• Start Visual Studio and click on “Create new project.”

• Then, select Console App (.NET Framework) C# from the project templates displayed.

  

  Console Application

• Click Next.

• Here, specify the Framework version you would like to use. The latest one is recommended.

  

  Configure Project

• Click on Create to complete the process.

Now the project will be created and made ready to use.

2. IronXL C# Library Installation

IronXL library can be downloaded and installed using the following methods:

1. By using Visual Studio with NuGet Package Manager.
2. Download and install with the DLL Manually.

2.1. Visual Studio with NuGet Package Manager

Visual Studio 2022 provides the NuGet Package Manager to install NuGet packages in your projects. You can access it in multiple ways: through the Project Menu, or by right-clicking your project in the Solution Explorer.

Package Manager

• In the Browse tab, search for "IronXL.Excel" and click on the Install button to add the library
• After this process has been completed, IronXL has been successfully installed.

2.2. Download and Install with the DLL Manually

Another method to download and install IronXL Library is to use the NuGet Package Manager Console.

• Open the Developer Command Prompt console.

Type in the following command:

Install-Package IronXL.Excel

• Press the Enter key. This will download and install the package in your project automatically.

2.4. Include the IronXL Library

1. Open the Program.cs file.
2. Add the following line of code to the top of the code file:

using IronXL;

using IronXL;

Imports IronXL

3. GemBox.Spreadsheet Installation

To install the GemBox.Spreadsheet, download and run the setup installer. Alternatively, you can install GemBox.Spreadsheet through NuGet:

Access NuGet Package Manager through Solution Explorer and search for GemBox.Spreadsheet, or open the Developer Command Prompt Console and run the command below:

PM> Install-Package GemBox.Spreadsheet -Version 47.0.1418

Search GemBox.Spreadsheet

Next, open the program.cs file as before and add the lines of code below the using statement that we added in the previous section:

using Gembox.Spreadsheet;

// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

using Gembox.Spreadsheet;

// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

Imports Gembox.Spreadsheet

' If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

4. Create and Save a New Excel Workbook

An Excel Workbook can contains multiple worksheets. Both libraries provide the facility to create Excel workbooks containing one or more worksheets. Let's have a look at how each library accomplishes this.

4.1. New Excel Workbook and Sheets using IronXL

It is very easy to create a new Excel Workbook using IronXL!

Add the following code to your Program.cs file:

WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
var worksheet = workbook.CreateWorkSheet("IronXL Features");

WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
var worksheet = workbook.CreateWorkSheet("IronXL Features");

Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
Dim worksheet = workbook.CreateWorkSheet("IronXL Features")

IronXL supports all Excel spreadsheet versions (both .XLS and .XLSX).

Add the next line of code below to save the workbook to the computer:

//Save spreadsheet
workbook.SaveAs("NewExcelFile.xlsx");

//Save spreadsheet
workbook.SaveAs("NewExcelFile.xlsx");

'Save spreadsheet
workbook.SaveAs("NewExcelFile.xlsx")

4.2. New Excel Workbook and Sheets using GemBox.Spreadsheet

Creating Excel Workbooks is also very easy in GemBox, as shown in the following code snippet:

// Create a new empty Excel file.
var workbook = new ExcelFile();

// Create a new worksheet and set cell A1 value to 'Hello world!'.
workbook.Worksheets.Add("Sheet 1").Cells["A1"].Value = "Hello world!";

// Save to XLSX file.
workbook.Save("Spreadsheet.xlsx");

// Create a new empty Excel file.
var workbook = new ExcelFile();

// Create a new worksheet and set cell A1 value to 'Hello world!'.
workbook.Worksheets.Add("Sheet 1").Cells["A1"].Value = "Hello world!";

// Save to XLSX file.
workbook.Save("Spreadsheet.xlsx");

' Create a new empty Excel file.
Dim workbook = New ExcelFile()

' Create a new worksheet and set cell A1 value to 'Hello world!'.
workbook.Worksheets.Add("Sheet 1").Cells("A1").Value = "Hello world!"

' Save to XLSX file.
workbook.Save("Spreadsheet.xlsx")

5. Reading Excel Files

IronXL and GemBox are both capable or reading data from existing Excel files. Let's look at the sample code for each of the libraries one by one.

5.1. Reading Excel Files using IronXL

The WorkBook class in IronXL represents an Excel sheet. To open and read an Excel file in C#, we use WorkBook.Load and specify the exact path of the Excel file (.xlsx).

The following one-liner is used to open the file for reading:

//Load WorkBook
var workbook = WorkBook.Load(@"Spreadsheets\\sample.xlsx");

//Load WorkBook
var workbook = WorkBook.Load(@"Spreadsheets\\sample.xlsx");

'Load WorkBook
Dim workbook = WorkBook.Load("Spreadsheets\\sample.xlsx")

Remember, each WorkBook can have multiple WorkSheet objects. This represents worksheets in the Excel document. If the workbook contains multiple worksheets, retrieve them by name as follows:

//Open Sheet for reading
var worksheet = workbook.GetWorkSheet("sheetnamegoeshere");

//Open Sheet for reading
var worksheet = workbook.GetWorkSheet("sheetnamegoeshere");

'Open Sheet for reading
Dim worksheet = workbook.GetWorkSheet("sheetnamegoeshere")

We read data from each cell of the worksheet with the next lines of code:

// Read from Ranges of cells elegantly.
foreach (var cell in worksheet["A2:A10"])
{
    Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
}

// Read from Ranges of cells elegantly.
foreach (var cell in worksheet["A2:A10"])
{
    Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
}

' Read from Ranges of cells elegantly.
For Each cell In worksheet("A2:A10")
	Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text)
Next cell

5.2. Read Excel Files using GemBox.Spreadsheet

GemBox.Spreadsheet allows for quick reading of Excel files from your C# application. The following code illustrates how to open and read a file.

// Load Excel workbook from file's path.
ExcelFile workbook = ExcelFile.Load("SimpleTemplate.xlsx");

// Iterate through all worksheets in a workbook.
foreach (ExcelWorksheet worksheet in workbook.Worksheets)
{
// Display sheet's name.
Console.WriteLine("{1} {0} {1}\n", worksheet.Name, new string('#', 30));

       // Iterate through all rows in a worksheet.
       foreach (ExcelRow row in worksheet.Rows)
       {
            // Iterate through all allocated cells in a row.
            foreach (ExcelCell cell in row.AllocatedCells)
            {
                // Read cell's data.
                string value = cell.Value?.ToString() ?? "EMPTY";

                // Display cell's value and type.
                value = value.Length > 15 ? value.Remove(15) + "…" : value;
                Console.Write($"{value}[{cell.ValueType}]".PadRight(30));
            }

            Console.WriteLine();
        }
}

// Load Excel workbook from file's path.
ExcelFile workbook = ExcelFile.Load("SimpleTemplate.xlsx");

// Iterate through all worksheets in a workbook.
foreach (ExcelWorksheet worksheet in workbook.Worksheets)
{
// Display sheet's name.
Console.WriteLine("{1} {0} {1}\n", worksheet.Name, new string('#', 30));

       // Iterate through all rows in a worksheet.
       foreach (ExcelRow row in worksheet.Rows)
       {
            // Iterate through all allocated cells in a row.
            foreach (ExcelCell cell in row.AllocatedCells)
            {
                // Read cell's data.
                string value = cell.Value?.ToString() ?? "EMPTY";

                // Display cell's value and type.
                value = value.Length > 15 ? value.Remove(15) + "…" : value;
                Console.Write($"{value}[{cell.ValueType}]".PadRight(30));
            }

            Console.WriteLine();
        }
}

Imports Microsoft.VisualBasic

' Load Excel workbook from file's path.
Dim workbook As ExcelFile = ExcelFile.Load("SimpleTemplate.xlsx")

' Iterate through all worksheets in a workbook.
For Each worksheet As ExcelWorksheet In workbook.Worksheets
' Display sheet's name.
Console.WriteLine("{1} {0} {1}" & vbLf, worksheet.Name, New String("#"c, 30))

	   ' Iterate through all rows in a worksheet.
	   For Each row As ExcelRow In worksheet.Rows
			' Iterate through all allocated cells in a row.
			For Each cell As ExcelCell In row.AllocatedCells
				' Read cell's data.
				Dim value As String = If(cell.Value?.ToString(), "EMPTY")

				' Display cell's value and type.
				value = If(value.Length > 15, value.Remove(15) & "…", value)
				Console.Write($"{value}[{cell.ValueType}]".PadRight(30))
			Next cell

			Console.WriteLine()
	   Next row
Next worksheet

6. Working with Excel Formulas

Implementing formulas is one of the most important Excel features. Both libraries provide a powerful formula calculation engine. Formulas can be recalculated and saved to cells.

6.1. Working with Excel Formulas using IronXL

After the workbook and worksheet are loaded, the code below can be used to either make changes to formulas or apply new formulas to specific cells:

// Set Formulas
worksheet["A1"].Formula = "Sum(B8:C12)";
worksheet["B8"].Formula = "=C9/C11";
worksheet["G30"].Formula = "Max(C3:C7)";
 
// Force recalculate all formula values in all sheets.  
workbook.EvaluateAll();

// Set Formulas
worksheet["A1"].Formula = "Sum(B8:C12)";
worksheet["B8"].Formula = "=C9/C11";
worksheet["G30"].Formula = "Max(C3:C7)";
 
// Force recalculate all formula values in all sheets.  
workbook.EvaluateAll();

' Set Formulas
worksheet("A1").Formula = "Sum(B8:C12)"
worksheet("B8").Formula = "=C9/C11"
worksheet("G30").Formula = "Max(C3:C7)"
'
' Force recalculate all formula values in all sheets.  
workbook.EvaluateAll()

You can also retrieve formulas and their values:

// Get the formula's calculated value.  e.g. "52"
string formulaValue = worksheet["G30"].Value;
 
//Get the formula as a string. e.g. "Max(C3:C7)"
string formulaString = worksheet["G30"].Formula;

//Save your changes with updated formulas and calculated values.
workbook.Save();

// Get the formula's calculated value.  e.g. "52"
string formulaValue = worksheet["G30"].Value;
 
//Get the formula as a string. e.g. "Max(C3:C7)"
string formulaString = worksheet["G30"].Formula;

//Save your changes with updated formulas and calculated values.
workbook.Save();

' Get the formula's calculated value.  e.g. "52"
Dim formulaValue As String = worksheet("G30").Value
'
'Get the formula as a string. e.g. "Max(C3:C7)"
Dim formulaString As String = worksheet("G30").Formula

'Save your changes with updated formulas and calculated values.
workbook.Save()

6.2. Working with Excel Formulas using GemBox.Spreadsheet

GemBox.Spreadsheet provides facility to a large set of Excel formula functions, including Mathematical, Statistical, Logical, Lookup, Financial and many more. Let's look at how to use them.

// Set Formulas 
worksheet.Cells["A2"].Value = "=1 + 1";
worksheet.Cells["A3"].Value = "=3 * (2 - 8)";
worksheet.Cells["A10"].Value = "=SIGN(B9)";
worksheet.Cells["A11"].Value = "=SUM(B2:B10)";

// Excel worksheet calculation,
worksheet.Calculate();

// whole Excel file calculation.
worksheet.Parent.Calculate();

workbook.Save("Formula Calculation.xlsx");

// Set Formulas 
worksheet.Cells["A2"].Value = "=1 + 1";
worksheet.Cells["A3"].Value = "=3 * (2 - 8)";
worksheet.Cells["A10"].Value = "=SIGN(B9)";
worksheet.Cells["A11"].Value = "=SUM(B2:B10)";

// Excel worksheet calculation,
worksheet.Calculate();

// whole Excel file calculation.
worksheet.Parent.Calculate();

workbook.Save("Formula Calculation.xlsx");

' Set Formulas 
worksheet.Cells("A2").Value = "=1 + 1"
worksheet.Cells("A3").Value = "=3 * (2 - 8)"
worksheet.Cells("A10").Value = "=SIGN(B9)"
worksheet.Cells("A11").Value = "=SUM(B2:B10)"

' Excel worksheet calculation,
worksheet.Calculate()

' whole Excel file calculation.
worksheet.Parent.Calculate()

workbook.Save("Formula Calculation.xlsx")

7. Inter Convert files — (Excel, XLS, XLSX, XLSb, XLSM, XPS Data Interchange)

Both IronXL and GemBox.Spreadsheet provide the facility to convert between different spreadsheet file types.

7.1. Convert Spreadsheet Types using IronXL

CSV to Excel Formats

WorkBook workbook = WorkBook.LoadCSV("test.csv", fileFormat: ExcelFileFormat.XLSX, ListDelimiter: ",");
WorkSheet ws = workbook.DefaultWorkSheet;
 
workbook.SaveAs("CsvToExcelConversion.xlsx");

WorkBook workbook = WorkBook.LoadCSV("test.csv", fileFormat: ExcelFileFormat.XLSX, ListDelimiter: ",");
WorkSheet ws = workbook.DefaultWorkSheet;
 
workbook.SaveAs("CsvToExcelConversion.xlsx");

Dim workbook As WorkBook = WorkBook.LoadCSV("test.csv", fileFormat:= ExcelFileFormat.XLSX, ListDelimiter:= ",")
Dim ws As WorkSheet = workbook.DefaultWorkSheet
'
workbook.SaveAs("CsvToExcelConversion.xlsx")

XLSX file to XLSM

WorkBook workbook = WorkBook.Load("test.xlsx");
 
//This is how you can export workbook to .xlsm format
workbook.SaveAs("test.xlsm");

WorkBook workbook = WorkBook.Load("test.xlsx");
 
//This is how you can export workbook to .xlsm format
workbook.SaveAs("test.xlsm");

Dim workbook As WorkBook = WorkBook.Load("test.xlsx")
'
'This is how you can export workbook to .xlsm format
workbook.SaveAs("test.xlsm")

Excel to HTML with options

WorkBook workbook = WorkBook.Load("test.xlsx");
 
var options = new HtmlExportOptions()
{
    //This is how we can make row/column numbers visible in html document
    OutputRowNumbers = true,
    OutputColumnHeaders = true,
    OutputHiddenColumns = true,
 
    //This is how we can make hidden rows/columns visible in html document
    OutputHiddenRows = true,
    OutputLeadingSpacesAsNonBreaking = true
};
 
//This is how we can export workbook to the HTML file
workbook.ExportToHtml("workbook.html",options);

WorkBook workbook = WorkBook.Load("test.xlsx");
 
var options = new HtmlExportOptions()
{
    //This is how we can make row/column numbers visible in html document
    OutputRowNumbers = true,
    OutputColumnHeaders = true,
    OutputHiddenColumns = true,
 
    //This is how we can make hidden rows/columns visible in html document
    OutputHiddenRows = true,
    OutputLeadingSpacesAsNonBreaking = true
};
 
//This is how we can export workbook to the HTML file
workbook.ExportToHtml("workbook.html",options);

Dim workbook As WorkBook = WorkBook.Load("test.xlsx")
'
Dim options = New HtmlExportOptions() With {
	.OutputRowNumbers = True,
	.OutputColumnHeaders = True,
	.OutputHiddenColumns = True,
	.OutputHiddenRows = True,
	.OutputLeadingSpacesAsNonBreaking = True
}
'
'This is how we can export workbook to the HTML file
workbook.ExportToHtml("workbook.html",options)

7.2. Convert Spreadsheet Types using GemBox.Spreadsheet

XLSX file to PDF

ExcelFile workbook = ExcelFile.Load("ComplexTemplate.xlsx");
workbook.Save("Convert.pdf", new PdfSaveOptions(){SelectionType = SelectionType.EntireFile});

ExcelFile workbook = ExcelFile.Load("ComplexTemplate.xlsx");
workbook.Save("Convert.pdf", new PdfSaveOptions(){SelectionType = SelectionType.EntireFile});

Dim workbook As ExcelFile = ExcelFile.Load("ComplexTemplate.xlsx")
workbook.Save("Convert.pdf", New PdfSaveOptions() With {.SelectionType = SelectionType.EntireFile})

XLSX file to images

// Load an Excel file into the ExcelFile object.
var workbook = ExcelFile.Load("CombinedTemplate.xlsx");

// Create image save options.
var imageOptions = new ImageSaveOptions(ImageSaveFormat.Png)
{
    PageNumber = 0, // Select the first Excel page.
    Width = 1240, // Set the image width.
    CropToContent = true // Export only the sheet's content.
};

// Save the ExcelFile object to a PNG file.
workbook.Save("Output.png", imageOptions);

// Load an Excel file into the ExcelFile object.
var workbook = ExcelFile.Load("CombinedTemplate.xlsx");

// Create image save options.
var imageOptions = new ImageSaveOptions(ImageSaveFormat.Png)
{
    PageNumber = 0, // Select the first Excel page.
    Width = 1240, // Set the image width.
    CropToContent = true // Export only the sheet's content.
};

// Save the ExcelFile object to a PNG file.
workbook.Save("Output.png", imageOptions);

' Load an Excel file into the ExcelFile object.
Dim workbook = ExcelFile.Load("CombinedTemplate.xlsx")

' Create image save options.
Dim imageOptions = New ImageSaveOptions(ImageSaveFormat.Png) With {
	.PageNumber = 0,
	.Width = 1240,
	.CropToContent = True
}

' Save the ExcelFile object to a PNG file.
workbook.Save("Output.png", imageOptions)

CSV file to Excel

ExcelFile.Load("CSVTemplate.csv").Save("Convert.xlsx");

ExcelFile.Load("CSVTemplate.csv").Save("Convert.xlsx");

ExcelFile.Load("CSVTemplate.csv").Save("Convert.xlsx")

Similarly, you can convert between XLS, XLSX, ODS, CSV, and HTML files from your C# applications using GemBox.Spreadsheet.

8. Licensing

IronXL is a commercial C# Excel library. It is absolutely free for development purposes, but it must be licensed for commercial deployment. All IronXL Licensesare available for use with a single project, single developers, agencies, and global corporations, with or without SaaS and OEM redistribution. Each license includes a 30-day money-back guarantee, along with one year of product support and updates, validity for dev/staging/production, and a perpetual license (one-time purchase). The lite license starts at $749.

GemBox.Spreadsheet is also a commercial library. It can be used for free in both personal and commercial applications. However, it can also be licensed for professional use. Its licenses include usage for individual developers, small teams, and large companies. It provides royalty-free deployment (no server or OEM licenses), 18 months of free technical support, and free bug fixes and new releases. GemBox is under no obligation to provide technical or any other support to the users of the free version. The single-developer package starts from $890.

9. Conclusion

IronXL is a complete library offering everything you need to manipulate an Excel file. It is easy to use, providing the ability to convert spreadsheets between multiple file types. This interconversion allows users the flexibility to work with other file formats with ease.

GemBox.Spreadsheet is a .NET component library that helps you to create, read, write, edit, convert, and print spreadsheet files from your .NET C# applications using only one simple API. Excel files can be converted to and from different formats with ease. It is easy and fast to work with.

Overall, IronXL and GemBox.Spreadsheet are neck-and-neck in terms of dealing with Excel files. However, in several instances, IronXL was quicker and required only one line of code to achieve a task. It also enjoys a slight advantage over GemBox.Spreadsheet in respect of certain technical aspects and pricing. IronXL provides all the features and facilities of GemBox at just $749 for a single developer, whereas GemBox starts from $890. If you compare the IronXL professional and unlimited packages to the GemBox small team and large team packages, you will notice a massive price difference. The IronXL unlimited package ($2,999) is cheaper than GemBox.Spreadsheet's small team package ($4,450).

Try the free trial to see the efficiency of IronXL. Purchase the complete Iron Suite to get four additional libraries for the price of just two IronXL licenses. You could save up to 60% with the Iron Suite Unlimited License, which is still only half the price of the GemBox.Spreadsheet large team package ($13,350).