New v14 Designer

GrapeCity's SpreadJS Designer is the implementation of an Excel-like user interface with SpreadJS. Features include a ribbon, status bar, formula bar, context menus, and associated dialogs. The base SpreadJS product consists of a free desktop version of the designer, and developers can install it on their machines for improved spreadsheet template design capabilities.

Over the years, customers requested a design interface they could implement in their applications. In response, GrapeCity created a separate add-on product known as the Spread Designer Component. GrapeCity made the SpreadJS Designer more flexible by componentizing it. The SpreadJS v14 Designer includes:

  • Add or remove buttons for tabs in the ribbon
  • The option to change styling
  • The ability to change the functionality of different buttons
  • Custom dialogs

Cell Interactions

Some other features focus on user interaction with cells. These features include:

  • Incremental Loading
  • Accessibility Support
  • Multi-Column Picker
  • Comment Indicator Customization
  • HTML Export
  • getRange API Enhancement
  • Copy Images from Excel
  • NumbersFitMode
  • SelectionMode with AutoMerge
  • Table Expand Rows API
  • Pixel Scrolling with Scrollbar
  • Excel Fill Effects and Patterns

Incremental Loading

Incremental loading improves performance when working with large amounts of data. When the feature is enabled, SpreadJS loads values and formulas piece-by-piece in the background, so the user can see the workbook as data is loaded. Use incremental loading when loading a workbook from JSON into SpreadJS:

var jsonOptions = {  
                    incrementalLoading: true  
            }  
            var spread = GC.Spread.Sheets.findControl(document.getElementById('ss1'));  
            spread.fromJSON(JSON.parse(jsonStr), jsonOptions);
            

 

Accessibility Support

GrapeCity is proud to announce that SpreadJS v14 includes compatibility with screen readers and ADA compliance to better assist customers that need accessibility support in their applications. The supported screen readers are NVDA for Windows, Windows Narrator, and VoiceOver for OS X.

What's New in SpreadJS v14

Multi-Column Picker

GrapeCity added a new drop-down type to SpreadJS v14: the Multi-Column Picker. Create this drop-down by specifying a data source and column information and then set the style. The property function is especially useful for parsing the returned object.

What's New in SpreadJS v14

Comment Indicator Customization

Previously, the comment indicator appeared as a red triangle in the top right of the cell. SpreadJS v14 lets users change the color and size with a few simple API calls:

var comment = new GC.Spread.Sheets.Comments.Comment();  
            comment.indicatorSize(10); // Default is 6  
            comment.indicatorColor('blue'); // Default is 'red'  
            sheet.comments.add(5, 5, comment);
            

What's New in SpreadJS v14

HTML Export

Export cell ranges to HTML tables. Design report tables in SpreadJS and export them to HTML so that they look the same as they do in SpreadJS. Add these report tables to an HTML page and easily customize a report.

What's New in SpreadJS v14

getRange API enhancement

Using the getRange API in previous versions, the row index, column index, row count, and column count were needed to get the specified cell range. SpreadJS v14 uses a more convenient parameter: the range address as a string.

Simplify your code like so:

// Get a single cell: sheet.getRange(0, 0, 1, 1)  
            sheet.getRange("A1")  
            // Get whole columns: sheet.getRange(-1, 0, -1, 3)  
            sheet.getRange("A:C")  
            // Get whole rows: sheet.getRange(0, -1, 3, -1)  
            sheet.getRange("1:3")  
            // Get a range: sheet.getRange(0, 0, 3, 3)  
            sheet.getRange("A1:C3")
            

Copy Images from Excel

Per-user request, SpreadJS now supports copying images from Excel and pasting them into sheets. The pasted image will have the same size, border, and background as it did in Excel. Charts and shapes are copied as images. Multiple images are converted to a single big image when pasted into SpreadJS.

The allowCopyPasteExcelStyle option of the workbook is enabled by default:

spread.option.allowCopyPasteExcelStyle = true;
            

NumbersFitMode

Set the display strategy for user-defined date and number types with the NumberFitMode option. This option has two enumeration options: mask and overflow.

  • GC.Spread.Sheets.NumbersFitMode.mask: enabled by default, displays "###" in place of numbers or dates that don't fit in the cell
  • GC.Spread.Sheets.NumbersFitMode.overflow: overflows dates or numbers into adjacent cells

What's New in SpreadJS v14

SelectionMode with AutoMerge

AutoMerge was initially designed to let users select and edit individual cells in an automatically merged range of cells.

Now users select the automatically merged cells as if they were spanned cells. Switch this mode on and off with a simple API call:

GC.Spread.Sheets.AutoMergeMode.SelectionMode.source  
            GC.Spread.Sheets.AutoMergeMode.SelectionMode.merged
            

 

Table Expand Rows API

SpreadJS v13 included a functionality that automatically expanded rows as data was added in the table. SpreadJS v14 features an API that enables or disables this feature:

table.expandBoundRows(true);
            

Pixel Scrolling with Scrollbar

Pixel-based scrolling was added in a previous release of SpreadJS and allowed users to scroll by a specific amount of pixels instead of the default row-by-row scrolling.

Fill Effects and Patterns

Fill effects and patterns for cells have been a feature of Excel but were never implemented in SpreadJS. GrapeCity introduced these features into SpreadJS v14 with added support for importing and exporting files that include fill effects and patterns.

Calculation Enhancements

What's New in SpreadJS v14

