SpreadJS V15.1 (SP1) Released!

SpreadJS V15.1 has just been released, and we have added some exciting new features to PivotTable and TableSheet, and a few workbook and calculation enhancements.  This blog will give you a run-down of many of the features added in this release, including links to demos and examples of how you can add these to your application.

Cross Workbook Formulas

One of the top requested features for SpreadJS that we have added with this release is support for cross-workbook formulas.  This new feature allows your users to enter formulas that contain references to data in other SpreadJS workbooks.  File names for other workbooks are enclosed inside square brackets within a formula:

=[Calc.xlsx]Sheet1!A1

/spreadjs/demos/features/calculation/cross-workbook-formula/purejs

PivotTable Enhancements

Timeline Slicer Support

SpreadJS PivotTables now support timeline slicers, which gives your users the ability to slice data by specific dates conveniently.  In addition to filtering a PivotTable by dates, you can use the timeline slicer to dynamically filter by date and/or time with a powerful slider control.

/spreadjs/demos/features/pivot-table/pivot-slicer/pivot-table-timeline-slicer/purejs

Show Items with no Data

By default, PivotTables will display only items containing data, which is especially noticeable when rows or columns are not visible when filtered.  With this new “Show items with no data” option, you can enable the PivotTable to show these blank values even though they don’t have any data.

/spreadjs/demos/features/pivot-table/pivot-field/pivot-show-no-data/purejs

TableSheet Enhancements

Multiple Header Support

TableSheet now supports displaying multiple rows in column headers, allowing you to visually group columns together in hierarchies of related fields.  This functionality is included in the SpreadJS script files for TableSheet and the SpreadJS Designer.

/spreadjs/demos/features/table-sheet/multiple-header/purejs

Free Header Area Support

Another powerful feature that we have added with v15 SP1 is support for a free header area in TableSheet.  This lets you create a dynamic header area above the TableSheet view that includes full support for formulas, formatting, and more, making it ideal for providing supporting summary data at a glance.

/spreadjs/demos/features/table-sheet/free-header-area/purejs

Cross Column Support

One of the biggest TableSheet features we have added in this release is TableSheet Cross Column, greatly enhancing the data entry capabilities.  Cross column allows you to present (pivot or transpose) fields from multiple related tables or records in a single row view. An example use case is to present a Payment Method field with multiple record options in a single row instead of multiple rows.

In this example, cross columns allow you to present the Cash, Check, ACH, and CC options in a single row, making it easy for your user to enter or edit this data. Cross columns essentially pivot this data into a more streamlined row view, like a PivotTable for data entry.  This could also be used for things like budgeting or expense reports.

/spreadjs/demos/features/table-sheet/cross-column/overview/purejs

Create PivotTable from TableSheet name

Another customer-requested feature for TableSheets that we have added with this release is the ability to create PivotTables from TableSheets.  You can now reference TableSheet data when creating PivotTables like so:

var tableSheetName = "TableSheet1";
var sheet = spread.addSheetTab(0,  tableSheetName, GC.Spread.Sheets.SheetType.tableSheet);
var dataManager = spread.dataManager();
var productTable = dataManager.addTable("productTable", {
    remote: {
        read: {
            url: 'https://northwind.now.sh/api/products'
        }
    }
});
productTable.fetch().then(function (defaultView) {
    //set data source with a View
    var productWithSupplierView = productTable.addView("productWithSupplierView",
    [
        { value: 'id', caption: 'ID', width: 200, isPrimaryKey: true },
        { value: 'name', caption: 'NAME', required: true },
        { value: 'quantityPerUnit', caption: 'QUANTITY PER UNIT', required: true },
        { value: 'unitPrice', caption: 'UNIT PRICE' },
        { value: 'unitsInStock', caption: ["ShipTo","unitsInStock", "aaa"] },
        { value: 'unitsOnOrder', caption: ["ShipTo","unitsOnOrder", "aaa"] },
        { value: 'reorderLevel', caption: ["ShipTo","reorderLevel", "bbb"] },
        { value: 'discontinued', caption: ["ShipTo","discontinued", "bbb"], defaultValue: true }
    ]
    );
    sheet.setDataView(productWithSupplierView);
}).then(()=>{
    var ptSheet = spread.getSheet(0);
    var pivotTable = ptSheet.pivotTables.add("pivot1", tableSheetName, 1, 1);
});

