Working with Excel files using EPPlus and XLSX.JS

February 29, 2016 C#, JavaScript, Opinion, QuickTip, Tools No comments

Need to quickly generate an Excel file on the server (.NET) with no headache or need to import  another Excel file in your JS client?

I can recommend two libraries for their simplicity and ease of use.


To smooth transitioning from Excel files to electronic handling of data we offered our users possibility of importing data. As our application is web based it meant some JS library to work with Excel files. A bit of complication was that our users over time developed a habit of having all kinds of modifications in their “custom” Excel files. So something that would allow us easily work with different formats was a preference.

XLSX.JS library available on GitHub proved to be a good choice. I could only imagine how much better it is over some monsters that would only work in IE. I think starting documentation is fairly good, so I will just go through some bits and pieces from our use case.

Setting up XLSX.JS and reading files is straight forward: npm or bower, include of file and you are ready to write XLSX.readFile('test.xlsx') or App.XLSX.read(excelBinaryContents, {type: 'binary'}).

Reading as binary is probably a better bet as it will work in IE, though you will have to write some code to implement FileReader.prototype.readAsBinaryString() in IE. You can have a look at our implementation of file-select component on gist.

Using XLSX in your JavaScript is fairly easy, though there might be some hiccups with parsing dates. See this gist.


We also have two use cases where we need to generate Excel file on the server. One was to generate some documentation for business rules so we can have it up to date and share with our users at all times. It was implemented as part of CI that would save a file to a file system. The other use case was downloading of business related data via web interface. These two were super easy to do with open source library called EPPlus.

You just add EPPlus through NuGet and start using (var excelPackage = new ExcelPackage(newFileInfo)). See the gist below. First file demonstrates how to operate with cells and the other one just shows how you can use streams to make file downloadable.

These two libraries really helped me to efficiently implement some of the Excel file business use cases.

Next time I will have to generate Excel file on server or read it on client I will most certainly use these two again.

No comments

Book Review: C# in Depth

November 27, 2015 .NET, Book Reviews, C# No comments

C# in Depth, 3rd EditionI’ve been writing C# code for more than 10 years by now and yet had a lot to learn from the book.

The book is written by Jon Skeet (the guy Number One at StackOverflow) and is a purely about the C# language. It distances itself from the .NET framework and libraries supplementing the framework.

Structure of the book follows C# language versions. This isn’t particularly useful if you are trying to learn the language by reading this book after some other introductory book. But for software professionals, though, it could be a joy, since it takes you through your years of experience with the language. It tends to remind you about times when something wasn’t possible and later it became possible. Kind of nostalgia, I would say.

First chapters could be somewhat boring. I read the book cover to cover, since I didn’t want to miss on anything, but probably it isn’t the best way to read the book.

Jon is very pedant when it comes to defining anything. This, of course, is double sided: very good when you have strong knowledge and understand components of the definition, but, unfortunately, it complicates understanding. There were some places in the book which I had to read few times to completely understand. Just for instance, in a chapter about Covariance and Contravariance:

[…] the gist of the topic with respect to delegates is that if it would be valid (in a static typing sense) to call a method and use its return value everywhere that you could invoke an instance of a particular delegate type and use its return value, then that method can be used to create an instance of that delegate type.

Jon Skeet. C# in Depth. Kindle Edition.

On the other hand, I found it very important that details are not omitted. I was reading the book for the depth of it. So details and preciseness is exactly what I was expecting, even though they come with the price of slower comprehension.

You may have different background than I do, but if you are a .NET developer with some years of experience, chances are the only chapters with new and difficult information will be those that are not reflecting everyday practical usage of C# language. For example, all of us use LINQ these days, but very few of us would need to know how it works internally or need to implement their own LINQ provider. Very few of us would dig into DLR or how async/await is working.

Almost in each and every chapter there was something where I could add to my knowledge. For me the most important chapter to read was “Chapter 15. Asynchrony with async/await”, since I have very limited experience in this feature.


In my opinion, the best two books ever for C# programmers are “CLR via C#” and “C# in Depth”, meaning that I just added one to this list.

“C# in Depth” is a great, precise, and thorough book to complement and enrich your knowledge.

I highly recommend reading it.

No comments

