Tabulator javascript datagrid inside frappe reports

Tabulator js in Frappe Report.md

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: [],
    });
  },

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) {
                definitions.forEach((column) => {
                    // map to frappe report column convention
                    col = frappe.query_report.columns.filter(val=>val.fieldname==column.field).length ? frappe.query_report.columns.filter(val=>val.fieldname==column.field)[0] : column;
                    column.title = col.label;
                    column.field = col.fieldname;
                    column.frozen = col.frozen;
                    column.visible = col.visible;
                    column.col_order = col.col_order;
                    column.headerFilter = true; // add header filter to every column
                    // todo: handle link columns
                });
                // remove undefined columns for conditional column visibility
                let i = definitions.length;
                while (i--) {
                    if(definitions[i].field === undefined) {
                        definitions.splice(i, 1);
                    }
                }
                definitions.sort((a, b) => a.col_order - b.col_order);
                return definitions;
            },
        };
    }

ERPNext Version 14 Does this works?