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!

Design philosophy of a software library

Recently, I received an email from a customer. “Where are the Workbook and Worksheet class variables?” (I’m paraphrasing). The background is that I sell a spreadsheet software library (check it out here).

My answer is “I don’t want you to worry about them.” (I’m paraphrasing my reply).

From hours of looking at source code from other spreadsheet libraries, I’ve come to the conclusion that spreadsheets are kind of hard to create programmatically (read: super flying fishball noodly tedious). From hours of looking at hundreds of Excel spreadsheets, I’ve come to the conclusion that Excel makes spreadsheets ridiculously easy for users.

Why are programmers working harder than Excel users?

So here’s my story. Back in my last job, I dealt with lots of financial data. Millions of rows of data in databases, with information on how long a satellite call was, how much data in megabytes that email was and how many instant messages were sent. And each row had a price attached to it.

The marketing department wanted to know what products and services were up. The sales people wanted to know their commissions. The customer service officers wanted to know if the customer exceeded the usage limit. The directors wanted to know the quarterly results.

I maintained internal websites for the staff to get and update all that information. I also maintained public websites for customers to get information about their usage. Business logic and requirements were flying at me all over the place.

Now ASP.NET allows me to throw a bunch of data at a DataGrid (or GridView, or whatever it’s called now) and it’s nicely displayed on a web page.

Then people wanted to download all that tabular data into an Excel spreadsheet. Oh the horror…

I’m a programmer. I can write text files, schedule emails, update information into Sybase or SQL Server or Oracle databases, but I haven’t a clue about Excel spreadsheets.

The most complicated thing I’ve had to do in Excel was create a rectangular grid with numeric data and do a sum. There’s a SUM function in Excel. I know that much.

I survived that by doing a ton of research and testing. And what eventually resulted was an Open XML reference manual for spreadsheets, but that’s a different story.

I’m not much of an Excel user. I’m guessing you’re not too.

So when I did research on what an actual spreadsheet library can do, I was appalled at the amount of code I still had to write and figure out. “What do you mean I have to write 20 lines of code? I can do that in Excel with a few clicks on the mouse!”.

Those spreadsheet libraries carefully exposed classes and functions and interfaces that Excel uses internally. But you know what? The Excel user sees none of that.

A common part is that all the libraries require the programmer to keep track of worksheet classes. Such as workbook.worksheets[0] or workbook.worksheets[“Sheet1”] or something similar.

Do you see Excel forcing the user to keep track? No. The user sees a bunch of tabs that represent the existing worksheets, but she always only see the actively selected worksheet, because that’s the one she’s concerned about.

So while internally, SpreadsheetLight (my library) has a Workbook class and a Worksheet class, the programmer doesn’t have to worry about it. My design philosophy is that you should be able to do whatever you need in the spreadsheet as easily as you can in Excel.

Here’s how you create a table with Open XML SDK:

Table table1 = new Table(){ Id = (UInt32Value)2U, Name = "Table2", DisplayName = "Table2", Reference = "I2:O13", TotalsRowCount = (UInt32Value)1U };
table1.AddNamespaceDeclaration("x", "");
AutoFilter autoFilter1 = new AutoFilter(){ Reference = "I2:O12" };

SortState sortState1 = new SortState(){ Reference = "I3:O12" };
SortCondition sortCondition1 = new SortCondition(){ Descending = true, Reference = "K3:K12" };


TableColumns tableColumns1 = new TableColumns(){ Count = (UInt32Value)7U };
TableColumn tableColumn1 = new TableColumn(){ Id = (UInt32Value)1U, Name = "Col9", TotalsRowLabel = "Totals" };
TableColumn tableColumn2 = new TableColumn(){ Id = (UInt32Value)2U, Name = "Col10" };
TableColumn tableColumn3 = new TableColumn(){ Id = (UInt32Value)3U, Name = "Col11" };
TableColumn tableColumn4 = new TableColumn(){ Id = (UInt32Value)4U, Name = "Col12" };
TableColumn tableColumn5 = new TableColumn(){ Id = (UInt32Value)5U, Name = "Col13" };
TableColumn tableColumn6 = new TableColumn(){ Id = (UInt32Value)6U, Name = "Col14" };
TableColumn tableColumn7 = new TableColumn(){ Id = (UInt32Value)7U, Name = "Col15", TotalsRowFunction = TotalsRowFunctionValues.Sum };

