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 simply enclosed inside square brackets within a formula:

=[Calc.xlsx]Sheet1!A1

See Cross Workbook Formula demo

PivotTable Enhancements

Timeline Slicer Support

SpreadJS PivotTables now support timeline slicers, which gives your users the ability to conveniently slice data by specific dates.  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.

See the PivotTable Timeline Slicer demo

Show Items with no Data

By default, PivotTables will display only items that contain data, and this 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 in them.

See the PivotTable Show Items With No Data demo

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 as well as the SpreadJS Designer.

See the TableSheet's Multiple Header demo

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 to provide supporting summary data at a quick glance.

See the TableSheet Free Header Area demo

Cross Column Support

One of the biggest TableSheet features we have added in this release is known as 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 allows 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 pivots this data into a more streamlined row view, like a PivotTable but for data entry.  This could also be used for things like budgeting or expense reports.

See the TableSheet's Cross Column demo

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 that the width of the column.  This column header StackedMode API enhancement allows you to display the full text without having to increase 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:

See the TableSheet's Column Header Fit Mode demo

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 published on our GitHub

Filter Auto Extend Support

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

See the Auto Extend Filter Range demo

Vue3 Support

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

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 a property “productName” you could simply use this operator to refer to it:

A1.productName

See the Property Function demo

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 in shapes.

See the Shape Text demo

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 was set, row or column changes would not affect it.  With this release, we have added the ability for print ranges to automatically update when rows or columns are added or removed.

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.

See our demo that showcases this Show All Sheets Button

GetUsedRange Support

SpreadJS now supports the “GetUsedRange” API, which essentially  returns the range of cells that have data in them.

See the Get Set Data demo

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:

See the Table demo

VeryHidden Worksheet Support Status

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

See the Visibility demo

SetStyle/SetStyleName Cell Range Support

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

We hope are you are excited about the new features and enhancements added to SpreadJS in the v14.1 release. To try these features, download a free 30-day trial of SpreadJS.

Check out our demos and documentation to learn more.