What is the point of the ‘event’ keyword in C#?

October 31, 2015 .NET, C# No comments

Do you know what a delegate and event are in C#? Can you clearly explain in one-two sentences what the difference between these two is?

It might sound simple as we, .net developers, use these quite frequently. Unfortunately it isn’t that straight forward, especially when you want to be precise. Just try it now aloud.

First difficulty is when you try to differentiate between the delegate type and an instance of a delegate. Next difficulty is when you try to explain events. Your explanation may mention subscribing and unsubscribing to an event. But, hey, you can do exactly the same with a delegate instance by using exactly same “+=” and “-=”. Have you thought about this? The real difference is that with an exposed event it’s about all your external code can do. Instead with an exposed delegate instance external code can do other things like changing the whole invocation list by using “=” or invoking a delegate right away.

An event is nothing more than encapsulation convenience over delegate provided by C# language.

On a conceptual level, an event is probably much more than just some convenience, but that’s beside the point in this post.

To feel the difference all you need is to write some code. Nothing helps to understand things better than writing code and reading quality resources.

Please see below my try on understanding the difference between events and delegates. I added plenty of comments in a try to be explanatory.

Click here to see the gist

Person’s sickness event triggering is responsibility of a person’s internals (stomach, in this example) and it is correct to be encapsulated in the Person’s class, otherwise external code would be able to make a person sick (I like this double-meaning).

Next step is to understand what C# compiler generates when you use the ‘event’ keyword and how else you can declare an event other than in a field-like style. I don’t describe it in this post, I’ve only read about these details, but they are quite interesting as well.

Proficiency in programming language comes with a deep understanding of the basics. I’m proving this to myself every now and then.

No comments

Simple check for breaking Database changes using NUnit and T-SQL CHECKSUM_AGG

October 1, 2015 C#, HowTo, QuickTip, SQL No comments

Imagine that your database is being used by another system that directly reads data from few of the tables. I know this is not a good idea and I would not recommend anyone to do anything similar. Unfortunately developers often do what they don’t really want to do.

In this post I just want to document a very simple way of verifying that you are not removing or modifying tables and columns that are being used by someone else.

My solution is dumb unit tests that select table schema information for the list of tables and columns and then builds checksum that is finally compared to expected checksum.

I don’t pretend to sound like this somewhat dirty solution is a right way, but it is very quick to implement and effective way. Here is the implementation in C#:

Click here to see the gist.


In code above we verify that we are not removing or modifying few columns in tables Table1 and Table55, since they are used by SystemX. NUnit TestCase attributes are used to allow for multiple table checks. First parameter is a checksum that you would get for the first time by failing the unit test or by running query separately. Checksum calculation is simple but it has a bit of overburden with concatenating column names into one string and then parsing it again into a temp table for the “IN” condition. (Of course there are other ways, but I find this one to be a bit less code). Also it probably worth to mention that we apply database changes using FluentMigrator, so tracking what exactly was changed that caused particular Unit Test case to fail would be just to have a look at the latest commit.

I think this is acceptable solution for a small set of checks. Probably you need another solution if your database is heavily used by another systems.

Hopefully is helpful to someone. At least I write this post to have it handy for myself in case I need something similar done quickly.

No comments

GMT vs. UTC and Time Zones in .NET

May 26, 2013 .NET, C# No comments

Recently I had correspondence with HR from Great Britain. In one of the e-mails I wrote “16:00 CEST (UTC+2) would work for me” which confused the guy, so he asked what GMT UK time it is. If I was too much pedantic I could have replied that GMT is ~same as UTC and that in GMT it is 14:00. But of course it would confuse him even more. I replied with “3PM” as it was corresponding time in UK.
Problem is that 3PM (or 15:00) is not GMT time, but rather “GMT Standard Time + Daylight Saving Time” and when you look it up it is better called BST – British Summer Time. On the other hand I also understand that for many people it is much easier to refer to their time zone as GMT (maybe for historical reasons).
I wouldn’t write this blog post if confusion was only in heads of ordinary people. Unfortunately big enterprises and small startups still frequently fail to write proper time zone handling in their applications.
Lets start with this: We no longer argue whether Earth is orbiting Sun or the other way around, the same way we no longer depend on some astronomical events to measure time, although none would like if at 1PM it was night. There is atomic clock invented which is ultimate truth of time, but since Earth is not spinning that precisely Coordinated Universal Time (UTC) has been developed. GMT is historical term and it would be great to leave it aside.
If you want to understand modern time measurements I would strongly recommend to read these two articles: Coordinated Universal Time (UTC) Explained and GMT and Other Time Systems Explained.
Now bit more for not so ordinal people
When it comes to source code things are not getting much cleaner. Answer to question “Difference between UTC and GMT Standard Time in .NET” at SO:
GMT does not adjust for daylight savings time. You can hear it from the horse’s mouth on this web site. Add this line of code to see the source of the problem:
Console.WriteLine(TimeZoneInfo.FindSystemTimeZoneById("GMT Standard Time").SupportsDaylightSavingTime);


