Embedding

You can embed a spreadsheet in a webpage or blog with a single script tag. When it runs, in will insert the spreadsheet and add any required dependencies.

Basic Embedding

Here’s the simplest possible HTML page that loads a spreadsheet:

<html>
  <body>
    <script src="https://treb.app/treb/current/embed.js" data-treb></script>
  </body>
</html>

Open this example page in your browser

That will work in all browsers down to IE11. We try to keep the core library a reasonable size (it’s about 160K gzipped) so some functions are moved into support libraries that are loaded on demand. If you are running from your own webserver, as long as the files are in the same directory they’ll get loaded as needed.

Other ways to embed

The script above is a loader which adds the actual script tags to the document, adds a div element for the spreadsheet, and then inserts the markup. If you want a little more control you can add the script files manually, and then either use div tags to insert spreadsheets or use the API.

<html>
  <head>

    <!-- treb script files -->
    <script defer type="text/javascript" nomodule 
            src="https://treb.app/treb/current/treb-bundle-es5.js"></script>
    <script defer type="module" 
            src="https://treb.app/treb/current/treb-bundle.js"></script>

  </head>
  <body>

    <!-- insert the spreadsheet in this div tag -->
    <div data-treb></div>

  </body>

</html>

Open this example page in your browser

Note that there are two script files, one for IE11 and one for modern browsers. Any modern browser will just ignore the IE11 version (that’s what the nomodule attribute is for).

Loading a document

TREB has a native document format, using JSON. It’s compact and efficient (actually it’s somewhat verbose, but it’s designed to compress well). TREB can also read CSV, TSV and XLSX files.

We recommend using JSON files if possible. CSV/TSV files don’t retain style information, or support multiple sheets, and XLSX files are huge and slow to read. It’s easy to convert existing XLSX or CSV/TSV files to our native format, so we suggest that you do that and serve JSON files. But you can point to any file you like.

To load a file in the embedded spreadsheet, you pass the file as a value for the data-treb attrribute. So for example:

<div data-treb='https://treb.app/doc/sample.treb.json'></div>

This will create a TREB component and load the linked file:

Layout & styling

Use standard CSS or inline styles on the containing element (the element that has the data-treb attribute) to set a size for the embedded spreadsheet. If you don’t, it will use a default size.

You can style the spreadsheet with CSS; see the gallery page for more. But note that because the spreadsheet cells are painted, they won’t automatically update to changes in CSS. You should call the UpdateTheme API method any time you modify the stylesheet to ensure the spreadsheet is updated.

Configuration

To configure the spreadsheet, add a data-options attribute to the tag with the options you want. For example,

<div data-treb='https://treb.app/doc/sample.treb.json' data-options='toolbar,scroll=C2'></div>

Compared to the previous example, this one adds the toolbar button to the sidebar and scrolls the document to C2:

Separate embedded spreadsheets on the same web page are independent.

Options

Mulitple options can be passed as a data-options argument separated by commas. Configuration options can be boolean (true or false) or take a value.

To set an option to true, it’s sufficient to just pass the option, so toolbar is the same as toolbar=true. To set another value, pass the value as toolbar=false, tab_bar=auto or scale=1.1.

Available options include:

OptionDefaultDescription
dndfalseAllow drag-and-drop of supported files (XLSX, CSV, TSV or treb files)
expandfalseAllow expanding the spreadsheet. By default, the spreadsheet retains its size (number of rows and columns)
storage_keySave changes to browser localStorage, using the given name. If the name exists in localStorage, the modified document will be loaded instead of the original.
formula_bartrueShow the formula bar
expand_formula_buttonfalseShow the "expand formula" button (multiple rows)
scrollScroll to the given address when the document is loaded
sheetOpen the named sheet (tab) when the document is loaded
resizabletrueAllow resizing the spreadsheet, and add the resize handle
exporttrueShow the "download as XLSX" button allowing export of the document
popouttrueShow the "open in new tab" button
headerstrueShow row/column headers
recalculatefalseRecalculate on load. By default, only volatile functions (like DATE and NOW) will update on load.
scrollbarstrueShow document scrollbars. If scrollbars are disabled, the document cannot be scrolled
tab_bar"auto"Show the tab bar (pages or sheets). Setting to `auto` (the default) will show the tab bar if there are multiple sheets and hide it otherwise.
add_tabfalseAllow adding sheets; sheets can be added either via the structure menu (in the toolbar) or via the add sheet button in the tab bar.
delete_tabfalseShow the delete tab button in the tab bar
collapsedfalseStart with the sidebar hidden
global_nameCreate a global variable for the spreadsheet (like on the front page of this website). This can be handy for debugging.
toolbarfalseShow the toolbar button. Set to "show" to show the toolbar on load
scale1.0Set the view scale
scale_controlfalseShow the scale buttons. This will also show the tab bar.
persist_scalefalseIf a website visitor changes the spreadsheet scale, this option will store the new scale in localStorage.

