How to export a table to Excel ( 2 steps )

API & Customization Show & Tell
1

Hi builders.

I will explain how to export tables, or search results to xls files. I have hosted the js file in my server, but you can download the files and host in your own server if you prefer, or use my url server (no problem).

If you need more information about this plugin.

https://www.jqueryscript.net/table/Export-Html-Table-To-Excel-Spreadsheet-using-jQuery-table2excel.html

1st. STEP: In your API&Code section add this code:

// ---- LAZY LOAD EXTERNAL JS export2excel

KnackInitAsync = function ($, callback) {
window.$ = $;
LazyLoad.js(['https://ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js', 'https://hoipoi.es/toexcel/dist/jquery.table2excel.js'], function () {
callback();
});
};

2nd STEP: Add a Rich text view after your table or search result with this html code:

<button class="exportToExcel">Export to XLS</button>
<script>
$(function() {
$(".exportToExcel").click(function(e){
var table = $('.kn-table-wrapper');
if(table && table.length){
$(table).table2excel({
exclude: ".noExl",
name: "Excel Document Name",
filename: "myFileName" + new Date().toISOString().replace(/[\-\:\.]/g, "") + ".xls",
fileext: ".xls",
exclude_img: true,
exclude_links: true,
exclude_inputs: true,
preserveColors: false
});
}
});
});
</script>

All this could also be done inside a View Render Trigger but I have thought it's easier to do so for people who don't have much experience I hope that you enjoy it.

Regards.

2

Just thinking out loud: wouldn't be even more appropriate to actually load the views inside its callback? By that I mean:

LazyLoad.js(['https://ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js', 'https://hoipoi.es/toexcel/dist/jquery.table2excel.js'], function () {
  console.log('Loaded external files!');
  $(document).on('knack-view-render.any', function(event, view, data) {

  // whatever

  });
});

