OpenXML Spreadsheet Boot Camp

Special Offer: There’s an Open XML bundle package that includes everything at a discounted price! Get it here.

Deconstructing Excel for programmers

OpenXML Spreadsheet Boot Camp (henceforth “OSBC”) is a training course teaching you how to get from a given Excel spreadsheet to code that generates that spreadsheet. As a programmer, you may be given Excel files and the user requirement is “I want my Excel file to look like this one!”. The goal of this course is to teach you how to reproduce that given Excel file.

This course teaches you how to discover the styles and document properties that the user never tells you but expects you to know anyway. Then you learn how to break all that you’ve learnt into actionable tasks. Finally, you learn how to write the code that will generate that Excel file.

While you can decompile an Open XML Excel spreadsheet into code using the Productivity Tool (part of the Open XML SDK), you get reams of code that you still have to wade through. And you might not know which parts of the code correspond to the respective Excel properties. This course teaches you how to visually inspect an Excel file to get that colour setting, that font setting, without going through XML files or code (as far as possible).

Your user used Excel to create that file. You will use Excel to get those internal settings too.

Who should take this course

Anyone who needs to reproduce a given Open XML Excel spreadsheet. However, anyone who wants a deeper understanding of how Excel works will find this course useful too. Half of the course is on dissecting Excel behaviour, so that’s going to be fun.

But you get the most out of this when you can see that this colour on this colour picker of the dialog box in Excel translates to this particular setting in code.

Course Curriculum

The course will have 8 lessons. This is in addition to what you get from the reference textbook, “Spreadsheet Open XML From Scratch“. Oh the textbook isn’t compulsory, but you will find it useful for learning the nuances of Open XML.

You get all 8 lessons at once, although they are designed at a pace of 1 lesson per week. This means you can go at your own pace.

Lesson 1 – Identifying Cell Values

You learn how to recognise the different types of data an Excel cell can accept. If you don’t know what kind of data there is, then you won’t know to look for it.

Lesson 2 – Brute Force Theme Creation

A lot of what a typical Excel user touch on is about styles. And much of Excel styles depend on the theme. In this lesson, you learn how to create a theme and how to recognise if styles depend on themes.

Lesson 3 – Recognising Styles

You will learn how to recognise (and get settings of):

  • Number format codes
  • Cell background fill colours (did you know they’re internally foreground fills?)
  • Font, font sizes, font colours and other settings (bold, italics and strikes galore)
  • Borders
  • Text alignment

This is probably the bulk of your work.

Lesson 4 – Formatting Styles

This lesson builds on lesson 3. Previously you learnt to recognise them. Now you implement them.

Lesson 5 – Empty Cells And Styles

Cells contain values, formulas and styles. Just because it’s empty, doesn’t mean it can’t have style. Even entire rows and columns can be styled.

Lesson 6 – Merged Cells And Borders

We focus specifically on merged cells in this lesson. How do you get a proper border surrounding some merged cells? Learn that here.

Lesson 7 – Inline/Shared Strings

Where text formatting got a whole lot more complicated… In this lesson, you learn about rich text, and how to recognise and use it.

Lesson 8 – Pages, Printing And Protection

In the last lesson, you learn about those page margins, print settings and document protection settings. Tried typing in a cell but no go? It’s protected.

How much is it?

The whole course is USD 10.

Get the whole course here!

And if you haven’t already gotten the super-delicious Open XML Bundle, check it out here! You get the boot-camp course and 2 other Open XML reference manuals.

That’s all and enjoy the course!

Vincent Tan