After much researching and programming and suffering (and tea), I’m happy to announce that version 1.1 of SpreadsheetLight is up! SpreadsheetLight is a spreadsheet manipulation library based on Open XML.
I added some functionality for defined names and clearing out data from rows/columns/cells. You can also split worksheet panes now. Version 1 didn’t have split pane functionality because I couldn’t find a way to make it easy for the programmer. Well, I wrote a function that allows you to do it with a minimum of fuss.
But the main bulk of version 1.1 updates is the insert/delete rows/columns part. Say you want to insert 4 rows at the 3rd row. This means all the rows from the 3rd row (including the 3rd row) must be shifted downwards 4 rows. Every merged cell, cell formula, table and defined name must be accounted for.
Merged cells need to be enlarged or trimmed off as needed. Or just simply deleted if it’s entirely within the delete range.
Tables need to be updated so the cell references within are correctly adjusted. If it used to contain C3:F6, after inserting 4 rows, it has to be C7:F10.
Cell formulas and defined names are sort of related, with the former typically of a more complex form than the latter. Consider taking on something like this:
= A1 +LOG10 +”BCD32″ -SUM($H6:Sheet1!K$9) *LOG10($F$5) + BCD32 – SUM(F2:G3)
That has to become this:
= A1 +LOG14 +”BCD32″ -SUM($H10:Sheet1!K$13) *LOG10($F$9) + BCD36 – SUM(F2:G7)
Note the LOG10 part. The first instance is a cell reference. The second is the logarithm function, base 10, of the cell originally $F$5. Note also that the first instance of BCD32 is a literal string. Literal strings should not be changed, even if they contain a valid cell reference.
That part took me quite a while. Let’s just say I’m glad I’m fairly proficient in regular expressions…
The only thing I left out was pictures (aka worksheet drawings) for insert/delete rows/columns. To create similar behaviour to when you’re doing it in Excel requires more arcane coding…
So if you’re looking for a spreadsheet library that’s light-weight yet capable of heavy-weight functionality, with simple-to-use functions, then consider SpreadsheetLight. Or tell your boss.
I’ve also included a Platinum version, which includes the source code of SpreadsheetLight (licensed with the MIT license). So if you want to know how I accomplished all that magic, consider the Platinum version then. Lots of comments to make the hairy parts less hairy…