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);
font.SetFontThemeColor(SLThemeColorIndexValues.Accent2Color);
rst = new SLRstType();
rst.AppendText("But seriously...", font);
sl.SetCellValue(4, 2, rst.ToInlineString());

font = new SLFont();
font.SetFont(FontSchemeValues.Minor, 15);
font.SetFontThemeColor(SLThemeColorIndexValues.Accent1Color);
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;
font.SetFontThemeColor(SLThemeColorIndexValues.Accent3Color);
rst.AppendText("so ", font);

rst.AppendText("much...");

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

SLStyle style = new SLStyle();
style.Fill.SetPatternForegroundColor(SLThemeColorIndexValues.Accent1Color);
style.Fill.SetPatternType(PatternValues.Solid);
sl.SetCellStyle(7, 2, style);
sl.SetCellValue(7, 3, "Accent 1");

style = new SLStyle();
style.Fill.SetPatternForegroundColor(SLThemeColorIndexValues.Accent2Color);
style.Fill.SetPatternType(PatternValues.Solid);
sl.SetCellStyle(8, 2, style);
sl.SetCellValue(8, 3, "Accent 2");

style = new SLStyle();
style.Fill.SetPatternForegroundColor(SLThemeColorIndexValues.Accent3Color);
style.Fill.SetPatternType(PatternValues.Solid);
sl.SetCellStyle(9, 2, style);
sl.SetCellValue(9, 3, "Accent 3");

style = new SLStyle();
style.Fill.SetPatternForegroundColor(SLThemeColorIndexValues.Accent4Color);
style.Fill.SetPatternType(PatternValues.Solid);
sl.SetCellStyle(10, 2, style);
sl.SetCellValue(10, 3, "Accent 4");

style = new SLStyle();
style.Fill.SetPatternForegroundColor(SLThemeColorIndexValues.Accent5Color);
style.Fill.SetPatternType(PatternValues.Solid);
sl.SetCellStyle(11, 2, style);
sl.SetCellValue(11, 3, "Accent 5");

style = new SLStyle();
style.Fill.SetPatternForegroundColor(SLThemeColorIndexValues.Accent6Color);
style.Fill.SetPatternType(PatternValues.Solid);
sl.SetCellStyle(12, 2, style);
sl.SetCellValue(12, 3, "Accent 6");

sl.SaveAs("InlineString.xlsx");

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.