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", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
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.

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

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

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

If you have a steady paycheck, the company paying you your salary is not going to ask for your salary back, right?

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?

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…

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?

You learn the basics of counting numbers in Chinese. Also, there was a lot of wind. And my camera battery threatened to die on me, hence the hurried nature. I also counted from 1 to 10 in Cantonese, Hokkien and Japanese.

To those participating in VEDA, you made it! VEDA stands for “Vlog Every Day (in) April”. It appears to be a thing that happens on YouTube. There’s also VlogMAFIA, which stands for “Vlogs May Appear Frequently In April”. YouTube people are fun…

So I use an FTP client. I didn’t search very hard, and found something that works well for me. It’s SmartFTP and you can check it out.

But I’m a Windows user. What if you’re a Mac user? (I understand a certain percentage of people read my blog on Mac machines. Ok, I’m basing it on users on Safari and iPad/iPhone, but still, it’s a relatively good assumption…).

So here are 5 Mac FTP clients. Disclaimer: this is an article from Rackspace. I’m not paid.

FTP For Free: Top 5 Mac FTP Clients You Won’t Pay For

Every organization seems to have its head in the cloud. The off-site, third-party solution for hosting, sharing files and archiving data is fast becoming the de facto tool method for individuals and organizations seeking to collaborate and share files.

But the truth is “the cloud” is simply a newer, more sophisticated version of a seasoned technology that’s been in place for years. Individuals and organizations have been using off-site and third-party resources and technology like File Transfer Protocol (FTP) servers and clients to store and share data for decades. The Cloud and cloud storage continue to make headlines, but FTP is still widely used behind the scenes as an easy way to transfer, share and synchronize large files.

One of the reasons FTP use has declined is its inherent lack of security. According to technology blogger Milton Keynes, Buckinghamshire, United Kingdom, FTP is an anachronistic protocol and should be abandoned in favor of more secure technologies.

But for quickly and easily transferring large files like data sets and high-resolution photos and video, FTP can’t be beat. Additionally, many FTP solutions are available for free; here are five of our favorite FTP clients for Mac.

FileZilla

By far the most popular free FTP client, and not just for Mac users. FileZilla is an open-source FTP client that supports Mac, Microsoft Windows and GNU/Linux, according to the FileZilla Project. One drawback: while the FileZilla client is platform-independent, the FTP Server is Microsoft Windows-specific.

Ed: I’m a .NET programmer. This isn’t that bad a drawback… But I’m not a Microsoft fan either. Be fanatic about what you can do with the platform, not the platform itself.

Support is plentiful for FileZilla, and available through the FileZilla website as well as an associated wiki, user and developer forums, bug fix requests and tracker reports.

Fetch Softworks

This is the original FTP client for the Mac, dating back to 1989. Fetch supports both FTP and SFTP, as well as FTP with TLS/SSL (FTPS), according to the Fetch website. One of Fetch’s greatest strengths is its ease of use; as with most things Macintosh, synchronizing and transferring files is as easy as drag-and-drop, and Fetch includes intuitive features like automatically resuming downloads of stalled or failed transfers. Fetch is compatible with nearly all FTP servers.

The Fetch website includes links to support resources, user forums, FAQs and message boards for users. The catch? Fetch is only free for a fifteen-day trial period, after which you must purchase the software.

Ed: I kinda like this one. What’s my reason? There’s a dog. Ok fine, the software looks competent. Most (if not all) purchases are based on emotions. On first impressions, I feel Fetch is better than the other 4 simply because I can imagine feeling comfortable using it. Understand that “free” is also a price. (I have nothing against free open-source projects)

Cyberduck

CNET.com editors call Cyberduck a “lean, mean, file managing machine,” and with good reason. The free, open-source software can interface with standard FTP servers as well as cloud-based solutions like Google Docs and Rackspace Cloud Files. Cyberduck, while an entry level (read: easy-to-use with limited functionality) client, nonetheless handles basic transfers, synchronization and interoperability quickly and easily.

If you’re looking for an agnostic, simple FTP client without a lot of unnecessary bells and whistles, give Cyberduck a try.

RBrowser

RBrowser is a UNIX-based, free, full-featured FTP and SFTP client designed with a simple graphic interface. RBrowser is more advanced than other SFTP clients; it combines all the secure tools available on both users’ local and remote systems, as well as maintaining a continuous secure connection to create links, according to the RBrowser website. With RBrowser, users are empowered to move and edit files quickly, and can make changes to files using direct remote-to-remote operations.

For more skilled users and UNIX-savvy administrators, RBrowser is the way to go.

Built-in Mac OS X FTP

While it’s not as full-featured as stand-alone FTP clients, Apple’s built-in FTP client works great if you’re looking for a simple way to transfer files quickly. It can be accessed directly from your Mac OS X desktop. The OS X Daily website offers step-by-step instructions with detailed screenshots. Once connected, you can browse files as though you’re looking at any local folder on your desktop, and transferring files is drag-and-drop.

Rackspace® Hosting is the service leader in cloud computing and founder of OpenStack™, an open source cloud platform. The San Antonio-based company provides Fanatical Support® to its customers, across a portfolio of IT services, including Managed Hosting and Cloud Computing. For more information, visit www.rackspace.com.