## Converting HSL to RGB

There seems to be 5 flavours of the “HSL”:

• HSL: Hue, Saturation, Lightness
• HSL: Hue, Saturation, Luminance
• HSV: Hue, Saturation, Value
• HSB: Hue, Saturation, Brightness
• HSI: Hue, Saturation, Intensity

I don’t understand why there are so many variants. I like RGB.

I needed a way to convert HSL (the luminance version) to RGB. Now .NET doesn’t have a way to convert any of the variants to its built-in System.Drawing.Color structure. Although the structure offers the GetHue(), GetSaturation() and GetBrightness() functions.

Generally speaking, the hue is between 0 and 360 degrees of a colour wheel. Saturation determines “how much” of that colour is displayed. And luminance determines “how bright/dark” that colour is.

So here’s the code to convert HSL to RGB (in the form of a System.Drawing.Color struct):

```public System.Drawing.Color RgbFromHsl(double Hue, double Saturation, double Luminance)
{
double fChroma = (1.0 - Math.Abs(2.0 * Luminance - 1.0)) * Saturation;
double fHue = Hue / 60.0;
double fHueMod2 = fHue;
while (fHueMod2 >= 2.0) fHueMod2 -= 2.0;
double fTemp = fChroma * (1.0 - Math.Abs(fHueMod2 - 1.0));

double fRed = 0, fGreen = 0, fBlue = 0;
if (fHue < 1.0)
{
fRed = fChroma;
fGreen = fTemp;
fBlue = 0;
}
else if (fHue < 2.0)
{
fRed = fTemp;
fGreen = fChroma;
fBlue = 0;
}
else if (fHue < 3.0)
{
fRed = 0;
fGreen = fChroma;
fBlue = fTemp;
}
else if (fHue < 4.0)
{
fRed = 0;
fGreen = fTemp;
fBlue = fChroma;
}
else if (fHue < 5.0)
{
fRed = fTemp;
fGreen = 0;
fBlue = fChroma;
}
else if (fHue < 6.0)
{
fRed = fChroma;
fGreen = 0;
fBlue = fTemp;
}
else
{
fRed = 0;
fGreen = 0;
fBlue = 0;
}

double fMin = Luminance - 0.5 * fChroma;
fRed += fMin;
fGreen += fMin;
fBlue += fMin;

fRed *= 255.0;
fGreen *= 255.0;
fBlue *= 255.0;

int iRed = 0, iGreen = 0, iBlue = 0;
// the default seems to be to truncate rather than round
iRed = Convert.ToInt32(Math.Truncate(fRed));
iGreen = Convert.ToInt32(Math.Truncate(fGreen));
iBlue = Convert.ToInt32(Math.Truncate(fBlue));
if (iRed < 0) iRed = 0;
if (iRed > 255) iRed = 255;
if (iGreen < 0) iGreen = 0;
if (iGreen > 255) iGreen = 255;
if (iBlue < 0) iBlue = 0;
if (iBlue > 255) iBlue = 255;

return System.Drawing.Color.FromArgb(iRed, iGreen, iBlue);
}
```

The algorithm is taken from the Wikipedia page on HSL and HSV. I added some checks at the end to make sure the RGB values are within 0 to 255.

About the only problem I had was the mod function. It’s not “integer modulus”, it’s “floating point modulus” (is that a thing?). So 5.5 mod 2 is 1.5 (because the remainder of 5.5/2 is 1.5). That took me a couple of hours to figure out. This is one of the few cases where thinking in programming terms instead of the mathematical terms cost me.

## The Crotch-Grabber Bully

In which I tell you about the time I was humiliated in front of an entire class by a teacher, was called a female fox, and almost had my nether regions grabbed. Though not at the same time (which would have made an awesome tale).

Untold in the video, is the time when I ran away from a bully. I was in the volleyball team in primary school. There was this big bully who was making fun of me. I think it was about my hair.

I ran away, by giving the teacher in charge an excuse.

Unfortunately, the school started a computer club, which happened to be in the exact time spot of the volleyball practises. Due to the excuse I gave, I couldn’t join the computer club.