Because I am not sure about the order of execution (everything is async, isn't it?)

Still, I've tried all the above options with dropzonejs and I couldn't make it work ($(...).something "is not a function" error). Other things worked fine (such as jQueryUI or bootstrap....). I'm still very confused on how to correctly use that.

For example, I couldn't manage to make google maps APIs to work with lazyLoad... I am actually getting it with the getScript() (which is... well... not good...)

3

Agreed Davide, to use it across views and only load once (and totally not tested by me):

LazyLoad.js(['https://ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js', 'https://hoipoi.es/toexcel/dist/jquery.table2excel.js'], function () {
	console.log('Loaded external files!');
});

$(document).on('knack-view-render.any', function(event, view, data) {

var buton_xls = $( "<a href='#' class='exportToExcel export-data kn-button is-button is-small'><span class='icon is-small'><i class='fa fa-file-excel-o'></i></span><span>Export to XLS</span></a>" );
$('.kn-export-button').append ( buton_xls );

$(document).ready(function () {
$(".exportToExcel").click(function(e){
e.preventDefault();
var table = $('.kn-table-wrapper');
if(table && table.length){

$(table).table2excel({
exclude: ".noExl",
name: "Excel Document Name",
filename: "myFileName" + new Date().toISOString().replace(/[\-\:\.]/g, "") + ".xls",
fileext: ".xls",
exclude_img: true,
exclude_links: true,
exclude_inputs: true,
preserveColors: false
});
}
});
});

});
4

383433730112: regarding your last question: "is possible to modify the modal content that appears when we want to export a table and add one more line"

Well, I'm not a ninja coder, but I guess you can use a mutationObserver. You can set it up to execute the callback once the div that contains the modal is created the first time and then you can fire the append with your new URL.

I do not have an example right now to show you but I used this method in the past for similar situations

5

Brad, so why not putting the lazyLoad outside of everything? if you put it inside the render-any, isn't it loading multiple times if you have multiple views in the same scene?

6

Sergi, I had a similar problem with another external script (dropzonejs) as I wanted to implement a multiple-file drag and drop upload. I couldn't get it work with lazyload, not sure why though.

I ended up writing the drag and drop upload myself from scratch........

Anyway, thanks for the sharing!

7

Sergi - thanks so much for working on this as it'll be handy...

I wonder if it's becuase you're LazyLoading inside the KnackInitAsync function rather than a simple LazyLoad inside either your view render event or at the top of your code so it's available anywhere.

So your view render code might be:

$(document).on('knack-view-render.any', function(event, view, data) {

LazyLoad.js(['https://ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js', 'https://hoipoi.es/toexcel/dist/jquery.table2excel.js'], function () {
	console.log('Loaded external files!');
});

var buton_xls = $( "<a href='#' class='exportToExcel export-data kn-button is-button is-small'><span class='icon is-small'><i class='fa fa-file-excel-o'></i></span><span>Export to XLS</span></a>" );
$('.kn-export-button').append ( buton_xls );

$(document).ready(function () {
$(".exportToExcel").click(function(e){
e.preventDefault();
var table = $('.kn-table-wrapper');
if(table && table.length){

$(table).table2excel({
exclude: ".noExl",
name: "Excel Document Name",
filename: "myFileName" + new Date().toISOString().replace(/[\-\:\.]/g, "") + ".xls",
fileext: ".xls",
exclude_img: true,
exclude_links: true,
exclude_inputs: true,
preserveColors: false
});
}
});
});

});
8

Well I have solved the problem, if i embed all the script inside the var buton_xls (html + script) and append into html. I don't like this solution, if anyone knows any other way to do it...

var buton_xls = $( "<a href='#' class='exportToExcel export-data kn-button is-button is-small'><span class='icon is-small'><i class='fa fa-file-excel-o'></i></span><span>Export to XLS</span></a><script>$(function() {$('.exportToExcel').click(function(e){e.preventDefault();var table = $('.kn-table-wrapper');if(table && table.length){$(table).table2excel({exclude: '.noExl',name: 'Excel Document Name',filename: 'myFileName' + new Date().toISOString().replace(/[\-\:\.]/g, '') + '.xls', fileext: '.xls', exclude_img: true, exclude_links: true, exclude_inputs: true, preserveColors: false });}});});</script>" );
  $('.kn-export-button').append ( buton_xls );
9

Someone with best javascript skills, could solve this doubt.
About the solution before I was trying to make it more elegant by adding an export button to xls right next to the default knack export button.
Everything works fine, but the terminal gives an Uncaught TypeError error: $ (...). Table2excel is not a function.
But I don't understand, because this script reference is after reading the src plugin file

My Code:

// LAZY LOAD EXTERNAL JS

KnackInitAsync = function ($, callback) {
window.$ = $;
LazyLoad.js(['https://ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js', 'https://hoipoi.es/toexcel/dist/jquery.table2excel.js'], function () {
console.log('Loaded external files!');
callback();
});
};
$(document).on('knack-view-render.any', function(event, view, data) {

var buton_xls = $( "<a href='#' class='exportToExcel export-data kn-button is-button is-small'><span class='icon is-small'><i class='fa fa-file-excel-o'></i></span><span>Export to XLS</span></a>" );
$('.kn-export-button').append ( buton_xls );

$(document).ready(function () {
$(".exportToExcel").click(function(e){
e.preventDefault();
var table = $('.kn-table-wrapper');
if(table && table.length){

$(table).table2excel({
exclude: ".noExl",
name: "Excel Document Name",
filename: "myFileName" + new Date().toISOString().replace(/[\-\:\.]/g, "") + ".xls",
fileext: ".xls",
exclude_img: true,
exclude_links: true,
exclude_inputs: true,
preserveColors: false
});
}
});
});

});

My Screenshots:

sdMb_2JQNAQxkkVESax00g.png1007×217

As an additional question, Someone with Javascript super powers, would know if it is possible to modify the modal content that appears when we want to export a table and add one more line. like this:

  • Commas (.CSV)
  • Tabs (.TXT)
  • JSON
  • XLS

The new line XLS, must to execute the table2excel function.

Thanks!!

10

Hi,

Has anyone found a solution to this?