TableStyleInfo tableStyleInfo1 = new TableStyleInfo(){ Name = "TableStyleDark4", ShowFirstColumn = true, ShowLastColumn = true, ShowRowStripes = true, ShowColumnStripes = true };

return table1;

Here’s how you do it with my library:

SLTable tbl = new SLTable("I2", "O12");

tbl.HasTotalRow = true;
// 1st table column, column I
tbl.SetTotalRowLabel(1, "Totals");
// 7th table column, column O
tbl.SetTotalRowFunction(7, SLTotalsRowFunctionValues.Sum);

tbl.HasBandedColumns = true;
tbl.HasBandedRows = true;
tbl.HasFirstColumnStyled = true;
tbl.HasLastColumnStyled = true;

// sort by the 3rd table column (column K) in descending order
tbl.Sort(3, false);

“Wait, where do I set the table name?” Don’t worry about it.

“Shouldn’t I need to set the table column IDs and values? Where do I set ‘Col9’ and ‘Col10’ and the others?” Don’t worry about it.

“How do I know what string value to use for the table style?” Don’t worry about it. Use one of the built-in enumerations.

“You know, the section on sorting doesn’t include the header row. Shouldn’t I…” Don’t. Worry. About. It.

You know what happens in Excel? You select a bunch of cells, decide to make it a table, select a table style and POOF! A table appears. I want the programmer to feel just as awesome.

Frankly speaking, this library of mine is what I wish I had when I was doing all that coding in my last job. The last thing I needed was figuring out how to database dump tabular data onto an Excel spreadsheet. The company I worked for was a telecommunications company, working with satellite providers, service providers, content producers. The core business doesn’t involve making Excel spreadsheets, but sharing information inevitably involves Excel spreadsheets being passed around.

If you’re designing a software library, consider going beyond “allow the programmer to do X”. Consider “allow the programmer to do X in like, one line”, or at least very easily. Because the programmer might not care about X as much as you do.

Unless X is “make spreadsheet library” of course.

Dot notation dropdown hell

Have you written something like this:

Would this be easier?

I’ve been thinking about this a lot, because I’m writing a software library. You know what’s hard? Deciding what classes, functions and properties to expose to the programmer.

My software library deals with spreadsheets, and for uhm, research, I downloaded 2 free open-source libraries for comparison. Then I looked at sample code for 2 commercial libraries too.

You know what I found?

For the most part, the libraries just expose the underlying class structures to the programmer.

While this gives the programmer ultimate coding power, I personally find this exhausting. It’s like the first time I encountered the .NET Framework. There’s an overwhelming number of classes with their own functions and enumerations… “I just want to write one single sentence to a file!” Even that took me a few minutes to get used to. Luckily there’s extensive documentation, or I’d just collapse under the weight.

Do you remember Nokia? It’s a telecommunications company, but I know it as a mobile phone maker. My experience with Nokia phones were that they were probably designed by engineers and programmers. There were a lot of dropdown menus.

We programmers can think in hierarchies. But users don’t usually think in hierarchies (I think there’s research showing dropdown menus on web sites confuse users).

Consider the basic task in programming: declaring a variable. Here’s how you do this in VB:
Dim asdf as Int32

Here’s how you do this in C# (and any C-family):
int asdf;

When I want a variable, I already know what type it should be. The type matters to me and the compiler. The name matters a little to me, and practically none to the compiler. By the time I type “Dim asdf as” I already forgot what type I wanted, because I was so busy coming up with a variable name.

I feel this is backwards. Yes, I kinda have a thing about VB in this case…

Remember the pinky twitching example?

This forces the programmer to go all the way back to the root class, and then traverse the properties down again to reach the Twitch() function.

This shortcuts a lot of the traversing.

I have a lot of respect for the programmers working on open-source projects. They give their time and effort to improving software without pay. I just feel the design sometimes leave a little to be desired.

