Modifying datatable function to use export to excel functionality

Learn2excel

Hello All,

I am new to development. I need help with the following.

There are two web pages that will be using the export to excel code and this is kept in a single .js file. The problem here is, the number of columns is not the same in both of these web pages. In one of the page, there are 7 columns. In the other page, there are 3 columns. Additionally, I also do not need the first column to be exported from both the pages.

Below is the function used to initialize datatable

$.fn.createDataTable = function (columns, ajaxOptions, order, initComplete = null)

Below is the function that I want to use for export to excel button:

buttons: [
{
extend: 'excelHtml5',
text: '<i class="btn_excel"></i> <span class=btn_text_align>Export to Excel</span>',
className: 'btn-sm btn_size',
titleAttr: 'Export to Excel',
exportOptions: {

Now I want to modify the main function createDataTable in the .js file to take columns and adjust the logic accordingly on pages that uses it so that the export to excel button works in both the pages correctly.

I am not sure what logic i need to use to achieve this requirement. If you are looking to help me on this, request you to provide me a full solution so that I can adjust any minor things to suit my code.

Thank you

Regards
Rajagopallan

kthorngren

This example and this example shows how to define the columns exported. You can define a classname, using columns.className for the column(s) to not export then use the :not() to export all columns not containing the className.

Kevin

Learn2excel

Hi Kevin

In both the example links, I see use of column visibility as a button to define the columns to be exported. I do not want use column visibility as a button to define the columns. I want to add a logic in the datatable function where columns is being passed as a parameter so that it understands how many columns are there in the particular webpage automatically and make the export to excel button to work accordingly when clicked.

Regards
Rajagopallan

kthorngren

Yes, the example is a column visibility example but the usage is the same for exporting. It would look something like this:

            columnDefs: [
                {
                    targets: 0,
                    className: 'noExport'
                }
            ],

            buttons: [
                {
                    extend: 'excelHtml5',
                    exportOptions: {
                        columns: ':not(.noExport)'
                    }
                },

However if you prefer to pass in a columns parameter then you can use standard Javascript methods to create a variable with column indexes, based on the length of the array, with 0 removed. Resulting in something like [1, 2] for a 3 column table. Then use that as the columns value, for example:

                {
                    extend: 'excelHtml5',
                    exportOptions: {
                        columns: myColumnIndexes
                    }
                },

Kevin

Learn2excel

Hi Kevin

Thanks for your response.

If you could actually give me a standard Javascript method for example that uses the columns parameter in the function when datatable is initialized it would be helpful.

$.fn.createDataTable = function (columns, ajaxOptions, order, initComplete = null)

Thank you

Regards
Rajagopallan

allan

You mean you want us to create a jQuery plugin called createDataTable, or that you already have one?

I don't understand why you would want that when DataTables is already available as a jQuery plugin?

Allan

Learn2excel

HI Allan

I don't want to create any plugin. The function createDataTable is already there in the code as mentioned in my first post.

Now I want to modify this main function createDataTable to take columns and adjust the logic accordingly on pages that uses it. Below is the code snippet for the export to excel button. So if you can let me know how this can be achieved. You can check my first post on this discussion to know the full requirement if you wish to.

buttons: [
{
extend: 'excelHtml5',
text: '<i class="btn_excel"></i> <span class=btn_text_align>Export to Excel</span>',
className: 'btn-sm btn_size',
titleAttr: 'Export to Excel',
exportOptions: {

kthorngren

This is my understanding of your requirements:

In one of the page, there are 7 columns. In the other page, there are 3 columns. Additionally, I also do not need the first column to be exported from both the pages.

I interpret that to mean you want to export all the columns except the first. Do I understand the requirement correctly?

Did you try the code I posted above for this?

Add this to your Datatables config:

columnDefs: [
    {
        targets: 0,
        className: 'noExport'
    }
],

And add this to your buttons config:

columnDefs: [
    {
        targets: 0,
        className: 'noExport'
    }
],

If you need help with this then post your full Datatables config and we will show you where to past the above code. Or better is a link to your page or a running test case showing us what you have so we can offer more specific suggestions.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

Learn2excel

Hi Kevin

Yes, you've got part of requirement where I mentioned that I do not want the first column to be exported correctly. I tried adding the columnDefs to the datatable definition and also the export options. The excel sheet does hide the first column, so that part is working.

But, my other part of the requirement is, is to modify the main function of createDataTable in to take columns and adjust the logic accordingly on pages that uses it. I mean if this code is to be applied to another table which will have a different number of columns, I don't want to keep adding more if conditions every time.

$.fn.createDataTable = function (columns, ajaxOptions, order, initComplete = null, afterInitComplete = null) {
const tableId = "#" + $(this).attr("id");
const _dt = $(tableId).DataTable({

In order to achieve this, do I need to add any function while the datatable is being initialized alone or do I need to add any function under the exportOptions property of the buttons as well.

Regards
Rajagopallan

kthorngren

Take a look at this doc about setting options and default settings.

modify the main function of createDataTable in to take columns and adjust the logic accordingly on pages that uses it.

Its hard to say what you need to do. First we don't know what the columns variable contains. We haven't seen your createDataTable() function and Datatables config. The only specific requirement you have given is to not export the first column. Without specific questions or requirements its impossible to provide suggestions.

Please post a running test case showing what you have and provide any specific questions or requirements you have. This way we can offer suggestions.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin