How to Work with Excel Files in C#
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#
- Create a Visual Studio project and add the IronXL NuGet package.
- Create an Excel file without Interop.
- Add Style to Excel file using IronXL.
- Read values from Excel and calculate.
- 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
Import and Export:
- Import Data: Supports XLS, XLSX, CSV, and TSV formats.
- Export Data: Can export worksheets to XLS, XLSX, CSV, TSV, and JSON formats.
Data Manipulation:
- System.Data Integration: Work with spreadsheets as
System.Data.DataSet
andSystem.Data.DataTable
objects. - Formulas: Supports Excel formulas, which are recalculated every time a sheet is edited.
- System.Data Integration: Work with spreadsheets as
Styling and Formatting:
- Cell Styling: Customize font, size, background pattern, border, alignment, and number formats.
- Ranges: Intuitive range setting with
WorkSheet["A1:B10"]
syntax.
Security:
- Encryption: Encrypt and decrypt XLSX, XLSM, and XLTX files with passwords.
- Cross Platform Compatibility:
- Works with .NET Framework, .NET Core, .NET Standard, and Azure.
- Compatible with Windows, macOS, Linux, Docker, Azure, and AWS.
Benefits
- No Need for Microsoft Office: IronXL does not require Microsoft Office to be installed, making it lightweight and easy to deploy.
- Ease of Use: The API is intuitive and easy to use, allowing developers to quickly integrate Excel functionality into their applications.
- Performance: IronXL is optimized for performance, ensuring fast and efficient processing of large Excel files.
- Versatility: Suitable for a wide range of applications, including web, desktop, and cloud-based solutions.
- 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
- .NET Framework: IronXL supports .NET Framework 4.5 and above .NET technologies.
- .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.
- Operating Systems: Works on Windows, macOS, and Linux. Compatible with Docker, Azure, and AWS environments.
- No Need for Microsoft Office: IronXL does not require Microsoft Office or Excel Interop to be installed.
- 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
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
- We add the IronXL namespace to start working with the library.
- Then we create an Excel object to create an XLSX file with
WorkBook.Create()
. - The
CreateWorkSheet
method is then called to create a worksheet inside the workbook. - Add values to a cell using
workSheet["A1"].Value
. - Then save the Excel file 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
- Create an Excel file using
WorkBook.Create
. - Create a worksheet using
workBook.CreateWorkSheet
. - Add values to cells using
workSheet["A1"].Value
. - Add styles to each cell using the properties on the cell like
workSheet["A1"].Style.Font.Bold
. - 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;
using System;
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.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;
using System;
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.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
Imports System
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.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
End Namespace
Code Explanation
- We load a sample Excel file into memory using
WorkBook.Load
. - Access the first worksheet in the workbook.
- Read a specific cell's value and display it using
Console.WriteLine
. - Iterate through a range of cells and print out each value. Lines are printed for every end of column 'H'.
- Calculate the sum of values in the range F2-F10 and print it.
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
End Namespace
Code Explanation
- Load the Excel file "sampleEmployeeData.xlsx" using the
Load
method. - Set the options for converting to HTML using
HtmlExportOptions
. - Use the
ExportToHtml
method to convert and save the workbook as an HTML file.
Input
Output
Real-World Use Cases
IronXL is a versatile .NET library with a wide range of real-world applications, including:
1. Business Reporting:
- Automating the generation of periodic reports, such as sales summaries, financial statements, and performance metrics.
- Creating custom dashboards that pull data from various sources and present it in an Excel format.
2. Data Analysis:
- Processing large datasets to perform statistical analysis, data cleaning, and data transformation.
- Exporting analyzed data into Excel for further examination and sharing with stakeholders.
3. Inventory Management:
- Managing product inventories by creating and updating Excel sheets with stock levels, supplier details, and reorder points.
- Generating inventory reports to track stock movement and identify trends.
4. Customer Relationship Management (CRM):
- Exporting customer data from CRM systems into Excel for detailed analysis and reporting.
- Updating CRM records by importing modified data from Excel files.
5. Educational Institutions:
- Creating and maintaining student grade books, attendance records, and progress reports.
- Generating exam results and detailed performance analysis for teachers and students.
6. Financial Services:
- Automating the creation of financial models, budgets, and forecasts in Excel.
- Consolidating financial data from multiple sources for comprehensive financial reporting.
7. Human Resources:
- Managing employee data, including contact details, payroll information, and performance evaluations.
- Generating reports on HR metrics such as headcount, turnover rates, and employee demographics.
8. Project Management:
- Tracking project timelines, milestones, and resource allocation using Excel sheets.
- Creating Gantt charts and other project management tools directly within Excel.
9. E-commerce:
- Exporting order details, customer information, and sales data from e-commerce platforms to Excel.
- Analyzing sales trends, customer behavior, and product performance.
10. Healthcare:
- Managing patient records, appointment schedules, and treatment plans in Excel.
- Analyzing healthcare data to identify patterns and improve patient care.
IronXL Licensing
IronXL is an enterprise library part of Iron Suite of products from Iron Software. 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, the 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";
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.
Frequently Asked Questions
What is the library for working with Excel files in C#?
IronXL is a powerful C# library from Iron Software that allows developers to read, write, and manipulate Excel documents without needing Microsoft Excel installed on the machine. It supports various formats like XLS, XLSX, and CSV.
What are the key features of the Excel library?
Key features of IronXL include data import/export support for formats like XLS, XLSX, CSV, and JSON, integration with System.Data for data manipulation, support for Excel formulas, cell styling and formatting, encryption, and cross-platform compatibility.
Do I need Microsoft Office installed to use the Excel library?
No, IronXL does not require Microsoft Office or Excel Interop to be installed, making it lightweight and easy to deploy.
How can I install the Excel manipulation library in my project?
You can install IronXL via the NuGet Package Manager in Visual Studio or using the Package Manager Console with the command: dotnet add package IronXL.Excel --version 2024.8.5
What are the benefits of using the Excel library from Iron Software?
Benefits of using IronXL include ease of use with an intuitive API, optimized performance for handling large Excel files, versatility for various applications, and comprehensive documentation.
How do I create an Excel file using the C# library?
To create an Excel file using IronXL, you can use the WorkBook.Create() method to create a new workbook, add worksheets with CreateWorkSheet, set cell values, and save the file with workBook.SaveAs.
Can I apply styles to Excel cells using the C# Excel library?
Yes, IronXL allows you to apply styles to Excel cells, such as customizing font, size, color, borders, and alignment using the Style properties on each cell.
Is the C# Excel library compatible with different operating systems?
Yes, IronXL is compatible with multiple operating systems, including Windows, macOS, Linux, and environments like Docker, Azure, and AWS.
How can I read values from an Excel file using the C# library?
To read values from an Excel file using IronXL, load the file with WorkBook.Load, select the worksheet, and then access specific cell values or ranges using their address.
Can the library convert Excel files to other formats?
Yes, IronXL can convert Excel files to other formats, such as HTML, using the ExportToHtml method with options for customization.