Recently I’ve been working a lot in .NET Core 3.0 and 3.1 projects. Both upgrading existing 2.2 projects and a couple of new greenfields projects. The thing that I’ve had to do in each and every one is switch from using the new System.Text.Json package back to using Newtonsoft.Json.

In almost all of them I’ve actually tried to keep going with System.Text.Json, but in the existing projects I haven’t had time to switch out things like custom JsonConverters or Newtonsoft.Json specific attributes on my models.

In new projects, I always get to the point where I just know how to do it in Newtonsoft. And as much as I want to try this shiny new thing, I have my own deadlines which don’t quite allow me to fiddle about with new toys.

So if you’re in the same boat as me and just need to get something out the door. The first thing you need is to install the following Nuget package :

Install-Package Microsoft.AspNetCore.Mvc.NewtonsoftJson

Then you need to add a specific call to your IMVCBuilder. This will differ depending on how you have set up your project. If you are migrating from an existing project you’ll have a call to “AddMvc()” which you can then tack onto it like so :

services.AddMvc().AddNewtonsoftJson();

However in new .NET Core 3+ projects, you have a different set of calls replace MVC. So you’ll probably have one of the following :

services.AddControllers().AddNewtonsoftJson();
services.AddControllersWithViews().AddNewtonsoftJson();
services.AddRazorPages().AddNewtonsoftJson();

If this is an API you will likely have AddControllers, but depending on your project setup you could have the others also. Tacking on AddNewtonsoftJson()  to the end means it will “revert” back to using Newtonsoft over System.Text.Json

Opening Excel files in code has been a painful experience long before .NET Core came along. In many cases, you actually needed the Excel application installed on the target/users machine to be able to open excel files via code. If you’ve ever had to use those “OLE DB Jet ” queries before, you know it’s not a great experience. Luckily there are some pretty good open source solutions now that don’t require excel on the target machine. This is good for Windows users so that you don’t have to install excel on a target users machine or web server, but also for people hosting .NET Core applications on Linux (And even Mac/ARM) – where Excel is obviously no where to be seen!

My methodology for this article is pretty simple. Create a standardized excel workbook with a couple of sheets, couple of formulas, and a couple of special formatting cases. Read the same data out in every single library and see which one works the best for me. Simple! Let’s get going!

Note On CSV Formats

I should note that if you are reading a CSV, or more so a single excel sheet that doesn’t have formulas or anything “excel” specific on the sheet, you should instead just parse it using standard CSV technique. We have a great article here on parsing CSV in .NET Core that you should instead follow. CSV parsers are great for taking tabular data and deserializing it into objects and should be used where they can.

Example Data

I figure the best way to compare the different libraries on offer is to create a simple spreadsheet to compare the different ways we can read data out. The spreadsheet will have two “sheets”, where the second sheet references the first.

Sheet 1 is named “First Sheet” and looks like so :

Notice that cell A2 is simply the number “1”. Then in column B2, we have a reference to cell A2. This is because we want to check if the libraries allow us to not only get the “formula” from the cell, but also what the computed value should be.

We are also styling cell A2 with a font color of red, and B2 has a full border (Although hard to see as I’m trying to show the fomula). We will try and extract these styling elements out later.

Sheet 2 is named “Second Sheet” and looks like so :

So we are doing a simple “SUM” formula and referencing the first sheet. Again, this is so we can test getting both the formula and the computed value, but this time across different sheets. It’s not complicated for a person used to working with Excel, but let’s see how a few libraries handle it.

In general, in my tests I’m looking for my output to always follow the same format of :

Sheet 1 Data
Cell A2 Value   : 
Cell A2 Color   :
Cell B2 Formula :
Cell B2 Value   :
Cell B2 Border  :

Sheet 2 Data
Cell A2 Formula :
Cell A2 Value   :

That way when I show the code, you can pick the library that makes the most sense to you.

EPPlus

When I first started hunting around for parsing excel in .NET Core, I remembered using EPPlus many moons ago for some very lightweight excel parsing. The nuget package can be found here : https://www.nuget.org/packages/EPPlus/. It’s also open source so you can read through the source code if that’s your thing here : https://github.com/JanKallman/EPPlus

