#Hypergrid by OpenFin

See the polymer component page for api documentation and demos.

Watch the Pivotal Labs presentation recorded on Jan 13th 2015.

Use the Hypergrid Openfin Installer to install an openfin startup link and see the demo running on your desktop. You can also see a few demos..

• behaviors - the main demo using some paper-elements, showing off all the current behaviors
• json standalone - a side project demonstrating a fin-hypergrid use case of a standalone json behaviour
• simple standalone - the simplest example of just a standalone fin-hypergrid with default behavior
• styled standalone - a slightly more sophisticated styled example of a standalone fin-hypergrid with a json behavior
• jquery partials - an example using jquery dynamic partial loading
• multi window snap and dock example

#Hypergrid The Hypergrid control is a Google polymer web component, canvas based open source general purpose grid. The purpose of this project is to address the Finance/Big Data community's desire for a high performance, unlimited row data-grid. At the moment, it is in a beta stage and still has ongoing work to be completed. These include bug-fixes/features/automated testing/etc. Please try it out and let us know what you think.

• HiDPI mode is now enabled by default and is no longer an attribute but a json startup config property. The performance issue witnessed in chrome browsers seems to have been fixed.

The design makes no assumptions about the data you wish to view which allows for external data sources as well as external manipulation and analytics. Manipulations such as sorting, aggregation, and grouping can be achieved using external best of breed high-performant real time tools designed for such purposes. Several grid behavior examples are provided including a Kx Q Kdb+ example.

##The Super Easy Setup If you just want to see Hypergrid working you can run the Hypergrid Openfin Installer or just go to the demo.

The following instructions and project structure is based on the google polymer team best practices for developement of polymer/web-components applications and elements. It is further documented here.

The only file that is necessary to deploy the hypergrid is the webcomponent html file, fin-hypergrid.min.html which is a vulcanized conglomeration of all code and dependencies required by fin-hypergrid. This file must be imported according to the webcomponents specification. If you are targeting non webcomponent compliant browsers you will also need the webcomponents.js polyfill found within the polymer project. An example of this is found here. The webcomponents.js file is not necessary if your target platform is a webcomponents compatible browser. As chrome is currently the only one, we suggest you include this polyfil.

1. This setup has been tested and works, if you have problems you most likely have security restrictions or proxy issues. You may need to use sudo for npm and bower installs. Make sure you have internet access, node/npm, grunt-cli, and bower installed and working properly on your machine.
   1. node installation
   2. grunt/grunt-cli installation
   3. bower
2. Create a directory 'developement' and cd into it
3. Clone this repo git clone https://github.com/openfin/fin-hypergrid.git
4. cd into the cloned project cd fin-hypergrid
5. Install the npm dependencies (sudo) npm install
6. Install the bower dependencies bower install
7. Start the grunt process grunt serve, after which your browser should automatically open

1. Notice that bower installs many dependencies a level up from the fin-hypegrid project directory, this is the polymer way of developing custom elements. The actual project directory IS fin-hypergrid, everything is done relative to this, it just needs to live in it's own containing developement directory.

##KDB+/Q by kx systems demo tabs (select either the 'Q' or 'Q Tree' tabs).

This Hypertree + Hypergrid example allows you to define a on the fly custom drill down hierarchy while supporting grouping, aggregations, and sorting in realtime. The current example demonstrates 20MM updates/second. The default aggregations available are sum, min, max, first, last, average, weighted average, mean, and standard deviation. Press options/alt to drag and drop define the visible columns and the drill down hierarchy.

1. The Q tabs will not be populated with data until you run a Q server script that is provided.
2. Make sure q 32 bit free version is installed Q free version
3. Startup either q bigtable.q, q sorttable.q, or install and run the extremely powerfull Hypertree server found in Stevan Apters github area.
4. Make sure grunt serve is running or your browser is pointed to the proper demo tab.
5. If you are running locally, the grunt serve process should automatically refresh your web browser with the q driven grid now populated with data

var jsonGrid = document.querySelector('#json-example');

var jsonModel = jsonGrid.getBehavior();

jsonModel.setFixedColumnCount(1);

Hypergrid utilizes a custom scrollbar component so as to not be limited to tables of 33MM pixels in width or height. In addition to the custom scrollbar, The OpenFin hypergrid utilizes row and column cell scrolling, not pixel scrolling. This has many benefits that become apparent over time.

