SpreadsheetLight Tutorial | SpreadsheetLight for .NET Framework

Hey there fellow developer! Overwhelmed by everything offered in SpreadsheetLight? Not sure where to begin, even with all the example source code available? You’re in the right place then.

There are only 2 rules to remember when using SpreadsheetLight.

Rule 1: Everything begins and ends with SLDocument

The SLDocument object is the alpha and omega of all your spreadsheet operations. If it’s not done with the SLDocument object, it’s not done at all.

Rule 2: Most operations are action-oriented

The design of SpreadsheetLight is action-oriented, rather than object-oriented. This is to mimic the user interface of Microsoft Excel (or other spreadsheet software with a visual user interface, such as LibreOffice Calc).

A spreadsheet user performs actions on a spreadsheet, such as set a cell value or insert a picture. Traditional object-oriented software libraries usually get you to assign values to properties and sometimes call functions (and sometimes without telling you what’s the acceptable range of values for a parameter. Let’s face it, Open XML SDK can be arcane…).

Nota bene

This tutorial is not meant to teach you every single thing that you can do with SpreadsheetLight. It’s meant to get you comfortable using the library, enough that you can do some self-discovery to find out what else can be done.

The general idea of how to accomplish a task is the same. This tutorial will help you understand this.

But the task implementation is different. And there are tons of example source code to help you with this.

Alright, let’s start.

The one-liner

Here’s how to start using SpreadsheetLight.

SLDocument sl = new SLDocument();

That basically says “Make a new spreadsheet!”. Yeah, that’s it. That’s the same as starting Excel up, and … then you stare at the screen.

“But Excel has like blank worksheets on a new document already! Where’s the worksheet here?”

SpreadsheetLight automatically creates a default worksheet for you. Excel 2007 and 2010 has 3 default worksheets, named Sheet1, Sheet2 and Sheet3. Excel 2013 has only 1 default worksheet, named Sheet1 (as does LibreOffice Calc actually).

So yeah, you already have a blank worksheet named Sheet1 with just that one line of code. In case the default name changes, if you want to access the default worksheet name, please use SLDocument.DefaultFirstSheetName. You know, just in case the default name changes in future.

The two-liner

Since that’s not very exciting, we’re going to save the spreadsheet (woohoo!).

SLDocument sl = new SLDocument();
sl.Save();

“Wait, what’s the name of the spreadsheet?”

Well, what name does Excel save a default spreadsheet to? Book1.xlsx (at least according to Excel 2007, 2010 and 2013).

To choose the name of the saved spreadsheet, you use:

SLDocument sl = new SLDocument();
sl.SaveAs("MahNewClothes.xlsx");

Now, in Excel you can also open a spreadsheet and then close it without saving. Tada!

SLDocument sl = new SLDocument();
sl.CloseWithoutSaving();

Or more realistically:

SLDocument sl = new SLDocument("MahNewWardrobe.xlsx");
sl.CloseWithoutSaving();

That opens an existing spreadsheet named “MahNewWardrobe.xlsx”, closes the spreadsheet and frees the resources used by the internal data structures (because of the closing).

“What data structures? Where’s the workbook and worksheet and cell object properties? Where’s my inhaler, I’m hyperventilating!”

Calm down. They’re internal because I don’t want you to worry about them. Do you see Excel users worrying about them? No. They just click on a worksheet and start typing or copy-paste stuff.

Are you getting the hang of it yet? SpreadsheetLight simulates the user interface of Excel as much as possible, bringing an equivalent programming interface to you.

Remember rule 2: action-oriented. Think “What do I want to do here?” rather than “Which object do I look for to assign a property value?”

With that, SLDocument implements the Disposable interface, so it’s suggested to do this:

using (SLDocument sl = new SLDocument())
{
    sl.SaveAs("MahNewShoes.xlsx");
}

That keeps things nice and tidy and cleans up resources after you’re done.

Now let’s look at the most basic task you do in a spreadsheet.

The most basic spreadsheet operation

Here’s how to set a cell value:

using (SLDocument sl = new SLDocument())
{
    sl.SetCellValue("B3", "It costs what for a Jimmy Choo?!?");
    sl.SaveAs("MahNewShoes.xlsx");
}

This illustrates rule 2 again. You want to do an action, so you look for a function exposed by SLDocument with that action you have in mind. Which brings us to rule 1, everything is ultimately done with SLDocument.

Let’s look at a more complex task.

A more complex task

Let’s say you want to insert a picture. So you do

using (SLDocument sl = new SLDocument())
{
    sl.InsertPicture(
    sl.SaveAs("MahNewShoes.xlsx");
}

And you find that InsertPicture() takes in a parameter of type SpreadsheetLight.Drawing.SLPicture. Ok, what in Batman’s name is SLPicture? So you take a look.

(I assume you have “using SpreadsheetLight.Drawing;” at the top of your source code file so that it’s shorter to type…)

using (SLDocument sl = new SLDocument())
{
    SLPicture pic = new SLPicture("jimmychoo1.jpg");
    sl.SaveAs("MahNewShoes.xlsx");
}

“Oooh, I can just put in the file name. Wait, it doesn’t appear in my spreadsheet?!”

Remember rule 1. Everything is done with SLDocument.

using (SLDocument sl = new SLDocument())
{
    SLPicture pic = new SLPicture("jimmychoo1.jpg");
    sl.InsertPicture(pic);
    sl.SaveAs("MahNewShoes.xlsx");
}

“Wait, I want to change the position!”

using (SLDocument sl = new SLDocument())
{
    SLPicture pic = new SLPicture("jimmychoo1.jpg");
    pic.SetPosition(5, 5);
    sl.InsertPicture(pic);
    sl.SaveAs("MahNewShoes.xlsx");
}

The general flow

For most operations, the SLDocument exposes a Mount Everest sized list of functions that lets you do stuff. Such as set cell values or merge cells.

For more complex tasks, it’s a short series of steps.

You find the action function of SLDocument that does what you want
You learn about the parameter of that function (SLPicture in the case above)
You manipulate any properties you want of the object (SLPicture)
You dump that object to SLDocument using the action function (the InsertPicture() function in our case)

No matter how complex the task is, you always go back to SLDocument. In the case of SLPicture, it’s a 2-chunk process. You start with SLDocument, manipulate SLPicture, then dump it to SLDocument.

For more complex tasks, you might have a multi-chunk process. For example, charts are particularly complex.

Let’s say you want to do something to the data series labels on a chart you want to insert. Which means you want to insert a chart in the first place.

Starting with SLDocument, you find the InsertChart() function, which takes in an SLChart as a parameter.

After doing some prep work on SLChart, you find that it has the SetDataLabelOptions(), which takes in an SLDataLabelOptions as one of the parameters.

So you go explore what SLDataLabelOptions is, set the properties as required. Then set it to your SLChart object with SetDataLabelOptions(). Then set your SLChart object to SLDocument with InsertChart().

Full circle.

This means you don’t have to learn about all the various classes exposed in SpreadsheetLight until you actually need them. You just focus on SLDocument first. Everything else can be learnt when you need them.

With that, there are lots of examples to show you how to do specific tasks. And I wish you all the best in your software projects.

Tutorial