Teaching classes

I’ve been asked what possible career could I have with a maths degree. The asker usually already have an answer. Be a teacher.

When I graduated from university, it was about a year after Sep 9/11. Finding a job was hard. My friends, driven to desperation, decided to see if teaching was a viable career. I almost wanted to try too, but then I deliberately decided not to be a teacher (at least in Singapore), so I just followed my friends to the application centre.

I spent maybe an hour looking at the architecture of the centre while my friends filled in the application forms. So in Singapore, before you’re approved as a teacher, you had to go study at National Institute of Education (NIE) first. This is on top of whatever degree you’ve already earned. NIE teaches you how to be an effective teacher (we take education seriously here in Singapore).

After NIE, then you get assigned to schools. I don’t know the details, but I believe you don’t get a choice about the assignment.

Out of my university friends, only one actually became a teacher. There was once I mentioned that I forgot the double angle formulas of sine and cosine. She lectured me on that, together with another friend who was a part-time maths tutor.

I have since learned my lesson. (By not saying anything that’s maths-related. What, you think I’d go memorise the double angle formula just in case? Sheesh…)

So it’s a weird turn of events that I contemplated teaching a class of my own. It’s when I realised that classes on professional subjects are different than academic subjects. I’ve gone to a few Visual Studio courses (because they were the only courses I was approved to attend, and upper management pressured me to “Go take some courses dammit Vincent!”. Well, my manager didn’t actually say that, but the tone was there…), and the main difference was that you actually have to get something done.

So now, I’m teaching a programming course. Apparently, there are many tutorials out there teaching you how to do something in an Open XML spreadsheet (and for Word documents), but no one really teaches you how to know what settings to use.

The goal of the course: Given an Open XML spreadsheet (say, as a user requirement), reproduce that spreadsheet (with data changes of course). It’s called “OpenXML Spreadsheet Boot Camp”, and you can find out more here.

SpreadsheetLight now freely downloadable

Wow, I just mentioned I relaunched SpreadsheetLight, and now it’s free?

Truth be told, it was a hard decision. I spent a lot of time and effort on SpreadsheetLight. There’s an ECMA documentation PDF that’s over 5000 pages long involved. There’s cross-referencing with the Open XML SDK documentation. There’s experimentation with the actual Excel software to make sure the values are correct. I wrote copious amounts of XML documentation (every single public function and property) so that IntelliSense helps the programmer while he’s using the library.

Did you know Excel’s interface takes in values that are different when assigned to certain corresponding properties? SpreadsheetLight functions take in those Excel “approved” values rather than the Open XML recognised ones. Using SpreadsheetLight is as close to using Excel as you can get.

The crucial point came down to this: I want programmers to benefit from the software. It’s of no use to me or any programmer lying forgotten and hidden behind a pay wall.

And so I freed it.

It’s now free, both in gratis and libre. It uses the MIT License. Download it. Do whatever you want. Come back and tell me how easy your life became.

I only ask that you tell anyone who might benefit from the use of a spreadsheet library. Even if you don’t need to create spreadsheets, you can still download it and see how easy it is to create one.

SpreadsheetLight can be found here. Thank you.

Translating user requirements to code can be hard

Back when I was working as a professional programmer (my job title was “Systems Analyst”), I would attend meetings with my supervisor to gather user requirements. Sometimes, I’m not required at the meeting, but typically, my supervisor needed to know if something was technically feasible. That’s where I came in.

At those meetings, which could be 3 to 4 hours long, I would gather notes. (Once I had to attend a whole-day event. I had to look at it as “I learnt more about my users’ business” rather than “I wasted an entire day”. I’ll tell you about it some other time…) Sometimes, these notes weren’t written because my users specifically told me about it. It’s just that I took note of potential technical limitations and problems that my users didn’t see.

For example, they might draw a sketch of the user interface on the whiteboard and I’d copy it if necessary. Then I took notes on the possible internal code structure if necessary.

The hardest kinds of user requirement gathering are the super-really-obvious ones. Even to you.

The hardest user requirement I’ve ever been given was an Excel spreadsheet. That’s it. The user basically said, “I want you to give me a report that looks like this Excel file.” Implicit requirement was that the file data reflect current database data.

I had to hunt for the source of the data (which database?). Other than that, I had to figure out how to create the Excel spreadsheet layout based on the given sample file.

