[SOLVED] Datagrid filter - Turn pagination off/on - ExportToExcel

[jega]

Hi.

Have a datagrid with filter and pagination. If i want to exportToExcel, it only exports the page records. Turning pagination off $('#dgPersonList').datagrid({pagination:false}), reloads the grid and shows all records, and export now all records. Turning on again, reloading and shows fine.

But, when turning pagination off/on, the filter is disabled. How to have it enabled again

[stworthy]

Please try to download a newer version 'datagrid-export.js' file from https://www.jeasyui.com/extension/datagrid_export.php

[jega]

Hi stworthy

Can't see what the updated export js should do. And state.filtersource is always null.

In my datagrid i use remoteFilter = true. With pagination my datagrid always only holds 10 records (or whatever the pagecount is).

If using exportToExcel, it can only export the rows in the page. What i then do, is to set pagination to false before export, then i have all records in grid (etc. 100). Works fine, and export is ok with 100 reeords.

On setting pagination to false, the grid reloads and load all 100 rows, and when setting pagination back to true, it reloads again and give me 10 rows in page 1. So far so good.

BUT, when i switch state of pagination, the filter boxes disappears, and i have to reload the site to get filter it back.

That's my problem.

Have i explained it god enough ??


Jesper

[stworthy]

You used the remote filtering and only returned the current page rows to the browser. So the datagrid only holds 10 rows on the client side. To solve this issue, please download the newer 'datagrid-filter.js' file from https://www.jeasyui.com/extension/datagrid_filter.php and set the 'clientPaging' property to true. On the server side, please return all the filtered rows instead of the current page rows back to the browser.

[jega]

Hi stworthy

So far so good.

Have changed default clientPaging to false

   $(function(){
      var dg = $('#dg').datagrid({
         url: 'getfromdb.asp?st=testlist&cp=true',
         pagination: true,
         clientPaging:true,
         remoteFilter: true,
         filterDelay: 800,
         onLoadSuccess: function(){
            
         }
      });
      dg.datagrid('enableFilter');
   });

To use the same serverside code as to remote paging, i put cp=true in the url string. If true, return all rows from server.

Now i have clientPaging, and all 30 rows can be exported even if looking on page 1 ( row 1-10). Working fine with filter also.

This was the test.

The site where i have to use it on, have clientPaging = false and cp=false as default on the datagrid. Why? Because the user most times only works with filtering data, maybe thousands of lines. This is much faster with remote pagination, that only sends 10 records at a time.

If the user want an export, he push switchbutton to Yes.

       <input id="exportChoice" class="easyui-switchbutton" data-options="
             width:160,
             onText:'yes',
             offText:'No',
             handleText:' Show Export',
             handleWidth:100,
             onChange:function(checked){
                if (checked == true){
                   $('#exportButtons').css('display','')
                   $('#dg').datagrid({
                     url: 'getfromdb.asp?st=testlist&cp=true',
                     clientPaging:true
                  });
                }
                else{
                   $('#exportButtons').css('display','none')
                   $('#dg').datagrid({
                     url: 'getfromdb.asp?st=testlist&cp=false',
                     clientPaging:false,
                     onLoadSuccess: function(){
                        $('#dg').datagrid('enableFilter');   
                     }
                  });
                  
                }
             }">


It changes fine to clientPaging, but all filterBoxes is disappearing.

Try http://webudv.danklub.dk/easyuitest/datagrid-export.asp


jesper

[jega]

Also tried to enableFilter after change to clientPaging, with no luck.

But i found the solution at last.

                if (checked == true){
                   $('#exportButtons').css('display','')
                   $('#dg').datagrid('disableFilter');
                   $('#dg').datagrid({
                     url: 'getfromdb.asp?st=testlist&cp=true',
                     clientPaging:true
                  });
                   $('#dg').datagrid('enableFilter');
                }

Added $('#dg').datagrid('disableFilter') before, keep filter on.

Jesper

[jega]

Hi again

Has to reopen this topic.


                $('#dgPersonList').datagrid('disableFilter');
                $('#dgPersonList').datagrid({
                   url: 'getfromdb.asp?st=perslist&buuid='+QueryString.get('buuid')+'&cp=true',
                   clientPaging:true                   
            });
            $('#dgPersonList').datagrid('enableFilter');

To get clientPaging true/false, and still had the filter, i found that if i disableFilter and enableFilter, it worked, but doing this, it fetch data from server twice.

Why does filterboxes disappear when call dgPersonList with any thanged param.


Jesper

[stworthy]

In fact you don't need to disable the filtering and enable it again. The purpose of these actions is to get all the rows from server. So you can get all these rows and pass them to the 'toExcel' method to export excel. The code looks like this:

Code:

$.post('...', function(rows){
	$('#dg').datagrid('toExcel', {
		filename: 'datagrid.xls',
		rows: rows,
		worksheet: 'Worksheet'
	});
})

[jega]

To get all rows from server, the clientPaging must be true, but when user open the site with the datagrid, it must be with clientPaging = false because most times the user just want to do some filtering to see the records in the grid. If it's 10.000 rows, it takes a lot more time when it must return all rows from server to the grid every time, instead of only 10, 20 or 30 rows on the page.

Therefore, i give the user the option to turn on export, where i set the clientPaging = true, and get all rows from server. In the case the users want to export, they know that it takes some time to load all rows.

Maybe i can't explain it good enough.

Jesper

[stworthy]

If you want to switch the client paging and server paging, just set the 'clientPaging' to true or false and call 'load' method to load the data again. Please look at this code:

Code:

