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.
If you need to read in .XLS files, then just use Excel to convert them to .XLSX files (there’s the compatibility pack). Get your users on board with this. Ask them to save to .XLSX instead of .XLS.
“What about template .XLS files?” They’re template files, right? Meaning you can upgrade them to .XLSX template files, right? Compatibility pack from Microsoft. Did I mention that the compatibility pack is provided free by Microsoft?
“Where’s the data grid component? I want to bind the data for my web/Windows app.”
SpreadsheetLight has no visual component. One of the big design considerations is to make automation of spreadsheet creation/update easy. This generally means no humans are involved, so no visual interface is needed.
The end resulting product is an Open XML spreadsheet which can be opened up by Microsoft Excel or LibreOffice Calc or any spreadsheet software that supports Open XML spreadsheets.
“Do I need to install Excel on my web server?”
SpreadsheetLight requires only the Open XML SDK to be installed, which is free from Office/Excel Interop.
You do not need to install Microsoft Office/Excel on your deployed machine, be it a web server or a normal server.
“How do I install SpreadsheetLight into the GAC?”
Most of the time, that’s the wrong question to ask. Your problem is probably to make sure all your projects use the same version of SpreadsheetLight. Then just use NuGet, and this Visual Studio package manager will handle that for you. You can get SpreadsheetLight on NuGet here or just go to your Visual Studio package manager to do it.
But if you really want to…
gacutil -i SpreadsheetLight.dll
But Microsoft states that’s for development purposes. For production, it’s better to use the Windows installer to do it. But I don’t know how to make this work…
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 “few restrictions in use”.
The few 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?
What’s your performance? Any benchmark tests?
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.
I get an error about SmartTags. What’s up with that?
Version 3.4.5 now works with Open XML SDK 2.5, and the source code has been changed to ignore the SmartTags portion. So get the latest version from NuGet and you’ll be fine. As for the source code being available for download, I want to wait till at least a minor version before releasing it (to make sure bugs and such are fixed).
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.
It’s not open source! Why does SpreadsheetLight not come with the Visual Studio solution and project files?
Let me answer the first part first. “Open source” just means the source code is open for viewing and freed to be modified, as I understand it. It doesn’t say anything about including Visual Studio solution and project files.
As for the second part, there are a few reasons. SpreadsheetLight started out as a commercial product. I reserve (and still do) the right to hold the strong name key used to sign the DLL. Since the strong name key is listed in the solution/project file, this meant I have to either provide the strong name key file or make a new solution/project version without the strong name key file.
I went with a 3rd option. I don’t provide any solution/project files at all.
This also has the side effect of not depending on Visual Studio. Who knows? Maybe Mono will have support. So I don’t want to tie you down on a specific IDE.
But it’s hard to compile! It’s tedious to set up everything! Why are you making my life difficult?
You might not like this answer then. One of those other reasons I had was to reduce the amount of support I might have to provide. Not that I hate you, I just prefer to receive support requests that are really important. Have you been to Q&A forums and been flooded by newbie questions?
Using SpreadsheetLight does not require you to compile anything at all. Just use the DLL. I repeat. You do not have to compile the source code at all.
It’s why I spent so much time making sure the documentation XML for the Intellisense is really good. So good that it should make using the library as self-explanatory as possible. I encourage self-exploration.
The absence of the solution/project files implicitly make the library hard to use for any newbie developer who wants to make changes to the source code. But the library is still easy to use as in you just plug it into your project as a reference.
A developer of at least intermediate technical expertise will have no problems setting up a new Visual Studio solution/project and getting all the source code to compile. If you can do this, then you’re probably capable enough to make changes to the source code and customising it for your own use.
If you can’t even compile the source code, I’d rather you not try to make changes. You’re not ready yet.
But I want to read the source code!
Notepad opens the source code files just fine. In fact, Visual Studio can open the C# source code too, with all the colour highlighting and stuff.
You do not need to be able to compile the source code to read it.
I’ve downloaded the source code for EPPlus and ClosedXML. Excellent library alternatives, and they have Visual Studio solution/project files! Go use theirs if that’s more appealing to you. No harm done.
Where was I? Oh yeah. I’ve read their source code and followed the logic just fine. Using Notepad.
I’ve also gone to the discussion and issue tracker pages of EPPlus and ClosedXML. The developers are swamped with bug fix and support requests, many of which aren’t resolved.
I want developers who really care about using SpreadsheetLight to ask me stuff. Developers who find using the library really useful. These are the people I care intensely for.