GrapeCity SpreadJS v14 features an updated calculation engine with new functions and enhanced features. These enhancements include:

  • The OBJECT Function
  • The PROPERTY Function
  • The RANGEBLOCK Function
  • Iterative Calculations
  • X and LET Functions
  • The CONVERT Function
  • Cascade Sparkline Total Column
  • Header Formulas and SparklineEx

The OBJECT Function

In the SpreadJS v13 release, GrapeCity introduced the RangeCellType, which gave users the ability to define a range of cells to use as a template within a single cell. Complex layouts could be designed and incorporated with one another without worrying about trying to align column and row sizes.

SpreadJS v14 features a new OBJECT function. This function lets users define an Object from a set of property names and expressions. Objects can be used for data binding to other cells or as RangeCellType or RANGEBLOCK parameters.

OBJECT(property1, expression1, property2, expression2, ...)  
            - property: The property of the object, can be a cell range reference or a text string  
            - expression: The value of the corresponding property, can be a cell range reference or a text string  
            Examples:  
            OBJECT(A1:A3, B1:B3)  
            OBJECT(A1, B1, A2, B2)  
            OBJECT("name", "test")
            

The PROPERTY Function

The SpreadJS v14 PROPERTY function lets developers parse an object by its properties. The function is laid out as follows:

PROPERTY(data_expression, property_path)  
            - data_expression: The object data, which accepts a cell reference whose value is an object or the result of the OBJECT function  
            - property_path: The property path in the object, which goes through the object properties  
            Example:  
            =PROPERTY(G10, "User.Address.Home")
            

Use this function to inspect object properties, or use it in the Multi-Column Picker to parse the returned object into the corresponding property values.

The RANGEBLOCK Function

The RANGEBLOCK function is another new feature that works with RangeCellTypes. Use this function to implement sparklines within RangeCellTypes. Ideal for implementing dashboards and multi-row scenarios.

What's New in SpreadJS v14

Cascade Sparkline Total Column

The cascade chart feature is especially useful for charting income statements and balance sheets with totals.

What's New in SpreadJS v14

Header Formulas and SparklineEx

A column formula or sparkline in the header row is useful for summarizing a dataset in some cases.

What's New in SpreadJS v14

Iterative Calculations

Iterative calculations, also known as circular references, help solve specific calculations by repeatedly using the previous result. GrapeCity is excited to add this user-requested feature.

Calculate the future value of investments, or provide automatic timestamps to files. Perform what-if analysis with the CalcEngine.goalSeek function, which uses iterative calculations in SpreadJS.

X and LET Functions

These new functions include XMATCH, XLOOKUP, and LET, which allow users to create easy to read formulas and make calculations with large datasets. Compared to VLOOKUP and MATCH, XLOOKUP and XMATCH use faster algorithms and offer more flexible options.

The LET function makes it easier for users to define intermediate formulas when working with complex calculations—essentially letting users define a variable to use within the formula. This function helps simplify formulas and improve performance in the case of repeated expressions within a formula.

What's New in SpreadJS v14
What's New in SpreadJS v14
What's New in SpreadJS v14

CONVERT Function

SpreadJS v14 now supports several new units of the CONVERT function. Units include weight and mass, distance, force, pressure, temperature, volume, area, and binary prefixes.

New Charts and Enhancements

SpreadJS v14 includes new chart enhancements and a new chart type:

  • Funnel Charts
  • Axis Cross Points
  • Pattern Fill
  • Bar/Column Chart Gap Width and Overlap
  • Doughnut Chart Hole Size
  • Line Break in Charts
  • DataLabel Delimiter Options

Funnel Chart

SpreadJS v14 now includes funnel charts as a supported Excel chart type. Excel files exported to or imported from Excel that contain funnel charts will load those charts successfully.

These charts are best used to represent stages in a sales process to show the potential revenue in each stage. You could also show users' flow, throw an email campaign starting with emails sent out, how many of those were viewed, clicked, etc., with values typically decreasing at each level, resembling a funnel.

What's New in SpreadJS v14

Axis Cross Points

Some charts represent a combination of many different charts. In such instances, the ability to choose where the X and Y-axis cross each other is valuable.

SpreadJS v14 now includes an API to set that cross point:

let axes = chart.axes();  
            axes.primaryCategory.crossPoint = 3;  
            axes.primaryValue.crossPoint = 100;
            

Or set these cross points with the GC.Spread.Sheets.Charts.AxisCrossPoint enumeration. Settings include automatic, maximum, or minimum.

What's New in SpreadJS v14

Pattern Fill

One of Excel's chart features is the ability to add pattern fill to the charts. SpreadJS v14 now supports this feature. Apply several different patterns to the foreground or background of charts:
What's New in SpreadJS v14

Bar/Column Chart Gap Width and Overlap

SpreadJS v14 column and bar charts feature two new properties: gap width and series overlap.

  • gapWidth: sets space between the bar or column clusters as a percentage of the bar or column width (between 0 and 5)
  • overlap: sets bar or column positions (between -1 and 1)

What's New in SpreadJS v14
What's New in SpreadJS v14

Doughnut Chart Hole Size

SpreadJS v14 lets users customize the size of the hole in their doughnut charts. Display wider slices to better accommodate data labels with long text. Set it to zero and manipulate the data to create a gauge chart for a dashboard.

What's New in SpreadJS v14
What's New in SpreadJS v14

Line Break in Charts

SpreadJS charts now support line breaks for the chart area, including axis labels and legend text, just like Excel.

DataLabel Delimiter Options

Chart DataLabels previously only supported commas as a delimiter. SpreadJS v14 now supports semicolons, periods, and line breaks as well.