Fonts & Font Styling

As a general matter, we think using different fonts and different font sizes in a spreadsheet can be distracting. So we discourage it. That’s just our bias. You can actually set fonts and font sizes, there’s just no toolbar button for it. You can change fonts using the API, or using CSS.

CSS and Cell Styles

There are two layers of styling in TREB: themes and cell styles. Themes are CSS applied to the whole document. Cell styles are specific to a cell or range of cells. Themes are set by the host (the containing document); cell styles are specified in the spreadsheet itself.

In the absence of a cell style, the theme controls. So there are default fonts and font sizes in the theme, and these apply to every cell. If the CSS changes, then every cell will change. CSS is external to any document, so if you open the same spreadsheet on web pages with different CSS, it may look different.

Cell styles override the theme and persist with the document. So if you open a file that has cell styles, it should (theoretically) look the same on any web page.

The great thing about CSS is that we can define different defaults for different environments. We pick a couple of basic fonts that look good on windows, mac, and (some distros of) linux; then we can generally assume any file will look good on any website, in any browser.

If you set a specific font in a cell style or CSS and it’s not available on a particular platform, you may get ugly text as the browser defaults to Arial or Times or something.

How do I Set Cell Styles?

There are three properties relevant to fonts: font_face, font_size.unit and font_size.value. Font face can have multiple values, like in CSS, to provide fallbacks or fonts for different platforms.

You can use pt or px for the font size, but we recommend using em or % which are relative to the base cell font size. This is useful in case there are different font sizes for different platforms.

sheet.ApplyStyle('B2:G7', {
  font_face: 'Comic Sans MS',
  font_size: {
    value: 1.5,
    unit: 'em',
  },
});

The first parameter to ApplyStyle is a range. Set this to undefined and it will apply to the active selection.

This example sets font sizes (and colors) in cell styles, but leaves the font face to the theme:

Can I Use Web Fonts?

The best way to use web fonts would be to use the font loading API to ensure your fonts are loaded, and then create the spreadsheet on the page after fonts are ready.

If you create the spreadsheet before fonts are loaded, and you use those fonts in either CSS or cell styles, you’ll probably see the spreadsheet render with fallback fonts (whatever is set in the browser), and it won’t update automatically.

You can update when fonts are loaded by reloading the theme, with

sheet.UpdateTheme();

IE11 doesn’t support the font loading API, so if you need to support that you’ll have to resort to a workaround (they do exist).


This is "Caveat" from Google fonts (set in CSS):

/* apply to spreadsheet UI */
.treb-main.treb-theme {
  font-family: 'Caveat', cursive;
  font-size: 16pt;
}

/* apply to spreadsheet cells */
.treb-main.treb-theme .grid-cells {
  font-family: inherit;
  font-size: 14pt;
}

We’re not saying you should do this, just that you can.

What About Font Features?

Font features are not supported, unfortunately. That’s because we paint the spreadsheet, rather than using layout, and HTML canvas (as of 2021) does not support most font features.


Complex Numbers

Starting in version 11, TREB supports complex numbers as an intrinsic type. You can use complex numbers anywhere you would use real numbers. You can type them in cells and use them as arguments to functions. See below for a list of functions that work with complex numbers.

Complex number support is very much a work in progress. If you have questions, comments, or feature requests, please let us know.

Parsing

When entering a complex number, you must use the token i (lower-case ASCII “i”, unicode U+0069) for the imaginary component. The token i on its own implies a value of 1i. The magnitude must precede the token, for example 3i or 1.2i.

When entering a formula, complex numbers are not automatically treated as atomic units. Normal precendence rules apply, so

=B3 * 2-3i

is the equivalent of

=(B3 * 2) - 3i

You can of course use parentheses to group them explicitly.

