Export all row without drawing all row

datatable_user

Below is a working code to generate excel. It will draw all row and export the data. After that it will return the table to it's previous state. Which mean if there's initially 10 row per page, it will revert back to 10 row per page.

                        buttons: [
                            {
                                extend: 'excelHtml5',
                                text: 'Excel',
                                exportOptions: {
                                    columns: ':visible'
                                },
                                title: '',
                                action: function (e, dt, node, config) {
                                    var dtButton= this; //we need this as param for action.call()
                                    var currentPageLen = dt.page.len();
                                    var currentPage = dt.page.info().page;

                                    //draw all before export
                                    dt.page.len(dt.page.info().recordsTotal).draw();

                                    dt.one( 'draw', function () {
                                            $.fn.DataTable.ext.buttons.excelHtml5.action.call(dtButton, e, dt, node, config); //trigger export

                                            //setTimeout is needed here because action.call is async call
                                            //without setTimeout, pageLength will show all
                                            setTimeout(function() {
                                                    dt.page.len(currentPageLen).draw(); //set page length
                                                    dt.page(currentPage).draw('page'); //set current page
                                            }), 50;
                                    });


                                }
                            }
                        ]

The problem is the method of drawing all row and return back which might confuse the user. Initially user only see 10 row and all the sudden 100 row and revert back to 10 row. I was wondering how can I generate all row to excel without changing the user experience. Thank you.

kthorngren

Are you using server side processing, ie have serverSide set true? If yes, why do you have server side processing enabled with only 100 rows? Remove serverSide: true to fetch all 100 rows when the table loads and remove the action part of the button config.

If this doesn't help then please post the full Datatables config so we can get a better picture of what you have.

Kevin

datatable_user

Yes "serverSide": true, and I set datatable to only retrieve first 50 row. When user click page 6, another backend call will be made to retrieve the next 50 row.

I cannot set it to retrieve all row at initial load because huge number of row and user might not always want to export to excel.

Thanks for response

datatable_user

I'm thinking of work around like when user click button on exporting to excel, I will create another temporary table (this table is invisible/in the background). Call backend with the temp table, export excel and destroy temp table.

I will try it and get back to you guys. Thanks.

allan

See this FAQ regarding the download of the full data set when server-side processing is enabled.

Allan

kthorngren

If you need to use server side processing the pulling the data client side for exporting defeats the use of server side processing. A better option is to export the data server side as explained in this FAQ. See the linked plugin for sending Datatables information to the server script to allow it to fetch the proper data.

I will create another temporary table (this table is invisible/in the background).

That's one option. Another might be to use jQuery ajax to fetch the data in the action function. Possibly the async nature of this won't affect the user's experiance.

Kevin

datatable_user

I try to create temp table when user click export button. For some strange reason, there's no backend call when I check chrome developer network tab. It suppose to be an error because file scripts/server_processing.php does not exist. Console.log is trigger though and I can see the temp_table properties in chrome console tab.

Please somebody provide me insight what's going on here.

                            {
                                extend: 'excelHtml5',
                                text: 'Excel',
                                exportOptions: {
                                    columns: ':visible'
                                },
                                title: '',
                                action: function (e, dt, node, config) {
                                    var dtButton = this; // We need this as param for action.call()
                                    var currentPageLen = dt.page.len();
                                    var currentPage = dt.page.info().page;

                                    // Initialize the temporary DataTable
                                    var temp_table = $('#temp_table').DataTable({
                                        serverSide: true, // Enable server-side processing
                                        ajax: {
                                            url: 'scripts/server_processing.php',
                                            type: 'POST'
                                        }
                                    });

                                    // For debugging purposes, log the table object
                                    console.log('temp_table', temp_table);

                                }
                            }

kthorngren

there's no backend call when I check chrome developer network tab. It suppose to be an error because file scripts/server_processing.php does not exist.

Even if cripts/server_processing.php doesn't exist you should still see an ajax request with a 404 not found response. My guess is a table with the ID of temp_table is not found so the Datatables initialization doesn't work. Without a test case its hard to debug what the problem might be. Please provide a test case replicating the issue so we can help debug.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

You can find some server side processing templates to start from here.

