SpreadJS 15

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.
• 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

• 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.
• Named Style Apply Flags - Apply flags have been added to named styles to handle the effect of different properties in the named styles.
• 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 Microsoft 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.

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.
• 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.
• 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.
• 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.

Designer Enhancements

• 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.

Angular 13 Support

• Angular 13 is now supported in SpreadJS v15 in addition to Vue and React.