Rich strings and inline strings in spreadsheets

Quite a while ago, I was mucking around in Excel and I discovered you can set the text in a cell to different fonts! Even different colours! (Ok, you’re probably bored of me going on about spreadsheets and Open XML, but it’s all I’m thinking about right now…) Granted, it’s a limited set of font style manipulations, but I’ve always thought the text in a cell was completely subjected to the cell style. I never thought you could change anything within a cell. I’m not an expert Excel user, ok?

The term used is an “inline string”. At least that’s what it’s referred to in the Open XML SDK, as the InlineString class.

This gave me a problem. How do I implement this in my spreadsheet library? It’s not as easy as just setting a cell value. You’d have to set up all the fonts and colours and bolds and italics and underlines, and then dump that bunch of stuff into a cell.

If you do it by hand, you’ll run (haha, foretelling a pun) into the DocumentFormat.OpenXml.Spreadsheet.Run class. Basically, you’re appending style runs. Here’s how you do it in Excel:

Inline string

You select the text in the formula box (not within the cell). Then you apply any font styles you want.

Aaannd… here’s where I tell you how my spreadsheet library is going to make your life easier. Here’s a sample screenshot of a result:

SpreadsheetLight inline string

Let’s look at the source code to generate that.

SLDocument sl = new SLDocument(SLThemeTypeValues.Metro);

SLFont font;
SLRstType rst;

font = new SLFont();
font.FontColor = System.Drawing.Color.Red;
rst = new SLRstType();
rst.AppendText("Roses are ");
rst.AppendText("red", font);
sl.SetCellValue(2, 2, rst.ToInlineString());

font = new SLFont();
font.FontColor = System.Drawing.Color.Blue;
rst = new SLRstType();
rst.AppendText("And violets are ");
rst.AppendText("blue", font);
sl.SetCellValue(3, 2, rst.ToInlineString());

font = new SLFont();
font.Bold = true;
font.Italic = true;
font.Underline = UnderlineValues.Double;
font.SetFont(FontSchemeValues.Major, 11);
rst = new SLRstType();
rst.AppendText("But seriously...", font);
sl.SetCellValue(4, 2, rst.ToInlineString());

font = new SLFont();
font.SetFont(FontSchemeValues.Minor, 15);
rst = new SLRstType();
rst.AppendText("you don't ", font);
font = new SLFont();
font.Italic = true;
rst.AppendText("have ", font);

rst.AppendText("to ");

font = new SLFont();
font.Underline = UnderlineValues.Single;
font.FontColor = System.Drawing.Color.OrangeRed;
rst.AppendText("emphasise ", font);

rst.AppendText("it ");

font = new SLFont();
font.Bold = true;
rst.AppendText("so ", font);


sl.SetCellValue(5, 2, rst.ToInlineString());

SLStyle style = new SLStyle();
sl.SetCellStyle(7, 2, style);
sl.SetCellValue(7, 3, "Accent 1");

style = new SLStyle();
sl.SetCellStyle(8, 2, style);
sl.SetCellValue(8, 3, "Accent 2");

style = new SLStyle();
sl.SetCellStyle(9, 2, style);
sl.SetCellValue(9, 3, "Accent 3");

style = new SLStyle();
sl.SetCellStyle(10, 2, style);
sl.SetCellValue(10, 3, "Accent 4");

style = new SLStyle();
sl.SetCellStyle(11, 2, style);
sl.SetCellValue(11, 3, "Accent 5");

style = new SLStyle();
sl.SetCellStyle(12, 2, style);
sl.SetCellValue(12, 3, "Accent 6");


I’m using the Metro theme, which means the major Latin font is Consolas, and the minor Latin font is Corbel. The body text is in minor Latin font.

You’ll notice the 2 new classes, SLFont and SLRstType classes. The SLRstType models after the Open XML SDK (abstract) class RstType. I think it stands for “rich string type” (r + st + type).

I have filled in 6 cells with the accent colours, just so you can see how the colours are used. The accent colours are tied to the theme used, as is the major and minor Latin fonts. So if you use these colours and fonts, the text is automatically formatted against the current theme.

