What's New in SpreadJS v15
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.
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.
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']");
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
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.
Context Menu Scrolling
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:
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
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
Enhanced Outline Date Display in Filter Box
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:
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.
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.
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 ﾃﾞﾊﾟーﾄ.
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 ＳｐｒｅａｄＪＳ.
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 デパト.
We have also added many enhancements to the Designer, giving your users an even better experience using the optional Designer Add-on.
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.
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".
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.
Angular 13 is now supported in SpreadJS v15 in addition to Vue and React.