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 excel file 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 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:
IronXL: .NET Excel Library
IronXL 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 Application, and .NET Core Applications. IronXL is very easy to use in .NET applications for developers.
IronXL doesn't need the installation of Microsoft Office on a local machine where we have to use the IronXL library. And IronXL also doesn't use Excel Interop for excel operations. IronXL makes working with excel files in the .NET environment very easy, rapid, and straightforward.
IronXL makes it easy to perform all Excel operations and calculations without detailed programming information.
IronXL enables the developers to perform many special excel operations by writing a few lines of code like 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
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);
}
To know more about the IronXL example, click here. IronXL supports all major operating systems like Windows, Linux, and macOS.
Pricing Plan
IronXL has three pricing plans. The basic package starts from $499. But it is free for development purposes. It also offers 30 days free trial. You can see more details from the given picture.
FastExcel: C# Excel Library
FastExcel is a C# excel library that assists the user with the functionality of fast reading and writing of the excel spreadsheets. It is an open-source library. 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. FastExcel only requires .NET Framework 4.5 or higher and .NET Core 2.0.
It provides basic excel functionalities but not advanced functionalities like functions etc. But we can add new sheets, cell ranges, add data, and many other things using Fast Excel Library.
Let's have a look at 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.
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.