The code to read our excel spreadsheet looks like so :

static void Main(string[] args)
{
    using(var package = new ExcelPackage(new FileInfo("Book.xlsx")))
    {
        var firstSheet = package.Workbook.Worksheets["First Sheet"];
        Console.WriteLine("Sheet 1 Data");
        Console.WriteLine($"Cell A2 Value   : {firstSheet.Cells["A2"].Text}");
        Console.WriteLine($"Cell A2 Color   : {firstSheet.Cells["A2"].Style.Font.Color.LookupColor()}");
        Console.WriteLine($"Cell B2 Formula : {firstSheet.Cells["B2"].Formula}");
        Console.WriteLine($"Cell B2 Value   : {firstSheet.Cells["B2"].Text}");
        Console.WriteLine($"Cell B2 Border  : {firstSheet.Cells["B2"].Style.Border.Top.Style}");
        Console.WriteLine("");

        var secondSheet = package.Workbook.Worksheets["Second Sheet"];
        Console.WriteLine($"Sheet 2 Data");
        Console.WriteLine($"Cell A2 Formula : {secondSheet.Cells["A2"].Formula}");
        Console.WriteLine($"Cell A2 Value   : {secondSheet.Cells["A2"].Text}");
    }
}

Honestly what can I say. This was *super* easy and worked right out of the box. It picks up formulas vs text perfectly! The styles on our first sheet was also pretty easy to get going. The border is slightly annoying because you have to check the “Style” of the border, and if it’s a style of “None”, then it means there is no border (As opposed to a boolean for “HasBorder” or similar). But I think I’m just nit picking, EPPlus just works!

NPOI

NPOI is another open source option with a Github here : https://github.com/tonyqus/npoi and Nuget here : https://www.nuget.org/packages/NPOI/. It hasn’t had a release in over a year which isn’t that bad because it’s not like Excel itself has tonnes of updates throughout the year, but the Issues list on Github is growing a bit with a fair few bugs so keep that in mind.

The code to read our data using NPOI looks like so :

…..

…Actually you know what. I blew a bunch of time on this to try and work out the best way to use NPOI and the documentation is awful. The wiki is here : https://github.com/tonyqus/npoi/wiki/Getting-Started-with-NPOI but it has a few samples but most/all of them are about creating excel workbooks not reading them. I saw they had a link to a tutorial on how to read an Excel file which looked promising, but it was literally reading the spreadsheet and then dumping the text out.

After using EPPlus, I just didn’t see any reason to continue with this one. Almost every google answer will lead you to StackOverflow with people using NPOI with such specific use cases that it never really all pieced together for me.

ExcelDataReader

ExcelDataReader appeared in a couple of stackoverflow answers on reading excel in .NET Core. Similar to others in this list, it’s open source here : https://github.com/ExcelDataReader/ExcelDataReader and on Nuget here : https://www.nuget.org/packages/ExcelDataReader/

I wanted to make this work but…. It just doesn’t seem intuitive at all. ExcelDataReader works on the premise that you are reading “rows” and “columns” sequentially in almost a CSV fashion. That sort of works but if you are looking for a particular cell, it’s rough as hell.

Some example code :

static void Main(string[] args)
{
    System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
    using (var stream = File.Open("Book.xlsx", FileMode.Open, FileAccess.Read))
    {
        using (var reader = ExcelReaderFactory.CreateReader(stream))
        {
            do
            {
                while (reader.Read()) //Each ROW
                {
                    for (int column = 0; column < reader.FieldCount; column++)
                    {
                        //Console.WriteLine(reader.GetString(column));//Will blow up if the value is decimal etc. 
                        Console.WriteLine(reader.GetValue(column));//Get Value returns object
                    }
                }
            } while (reader.NextResult()); //Move to NEXT SHEET

        }
    }
}

