Tabulator javascript datagrid inside frappe reports

To use Tabulator javascript datagrid inside frappe reports

Hack to use Tabulator http://tabulator.info datagrid to display data inside frappe script reports. Check the several advanced features in Tabulator http://tabulator.info/docs/4.9.

How it works:

Hide frappe datatable by hiding the .report-wrapper div
Hide the message div, which displays 'No Data to Show' and other messages
Instantiate Tabulator in onload, monkey patch query_report's refresh method
set_data and columns after refresh of query_report
Destroy tabulator and reset query_report refresh on navigating away from report

Step 1: Get Tabulator js, css files

add https://cdnjs.cloudflare.com/ajax/libs/tabulator/4.9.3/js/tabulator.min.js to /public/js
add https://cdnjs.cloudflare.com/ajax/libs/tabulator/4.9.3/css/bootstrap/tabulator_bootstrap4.min.css to /public/css

Step 2: Set include files in /hooks.py

app_include_css = [
    "/assets/my-app/css/tabulator_bootstrap4.min.css",
]
app_include_js = [
    "/assets/my-app/js/tabulator.min.js",
    "/assets/my-app/js/data_tabulator.js",
]

Step 3: Create /public/js/data_tabulator.js and copy code below

frappe.DataTabulator = Class.extend({
  init: function (report, opts) {
    $.extend(this, opts);
    this.report = report;
    this.options = this.options || this.get_options();
    this.setup();
    this.make(report);
  },

  setup() {
    let me = this;
    this.toggle_frappe_datatable(true);

    // monkey-patch refresh as frappe query_report does not provide refresh event
    this.original_refresh = this.report.refresh;
    this.report.refresh = function () {
      me.original_refresh.apply(this, arguments).then(() => {
        me.set_data();
      });
    };
    // destroy tabulator when navigating away
    window.addEventListener("hashchange", this.destroy.bind(this), {
      once: true,
    });
  },

  get_options() {
    // default tabulator options
    return {
      height: 450,
      layout: "fitColumns",
      placeholder: "Nothing to Show",
      data: [],
      // If you set the autoColumns option to true,
      // every time data is loaded into the table through the data option or through the setData function,
      // Tabulator will examine the first row of the data and build columns to match that data.
      autoColumns: true,
      autoColumnsDefinitions: function (definitions) {
        return report.columns.map((column) => {
          column.title = column.label;
          column.field = column.fieldname;
          // add header filter to every column
          column.headerFilter = true;
          return column;
        });

      },
    };
  },

  make() {
    let me = this,
      el = $("<div id='tabu-lator' class='table-bordered;'></div>");
    el.insertBefore($(".report-wrapper"));
    console.log(this.options);
    this.tabulator = new Tabulator("#tabu-lator", this.options);
  },

  destroy(e) {
    this.report.refresh = this.original_refresh;
    this.tabulator && this.tabulator.destroy();
    $("#tabu-lator").remove();
    this.toggle_frappe_datatable(false);
    // need to set route_options here to force refresh when navigating back to same report
    frappe.route_options = { _t: new Date().getTime() };
  },

  set_data() {
    if (this.tabulator) {
      this.tabulator.setData(this.report.data.length ? this.report.data : []);
    }
  },

  toggle_frappe_datatable(flag) {
    // hide frappe report datatable and message area
    $(".report-wrapper").toggleClass("hidden d-none", flag);
    this.report.$message.toggleClass("hidden d-none", flag);
  },
});

Step 4: Usage in any script report:

Add the following sections to any script_report js file

    // instantiate tabulator in onload
  onload(report) {
    this.table = new frappe.DataTabulator(report, {

      // provide options as needed for the report. If not provided, will use defaults
      // http://tabulator.info/docs/4.9/options
      // options: { },

      
      // http://tabulator.info/docs/4.9/columns
      // Columns will be auto generated from frappe.query_report.columns.
      // Set columns property to override default, e.g. to create columns with custom formatters, type etc.
      // columns: [],
    });
  },

vijaywm/Tabulator js in Frappe Report.md