What would’ve happened had I dealt with the bully in a different way, such that I was able to join the computer club? Would it have made a great different to me now? I wouldn’t know.

This post title is probably gonna attract all sorts of weird spam…

## 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).

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.

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 };
AutoFilter autoFilter1 = new AutoFilter(){ Reference = "I2:O12" };

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

sortState1.Append(sortCondition1);

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 };

tableColumns1.Append(tableColumn1);
tableColumns1.Append(tableColumn2);
tableColumns1.Append(tableColumn3);
tableColumns1.Append(tableColumn4);
tableColumns1.Append(tableColumn5);
tableColumns1.Append(tableColumn6);
tableColumns1.Append(tableColumn7);
TableStyleInfo tableStyleInfo1 = new TableStyleInfo(){ Name = "TableStyleDark4", ShowFirstColumn = true, ShowLastColumn = true, ShowRowStripes = true, ShowColumnStripes = true };

table1.Append(autoFilter1);
table1.Append(sortState1);
table1.Append(tableColumns1);
table1.Append(tableStyleInfo1);
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.SetTableStyle(SLTableStyleTypeValues.Dark4);

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.

## Squeezing solutions

In which I tell you about solving problems with at least 2 different approaches. And the squeeze theorem.

## Dot notation dropdown hell

Have you written something like this:
`body.LeftArm.ForeArm.Hand.Pinky.Twitch()`

Would this be easier?
`body.Twitch(BodyPart.LeftPinky)`

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?

`body.LeftArm.ForeArm.Hand.Pinky.Twitch()`
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.

`body.Twitch(BodyPart.LeftPinky)`
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?

## I was at Barcamp Singapore 8!

I attended some talks at Barcamp Singapore 8 held at Tembusu College. I also gave a talk on “Vlogging on YouTube”, which had less-than-enthusiastic reviews.

## Losing weight in different cultures

Generally speaking, “losing weight” is a bad thing in Asian cultures, because it means you don’t have enough food to eat.

you’re afraid people will ask for refunds, and you don’t make any money at all.

But if you have your own business, and if your customers ask for a refund? Then you’d have rendered a service or provided a product, but you don’t get paid.

If that’s the case, why are there so many food stalls here in Singapore? Singaporeans are notoriously picky eaters. It can’t be that every single food stall sells delicious food, right?

What are you truly scared of?

## Collision detection in merging Excel cells

You can merge cells in Excel. “Duh!” you say. So how does Excel ensure that the cells you want to merge don’t overlap any existing merged cells? How do you check programmatically that the cells you want to merge won’t overlap any existing merged cells?

Well, that was the problem I faced while writing my spreadsheet library software (check out SpreadsheetLight here!). The inkling of an idea was formed when I visited StackOverflow. Can’t remember what I was there for, but the answer page wasn’t about Excel or spreadsheets. It was about collision detection.

Yes, the type of collision detection used in games so you don’t walk through walls and stuff.

And thus I learnt a new mathematical theorem: the separating axis theorem (or hyperplane separation theorem). From what I understand, given two N-dimensional thingies, if you can find one (N-1)-dimensional hyperplane that separates those two thingies, then those two thingies are separate (or not overlapping).

Yes, “thingies” is a technical term.

For example, given two 3D objects, if you can find one 2D plane that separates them, then the 3D objects are not overlapping. Another example is when you’re given two 2D objects, and if you can find one line (which is 1D) that separates them, then the 2D objects are not overlapping.

There are some conditions to be fulfilled, such as the objects being convex and disjoint and whatever. (yes I’m a mathematician…) I’ll leave it to you to do your own reading.

But for our purposes, how do we check if that rectangle of Excel cells we want to merge won’t overlap with any existing merged cells? We have these conditions that make our lives easier:

• The merged cells are rectangular
• The 4 end points (top-left, top-right, bottom-left, bottom-right) of a merged cell are in whole numbers
• The (merged) cells map strictly to a 2D rectangular grid