This is an advantage if you set the font as the minor Latin font, instead of directly as “Corbel”. If the user changes the theme of your resulting spreadsheet, the text changes to the new theme’s minor Latin font. Of course, if you want the text to stay as “Corbel”, regardless of the theme, then set it directly and explicitly as “Corbel”. The SLFont class has overloaded functions for this.

P.S. Can you tell I’m excited about this? I’m going to launch this baby. Soon. I will limit such “promotional” articles, but I really think Excel gives me surprises, so I thought you might want to know what your user thinks is normal Excel activity.

Magazine publishing part 1 – Fonts

With a few issues of my magazine published already, I feel confident enough to write about the process. In my free ebook (which you can get by subscribing to Singularity. Sign up at the blog), I told you to use only 2 different fonts in your magazine, 3 at most. The truth is, you can use as many fonts as you like. It’s your magazine. However, I told you to select only 2 or 3 fonts because I don’t want you to be paralysed by choice. I’m going to teach you more about font selection here.

4 broad categories of fonts

Fonts can generally be categorised into the following:

  • Serif fonts
  • Sans serif fonts
  • Fixed width fonts
  • Fancy fonts

Serif fonts have decorative features on the characters. “Times New Roman”, “Georgia” and “Cambria” are serif fonts.

Sans serif fonts don’t have those decorative features (“sans” means “without”). “Arial”, “Helvetica” and “Calibri” are sans serif fonts.

Fixed width fonts have, well, fixed widths for each character. This is especially useful to programmers because it makes code easier to read. “Courier New”, “Lucida Console” and “Consolas” are fixed width fonts.

Fancy fonts are designed for decorative purposes. “Chiller”, “Jokerman” and “Vivaldi” are fancy fonts.

Fancy font examples

Design for contrast

When designing for a web page, the conventional advice is to use a serif font for headings and a sans serif font for text. This is because sans serif fonts are easier to read on the screen, because they lack the decorative features which can crowd the pixels and make it hard to read. A serif font is used for the headings to provide contrast.

In physical print, it’s the opposite. That’s because a newspaper or a book has infinite resolution, so serif fonts work well. Those decorative features make it easier for a reader to quickly identify characters and thus it’s easier to read.

As for fonts in an online magazine, it’s meant to be read on the screen, so in general, design for that. However, a magazine is not a web page. Be creative. Use whatever font you feel suits the content. Use a font for emphasis, for decoration, for subtlety, for telling your story.

Size matters

I look at a computer screen a lot. So I appreciate larger font sizes so I don’t have to squint. For this reason, I decided on larger font sizes wherever it made sense. This might make even more sense because mobile devices can also read my magazine. Designing a magazine for mobile devices is a separate article altogether, since we might have to design for dynamic text and image flow while still retaining the look of the original article in the magazine (or even the magazine itself).

But feel free to experiment on font sizes for other types of texts. Blow up heading text for emphasis. Enlarge pull quotes. Dwarf answers to quizzes. Write fine print. Design for the screen, but deviate for design.

The fonts I currently use

I use “Perpetua” for the Singularity title on the cover page. Can’t remember which book I saw this on, but the font used was on the copyright page of the book (yes, I read that page too. I’m curious, ok?). Sometimes, publishers print the font in which the text is set on. I tried “Perpetua” with the text “Singularity”, and it looked great. Stately with decorum, yet not too formal. So that stuck.

Magazine text was originally set in “Calibri”, the default font in Microsoft Word (I’ll tell you about the tools I use another time). Now I use “Corbel” because it renders the text better for easier reading (to me at least).

On the front cover, I use “Candara” for supporting text. No special reason other than I wanted to try a different sans serif font than the magazine text font. It also looked great, so that stuck as well.

Here are some interesting fonts during my experiments:
Font suggestions

  • Castellar – Automatically in small caps. Great for decorative text
  • Copperplate Gothic – Also in small caps. Reminds me of Gotham City…
  • Edwardian Script – Alternative to the Vivaldi script
  • Elephant – If you want emphasis, get one of the largest land animals as a mascot
  • Harrington – Reminds me of book stores…
  • Palatino Linotype – Good-looking serif font. Consider using it for headings
  • Papyrus – For when you need writing that looks like it was written on old paper
  • Rockwell – For a feeling of solidarity

Ok, that’s it. If there’s anything you want to know, please comment.