SpreadsheetLight version 3

Version 3 of my spreadsheet library is now available. There’s a whole bunch of updates, including Excel 2010 conditional formatting such as data bars with negative value fill colours and icon sets with no icons.

SpreadsheetLight is possibly the most developer-friendly spreadsheet library ever. Even if I do say so myself. 🙂

Software is everywhere

Coming soon to toasters and refrigerators. YouTube hates me, or WMV files, because the rendered video has visual static…

And celebrate whatever holiday is meaningful to you. Happy holidays!

Being a software god is tough

“Can this value be negative?” asked my colleague.

We were in a meeting with a product manager to get project requirements. The software application was to calculate settlement revenue between our company and our company’s partners, who were content providers. We charge the public customers for the content, then we share the revenue with the content providers.

The ideal situation was that all the numbers are nice and neat, people pay on time, everyone plays nice and so on and so forth. But reality isn’t this simple.

The value in question was that the product manager wanted to have a mechanism for him to introduce adjustments. You know, in case something happens and we should bill the content provider more, meaning we share less revenue with them.

But you know, you can’t just give the content provider a net number. They’d want to know why they’re receiving less money. So the adjustment had to be a line item on the settlement report.

Since I wasn’t the senior developer there, I decided to voice the concern to my colleague later on. But my colleague anticipated it. “Can this value be negative?” asked my colleague.

The product manager thought about it, and said yes, it could be negative. It means our company had to pay the content provider more money. Say we calculated last month’s settlement wrongly and we’re correcting that this month *cough*.

Let me tell you, finance people are getting blase with the number of zeroes in financial figures, but put one hyphen in front of a number and the whole financial sector goes into collective apoplexy. Sheesh…

My point is that unthinkable input values are always possible. Like negative values. Sometimes, I think developers forget the entire infinity of numbers on the other side of the real line…

I’m continuing sort of a discussion of the book “Geekonomics” by David Rice. Rice wrote that writing software was akin to creating an entire world in which the developer was, well, the supreme being.

Every single rule is determined by the developer. Every limit. Every calculation. Every display.

Well, everything that the developer is aware of anyway.

If the developer didn’t remember to put in the laws of gravity, that cannonball would’ve fired straight into space instead of landing nicely on the enemy tanks in that simulation war game.

Mother Nature takes care of anomalies in her stride. Entire species of dinosaurs dying out? No problem. Hey this mammalian species looks interesting. Let me give them a chance.

Software anomalies (read: bugs) aren’t so easily absorbed… With the world increasingly overrun by software, where medical devices, stock markets, airline ticket prices (supposedly tuned by software to remain competitive by comparing prices of other airlines), traffic lights, cars (Google cars are driven by software), online commerce and possibly even your toaster (if it isn’t already wired to the Internet), software anomalies can have a huge impact.

Software is written by developers. A developer is human. A human is flawed. Hence software is by design, flawed.

The Architect in The Matrix designed all the software in the world of Matrix (ok, maybe there’s delegation…). Right down to leaves falling and wind blowing in your face and steak tasting like steak (and not chicken) and pigeons flying like they’re supposed to.

But the Architect is also the creation of a human being. The Architect simulated the “real” world flawlessly, but only so far as human knowledge goes. What if Newtonian physics didn’t exist? If the imperial system or the metric system wasn’t invented, would the Architect invent some other measurement system?

In the end, the entire Matrix was bugged by an anomaly. Agent Smith.

And before that, the Matrix was bugged by another systemic anomaly. Which was sort of solved by creating the notion of The One. Hence Neo. (Ironically, the anomaly Agent Smith was the creation of the then current The One, the solution to solving the original systemic anomaly).

Get this. The Architect had to solve the systemic anomaly by flushing the entire Matrix, killing everyone except The One and the people chosen by The One to repopulate the Matrix.

Software is precise and elegant. Until it meets humans. Then everything hits the fan.

The Architect couldn’t solve his own software bugs (unless you call purging the entire Matrix as “solving”). Being a software god is tough.

Would you say the computer software entity known as The Architect is proficient in writing code? Because coming up soon, I’m going to write about another topic, that of software developer licensing, something that Rice also touched on. I’m talking “pass the bar or you don’t get to practice law” kind of accreditation.

Clopen source

A few years ago, when I was working in a job, I attended a social media course. There was a training budget to use up, and I couldn’t find any technical courses worth attending. So I attended the social media one.