Output: True.
This is not a .NET problem, it is Windows messing up. The registry key that TimeZoneInfo uses is HKLMSOFTWAREMicrosoftWindows NTCurrentVersionTime ZonesGMT Standard Time
You’d better stick with UTC.
And I partially agree that it is Windows messing up for few reasons. If you look at corresponding to registry entry “GMT Standard Time” UI:


you will notice that it is displayed as UTC, but it is also DST adjustable. Now when you read this MSDN page about TimeZoneInfo, you will get an impression that “standard times” are not DST adjustable, but when converting in .NET it actually takes daylight into account. Concluding I’m afraid that when dealing with time zone conversions we have to be very cautious.

Conversion itself can be done utilizing class TimeZoneInfo (same link again):


TimeZoneInfo GMT = TimeZoneInfo.FindSystemTimeZoneById("GMT Standard Time");
DateTime postDateUtc = TimeZoneInfo.ConvertTimeToUtc(dbRecord.PostDateInLocalLondon, GMT);

And please be careful. You would need some exception handling and be ready for not-existing or ambiguous time. For example, see how this:

TimeZoneInfo GMT = TimeZoneInfo.FindSystemTimeZoneById("GMT Standard Time");
DateTime notExistingInGmtTime = new DateTime(2013, 3, 31, 1, 30, 0, DateTimeKind.Unspecified);
DateTime dateInUtc = TimeZoneInfo.ConvertTimeToUtc(notExistingInGmtTime, GMT);

would throw an exception. Reason is that there is no such time as 1:30AM on 31 March 2013 in London.

How to work with Time, Dates and Time Zones in .NET
Everything starts with basics. If you get them wrong how do you expect to write quality code? It is not a shame to read again about DateTime or even Boolean. Deep inside there is always something hidden – it just depends on depth.
In my career I’ve experienced quite many (as for such fundamental concept) different problems and inconveniences because of incorrect handling of time.
My rules of thumb:
  • Always persist date and time in UTC, otherwise save offset, but never-ever local time
  • Use DateTimeOffset – it is DateTime v2, otherwise at least make sure to avoid DateTime pitfalls
  • Be explicit about TimeZone used, in APIs I would also call date&time fields with suffix “Utc” (ugly?)
  • Convert to local time just before displaying
  • … and check for best practices over internet
Before finishing this post I would like to mention I have a task for myself as well: exploring Noda Time. If you haven’t read What’s wrong with DateTime anyway? you should!
… and, sorry but had to copy one final issue
If it is Coordinated Universal Time, why is the acronym UTC and not CUT? When the English and the French were getting together to work out the notation, the french wanted TUC, for Temps Universel Coordonné. UTC was a compromise: it fit equally badly for each language. :)

No comments

OData service with WCF and data in memory

May 20, 2012 C#, OData, Web 3 comments

In previous blog post I briefly touched OData protocol by showing quick usage of OData feed and then implemented ever simple WCF data service with using Entity Framework.

Now, let’s imagine that we have some data in memory and would like to expose it. Is it hard or easy?

Querying in memory data

If you can represent your data as IQueryable then, for most cases, you are fine. There is an extension method to IEnumerable called AsQueryable, so as long as your data can be accessed as IEnumerable you can make it IQueryable.

Sample data kept in memory

Now, let’s create some sample data:

private IList<Sport> _sports = new List<Sport>();
private IList<League> _leagues = new List<League>();