You can’t really ask what settings the particular spreadsheet cell has, because the user would say “It’s there! In the file! What do you mean how did I set it?” All the styling/layout requirements was already in the Excel file.

I’m starting a course!

All this is really a long way to tell you that I’m starting a course teaching about Open XML spreadsheets, using the Open XML SDK as part of the tool kit.

“But there are lots of resources online about Open XML! And they’re free!”

I know. They tell you how to do a specific task, such as setting a cell value or “This is how to read an Open XML file” and so on. But they don’t tell you why you should do it.

I’ve got a couple of customers (who bought my Open XML spreadsheet reference) asking me, “How do you set this style?”. Then they show me a screenshot or the actual Excel file, and it’s like this complicated (or elegantly professional, depending on your perspective) mess of a jumble of cells with background colours and borders and OMG is that a merged cell?! (One programmer sent me the actual file he’s to simulate, and I felt really sorry for him…)

So in addition to teaching you about the Open XML parts and code and stuff, I’ll also teach you how to translate a given Open XML spreadsheet into code that generates that spreadsheet.

Tentative price is USD 30 for the whole course. The course is about 8 to 10 lessons, with each lesson given weekly. I’ve a rough outline of the course curriculum, but I’m open to suggestions at this point (contact me or leave a comment). It should be up in the next couple of weeks.

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.

Tax season and SpreadsheetLight deal

It’s currently tax season. Making financial and tax reports can be taxing (haha!). I know, because I’ve spent years writing software that creates Excel reports for sales, revenue, debt and other financial reports.

So from now till the end 15th of April 2012, I’m offering my spreadsheet software library at the unbelievably low cost of USD 199 150. SpreadsheetLight runs on .NET Framework and Open XML SDK, is written in C#, uses the MIT License, and is designed to be easy to integrate into existing software projects with a minimum of fuss.

UPDATE: The promotion will be until 16th April 2012, and I’m selling it at USD 150. Need a spreadsheet library software? Get it now before I raise the price.

The reason is because I want you to have an easier time writing your programs. My experience in churning out Excel reports tells me it can be frustrating at times…

So check out SpreadsheetLight. Or get your manager to take a look.

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…

Launching SpreadsheetLight

I am excited to tell you that my spreadsheet software library is available!

SpreadsheetLight

For the initial launch version (I decided to go for version 1. Why do people launch with versions 0.8? I don’t know…), you get comprehensive support for styles, rich text formatting, 47 named cell styles, themes (either one of the 20 built-in themes or create-your-own). Well, like I said, comprehensive styling support.

There’s also the (hum-drum) support for merging cells and freezing panes. I actually explored how to split panes. I certainly wrote about it in my Open XML guide, but it turns out that there’s a tiny rectangle at the top-left corner that Microsoft Excel didn’t tell me about. The size of that tiny rectangle is dependent on the font you use, and even the screen resolution of your computer screen.

While I could add a function that allows you to just input the size in EMUs (English Metric Units), I decided that if I can’t do it well, I don’t want to do it. At least for the initial launch.

Row heights and column widths were also big time drains. It turns out that they’re also dependent on the font and screen resolution of your computer screen. I was trying to calculate the standard row heights and column widths for the fonts in the built-in themes, and I thought I had them. I wrote a program using SpreadsheetLight to generate spreadsheets with different minor fonts, and I wrote a program to read in those spreadsheets and get the “standard” row height and column width. I spent 3 hours collecting data.

Then on a whim, I switched my computer screen’s resolution from 120 DPI to 96 DPI (my eyesight’s not that good ok? I need bigger text…), and whoa! All that data doesn’t apply anymore… All in all, I think I spent 6 or 7 days trying to figure out a general calculation formula. I failed. I don’t know how Excel does it.

I also surprised myself by including extensive support for pictures. I thought you just insert a picture into the worksheet and that’s it! It turns out there’s a ton of post-insertion manipulations you can do. For example, if your picture has transparent areas, you could set a background fill, and that background colour will be visible through the picture. Also, you can rotate the picture in 3D.

SpreadsheetLight is licensed under the MIT license. I decided to use one of the available software licenses instead of making up one of my own. As far as I can tell, the MIT license allows the recipient of the software to use the software in personal or commercial products. It’s also categorised as free software, as in freedom of use, not free as in cost. I don’t want to deal with per-client, or per-server, or per-developer or per-what-not licensing restrictions.

