Sample code

Find out how to make your life easier with SpreadsheetLight (often with a handful of lines of code). Obviously I can’t show you everything that SpreadsheetLight is capable of. If you can’t find any sample code here to do what you want, and you’re on a tight schedule, just contact me and I’ll show you how to accomplish your specific requirement.

You can read and modify existing Excel spreadsheets, even though SpreadsheetLight is optimised for creating brand new spreadsheets. In any case, your server will probably register nary a blip in performance and memory use. Micro-optimisation, down that path thou shalt not go.

Download it now, or check out the speed/memory benchmark code if you’re not ready yet.

Possibly the shallowest learning curve ever.
Because you’re worth it.

I found SpreadsheetLight when I did a Google search for “Free Export to Excel Libraries” — and Vincent Tan referenced it in a post he did on StackOverflow. I am the administrator of a small intranet site which uses a lot of legacy ASP Classic, and I’m trying to migrate the site into a modern .NET foundation. One of the things the site must do on many of its pages is display database data in a tabular format, and support downloading that data to Excel spreadsheets.

As it turns out, it’s pretty difficult to do that, so I’ve been searching for a long time for an efficient way to do that. The only solution I discovered, which fit most of our needs, involved actually downloading the ASPX page’s GridView control as an HTML object onto an Excel spreadsheet, and all the fields were then changed to text — including dates and currency values, which meant the user had to convert those data types before doing any kind of calculations with them.

Dissatisfied with that solution, I did the aforementioned Google search and hit upon Vincent Tan’s SpreadsheetLight library. I took a look at the licensing (free), took a look at the capabilities (creates an XML document from scratch using our data), and offered both sample code AND source code. I decided to download it.

At the same time I tried a few other really terrific sounding libraries, but no matter how much I replicated the sample code, I couldn’t get them to work. In desperation, I went back and tried SpreadsheetLight instead. And in less than ten minutes I had my web pages downloading full Excel XLSX files, created from the datatables used to create those web pages, and downloading them in native Excel format — not a series of strings in all columns. I could do calculations, I could use the dates AS dates, it held onto all the leading zeroes in critical fields — just like magic, it worked immediately, out of the box, no muss, no fuss, and no headache.

I’ve since deployed SpreadsheetLight to several other solutions in our intranet, and no one’s even noticed the switch. It was simple to deploy, works immediately, and Vincent even offered me a couple of words of advice (though he didn’t have to).

I can’t speak highly enough of SpreadsheetLight. If you’re looking for a quick and easy solution to get your data out as real Excel spreadsheets, without a lot of hoop-jumping, look no further than SpreadsheetLight.

Josh Hill

Tip: You might find the SLConvert class to be helpful in doing miscellaneous conversions, such as getting cell references from a row/column index, as well as converting centimetres to inches (Excel by default follows the imperial system instead of using the metric system. Unlike most civilised countries *cough* 🙂

Tip: You can find out basic worksheet statistics with SLWorksheetStatistics. For example, the number of rows/columns/cells used. Use the GetWorksheetStatistics() of the SLDocument class.

And just for kicks, here’s how to do it in ASP.NET MVC.

public FileStreamResult GenerateReport()
{
    MemoryStream ms = new MemoryStream();
    using (SLDocument sl = new SLDocument())
    {
        sl.SetCellValue("B3", "I love ASP.NET MVC");
        sl.SaveAs(ms);
    }
    // this is important. Otherwise you get an empty file
    // (because you'd be at EOF after the stream is written to, I think...).
    ms.Position = 0;

    return File(ms, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Report.xlsx");
}

You’ve got style