SpreadsheetLight is an open source Open XML spreadsheet library for .NET Framework written in C#, and is released under the MIT License. You can create new Open XML spreadsheets, or work with existing Open XML spreadsheets that are compatible with Microsoft Excel 2007/2010/2013 and LibreOffice Calc.
No Office/Excel Interop needed. You do not need Microsoft Office/Excel to be installed.
Dependency: Open XML SDK 2.5
See the version list below for details.
Install-Package SpreadsheetLight -Version 3.4.9
dotnet add package SpreadsheetLight --version 3.4.9
<PackageReference Include="SpreadsheetLight" Version="3.4.9" />
paket add SpreadsheetLight --version 3.4.9
You can read the full list of changes at http://spreadsheetlight.com/version-history/ Note that source code is only available up to minor releases, meaning Major.Minor.0 versions.
- Get filter range of a worksheet if a filter exists. See SLDocument.GetFilterRange().
- On the Mono Framework, the error "System.OverflowException : Arithmetic operation resulted in an overflow." no longer occurs. This was due to the System.Drawing.Bitmap not setting a default resolution (within Mono Framework. Running on .NET is fine. This part happens during the initialisation phase for setting the internal SLSimpleTheme class). Mono uses libgdiplus, which apparently assigns the Bitmap object zero for both horizontal and vertical resolutions. Hence the overflow error.
- Bug fix: It was not possible to copy a cell style from a cell to another cell that's on the same row or column. It is now fixed (it was De Morgan's laws incorrectly implemented).
- Bug fix: Creating a data validation that references another worksheet as a data source now works correctly.
- Bug fix: Calling InsertRow() multiple times now work.
- Bug fix: If a formula is passed as a parameter to HighlightCellsEqual() in SLConditionalFormatting class, it presents incorrect behaviour (something like $B$1 becomes "$B$1", with incorrectly double quoting). It is now fixed (along HighlightCellsBeginningWith(), HighlightCellsBetween(), HighlightCellsContainingText(), HighlightCellsEndingWith(), HighlightCellsGreaterThan(), HighLightCellsLessThan())
- Bug fix: Copying a worksheet now returns true if successful.
- You can now check with SLDocument.HasCellError(). WARNING: SpreadsheetLight does not have a formula calculation engine, so only existing errors are reported.
- Bug fix: SLStyle.Alignment.TextRotation now rotates text correctly.
- Bug fix: Worksheet names are now (more? [Is that grammatically correct?]) correctly surrounded with single quotes in formulas and chart sheet formulas if they contain special characters (every special character on your keyboard except underscore and period).
- Bug fix: GetCellValueAsString() now no longer contain the phonetic text (if it exists). As part of the correction, SLRstType.ToPlainString() also no longer contain the phonetic text.
- Bug fix: Inline strings in existing Excel files will now be correctly loaded (and saved into the shared strings table if the particular worksheet with the inline strings is selected).
Thanks to these awesome people for sending feature and bug requests: Bodo F, JF, HK, Vincent D, Jerry S
- Bug fix: Selecting worksheets one after another that has row properties (such as custom row heights) will no longer cause an error. (The error is actually in WriteSelectedWorksheet(), where the iteration was over a combined list of row properties indices and cell row indices, and the error occurs if there's a row properties row without any cells.)
Thanks to David L for telling me about the bug.
- Shared string unique count is now written in the file. This makes opening the resulting Excel file faster if there are large number of text strings. Set the property SLDocument.WriteUniqueSharedStringCount to false if the file opens with an error.
- Bug fix: Copy rows/columns now does not cause a runtime error (there were 2 separate cell stores and the wrong one was used, hence the reference index not found).
- SmartTags is now removed from consideration (not so smart now, are you? ;). Which means the code is now ready for Open XML SDK 2.5! And yes, it now works with Open XML SDK 2.5 (have I mentioned that? lol)
- Internal cell storage structure is revamped. It used to be a 1-dimensional dictionary with a 2-dimensional key, but is now a 2-dimensional dictionary with a 1-dimensional key. Tech explanation: Dictionary<SLCellPoint, SLCell> to Dictionary<int, Dictionary<int, SLCell>>. This is done because a 1-dimensional key is at most 2^31 hash entries, which cannot contain the theoretical 1048576 (2^20) rows and 16384 (2^14) columns per worksheet supported by latest versions of Excel.
- Bug fix: plotting separate data series on charts as different chart types now works (your typical combination chart such as a column chart with lines)
- Breaking change: SLDocument.GetCells() now returns a Dictionary<int, Dictionary<int, SLCell>>
- Bug fix: Formulas will be correctly changed when columns are deleted (when the formula involves said deleted columns).
- Bug fix: Cell value/formula set on a shared cell formula base cell now works correctly. For example, setting on C3 when C3 holds a shared cell formula will work.
- Bug fix: Setting filter on worksheet now works correctly. It failed to sort before (basically also need to set underlying defined name _xlnm._FilterDatabase. Gawdiggitty.)
- You can now get row/column grouping levels. See GetRowGroupLevel() and GetColumnGroupLevel() functions.
- You can now get a list of the shared cell formulas in the currently selected worksheet. See GetSharedCellFormulas() function.
- Cell formulas are now more correctly maintained when copying/inserting/deleting cells/rows/columns.
- Catered for situation where cell reference ranges aren't in top-left to bottom-right format (such as E1:A7). See SLTool for translating reference sequences to SLCellPointRange and vice versa. Don't worry, you probably wouldn't have known about this anyway...
- You can now draw borders on a cell range! And border grids! Try out the DrawBorder() and DrawBorderGrid() functions.
- You can now merge cells and set style/border properties at the same time! No more manual border drawing on merged cells! Woohoo! See MergeWorksheetCells() function overloads.
- You can now make SpreadsheetLight throw up, I mean, throw exceptions! When there are exceptions, of course. See ThrowExceptionsIfAny property. Not sure how much help this is though...
- You can now forcibly get a boolean value if it looks like it's a boolean but actually stored as text. See the GetCellValueAsBoolean() functions.
- Breaking change: SLCellFormula.Reference data type changed from string to List<SLCellPointRange> (but you shouldn't have been using this anyway...)
- Bug fix: The properties "count" and "uniqueCount" removed from shared strings table part. It seems a high number of shared strings will cause the calculation to render a corrupt file. See when writing the shared strings table for details.
- Bug fix: Outline (grouping) levels of rows and columns now limited to 0 to 7 (was allowed to go to 8 previously. See GroupRows() and GroupColumns() in RowColumnFunctions.cs)
- Removed optional argument use (specifically IsStylish) so that developers using Visual Studio 2008 (.NET Framework 3.5) can still compile the source code.
- Optimised GetWorksheetStatistics(). It's now faster and less memory intensive.
- Bug fix: autofitting rows with a smaller initial height now correctly autofit to content
- Bug fix (?): SLStyle.MergeStyle() now takes on the new style object's format code regardless.
This package has no dependencies.
This package is not used by any popular GitHub repositories.