Have you looked at an iPhone? The interface allows the user to reach something within a couple of taps.

Do you know Google recommends that website links be no more than 3 levels deep? This means every page should be available from every other page via no more than 3 clicks.

So why did the first twitching example need 5 levels to reach my Twitch() function?

What ketchup bottle manufacturers can learn from a drinks stall uncle

Pouring milk
[image by Nicholas Moore]

My posterior hurt. It was a slow sultry afternoon, and my friends and I were hanging out at a hawker centre. We had dinner there and continued to chat. Then I couldn’t stand it any more and stood up. The seat was hard, and I was starting to lose sensation in my gluteus maximus.

I needed a drink anyway, so I went to a drinks stall to order teh si siu dai.

In the Hokkien dialect, teh means tea with condensed milk and sugar (when ordered from a typical drinks stall in Singapore. Otherwise it just literally translates to “tea”.). teh si means tea with evaporated milk (and sugar). Evaporated milk is less sweet than condensed milk, so sugar is typically added to the tea. siu dai means less sugar. So, say it with me, teh si siu dai means tea with evaporated milk and less sugar.
[end digression]

The drinks stall uncle found that his current can of evaporated milk ran out. (In Singapore, “uncle” can be used to address any male who’s older than you. If you really want to get into the Singapore mode of speech, pronounce it as “unk-uh”, without curling your tongue to form the “l” sound.) So he brought out a new can of evaporated milk, and what appeared to be an ice pick.

He placed the sharp end of the ice pick at one edge of the top of the can. He held it in place with one hand, then with his other hand, hammered the handle of the pick, and bored a hole into the can in one smooth motion.

My first thought was, “I would have used a can opener”.

Before I could finish that thought, the uncle took that ice pick, placed the sharp end at the other edge side of the can, and bored a hole there.

Tin can with holes by ice pick (top view)

My next thought was, “Why did he bore that second hole?”

Before I could finish that thought, the uncle held the can up and half-filled a glass with evaporated milk.

In that instant, I answered my own question. It was to let the air flow in, so a vacuum wasn’t created, so the evaporated milk would pour out smoothly.

Side view of bored tin can

And immediately after I got that insight, questions surfaced. “Why didn’t they do it for ketchup bottles? Why are ketchup bottles designed the way they are? Ketchup’s viscous, so why a small opening to make your life miserable? Why… oh thank you uncle.” My teh si siu dai was ready, and I took my drink back to my seat.

Well, I was never much of a condiment kind of guy, so ketchup bottles didn’t bother me much. Then again, in these modern times, I don’t see ketchup bottles in use any more. But just in case

today’s whippersnappers, lacking experience with this task in the home environment, need instruction all the more if they are to avoid a public faux pas and consequent humiliation

I will refer you to a definitive guide on ketchup decantation.

That’s it. Unless you want to know about the different variations of tea in Singapore…

CSS colours and hexadecimal

CSS, which stands for Cascading Style Sheets, allows you to work design wonders with your HTML. A web page is split into two parts, the design and look versus the data and functions of the page. And CSS is the main driving force behind the former. Aaron’s article gives an excellent introduction to this separation of form and function of a web page. Then stroll through the breathtaking CSS Zen Garden to gain inspiration for your CSS design project.

So why do you need to know anything about hexadecimals? Because a major component of CSS values come from declaring colours. And colours are represented in mainly hexadecimal values.

In CSS, colours are commonly represented as RGB triplets, such as #336699. RGB is a short form of Red, Green and Blue. A discussion of colour theory and representation is beyond the scope of this article. Simply know the six characters (not including the hex sign #) is divided into 3 parts. First two characters represent red, middle two for green and last two for blue.

These two-character parts are the hexadecimal values of the colour component (red or green or blue), and range from 0 to 255. The higher the number, the more of that colour component. So #0000ff represents pure blue. If it’s still new to you, rest easy, because most standard image processing software has a hexadecimal colour converter.

There are other ways of declaring colours in style sheets, such as color:Red. To a non-technical person, that is very useful. But we’re programmers. What if we need to dynamically generate the style sheet?