Andy Crouch - Code, Technology & Obfuscation ...

Parsing Excel Files in .Net Core

Writing In Ledger Under Lense

Photo: Unsplash

.Net Core development is both fun and frustrating.

It’s like working in a multiverse where everything is the same only slightly different. It’s familiar enough and seems like the .Net we all love. However, it’s frustrating when you attempt to add code that is not supported or implemented. Framework features you have taken for granted for years no longer exist and libraries that you use over and over are no longer supported. The situation is evolving at an impressive pace though. See Mark Woodhall’s “dotnet core package a day” series as an example. New libraries are appearing all the time and existing ones are being polished very quickly.

As part of our current development work at Open Energy Market, I have been writing an Excel file parser. On .Net 4.6 there are a plethora of libraries for reading and parsing Excel data into models. My recent favourite was LinqToExcel. These libraries all supported both the old and new file formats used by Excel. This is an interesting point. The new xslx format is over 10 years old so “new” might be a wrong term.

On .Net Core the options are much more restricted and I found the best option was an unofficial port of EPPlus. This is super simple to install via NuGet:

PM> Install-Package EPPlus.Core 

Reading and parsing data from a xslx file is very straightforward. (Note there is a lack of error handling and file creation for clarity).

Code To Read Excel Files In EPPlus

Writing data to a file is also very easy.

Code To Write Excel Files In EPPlus

Both of these snippets are reading and writing standard tabular Excel data. Unfortunately, some of the files I was parsing are laid out in a “unique” manner. Some of our suppliers like to provide invoice data using Excel instead of the more suitable pdf or word formats. So I needed a way to parse blocks of defined cells (named ranges). This was also easy using the Cells method of the Worksheet object. For example, the following is reading the B6 to E12 range.

Code To Read A Range In Excel Files In EPPlus

EPPlus is a great library. It makes working with xslx files trivial and is by far and away the only serious option on .Net Core.

.Net Core On Arch Linux

Microsoft .Net Logo

Photo: Microsoft

I work a lot with .Net.

When I am writing non .Net code I develop usually in a Linux based environment. I have been around long enough to remember when Linux was a “cancer” in Microsoft’s eyes. I have to admit that I never thought I would see the collaborative stance than Microsoft has adopted. Over the next year, we will be able to build .Net based applications and SQL Sever databases on Linux. Even Azure has Linux based app services due for release soon.

So, all excited, I and a colleague set about building some .Net Core based services. We both were running Antergos (Arch derivative), he was using his Vim environment and I was trying out VS Code.

Before the chants of “Arch is not supported by .Net core” begin - we know! This was an experiment in existing environments.

To setup the development environment you need to install the following two (AUR) packages:

  • dotnet-cli
  • dotnet-sdk

First the good.

No Visual Studio. This is a personal comment but the longer I use Visual Studio the more I do not like it anymore. It’s not that is a bad application, it is because it is a big, bloated, application. It is trying to be too many things. It is slow, error prone and has features I spend time turning off. Most modern text editors have plugins for colour schemes, IntelliSense and code centric features. You can make your editor work as you want by choice rather than having to endure someone else’s idea of how it works.

Visual Studio Code is a promising editor. Given how young it is (in editor years) I found that with a few select plugins I could be productive very quickly. It seems fairly flexible and it was easy to configure the build targets to get up and running.

.Net core has followed the lead of other frameworks. The terminal can now be used to create, configure and manage applications.

$ dotnet new console 
$ dotnet restore 
$ dotnet run

Running the above code in a terminal creates and runs a “hello world” style application. There is excellent documentation here.

The command line tool is quite well thought through. You find you quickly learn the commands for creating applications and adding references. NuGet is present and still one of the better dependency managers I have used.

(As an aside someone recently on Hacker News suggested that they should replace NuGet with npm. I was killing myself with laughter until I realised it was not 1st April!)

Now the bad (Linux General)

You cannot debug on Linux. If you can I sure as hell couldn’t get it to work. All “what do you need a debugger for” jokes aside, every option I tried came up dry. I installed the Rider IDE and loaded up the project and …. nothing. Googling for solutions led to some articles all bemoaning licensing issues. All these articles also promised to have .Net Core debugging is upcoming releases. This is a massive omission.

Now the bad (Arch Specific)

.Net core development on Antergos/Arch right now is a no go.