The first line in particular is really annoying (Everything blows up without it). But you’ll notice that we are moving through row by row (And sheet by sheet) trying to get values. Ontop of that, calling things like “GetString” doesn’t work if the value is a decimal (Implicit casts would have been better IMO). I also couldn’t find any way to get the actual formula of the cell. The above only returns the computed results.

I was going to slog my way through and actually get the result we were looking for, but it’s just not a library I would use.

Syncfusion

Syncfusion is one of those annoying companies that create pay-to-use libraries, upload them to nuget, and then in small print  say you need to purchase a license or else. Personally, I would like to see Microsoft not allow paid libraries into the public Nuget repo. I’m going to include them here but their licensing starts at $995 per year, per developer, so I don’t see much reason to use it for the majority of use cases. The nuget page can be found here https://www.nuget.org/packages/Syncfusion.XlsIO.Net.Core/

The code looks like :

static void Main(string[] args)
{
    ExcelEngine excelEngine = new ExcelEngine();
    using (var stream = File.Open("Book.xlsx", FileMode.Open, FileAccess.Read))
    {
        var workbook = excelEngine.Excel.Workbooks.Open(stream);

        var firstSheet = workbook.Worksheets["First Sheet"];
        Console.WriteLine("Sheet 1 Data");
        Console.WriteLine($"Cell A2 Value   : {firstSheet.Range["A2"].DisplayText}");
        Console.WriteLine($"Cell A2 Color   : {firstSheet.Range["A2"].CellStyle.Font.RGBColor.Name}");
        Console.WriteLine($"Cell B2 Formula : {firstSheet.Range["B2"].Formula}");
        Console.WriteLine($"Cell B2 Value   : {firstSheet.Range["B2"].DisplayText}");
        Console.WriteLine($"Cell B2 Border  : {firstSheet.Range["B2"].CellStyle.Borders.Value}");
        Console.WriteLine("");

        var secondSheet = workbook.Worksheets["Second Sheet"];
        Console.WriteLine($"Sheet 2 Data");
        Console.WriteLine($"Cell A2 Formula : {secondSheet.Range["A2"].Formula}");
        Console.WriteLine($"Cell A2 Value   : {secondSheet.Range["A2"].DisplayText}");
    }
}

So not bad. I have to admit, I fiddled around trying to understand how it worked out borders (As the above code doesn’t work), but gave up. The font color also took some fiddling where the library returns non standard objects as the color. Some of the properties for the actual data are also a bit confusing where you have value, text, displaytext etc. All returning slightly different things so you sort of have to just spray and pray and see which one works.

If EPPlus didn’t exist, and Syncfusion wasn’t fantastically overpriced, this library would actually be pretty good.

TL;DR;

Use EPPlus. https://github.com/JanKallman/EPPlus

This article is part of a series on creating Windows Services in .NET Core.

Part 1 – The “Microsoft” Way
Part 2 – The “Topshelf” Way
Part 3 – The “.NET Core Worker” Way

This article has been a long time coming I know. I first wrote about creating Windows Services in .NET Core all the way back in September (3 months ago). And on that post a helpful reader (Shout out to Saeid!) immediately commented that in .NET Core 3, there is a brand new way to create Windows Services! Doh! It reminds me of the time I did my 5 part series on Azure Webjobs in .NET Core, and right as I was completing the final article, a new version of the SDK got released with a tonne of breaking changes making me have to rewrite a bunch.

Thankfully, this isn’t necessarily a “breaking change” to how you create Windows Services, the previous articles on doing it the “Microsoft Way” and the “Topshelf Way” are still valid, but this is just another way to get the same result (Maybe with a little less cursing to the programming gods).

The Setup

The first thing you need to know is that you need .NET Core 3.0 installed. At the time of writing, .NET Core 3.1 has just shipped and Visual Studio should be prompting you to update anyway. But if you are trying to do this in a .NET Core 2.X project, it’s not going to work.

If you like creating projects from the command line, you need to create a new project as the type “worker” :

dotnet new worker

If you are a Visual Studio person like me, then there is actually a template inside Visual Studio that does the exact same thing.

Doing this creates a project with essentially two files. You will have your program.cs which is basically the “bootstrapper” for your app. And then you have something called worker.cs which is where the logic for your service goes.

