What's New in SpreadJS v13

What's New in SpreadJS v13.1

Enhanced Hyperlink Support

SpreadJS will now support keeping hyperlinks in imported Excel files intact. Users can create hyperlinks with SpreadJS and export those successfully to Excel. Also, hyperlinks can be managed with the SpreadJS Designer dialog and the context menu at runtime.

Read more about hyperlinks | Hyperlink demos

Format Strings

With our new enhancements, developers will have more control over how they set formats, including formulas and values, at the same time! Now, Formulas can be set as formatting and applied to different cells.

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)}}.";
            

Read more about Format Strings | Format Strings 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.

Custom Sorting

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. In SJS, we 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

With v13.1, users can now do insert copied/cut cells in one action, with the context menu option Insert Copied Cells... and choose to shift cells to the right or down.

Multi-Select ComboBox

With v13.1, we have added the ability for users to select multiple options from the the ComboBox 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.

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 the 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

SpreadJS v13 - November 5, 2019

• In-cell images and buttons with a variety of useful drop-down menus
• Cell State styles for visual feedback based on user activity
• New cell types:
  • Radio List
  • Checkbox List
  • Button List
  • Range Template
• Excel parity features:
  • Chart Enhancements
  • Table Behavior Enhancements
  • Text Rotation
  • Pixel Scrolling
  • Dynamic Arrays and new Array functions
• Formula Tracing for enhanced spreadsheet audit and debugging
• Dynamic Column Width
• Split Resizing
• Auto Merging

Cell Buttons and Drop-Downs

Button capabilities have been added to cell styles in SpreadJS. These predefined buttons can be defined within cell styles. Different buttons include checkmarks, drop-downs, undo/redo, and spin buttons. In addition to a few predefined commands, developers can code specific behavior for those buttons using the new API.

New drop-down celltypes have been added to SpreadJS. These menus include: calculator, date picker, month picker, time picker, color picker, list, slider, and workflow list.

In conjunction with this feature, we have created cell buttons, which are built-in button features that allow you to add interactive buttons to Spread. 

 

Cell States

Cell States allow you to set the style of a cell range based on the current state of the cell. Cell states allow you to provide visual feedback to users based on their actions. These actions can include hovering over cells, selecting cells, and editing cells.

New Cell Types

Radio List, Checkbox List, Button List, and Range Template have been added.

We have added the ability to create lists of these selection cell types within a single cell. There are many different options that can be customized, including the direction, text alignment, and spacing.

We have also applied this new list cell type to our new buttons as well, giving developers the ability to put buttons in a list and customize them:

The Range Template cell type provides the developer with template-based cell rendering. This allows you to define a template of cell ranges that can then be displayed as a card view within a cell.

Chart Enhancements

These enhancements include font and position support for legends, axis display unit selection, custom area borders, hover styles, logarithmic axes, and trendlines. For more information on these features, see our in-depth Chart Enhancements blog.

Table Enhancements

The table functionality has been enhanced, with changes including: resize handlers, scrolling headers, changes to context menus, table selection, tab navigation, and total row. For more information on the specifics of these changes, check out the Table Enhancements blog.

Text Rotation

We have added Excel-like text rotation, which gives developers the ability to customize how users can rotate text in their workbooks.

Pixel Scrolling

Pixel scrolling has been added, which allows the developer to specify a specific pixel amount to scroll with each increment.

Dynamic Arrays

Excel recently introduced an enhancement for Array formulas known as Dynamic Arrays. This allows for automatic expansion of array results and introduces a set of new array manipulation functions. Using Array functions can be a huge performance boost, especially when they replace a large number of formulas with a single array formula. These functions include:

FILTER - filters an array based on a boolean array RANDARRAY - returns an array of random numbers SEQUENCE - generates a list of sequential numbers SORT - sorts the contents of a range or array SORTBY - sorts the contents of a range or array based on values in a corresponding range or array UNIQUE - returns a list of unique values in a list or range

We have also added an "@" operator, which represents an implicit intersection. This operator allows the expression to evaluate at the intersection of the specified row and column.

Formula Tracing

Formula tracing is a new feature in SpreadJS that gives you the ability to inspect the dependent and precedent cells of a formula. This feature can help you provide a visual walk-through of formulas to see how they relate to one another.

Dynamic Column Width

Dynamic column width (also known as proportional sizing) is a feature we have added that ensures columns fill the viewport exactly. This means that when the viewport size is changed, or the user adds/deletes/resizes any column, the columns that have dynamic width applied to them will automatically resize to fill the viewport.

Split Resizing

Split resizing gives developers control over dynamic sizes in their workbooks. Split resizing also applies to rows as well, and only resizes adjacent rows/columns.

Auto Merging

Auto merging is a new feature in SpreadJS v13 that helps developers consolidate data in a workbook. When this feature is used, a developer can automatically merge adjacent rows/columns that have the same data. The developer can specify the policy for how the auto merge happens.

In addition, merged data will stay on the screen when scrolling, as long as part of the merged data is still visible:

For more information about these and other features for SpreadJS v13, check out our other blogs about these specific features. Visit our demos to try them out for yourself!