Even if you’re not interested in spreadsheet software, have a look at SpreadsheetLight. Tell some other programmer about it. Tell your manager about it. It took me slightly over 2 months of intense coding, and I want someone in the world out there to have an easier life because of SpreadsheetLight. Thanks!

Oh, and the image art is designed by Charlie Pabst from Charfish Design. While I have a fair competence in image work, I decided to get a professional designer to help me. It’s a business and professional product. I’m not going to risk the product’s success so I could stoke my ego…

Named cell styles are still explicitly declared

Styling cells in Microsoft Excel has its difficulties (as I’ve written before). The biggest one is keeping track of all the indices. In Open XML SDK, you have the ability to have a named cell style.

At first glance, you might think that’s awesome. You just use a named cell style, and all the related styles are applied. It’s like there’s a red car that uses hybrid fuels. “Yes, I would like to have a red car that uses hybrid fuels.” Not quite.

You see, the named cell style is dependent on the implementing spreadsheet software. For example, Microsoft Excel has the “Normal”, “Bad”, “Good” and “Neutral” named cell styles. But Google Spreadsheets and OpenOffice.org Calc do not have to have those named cell styles, or even style it the same as Microsoft Excel. This is where Open XML SDK isn’t quite “open”… After much research and work, I discovered the SDK is basically Open-XML-ising Microsoft Excel (and Word and PowerPoint). I’m neutral on the stands of open source and “forcing standards”. I just use whatever there is, and make something within the limitations.

Because of the dependency, the underlying individual styles need to be declared explicitly. Actually more so because of the dependency.

So for my spreadsheet software library SpreadsheetLight, I used Excel as the guideline.

In researching Excel named cell styles, I had to look at the underlying XML files (because Open XML spreadsheets are made of XML files). While the Open XML SDK comes with a document explorer (the Productivity Tool), I needed to make notes and also that I felt the need to see the XML file itself, rather than using the explorer tool.

This gave me a problem because while XML files are supposed to be human-readable, it doesn’t make it easy to read. The “natural” XML file has no indents. Oh my Godiva chocolate, it’s so hard to read… Then I remembered I had an XML tool, XML Studio. I fired that up and a few clicks later, the XML file had nice indents and I could find out where the individual style tags were. XML Studio was amazing to use.

Disclaimer: I was given a free developer license of XML Studio by Liquid Technologies. But the software is really useful if you work with XML files a lot.

After doing my notes for a while, I discovered even that’s not enough. There were too many individual styles! I needed the indices for those styles, because only the index was referenced in the final style (CellFormat classes). I didn’t really feel up to annotating the indices… until I remembered my partially completed Open XML spreadsheet decompiler tool. When I created that tool, one of my aims was to put in comments on the index of the individual styles.

Note to Liquid Technologies: You might want to consider putting in XML comments on the index of an XML child tag with respect to its parent. But I don’t know if that’s useful to programming spheres other than Open XML…

Anyway, my hard work paid off, and SpreadsheetLight allows you to apply named cell styles. Here’s how the spreadsheet looks like:
Applying named cell styles

Note that some of the named cell styles use accent colours. The accent colours are part of the spreadsheet’s theme. So in offering named cell styles as a feature, I also had to allow you to create your own theme. And here’s the code using SpreadsheetLight:

System.Drawing.Color[] clrs = new System.Drawing.Color[12];
clrs[0] = System.Drawing.Color.White;
clrs[1] = System.Drawing.Color.Black;
clrs[2] = System.Drawing.Color.WhiteSmoke;
clrs[3] = System.Drawing.Color.DarkSlateGray;
clrs[4] = System.Drawing.Color.DarkRed;
clrs[5] = System.Drawing.Color.OrangeRed;
clrs[6] = System.Drawing.Color.DarkGoldenrod;
clrs[7] = System.Drawing.Color.DarkOliveGreen;
clrs[8] = System.Drawing.Color.Navy;
clrs[9] = System.Drawing.Color.Indigo;
clrs[10] = System.Drawing.Color.SkyBlue;
clrs[11] = System.Drawing.Color.MediumPurple;

SLDocument sl = new SLDocument("ColourWheel", "Castellar", "Harrington", clrs);

sl.SetRowHeight(6, 24);
sl.SetColumnWidth(1, 1);
sl.SetColumnWidth(2, 13);
sl.SetColumnWidth(3, 13);
sl.SetColumnWidth(4, 13);
sl.SetColumnWidth(5, 13);
sl.SetColumnWidth(6, 13);
sl.SetColumnWidth(7, 13);