It should be fairly easy to spot, but to add extra background services to this program to run in parallel, you just need to create a new class that inherits from BackgroundService :

public class MyNewBackgroundWorker : BackgroundService
{
    protected override Task ExecuteAsync(CancellationToken stoppingToken)
    {
        //Do something. 
    }
}

Then in our program.cs file, we just add the worker to our service collection :

.ConfigureServices((hostContext, services) =>
{
    services.AddHostedService<Worker>();
    services.AddHostedService<MyNewBackgroundWorker>();
});

AddHostedService has actually been in the framework for quite some time as a “background service” type task runner that typically runs underneath your web application. We’ve actually done an article on hosted services in ASP.NET Core before, but in this case, the hosted service is basically the entire app rather than it being something that runs behind the scenes of your web app.

Running/Debugging Our Application

Out of the box, the worker template has a background service that just pumps out the datetime to the the console window. Let’s just press F5 on a brand new app and see what we get.

info: CoreWorkerService.Worker[0]
      Worker running at: 12/07/2019 08:20:30 +13:00
info: Microsoft.Hosting.Lifetime[0]
      Application started. Press Ctrl+C to shut down.

We are up and running immediately! We can leave our console window open to debug the application, or close the window to exit. Compared to the hell we went through trying to debug our Windows Service when creating it the “Microsoft” way, this is like heaven.

Another thing to note is that really what we have infront of us is a platform for writing console applications. In the end we are only writing out the time to the console window, but we are also doing that via Dependency Injection creating a hosted worker. We can use this DI container to also inject in repositories, set environments, read configuration etc.

The one thing it’s not yet is a windows service…

Turning Our App Into A Windows Service

We need to add the following package to our app :

Install-Package Microsoft.Extensions.Hosting.WindowsServices

Next, head to our program.cs file and modify it by adding a call to “UseWindowsService()”.

public static IHostBuilder CreateHostBuilder(string[] args) =>
    Host.CreateDefaultBuilder(args)
    .ConfigureServices((hostContext, services) =>
    {
        services.AddHostedService<Worker>();
    }).UseWindowsService();

And that’s it!

Running our application normally is just the same and everything functions as it was. The big difference is that we can now install everything as a service.

To do that, first we need to publish our application. In the project directory we run :

dotnet publish -r win-x64 -c Release

Note in my case, I’m publishing for Windows X64 which generally is going to be the case when deploying a Windows service.

Then all we need to do is run the standard Windows Service installer. This isn’t .NET Core specific but is instead part of Windows :

sc create TestService BinPath=C:\full\path\to\publish\dir\WindowsServiceExample.exe

As always, the other commands available to you (including starting your service) are :

sc start TestService
sc stop TestService
sc delete TestService

And checking our services panel :

It worked!

Installing On Linux

To be honest, I don’t have a hell of a lot of experience with Linux. But the general gist is…

Instead of installing Microsoft.Extensions.Hosting.WindowsServices , you need to install Microsoft.Extensions.Hosting.Systemd . And then instead of calling UseWindowsService()  you’ll instead call UseSystemd() .

Obviously your dotnet publish and installation commands will vary, but more or less you can create a “Windows Service” that will also run on Linux!

Microsoft vs Topshelf vs .NET Core Workers

So we’ve now gone over 3 different ways to create Windows Services. You’re probably sitting there going “Well… Which one should I chose?”. Immediately, let’s bin the first Microsoft old school way of going things. It’s hellacious to debug and really doesn’t have anything going for it.

That leaves us with Topshelf and .NET Core workers. In my opinion, I like .NET Core Workers for fitting effortlessly into the .NET Core ecosystem. If you’re already developing in ASP.NET Core, then everything just makes sense creating a worker. On top of that, when you create a BackgroundService, you can actually lift and shift that to run inside an ASP.NET Core website at any point which is super handy. The one downside is the installation. Having to use SC commands can be incredibly frustrating at times and Topshelf definitely has it beat there.