At one point in the one-day course, the trainer was showing the attendees how easy it was to create a Wikipedia entry. He showed the update history. He showed how to edit other Wikipedia entries.

Now I’m going to tell you about a book I read recently. Don’t worry, it’s related. The book is “Geekonomics” by David Rice. There’s a chapter on open source (specifically related to software development).

Rice wrote that the openness of the open source movement might also be its downfall. Because anyone can contribute to an open source project, anyone does.

He also wrote that open source project contributors are typically not paid (in the form of money). They contribute for geek cred. And if you don’t contribute anything, you don’t get any geek cred at all.

And so developers typically contribute to features the developers want themselves or that the features are cool, and not because the features are user-requested or even helpful to the project. I mean they’re not paid, so they might as well do something cool.

Remember, no contribution, no geek cred.

Now Wikipedia is successful because if any “amateur” goes in to create “useless” entries or update existing entries with wrong information, there’s someone else who’s willing to go in and change it. The long tail of contributors work here because the entries aren’t typically arcane. And that contributors are motivated enough to make Wikipedia better.

Open source software projects don’t work quite as well. Amateur developers add useless or unnecessary features. No one wants to go in and edit code. Because developers do it for geek cred, if you go in and delete their stuff, even if their stuff is unnecessary or possibly even detrimental, the original developer/contributor is going to be upset with you. Because you’re removing their geek cred.

This had put me in a bit of a quandary. For the purposes of this article, I’ll define “open source” as:

  • Source code is available
  • Licensing is such that users are able to study, change, redistribute the software and the source code

I’m not sure about the licensing part. Any license that qualifies as open-source is good enough for me.

And “closed source” will be defined as “not everything that has to do with the software is made available”. I’ll explain the meaning in a bit.

I made a spreadsheet library and made it open source. It’s open source because the source code is available for anyone to read. I’ve licensed it with the MIT License, which basically allows you to do whatever you want with very few restrictions.

It’s also closed, because I didn’t include the .csproj file and the strong name key file in the downloadable package. There are various reasons, but the main ones are that I intend to keep the branding and that there’s only one “original” SpreadsheetLight library running out there (mine). Anyone who forks my source code can compile it, but the resulting DLL won’t be the “original”, so to speak.

So I consider my project as clopen source. “Clopen” is a mathematical term used in topology to mean both open and closed. I’m not going to bore you with the details. Go Google the definition yourself.

The main point I had was that, while my project was open source, I don’t quite encourage open collaboration. Open collaboration is not a requirement for a project to be considered open source. And it is open source, because you can view all the source code.

My main motivation was that I had a specific vision for the project, which was to make the spreadsheet library as easy to use as possible for developers who are on a tight schedule and don’t have time to learn how to make spreadsheets with a third party library. This meant I had to maintain strict control over things like method signatures and even method/property/class names.

Can you imagine having just anyone coming in to change the source code? Or just adding a feature because they need it, but the intended developer audience doesn’t need it?

Not every contributor is interested in being aligned with the project’s vision and purpose.

Blind libraries

I have discovered that the software I’m working on right now falls into a particularly interesting category.

Pre-emptive note: I mean no disrespect for blind people or people with visual problems.

There are software that are basically machine-machine. These software programs talk almost exclusively with other programs with nary a human interaction. Stock software, scheduled financial server programs, batch data upload programs.

Then there are software that’s basically made for human interaction. Facebook, Twitter, mobile map software, image/video editing software.

My software sits somewhere between them. I’m going to use Microsoft Excel as the example because that’s what my software is related to.

Microsoft Excel is essentially a visual software. Sure you can enter data into cells and the spreadsheet is basically rows and cells of data. But it’s geared for a human to understand that data, and to interact with that data. Excel merely exists to facilitate that interaction.

Short digression: I was reading up on Excel user manuals (yes, I do that) and it turns out that the earliest spreadsheet software were effectively command-line. We’ve come a long way since then, huh?

My software is a spreadsheet library/component. Basically it allows a program to create and manipulate spreadsheets without any user interaction. More specifically, without any visual interaction.

That monthly revenue report you need? Have the data uploaded first (probably the machine-machine type of software doing this). Then use a spreadsheet library to create that report, styling rows/columns/cells or add a chart.

And then the final product, the spreadsheet itself is handed over to a human.

I want you to think about this for a second. The spreadsheet library is working blind.

Teaching the blind to paint

Imagine teaching a blind person to paint a tree.

