I get Out Of Memory errors!
If you’re on a 64-bit machine, try compiling your application in 64-bit mode. That usually solves the problem. Also, actually have enough RAM on your machine.
The issue is that your 32-bit application is trying to play nice and not get a whole chunk of memory. Even if you have a gazillion gigabytes of RAM. It’s probably to do with a 32-bit application and memory and address space and such. But don’t quote me on it. I have enough problems making the library go faster with less memory…
My preliminary tests suggest that a worksheet with 40 columns of 1,000,000 (that’s 1 million) rows of random floating point data eats up 6 to 7 GB of RAM. If you need to generate a worksheet with 40 million cells, I think you have bigger problems…
Do you support Excel 2003?
No, sorry. SpreadsheetLight supports Open XML spreadsheets, meaning Excel 2007 and Excel 2010 (and later). Or more accurately, the .XLSX extension rather than .XLS extension. However, you can download the Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint file formats. That allows you to open and edit spreadsheets created by Excel 2007 (and later).
Why don’t you support Excel 2003? (Or why don’t you support .XLS?)
Microsoft Office (and by extension, Excel) is moving towards Open XML support. The design decision is to completely support future versions of Microsoft Excel, which means Open XML. Of course, Excel will inherently support legacy files such as the .XLS files.
What’s the price of SpreadsheetLight?
It’s free, in both gratis and libre versions. Meaning it’s free as in “priced at zero dollars” and free as in “little restrictions in use”.
The little restrictions there are, are covered by the MIT License.
What’s your support like?
See here for details. And I type bloody fast.
Do I get source code? What’s the licensing agreement?
Yes you get source code. Full library source code is included in your download.
Source code is licensed under the MIT License. In short, you get to do whatever you want with the source code, provided you understand that
- A copy of the license has to be included in any derivative/changed work if substantial parts are used
- The author(s) of the source code is/are not liable for damages
That’s a ridiculously short summary of an already short legal license. You’re encouraged to read the full thing for details.
What’s this Open XML SDK?
Open XML SDK is a software development kit from Microsoft to allow developers to create Open XML files, which are word processing documents (Word), presentation documents (PowerPoint) and spreadsheet documents (Excel).
What’s this DocumentFormat.OpenXml I see in the source code?
It’s the namespace used by Open XML SDK. See above.
SpreadsheetLight not only work based on the Open XML SDK, it is designed to work with any code you write with Open XML SDK (if you’re hacker enough to delve into the SDK depths).
The way this works is that SpreadsheetLight uses enumerations from the SDK as far as possible. Only when it’s easier for a function to take in an enumeration (instead of magic numbers) that the SDK doesn’t provide, then a custom enumeration is created.
This also includes the case when the Microsoft Excel user interface offers drop-down lists and those values aren’t available in the SDK. This happens when Excel is making your user’s life easier by handling all the details internally. But the SDK doesn’t have those details! Your user tells you how to get the result (like pulling a drop-down and tells you she wants the Peacock gradient preset), but you have no idea what she’s talking about. This is when SpreadsheetLight offers enumerations so you can mimic the Excel user interface.
You have no idea how much time I spent twiddling a gradient preset, saving the file, then open it up to find the actual values… (there are 24 gradient presets! With 4 type combinations! And gradient directions! And some gradients have 7 gradient stops! Ok, I’m gonna stop now…) Well done, Excel.
Where are the Workbook and Worksheet and Cell classes and variables!?!?
Don’t panic. SpreadsheetLight is designed to make your life easier. Those classes are still there, but you don’t have to worry about them.
The Excel user doesn’t worry about which worksheet she’s working on until she decides to go work on another worksheet. You shouldn’t have to worry about worksheet management too.
36 overloaded functions just to set a cell value!?! Why not just accept a value of type object?
They correspond to value types of boolean, byte, 16-bit integer (short), unsigned 16-bit integer (ushort), 32-bit integer (int), unsigned 32-bit integer (uint), 64-bit integer (long), unsigned 64-bit integer (ulong), float, double, decimal, string, inline strings (rich text) and the SLRstType class that makes inline strings easier to create. That’s 14.
Then there are 4 versions for DateTime. Why 4? To handle the 1900 and 1904 epochs. See the documentation for more details. So we’re up to 18.
Then we have two versions, one to accept cell references (such as A1) and another to accept the cell row index and cell column index. Thus 18 * 2 = 36.
The reason SpreadsheetLight doesn’t accept values of type object is to enforce strong typing. And also that you know exactly what to provide as a function parameter. All functions in SpreadsheetLight follow this design.
Why do you have a version for cell references and another for row/column indices?
These 2 versions permeate throughout SpreadsheetLight whenever it makes sense. The reason is that when you know exactly where a cell resides, or the exact range of cells, it’s easier to refer to them as cell references. Just like in Excel.
However, if you deal with dynamic data that not only changes its range, but also where it might be on the worksheet, then row/column indices is there for you.
Perhaps you have 2 sets of dynamic tabular data, one on top of each other. The position of the 2nd set of data depends on how many rows of data there is in the 1st set. Having indices that you can iterate over is helpful in situations like this.
I read your source code. You swallow Exceptions in try-catch clauses. That’s unacceptable.
SpreadsheetLight is designed with document automation in mind. Say an application executing on a Sunday morning 1 AM, and generates 183 Excel files for various departments for various purposes.
Imagine you’re exploring the depths of a woman’s bodice in her boudoir, or enjoying the warm embrace of a man’s arms beside his armoire. Do you want to be interrupted by a phone call telling you that an application of yours failed?
I assume you do not. And if you do, congratulations on your upcoming promotion!
So SpreadsheetLight tries not to throw up, I mean, throw exceptions.
The errors are most probably minor and the worst thing that can probably happen is that some cell wasn’t set. Throwing an exception tells you very little. A user on Monday morning telling you that the sum figure in cell F218 is missing tells you exactly what’s wrong.
Of course, if the error is critical, then uh, have you read the disclaimer in the MIT License?
I get a “That assembly does not allow partially trusted callers.” error. What’s up with that?
SpreadsheetLight is signed with a strong name. Your application probably needs to have the AllowPartiallyTrustedCallers attribute set. See here for details.
What’s your performance? Any benchmark tests?
Read performance tips here. Check out SpreadsheetLight’s benchmark tests.
I get an error about SmartTags. What’s up with that?
You’re probably using Open XML SDK 2.5 (or higher?). SmartTags is deprecated in Excel 2010, and so technically speaking, only SDK 2.0 supports it.
SpreadsheetLight runs internally on SDK 2.0, so you might want to get that version instead. For convenience, you can download it here (under the installation instructions), but it only contains the SDK 2.0 DLL and XML documentation. If I understand it correctly, that’s as far as I can do in terms of distribution under the Microsoft license.
As far as I can tell, SDK 2.5 offers Office/Excel 2013 specific features, and well, we’re not doing any of that. Yet.
