Skip to main content Skip to footer

Using Rich Text Formatting in a JavaScript Spreadsheet

In previous versions, rich text in SpreadJS was only possible with embedded HTML. With version 12, the rich text feature of SpreadJS allows developers to add rich text to their application and even provide an editor for their users. In this tutorial, we'll look at a few different ways that rich text can be used.

Download the sample zip for this tutorial.

Set up the JavaScript spreadsheet in SpreadJS 12

We'll start by adding the script and CSS references and initializing the Spread instance:

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <meta charset="utf-8" />
    <title>SJS Rich Text Examples</title>

    <link href="http://cdn.grapecity.com/spreadjs/hosted/css/gc.spread.sheets.excel2013white.12.0.0.css" rel="stylesheet" type="text/css" />
    <script type="text/javascript" src="http://cdn.grapecity.com/spreadjs/hosted/scripts/gc.spread.sheets.all.12.0.0.min.js"></script>

    <script>
        window.onload = function () {

            var spread = new GC.Spread.Sheets.Workbook(document.getElementById("spreadSheet"), { sheetCount: 1 });
            var activeSheet = spread.getActiveSheet();
        }
    </script>
</head>
<body>
    <div id="spreadSheet" style="width: 825px; height: 800px; border: 1px solid gray"></div>
</body>
</html>

The basics of Rich Text in SpreadJS

Let's look at the basic structure of rich text code.

var richTextVariable = {
    richText: [
        {
            style: {
                font: "font type here",
                (other style properties...)
            },
            text: "richtext text here"
        }
    ]
}

The text is separated into different parts based on the formatting, and is essentially string concatenation. All the text is combined into one variable, which is then applied to the cell:

sheet.setValue(0, 0, richTextVariable, GC.Spread.Sheets.SheetArea.viewport);

In addition, you can define rich text variables inline, when you're setting them in the cell:

sheet.setValue(0, 0, { richText: [{style:{font: '20px Arial'}, text: 'Test'}]}, GC.Spread.Sheets.SheetArea.viewport);

Subscripts and superscripts in Rich Text formatting

Many SpreadJS users depend on scientific and mathematical formulas in their spreadsheets, so the subscripts and superscripts features are especially useful additions to SpreadJS 12. To use superscripts and subscripts, simply set the "vertAlign" style property in the rich text code, with the following definitions:

  • 1 = superscript
  • 2 = subscript

For this particular example, we'll use a superscript in scientific notation:

var scientificNotation = {
    richText: [
        {
            style: {
                font: "24px Calibri"
            },
            text: "2.13"
        },
        {
            style: {
                font: "24px Calibri"
            },
            text: " x "
        },
        {
            style: {
                font: "24px Calibri"
            },
            text: "10"
        },
        {
            style: {
                font: "24px Calibri",
                vertAlign: 1
            },
            text: "3"
        },
        {
            style: {
                font: "24px Calibri"
            },
            text: " = "
        },
        {
            style: {
                font: "24px Calibri"
            },
            text: "2130"
        }
    ]
};
sheet.setValue(0, 0, { richText: [{style:{font: 'bold 20px Arial'}, text: 'Scientific Notation'}]}, GC.Spread.Sheets.SheetArea.viewport);
sheet.setValue(0, 1, scientificNotation, GC.Spread.Sheets.SheetArea.viewport);

This code results in a formatted cell with scientific notation:

Formatted cell with scientific notation

Add bold and italics within a spreadsheet cell

You can also set specific typographical emphasis, like bold and italics, when you're setting the font for the text. To show this off, we can create a financial equation with bold mathematical symbols and italicized variables:

var financialEquation = {
    richText: [
        {
            style: {
                font: "italic 24px Calibri"
            },
            text: "A"
        },
        {
            style: {
                font: "bold 24px Calibri"
            },
            text: " = "
        },
        {
            style: {
                font: "italic 24px Calibri"
            },
            text: "P"
        },
        {
            style: {
                font: "bold 24px Calibri"
            },
            text: "(1 + "
        },
        {
            style: {
                font: "italic 24px Calibri"
            },
            text: "r"
        },
        {
            style: {
                font: "bold 24px Calibri"
            },
            text: "/"
        },
        {
            style: {
                font: "italic 24px Calibri"
            },
            text: "n"
        },
        {
            style: {
                font: "bold 24px Calibri"
            },
            text: ")"
        },
        {
            style: {
                font: "italic 24px Calibri",
                vertAlign: 1
            },
            text: "nt"
        }
    ]
};
sheet.setValue(1, 0, { richText: [{style:{font: 'bold 20px Arial'}, text: 'Financial Equation'}]}, GC.Spread.Sheets.SheetArea.viewport);
sheet.setValue(1, 1, financialEquation, GC.Spread.Sheets.SheetArea.viewport);

This results in the following rich text:

Bold and italics rich text formatting

Add multiple colors in a single spreadsheet cell

