SpreadJS v13.1 Release

SpreadJS v13.1 is available. This release offers several great new features including:

• Hyperlinks
• Format String Enhancements
• Dirty Cell States
• Custom Sorting
• Percentage Format Enhancement
• Ellipsis and Tips for Cells
• Inserting Copied/Cut Cells
• Multi-Select ComboBox
• Enable/Disable Table Auto Expand
• Chart Enhancements
  • Hidden/Empty Cells/Rows/Columns
  • Display Not A Number as Blank
  • Series Line Types

Enhanced Hyperlink Support

From popular customer demand, we have improved our Excel-like advanced hyperlinks! SpreadJS will now support keeping hyperlinks in imported Excel files intact. Users can create hyperlinks with SpreadJS and export those successfully to Excel. We added extensive API to manage the different properties of those hyperlinks, including getters, setters, and hyperlink types.

Also, new functions were added to allow hyperlinks to be created automatically and a command for opening the URL of the hyperlink. Hyperlinks can be managed with the SpreadJS Designer dialog and the context menu at runtime.

There are a few different places that hyperlinks can point to, which include:

• A specific location in the Spread workbook
• An email address
• A URL for a website that opens in a new tab

Additionally, hyperlinks in SpreadJS can be connected to a particular behavior so that when the hyperlink is clicked, it can run custom commands that the developer defines.

Read more about hyperlinks | Hyperlink demos

Format Strings

Format strings provide a way for text to be formatted in cells automatically to developer specifications. With our new enhancements, developers will have more control over how they set formats, including formulas and values, at the same time! In previous versions, if a developer wanted to format a cell with a formula in it, the formatted cell would have to reference the cell with the formula result in it, rather than combining them. With the enhancement, formulas can be set as formatting and applied to different cells.

Text value templates eliminate problems that can arise when trying to create dashboards and summarizing data, needing hidden rows, columns, and sheets just to format data.

Essentially a cell that would normally just have data in it could now be formatted with other text. As an example, a cell that would normally have a SUM formula for a range of cells can now be combined with a day value and other text:

This would evaluate as: "On Tuesday, Total Sales Were: $1035."

Custom functions can also be created with this, such as converting currencies to different countries/regions for international data.

With this enhancement, the current cell can be referenced with the "@" symbol.

Used in conjunction with the formatString API and formulas. One critical use case for this is in creating sparklines with user input:

style.formatter = "On \{{=TEXT(TODAY(),"DDDD")}}, Total Sales Were: $\{{=SUM(A1:A5)}}.";

You can also do other things like:

• Formatting long display strings without concatenation
• Displaying numeric values as words
• Display a KPI Range Template of a value in a dashboard or report

Read more about format strings | Format string demos

Dirty Cell States

We made an addition to our cell state implementation in the form of the dirty cell state. This allows you to use different styles to mark cells that have data that has been changed by the user, which can be useful when needing to track changes in a workbook or processing data after user input. In workbooks that use data from a database, getting only the cells that were changed instead of the whole database can be useful for performance enhancements.

Custom Sorting

In some cases, the standard sorting in SpreadJS is not enough for specific data. As a developer, custom sorting can be created with the API as a custom function and can sort on either the text or values in cells. As an example, custom sorting could be applied to numbers like "1.2.3.4.9", allowing the developer to sort by just the last number. This can work with different languages as well, such as sorting Chinese Pinyin or Strokes.

To create a custom sorting function, a developer will have to write a function that compares values or text using the API that we provide.

Percentage Formatting Enhancement

SpreadJS already supports percentage formats, but we have enhanced the editing to make it more Excel-like. When a percentage cell entered in Excel, a "%" symbol added to the end of the data.

We have enhanced our percentage formatting to match Excel.

Ellipsis and Tips for Cells

Text ellipsis has been added for cells when text can't fit within a cell. This works in many situations, such as indent, vertical text, auto fit, vertically aligned cells, cell padding, underlined cells, and outline columns. Text tips have shown when hovering the cursor over cells with ellipsis to display the full text of those cells.

Insert Copied/Cut Cells

When manipulating large amounts of data in a workbook, it can be useful to copy or cut cells and move them to other cells in a workbook. Typically, a user would have to insert rows/columns first and then copy or cut cells to paste in those cells. With v13.1, users can now do this in one action, with the context menu option Insert Copied Cells... and choose to shift cells to the right or down.

Multi-Select ComboBox

Combo boxes are mostly a drop-down menu cell type in SpreadJS, and with v13.1, we have added the ability for users to select multiple options from the list. For API, this is just a simple boolean property of the ComboBox class called isMultiSelect. Setting this to true will allow multi-select for instances of that cell type. It should be noted that this is a separate cell type from our cell button drop-down menus, which are a part of cell styles.

Enable/Disable Table Auto Expand

With v13, we added auto-expand for tables, so that when users tab at the end of the table, the table automatically expands with a new row. With 13.1, we have added the ability to disable this feature, though it is enabled by default.

Chart Enhancements

Hidden/Empty Cells/Rows/Columns

We have also added the ability for developers to control the display of hidden rows and columns in their charts. We have provided a boolean property that can be set in code known as Chart.ignoreHidden().

If using the SpreadJS Designer, we added a UI option to ignore hidden rows/columns in a chart. With that option enabled, the chart data will only show the data that is visible in the source data range, without showing blank spaces.

Display Not A Number as Blank

In addition to hidden and empty cells, we have added an option to show "#N/A" cells as empty in charts. Primarily, the charts will treat these cells the same way that it treats empty cells.

Series Line Type

Line types have been added for inline series charts, including API as well as Designer options to change the styles of a line:

Other SpreadJS Enhancements

We have added other enhancements with this release as well, including:

• CalcOnDemand for Designer
  • The calculate on-demand option added to Spread settings in the Designer
• Shape API return formula
  • getFormula and setFormula methods added to shapes
• PDF Black and White Export
  • Export PDF files in black and white
• ShowFormulas Option
  • Developers can choose to show formulas of cells in a workbook rather than the value results
• RangeChanged Event Tables
  • This event fires whenever a range of cells is changed, and we added an optional Tables[] attribute to show if the array contains any tables
• Support Date Axis
  • The date axis supports charts just like Excel