We worked within our test environment for a couple of weeks. The first of which were fine. Then an update broke SSL compatibility. We had already patched packages to work with a specific SSL version to get a working SQL Server connection. Then another update flat out broke the .Net environment. This was down to a clang update. This was where our test ended. While I love to work in a terminal-based environment I need one that will allow me to be productive. Until Antergos/Arch becomes a supported platform I will continue my .Net Linux journey on a supported distro in a VM/Docker image.

Dotfiles and Zsh

Z Shell Screenshot

Photo: Andy Crouch

The trouble with getting older is the amount of baggage you accumulate.

I have worked across various Linux and Windows machines in the last 18 odd years. Since 2002 when I installed Slackware over the course of a weekend, I have rarely been without a terminal. For years I ran Cygwin when I was on a Windows machine. Recently I found Babun which is the best *nix environment on Windows. I will write more details on that soon.

When you use the terminal you tend to find little tweaks and changes start to creep into your rc and profile files. Over time and after installing many tools and frameworks, your files start to look tired. You find you have blocks commented out. Configurations for tools you have long abandoned and settings which make no sense. Over the years I have included tweaks for different machines and terminal variants. Most of these are no longer required.

So I have decided to prune and more or less start over with my dotfiles. I will be updating my setup and documenting it as I go here on the blog. I will also make my configuration available on GitHub once I have it working in a basic fashion.

I decided to look at how I use the terminal first.

Zsh

Bash has been my terminal of choice right up until about a year ago. I had created a great framework which allowed me to load shared configuration. Machine dependent configuration was sourced by host name. It worked well and meant that I stared at this prompt for about 15 years.

Bash Shell Screenshot

I had come across z shell (zsh) when installing Arch. I wasn’t really blown away, perhaps a little indifferent. Then I read an article (which I can no longer find) that sold me on switching to the z shell (zsh) based on the productivity gains.

Wikipedia describes zsh as:

Zsh is an extended Bourne shell with a large number of improvements, including some features of bash, ksh, and tcsh.

Benefits of zsh

I started to write a long parapgraph on the benefits of zsh. However, I came across this excellent slide deck (by Brendon Rapp) which covers many of the benefits of zsh much better than I can:

Setup and configuration

So I have switched full time to zsh. For Arch users, this link describes the process of switching your shell from Bash to zsh. The first time you fire zsh up it will walk you through a setup wizard. This will set up a default terminal experience. This is fine but what you want to do next is install the excellent oh-my-zsh framework.

oh-my-zsh contains hundreds of plugins and themes that make using zsh even better. Installation details can be found on the project page but it is as simple as

$ sh -c "$(curl -fsSL https://raw.githubusercontent.com/robbyrussell/oh-my-zsh/master/tools/install.sh)"

The project page covers enabling plugins and setting themes so I will not rehash that here. For convenience, there are screenshots of most themes.

I saw a co-worker using powerline status bars in vim, tux and shell and decided to go for something similar. There is a simple powerline type theme in oh-my-zsh called agnoster. It was fine but I had read about Powerline 9K which I decided to try as it is more configurable. Full installation details are here for Powerline 9K. Powerline 9K is very configurable and allows you to configure segments within your prompt. There are segments already available for many programming environments and git.

Update On New Laptop

Laptop Phone And Notebook

Photo: Unsplash

Well, I finally got the time to order the new development laptop I first mentioned I was in need of here.

So what did I go for?

PC Specialist won again purely on the price vs spec front. I looked around at what laptop offers were out there and I could find nothing to compete with PC Specialist.

So what did I order you ask? Well:

  • Cosmos Series case and screen (1920 x 1080)
  • i5 Quad Core Processor 7300HQ 2.5GHz
  • 32GB Kingston SODIMM DDR4 2133MHz
  • 256GB SAMSUNG SM961 M.2, PCIe NVMe
  • 500GB SERIAL ATA III 2.5” HARD DRIVE WITH 32MB CACHE (7,200rpm)
  • NVIDIA® GeForce® GT 940MX - 2.0GB DDR5 Video RAM - DirectX® 12
  • No OS

It came in at £25 more than my set budget of £1000 but I couldn’t help get the DDR4 RAM.

The case is plastic but sturdy. It will not win a beauty contest with a Mac but resembles more of an Alienware case. The screen is very clear and glare-free and I would never go back to anything but an FHD screen anyway. The keyboard is not as nasty as some but is not a Thinkpad keyboard. There is a decent amount of travel and the layout follows the standard UK design. As far as powering Visual Studio, I set it up with Visual Studio 2017. In first tests, I am loading our large solution in about 25 seconds which is a massive improvement.

