Creating Spreadsheets in ASP.NET using SpreadsheetLight

There are lots of ways of creating a spreadsheet in the .NET Framework but the most simple ones rely on some sort of Excel interop or having Excel installed on the machine where the code is running. When using ASP.NET, the code runs on the web server so it is highly unlikely that Excel will be/should be installed.

One option to avoid having Excel on your server is to create the spreadsheet directly using Office Open XML (OOXML).   OOXML is the file format that Microsoft Office have been using for files since Office 2007 and have file formats that end in ‘x’ (.docx, .xlsx, etc). While this gives you an amazing level of control over your output, OOXML is not particularly intuitive, with even simple documents requiring a large amount of code.

My current preferred alternative to working directly with OOXML is to use SpreadsheetLight. SpreadsheetLight is an open source library that takes a lot of the heavy lifting out of generating spreadsheets. Before getting started, it’s worth noting that, under the hood, SpreadsheetLight creates a spreadsheet using OOXML and so will be in .xlsx format rather than .xls.

Getting Set Up

There are two things you’ll need before you start:

  1. The SpreadsheetLight Nuget package.
  2. The Open XML 2.0 SDK. It’s important that this is the 2.0 version rather than the 2.5 version currently on Nuget. Add the DocumentFormat.OpenXML.dll from the SDK as a project reference.

When you’ve got these, add a few import statements and you’ll be ready to go.

Creating Your First Spreadsheet

Getting a working spreadsheet is just a few lines:

In SpreadsheetLight, everything goes through the SLDocument object. If there’s a way you want to manipulate your spreadsheet, chances are the SLDocument object is the place to start. So to start, create a SLDocument object.

Next, we just set “Some Text” in the cell at row 1, column 1. All the methods that allow cell access are overloaded to take either the row and column numbers or the Excel-style “A1” references so you can use the style you prefer.

To save the file here, we use the SaveAs method which will create a file at the location given (as long as the running process has the permission).

Downloading Spreadsheet Without Saving

In an ASP.NET application, often you will want the user to automatically download the spreadsheet without first saving the file to disk somewhere. The methods for doing this vary between WebForms and MVC so here’s an example for each that uses the spreadsheet we created above.

In WebForms:

In MVC:

The MVC version will return a FileStreamResult that can be returned by your controller. If you’re using a MVC version, you will need an additional Import statement for System.IO to access the MemoryStream class.

It’s really important to make sure the ms.Position = 0 line is there in order to reset the current position of the stream to the beginning before trying to send it to the client.

Adding More Content

SpreadsheetLight can be used to create a wide variety of content in a spreadsheet with all the colours, charts and styles you might want. I’m not going to go through all of those here though, there are plenty of examples over on the SpreadsheetLight website.

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.