We're pleased to announce the release of SpreadJS v13.2.0. This release includes several new features. This post introduces those features and how you can use them in your applications today:

Chart Series Data Label Customization

Data labels (for series in a chart) can now be customized in terms of styling to match your application's look and feel easier.

These options include:

  • Separator
  • BackColor
  • BackColorTransparency
  • BorderColor
  • BorderWidth
  • BorderColorTransparency

These options can be set in the dataLabels options for the series:

var series = chart.series().get(1);
    series.dataLabels= {
        showValue : false,
        showSeriesName: true,
        showCategoryName: false,
        showPercentage:false,
        position: GC.Spread.Sheets.Charts.DataLabelPosition.Center,
        color: "white",
        backColor: "red",
        backColorTransparency: 0.25,
        borderColor: "green",
        borderWidth: 2
    };
chart.series().set(1, series);

What's New in SpreadJS

Preserving Chart Import Flags

In some cases, there are Excel charts that aren't supported in SpreadJS. We now have a flag that lets users preserve unsupported chart types so that those can still be exported from SpreadJS. When the flag is enabled, unsupported chart types will simply show by default as a box with the phrase "Unsupported Chart Type" in it. The content can also be customized to fit specific needs, such as describing the specific chart type that isn't supported:

sheet.charts.preserveUnsupportedChart(true, function(chart, chartHost){
      var paintElement= document.createElement('div');
      var type= chart.chartType();
      paintElement.innerHTML = 'Unsupported Chart Type:' + type;
      chartHost.appendChild(paintElement);
})

Sorting with Groups

Sorting has been enhanced to now keep rows and columns grouped when sorting. Previously, SpreadJS only performed a flat sort, which would move items around regardless of their groups. With this update, items in groups are sorted recursively, so each group's content is sorted as normal, and then those groups are sorted in the sheet, like so:

Original: What's New in SpreadJS

Sorted: What's New in SpreadJS

To make sure range sorting uses group sorting, just set the option in the RangeSorting event:

spread.bind(GC.Spread.Sheets.Events.RangeSorting, function (e, info) {
    info.groupSort = GC.Spread.Sheets.GroupSort.full;
});

Sort Ignore Hidden Rows

When sorting data that has hidden rows, it might be useful to ignore hidden rows and not include them in the sort, like Excel does:

Before:

After:

In the above case, A2:A5 are hidden. By setting the ignoreHidden option to true when sorting will enable this behavior, such as in the sortRange method or the RangeSorting event for sorting with the UI:

sheet.sortRange(0, 0, 10, 1, true, [{index:0, ascending: true}], {ignoreHidden: false});

sheet.bind(GC.Spread.Sheets.Events.RangeSorting, function (e, info) {
    info.ignoreHidden = true;
});

Allow Shape Rotation

With previous releases of SpreadJS, shapes have supported the allowResize and allowMove APIs, which let developers enable or disable resizing or moving shapes in their applications. With 13.2.0, we have also added the allowRotate API for shapes, so developers can set this to false to get rid of the rotate handler in the Shape UI:

var heart = sheet.shapes.add("Shape1", GC.Spread.Sheets.Shapes.AutoShapeType.heart, 100, 60, 200, 160);
heart.allowRotate(false);

Shape Show/Hide Handles

Shapes can be used in a lot of different scenarios, and in some cases it would be useful to hide the handles for resizing, rotating, adjust, etc. This can be done by setting the showHandle function to false:

var heart = sheet.shapes.add("Shape1", GC.Spread.Sheets.Shapes.AutoShapeType.heart, 100, 60, 200, 160);
heart.showHandle(false);

If allowResize and/or allowRotate are still set to true on the shape, users will still be able to interact with the shape, they just won't see the UI for it.

View Zoom

Developers can now intervene in the zooming process with the ViewZooming and ViewZoomed event in order to change or extend that zooming functionality, which can be useful for things like making sure that a sheet can only be zoomed to a certain factor.

These events are just bound on the sheet like other sheet events:

activeSheet.bind(GC.Spread.Sheets.Events.ViewZooming, function (e, info) {
    if (info.newZoomFactor >= 2) {
        info.newZoomFactor = 2;
    }
});
activeSheet.bind(GC.Spread.Sheets.Events.ViewZoomed, function (e, info) {
    alert("Zoom (" + info.newZoomFactor + ")");
});

Scrollbar Customization

The scrollbar can now be customized to fit different requirements of an application. Setting a scrollbar as "mobile" allows developers to customize the styling via CSS code.

 

ClearPendingChanges by Range

The clearPendingChanges API now works for cell ranges as an optional parameter. This can essentially clear any dirty, insert, or delete changes from the current sheet for the specified range. This can be useful in cases where a user might make a change to a cell and then revert the change back without using undo. Normally, that would be considered a dirty cell (even though the text was the same). With this release, the developer has control over this functionality.

RangeChanged Event isUndo Property

The RangeChanged event now supports the isUndo property, so developers can now distinguish between normal and undo operations on ranges, which matches the other events like CellChanged, RowChanged, etc. No code changes have to be made, and simply executing range changed events can be undone and redone by the undoManager.

Try SpreadJS free for 30 days

Download SpreadJS

Download Now!