sl.SetCellValue(2, 2, "Normal");
sl.ApplyNamedCellStyle(2, 2, SLNamedCellStyleValues.Normal);
sl.SetCellValue(2, 3, "Bad");
sl.ApplyNamedCellStyle(2, 3, SLNamedCellStyleValues.Bad);
sl.SetCellValue(2, 4, "Good");
sl.ApplyNamedCellStyle(2, 4, SLNamedCellStyleValues.Good);
sl.SetCellValue(2, 5, "Neutral");
sl.ApplyNamedCellStyle(2, 5, SLNamedCellStyleValues.Neutral);

sl.SetCellValue(3, 2, "Calculation");
sl.ApplyNamedCellStyle(3, 2, SLNamedCellStyleValues.Calculation);
sl.SetCellValue(3, 3, "Check Cell");
sl.ApplyNamedCellStyle(3, 3, SLNamedCellStyleValues.CheckCell);
sl.SetCellValue(3, 4, "Explanatory Text");
sl.ApplyNamedCellStyle(3, 4, SLNamedCellStyleValues.ExplanatoryText);
sl.SetCellValue(3, 5, "Input");
sl.ApplyNamedCellStyle(3, 5, SLNamedCellStyleValues.Input);

sl.SetCellValue(4, 2, "Linked Cell");
sl.ApplyNamedCellStyle(4, 2, SLNamedCellStyleValues.LinkedCell);
sl.SetCellValue(4, 3, "Note");
sl.ApplyNamedCellStyle(4, 3, SLNamedCellStyleValues.Note);
sl.SetCellValue(4, 4, "Output");
sl.ApplyNamedCellStyle(4, 4, SLNamedCellStyleValues.Output);
sl.SetCellValue(4, 5, "Warning Text");
sl.ApplyNamedCellStyle(4, 5, SLNamedCellStyleValues.WarningText);

sl.SetCellValue(6, 2, "Heading 1");
sl.ApplyNamedCellStyle(6, 2, SLNamedCellStyleValues.Heading1);
sl.SetCellValue(6, 3, "Heading 2");
sl.ApplyNamedCellStyle(6, 3, SLNamedCellStyleValues.Heading2);
sl.SetCellValue(6, 4, "Heading 3");
sl.ApplyNamedCellStyle(6, 4, SLNamedCellStyleValues.Heading3);
sl.SetCellValue(6, 5, "Heading 4");
sl.ApplyNamedCellStyle(6, 5, SLNamedCellStyleValues.Heading4);
sl.SetCellValue(6, 6, "Title");
sl.ApplyNamedCellStyle(6, 6, SLNamedCellStyleValues.Title);
sl.SetCellValue(6, 7, "Total");
sl.ApplyNamedCellStyle(6, 7, SLNamedCellStyleValues.Total);

sl.SetCellValue(8, 2, "Accent1");
sl.ApplyNamedCellStyle(8, 2, SLNamedCellStyleValues.Accent1);
sl.SetCellValue(8, 3, "Accent2");
sl.ApplyNamedCellStyle(8, 3, SLNamedCellStyleValues.Accent2);
sl.SetCellValue(8, 4, "Accent3");
sl.ApplyNamedCellStyle(8, 4, SLNamedCellStyleValues.Accent3);
sl.SetCellValue(8, 5, "Accent4");
sl.ApplyNamedCellStyle(8, 5, SLNamedCellStyleValues.Accent4);
sl.SetCellValue(8, 6, "Accent5");
sl.ApplyNamedCellStyle(8, 6, SLNamedCellStyleValues.Accent5);
sl.SetCellValue(8, 7, "Accent6");
sl.ApplyNamedCellStyle(8, 7, SLNamedCellStyleValues.Accent6);

sl.SetCellValue(9, 2, "Accent1Perc60");
sl.ApplyNamedCellStyle(9, 2, SLNamedCellStyleValues.Accent1Percentage60);
sl.SetCellValue(9, 3, "Accent2Perc60");
sl.ApplyNamedCellStyle(9, 3, SLNamedCellStyleValues.Accent2Percentage60);
sl.SetCellValue(9, 4, "Accent3Perc60");
sl.ApplyNamedCellStyle(9, 4, SLNamedCellStyleValues.Accent3Percentage60);
sl.SetCellValue(9, 5, "Accent4Perc60");
sl.ApplyNamedCellStyle(9, 5, SLNamedCellStyleValues.Accent4Percentage60);
sl.SetCellValue(9, 6, "Accent5Perc60");
sl.ApplyNamedCellStyle(9, 6, SLNamedCellStyleValues.Accent5Percentage60);
sl.SetCellValue(9, 7, "Accent6Perc60");
sl.ApplyNamedCellStyle(9, 7, SLNamedCellStyleValues.Accent6Percentage60);

