Please specify the 'fields' parameter while calling the 'toCsv' or 'toExcel' methods. Be sure to download a newer version from https://www.jeasyui.com/extension/datagrid_export.php

Code:

$('#dg').datagrid('toCsv', {
    filename: 'datagrid.csv',
    fields: ['itemid','productid'],
    rows: rows,
    footer: [{
        productid: 'Summary',
        listprice: 30
    },{
        productid: 'Summary',
        listprice: 40
    }]
});

Hi Jarry,

Thanks again for your help. This new enhancement work pretty good and as expected.
Thanks ya bro!

Bump, anyone can please help?

Hi Sir,

Would like to ask, is there possible to export to excel/csv for specify columns only from a datagrid?

Note: I have implemented datagrid with filter and export extension, all working fine after guided by Jarry.

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!

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

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?

Please try to download again from the site.

Thank You! It work as expected now.

Please call the 'toCsv' method to export to a csv file. Make sure to download a newer version from https://www.jeasyui.com/extension/datagrid_export.php

Hi Jarry,

Thank you for your help. After I downloaded the new version of datagrid_export.js, the download to excel button is responding, it will prompt the name & location to save the csv file, however after i confirm, it just failed with network error.

Appreciate if you can further help to check again?

My Code:

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

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

Hi Sir,

Can someone help on this? I need to export all data in the datagrid to Text file(if possible tab Delimited).

I tried to check with the "toExcel" extension, but it got error every time when we open the exported excel file, which is quite annoying & troublesome. Hence need another solution for this.

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

Hi any experts can help?

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.

Hi Jarry,

Thank you, that's work.

However if I want it to initiate when the page first load, without needed to reload the datagrid, i now use queryParams.


dg.edatagrid({
                    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},
                    ]]
})

[front end code:]

Hi Sir,

I'm using editable datagrid extension, having a datagrid which data come from database. However I need to pass parameter(let say userID) to the page so that the datagrid will show different set of data based on login id.

May I know any example to achieve this?

front end code:
dg.edatagrid({
                    //title:'DataGrid - DetailView',
                    width:'auto',
                    height:'auto',
                    remoteSort:false,
                    //singleSelect:true,
                    nowrap:false,
                    fitColumns:true,
                    striped:true,
                    pagination:true,
                    multiSort:true,
                    url:'php/get_coo_data.php',
                    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}
                    ]]
})


backend code:
require_once('config.php');
userID = POST['userID'];

$sql = "SELECT * FROM item WHERE user_id = userID ";

$result = mysqli_query($sql);

$data = array();

while($row = mysqli_fetch_assoc($result)){
   $data[]   = $row;
}

echo json_encode($data);



Thanks.