Stacked Mode Vertical Enhancement

In many cases, columns in a TableSheet will contain header text that is longer than the column's width.  This column header StackedMode API enhancement allows you to display the full text without increasing the column width, making it ideal for presenting narrow column data/entry while still providing context to the header labels.  This enhancement now supports using vertical text within those stacked mode column headers:

/spreadjs/demos/features/table-sheet/customize-view/column-header-fit-mode/purejs

Designer Enhancements

CultureInfo Enhancements

One of the new Designer features we have added is enhancements to the CultureInfo.  This includes:

• Exposing properties (id, name, predefinedFormats) in the CultureInfo type
• Support for customization of culture-related features in the number format dialog
• A new culture information package project that is  published on our GitHub

Filter Auto Extend Support

As part of Excel compatibility, we have enhanced runtime filter support in the Designer . When a filter is applied on a single cell, it is automatically extended to adjacent cells that have data in them.

/spreadjs/demos/features/filters/filter-auto-extend/purejs

Vue3 Support

We already support Vue3 in SpreadJS, and with this release, we have added Vue3 support to the Designer Component.

Calculation Enhancement

“.” Object Operator Support

The calculation engine now supports using “.” as an operator to retrieve the properties of an object.  For example, if cell A1 contains an object with the property “productName,” you could use this operator to refer to it:

A1.productName

/spreadjs/demos/features/calculation/object/purejs

Workbook Enhancements

Referenced cell content inside Shapes

In a previous SpreadJS release, we added the ability for shapes to have text added inside of them.  We have enhanced this functionality with this release to now include the ability to add cell references as the content is in shapes.

/spreadjs/demos/features/shapes/customization/shape-text/purejs

Non-contiguous Range Sparkline

Another customer-requested feature that we have added is the ability to create sparklines from data in non-contiguous ranges of cells.a

Automatically Update Print Ranges when adding/removing rows/columns

SpreadJS supports printing, but once a print range is set, row or column changes would not affect it.  With this release, we have added the ability for print ranges to update when rows or columns are added or removed automatically.

Show All Sheets Button

To make multiple sheets easy to navigate in SpreadJS, we have added an “All Sheets” button in the sheet tab bar, which shows a drop-down of all of the sheets in the workbook to make them easier to select.

/spreadjs/demos/showcase/financialkpis/purejs

GetUsedRange Support

SpreadJS now supports the “GetUsedRange” API, which essentially returns the range of cells with data.

/spreadjs/demos/features/worksheet/get-set-data/purejs

Drag-Drop Enhancement

The drag-drop functionality in SpreadJS has been enhanced to insert rows rather than overwriting existing rows.  When selecting a row and dragging it, a highlighted line will appear for the new location of the dragged row:

/spreadjs/demos/features/tables/basic-table/purejs

VeryHidden Worksheet Support Status

In the SpreadJS v15.1 release, we have added a new hidden status known as “VeryHidden.”  This behaves the same as the standard “Hidden” status, except that hide and unhide operations cannot be performed through the UI.  This setting is exported and supported in Excel as well.

/spreadjs/demos/features/worksheet/sheet-visible#demo_source_name

SetStyle/SetStyleName Cell Range Support

In the previous release of SpreadJS, setting style and style names would have to be done cell-by-cell using a loop.  This release has eliminated the need for a loop by providing the setStyle and setStyleName API on entire cell ranges, making the code a bit simpler.

To give these features a try, download a free 30-day trial of SpreadJS here today!