##Hypergrid example for displaying RDBMS data

This is an example usage of the hypergrid control looking at the +1MM row postrgres db example from the greenplum getting started tutorial.

For this example to work you'll need to

• install the [greenplum tutorial db](http://gpdb.docs.pivotal.io/gs/42/pdf/GP-Getting-Started.pdf
• npm install any-db-postgress and websocket.io
• run the node script found in the rdbms directory
• navigate to the q behavior example and press "reconnect"

feel free to connect this example to any other db that any-db supports (MS SQL, MySQL, Postgres, and SQLite3). Make sure to npm install the proper any-db-***** interface and edit the config.js file.

Cell renderers are easy to create and the default method for generating highly customized dynamic cell content. They live in an object called a cellProvider which is provided by YOUR behavior object.

There are four areas that have distinct cell renderering override capabilities.

These areas coorespond to four functions on a CellProvider found in YOUR behavior object.

        getCell: function(config) {
            var cell = this.cellCache.simpleCellRenderer;
            cell.config = config;
            return cell;
        },

        //replace this function in on your instance of cellProvider
        getTopLeftCell: function(config) {
            var cell = this.cellCache.emptyCellRenderer;
            cell.config = config;
            return cell;
        },

        //return the cellRenderer instance for renderering fixed col cells
        getFixedColumnCell: function(config) {
            var cell = this.cellCache.simpleCellRenderer;
            cell.config = config;
            return cell;
        },

        //return the cellRenderer instance for renderering fixed row cells
        getFixedRowCell: function(config) {
            var cell = this.cellCache.simpleCellRenderer;
            cell.config = config;
            return cell;
        },

#Creating a custom cell renderer. Creating a cell renderer and using it is very easy, ie:

var jsonModel = jsonGrid.getBehavior();

//get the cell cellProvider for altering cell renderers
var cellProvider = jsonModel.getCellProvider();

//replace the main area's getCell functon
cellProvider.getCell = function(config) {
    var renderer = cellProvider.cellCache.simpleCellRenderer;
    config.halign = 'left';
    var x = config.x;

    //setting properties for an entire row, just use modulo operator
    if (y % 3 === 0) {
        //change the background color to green on every 3rd row
        config.bgColor = '#00ff00';
    } else if ((y - 1) % 3 === 0) {
        //change the background color to blue and foreground to white just below every 3rd row
        config.bgColor = '#0000ff';
        config.fgColor = '#ffffff';
    }
    if (x === 0) {
        renderer = cellProvider.cellCache.linkCellRenderer;
    } else if (x === 2) {
        config.halign = 'center';
    } else if (x === 3) {
        config.halign = 'center';
    } else if (x === 6) {
        config.halign = 'center';
    } else if (x === 7) {
        var travel = 60 + Math.round(config.value*150/100000);
        var bcolor = travel.toString(16);
        config.halign = 'right';
        config.bgColor = '#00' + bcolor + '00';
        config.fgColor = '#FFFFFF';
        config.value = accounting.formatMoney(config.value);
    } else if (x === 8) {
        var travel = 105 + Math.round(config.value*150/1000);
        var bcolor = travel.toString(16);
        config.halign = 'right';
        config.bgColor = '#' + bcolor+ '0000';
        config.fgColor = '#FFFFFF';
        config.value = accounting.formatMoney(config.value, "€", 2, ".", ",");
    }

    renderer.config = config;
    return renderer;
};

You can autosize a column to best fit size by double clicking on the column header.

Hypergrid comes with several default cell editors you can easily select, and the ability to create your own. The javascript code for the cell editors is found here.

• choice
  
• color
  
• date
  
• slider
  
• spinner
  
• textfield
  

You can easily create your own cell editors by subclassing any of the existing cell editors and registering your cell editor with Hypergrid.

• Create a subclass of an existing cell editor. See creating polymer components for understanding more about the google best practices for building next generation web components.

<link rel="import" href="../fin-hypergrid/html/cell-editors/fin-hypergrid-cell-editor-simple.html">
<polymer-element name="my-number-cell-editor" extends="fin-hypergrid-cell-editor-simple">
  <template>
    <input id="editor" type="number">
  </template>
  <script>
    Polymer({
        alias: 'numberfield',
        selectAll: function() {
            this.input.setSelectionRange(0, this.input.value.length);
        }
    });
  </script>
</polymer-element>

• Register it with your hypergrid

myHypergrid.initializeCellEditor('my-number-cell-editor');

• make use of your new new cell editor

var editorTypes = ['choice','numberfield','spinner','date','choice','numberfield','choice','textfield','numberfield'];
myBehavior.getCellEditorAt = function(x, y) {
    var type = editorTypes[x];
    var cellEditor = this.grid.cellEditors[type];
    return cellEditor;
};

Many of the hypergrid default values and properties can be set through property overriding functions addGlobalProperties and addProperties. The current list is.

    var lnfOverrides = {
        font: '13px Tahoma, Geneva, sans-serif',
        color: '#ffffff',
        backgroundColor: '#505050',
        foregroundSelColor: 'rgb(25, 25, 25)',
        backgroundSelColor: 'rgb(183, 219, 255)',

        topLeftFont: '14px Tahoma, Geneva, sans-serif',
        topLeftColor: 'rgb(25, 25, 25)',
        topLeftBackgroundColor: 'rgb(223, 227, 232)',
        topLeftFGSelColor: 'rgb(25, 25, 25)',
        topLeftBGSelColor: 'rgb(255, 220, 97)',

        fixedColumnFont: '14px Tahoma, Geneva, sans-serif',
        fixedColumnColor: 'rgb(25, 25, 25)',
        fixedColumnBackgroundColor: 'rgb(223, 227, 232)',
        fixedColumnFGSelColor: 'rgb(25, 25, 25)',
        fixedColumnBGSelColor: 'rgb(255, 220, 97)',

        fixedRowFont: '11px Tahoma, Geneva, sans-serif',
        fixedRowColor: '#ffffff',
        fixedRowBackgroundColor: '#303030',
        fixedRowFGSelColor: 'rgb(25, 25, 25)',
        fixedRowBGSelColor: 'rgb(255, 220, 97)',

        backgroundColor2: '#303030',
        lineColor: '#707070',
        voffset: 0,
        editorActivationKeys: ['esc', 'alt'],
        scrollbarHoverOver: 'visible',
        scrollbarHoverOff: 'visible',
        scrollingEnabled: true,

        defaultRowHeight: 20,
        defaultFixedRowHeight: 20,
        defaultColumnWidth: 100,
        defaultFixedColumnWidth: 100
    };

    var table = document.querySelector('#myHypergrid');

    //to apply to a specific table
    table.addProperties(lnfOverrides);

    //to apply for all tables
    //table.addGlobalProperties(lnfOverrides);

You can listen to various events that occur within the Hypergrid. Take note in order to attach listeners you must call 'addFinListener' on the grid. Each event has a detail object with various bits of information about the event. Please contact us if there are more events you need and we'll be happy to put them in.

    var jsonModel = document.querySelection('#json-grid');
    jsonModel.addFinListener('fin-cell-click', function(event) {
        console.log('fin-cell-click', event.detail);
    });

By far the most common behavior to use will be the JSON behavior. If you're not sure which one to start with, select this one.

To populate the json behavior with data simply provide hypergrid with an array of same shaped objects.

    var myJSONBehavior = document.querySelector('#myHypergrid').getBehavior();
    myJSONBehavior.setData([
        {   
            first_name:'moe',
            last_name: 'stooge',
            birth_date: '1920-01-01'
        },
        {   
            first_name:'larry',
            last_name: 'stooge',
            birth_date: '1922-05-05'
        },
        {   
            first_name:'curly',
            last_name: 'stooge',
            birth_date: '1924-03-07'
        },
    ]);

There are several ways to specify fields and headers with hypergrid.

• Let hypergrid default them. Hypergrid will inspect the first object in the data array and use all fields it finds. It will create header labels by uppercasing and placing spaces between the words delimitted by camelcase, dashes, or underscores.
  • moeLarryCurly -> Moe, Larry, Curly
  • moe-larry-curly -> Moe, Larry, Curly
  • moe_larry_curly -> Moe, Larry, Curly
• Specify fields and headers using setFields and setHeaders

    myJSONBehavior.setData(myData);
    myJSONBehavior.setHeaders(['header one','header two','header three','header four']);
    myJSONBehavior.setFields(['one','two','three','four']);

• Specify fields and headers using setColumns ala slickgrid API.

    myJSONBehavior.setData(myData);
    myJSONBehavior.setColumns([
        {
            title: 'First Name',
            field: 'first_name',
        },
        {
            title: 'Last Name',
            field: 'last_name',
        },
        {
            title: 'Birth Date',
            field: 'birth_date',
        }
    ]);

adding totals rows just under the column headers with Hypergrid is extremely simple. Call setTotals function on the JSON behavior and pass in an array of arrays of the content you want displayed.

    var totals =
        [['','1st',251,'XXXX-XX-XX','1 of 50','1 of 50','T/F','$$$$$','£££££']];

    myJSONBehavior.setTotals(totals);

    //update the totals and see the change
    totals[0][2] = 300;
    myJSONBehavior.changed();

Hypergrid allows you to snapshot the user configured state and then reapply it later(memento pattern) this includes.

• column order
• column widths
• row heights
• sorted column asc/des

to do this

1. configure the table the way you would like
2. call var state = myGrid.getState();
3. save the state object however you like, json/local storage/mongodb/etc...
4. later on, call myGrid.setState(state); with the previous state object to return

the above table will produce the below state object

{  
   "columnIndexes":[8,7,1,3,2,4,6],                 //column order
   "fixedColumnIndexes":[0],                        //fixed columns order
   "hiddenColumns":[5,0],                           //hidden column indexes
   "columnWidths":[66,50,51,81,73,96,78,74,60],     //
   "fixedColumnWidths":[53],                        //
   "rowHeights":{"1":46,"3":51,"5":51,"7":50},      //
   "fixedRowHeights":{},                            //
   "sorted":[0,0,0,0,0,0,0,1,0]                     //1 ascending, 2 descending
}

state objects can be created programmatically or by hand and applied. This is how you may pre configure your grid properties.

Hypergrid has a column picker that allows you to drag and drop columns for configuring which are visible. You can also reorder the columns here. press alt/option to open the column picker, you can press alt/option or esc to close it

It's really easy to see your data updates manifest in the hypergrid with the JSON behavior. You don't need to call setData again.

1. Update the underlying javascript object field.
2. Call the 'changed()' function on the json behavior object.

    var myData = [
        {   
            first_name:'moe',
            last_name: 'stooge',
            birth_date: '1920-01-01'
        },
        {   
            first_name:'larry',
            last_name: 'stooge',
            birth_date: '1922-05-05'
        },
        {   
            first_name:'curly',
            last_name: 'stooge',
            birth_date: '1924-03-07'
        },
    ];

    var myJSONBehavior = document.querySelector('#myHypergrid').getBehavior();
    myJSONBehavior.setData(myData);
    
    //update my data
    myData[0].first_name = 'Groucho';
    myData[0].last_name = 'Marx';
    
    //tell the behavior it's underlying data has changed
    myJSONBehavior.changed();

Hypergrid supports clickable link cells, to achieve this you need to...

• register a listener to the table for 'fin-cell-click'

jsonGrid.addFinEventListener('fin-cell-click', function(e){
    var cell = e.detail.cell;
    if (cell.x !== 0) {
        return;
    }
    alert('fin-cell-click at (' + cell.x + ', ' + cell.y + ')');
});

• override the getCursorAt method on behavior to be a function that returns the string of the name of the cursor for the column with the links

jsonModel.getCursorAt = function(x,y) {
    if (x === 0) {
        return 'pointer'
    } else {
        return null;
    }
};

• override the cell-provider to return the linkRenderer for the desired link columns

cellProvider.getCell = function(config) {
    var renderer = cellProvider.cellCache.simpleCellRenderer;
    config.halign = 'left';
    var x = config.x;
    if (x === 0) {
        renderer = cellProvider.cellCache.linkCellRenderer;
    } else if (x === 2) {
    ...
    ...
    ...

see the 'Last Name' column of the JSON tab in the main example;

There is an example integration between the Hypergrid and Microsoft Excel over the OpenFin InterApplicationBus. The example only works when running Hypergrid in the OpenFin Runtime, which is installed from the Hypergrid Openfin Installer.

Keep in mind the Excel integration is NOT a Hypergrid specific feature but is a feature that allows any OpenFin application to communicate with Excel via the InterApplicationBus.

Assumptions

• Windows machine
• Running in an openfin container

Steps to Excel-Hypergrid Integration Demo

1. Download and Unzip excel.zip
2. Launch Hypergrid Demo application
3. Launch Excel
4. Open either FinDesktopAddin-packed or FinDesktopAddin64-packed depending for your Excel
   • Enable plug for the session
5. Open hypergrid.xls file from unzipped directory
6. Select a cell(s) in Hypergrid Demo Application

The excel-integration demo consists of an OpenFin app, and a C# XLL plugin built using the Excel-DNA infrastructure. The Excel-DNA infrastructure provides a C++ XLL plugin which exposes the Excel Object Model to C# dll's and code which can be configured using a manifest file (.dna). Here are the steps to setting up the integration demo...

Because so many folks have been asking for an example of JSON with Excel, we've provided a bare bones example that should help you out understanding how it all works. Here is the link for the installer. The example uses the same FinDesktopAddin-packed and hypergrid.xls file from the above Hypergrid Excel Integration above. The html for the example is here.

To enable excel messaging for your behavior, embed the fin-hypergrid-excel tag inside your grid tag

<fin-hypergrid>
    <fin-hypergrid-behavior-default></fin-hypergrid-behavior-default>
    <fin-hypergrid-excel
            publish="onSelect",
            subscribe="onExcelChange"
            interval="500"
            logging=false>
    </fin-hypergrid-excel>
</fin-hypergrid>

All the attributes listed above default to the values listed above. These properties can be described as follows...

attribute	values/examles	description
publish	any valid javascript identifier onSelect onJSONSelect	this is the topic used to publish the cell data messages to the InterApplicationBus . This value MUST be the same as the second argument in the Excel addin function =FinDesktopSync("hypergrid-demo","onSelect","").
subscribe	any valid javascript identifier onExcelChange onJSONExcelChange	this is the topic used to subscribe to cell data messages from the InterApplicationBus . This should be left to the default of "onExcelChange" unless you want to push the cell data to other InterApplicationBus endpoints.
interval	integer value in milliseconds 500 1000	millisecond interval to publish the cell data messages to the InterApplicationBus
logging	Boolean value true false	enable or disable logging of messages and errors to the console

http://exceldna.codeplex.com/

https://exceldna.codeplex.com/documentation

http://nodejs.org/download/

• add filter support
• add grouping
• local storage for layout values
• events should work for mobile
• add ink effects
• Test suite for all components and upstream dependency projects
• Tooltip support
• context menu support
• Layer abstraction
• Move to gulp
• refactor behaviors to controller-chain and model
• instant edit
• support expandable sub-content
• move this todo to github issues

-- done --

• tree-table support
• Column autosizing
• Hover event support
• Page up/down buttons
• add column selection/reordering to all behaviors
• Fix live reload on file change edge conditions
• GridBehaviors for other data sources
• fix mouse event locations when zoomed
• fix scrollbar/scrolled mouse events are broken
• fix events to work well with mobile
• refactor column selection feature to other behaviors
• rafactor features to be self contained/column selector

• tree-table support
• Column autosizing
• Hover event support
• clickable hyperlink cells
• polymer web component based
• material design compliant
• high performant canvas based rendering
• low cpu and memory usage
• Arbitrary row/column sizes
• Drag and Drop column resizing and animated reordering
• Drag and Drop row resizing
• Memento based table state retention
• Data per cell can be anything (text, numerical, nested arrays, etc.)
• Shape/size in both pixel and row/column count can change dynamically
• Infinite scrolling row/col through external high performant data sources (see Q examples)
• Copy to paste buffer selected cells
• Multi-rectangle based selection model
• Mouse driven dragging selections
• Shift/control selection augmentation
• Arbitrary fixed columns and/or rows
• configurable via passed in properties
• polymer themes supported
• Fast arrow key navigation
• Non-linear accelerated vertical key navigation
• Custom scrollbar implementation for infinite scroll of large data sets
• Cell based scrolling (not pixel)
• Pluggable behavior based eventing
• In place editing mechanism using html5 overlayed components
• Q\kdb+ GridBehavior examples provided.
  1. 100MM row example
  2. 1MM row sortable example
  3. 1MM row analytic tree/pivot table example
• Simple in memory based GridBehavior example provided
• JSON example
• Easily customizable and extensible cell rendering
• Npm/grunt-based full featured dev environment