Everything should be made as simple as possible, but no simpler.
– Albert Einstein
The idea is to give you as much access as possible without overwhelming you with internal details. Other spreadsheet libraries expose much of the internal classes and properties and typically expect you to figure out what properties to set. In contrast, SpreadsheetLight probably hides up to 40% of its internal classes (because you’re not supposed to worry about them).
If a bunch of properties need to be set as a group, and you miss out on one of them (or don’t know what to give as a value), you’re pretty much screwed.
There are 2 design models used: the Apple iPhone and the Microsoft Excel software itself.
The iPhone offers the user easy access to commonly used functions, typically within a couple of taps and swipes. Because of this, SpreadsheetLight “flattens” as much functionality into a single class as possible. This means you don’t have to look through dozens of classes that may or may not help you. (This also explains why some classes have mountain-sized lists of overloaded functions)
This “flattening” effect is also seen in the Excel software. The commonly used functions are all accessible through the interface (in particular, the Ribbon) without going through too many drop-down menus.
Following this design, as far as possible, you can perform similarly-mapped tasks in code.
Because of this, you shouldn’t think of the spreadsheet in terms of object oriented programming. Think of writing code like using the Excel software.
After all, why should your code be harder to write than someone using Excel?
On code design
SpreadsheetLight works on the principle of having a selected worksheet at all times. This is the same as using the Excel software, where the user always has a worksheet active. The idea is to free you from having to keep track of worksheets because SpreadsheetLight tracks them internally for you.
This also means the primary class SLDocument has a very long list of functions. Luckily, we have the partial class construct, so similar functions are grouped together into separate files, which keeps each class file manageable. The SLDocument class handles most of the worksheet and cell operations and style operations, often overloading functions onto itself so you don’t have to dig into the relevant classes.
For example, some styling operations from the SLStyle class spilled over to SLDocument so that you don’t have to explicitly initialise an SLStyle variable to do stuff.
Everything is also set with a function from SLDocument class. This means even if you decide to explore the other classes in the library, if you don’t see it as a function parameter from a function from SLDocument class, then you don’t have to worry about it. Yet.
For example, conditional formatting.
SLConditionalFormatting cf = new SLConditionalFormatting("C3", "G6");
cf.SetDataBar(SLConditionalFormatDataBarValues.LightBlue);
sl.AddConditionalFormatting(cf);
“sl” is the variable for SLDocument class. The AddConditionalFormatting() function takes in a SLConditionalFormatting class as a variable. That’s when you go exploring what the SLConditionalFormatting class can do.
Everything is tied back to the primary class SLDocument, so you don’t have to feel lost on what to do, or if some property was set properly. If it’s not done with the SLDocument class, it’s not done at all.
In the case of the conditional formatting example above, you may have set all the properties on the conditional formatting class. But if you don’t use the AddConditionalFormatting() function to add it to the currently selected worksheet, nothing happens.
On memory management
The most distinctive feature of SpreadsheetLight is the concept of a “currently selected worksheet”. This works like how Excel users create worksheets: one at a time.
As a side effect, this also helps with memory management.
Other spreadsheet libraries typically allow you access to all the worksheets, usually with an index to access an internal array of the worksheets. To implement this, all of the data in all of the worksheets have to be kept in memory at all times. This means if you have 10 worksheets, all 10 worksheets are kept in memory. SpreadsheetLight on the other hand, just keeps 1 worksheet in memory, the currently selected one.
Let’s say on a whim, you’ve decided you want to drink a cup of tea, a cup of coffee and a cup of Nutella mixed with hot milk. If you do it like “normal” spreadsheet libraries, you will take 3 cups and prepare all 3 types of drinks, all together.
The good news is that you can sip from any of your drinks at any time (accessing data from any worksheet at any time). The bad news is that nobody else can use those cups while you’re drinking (computer memory “locked” by your application). Also, you will take one long session to wash all 3 cups so no one can use the sink while you’re washing (longer period of CPU usage at a higher level).
Following the “currently selected” concept, you could just make a cup of tea and then enjoy that first. Other people can still use the other 2 cups that would’ve otherwise be filled with coffee and Nutella/milk. Then you wash that cup, and make yourself another drink, possibly choosing the Nutella/milk option. Then you wash and make yourself a cup of coffee.
At any one time, only one cup is used. And while the total time for washing is still about the same, washing 3 separate times allow other people to use the sink. Or at least give the impression that the sink is not completely owned by you. (This reduces the total memory footprint while SpreadsheetLight is running, thus allowing the computer to allocate those resources to something else. Great for web servers).
So if you do benchmark tests with SpreadsheetLight, you will notice that on adding or selecting another worksheet, there’s a slight pause. This is because SpreadsheetLight is saving all the data on the “currently selected” worksheet, and then move to the next worksheet. Other libraries will register nary a blip on the performance.
But if you have, say, 5 moderately large worksheets, then you will probably see 5 moderately sized peaks in memory for SpreadsheetLight. You will see a rise in memory as you fill in cell data, and then drop to almost the previous memory level, and then rise again as you fill in cell data in a new worksheet, and so on (see how SpreadsheetLight performs in both speed and memory). Other libraries will just have one monotonically increasing memory footprint.
And on the final save, SpreadsheetLight does so quickly, because it’s just saving the currently selected worksheet. Other libraries will be saving all 5 worksheets, meaning the computer/server will suddenly spike in CPU and memory usage. This “spike lock” may or may not affect you, but I find a milder behaviour to be beneficial to keeping my blood pressure in check.
I also find it easier on the taste buds to drink one type of drink, one at a time.
Frankly speaking…
You’ve read this far? Good for you.
If SpreadsheetLight did its job properly, you shouldn’t have to email me questions about how to use it. Because you should’ve known how to do whatever you want simply through self-discovery. The extensive XML documentation file that comes with SpreadsheetLight will allow Visual Studio (most probably the IDE you’re using) to give you information on how to use that method or what range of parameters is allowed. There’s also sample code. And an FAQ.
No news is good news, right?
On the other hand, I don’t really know how well SpreadsheetLight is working out for you. So if it’s really useful, I’d appreciate it if you could write me a short email telling me about it. That makes expending all that effort writing it worthwhile.
