Programmatically Manage Excel Spreadsheets with .NET
Spreadsheets are great tools. They are user interface and database combined in one application and have been infinitely helpful for ages. In this post, we are going to look into a handy NuGet package called ClosedXML to create and manipulate Excel spreadsheets by using a CLI demo application.
Demo Use Case
As the energy crisis is getting worse, electricity prices are skyrocketing everywhere. So I decided to create a spreadsheet to log daily electricity costs programmatically. This generally is possible if you have a Smart Meter and can get daily costs from your supplier. Either way, the main objective is to demonstrate using .NET and ClosedXML NuGet package.
The final spreadsheet will look like this:
Usage
The project is a CLI project created using the dotnet tool and CliFx. You can also find the detailed blog post about creating your own CLIs published on this blog.
After you’ve cloned the repository, publish the application as a dotnet tool.
dotnet pack
dotnet tool install --global --add-source ./ElectricityCost.CLI/nupkg/ ElectricityCost.CLI
Then you can use it anywhere on your machine.
For example, to create a new spreadsheet from the built-in template, you can run this:
ec closedxml template new --path ElectricityCosts.xlsx
And you can add new costs by providing the day of the month and the cost value:
ec closedxml add --path ElectricityCosts.xlsx --day 2 --cost 3.44
Implementation: Using CloseXML - An easier alternative to OpenXML SDK
ClosedXML is a wrapper around OpenXML that makes Excel spreadsheet manipulation a breeze.
When working with a spreadsheet, you often want to address the cells by row and columns as you would typically do in a table and read/write data into it. ClosedXML allows us to do precisely that.
using ClosedXML.Excel;
using (var workbook = new XLWorkbook())
{
var worksheet = workbook.Worksheets.Add("ClosedXMLDemo");
worksheet.Cell("A1").Value = "Hello World!";
workbook.SaveAs("Sample1.xlsx");
}
The code snippet above creates a new Excel file and saves it in the Sample.xlsx file. SaveAs might sound like it only handles existing files, but it is called to create new files.
Working on existing spreadsheets is also relatively straightforward. Pass the file’s path to the XLWorkbook constructor, and you can find the spreadsheet by a simple LINQ query.
using (var workbook = new XLWorkbook("Sample1.xlsx") )
{
var worksheet = workbook.Worksheets.First(ws => ws.Name == "ClosedXMLDemo");
Console.WriteLine(worksheet.Cell("A1").Value);
}
And setting formulas is also as simple as setting the value. The following snippet shows a formula to calculate the sum of daily costs:
worksheet.Cell("E1").FormulaA1 = $"=SUM(B2:B{numberOfDaysInCurrentMonth + 1})";
Managing the styles is also quite intuitive:
var rngSubTotals = rngTable.Range("D2:E3");
rngSubTotals.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
rngSubTotals.Style.Font.Bold = true;
rngSubTotals.Style.Font.FontSize = 20;
rngSubTotals.Style.Font.FontColor = XLColor.Red;
rngSubTotals.Style.NumberFormat.Format = $"{CURRENCY_SYMBOL} #,##0.00";
For the sake of brevity, I’m not going to put all the code in this post. So, please visit the GitHub repo and play around with the code.
Conclusion
In this post, we looked into using a very intuitive and powerful NuGet package: ClosedXML. However, dealing with OpenXML directly can be overwhelming so having the ability to manipulate Excel spreadsheets with a straightforward tool is handy.