sl.SetCellValue(10, 2, "Accent1Perc40");
sl.ApplyNamedCellStyle(10, 2, SLNamedCellStyleValues.Accent1Percentage40);
sl.SetCellValue(10, 3, "Accent2Perc40");
sl.ApplyNamedCellStyle(10, 3, SLNamedCellStyleValues.Accent2Percentage40);
sl.SetCellValue(10, 4, "Accent3Perc40");
sl.ApplyNamedCellStyle(10, 4, SLNamedCellStyleValues.Accent3Percentage40);
sl.SetCellValue(10, 5, "Accent4Perc40");
sl.ApplyNamedCellStyle(10, 5, SLNamedCellStyleValues.Accent4Percentage40);
sl.SetCellValue(10, 6, "Accent5Perc40");
sl.ApplyNamedCellStyle(10, 6, SLNamedCellStyleValues.Accent5Percentage40);
sl.SetCellValue(10, 7, "Accent6Perc40");
sl.ApplyNamedCellStyle(10, 7, SLNamedCellStyleValues.Accent6Percentage40);

sl.SetCellValue(11, 2, "Accent1Perc20");
sl.ApplyNamedCellStyle(11, 2, SLNamedCellStyleValues.Accent1Percentage20);
sl.SetCellValue(11, 3, "Accent2Perc20");
sl.ApplyNamedCellStyle(11, 3, SLNamedCellStyleValues.Accent2Percentage20);
sl.SetCellValue(11, 4, "Accent3Perc20");
sl.ApplyNamedCellStyle(11, 4, SLNamedCellStyleValues.Accent3Percentage20);
sl.SetCellValue(11, 5, "Accent4Perc20");
sl.ApplyNamedCellStyle(11, 5, SLNamedCellStyleValues.Accent4Percentage20);
sl.SetCellValue(11, 6, "Accent5Perc20");
sl.ApplyNamedCellStyle(11, 6, SLNamedCellStyleValues.Accent5Percentage20);
sl.SetCellValue(11, 7, "Accent6Perc20");
sl.ApplyNamedCellStyle(11, 7, SLNamedCellStyleValues.Accent6Percentage20);

sl.SetCellValue(13, 2, 12345678);
sl.ApplyNamedCellStyle(13, 2, SLNamedCellStyleValues.Comma);
sl.SetCellValue(13, 4, 12345678);
sl.ApplyNamedCellStyle(13, 4, SLNamedCellStyleValues.Comma0);
sl.SetCellValue(14, 2, 12345678);
sl.ApplyNamedCellStyle(14, 2, SLNamedCellStyleValues.Currency);
sl.SetCellValue(14, 4, 12345678);
sl.ApplyNamedCellStyle(14, 4, SLNamedCellStyleValues.Currency0);
sl.SetCellValue(15, 2, 123);
sl.ApplyNamedCellStyle(156, 2, SLNamedCellStyleValues.Percentage);

sl.SaveAs("NamedCellStyles.xlsx");

I set the column widths of the 2nd to 7th column, and the row height of the 6th row so it’s easier to see.

The main body text (the minor font) is in Harrington, and the title font (major font) is Castellar. You will note that even though the major font is supposedly used for heading and title texts, only the named cell style Title uses the major font. The headings 1 through 4 use the minor font.

Pictures in Excel have way too much power…

You’d think inserting a picture into an Excel worksheet is pretty straightforward. If you’re just inserting the picture, then it is.

Then you check what other properties you can play with manipulating the picture. Woah amigo! There’s a whole plethora of options!

Excel picture format options

That’s like giving the typical user a (good enough) subset of rendering functions, including 3D. And here I thought you could just resize the picture, and maybe rotate it… If you’re not going for full 3D manipulation as in OpenGL or DirectX, Excel will do it for you.

Excel respects alpha/transparency of the picture (right now, I only know of PNG images that contain alpha information AND is fairly prevalent). And you can outline the picture and fill the transparent portions with a colour.

SpreadsheetLight picture fill and outline

You can even do shadows!

