How to Work with Excel Files in C#

Introduction

Working with Microsoft Excel files in programming languages has become a significant requirement in today's modern programming paradigm. Excel generation is required for automation, data analysis, measuring productivity, reports, etc. IronXL from Iron Software is a powerful C# library that allows developers to read, write, and manipulate Excel documents without needing Microsoft Excel installed on the machine in any application like web services, console, desktop, etc. This makes it an excellent choice for applications that need to handle Excel data efficiently and effectively. In this article, we’ll explore how to use IronXL to work with Excel files in C#.

How to work with Excel workbook in C#

1. Create a Visual Studio project and add the IronXL NuGet package.
2. Create an Excel file without Interop.
3. Add Style to Excel file using IronXL.
4. Read values from Excel and calculate.
5. Convert Excel to HTML for web usage.

Introducing IronXL Library

IronXL is a robust library for .NET that simplifies working with Excel files. IronXL is a powerful .NET library designed for creating, reading, and editing Excel files effortlessly. It features an intuitive API that simplifies working with Excel documents, supporting various formats like XLS, XLSX, and CSV. This versatility allows for easy manipulation of cell values, formulas, and formatting. IronXL is optimized for performance, and capable of efficiently handling large files and complex data operations while ensuring efficient memory usage. Its cross-platform compatibility enhances its utility, making IronXL an invaluable tool for developers across different operating systems. Here are some of its key features and benefits:

Key Features

1. Import and Export: 1. Import Data: Supports XLS, XLSX, CSV, and TSV formats.

2. Export Data: Can export worksheets to XLS, XLSX, CSV, TSV, and JSON formats.

3. Data Manipulation:

4. System.Data Integration: Work with spreadsheets as `System.Data.DataSet` and `System.Data.DataTable` objects.
5. Formulas: Supports Excel formulas, which are recalculated every time a sheet is edited.

6. Styling and Formatting:

7. Cell Styling: Customize font, size, background pattern, border, alignment, and number formats.
8. Ranges: Intuitive range setting with `WorkSheet["A1:B10"]` syntax.
9. Security:

Encryption: Encrypt and decrypt XLSX, XLSM, and XLTX files with passwords.5. Cross Platform Compatibility: 1. Works with .NET Framework, .NET Core, .NET Standard, and Azure.2. Compatible with Windows, macOS, Linux, Docker, Azure, and AWS.

Benefits

1. No Need for Microsoft Office: IronXL does not require Microsoft Office to be installed, making it lightweight and easy to deploy.
2. Ease of Use: The API is intuitive and easy to use, allowing developers to quickly integrate Excel functionality into their applications.3. Performance: IronXL is optimized for performance, ensuring fast and efficient processing of large Excel files.
3. Versatility: Suitable for a wide range of applications, including web, desktop, and cloud-based solutions.5. Comprehensive Documentation: Extensive documentation and examples are available, making it easier for developers to get started and find solutions to common problems.

Getting Started

To use IronXL in your .NET projects, you need to ensure that your development environment meets the following prerequisites:

Prerequisites

1. .NET Framework: IronXL supports .NET Framework 4.5 and above .NET technologies.
2. .NET Core and .NET Standard: Compatible with .NET Core 2, 3, 5, 6, 7, 8 and 9. Supports .NET Standard 2.0 and 2.1.
3. Operating Systems: Works on Windows, macOS, and Linux. Compatible with Docker, Azure, and AWS environments.
4. No Need for Microsoft Office: IronXL does not require Microsoft Office or Excel Interop to be installed.
5. Code editor: Any visual c# code editor like Visual Studio etc.

Installation

You can install IronXL via NuGet Package Manager in Visual Studio or using the Package Manager Console with the following command:

dotnet add package IronXL.Excel --version 2024.8.5

dotnet add package IronXL.Excel --version 2024.8.5

'INSTANT VB TODO TASK: The following line uses invalid syntax:
'dotnet add package IronXL.Excel --version 2024.8.5

How to work with Excel workbook in C#

To get started with working on an Excel file, first, let's develop a Visual Studio project and add the IronXL library.

Step 1: Create a Visual Studio Project and Add IronXL NuGet Package

Open Microsoft Visual Studio and select the 'Create a new project' option to get started with creating the project.

Select the project template of your choice, here we have selected the Console application for simplicity.

Now enter the project name and location for the project.

Select the .NET Framework version you prefer. We have selected the latest one available on our machine.

Once the Create button is clicked, the project will be created and ready for use. Open Solution Explorer to check the project files.

Now, let's install the IronXL library from the NuGet package manager as shown above.

Step 2: Create an Excel file without Interop

Now, let's create an Excel file without using the Microsoft Interop library.

