Print Page - datagrid with extension datagrid-filter, export filtered data to excel.

Title: datagrid with extension datagrid-filter, export filtered data to excel.
Post by: Franky 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.

Title: Re: datagrid with extension datagrid-filter, export filtered data to excel.
Post by: Franky on January 31, 2021, 06:03:26 PM

Hi any experts can help?

Title: Re: datagrid with extension datagrid-filter, export filtered data to excel.
Post by: jarry 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
    }]
})

Title: Re: datagrid with extension datagrid-filter, export filtered data to excel.
Post by: Franky on February 02, 2021, 09:58:23 PM

Quote from: jarry 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
    }]
})

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....."

(http://)

Title: Re: datagrid with extension datagrid-filter, export filtered data to excel.
Post by: Franky on March 05, 2021, 01:43:30 AM

Quote from: jarry 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
    }]
})

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?

Title: Re: datagrid with extension datagrid-filter, export filtered data to excel.
Post by: jarry 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.

Title: Re: datagrid with extension datagrid-filter, export filtered data to excel.
Post by: Franky on March 07, 2021, 10:47:10 PM

Quote from: jarry 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.

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);

Title: Re: datagrid with extension datagrid-filter, export filtered data to excel.
Post by: jarry 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

Title: Re: datagrid with extension datagrid-filter, export filtered data to excel.
Post by: Franky on March 08, 2021, 09:48:07 PM

Quote from: jarry 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

Hi Jarry,

Thank you again for your time and quick respond, now it work like a charm! thanks!

EasyUI Forum

General Category => EasyUI for jQuery => Topic started by: Franky on January 28, 2021, 01:05:52 AM