SpreadsheetLight picture shadows

Do you know it can take a phenomenal amount of work to get shadows working in 3D renderings? You’ve got to know the light source position, calculate the rays of light blocked by an object to create the illusion of a shadow (by creating a polygon that’s the outline of that object as described by those blocked rays of light). Well, technically speaking, Excel has got it easy, since the object is always a rectangle (an image).

Speaking of light, we can also do reflections. Let’s run a full gamut of the 3D power.

SpreadsheetLight 3D pictures

I lost the software keys to my 2 3D rendering software (Bryce 4 and TrueSpace). It sucked that I couldn’t render landscapes anymore… But, I’m seeing Excel as an alternative to getting some 3D-ish images.

Well, the above screenshots were from a spreadsheet generated by my software library, SpreadsheetLight. The library is based on the Open XML SDK, which in turn is based on what Excel can do. Here’s how the source code looks like:

SLDocument sl = new SLDocument(SLThemeTypeValues.Oriel);

SLPicture pic;
                
sl.SetCellValue(2, 2, "Accent 1");
sl.ApplyNamedCellStyle(2, 2, SLNamedCellStyleValues.Accent1);
sl.SetCellValue(2, 3, "Accent 2");
sl.ApplyNamedCellStyle(2, 3, SLNamedCellStyleValues.Accent2);
sl.SetCellValue(2, 4, "Accent 3");
sl.ApplyNamedCellStyle(2, 4, SLNamedCellStyleValues.Accent3);
sl.SetCellValue(2, 5, "Accent 4");
sl.ApplyNamedCellStyle(2, 5, SLNamedCellStyleValues.Accent4);
sl.SetCellValue(2, 6, "Accent 5");
sl.ApplyNamedCellStyle(2, 6, SLNamedCellStyleValues.Accent5);
sl.SetCellValue(2, 7, "Accent 6");
sl.ApplyNamedCellStyle(2, 7, SLNamedCellStyleValues.Accent6);

pic = new SLPicture("mandelbrot.png");
// anchor at cell (4,2) with 0 horizontal and vertical offsets.
pic.SetRelativePositionInPixels(4, 2, 0, 0);
sl.InsertPicture(pic);

pic = new SLPicture("mandelbrot.png");
pic.SetRelativePositionInPixels(4, 6, 0, 0);
pic.SetSolidFill(DocumentFormat.OpenXml.Drawing.SchemeColorValues.Accent3, 20);
pic.SetSolidOutline(DocumentFormat.OpenXml.Drawing.SchemeColorValues.Accent4, 0);
pic.SetOutlineStyle(4, DocumentFormat.OpenXml.Drawing.CompoundLineValues.Double, DocumentFormat.OpenXml.Drawing.PresetLineDashValues.LargeDash, DocumentFormat.OpenXml.Drawing.LineCapValues.Round, SLPicture.SLPictureJoinType.Bevel);
sl.InsertPicture(pic);

sl.AddWorksheet("Sheet2");

sl.SetCellValue(2, 2, "Accent 1");
sl.ApplyNamedCellStyle(2, 2, SLNamedCellStyleValues.Accent1);
sl.SetCellValue(2, 3, "Accent 2");
sl.ApplyNamedCellStyle(2, 3, SLNamedCellStyleValues.Accent2);
sl.SetCellValue(2, 4, "Accent 3");
sl.ApplyNamedCellStyle(2, 4, SLNamedCellStyleValues.Accent3);
sl.SetCellValue(2, 5, "Accent 4");
sl.ApplyNamedCellStyle(2, 5, SLNamedCellStyleValues.Accent4);
sl.SetCellValue(2, 6, "Accent 5");
sl.ApplyNamedCellStyle(2, 6, SLNamedCellStyleValues.Accent5);
sl.SetCellValue(2, 7, "Accent 6");
sl.ApplyNamedCellStyle(2, 7, SLNamedCellStyleValues.Accent6);

pic = new SLPicture("mandelbrot.png");
pic.SetRelativePositionInPixels(4, 2, 0, 0);
// transparency 0%, blur 6pt, angle 90 degrees, distance 3pt
pic.SetInnerShadow(DocumentFormat.OpenXml.Drawing.SchemeColorValues.Accent1, 0, 6, 90, 3);
sl.InsertPicture(pic);