Since the merged cells are rectangular, they’re also convex (I’m not going to explain why, just trust me). Since the 4 end points are in whole numbers, line boundaries can be easily calculated (it’s easy to check if something is <7 or >=8 for example). And since they map strictly to a 2D rectangular grid, the separating axis is a line. And even better, you can always find a subset of solutions of those separating axes that are either horizontal or vertical.

Ok, diagrams are in order.

So in our case, as long as you can find one horizontal line or one vertical line that separates the 2 merged cells we’re checking, then the 2 merged cells are separate.

Let’s try an example. Excel uses cell references that are in the A1 format, which means row 3 column 4 becomes “D3”. The column index uses alphabets and is in base-26. Read more here.

We’re going to simplify that. Let’s say our existing set of merged cells only has one entry. This merged cell has the top-left corner at row 1 column 1, and the bottom-right corner at row 3 column 4. Suppose we want to check if this merge cell overlaps: top-left corner at row 7 column 8, bottom-right corner at row 10 column 10.

The horizontal line of 5 fits the bill, or the line y=5 if you want to be mathematical about it (but y goes from negative to positive downwards instead of the usual Cartesian y). Or y=6. Or even y=7 (note that the line can “touch” one of the merged cells, but not both. This is where the “whole number” condition comes in).

The vertical lines x=5, x=6 or x=8 also fit the bill.

Thus, our 2 merged cells don’t overlap.

So what’s the programmatic way to check? You’d be surprised at the simplicity. To make it easier, the variables with “StartRowIndex” mean the top row of the merged cell, “EndRowIndex” mean the bottom row of the merged cell. And “StartColumnIndex” mean the left-most column of the merged cell, and “EndColumnIndex” mean the right-most column of the merged cell.

```if (!(iEndRowIndex < mc.StartRowIndex || iStartRowIndex > mc.EndRowIndex || iEndColumnIndex < mc.StartColumnIndex || iStartColumnIndex > mc.EndColumnIndex))
{
// there's an overlap!
}
```

So the merge cell we want to check has top-left corner at (iStartRowIndex, iStartColumnIndex) and bottom-right corner at (iEndRowIndex, iEndColumnIndex).

The variable “mc” refers to a custom class I use to represent merged cells. Obviously, you’d run that condition in a loop through all your existing merged cells. If you can’t find an overlap after running through that loop, then the merge cell you’re checking is good to go.

Let’s run through the individual conditions.

• (iEndRowIndex < mc.StartRowIndex) means our merged cell is completely above the existing merged cell
• (iStartRowIndex > mc.EndRowIndex) means our merged cell is completely below the existing merged cell
• (iEndColumnIndex < mc.StartColumnIndex) means our merged cell is completely to the left of the existing merged cell
• (iStartColumnIndex > mc.EndColumnIndex) means our merged cell is completely to the right of the existing merged cell

The first 2 conditions check for the existence of a horizontal separating axis. The next 2 conditions check for the existence of a vertical separating axis.

Note the negation of the entire boolean condition. Those 4 conditions check for existence of solutions. Negation means checking for overlaps.

Note also that we use strictly greater than or strictly less than checks. If the merge cells share a row or column, then they overlap, right?

Did you know my first draft of writing the code had me checking 6 different conditions? Each condition had its own if statements. This one just had one. I’m so lucky to have learnt the separating axis theorem. I was checking if a point was above, within or below the merge cell, then I had to check if the point was to the left, within or to the right of the merge cell, and … urgh, it was horrible…

## \$100 Startup bundle

I respect Chris Guillebeau a lot (possibly even a raving fan). His book \$100 Startup is going to be on sale soon. But the more important reason is that you can get that together with a bunch of business-y stuff at Only72 right now for \$100 total (yes, that’s totally an affiliate link).

Out of all the products in the bundle, I’m interested in the “How To Make iPhone Apps With No Programming Experience” ebook, the small business infrastructure ebook, and guide to publishing ebook.

You want to start a business? This’ll get you started. Hurry, it’s only on sale for 72 hours (hence the name).

Can’t wait to read Chris’s new book. Have I mentioned that’s a hardcover that will be shipped anywhere you live if you buy the bundle now?