=B3 * (2 - 3i)

Rendering and Formatting

When displaying complex numbers, TREB uses an italic character 𝑖 (mathematical italic small i, unicode U+1D456). We believe this character is available in the default fonts on all platforms we support. If necessary, you can change this character using a run-time option.

Generally speaking we try to render complex numbers in the simplest possible way.

If a complex value has no imaginary value (or if it would render as 0 in the current number format), when displaying the value we will omit the imaginary component. The same goes for the real component, if there is an imaginary component. So complex numbers may render as

  • 3.2
  • 2𝑖
  • 0.00

Number formatting is not well defined for complex numbers. At the moment, we use the currently selected number format and apply it to both the real and imaginary components.

We recommend that you use only basic number formats like “General” or “Number”. Fractional number formats also work well. Increasing or decreasing the decimal (or fractional) precision works as expected.

TREB only displays complex numbers in rectangular form, but you can use the Abs and Arg functions to get components for the polar form.

Import & Export

We don’t (for now) supporing importing or exporting complex numbers in XLSX files. The XLSX format doesn’t have a complex type. Excel has a number of functions that work with complex numbers, but the actual values they use are strings.

For the time being, we’re just punting on this issue, but we are open to suggestions.

Functions and Operators

The following functions and operators support complex numbers:

Unary operators

  • - (negation)

Binary operators

  • + (addition)
  • - (subtraction)
  • / (division)
  • * (multiplication)
  • ^ (exponentiation) (see note)

Equalities

  • = (equality)
  • <> (inequality)

Functions specifically written for complex numbers

  • ComplexLog - the complex Log function
  • Arg - returns the principal argument of a complex number
  • Real - returns the real component of a complex number
  • Imaginary - returns the coefficient of the imaginary component
  • Conjugate - returns the complex conjugate
  • Complex - promotes real values to the complex domain (see note)

Functions updated to support complex arguments

  • Exp
  • Abs
  • Sum
  • MMult
  • MInverse
  • MDeterm
  • Power (see note)
  • Sqrt (see note)

Note: the SQRT function return NaN for some operations on real values, like SQRT(-1). The same is true for the POWER function and the exponentiation operator ^.

We don’t want to change existing behavior, so we are leaving this as-is for real values. If the argument to SQRT is complex, e.g. SQRT(-1 + 0i) it will return the complex result 𝑖. If the argument is real, however, SQRT(-1) will return NaN.

Our reasoning here is twofold: first, we do not want to change existing behavior or expectations. Second, we do not want functions to accidentally introduce complex numbers into models that expect real values only.

This presents a problem for values returned from functions which may be in either real or complex domain. To resolve this, and create a pattern for consistent behavior, we provide the function COMPLEX. This function coerces real values to the complex domain.

  • =SQRT(-1) returns NaN
  • =SQRT(-1 + 0i) returns 𝑖
  • =SQRT(COMPLEX(-1)) returns 𝑖

The function REAL can be used for the converse, i.e. ensuring a number is treated as real, although this may lose information if a number has an imaginary compnent.


Programming Interface

Getting Started

The main script adds a TREB object to the global namespace. This object has one method, CreateSpreadsheet. You can use this method to create spreadsheet instances in your web document.

CreateSpreadsheet takes an options parameter. The most important option is the HTML node where you want to insert your spreadsheet. If you have a basic webpage that looks like this

<html>
  <head>

    <!-- treb script files -->
    <script defer type="text/javascript" nomodule 
            src="https://treb.app/treb/current/treb-bundle-es5.js"></script>
    <script defer type="module" 
            src="https://treb.app/treb/current/treb-bundle.js"></script>

  </head>
  <body>

    <!-- we will use script to insert the spreadsheet in this div -->
    <div class="embedded-spreadsheet"></div>

  </body>

</html>

You can create a spreadsheet with script like this:


<script>

// run this when the DOM is complete
document.addEventListener("DOMContentLoaded", () => {

  // get the node
  const container = document.querySelector('.embedded-spreadsheet');

  // call CreateSpreadsheet with the container node
  TREB.CreateSpreadsheet({ container });

});

</script>

For more information, see the API page for Create Spreadsheet.

API Reference

The API reference is available here. This is a work in progress, so please let us know if you have any questions or if anything isn’t clear.

If you use typescript (or an editor that supports it), there’s a typings file included in the download.