pic = new SLPicture("mandelbrot.png");
pic.SetRelativePositionInPixels(4, 6, 0, 0);
// transparency 50%, horizontal size 100%, vertical size -23%, horizontal skew -13.34%, vertical skew 0%
// blur 6pt, angle 45 degrees, distance 3pt
// origin at picture's bottom left, don't rotate with picture.
pic.SetPerspectiveShadow(DocumentFormat.OpenXml.Drawing.SchemeColorValues.Accent2, 50, 100, -23, -13.34m, 0, 6, 45, 3, DocumentFormat.OpenXml.Drawing.RectangleAlignmentValues.BottomLeft, false);
sl.InsertPicture(pic);

sl.AddWorksheet("Sheet3");

sl.SetCellValue(2, 2, "Accent 1");
sl.ApplyNamedCellStyle(2, 2, SLNamedCellStyleValues.Accent1);
sl.SetCellValue(2, 3, "Accent 2");
sl.ApplyNamedCellStyle(2, 3, SLNamedCellStyleValues.Accent2);
sl.SetCellValue(2, 4, "Accent 3");
sl.ApplyNamedCellStyle(2, 4, SLNamedCellStyleValues.Accent3);
sl.SetCellValue(2, 5, "Accent 4");
sl.ApplyNamedCellStyle(2, 5, SLNamedCellStyleValues.Accent4);
sl.SetCellValue(2, 6, "Accent 5");
sl.ApplyNamedCellStyle(2, 6, SLNamedCellStyleValues.Accent5);
sl.SetCellValue(2, 7, "Accent 6");
sl.ApplyNamedCellStyle(2, 7, SLNamedCellStyleValues.Accent6);

pic = new SLPicture("mandelbrot.png");
pic.SetRelativePositionInPixels(6, 3, 0, 0);
pic.SetFullReflection();
// width 6pt, height 6pt
pic.Set3DBevelBottom(DocumentFormat.OpenXml.Drawing.BevelPresetValues.Convex, 6, 6);
// width 3pt, height 4pt
pic.Set3DBevelTop(DocumentFormat.OpenXml.Drawing.BevelPresetValues.ArtDeco, 3, 4);
// extrusion colour transparency 0%, extrusion (or depth) height 15 pt
pic.Set3DExtrusion(System.Drawing.Color.Green, 0, 15);
// contour colour transparency 40%, contour width 4pt
pic.Set3DContour(DocumentFormat.OpenXml.Drawing.SchemeColorValues.Accent3, 40, 4);
pic.Set3DMaterialType(DocumentFormat.OpenXml.Drawing.PresetMaterialTypeValues.TranslucentPowder);
// 5 pt above "ground"
pic.Set3DZDistance(5);
// field of view 105 degrees, zoom 100%
// camera latitude, longitude, revolution in degrees (50, 40, 30)
// light rig latitude, longitude, revolution in degrees (0, 0, 30)
pic.Set3DScene(DocumentFormat.OpenXml.Drawing.PresetCameraValues.PerspectiveFront, 105, 100, 50, 40, 30, DocumentFormat.OpenXml.Drawing.LightRigValues.Sunrise, DocumentFormat.OpenXml.Drawing.LightRigDirectionValues.TopLeft, 0, 0, 30);
sl.InsertPicture(pic);

sl.SaveAs("Pictures.xlsx");

I use the enumerations available in Open XML SDK as far as possible. This means you can sort of port over any programs you have written using Open XML SDK. And also that I don’t have to invent my own enumerations that are essentially copies of the SDK enumerations.

One thing that confused me was the 3D rotation options in Excel.

SpreadsheetLight 3D picture rotation options

Excel used the vertical “axis” as the X value, and the horizontal “axis” as the Y value. To the user, the X, Y and Z values are like the first, second and third values of … something.

To me, X, Y and Z have special meanings, particularly when applied to the context of 3D. Those values don’t even increase in a consistent manner. The X value decreases if you use the right-hand rule (the left button), but the Y and Z values increase when you use the right-hand rule (left and right buttons respectively).

The Open XML SDK then complicates matters by using the terms latitude, longitude and revolution. Each ranging from 0 degrees to 359.9 degrees (or strictly less than 360 degrees in other words).

And in case you’re wondering about the state of the spreadsheet library progress, I froze the feature set of SpreadsheetLight for version 1. Otherwise, I’d never get it shipped. I’m now documenting the hashbrown out of the source code. The point is to make it ridiculously easy for a programmer to pick up SpreadsheetLight and start using it (without having to refer to a manual, which I’ll also be writing).