Excel download with serverside scripts using Buttons

ebad01

I am using Datatables to display data to the users where I am fetching data using serverside scripting. One of the feature I am required to build is excel download which I believe can be achieved by using BUTTONS (previously via table tools which are now depricated). While using buttons I am only able to download the data on display i.e. 10/25/50/100 as selected by the user and not the entire dataset (for example ~1000 rows). Googling has told me that prople have achieved this in the past but using 'oTableTools'. Can someone point me in the right direction to achive my goal using 'BUTTONS'.

Thorsten

Hi,

sample with more than 1000 rows

http://jsfiddle.net/ebRXw/724/

export only the current page is not the default behaviour.. so maybe u are using this?

exportOptions: {
modifier: {
page: 'current'
}
}

ebad01

Hi Thorsten,

Many thanks for your quick reply. I am using serverside scripting and that only has 10 rows. I am not using the exportOptions: { modifier: { page: 'current' } } option. I have hardcoded 'var dataSet' to contain a few rows from your example just to see if I can overwrite the data but it still exports only 10 rows (that are on table's display).

my table init code looks like below

dt = $('#example').DataTable( {
            dom: 'Bfrtip',
            data: dataSet,
        "processing": true,
        "serverSide": true,
        "deferRender": true,
        "fnDrawCallback": function( oSettings ) {
            $(".yesnotoggle").bootstrapSwitch();
            $(".yesnotoggle").on('switchChange.bootstrapSwitch', function (event, state) {
                toggleModuleControlParam(state, this);//set param value as requested by the user
                });
          },
        "ajax": 
            {   "url": "/includes/classes/processors/server_processing.php",
                "data":{
                        user_id : '<?php echo $_SESSION[user_id];?>',
                        sqlquery: '<?php echo $this->sql;?>',
                        primarykey :'<?php echo $this->primarykey;?>',
                        callbackfunctions:'<?php echo json_encode($this->func);?>',
                        permissions:'<?php echo json_encode($this->permissions);?>',
                }
            }, 
             "columns": [
                         <?php if (($this->permissions[action_view_records])||($this->permissions[action_edit_records])){?>
                         {
                             "class":          "details-control",
                             "orderable":      false,
                             "data":           null,
                             "defaultContent": ""
                         },
                         <?php }
                         
                         $headCnt = 1;

                            foreach($headers as $k=>$header){
                                //if(in_array($header, $this->unwantedFieldList)){ 
                                
                                if($headCnt<(count($headers)-1)){
                                    echo "{ \"data\": \"$headCnt\" },";}
                                else{
                                echo "{ \"data\": \"$headCnt\" }";
                                break; 
                                    /*  { "data": "1" },
                                        { "data": "2" },
                                        { "data": "3" }
                                    */
                                }
                                $headCnt++;
                         }?>
                        
                     ], 
                     buttons: [
                                    {
                                        extend: 'excelHtml5',
                                        title: 'ExcelTest'
                                    },
                                    {
                                        extend: 'csvHtml5',
                                        title: 'CsvTest'
                                    }
                                    ],
                     <?php if($this->unwantedFieldList){//to hide unwanted columns from the table ?>
                     "aoColumnDefs": [
                                    
                     <?php $fieldCnt = 1;
                        foreach($headers as $k=>$header){//loop through all the headers
                                if(in_array($header, $this->unwantedFieldList)){ //check if this header should be hidden
                        ?>{
                                "bSearchable": false,
                                "bVisible": false,
                                "aTargets": [<?php echo $fieldCnt;?>]},
                                
                     <?php 
                                $fieldCnt++;
                            }
                        }?>
                        ],
                     <?php }?>
                     "order": [[1, 'asc']]
    } );

ebad01

A quick point about my code above, Line 3 data: dataSet, should be removed. I only added this to try your suggestion, else we are giving 2 data sources i.e. other one is on line 13 onwards 'ajax:' which is what I wish to use for entire dataset download as excel.

Thanks,
Ebad

Thorsten

I am not using .php so i cant tell you if there is something wrong in your php code. But i can show you the way i am using the datatable and his buttons. Maybe it helps.

<table class="table table-striped table-bordered table-hover" id="sampleGrid">
                            <thead>
                                <tr>
                                    <th style="display:none;">Id</th>
                                    <th>Column1</th>
                                    <th>Column2</th>
                                </tr>
                            </thead>
</table>

jQuery("#sampleGrid").dataTable({
                dom: 'Bfrtip',
                bStateSave: true,
                bFilter: true, 
                bInfo: true,
                processing: true,
                sAjaxDataProp: "",
                aoColumns: [
                    { "mData": "Id" },
                    { "mData": "Column1" },
                    { "mData": "Column2" }
                ],
                sAjaxSource: "/api/SampleApi/GetSampleData", // Ajaxcall
                buttons: [
                {
                    extend: 'excelHtml5',
                    title: 'SampleDataExcelExport'
                },
                {
                    extend: 'csvHtml5',
                    fieldSeparator: ';',
                    title: 'SampleDataCsvExport'
                }
                ],
                lengthMenu: [
                    [15, 20, 25, 50, 100, 200],
                    [15, 20, 25, 50, 100, 200]
                ],
                pageLength: 15,
                columnDefs: [
                    {
                        targets: [0],
                        visible: false,
                        searchable: false
                    }
                ],
                order: [
                    [1, "asc"]
                ]
            });

GetSampleData is an WebApiController-Method which return and
IEnumarable<SampleDataDto>.

SampleDataDto looks exactly like the Columndefinition

ebad01

Hi Thorsten, thanks.

Q1- Does GetSampleData contain all the records (~1000 e.g.) in it and this get paginated by datatables?? In my case server_processing.php only returns 10 rows which is returned as json. If I remove my limit it gets really slow while returning everything and still paginates.
Q2- Are we expecting to make a subsequent call to the server side script upon user click on the 'Excel' (file download) button. Looking at the code I understand that we are only exporting whatever we have at the client and no call is made.

Thorsten

Hi,

Q1: Yes.
Q2: I am not sure about that but i think this is exactly your problem. seems like youre are loading data on demand? what happens if u click some pages first and than hit the export button, does he still only export 10 rows or 10xPages clicked?

buttons.exportData()

this function is used by the excel export