using IronXL;
namespace WorkingWithIronXL
{
    public class Program
    {
        public static void Main()
        {
            // Create new Excel WorkBook Object
            WorkBook workBook = WorkBook.Create();
            // Create WorkSheet
            WorkSheet workSheet = workBook.CreateWorkSheet("newXlDemo");
            // Add data in new worksheet
            workSheet["A1"].Value = "IronXL is the best Excel library";           
            // Save the Excel file as XLSX
            workBook.SaveAs("myIronXlDemo.xlsx");
        }
    }
}

using IronXL;
namespace WorkingWithIronXL
{
    public class Program
    {
        public static void Main()
        {
            // Create new Excel WorkBook Object
            WorkBook workBook = WorkBook.Create();
            // Create WorkSheet
            WorkSheet workSheet = workBook.CreateWorkSheet("newXlDemo");
            // Add data in new worksheet
            workSheet["A1"].Value = "IronXL is the best Excel library";           
            // Save the Excel file as XLSX
            workBook.SaveAs("myIronXlDemo.xlsx");
        }
    }
}

Imports IronXL
Namespace WorkingWithIronXL
	Public Class Program
		Public Shared Sub Main()
			' Create new Excel WorkBook Object
			Dim workBook As WorkBook = WorkBook.Create()
			' Create WorkSheet
			Dim workSheet As WorkSheet = workBook.CreateWorkSheet("newXlDemo")
			' Add data in new worksheet
			workSheet("A1").Value = "IronXL is the best Excel library"
			' Save the Excel file as XLSX
			workBook.SaveAs("myIronXlDemo.xlsx")
		End Sub
	End Class
End Namespace

Code Explanation

1. We add the IronXL namespace to start working with the library
2. Then we create an Excel object to create an XLSX file with WorkBook.Create();
3. The CreateWorkSheet method is then called to create a worksheet inside the workbook
4. Add values to cell using workSheet["A1"]
5. Then save the Excel using workBook.SaveAs by providing a name for the file

Output Excel file

Step 3: Add Style to the Excel file using IronXL

Now let's see how to add styles to Excel cells.

using IronXL;
namespace WorkingWithIronXL
{
    public class Program
    {
        public static void Main()
         {
            // Create a new workbook
            WorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX);
            // Create a new worksheet
            WorkSheet workSheet = workBook.CreateWorkSheet("StyledSheet");
            // Add multiple values to cells
            workSheet["A1"].Value = "This Styled Text with Awesome IronXL library";
            workSheet["A2"].Value = 999999;
            // Apply styles to cells
            workSheet["A1"].Style.Font.Bold = true;
            workSheet["A1"].Style.Font.Italic = true;
            workSheet["A1"].Style.Font.Height = 14;
            workSheet["A1"].Style.Font.Color = "#FF0000"; // Red color
            workSheet["A2"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Double;
            workSheet["A2"].Style.BottomBorder.SetColor("#00FF00"); // Green color
            // Save the workbook
            workBook.SaveAs("myIronXlWriteDemo.xlsx");
        }
    }
}

using IronXL;
namespace WorkingWithIronXL
{
    public class Program
    {
        public static void Main()
         {
            // Create a new workbook
            WorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX);
            // Create a new worksheet
            WorkSheet workSheet = workBook.CreateWorkSheet("StyledSheet");
            // Add multiple values to cells
            workSheet["A1"].Value = "This Styled Text with Awesome IronXL library";
            workSheet["A2"].Value = 999999;
            // Apply styles to cells
            workSheet["A1"].Style.Font.Bold = true;
            workSheet["A1"].Style.Font.Italic = true;
            workSheet["A1"].Style.Font.Height = 14;
            workSheet["A1"].Style.Font.Color = "#FF0000"; // Red color
            workSheet["A2"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Double;
            workSheet["A2"].Style.BottomBorder.SetColor("#00FF00"); // Green color
            // Save the workbook
            workBook.SaveAs("myIronXlWriteDemo.xlsx");
        }
    }
}

Imports IronXL
Namespace WorkingWithIronXL
	Public Class Program
		Public Shared Sub Main()
			' Create a new workbook
			Dim workBook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
			' Create a new worksheet
			Dim workSheet As WorkSheet = workBook.CreateWorkSheet("StyledSheet")
			' Add multiple values to cells
			workSheet("A1").Value = "This Styled Text with Awesome IronXL library"
			workSheet("A2").Value = 999999
			' Apply styles to cells
			workSheet("A1").Style.Font.Bold = True
			workSheet("A1").Style.Font.Italic = True
			workSheet("A1").Style.Font.Height = 14
			workSheet("A1").Style.Font.Color = "#FF0000" ' Red color
			workSheet("A2").Style.BottomBorder.Type = IronXL.Styles.BorderType.Double
			workSheet("A2").Style.BottomBorder.SetColor("#00FF00") ' Green color
			' Save the workbook
			workBook.SaveAs("myIronXlWriteDemo.xlsx")
		End Sub
	End Class
End Namespace

Code Explanation

1. Create an Excel file using WorkBook.Create
2. Create a worksheet using workBook.CreateWorkSheet
3. Add values to cells using workSheet["A1"].Value
4. Add styles to each cell using the properties on the cell like workSheet["A1"].Style.Font.Bold
5. Save the workbook using workBook.SaveAs

Output Excel File

Step 4: Read Values from Excel and calculate

Now that we have seen how to create Excel files, let's see the following code on how we can read Excel files using IronXL and perform some in-memory calculations.

using IronXL;
namespace WorkingWithIronXL
{
    internal class IronXlDemo
    {
        public static void ReadData()
        {
            // Load the Excel worksheet
            WorkBook workBook = WorkBook.Load("sampleEmployeeData.xlsx");
            // Select the first worksheet
            WorkSheet workSheet = workBook.WorkSheets[0];
            // Read a specific cell value
            int cellValue = workSheet["A2"].IntValue;
            Console.WriteLine($"Value in A2: {cellValue}");
            // Read a range of cells
            foreach (var cell in workSheet["A1:H10"])
            {
                //Console.WriteLine($"Cell {cell.AddressString} has value '{cell.Text}'");
                Console.Write($"{cell.Text}\t");
                if(cell.AddressString.Contains("H"))
                {
                    Console.WriteLine();
                }
            }
            // Calculate aggregate values
            decimal sum = workSheet["F2:F10"].Sum();
            Console.WriteLine($"Sum of F2:F10: {sum}");
        }
}

using IronXL;
namespace WorkingWithIronXL
{
    internal class IronXlDemo
    {
        public static void ReadData()
        {
            // Load the Excel worksheet
            WorkBook workBook = WorkBook.Load("sampleEmployeeData.xlsx");
            // Select the first worksheet
            WorkSheet workSheet = workBook.WorkSheets[0];
            // Read a specific cell value
            int cellValue = workSheet["A2"].IntValue;
            Console.WriteLine($"Value in A2: {cellValue}");
            // Read a range of cells
            foreach (var cell in workSheet["A1:H10"])
            {
                //Console.WriteLine($"Cell {cell.AddressString} has value '{cell.Text}'");
                Console.Write($"{cell.Text}\t");
                if(cell.AddressString.Contains("H"))
                {
                    Console.WriteLine();
                }
            }
            // Calculate aggregate values
            decimal sum = workSheet["F2:F10"].Sum();
            Console.WriteLine($"Sum of F2:F10: {sum}");
        }
}

Imports Microsoft.VisualBasic
Imports IronXL
Namespace WorkingWithIronXL
	Friend Class IronXlDemo
		Public Shared Sub ReadData()
			' Load the Excel worksheet
			Dim workBook As WorkBook = WorkBook.Load("sampleEmployeeData.xlsx")
			' Select the first worksheet
			Dim workSheet As WorkSheet = workBook.WorkSheets(0)
			' Read a specific cell value
			Dim cellValue As Integer = workSheet("A2").IntValue
			Console.WriteLine($"Value in A2: {cellValue}")
			' Read a range of cells
			For Each cell In workSheet("A1:H10")
				'Console.WriteLine($"Cell {cell.AddressString} has value '{cell.Text}'");
				Console.Write($"{cell.Text}" & vbTab)
				If cell.AddressString.Contains("H") Then
					Console.WriteLine()
				End If
			Next cell
			' Calculate aggregate values
			Dim sum As Decimal = workSheet("F2:F10").Sum()
			Console.WriteLine($"Sum of F2:F10: {sum}")
		End Sub
	End Class

Code Explanation

1. Here we have employee data saved in the Excel object model as shown below
2. Using IronXL load we load the Excel file to memory and read the first sheet
3. Then we iterate through each cell to get the values and display them on the console
4. Then we calculate the sum of age using cells F2-F10 and then display it on the console.

Input Excel

Output

Step 5: Convert Excel to HTML for web usage

Now that we know how to generate, read, and write Excel files let us see how to convert Excel to HTML for web usage.

using IronXL;
using IronXL.Options;
namespace WorkingWithIronXL
{
    internal class IronXlDemo
    {
        public static void ConvertToHtml()
        {
            WorkBook workBook = WorkBook.Load("sampleEmployeeData.xlsx");
            var options = new HtmlExportOptions()
            {
                // Set row/column numbers visible in HTML document
                OutputRowNumbers = true,
                OutputColumnHeaders = true,
                // Set hidden rows/columns visible in HTML document
                OutputHiddenRows = true,
                OutputHiddenColumns = true,
                // Set leading spaces as non-breaking
                OutputLeadingSpacesAsNonBreaking = true
            };
            // Export workbook to the HTML file
            workBook.ExportToHtml("workBook.html", options);
        }
}

using IronXL;
using IronXL.Options;
namespace WorkingWithIronXL
{
    internal class IronXlDemo
    {
        public static void ConvertToHtml()
        {
            WorkBook workBook = WorkBook.Load("sampleEmployeeData.xlsx");
            var options = new HtmlExportOptions()
            {
                // Set row/column numbers visible in HTML document
                OutputRowNumbers = true,
                OutputColumnHeaders = true,
                // Set hidden rows/columns visible in HTML document
                OutputHiddenRows = true,
                OutputHiddenColumns = true,
                // Set leading spaces as non-breaking
                OutputLeadingSpacesAsNonBreaking = true
            };
            // Export workbook to the HTML file
            workBook.ExportToHtml("workBook.html", options);
        }
}

Imports IronXL
Imports IronXL.Options
Namespace WorkingWithIronXL
	Friend Class IronXlDemo
		Public Shared Sub ConvertToHtml()
			Dim workBook As WorkBook = WorkBook.Load("sampleEmployeeData.xlsx")
			Dim options = New HtmlExportOptions() With {
				.OutputRowNumbers = True,
				.OutputColumnHeaders = True,
				.OutputHiddenRows = True,
				.OutputHiddenColumns = True,
				.OutputLeadingSpacesAsNonBreaking = True
			}
			' Export workbook to the HTML file
			workBook.ExportToHtml("workBook.html", options)
		End Sub
	End Class

Code Explanation

1. Here we are loading the Excel "sampleEmployeeData.xlsx" using the Load method
2. Then set the options for converting to HTML using HtmlExportOptions
3. Then we use the ExportToHtml method to convert to HTML

Input

Output

Real-World Use Cases

IronXL is a versatile .NET library with a wide range of real-world applications, including:

1. Business Reporting:

1. Automating the generation of periodic reports, such as sales summaries, financial statements, and performance metrics.
2. Creating custom dashboards that pull data from various sources and present it in an Excel format.

2. Data Analysis:

1. Processing large datasets to perform statistical analysis, data cleaning, and data transformation.
2. Exporting analyzed data into Excel for further examination and sharing with stakeholders.

3. Inventory Management:

1. Managing product inventories by creating and updating Excel sheets with stock levels, supplier details, and reorder points.
2. Generating inventory reports to track stock movement and identify trends.

4. Customer Relationship Management (CRM):

1. Exporting customer data from CRM systems into Excel for detailed analysis and reporting.
2. Updating CRM records by importing modified data from Excel files.

5. Educational Institutions:

1. Creating and maintaining student grade books, attendance records, and progress reports.
2. Generating exam results and detailed performance analysis for teachers and students.

6. Financial Services:

1. Automating the creation of financial models, budgets, and forecasts in Excel.
2. Consolidating financial data from multiple sources for comprehensive financial reporting.

7. Human Resources:

1. Managing employee data, including contact details, payroll information, and performance evaluations.
2. Generating reports on HR metrics such as headcount, turnover rates, and employee demographics.

8. Project Management:

1. Tracking project timelines, milestones, and resource allocation using Excel sheets.
2. Creating Gantt charts and other project management tools directly within Excel.

9. E-commerce:

1. Exporting order details, customer information, and sales data from e-commerce platforms to Excel.
2. Analyzing sales trends, customer behavior, and product performance.

10. Healthcare:

1. Managing patient records, appointment schedules, and treatment plans in Excel.
2. Analyzing healthcare data to identify patterns and improve patient care.

IronXL Licensing

IronXL is an enterprise library part of Iron Suite of products from IronSoftware. It requires a license to run. Users can download a trial license to work with using their email ID from here. Once the data is entered license is delivered to the email ID provided. This license needs to be placed at the beginning of the code, before using the IronXL library as below.

License.LicenseKey = "your Key Here"

License.LicenseKey = "your Key Here"

'INSTANT VB TODO TASK: The following line uses invalid syntax:
'License.LicenseKey = "your Key Here"

Conclusion

IronXL is a powerful .NET library for creating, reading, and editing Excel files, offering an intuitive API that simplifies the process of working with Excel documents. It supports a wide range of Excel formats, including XLS, XLSX, and CSV, making it versatile for various use cases. IronXL allows easy manipulation of cell values, formulas, and formatting, and is optimized for performance, efficiently handling large files and complex operations. Its efficient memory management ensures responsive applications, and its cross-platform compatibility makes it a valuable tool for developers working on different operating systems.