Topshelf in general is very user friendly and has the best installation process for Windows Services. But it’s also another library to add to your list and another “framework” to learn, which counts against it.

Topshelf or .NET Core Workers, take your pick really.

These days, people who are considered “fullstack” developers are considered to be unicorns. That is, it’s seemingly rare for new grad developers to be fullstack, and instead they are picking a development path and sticking with it. It wasn’t really like that 10-15 years ago. When I first started developing commercially I obviously got to learning ASP.NET (Around .NET 2-ish), but you better believe I had to learn HTML/CSS and the javascript “framework” of the day – jQuery. There was no “fullstack” or “front end” developers, you were just a “developer”.

Lately my javascript framework of choice has been Angular. I started with AngularJS 1.6 and took a bit of a break, but in the last couple of years I’ve been working with Angular all the way up to Angular 8. Even though Angular’s documentation is actually pretty good, there’s definitely been a few times where I feel I’ve cracked a chestnut of a problem and thought “I should really start an Angular blog to share this”. After all, that’s exactly how this blog started. In the dark days of .NET Core (before the first release even!), I was blogging here trying to help people running into the same issues as me.

And so, I’ve started Tutorials For Angular. I’m not going to profess to be a pro in Angular (Or even javascript), but I’ll be sharing various tips and tricks that I’ve run into in my front end development journey. Content is a bit light at the moment but I have a few long form articles in the pipeline on some really tricky stuff that stumped me when I got back into the Angular groove, so if that sounds like you, come on over and join in!

Rate limiting web services and APIs is nothing new, but I would say over the past couple of years the “leaky bucket” strategy of rate limiting has rised in popularity to the point where it’s almost a defacto these days. The leaky bucket strategy is actually quite simple – made even easier by the fact that it’s name comes from a very physical and real world scenario.

What Is A Leaky Bucket Rate Limit?

Imagine a 4 litre physical bucket that has a hole in it. The hole leaks out 1 litre of water every minute. Now you can dump 4 litres of water in the bucket all at once just fine, but still it will flow out at 1 litre per minute until it’s empty. If you did fill it immediately, after 1 minute, you could make 1 request but then have to wait another minute. Or you could wait 2 minutes and make 2 at once etc.  The general idea behind using a leaky bucket scenario over something like “1 call per second” is that it allows you to “burst” through calls until the bucket is full, and then wait a period of time until the bucket drains. However even while the bucket is draining you can trickle in calls if required.

I generally see it applied on API’s that to complete a full operation may take 2 or 3 API calls, so limiting to 1 per second is pointless. But allowing a caller to burst through enough calls to complete their operation, then back off, is maybe a bit more realistic.

Implementing The Client

This article is going to talk about a leaky bucket “client”. So I’m the one calling a web service that has a leaky bucket rate limitation in place. In a subsequent article we will look at how we implement this on the server end.

Now I’m the first to admit, this is unlikely to win any awards and I typically shoot myself in the foot with threadsafe coding, but here’s my attempt at a leaky bucket client that for my purposes worked a treat, but it was just for a small personal project so your mileage may vary.

class LeakyBucket
{
    private readonly BucketConfiguration _bucketConfiguration;
    private readonly ConcurrentQueue<DateTime> currentItems;
    private readonly SemaphoreSlim semaphore = new SemaphoreSlim(1, 1);

    private Task leakTask;

    public LeakyBucket(BucketConfiguration bucketConfiguration)
    {
        _bucketConfiguration = bucketConfiguration;
        currentItems = new ConcurrentQueue<DateTime>();
    }

    public async Task GainAccess(TimeSpan? maxWait = null)
    {
        //Only allow one thread at a time in. 
        await semaphore.WaitAsync(maxWait ?? TimeSpan.FromHours(1));
        try
        {
            //If this is the first time, kick off our thread to monitor the bucket. 
            if(leakTask == null)
            {
                leakTask = Task.Factory.StartNew(Leak);
            }

            while (true)
            {
                if (currentItems.Count >= _bucketConfiguration.MaxFill)
                {
                    await Task.Delay(1000);
                    continue;
                }

                currentItems.Enqueue(DateTime.UtcNow);
                return;
            }
        }finally
        {
            semaphore.Release();
        }
                
    }