Kevin

datatable_user

My guess is a table with the ID of temp_table is not found

That is the cause actually. Thanks

datatable_user

As requested, I provide the test case : https://jsfiddle.net/wLhgnju2/1/

I also use $.fn.dataTable.pipeline where I retrieve the first 50 row, but I could not get it to work in test case so I leave it out from the test case.

I'm getting this error with jsfiddle: Uncaught TypeError: Cannot read properties of undefined (reading 'aDataSort') when I click Excel btn. but actually on my local I'm getting different error : VM40351:375 Uncaught TypeError: this.processing is not a function

Please help me. Thank you

allan

You are creating a new DataTable, but there are no columns defined. The table is empty and you don't specify the columns parameter to define columns for your temp table.

I'd urge you to step back for a moment though. To my mind, download the entire data set to the client-side doesn't make any sense. You have chosen to use server-side processing, presumably because your data set is large (50k or more records). However, if you download all the data to the client-side for export, then all you are doing with server-side processing is introducing network latency, since it needs to request the data for very draw.

Also by the time you need server-side processing due to large data sets, the export on the client-side is going to struggle.

If you need server-side processing and you need export, then (imho) you need to build the export files at the server-side.

Allan

p.s. You are using DataTables 1.x which is no longer supported.

datatable_user

You are creating a new DataTable, but there are no columns defined. The table is empty and you don't specify the columns parameter to define columns for your temp table.

After I added the column in temp table, the error is similar to my local. Here is updated code : https://jsfiddle.net/wLhgnju2/3/

What do you mean the table tag is empty?

I appreciate your suggestion. I am researching about Apache POI

allan

What do you mean the table tag is empty?

Just that. It is simple <table id="temp_table" class="table"></table>. i.e. it is empty.

                  var table_tmp = $('<table>', {
                     id: 'temp_table',
                     class: 'table'
                  });

this.processing is not a function

This line of code:

$.fn.DataTable.ext.buttons.excelHtml5.action.call(temp_table, e, temp_table, node, config);

I don't know why you are passing temp_table as the scope for the function call. It should be a DataTables Buttons scope. It is actually more complex than just that though, since you are creating a new table and you need to execute the function with the scope based on that table. I'm actually not 100% sure how that would be done, if it is even possible.

You'd need to create a temp DataTable with the full data set and an export button configured for it, and trigger that button (button().trigger()).

However, if you do that, then you are creating a whole load of work to support server-side processing, and then just creating a client-side table anyway!

You haven't said how many rows / columns you are working with here. Is server-side processing required?

Allan

datatable_user

  buttons: {
    name: 'primary',
    buttons: [
      {
        extend: 'excel',
        text: 'Export to Excel',
        className: 'btn-sm btn-primary', // Add your desired class here
        attr: {
          id: 'excelButton' // Add an ID here
        },
        action: function (e, dt, button, config) {
          // Custom action to be executed on button click
          alert('Export button clicked');
        }
      }
    ]
  }

For a button like above, how do I use this table.button('2-1').trigger(); I get the code from the manual but unsure how to do it. Can I run table.button('2-1').trigger(); from browser console? Thanks

kthorngren

table.button('2-1').trigger();

Likely the button-selector wont' be 2-1. Most likely it will be an integer representing the buttons' index. 2-1 would be used if you have a button collection as explained here.

If you still need help then, at a minimum, post your full Datatables init code. Better is a test case showing what you are trying to do.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

datatable_user

Hi, thanks for the response. I provide test case url : https://jsfiddle.net/yaor6g1s/

The export works on first click. On 2nd click dt.one('draw', function () { is not triggered. I don't understand why. I appreciate some help.

On that jsfiddle site you need to click sort icon so the export will trigger. I don't know why. I don't have that problem on my local. My problem now is export wont trigger on 2nd download click.

kthorngren

One problem is you need to create the dt.one('draw', function () { event handler before executing dt.page.len(dt.page.info().recordsTotal).draw(); so it executes after this particular draw() call.

Updated test case:
https://jsfiddle.net/d36jeafm/1/

Kevin

datatable_user

hey thanks. Your reply and everyone here help me tremendously.