Multi-personality classes

I’ve been working on my spreadsheet library and I discovered something. There are classes where I needed them to be multiple end results at the beginning of their lives. Let me illustrate.

Suppose we have an Embryo class. However, the way we use it is that we need it to survive all the way to the end of our program, and we happen to need the Testicles and Ovaries properties depending on how our program uses it. For example:

Embryo emb = new Embryo();
if (boy)

The thing is, we don’t know beforehand whether we have a boy or girl until runtime. And even if we know beforehand that we have a boy or girl, we need a generic placeholder class that can represent either a boy or girl until some later time. Using the .NET object class and doing boxing/unboxing seems unproductive (no pun intended).

So what’s the actual situation? Chart axis.

The primary horizontal axis of an Excel chart can be either a category axis, date axis or value axis. Category axes are used for most cases, where the chart data’s categories are text. But if they’re dates, then date axes are used. And if they’re scatter charts, then the axis is a value axis.

Well, the primary horizontal axis is usually those 3 types. Bar charts have them at the primary vertical axis. *sigh* Don’t even get me started…

So the primary horizontal axis has to be all 3 at the same time. Well, the way I expose the property/class requires it to be all 3 at the same time.

“Why don’t you expose them individually? That would solve the ambiguity problem.”

I could do this:

SLChart chart = new SLChart("B2", "G6");
chart.PrimaryCategoryAxis.Title.Text = "A category title";
chart.PrimaryDateAxis.Title.Text = "A date title";
chart.PrimaryValueAxis.Title.Text = "A value title";

And then based on the type of axis used, I’ll use the different axis class. The thing is, I want to expose only one property instead of 3. This means programmers using my library don’t have to differentiate which axis class/property to use.

If I expose only 1 property/class, then that underlying class has to be all 3 types of axis at the same time.

There’s probably a design pattern I don’t know about. If you know it, or have comments on how to approach this, I’d love to hear it in the comments.

Now if you’re in the business of producing (hahaha… pun… never mind) Excel spreadsheets, try my library!

Announcing SpreadsheetLight version 1.1

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…