The blind person has no idea what green or brown looks like. He doesn’t even know what a tree looks like. But you tell the blind person that this container has green pigment and that container has brown pigment.

Then you teach the blind person to hold the paintbrush this way, and apply the green pigment like that, and to have the paintbrush come into contact with the canvas in this way. And with practice, leaves start to appear. Or some semblance of it. You do the same with the tree trunks and branches, using the brown pigment.

The blind person still has no idea what an actual tree looks like, so he doesn’t even know if what he painted is accurate. But he finishes his painting and hands that painting off to someone who can actually judge the painting.

That’s what I feel my software is doing. This is especially true when I have to design function interfaces that allow a developer to do visual things.

Like insert an image.

Oh I can let you insert an image. No problem. Give me a file name.

Where do I position it? Oh.

With respect to what on the screen? Oh yeah, what screen?

How do I know if it’s large enough? Oh yeah, can’t see the image.

Some things are simpler to understand. Like “I want that cell to have a purple background.” I’ll let you assign a System.Drawing.Color structure, or assign a hexadecimal value, or a theme colour.

Keep in mind that the software doesn’t care if you assign #00FFFF or #FFFF00.

So that’s my experience with my spreadsheet library. I’m working on a word processing library right now, which is even more insane.

While spreadsheets have styles, word processing documents live on styles. Word processing software like Microsoft Word or LibreOffice Writer will be useless if they don’t allow the user to bold this word or italicise that sentence or underline that heading.

Final thought

Microsoft Office is about USD 300+, which is a visual software. The “blind libraries” commercial software cost about USD 999. And that’s the low end price.

Did you know that mine is open source and free? Check out SpreadsheetLight (there we go, self-promotion).

Setting it free

It was the hardest, most painful decision I had to make in a long while.

I had worked hard on the project. I downloaded a few open-source versions of similar projects for comparison. I went through the use cases, on how to do certain tasks with those open-source projects.

Then I went to a couple of commercial projects. I couldn’t afford to buy them, but I went through sample source code to compare the use cases as well.

I did all that for research. Then I came up with a list of features that would be useful. Then I implemented them in a way that makes it easy for programmers to use.

It was also supposed to be a “big ticket” item, something I could sell for a higher price. I’m biased of course, but I believe my software is easier to use than either the open-source projects or the commercial projects.

The open-source projects targeted people who wanted free software, or supported open source projects (vehemently sometimes), or small to medium businesses. The commercial projects targeted the big enterprises. I targeted the small to medium businesses, appealing to the programmers or the IT managers/directors of those businesses and companies.

I launched my project. It didn’t do very well.

I added more features. I wrote detailed documentation of the software. I tweaked the price. I changed the sales copy. I did some advertising. I marketed the software project as best as I could. I created a whole website around it. Months of work went into the project. Still it floundered.

In the end, I dug into the core of why the project existed, and it was because I wanted to make the lives of programmers easier. And putting the software behind a payment wall might have put a dent in that.

And so I set it free.

It was heart-wrenching. I cried. Not so much for the lack of sales, but more because no one wanted to use the software. Ok, fine, it was equal measure of “no one using it” and “I need to eat”.

So if you have a few minutes, I’d appreciate it if you’d check out my spreadsheet software library. It’s called SpreadsheetLight, and it’s free for download, and has source code available. Tell someone who might find the software useful. Thanks.

Stereotypes and Programming

Generally, stereotypes are bad. They’re useful only as a tool to fill in gaps, but most people use it the wrong way, which is they rely *only* on their version of the stereotype.

Also, all software frameworks are stereotypes. Also, the sun was being dramatic.

SpreadsheetLight Relaunch

Just a quick note to let you know I’ve moved my spreadsheet software library, SpreadsheetLight, to its own website. Check it out.

Since the first launch in January, I’ve added support for tables, conditional formatting, basic charts and lots of small additional functions to make a spreadsheet programmer’s life easier.

To do this, I read books on how to use Excel. You read that right, I didn’t read up on how to write code libraries, I read up on how normal people use Excel. Because I want you to write spreadsheet code as easily as a person using Excel.

If an Excel guru recommends you to use a particular tip on your charts, I want to take that into account when I design the library functions.

Anyway, if you’re looking for a spreadsheet library, consider SpreadsheetLight. Or tell your manager. Tell your friends. I appreciate it. Thanks!

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

FTP clients for Mac

I use an FTP client to transfer my web files to my web server. And I hardly have to do even that. That said, the in-built interface of web hosting companies can leave a lot to be desired…

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.