JavaScript Spreadsheet Release Information

TableSheet and Data Manager

Two of the most significant features we have added to SpreadJS v15 are the TableSheet and Data Manager. The SpreadJS TableSheet is a new type of sheet component that is an extremely powerful data table that can also take full advantage of the existing 500+ function SpreadJS calculation engine.

This quick, data-bound table view includes a relational data manager that makes sorting, filtering, and many other data management functions incredibly fast.

PivotTable Enhancements

Slicer

You can filter PivotTables with a single click using the new slicer feature. With a familiar interface, you can filter an aggregate target by connecting multiple PivotTables and managing the filter state of row and column fields.

GETPIVOTDATA Spill Support

The GETPIVOTDATA function, which returns the visible data from a PivotTable, now supports spilling, which means the results can now populate multiple cells. It should be noted that the allowDynamicArray property has to be set to true for this to work.

Calculated Items

PivotTables now support calculated items, which can be added into the row or column field areas using constants or references to other fields in the PivotTable. Calculated items use formulas to calculate new values that don't exist in the original data, for example, calculating the total number of outstanding orders in an office supply PivotTable.

pivotTable.addCalcItem("OrderStatus", "Outstanding Orders","=OrderStatus[Canceled] + OrderStatus[Backorder] + OrderStatus[Pending]");
pivotTable.addCalcItem("OrderStatus", "Total Order", "=OrderStatus['Outstanding Orders'] + OrderStatus[Shipped]");
pivotTable.addCalcItem("OrderStatus", "Order Proportion", "=OrderStatus['Outstanding Orders']/OrderStatus['Total Order']");

Date-to-Date Filter

Data Type filters >are already supported in PivotTables, but with the v15 release, we have added some built-in date-to-date filters, including:

Quarter-to-Date: The period starting at the beginning of the current quarter and ending on the current date
Month-to-Date: The period starting at the beginning of the current calendar month and ending on the current date
Year-to-Date: The period starting at the beginning of the current year and ending on the current date

ShowHeaders

Headers can now be hidden or displayed in PivotTables for the names of the fields in row or column directions. By default, the showHeaders property is true.

ToJSON Ignore Style

In some cases, you may want to import just the visible data for a PivotTable, where the new ignoreStyle parameter of the toJSON and fromJSON methods comes in. When this is true, the pivot cache and PivotTable will drop while importing just the values.

Custom Format Field Settings

When right-clicking on a PivotTable, you can change settings for the value fields. There is now a new dialog to format the values of those fields.

Filter Dialog Searching

The SpreadJS PivotTable now supports a search box in the filter dialog, so applying filters is more convenient.

Workbook Enhancements

Sometimes the SpreadJS instance on a page is not big enough to show the context menu. To keep providing access to the context menu, we have added scrolling capabilities to it, with up and down indicators to scroll up and down.

Center Across Selection

Centering cells is even easier with v15. You can now select a range of cells and center the text to align it in the center of the entire selection.

InvalidOperation Event for DragFill

The InvalidOperation event is now fired when any invalid operation is made that involves drag filling, such as trying to drag fill on a merged range.

Color Index Format for Cell Formatting

Cell formatting now supports color index formatting in the form of color indexes ranging from 1 to 56. For example, setting this formatter would show the following colors in the sheet:

activeSheet.getRange(2,2,1,6).formatter("[color44][<300]0.0;[color3][>400]0.0;[color45]0.0")
activeSheet.getCell(2,2).value(100);
activeSheet.getCell(2,3).value(200);
activeSheet.getCell(2,4).value(300);
activeSheet.getCell(2,5).value(400);
activeSheet.getCell(2,6).value(500);
activeSheet.getCell(2,7).value(600);

Named Style Apply Flags

Apply flags have been added to named styles to handle the effect of different properties in the named styles, including:

Number Format
Font
Fill
Border
Alignment
Protection

Header/Footer Options for Different Pages

Headers and footers that are printed at the top or bottom of workbooks and worksheets can be customized, including specifying when to apply them:

Normal: Applies header or footer on all pages
First: Applied header or footer on the first page only
Odd and Even: Applies header or footer on odd pages or even pages only

When there are dates within filters, they are laid out in an enhanced outline, allowing easier filtering by date:

Formula Entry With "+"

To match with Excel, SpreadJS v15 now supports using "+" or "-" in addition to "=" to begin entering a formula in a cell.

Row and Column States

SpreadJS now supports row and column states, which can help implement conditional formatting. These states include:

Edit
Hover
Active
Selected
Updated
Inserted
Dirty
Invalid

In any of these states, you can apply a specific conditional formatting rule via addRowStateRule or addColumnStateRule that only applies when conditions are satisfied within those states in the rows and columns, such as highlighting an entire row red if a value is below a certain amount.

New Calculation Engine Functions

LAMBDA

SpreadJS v15 now supports the Excel LAMBDA function, which gives you the ability to create and name custom reusable functions that can be called just like any other function. A custom function defined with LAMBDA uses the standard Excel formula language.

These custom functions aren't limited to SpreadJS's custom function feature so that these custom functions can be saved within Excel files and exported without issues.

ASC

The new ASC function converts full-width or double-byte characters to half-width or single-byte characters. Essentially a full-width letter symbol, letter, or katakana can be converted to half-width characters using the ASC function:

ASC("デパート") returns ﾃﾞﾊﾟーﾄ.

DBCS

The new DBCS function is the opposite of the ASC function and converts half-width or single-byte characters to full-width or double-byte characters. Just like the ASC function, this one supports text as either a number, date, string, or boolean:

DBCS("SpreadJS") returns ＳｐｒｅａｄＪＳ.

JIS

The JIS function is exactly like the DBCS function, but is used exclusively for characters in Japanese and German and converts half-width characters to full-width:

JIS("ﾃﾞﾊﾟﾄ") returns デパト.

Designer Enhancements

We have also added many enhancements to the Designer, giving your users an even better experience using the optional Designer Add-on.

Printing

The Designer now supports printing with a page setup dialog. This dialog includes tabs for Page, Margins, Header/Footer, and Sheet, allowing you to control the various, familiar options for printing SpreadJS workbooks.

Text to Columns

You can now convert delimiter-separated text in a cell to text across multiple columns, essentially splitting the text by the delimiter. This new feature in the SpreadJS Designer includes a wizard window to help with converting the text.

Remove Duplicates

The new Remove Duplicates option can be used to more quickly and automatically remove redundant data. This new functionality will keep only the first occurrence of a value in a selected cell range and delete the other identical values.

Change Sheet Tab Position

The position of the sheet tab strip in the SpreadJS Designer can now be changed with a simple context menu item.

Name Manager Dialog Filter

In some cases, a workbook might have a lot of different custom names defined in it, so we have added a new filter within the Name Manager dialog to help with searching.

Ctrl + F Support

The Find and Replace dialog and functionality already exist in the SpreadJS Designer, but with v15, we have added the ability to open this dialog up using "Ctrl+F quickly".

Chart Enhancements

Data Point Style Customization

Data points in a chart had a predefined set of colors automatically chosen when the chart was created. With v15, these colors and styles can now be customized individually using the IDataPointStyle interface.