Print Page - tabletoexcel for datagrid

Title: tabletoexcel for datagrid
Post by: devnull on December 04, 2014, 04:37:37 PM

I need to be able to allow the user to download a datagrid (table) into a local excel spreadsheet with basic styling.

I have tried the following code which works on a regular table, but does not work on an easyui datagrid which comprises of multiple tables and views.

Is it possible to provide this method as an enhancement for the datagrid ?

Code:

<a id="dlink"  style="display:none;"></a>
<input type="button" onclick="table2excel('testTable', 'name', 'myfile.xls')" value="Export to Excel">

var tabletoexcel = (function () {
  var uri = 'data:application/vnd.ms-excel;base64,'
  , template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>'
  , base64 = function (s) { return window.btoa(unescape(encodeURIComponent(s))) }
  , format = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) }
  return function (table, name, filename) {
  if (!table.nodeType) table = document.getElementById(table)
  var ctx = { worksheet: name || 'Worksheet', table: table.innerHTML }
  
  document.getElementById("table2excel").href = uri + base64(format(template, ctx));
  document.getElementById("table2excel").download = filename;
  document.getElementById("table2excel").click();
  
  }
})()

Title: Re: tabletoexcel for datagrid
Post by: stworthy on December 05, 2014, 07:50:52 AM

The method 'toExcel' can be extended as:

Code:

$.extend($.fn.datagrid.methods, {
    toExcel: function(jq, filename){
        return jq.each(function(){
            var uri = 'data:application/vnd.ms-excel;base64,'
            , template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>'
            , base64 = function (s) { return window.btoa(unescape(encodeURIComponent(s))) }
            , format = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) }

            var alink = $('<a style="display:none"></a>').appendTo('body');
            var table = $(this).datagrid('getPanel').find('div.datagrid-view2 table.datagrid-btable');
            var ctx = { worksheet: name || 'Worksheet', table: table.html()||'' };
            alink[0].href = uri + base64(format(template, ctx));
            alink[0].download = filename;
            alink[0].click();
            alink.remove();
        })
    }
})

Title: Re: tabletoexcel for datagrid
Post by: devnull on December 06, 2014, 09:21:30 PM

Great, would this also work when there are frozen columns ?

Title: Re: tabletoexcel for datagrid
Post by: stworthy on December 07, 2014, 08:03:09 AM

The updated method works with frozen columns.

Code:

$.extend($.fn.datagrid.methods, {
    toExcel: function(jq, filename){
        return jq.each(function(){
            var uri = 'data:application/vnd.ms-excel;base64,'
            , template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>'
            , base64 = function (s) { return window.btoa(unescape(encodeURIComponent(s))) }
            , format = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) }

            var alink = $('<a style="display:none"></a>').appendTo('body');
            var view = $(this).datagrid('getPanel').find('div.datagrid-view');
            var table = view.find('div.datagrid-view2 table.datagrid-btable').clone();
            var tbody = table.find('>tbody');
            view.find('div.datagrid-view1 table.datagrid-btable>tbody>tr').each(function(index){
                $(this).clone().children().prependTo(tbody.children('tr:eq('+index+')'));
            });
            var ctx = { worksheet: name || 'Worksheet', table: table.html()||'' };
            alink[0].href = uri + base64(format(template, ctx));
            alink[0].download = filename;
            alink[0].click();
            alink.remove();
        })
    }
});

Title: Re: tabletoexcel for datagrid
Post by: devnull on December 08, 2014, 06:12:57 AM

Great, thanks very much, but can it also include the header row ?

Title: Re: tabletoexcel for datagrid
Post by: stworthy on December 08, 2014, 06:59:37 AM

No header is added. You have to modify the code to include it by yourself.

Title: Re: tabletoexcel for datagrid
Post by: LdyMox on October 14, 2015, 07:54:11 AM

Anyone have a version of this that works in IE {11}? This code works great in Mozilla but errors out in IE (Getting error that means my URI string limit was exceeded). I have been trying a number of things with no luck. Thanks.

Title: Re: tabletoexcel for datagrid
Post by: tulip on April 24, 2016, 06:52:38 PM

Hi everyone!
I'm a new member of this forum. I would like to ask you about how to use this " extend toexcel". Thank you very much!

Title: Re: tabletoexcel for datagrid
Post by: thecyberzone on May 03, 2016, 01:19:11 AM

How to call this extended toExel method of datagrid ? I want to store current datagrid data in a filename "text.xlsx", how to do that?

Some may be like this
$('#dg').datagrid('toExcel',{filename:'text.xlsx'});

but this does not work, anyone please help me.

Title: Re: tabletoexcel for datagrid
Post by: Pierre on May 03, 2016, 03:50:15 AM

please try this:
$('#dg').datagrid('toExcel','text.xls');

Title: Re: tabletoexcel for datagrid
Post by: thecyberzone on May 04, 2016, 01:18:53 AM

It works, but during opening the Excel file it gives a warning saying that Format is different from current version.

And one more. Someone is asking for Header Row in the exported Excel sheet.

you just replace following line of code
var table = $(this).datagrid('getPanel').find('div.datagrid-view2 table.datagrid-btable');
with
var table = $(this).datagrid('getPanel').find('div.datagrid-view2');

and that's all for including Header Rows of Datagrid in Excel sheet.

Title: Re: tabletoexcel for datagrid
Post by: jega on July 16, 2016, 11:23:05 PM

Just tried the extended code,

Simple datagrid with pagination, 4 cols and 25 rows

In this line

alink[0].click(); An error says "The data area that is send to a systemcall, is too small"

Title: Re: tabletoexcel for datagrid
Post by: shumakosik on November 29, 2016, 09:43:48 PM

Quote from: jega on July 16, 2016, 11:23:05 PM

Just tried the extended code,

Simple datagrid with pagination, 4 cols and 25 rows

In this line

alink[0].click(); An error says "The data area that is send to a systemcall, is too small"

Hi. I have some problem too with IE 11...

EasyUI Forum

General Category => EasyUI for jQuery => Topic started by: devnull on December 04, 2014, 04:37:37 PM