    //Infinite loop to keep leaking. 
    private void Leak()
    {
        //Wait for our first queue item. 
        while(currentItems.Count == 0)
        {
            Thread.Sleep(1000);
        }

        while(true)
        {
            Thread.Sleep(_bucketConfiguration.LeakRateTimeSpan);
            for(int i=0; i < currentItems.Count && i < _bucketConfiguration.LeakRate; i++)
            {
                DateTime dequeueItem;
                currentItems.TryDequeue(out dequeueItem);
            }
        }
    }
}

class BucketConfiguration
{
    public int MaxFill { get; set; }
    public TimeSpan LeakRateTimeSpan { get; set; }
    public int LeakRate { get; set; }
}

There is a bit to unpack there but I’ll do my best.

• We have a class called BucketConfiguration which specifies how full the bucket can get, and how much it leaks.
• Our main method is called “GainAccess” and this will be called each time we want to send a request.
• We use a SemaphoreSlim just incase this is used in a threaded scenario so that we queue up calls and not get tangled up in our own mess
• On the first call to gain access, we kick off a thread that is used to “empty” the bucket as we go.
• Then we enter in a loop. If the current items on the queue is the max fill, then we just wait a second and try again.
• When there is room on the queue, we pop our time on and return, thus gaining access.

Now I’ve used a queue here but you really don’t need to, it’s just helpful debugging which calls we are leaking etc. But really a blockingcollection or something similar is just as fine. Notice that we also kick off a thread to do our leaking. Because it’s done at a constant rate, we need a dedicated thread to be “dripping” out requests.

And finally, everything is async including our semaphore (If you are wondering why I didn’t just use the *lock* keyword, it can’t be used with async code). This means that we hopefully don’t jam up threads waiting to send requests. It’s not foolproof of course, but it’s better than hogging threads when we are essentially spinwaiting.

The Client In Action

I wrote a quick console application to show things in action. So for example :

static async Task Main(string[] args)
{
    LeakyBucket leakyBucket = new LeakyBucket(new BucketConfiguration
    {
        LeakRate = 1, 
        LeakRateTimeSpan = TimeSpan.FromSeconds(5), 
        MaxFill = 4
    });

    while (true)
    {
        await leakyBucket.GainAccess();
        Console.WriteLine("Hello World! " + DateTime.Now);
    }
}

Running this we get :

Hello World! 24/11/2019 5:08:26 PM
Hello World! 24/11/2019 5:08:26 PM
Hello World! 24/11/2019 5:08:26 PM
Hello World! 24/11/2019 5:08:26 PM
Hello World! 24/11/2019 5:08:31 PM
Hello World! 24/11/2019 5:08:36 PM
Hello World! 24/11/2019 5:08:41 PM
Hello World! 24/11/2019 5:08:46 PM

Makes sense. We do our run of 4 calls immediately, but then we have to back off to doing just 1 call every 5 seconds.

That’s pretty simple, but we can also handle complex scenarios such as leaking 2 requests instead of 1 every 5 seconds. We change our leaky bucket to :

LeakyBucket leakyBucket = new LeakyBucket(new BucketConfiguration
{
    LeakRate = 2, 
    LeakRateTimeSpan = TimeSpan.FromSeconds(5), 
    MaxFill = 4
});

And what do you know! We see our burst of 4 calls, then every 5 seconds we see us drop in another 2 at once.

Hello World! 24/11/2019 5:10:09 PM
Hello World! 24/11/2019 5:10:09 PM
Hello World! 24/11/2019 5:10:09 PM
Hello World! 24/11/2019 5:10:09 PM
Hello World! 24/11/2019 5:10:14 PM
Hello World! 24/11/2019 5:10:14 PM
Hello World! 24/11/2019 5:10:19 PM
Hello World! 24/11/2019 5:10:19 PM
Hello World! 24/11/2019 5:10:24 PM
Hello World! 24/11/2019 5:10:24 PM