// server paging
$('#dg').datagrid('options').clientPaging = false;
$('#dg').datagrid('load', 'getfromdb.asp?st=testlist&cp=false');
// client paging
$('#dg').datagrid('options').clientPaging = true;
$('#dg').datagrid('load', 'getfromdb.asp?st=testlist&cp=true');

Make sure to download the newest 'datagrid-filter.js' file from https://www.jeasyui.com/extension/datagrid_filter.php

[jega]

Hi stworthy.

Thanks for help. Now it's working like expected.

Just a little thing. You have clientPaging set to true in datagrid-filter. It's best to have it default false, because if anyone downloading the new version, and they have server paging, then their project are not working, before they set it to false.


Regards
Jesper

[jega]

Extended it a little more.

Wanted to exclude som columns from export. It could be a column formatted as an image or the expander

   function exportExcel(){
      $('#dgPersonList').datagrid('toExcel', {
          filename: 'dg.xls',
          excludelist:'_expander,leaderStatus,profileImage,mapshow'
      });
   }

The datagrid-export.js

(function($){
    function getRows(target){
        var state = $(target).data('datagrid');
           //console.log(state)
        if (state.filterSource){
           //console.log(state.filterSource.rows);
            return state.filterSource.rows;
        } else {
            return state.data.rows;
        }
    }
    function toHtml(target, excludeList){
        var dg = $(target);
        var data = ['<table border="1" rull="all" style="border-collapse:collapse">'];
        var rows = getRows(target);
        var fields = dg.datagrid('getColumnFields',true).concat(dg.datagrid('getColumnFields',false));
      var excludeFromArray = excludeList.split(",");
      $.each(excludeFromArray,function(i){
         fields = fields.filter(function(value) { return value != excludeFromArray });
      });
      //console.log(fields);
      //return;
        var trStyle = 'height:32px';
        var tdStyle0 = 'vertical-align:middle;padding:0 4px';
        data.push('<tr style="'+trStyle+'">');
        for(var i=0; i<fields.length; i++){
            var col = dg.datagrid('getColumnOption', fields);
            var tdStyle = tdStyle0 + ';width:'+col.boxWidth+'px;';
            data.push('<th style="'+tdStyle+'">'+col.title+'</th>');
        }
        data.push('</tr>');
        $.map(rows, function(row){
            data.push('<tr style="'+trStyle+'">');
            for(var i=0; i<fields.length; i++){
                var field = fields;
                data.push(
                    '<td style="'+tdStyle0+'">'+row[field]+'</td>'
                );
            }
            data.push('</tr>');
        });
        data.push('</table>');
        return data.join('');
    }

    function toArray(target, excludeList){
        var dg = $(target);
        var rows = getRows(target);
        var fields = dg.datagrid('getColumnFields',true).concat(dg.datagrid('getColumnFields',false));
      var excludeFromArray = excludeList.split(",");
      $.each(excludeFromArray,function(i){
         fields = fields.filter(function(value) { return value != excludeFromArray });
      });
        var data = [];
        var r = [];
        for(var i=0; i<fields.length; i++){
            var col = dg.datagrid('getColumnOption', fields);
            r.push(col.title);
        }
        data.push(r);
        $.map(rows, function(row){
            var r = [];
            for(var i=0; i<fields.length; i++){
                r.push(row[fields]);
            }
            data.push(r);
        });
        return data;
    }

    function print(target, title, excludeList){
        var newWindow = window.open('', '', 'width=800, height=500');
        var document = newWindow.document.open();
        var content =
            '<!doctype html>' +
            '<html>' +
            '<head>' +
            '<meta charset="utf-8">' +
            '<title>'+title+'</title>' +
            '</head>' +
            '<body>' + toHtml(target,excludeList) + '</body>' +
            '</html>';
        document.write(content);
        document.close();
        newWindow.print();
    }

    function b64toBlob(data){
        var sliceSize = 512;
        var chars = atob(data);
        var byteArrays = [];
        for(var offset=0; offset<chars.length; offset+=sliceSize){
            var slice = chars.slice(offset, offset+sliceSize);
            var byteNumbers = new Array(slice.length);
            for(var i=0; i<slice.length; i++){
                byteNumbers = slice.charCodeAt(i);
            }
            var byteArray = new Uint8Array(byteNumbers);
            byteArrays.push(byteArray);
        }
        return new Blob(byteArrays, {
            type: ''
        });
    }

    function toExcel(target, filename, excludeList){
        var dg = $(target);
        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"><meta http-equiv="content-type" content="application/vnd.ms-excel; charset=UTF-8"><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}</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 ctx = { worksheet: name || 'Worksheet', table: toHtml(target, excludeList) };
        var data = base64(format(template, ctx));
        if (window.navigator.msSaveBlob){
            var blob = b64toBlob(data);
            window.navigator.msSaveBlob(blob, filename);
        } else {
            var alink = $('<a style="display:none"></a>').appendTo('body');
            alink[0].href = uri + data;
            alink[0].download = filename;
            alink[0].click();
            alink.remove();
        }
    }

    $.extend($.fn.datagrid.methods, {
        toHtml: function(jq, param){
            return toHtml(jq[0], excludeList);
        },
        toArray: function(jq, param){
            return toArray(jq[0],param.excludelist);
        },
        toExcel: function(jq, param){
            return jq.each(function(){
                toExcel(this, param.filename, param.excludelist);
            });
        },
        print: function(jq, param){
            return jq.each(function(){
                print(this, param.title, param.excludelist);
            });
        }
    });
})(jQuery);



Jesper