Skip to content

Instantly share code, notes, and snippets.

@markschl
Last active April 23, 2017 13:02
Show Gist options
  • Save markschl/c8317978e1d26bf049bb to your computer and use it in GitHub Desktop.
Save markschl/c8317978e1d26bf049bb to your computer and use it in GitHub Desktop.
Attempt to implement a Handsontable (www.handsontable.com) plugin allowing deferred loading of data (see also https://github.com/warpech/jquery-handsontable/issues/607). For an example see: http://jsfiddle.net/7Z3bD/54/
/**
* This Handsontable plugin implements deferred data loading, useful for
* large tables ("infinite scrolling").
*
*
* Example usage
* -------------
* In this example data is fetched from a Django + Tastypie Api
*
* var hot = new Handsontable(document.getElementById('table'), {
* dataSource: function(page, cb) {
* $.get("/api/v1/tablename", {page: page}).sucess(function(result) {
* cb(result.objects, result.meta.total_count);
* });
* },
* pageSize: 400,
* rowsBuffered: 400
* });
*
*
* Options explained
* -----------------
*
* - dataSource (mandatory):
* Data loading function. It replaces the *data*
* option / the loadData() function.
* It's arguments are:
* 1. a page number (1-based)
* 2. A callback function. This function accepts two arguments:
* 1. the data array
* 2. the total row count. After the first page is loaded,
* the data array of the given size is created.
* Changes to the total count are currently not taken into
* account
*
* - data (optional):
* If specified, the given array will be filled with data as the table
* is scrolled. However, all data it contained before will be removed.
*
* - pageSize (optional):
* Number of rows to load in one batch.
* Default: 100
*
* - rowsBuffered (optional):
* Number of rows above and below the visible range to be automatically
* preloaded
* Default: 100
*
* - loadDelay (optional):
* Delay in ms before starting a page load. This is
* done to prevent every page from being loaded
* during fast scrolling.
* Maybe it could be made more intelligent by determining
* the scrolling speed?
* Default: 100
*
* - loadingMsg (optional):
* DOM selector that should act as replacement for the default
*/
(function(Handsontable) {
"use strict";
/**
* @class
*/
function LazyLoader(hot, opt) {
this.hot = hot;
this.dataSource = opt.dataSource;
this.pagesize = opt.pageSize || 100;
this.rows_buffered = typeof opt.rowsBuffered === "number" ? opt.rowsBuffered : 0;
this.load_delay = opt.loadDelay || 100;
this.data = opt.data || new Array(1000);
if (opt.loadingMsg) {
this.loading_msg = opt.loadingMsg;
} else {
this.loading_msg = document.createElement("div");
this.loading_msg.className = "hot-loading-msg"
this.loading_msg.appendChild(document.createTextNode("Loading..."));
var s = this.loading_msg.style;
s.color = "#444";
s.borderRadius = "0.5em";
s.backgroundColor = "rgba(250,250,250,0.9)";
s.border = "1px solid rgba(150,150,150,0.9)";
s.padding = "1em";
}
document.body.appendChild(this.loading_msg);
var s = this.loading_msg.style;
s.position = "absolute";
s.zIndex = 999999;
this.msg_offset_top = Math.round(this.loading_msg.clientHeight / 2);
this.msg_offset_left = Math.round(this.loading_msg.clientWidth / 2);
s.display = "none";
this.loading = {};
this.init();
}
/**
* Clears and initializes the table. This function is called
* each time the data has to be reloaded.
* @param {LazyLoader~loadCallback} cb - optional callback function passed to loadPage()
*/
LazyLoader.prototype.init = function(cb) {
this.pages_loaded = {};
this.offsets = [];
this.resetOffsetCache();
this.is_new = true;
// clear the data array if it already exists
if (this.data !== null) {
this.data.splice.apply(this.data, [0, this.data.length].concat(new Array(this.data.length)));
}
var _this = this;
this.loadPage(1, function() {
_this.hot.loadData(_this.data);
cb && cb.apply(this, arguments);
});
};
/**
* Determines the currently visible row range and page (range).
* If necessary, fetches missing data using the dataSource function.
* @param {boolean} settingsChanged - true if the render was forced by a
* settings change (not scrolling), see isForced parameter from afterRender event
*/
LazyLoader.prototype.update = function(settingsChanged) {
if (settingsChanged) {
/* update the position of the loading message */
var cnt = this.hot.container,
off = Handsontable.Dom.offset(cnt);
this.loading_msg.style.top = off.top + Math.round(cnt.clientHeight / 2) - this.msg_offset_top + "px";
this.loading_msg.style.left = off.left + Math.round(cnt.clientWidth / 2) - this.msg_offset_left + "px";
}
/* get row ranges */
var first_row_visible = this.hot.rowOffset(),
last_row_visible = first_row_visible + this.hot.countVisibleRows();
first_row_visible -= this.getOffset(first_row_visible);
last_row_visible -= this.getOffset(last_row_visible);
/* These variables are always up to date */
this.first_page_visible = this.getPage(first_row_visible);
this.last_page_visible = this.getPage(last_row_visible);
var first_now, last_now;
this.buf_first_page = first_now = this.getPage(Math.max(1, first_row_visible - this.rows_buffered));
this.buf_last_page = last_now = this.getPage(Math.min(this.data.length, last_row_visible + this.rows_buffered));
var not_loaded = [],
page;
for (page = first_now; page <= last_now; page++) {
if (!(this.isLoading(page) || this.isLoaded(page))) {
not_loaded.push(page);
}
}
if (not_loaded.length) {
/* wait for a short time before loading the page, then check again
if still to be loaded */
// TODO: Any better way to deal with scrolling?
var _this = this;
setTimeout(function() {
for (var i = 0, len = not_loaded.length; i < len; i++) {
page = not_loaded[i];
if (!(_this.isLoading(page) || _this.isLoaded(page)) &&
page >= _this.buf_first_page &&
page <= _this.buf_last_page) {
_this.loadPage(page, function(pg) {
if (_this.isVisible(pg)) {
// TODO: this triggers afterRender() again!
_this.hot.render();
}
_this.updateLoadingMessage();
});
}
}
}, this.load_delay);
}
};
/**
* @param {number} rowindex - row index (0-based)
* @returns a page number (1-based)
*/
LazyLoader.prototype.getPage = function(rowindex) {
return Math.floor(rowindex / this.pagesize + 1);
};
/**
* Determines whether any of the visible rows
* are currently loading and displays a message
* if necessary.
*/
LazyLoader.prototype.updateLoadingMessage = function() {
for (var page = this.first_page_visible, lv = this.last_page_visible; page <= lv; page++) {
if (this.isLoading(page)) {
this.loading_msg.style.display = "block";
return;
}
}
this.loading_msg.style.display = "none";
};
/**
* Loads rows from one page into the data array. Afterwards, a custom
* plugin hook called 'afterLoadPage' is executed.
* @param {number} page - Page number to load (1-based index)
* @param {LazyLoader~loadCallback} cb - Function called when done
* @returns {boolean} true if the page is already loaded, otherwise false
*/
LazyLoader.prototype.loadPage = function(page, cb) {
if (this.isLoaded(page)) {
return true;
}
if (!this.isLoading(page)) {
this.loading[page] = true;
this.updateLoadingMessage();
var _this = this;
this.dataSource(page, function(data, size) {
if (_this.is_new) {
_this.data.length = size;
}
_this.pages_loaded[page] = true;
delete _this.loading[page];
if ((page - 1) * _this.pagesize >= size) {
// TODO: why does this even happen?
_this.updateLoadingMessage();
//throw "Page " + page + " is out of range.";
return;
}
var first = _this.getCorrectedPosition((page - 1) * _this.pagesize),
last = first + _this.pagesize - 1;
_this.data.splice.apply(
_this.data, [first, _this.pagesize].concat(data.slice(0, _this.pagesize))
);
cb && cb(page, first, last, _this.is_new);
_this.hot.runHooks('afterLoadPage', page, first, last, _this.is_new);
if (_this.is_new) {
_this.is_new = false;
}
});
}
return false;
};
/**
* @param {number} page - Page number
*/
LazyLoader.prototype.isLoading = function(page) {
return page in this.loading;
};
/**
* @param {number} page - Page number
*/
LazyLoader.prototype.isLoaded = function(page) {
return page in this.pages_loaded;
};
/**
* @param {number} page - Page number
*/
LazyLoader.prototype.isVisible = function(page) {
return page >= this.first_page_visible && page <= this.last_page_visible;
};
/**
* Sets an offset at a given row index. This information is used
* by getOffset()
* @param {number} index - row index
* @param {number} offset - row offset (positive = insertion, negative = deletion)
*/
LazyLoader.prototype.setOffset = function(index, offset) {
/* this.offsets is always kept sorted by index */
var data;
for (var i = this.offsets.length - 1; i >= 0; i--) {
data = this.offsets[i];
if (index <= data[0] && data[0] - index >= data[1] &&
offset > 0 && data[1] > 0) {
/* this offset can be added to an existing offset */
data[0] += offset;
data[1] += offset;
this.resetOffsetCache();
return;
}
if (index >= data[0]) {
break;
}
/* offsets at higher positions need to be adjusted */
if (offset < 0 && data[0] < index - offset) {
/* prevent moving indices below first deleted row */
data[0] = index;
} else {
data[0] += offset;
}
}
if (offset > 0) {
index += offset;
}
this.offsets.splice(i + 1, 0, [index, offset]);
this.resetOffsetCache();
};
/**
* Determines the offset introduced by insertions/deletions
* (see setOffset()) at the given position in the table.
* @param {number} position - row index
* @returns {number} Positive or negative offset
*/
LazyLoader.prototype.getOffset = function(position) {
if (this.current_offset !== null && position >= this.current_offset[0] &&
(this.next_offset_index === null || position <= this.next_offset_index[0])) {
// row with same offset as last time -> return cached offset
return this.current_offset[1];
}
var cum_offset = 0,
data = null,
prev_data = null,
offsets = this.offsets;
this.next_offset_index = null;
for (var i = 0, len = offsets.length; i < len; i++) {
data = offsets[i];
if (data[0] >= position) {
this.next_offset_index = data[0];
break;
}
cum_offset += data[1];
prev_data = data;
}
this.current_offset = [(prev_data === null ? 0 : prev_data[0]), cum_offset];
return cum_offset;
};
/**
* Adjusts a given row index by the offset at this position
* @param {number} position - row index
* @returns {number} adjusted row index
*/
LazyLoader.prototype.getCorrectedPosition = function(position) {
position += this.getOffset(position);
return position;
};
/**
* Clears the cache used to accelerate getOffset() calls
*/
LazyLoader.prototype.resetOffsetCache = function() {
this.current_offset = null;
this.next_offset_index = null;
};
/**
* @callback LazyLoader~loadCallback
* @param {number} page - page number
* @param {number} first - index of first row (corrected by offsets)
* @param {number} last - index of last row (corrected by offsets)
* @param {boolean} is_new - true if loading data for the first time
* after LazyLoader.init()
*/
Handsontable.hooks.add("afterInit", function() {
var opt = this.getSettings();
if ("dataSource" in opt) {
this.LazyLoader = new LazyLoader(this, opt);
/* The 'afterRender' callback triggers data loading */
this.addHook("afterRender", function(isForced) {
this.LazyLoader.update(isForced);
});
/* The plugin needs to keep track of row removals/additions for always being
able to correctly determine which items are visible at a given time */
// TODO: not yet investigated whether the UndoRedo infrastructure could be used for this
this.addHook("beforeRemoveRow", function(index, amount) {
this.LazyLoader.setOffset(index, -1 * amount);
});
this.addHook("afterCreateRow", function(index, amount) {
this.LazyLoader.setOffset(index, amount);
});
}
});
})(Handsontable);
@ashwinrayaprolu
Copy link

Updated the plugin to work for latest environment (0.16)

/**

  • This Handsontable plugin implements deferred data loading, useful for
  • large tables ("infinite scrolling").
    *
  • Example usage

  • In this example data is fetched from a Django + Tastypie Api.
  • $('#table').handsontable({
  •  dataSource: function(page, cb) {
    
  •      $.get("/api/v1/tablename", {page: page}).sucess(function(result) {
    
  •          cb(result.objects, result.meta.total_count);
    
  •      });
    
  •  },
    
  •  pageSize: 400,
    
  •  rowsBuffered: 400
    
  • });
  • Options explained

    • dataSource (mandatory):
  •    Data loading function. It replaces the _data_
    
  •    option / the loadData() function.
    
  •    It's arguments are: 
    
  •      1. a page number (1-based)
    
  •      2. A callback function. This function accepts two arguments:
    
  •         1. the data array
    
  •         2. the total row count. After the first page is loaded, 
    
  •            the data array of the given size is created.
    
  •            Changes to the total count are currently not taken into
    
  •            account
    
    • pageSize (optional):
  •    Number of rows to load in one batch.
    
  •    Default: 100
    
    • rowsBuffered (optional):
  •    Number of rows above and below the visible range to be automatically
    
  •    preloaded
    
  •    Default: 100
    
    • loadDelay (optional):
  •    Delay in ms before starting a page load. This is
    
  •    done to prevent every page from being loaded
    
  •    during fast scrolling.
    
  •    Maybe it could be made more intelligent by determining
    
  •    the scrolling speed?
    
  •    Default: 100
    
    • loadingMsg (optional):
  •    DOM selector or jQuery element that should act
    
  •    as replacement for the default loading message
    
    */

(function (Handsontable) {
"use strict";

/**
 * @class
 */
function LazyLoader(instance, opt) {
    this.instance = instance;
    this.dataSource = opt.dataSource;
    this.pagesize = opt.pageSize || 100;
    this.rows_buffered = typeof opt.rowsBuffered === "number" ? opt.rowsBuffered : 0;
    this.load_delay = opt.loadDelay || 100;
    this.data = opt.data || null;
    if (opt.loadingMsg) {
        this.$loading_msg = $(opt.loadingMsg);
    } else {
        this.$loading_msg = $("<div>Loading...</div>").css({
            color: "#444",
            "border-radius": "0.5em",
            "background-color": "rgba(250,250,250,0.9)",
            border: "1px solid rgba(230,230,230,0.9)",
            padding: "1em"
        });
    }
    this.$loading_msg.appendTo("body").css({
        display: "block",
        visibility: "hidden",
        position: "absolute",
        "z-index": 999999
    });
    this.msg_offset_top = Math.round(this.$loading_msg.height() / 2);
    this.msg_offset_left = Math.round(this.$loading_msg.width() / 2);
    this.$loading_msg.hide().css("visibility", "visible");
    this.loading = {};
    this.init();
}

/**
* Clears and initializes the table. This function is called
* each time the data has to be reloaded.
* @param {LazyLoader~loadCallback} cb - optional callback function passed to loadPage()
*/
LazyLoader.prototype.init = function (cb) {
    this.pages_loaded = {};
    this.offsets = [];
    this.resetOffsetCache();
    this.is_new = true;
    if (this.data !== null) {
        this.data.splice.apply(this.data, [0, this.data.length].concat(new Array(this.data.length)));
    }
    this.loadPage(1, cb);
};

/**
 * Determines the currently visible row range and page (range).
 * If necessary, fetches the data.
* @param {boolean} isForced - passed by the HT afterRender event, telling
*   if the render was forced by a settings change (not scrolling)
 */
LazyLoader.prototype.afterRender = function (isForced) {

    if (isForced) {
        /* update the position of the loading message */
        //var off = this.instance.$table.offset();
        var off = Handsontable.Dom.offset(handsontableContainer);
        var height = Handsontable.Dom.innerHeight(handsontableContainer);
        var width = Handsontable.Dom.innerWidth(handsontableContainer);
        this.$loading_msg.css({
            //top:  off.top  + Math.round(this.instance.$table.height() / 2) - this.msg_offset_top,
            top:  off.top  + Math.round(height / 2) - this.msg_offset_top,
            //left: off.left + Math.round(this.instance.$table.width()  / 2) - this.msg_offset_left
            left: off.left + Math.round(width  / 2) - this.msg_offset_left
        });
    }

    /* get row ranges  */
    var first_row_visible = this.instance.rowOffset(),
        last_row_visible = first_row_visible + this.instance.countVisibleRows();
    first_row_visible -= this.getOffset(first_row_visible);
    last_row_visible -= this.getOffset(last_row_visible);

    /* These variables are always up to date  */
    this.first_page_visible = this.getPage(first_row_visible);
    this.last_page_visible = this.getPage(last_row_visible);
    var first_now, last_now;
    this.buf_first_page = first_now = this.getPage(Math.max(1, first_row_visible - this.rows_buffered));
    this.buf_last_page = last_now = this.getPage(Math.min(this.data.length, last_row_visible + this.rows_buffered));
    var not_loaded = [],
        page;
    for (page = first_now; page <= last_now; page++) {
        if (!(this.isLoading(page) || this.isLoaded(page))) {
            not_loaded.push(page);
        }
    }
    if (not_loaded.length) {
        /* wait for a short time before loading the page, then check again
           if still to be loaded  */
        // TODO: Any better way to deal with scrolling?
        var _this = this;
        setTimeout(function () {
            for (var i = 0, len = not_loaded.length; i < len; i++) {
                page = not_loaded[i];
                if (!(_this.isLoading(page) || _this.isLoaded(page)) &&
                    page >= _this.buf_first_page &&
                    page <= _this.buf_last_page) {
                    _this.loadPage(page, function (pg) {
                        if (_this.isVisible(pg)) {
                            // TODO: this triggers afterRender() again!
                            _this.instance.render();
                        }
                        _this.updateLoadingMessage();
                    });
                }
            }
        }, this.load_delay);
    }
};

/**
 * @param {number} rowindex - row index (0-based)
 * @returns a page number (1-based)
 */
LazyLoader.prototype.getPage = function (rowindex) {
    return Math.floor(rowindex / this.pagesize + 1);
};

/**
 * Determines whether any of the visible rows
 * are currently loading and displays a message
 * if necessary.
 */
LazyLoader.prototype.updateLoadingMessage = function () {
    var is_loading = false;
    for (var page = this.first_page_visible, lv = this.last_page_visible; page <= lv; page++) {
        if (this.isLoading(page)) {
            is_loading = true;
            break;
        }
    }
    this.$loading_msg.toggle(is_loading);
};

/**
 * Loads rows from one page into the data array. Afterwards, a custom 
 * plugin hook called 'afterLoadPage' is executed.
 * @param {number} page - Page number to load (1-based index)
 * @param {LazyLoader~loadCallback} cb - Function called when done
 * @returns {boolean} true if the page is already loaded, otherwise false
 */
LazyLoader.prototype.loadPage = function (page, cb) {
    if (this.isLoaded(page)) {
        return true;
    }
    if (!this.isLoading(page)) {

        this.loading[page] = true;
        this.updateLoadingMessage();
        var _this = this;

        this.dataSource(page, function (data, size) {
            if (_this.is_new) {
                if (_this.data === null) {
                    _this.data = new Array(size);
                } else {
                    _this.data.length = size;
                }
            }

            _this.pages_loaded[page] = true;
            delete _this.loading[page];

            if ((page - 1) * _this.pagesize >= size) {
                // TODO: why does this even happen?
                _this.updateLoadingMessage();
                throw "Page " + page + " is out of range.";
            }

            var first = _this.getCorrectedPosition((page - 1) * _this.pagesize),
                last = first + _this.pagesize;
            _this.data.splice.apply(
                _this.data, [first, _this.pagesize].concat(data.slice(0, _this.pagesize))
            );

            //_this.instance.hooks.run('afterLoadPage', page, first, last, _this.is_new);
            //Handsontable.hooks.run(hot, 'beforeInit');
            _this.instance.runHooks('beforeInit', page, first, last, _this.is_new);
            cb && cb(page, first, last, _this.is_new);
            if (_this.is_new) {
                _this.instance.loadData(_this.data);
                _this.is_new = false;
            }
        });
    }
    return false;
};

/**
 * @param {number} page - Page number
 */
LazyLoader.prototype.isLoading = function (page) {
    return page in this.loading;
};

/**
 * @param {number} page - Page number
 */
LazyLoader.prototype.isLoaded = function (page) {
    return page in this.pages_loaded;
};

/**
 * @param {number} page - Page number
 */
LazyLoader.prototype.isVisible = function (page) {
    return page >= this.first_page_visible && page <= this.last_page_visible;
};

/**
 * Sets an offset at a given row index. This information is used
 * by getOffset()
 * @param {number} index - row index
 * @param {number} offset - row offset (positive = insertion, negative = deletion)
 */
LazyLoader.prototype.setOffset = function (index, offset) {
    /* this.offsets is always kept sorted by index */
    var data;
    for (var i = this.offsets.length - 1; i >= 0; i--) {
        data = this.offsets[i];
        if (index <= data[0] && data[0] - index >= data[1] &&
            offset > 0 && data[1] > 0) {
            /* this offset can be added to an existing offset */
            data[0] += offset;
            data[1] += offset;
            this.resetOffsetCache();
            return;
        }
        if (index >= data[0]) {
            break;
        }
        /* offsets at higher positions need to be adjusted */
        if (offset < 0 && data[0] < index - offset) {
            /* prevent moving indices below first deleted row */
            data[0] = index;
        } else {
            data[0] += offset;
        }
    }
    if (offset > 0) {
        index += offset;
    }
    this.offsets.splice(i + 1, 0, [index, offset]);
    this.resetOffsetCache();
};

/**
 * Determines the offset introduced by insertions/deletions
 * (see setOffset()) at the given position in the table.
 * @param {number} position - row index
 * @returns {number} Positive or negative offset
 */
LazyLoader.prototype.getOffset = function (position) {
    if (this.current_offset !== null && position >= this.current_offset[0] &&
        (this.next_offset_index === null || position <= this.next_offset_index[0])) {
        // row with same offset as last time -> return cached offset
        return this.current_offset[1];
    }
    var cum_offset = 0,
        data = null,
        prev_data = null,
        offsets = this.offsets;
    this.next_offset_index = null;

    for (var i = 0, len = offsets.length; i < len; i++) {
        data = offsets[i];
        if (data[0] >= position) {
            this.next_offset_index = data[0];
            break;
        }
        cum_offset += data[1];
        prev_data = data;
    }
    this.current_offset = [(prev_data === null ? 0 : prev_data[0]), cum_offset];
    return cum_offset;
};

/**
 * Adjusts a given row index by the offset at this position
 * @param {number} position - row index
 * @returns {number} adjusted row index
 */
LazyLoader.prototype.getCorrectedPosition = function (position) {
    position += this.getOffset(position);
    return position;
};

/**
 * Clears the cache used to accelerate getOffset() calls
 */
LazyLoader.prototype.resetOffsetCache = function () {
    this.current_offset = null;
    this.next_offset_index = null;
};

/**
 * @callback LazyLoader~loadCallback
 * @param {number} page - page number
 * @param {number} first - index of first row (corrected by offsets)
 * @param {number} last - index of last row (corrected by offsets)
 * @param {boolean} is_new - true if loading data for the first time
 *   after LazyLoader.init()
 */


Handsontable.hooks.add("afterInit", function () {
    var opt = this.getSettings();

    if ("dataSource" in opt) {
        this.LazyLoader = new LazyLoader(this, opt);

        /* The 'afterRender' callback triggers data loading  */
        this.addHook("afterRender", function (isForced) {
            this.LazyLoader.afterRender(isForced);
        });

        /* The plugin needs to keep track of row removals/additions for always being
           able to correctly determine which items are visible at a given time  */
        // TODO: not yet investigated whether the UndoRedo infrastructure could be used for this
        this.addHook("beforeRemoveRow", function (index, amount) {
            this.LazyLoader.setOffset(index, -1 * amount);
        });
        this.addHook("afterCreateRow", function (index, amount) {
            this.LazyLoader.setOffset(index, amount);
        });
    }
});

})(Handsontable);

@hristod
Copy link

hristod commented Sep 24, 2015

Hey, this looks just like what I might need! Which is the last version? The one from the comment or the one in the gist? Thank you!

@markschl
Copy link
Author

Sorry for not responding, I was quite busy with other things. I updated the Gist, should now work again (see also link http://jsfiddle.net/7Z3bD/45/). I also removed usages of jQuery and did some minor reformatting. Of course, there are still the limitations described in the issue; viewing works nicely, but editing will cause problems...

@markschl
Copy link
Author

Updated to work with manualColumnFreeze and manualColumnMove. Additionally, it is now possible to keep a reference to the data array by using the data option. Before, it was only accessible through Handsontable.LazyLoader.data.

@Dacesilian
Copy link

When I use code from this gist, browser is freezing. If I use code from http://jsfiddle.net/7Z3bD/54/ , everything is okay.
Anyway, thank you for your plugin!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment