Programmatically, editing an Excel file can be difficult for two primary reasons. Users have to maintain a consistent interface and document structure which are not always easy to do when working with spreadsheets.
The other reason is that it's not always clear what code will work in certain circumstances, especially if complex calculations are involved. There are many different reasons why editing excels files programmatically is complex. Programmers have to convert the data in the Excel file into a usable format; then, they have to parse the data and parse it back, which is not as straightforward.
Here I come up with a solution for this problem. I will show you how we can read and edit excel files programmatically without any hassle using different Excel libraries. Let's get started:
FastExcel: .NET Excel Library
FastExcel is a versatile and powerful library for opening, editing, saving Excel Files, reading, and other important Excel functionalities. It supports all .NET project templates like ASP.NET, Windows Applications, and .NET Core Applications. FastExcel is very easy to use in .NET applications for developers.
FastExcel doesn't need the installation of Microsoft Office on a local machine where we have to use the FastExcel library. And FastExcel also doesn't use Excel Interop for excel operations. FastExcel makes working with excel files in the .NET environment very easy, rapid, and straightforward.
FastExcel makes it easy to perform all Excel operations and calculations without detailed programming information.
FastExcel enables the developers to perform many special excel operations by writing a few lines of code like the sum function, multiple rows, total columns, reading excel files, writing excel files, modifying excel tables, adding columns and rows, removing columns, and rows.
Let's have a look at a few code examples:
Code Example
Creating Excel file
using (FastExcel.FastExcel fastExcel = new FastExcel.FastExcel(new FileInfo("Template.xlsx"), new FileInfo("Output.xlsx")))
{
//Create a worksheet with some rows
var worksheet = new Worksheet();
var rows = new List();
for (int rowNumber = 1; rowNumber < 100000; rowNumber++)
{
List cells = new List();
for (int columnNumber = 1; columnNumber < 13; columnNumber++)
{
cells.Add(new Cell(columnNumber, columnNumber * DateTime.Now.Millisecond));
}
cells.Add(new Cell(13,"FileFormat" + rowNumber));
cells.Add(new Cell(14,"FileFormat Developer Guide"));
rows.Add(new Row(rowNumber, cells));
}
worksheet.Rows = rows;
fastExcel.Write(worksheet,"sheet1");
}
Rows and cells management
using (FastExcel.FastExcel fastExcel = new FastExcel.FastExcel(new FileInfo("input.xlsx")))
{
//Create a some rows in a worksheet
var worksheet = new Worksheet();
var rows = new List();
for (int rowNumber = 1; rowNumber < 100000; rowNumber += 50)
{
List cells = new List();
for (int columnNumber = 1; columnNumber < 13; columnNumber += 2)
{
cells.Add(new Cell(columnNumber, rowNumber));
}
cells.Add(new Cell(13,"File Format Developer Guide"));
rows.Add(new Row(rowNumber, cells));
}
worksheet.Rows = rows;
// Read the data
fastExcel.Update(worksheet,"sheet1");
}
Pricing Plan
It is an open-source library. So, there is no pricing plan for it. You can use it for your basic functionalities of excel.
IronXL: C# Excel Library
IronXL is a C# excel library that assists the user with the functionality of fast reading and writing of excel spreadsheets. It takes less memory while running in the background. It doesn't use Open XML SDK for editing or interacting with data. It does the editing of XML files by itself. IronXL only requires .NET Framework 4.5 or higher and .NET Core 2.0. However, it supports the latest .NET frameworks like .NET framework 7.
We can add new sheets, and cell ranges, add data, and many other things using IronXL Library.
Let's have a look at code examples:
Code Example
Reading Excel file
using IronXL;
//Supported spreadsheet formats for reading include: XLSX, XLS, CSV and TSV
WorkBook workbook = WorkBook.Load("data.xlsx");
WorkSheet sheet = workbook.WorkSheets.First();
//Select cells easily in Excel notation and return the calculated value, date, text or formula
int cellValue = sheet["A2"].IntValue;
// Read from Ranges of cells elegantly.
foreach (var cell in sheet["A2:B10"])
{
Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
}
Excel Range Cell
using IronXL;
using System. Linq;
WorkBook workbook = WorkBook.Load("test.xls");
WorkSheet sheet = workbook.WorkSheets.First();
var range = sheet["A2:A8"];
//This is how we can iterate over our range and read or edit any cell
foreach (var cell in range)
{
Console.WriteLine(cell.Value);
}
// Another Example
var oneMoreRange = sheet["A9:A10"];
//This is how we can combine our ranges into a single selection
var resultRange = range + oneMoreRange;
foreach (var cell in resultRange)
{
Console.WriteLine(cell.Value);
}
IronXL supports all major operating systems like Windows, Linux, and macOS.
Pricing Plan
IronXL has three pricing plans. The basic package starts from $749. But it is free for development purposes. It also offers 30 days free trial. You can see more details in the given picture.
EPPlus: .NET Excel Library
EPPlus is a C Excel Library that provides access to Excel's objects and methods, including VBA and API. With EPPlus, developers can harness the power of Excel to build quality, complex applications or whip up a quick spreadsheet.
The library provides functions for managing cells, rows, and columns; working with worksheets, creating pivot tables, charts and graphs; manipulating pivot table fields; working with images, and exporting data to other formats. EPPlus has no dependencies on any other library such as Microsoft Excel. EPPlus is not just a static library - it is constantly evolving in response to changes in the Excel spreadsheet environment and user needs.
You can observe their changelog to see how it is evolving. EPPlus is distributed by NuGet. Version 5 of EPPlus supports .NET Framework from version 3.5 and supports .NET Core from version 2.0.
Pricing Plan
The pricing plans of EPPlus are a little bit confusing. They didn't provide complete information on how the pricing plans work and whether it is continual or if we have to buy year after year.
Unfortunately, EPPlus didn't give any code example to demonstrate how it will use. And it also didn't provide any tutorial or free trial version.
Summary
All libraries are fantastic and the best in their fields. But every library has its pros and cons. EPPlus doesn't have any tutorial guide, and developers are unsure how this library will workout. However, it has a Github repo. And its pricing plan is not much clear.
Fast Excel library is also sound, but it has limited features. But it is an open-source library, so we can use it in commercial projects. But in my opinion, a short excel library is not compatible with large projects. It would help if you went with IronXL or EPPlus library to use excel operations in your big commercial project.
IronXL library is a mature library with all the advanced features we have to use in an advanced excel application. The excellent point is that it offers 30 day free trial in commercials, so it becomes easy to test if it is compatible.