All in all I am pleased with the laptop. One of our new developers also purchased a 14” model with similar specs and he is using it with 3 external monitors. He was also pleased to get such good spec components for such a good price.

Now time to focus on code rather than hardware.

Too Much Choice

Footpath Crossover

Photo: Unsplash

How many choices do we make on an average day? Depending on the source you believe, the average adult makes around 35,000 decisions a day. These cover the mundane what to wear and what to eat through to what job to accept and where to live. Who do you want to marry and do you want children?

Recently I have found myself overwhelmed by choices. I become consumed by the variations on offer and procrastinated for prolonged periods. I am not talking about at meal times or when I get out of the shower and have to choose what to wear. I am talking about everything above that from what to do on a free day off to purchases. I am talking about the architecture and code choices I make for applications I write. As far as booking a holiday this year it took about 3 months. I settled on many different destinations until I found somewhere more appealing. I have always tended to over think things but as I have got older there feels like more choices are available. In talking to my wife I believe that the issue is the internet and the ability it gives you to research your choices. Even when in a shop about to buy something I am on my phone checking for better options.

In writing this piece I am choosing what to add and what to omit. Three days I have been redrafting it.

I am not alone in feeling this way. There have been many papers and books authored on the subject. The research shows that people are unable to handle large choice variations. They are more likely to make a snap decision to avoid the task of wading through the options. Based on the number of options presented research has shown that a snap decision may work out the best. Tesco (the UK supermarket) is reducing its product lines by around 30,000 items this year. The reason? The realisation that its fastest growing competitor, Aldi, offered very little choice. Tesco at the start of the year offered an unbelievable 28 varieties of Tomato sauce. Aldi offered one. It appears that the buying public does not need a mixture of packaging and product sizes. They want a fair priced item. They do not want to have to stand and think about Tomato sauce. The want to pick it up and buy it and move on to more interesting things.

Having used Linux for close to 20 years I have found the choices it offers to be empowering and a distraction. Repositories of packages have allowed me to create and build software with ease. It has no doubt increased my productivity as a developer. It has also lead me to wast hours on trying distributions and desktops. Tweaking configuration files to get settings how I want them. Trying themes to rectify some, frankly, hideous UI’s. And then there is Arch. I love Arch as a distribution and it would take a lot for me to leave it. While I know you can install it from scratch in a couple of hours I can get lost in the wiki. It is so deep and offers so many installation options I have lost days to installing it. So many packages and configurations, so much choice.

This is all the polar opposite of Apple and their approach to software. One core application per task with two theme options out of the box. How many people that you know that own Apple gear tweak their UI or need to install many applications to get their work done?

“choose less and feel better.”
Barry Schwartz - “The Paradox of Choice: Why More Is Less”

Words to adhere to but how to achieve it? I do not know but I am starting to look at each part of my life and the changes required. I have started by looking at the tools and environment changes I make to work. I have switched to Antegos for installing Arched based systems. I download the minimal ISO and have a Gnome based desktop (which I theme with Evopop) up and running in 15 minutes. This saves me a vast amount of time over installing vanilla Arch. Both my Windows and Linux environments are being scripted so I can restore them to a new machine quickly. I am working on cleaning up my dotfiles once and for all. I am limiting myself to one application per task. If I find a new application for a given task I am updating my setup and removing the old one. Considering I spend on average 10 hours a day in front of a computer this is already paying off time wise.

I am applying a new way of making purchases. I allow myself only one initial research session to select the best 3 options (based on requirements and price). I then make my decision based on predefined criteria and actually walk away from the internet. I found this useful recently as we have renovated the whole of our house. Before I would have lost vast amounts of time to selecting new furniture. To pick new items for 4 rooms I had it selected and purchased in 2 days. My wife is astounded and I have more time to do important things.

I am also taking the less is more approach with UI’s for applications I work on as they tend to offer too much choice. Look at Word from Microsoft or Writer from LibreOffice. The options and peripheral functionality on offer overwhelms users of the application. These applications should allow the user to focus on writing their content. This is why applications such as ia Writer or FocusWriter have such a good reputation, they strip the choices out. You have a screen to type text and you can insert tables and images. It is all you need to craft your prose. No distractions and no choices.

In rebuilding Open Energy Market’s core application I am looking at the options presented to the user. I am looking to strip away confusion and reduce the number of choices a user has to make. Do we need to ask this particular question? Can we default this pages values while providing a way that if they need changing the user can? The irony is that in undertaking this work I am presented with many options about the way to design it. I am back to having to make choices.