private void Populate()
    _sports = new List<Sport>();
    _leagues = new List<League>();

    _sports.Add(new Sport() { Id = 1, Name = "Swimming"});
    _sports.Add(new Sport() { Id = 2, Name = "Skiing"});

    var sport = new Sport() { Id = 3, Name = "Football"};
    var league = new League() { Id = 1, Name = "EURO2012", Region = "Poland&Ukraine" };
    var league1 = new League() { Id = 2, Name = "UK Premier League", Region = "UK" };

    var league2 = new League() { Id = 3, Name = "Austria Premier League", Region = "Austria" };

    _sports.Add(new Sport() { Id = 4, Name = "Tennis"});
    _sports.Add(new Sport() { Id = 5, Name = "Volleyball"});

Absolutely nothing smart or difficult there, but at least to give you an idea about dataset we have.

Few things to make it happen

To expose sports and leagues we would need to add public properties to our data service implementation like below:

public IQueryable<Sport> Sports { get { return _sports.AsQueryable(); } }
public IQueryable<League> Leagues { get { return _leagues.AsQueryable(); } }

Another important thing about exposing data is that you have to indicate key for your entities with DataServiceKey attribute.

To make our service bit more realistic I’m going to add caching as well.

Complete source code of service
public class SportsWcfDataService : DataService<SportsData>
    public static void InitializeService(DataServiceConfiguration config)
        config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
        config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
        config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
        // To know if there are issues with your data model
        config.UseVerboseErrors = true;

    protected override void HandleException(HandleExceptionArgs args)
        // Put breakpoint here to see possible problems while accessing data

    protected override SportsData CreateDataSource()
        return SportsData.Instance;

    protected override void OnStartProcessingRequest(ProcessRequestArgs args)
        var cache = HttpContext.Current.Response.Cache;
        cache.VaryByHeaders["Accept"] = true;
        cache.VaryByHeaders["Accept-Charset"] = true;
        cache.VaryByHeaders["Accept-Encoding"] = true;
        cache.VaryByParams["*"] = true;

public class SportsData
    static readonly SportsData _instance = new SportsData();

    public static SportsData Instance
        get { return _instance; }

    private SportsData()

    private void Populate()
        // Population of data
        // Above in this post 

    private IList<Sport> _sports = new List<Sport>();
    public IQueryable<Sport> Sports { get { return _sports.AsQueryable(); } }

    private IList<League> _leagues = new List<League>();
    public IQueryable<League> Leagues { get { return _leagues.AsQueryable(); } }

public class Sport
    public int Id { get; set; }
    public string Name { get; set; }
    public void AddLeague(League league)

    private IList<League> _leagues = new List<League>();
    public IEnumerable<League> Leagues { get { return _leagues; } }

public class League
    public int Id { get; set; }
    public string Name { get; set; }
    public string Region { get; set; }
Some results of our work

With this URL http://localhost:49936/SportsService.svc/ service can be seen:

<service xml:base="http://localhost:49936/SportsService.svc/">
  <collection href="Sports"><atom:title>Sports</atom:title></collection>
  <collection href="Leagues"><atom:title>Leagues</atom:title></collection>

Now, you can access data via URL or by writing C# Linq queries if connected with client app or LinqPad. Following request:

http://localhost:49936/SportsService.svc/Leagues()?$filter=Name eq ‘EURO2012’&$select=Region

Would produce result containing this:


What if you need to edit data?

If you need your data to be updatable, your SportsData would need to implement System.Data.Services.IUpdatable interface.

What if your datasource is not just few collections in memory?

What if you have very special data source, or you cannot simply keep your data in memory like collections of some data? This could be tricky or very tricky, depending on your data source, of course. But anyway you would need to implement interface IQueryable by hand if not provided by your data source.

Here is step-by-step walkthrough on msdn. Only by size of article you could imagine it is not trivial task to do. But if you managed to do it you can be proud, because you can implement your own Linq provider. (Do you remember NHibernate introducing support of Linq? It was big feature for product, roughly all they did was implementation of IQueryably by their NhQueryable.)

Why do I learn OData?

First of all I have to investigate if my team can use it. (Depending on outcome you might see more posts on this topic from me.) And another reason is that it is very exciting topic, about which I knew so little.

