Tabulator Download

Tabulator creates excellent tables and provides several download formats. I use the following download formats:

csv: CSV Format (downloads the rows)
xlsx: Excel Format (downloads groups and sum)
pdf: PDF Format

Tabulator exports the raw data. I show you here some easy ways for a good formatting.

Sorry, this document is only in english available.

Example

Depending on your browser you will get the english or german formats of number and datetime.

With you open the dropdown and choose options:

utf-8 CSV Export
Excel Export
PDF Export

The control columns have the property: downlad:false. They are not downloaded. Only the visible columns are dowonloaded.

Download problems and solutions

This are the download problems and solutions:

Null values should be displayed as blank or 0
- solution: accessor function:
  - for non numeric columns returning ‘‘
  - for numeric colums returning 0
countries with decimal komma
- xlsx: its not possible to calculate with numbers in the excelsheet
  - solution: accessor function returning number(value)
- csv:
  - field delimiters are changed to ';'
  - special caracters (ÄÖÜ,…) are well formatted with bom: true
  - decimal numbers has to be transformed to decimal komma
- pdf:
  - date format like in tabulator
  - number with decimal point and decimals like in tabulator
  - alignement to right
accessor functions can not appended in Json file. They have to be appended in in JavaScript

Look in the following description to get a better download result.

Prepare page head

Declare the following libraries and variables in your page head.

Attention Please refer to the tabulator documents to implent the right jsPDF and autotable table version corresponding to your tabulator version!

Accessor: k8tabulator.accessor(...)

k8tabulator.accessor uses the tabulator column formatter and formatterParams. It support the formatter 'datetime' and 'money'. For formatting the value, the k8formatter object is used (origin tabulator).

Append the accessor to the columns

In webkitthe function nestedLoop() append on all tabulator columns ths accessor function, if this is not already set.

k8formatter (source tabulator)

//default data formatters, tabulator.modules.format.js
var k8formatter = {
  "format":function(obj,value){
    var formatterParams=obj.formatterParams;
    switch(obj.formatter){
      case "money":
        value=parseFloat(value);
                
        var decimalSym = formatterParams.decimal || ".";
        var thousandSym = formatterParams.thousand || ",";
        var symbol = formatterParams.symbol || "";
        var after = !!formatterParams.symbolAfter;
        var precision = typeof formatterParams.precision !== "undefined" ? formatterParams.precision : 2;

if (isNaN(value)) {
           return this.emptyToSpace(this.sanitizeHTML(value));
        }

var number = precision !== false ? value.toFixed(precision) : value;
        number = String(number).split(".");

var integer = number[0];
        var decimal = number.length > 1 ? decimalSym + number[1] : "";

var rgx = /(\d+)(\d{3})/;

while (rgx.test(integer)) {
          integer = integer.replace(rgx, "$1" + thousandSym + "$2");
        }

return after ? integer + decimal + symbol : symbol + integer + decimal;
        break;
      case "datetime":
        var inputFormat = formatterParams.inputFormat || "yyyy-MM-dd HH:mm:ss";
        var outputFormat = formatterParams.outputFormat || "DD/MM/YYYY HH:mm:ss";
        var invalid = typeof formatterParams.invalidPlaceholder !== "undefined" ? formatterParams.invalidPlaceholder : "";

var newDatetime = moment(value, inputFormat);

if (newDatetime.isValid()) {
          return formatterParams.timezone ? newDatetime.tz(formatterParams.timezone).format(outputFormat) : newDatetime.format(outputFormat);
        } else {

if (invalid === true) {
            return value;
          } else if (typeof invalid === "function") {
            return invalid(value);
          } else {
            return invalid;
          }
        }
      default:
        return value;
      }
    },
    "sanitizeHTML":function (value) {
		if (value) {
			var entityMap = {
				'&': '&',
				'<': '<',
				'>': '>',
				'"': '"',
				"'": ''',
				'/': '/',
				'`': '`',
				'=': '='
			};

return String(value).replace(/[&<>"'`=\/]/g, function (s) {
				return entityMap[s];
			});
		} else {
			return value;
		}
	},
	"emptyToSpace": function (value) {
		return value === null || typeof value === "undefined" || value === "" ? " " : value;
	}
};

The k8formatter is copied out of the tabulator formatting function. It supports formatting for:

money
datetime

Download event

// GLOBALS_decimal_point: . or ,
// tableTab: the tabulator table
// el: the download button
// settings.title: title of the export
if(el.classList.contains('js_export_csv')){
  tabulator_export="csv";
  if(GLOBALS_decimal_point==","){
    tableTab.download(tabulator_export, settings.title+".csv",{"delimiter":";",bom:true});
  }else{
    tableTab.download(tabulator_export, settings.title+".csv",{bom:true});
  }
}else if(el.classList.contains('js_export_xlsx')){
  tabulator_export="xlsx";
  tableTab.download(tabulator_export, settings.title+".xlsx", {decimal:GLOBALS_decimal_point,sheetName:settings.title});
}else if(el.classList.contains('js_export_pdf')){
  var columnStyles={};
  var j=0;
  for(var i=0;i<otable.columns.length;i++){
    if(otable.columns[i]){
      var visible=getfromArray(otable.columns[i],"visible",true);
      var download=getfromArray(otable.columns[i],"download",true);
      if(visible && download){
        var align=getfromArray(otable.columns[i],"align","left");
        if(isset(otable.columns[i],'width')){
          columnStyles[j]={"halign":align,"columnWidth":ground(Number(otable.columns[i]['width'])*0.7,2)};
        }else{
          columnStyles[j]={"halign":align};
        }
        j++
      }
    }
  }
  tabulator_export="pdf";
  tableTab.download(tabulator_export, settings.title+".pdf", {
      orientation:"landscape", //set page orientation to portrait
      title:settings.title,    //add title to report
      autoTable:{             //advanced table styling
        margin: {left:20,right:20},
        columnStyles:columnStyles
      }
  });
}

Before calling the „download“ method the variable: tabulator_export is set to „csv“, „xlsx“ or „pdf“

CSV Format

Depending on the decimal sign the delimiter is set to ';' or ','.

Excel Format

The decimal sign is set to point or komma.

PDF Format

The ColumnStyles object is created:

"0":
- halign: right
- width: 100
"1": ...

For each colum the alignement and width is set. The width is important for columns with large content. Without width definition of all columns, the column with large content would be enlarged too much.

References

More informations to tabulator and libraries: