|
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 ? <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();
}
})()
|
|
|
|
|
Logged
|
-- Licensed User --
|
|
|
|
stworthy
|
|
« Reply #1 on: December 05, 2014, 07:50:52 AM » |
|
The method 'toExcel' can be extended as: $.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();
})
}
})
|
|
|
|
|
Logged
|
|
|
|
|
devnull
|
|
« Reply #2 on: December 06, 2014, 09:21:30 PM » |
|
Great, would this also work when there are frozen columns ?
|
|
|
|
|
Logged
|
-- Licensed User --
|
|
|
|
stworthy
|
|
« Reply #3 on: December 07, 2014, 08:03:09 AM » |
|
The updated method works with frozen columns. $.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();
})
}
});
|
|
|
|
|
Logged
|
|
|
|
|
devnull
|
|
« Reply #4 on: December 08, 2014, 06:12:57 AM » |
|
Great, thanks very much, but can it also include the header row ?
|
|
|
|
|
Logged
|
-- Licensed User --
|
|
|
|
stworthy
|
|
« Reply #5 on: December 08, 2014, 06:59:37 AM » |
|
No header is added. You have to modify the code to include it by yourself.
|
|
|
|
|
Logged
|
|
|
|
LdyMox
Newbie
Posts: 5
|
|
« Reply #6 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.
|
|
|
|
|
Logged
|
|
|
|
tulip
Newbie
Posts: 1
|
|
« Reply #7 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!
|
|
|
|
|
Logged
|
|
|
|
|
thecyberzone
|
|
« Reply #8 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.
|
|
|
|
|
Logged
|
|
|
|
|
Pierre
|
|
« Reply #9 on: May 03, 2016, 03:50:15 AM » |
|
please try this:
$('#dg').datagrid('toExcel','text.xls');
|
|
|
|
|
Logged
|
|
|
|
|
thecyberzone
|
|
« Reply #10 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.
|
|
|
|
|
Logged
|
|
|
|
|
jega
|
|
« Reply #11 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"
|
|
|
|
|
Logged
|
|
|
|
shumakosik
Newbie
Posts: 15
|
|
« Reply #12 on: November 29, 2016, 09:43:48 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... |
|
|
|
|
Logged
|
|
|
|
|