Very disappointing to admit that I start to really understand things when I touch them for reason and that reading dozen of blog posts on topic is useless unless I do something meaningful related to matters in those posts.

So, my Dear Reader, if you are reading this post and have read my previous post, but never tried OData I would be really pleased if you invest 10-30 or more minutes of your time to play with OData.



May 20, 2012 C#, OData, WCF, Web 3 comments

Let’s talk about OData.

At first glance

I would propose to start our OData journey with the best thing about it. Which is openness of data, easy of accessing and working with it. Client applications no longer need to depend on some specific service methods or formats if there is OData feed available. Consuming OData is simple and enjoyable.

For example, I want to know how many users with name ‘Andriy’ are there on StackOverflow with reputation higher than 500. No one at StackOverflow would develop special method in API which would allow me to request exactly this data.

But as StackOverflow exposes OData feed we can connect to it with LinqPad (get it here) and simply write normal C# linq query, like this one:

Users.Where(x=>x.DisplayName.Contains("Andriy") && x.Reputation > 500).OrderBy(x=>x.Reputation)


You can see same data if you use URL below. This URL was built by LinqPad to request data:

http://data.stackexchange.com/stackoverflow/atom/Users()?$filter=substringof(‘Andriy’,DisplayName) and (Reputation gt 500)&$orderby=Reputation

(View page source if you don’t like how your browser rendered that feed).

So, no magic. You just build special URL and get your data of interest in preferred format. You can use wide set of libraries both for client and server to implement and use OData.

Whenever you see this icon Datafeeds16 it is good indication that there is OData feed available. There are many applications/web sites that already utilize this protocol. Do you use Nuget? It works through OData. Know ebay? They expose its catalog via OData. Need more examples? Go to ecosystem page of OData.

So what is OData?

The Open Data Protocol (OData) is a Web protocol for querying and updating data that provides a way to unlock your data and free it from silos that exist in applications today. OData does this by applying and building upon Web technologies such as HTTP, Atom Publishing Protocol (AtomPub) and JSON to provide access to information from a variety of applications, services, and stores. The protocol emerged from experiences implementing AtomPub clients and servers in a variety of products over the past several years.  OData is being used to expose and access information from a variety of sources including, but not limited to, relational databases, file systems, content management systems and traditional Web sites.

…from http://www.odata.org/

Continue your reading about OData on its documentation page here.

If you have time, I would recommend to watch this “OData: There’s Feed for That” MIX10 video.

Let’s build our first OData service

As OData was initially introduced by Microsoft no wonder it is extremely easy to put it in place when you are on MS stack of technologies. If you are using EF there is almost nothing you have to do to make it happen.

Add “WCF Data Service” to your project.


You will get following code:

public class WcfDataService1 : DataService< /* TODO: put your data source class name here */ >
    // This method is called only once to initialize service-wide policies.
    public static void InitializeService(DataServiceConfiguration config)
        // TODO: set rules to indicate which entity sets and service operations are visible, updatable, etc.
        // Examples:
        // config.SetEntitySetAccessRule("MyEntityset", EntitySetRights.AllRead);
        // config.SetServiceOperationAccessRule("MyServiceOperation", ServiceOperationRights.All);
        config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;

Assuming that you have your data model generated on Northwind db. All you would need is something like this:

public class WcfDataService1 : DataService<NorthwindContext>
    public static void InitializeService(DataServiceConfiguration config)
        config.SetEntitySetAccessRule("*", EntitySetRights.All);
        config.SetServiceOperationAccessRule("*", ServiceOperationRights.All);
        config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;

    protected override NorthwindContext CreateDataSource()
        return new NorthwindContext(ConfigurationManager.ConnectionStrings["NorthwindContext.EF.MsSql"].ConnectionString);

And now clients can do whatever they like with your data. Of course, you can restrict them as you wish. OData is not about putting your database into web, you can control what you expose and to which extent. Also you can play with your service by adding caching, intercepting queries, changing behaviors and much-much more.

In next post I will show how we can build OData service for custom data you keep in memory.

In meantime you can checkout another video from NDC2011 by Vagif Abilov. Video is called “Practical OData with and without Entity Framework”. Follow this link to direct mp4 video file.