Formula Editor Panel

Long and complex formulas are difficult to view and edit in a narrow formula bar or a single cell box. Therefore, the formula editor enables you to display a long and complex formula in multiple lines with indentation for easy reading and editing.

SpreadJS provides this functionality as a plugin that depends on the gc.spread.sheets.formulapanel.x.x.x.min.js script file. Hence, you need to include this script file in your application to work with the formula editor panel. For the workbook to work synchronously with the formula editor panel, you need to bind them together using the attach() method.

The following code sample binds the workbook to the formula editor panel.

let workbook = new GC.Spread.Sheets.Workbook("spread-host");
let formulaEditor = new GC.Spread.Sheets.FormulaPanel.FormulaEditor("formula-editor-host");
formulaEditor.options.formatWidthLimit = -1;
formulaEditor.options.tabSize = 4;
formulaEditor.attach(workbook);
......
<!--DOM element-->
<div id="spread-host" style="width:100%; height:250px;border: 1px solid gray;"></div>
<div id="formula-editor-host" style="width:100%; height:300px;border: 1px solid gray;"></div>

The formula editor provides the formatWidthLimit option to change the formula text width as per the chosen limit. The available values are:

Additionally, the tabSize option is used to insert tab spaces before the formula expressions when the Tab key is pressed.

To start working with the formula editor, select the cell containing the formula you want to edit as shown in the image below.

Key Features

The primary features of a formula editor are mentioned below.

Formatter: Adjusts the formula indentation, spacing, line breaks, etc. to make the formula more readable and easier to maintain.

Auto-completion suggestions: Provides formula auto-completion, function parameter hints, etc. to improve the efficiency of writing formulas. While entering formulas, it intelligently suggests available functions, custom names, enumerated parameters, etc.

Flexible outline view: Allows collapsing and expanding of the formula view in the formula editor.

Syntax highlight: Enhances readability and usability by highlighting different syntax elements in the formula.

Lint option: Provides error prompts such as improper function parameters, mismatched brackets, etc. to reduce errors.

Tooltip information: Shows a tooltip containing details about a function when a user hovers over a function or enters a function.

Theme customization: Provides multiple themes and allows customizing existing themes to change the appearance of the editor.

By modifying the CSS file, you can change the appearance of the formula editor, such as font, tooltip, background, foreground, referenced ranges, and so on.

The following CSS explains how to define the font family and colors of functions, strings, foreground, and background of a formula editor panel.

// Define the color of the function token
.gc-formula-editor-token-function {
 color: blue;
}

// Define the color of the string token
.gc-formula-editor-token-const-string {
  color: red;
}

// Define the color, background-color, and font of the formula editor
.gc-formula-editor-content {
color: black;
background-color: white;
font: 16px, sans-serif;
}

Using Designer

To start working with the formula editor in Spread Designer, you need to select the cell containing the formula you want to edit and click the Show Formula Editor button, available inside the FORMULAS > Formulas Auditing tab group.

The formula editor lets you choose how you want your formula to be displayed on the panel. You can edit the formula view as well as the formula text. The edited formula can be saved by using the Save button. Once saved, SpreadJS automatically synchronizes the editor text to the cell. You can also use the editor.commandManager API to asynchronize it. Additionally, the Format button can be used to get a formatted tree view of the applied formula as well as change the formula width and tab size limit.

The following GIF shows the Formula Editor panel in the SpreadJS designer.

Shortcut Keys

You can use the following shortcut keys while formatting the formulas using the format editor.