EasyUI Forum
March 28, 2024, 01:53:54 AM *
Welcome, Guest. Please login or register.

Login with username, password and session length
News:
 
   Home   Help Search Login Register  
Pages: [1]
  Print  
Author Topic: datagrid with extension datagrid-filter, export filtered data to excel.  (Read 6774 times)
Franky
Newbie
*
Posts: 14


View Profile Email
« on: January 28, 2021, 01:05:52 AM »

Hi Sir,

Could provide me a sample or walkthrough on how can we export to excel with only those filtered rows?
Note: I'm using datagrid filter row extension.

I tried to use datagrid-export extension, however it export all rows not those filtered rows.
Logged
Franky
Newbie
*
Posts: 14


View Profile Email
« Reply #1 on: January 31, 2021, 06:03:26 PM »

Hi any experts can help?
Logged
jarry
Administrator
Hero Member
*****
Posts: 2260


View Profile Email
« Reply #2 on: January 31, 2021, 07:15:44 PM »

Please pass the rows parameter value when calling the 'toExcel' method.
Code:
var rows = $('#dg').datagrid('getRows');
$('#dg').datagrid('toExcel',{
    rows: rows,
    filename: 'table1.xls',
    caption: 'MyCaption',
    footer: [{
        productid: 'Summary',
        listprice: 30
    },{
        productid: 'Summary',
        listprice: 40
    }]
})
Logged
Franky
Newbie
*
Posts: 14


View Profile Email
« Reply #3 on: February 02, 2021, 09:58:23 PM »

Please pass the rows parameter value when calling the 'toExcel' method.
Code:
var rows = $('#dg').datagrid('getRows');
$('#dg').datagrid('toExcel',{
    rows: rows,
    filename: 'table1.xls',
    caption: 'MyCaption',
    footer: [{
        productid: 'Summary',
        listprice: 30
    },{
        productid: 'Summary',
        listprice: 40
    }]
})

Hi Jarry,

Thank you for your guidance, it work well with add in the rows:row parameters.

Would like to ask, did you know why when open the file with excel 2013, it showed a message
"The file format and extension of 'abc.xls' don't match. The file could be corrupted or unsafe....."


Logged
Franky
Newbie
*
Posts: 14


View Profile Email
« Reply #4 on: March 05, 2021, 01:43:30 AM »

Please pass the rows parameter value when calling the 'toExcel' method.
Code:
var rows = $('#dg').datagrid('getRows');
$('#dg').datagrid('toExcel',{
    rows: rows,
    filename: 'table1.xls',
    caption: 'MyCaption',
    footer: [{
        productid: 'Summary',
        listprice: 30
    },{
        productid: 'Summary',
        listprice: 40
    }]
})

Hi Jarry,

Sorry to disturb you again, recently I found that if I'm using rows:rows to get filtered data export to excel, the extracted excel consists only data for the 1st page. Data on the 2nd page and following pages will not show. Any ideal?
Logged
jarry
Administrator
Hero Member
*****
Posts: 2260


View Profile Email
« Reply #5 on: March 06, 2021, 04:56:40 PM »

The 'getRows' method only returns the current page rows. You can get the data you expect before exporting to excel. If you issue continues, please show some code snippets to demonstrate your issue.
Logged
Franky
Newbie
*
Posts: 14


View Profile Email
« Reply #6 on: March 07, 2021, 10:47:10 PM »

The 'getRows' method only returns the current page rows. You can get the data you expect before exporting to excel. If you issue continues, please show some code snippets to demonstrate your issue.

Hi Jarry,

Thanks for taking your time. My problem is the default page size is 10 rows, if user use filterbox to filter data and after filtered the data consists of 2 pages(14 rows for example), if user press the export csv button, it will only extract the 10 rows record in the 1st page. I need the excel to extract all 14 rows that's filtered by user instead of only 10 rows on the 1st page.



My current code snippets as below:

Javascript/Jquery:
Code:
dg.datagrid({
                        width:'auto',
                        height:'auto',
                        remoteSort:false,
                        nowrap:false,
                        fitColumns:true,
                        striped:true,
                        pagination:true,
                        multiSort:true,
                        url:'php/get_coo_data.php',
                        queryParams:{bpID:login},
                        columns:[[
                            {field:'item',title:'Item',width:40,sortable:true},
                            {field:'desc1',title:'Desciption',width:100,sortable:true},
                            {field:'prod_type',title:'Product Type',width:60,align:'center',sortable:true},
                            {field:'buyer_1',title:'Buyer 1',width:40,align:'center',sortable:true,editor:'text'},
                            {field:'buyer_2',title:'Buyer 2',width:40,align:'center',sortable:true,editor:'text'},
                            {field:'bpid',title:'BP',width:40,align:'center',sortable:true},
                            {field:'bpname',title:'BP Name',width:150,align:'center',sortable:true},
                            {field:'manufacturer',title:'Manufacturer',width:100,align:'center',editor:'text'},
                            {field:'state_1',title:'State 1',width:50,align:'center'},
                            {field:'country_1',title:'Country 1',width:50,align:'center'},
                            {field:'state_2',title:'State 2',width:50,align:'center'},
                            {field:'country_2',title:'Country 2',width:50,align:'center'},
                            {field:'state_3',title:'State 3',width:50,align:'center'},
                            {field:'country_3',title:'Country 3',width:50,align:'center'}
                        ]]
                    });

                    dg.datagrid('enableFilter');    // enable filter


                    $(function(){
                        $('#btnExportCsv').bind('click', function(){
                            var rows = $('#dg').datagrid('getRows');

                            $('#dg').datagrid('toCsv',{
                                rows: rows,
                                filename: 'Material_coo.csv'
                            })

                        });
                    });

While my server side code:
Code:
$bpID = isset($_POST['bpID']) && $_POST['bpID'] !==''  ? htmlspecialchars($_POST['bpID']) : '';

##*** Sorting ***
$sort = isset($_POST['sort']) ? strval($_POST['sort']) : 'item';
$order = isset($_POST['order']) ? strval($_POST['order']) : 'asc';

##*** Pagination ***
$page = isset($_POST['page']) ? intval($_POST['page']) : 1;
$rows = isset($_POST['rows']) ? intval($_POST['rows']) : 10;
$offset = ($page-1)*$rows;
 
##for easyui, sqlsvr pagination
$max = $rows*$page;
 
$result = array();
 
$whereSQL = isset($_POST['bpID']) && $_POST['bpID'] !=='' ? "WHERE TRIM(bpid) = '$bpID'" : '' ;

try{
    $result = $link->query("SELECT COUNT(*) FROM item_country_origin $whereSQL");
    $row = $result->fetch_row();
    $response["total"] = $row[0];

} catch (mysqli_sql_exception $e) {
    echo $e->__toString();
}

$sql = "SELECT * FROM item_country_origin $whereSQL ORDER BY $sort $order";

try{
    $stmt = $link->prepare($sql);
    $stmt->execute();
} catch (mysqli_sql_exception $e) {
    echo $e->__toString();
}

$res="";

if (!($res = $stmt->get_result())) {
    //echo "<br/>Getting result set failed: (" . $stmt->errno . ") " . $stmt->error;
}else{    
    $numOfRows = $res->num_rows;

    //echo "number of rows: " . $numOfRows;
}

$users = array();
while(($row = $res->fetch_assoc()) !== null ){
    array_push($users, $row);
}
$response["rows"] = $users;
 
echo json_encode($response);


« Last Edit: March 08, 2021, 12:25:04 AM by Franky » Logged
jarry
Administrator
Hero Member
*****
Posts: 2260


View Profile Email
« Reply #7 on: March 08, 2021, 12:38:56 AM »

Please call this code to get all the filtered rows.
Code:
var rows = $('#dg').datagrid('getData').filterRows;

The live example is available from http://code.reloado.com/ikibap/edit#preview. Please make sure to download the newest 'datagrid-filter.js' from https://www.jeasyui.com/extension/datagrid_filter.php
Logged
Franky
Newbie
*
Posts: 14


View Profile Email
« Reply #8 on: March 08, 2021, 09:48:07 PM »

Please call this code to get all the filtered rows.
Code:
var rows = $('#dg').datagrid('getData').filterRows;

The live example is available from http://code.reloado.com/ikibap/edit#preview. Please make sure to download the newest 'datagrid-filter.js' from https://www.jeasyui.com/extension/datagrid_filter.php

Hi Jarry,

Thank you again for your time and quick respond, now it work like a charm! thanks!
Logged
Pages: [1]
  Print  
 
Jump to:  

Powered by MySQL Powered by PHP Powered by SMF 1.1.18 | SMF © 2013, Simple Machines Valid XHTML 1.0! Valid CSS!