Rich Text also features different colors for different parts of the text in a cell. These can be set via the "foreColor" style property in the richtext definition. To show this off, we can implement a chemistry equation with different colors for the different elements:

var chemistryEquation = {
    richText: [
        {
            style: {
                font: "24px Calibri",
                foreColor: "rgb(0, 0, 153)"
            },
            text: "C"
        },
        {
            style: {
                font: "24px Calibri",
                vertAlign: 2
            },
            text: "3"
        },
        {
            style: {
                font: "24px Calibri",
                foreColor: "rgb(255, 0, 0)"
            },
            text: "H"
        },
        {
            style: {
                font: "24px Calibri",
                vertAlign: 2
            },
            text: "8"
        },
        {
            style: {
                font: "24px Calibri"
            },
            text: "+"
        },
        {
            style: {
                font: "24px Calibri",
                foreColor: "rgb(0, 102, 0)"
            },
            text: "O"
        },
        {
            style: {
                font: "24px Calibri",
                vertAlign: 2
            },
            text: "2"
        },
        {
            style: {
                font: "bold 24px Calibri"
            },
            text: "→"
        },
        {
            style: {
                font: "24px Calibri"
            },
            text: "4"
        },
        {
            style: {
                font: "24px Calibri",
                foreColor: "rgb(255, 0, 0)"
            },
            text: "H"
        },
        {
            style: {
                font: "24px Calibri",
                vertAlign: 2
            },
            text: "2"
        },
        {
            style: {
                font: "24px Calibri",
                foreColor: "rgb(0, 102, 0)"
            },
            text: "O"
        },
        {
            style: {
                font: "24px Calibri"
            },
            text: "+3"
        },
        {
            style: {
                font: "24px Calibri",
                foreColor: "rgb(0, 0, 153)"
            },
            text: "C"
        },
        {
            style: {
                font: "24px Calibri",
                foreColor: "rgb(0, 102, 0)"
            },
            text: "O"
        },
        {
            style: {
                font: "24px Calibri",
                vertAlign: 2
            },
            text: "2"
        }
    ]
};
sheet.setValue(2, 0, { richText: [{style:{font: 'bold 20px Arial'}, text: 'Chemistry Equation'}]}, GC.Spread.Sheets.SheetArea.viewport);
            sheet.setValue(2, 1, chemistryEquation, GC.Spread.Sheets.SheetArea.viewport);

Here's how the code renders:

Use different colors in a single spreadsheet cell

Add different font sizes and singles in a single cell

Rich Text in SpreadJS supports different font sizes and symbols combined together in a single cell. The font size can be set individually in the font property of the Rich text code. To show this off, we can create a mathematical equation:

var scientificEquation = {
    richText: [
        {
            style: {
                font: "28px Times New Roman"
            },
            text: "["
        },
        {
            style: {
                font: "italic 24px Times New Roman"
            },
            text: "iћA"
        },
        {
            style: {
                font: "italic 24px Times New Roman",
                vertAlign: 1
            },
            text: "μ"
        },
        {
            style: {
                font: "italic 24px Times New Roman"
            },
            text: "γ"
        },
        {
            style: {
                font: "italic 24px Times New Roman",
                vertAlign: 1
            },
            text: "μ"
        },
        {
            style: {
                font: "italic 24px Times New Roman",
                vertAlign: 2
            },
            text: "(a)"
        },
        {
            style: {
                font: "italic 24px Times New Roman"
            },
            text: "∂"
        },
        {
            style: {
                font: "italic 24px Times New Roman",
                vertAlign: 2
            },
            text: "μ"
        },
        {
            style: {
                font: "24px Times New Roman"
            },
            text: " - "
        },
        {
            style: {
                font: "italic 24px Times New Roman"
            },
            text: "m"
        },
        {
            style: {
                font: "24px Calibri",
                vertAlign: 2
            },
            text: "0"
        },
        {
            style: {
                font: "italic 24px Times New Roman"
            },
            text: "c"
        },
        {
            style: {
                font: "28px Times New Roman"
            },
            text: "]"
        },
        {
            style: {
                font: "italic 24px Times New Roman"
            },
            text: "ψ"
        },
        {
            style: {
                font: "24px Times New Roman"
            },
            text: " = 0"
        }
    ]
};
sheet.setValue(3, 0, { richText: [{style:{font: 'bold 20px Arial'}, text: 'Scientific Equation'}]}, GC.Spread.Sheets.SheetArea.viewport);
sheet.setValue(3, 1, scientificEquation, GC.Spread.Sheets.SheetArea.viewport);

Use different font sizes and symbols in a single spreadsheet cell

These are just a few examples of what can be done with Rich Text in SpreadJS, and we're working on adding even more functionality. Without any external dependencies, SpreadJS RichText allows all kinds of formatted text to be combined together in one cell.

Kevin Ashley - Spread Product Manager

Kevin Ashley

Product Manager
comments powered by Disqus