Export Buttons with Different Searches

AKASecret

Link to test case: https://live.datatables.net/gikufecu/1/edit?html,js,output
Description of problem: I want different export buttons that apply different filters when exporting. For example, I want an "Export System Architects" button, and an "Export Software Engineers" button. I don't understand how to set up these exportOptions, so I've included 3 different ways that I've tried unsuccessfully. (Also, I can't seem to get the buttons to display in the test case, but they appear in my working environment.)
Some of the Documentation I tried to implement:
https://datatables.net/extensions/buttons/examples/print/select.html
https://datatables.net/extensions/buttons/examples/initialisation/select.html
https://datatables.net/extensions/buttons/examples/html5/excelAutoFilter.html (how do I apply the filter without having to take action on the client first?)
I apologize if this has already been asked. I searched for "export" in the forums and went through the first 10 pages or so and couldn't find a similar question.
https://datatables.net/forums/discussion/41958
https://datatables.net/forums/discussion/75280
https://datatables.net/forums/discussion/comment/214196/#Comment_214196

A follow-up question: I want different columns displayed for each export. Is there a way to change the column names per export? So something like:
columns: [0, 1 title: "Title", 2, 3, 4],
for one export and
columns: [0, 1 title: "Job Title", 2 "Department", 3, 4],
for another export?

tangerine

This doesn't fix your buttons problem, but you should at least know that your loading sequence is wrong.
jQuery first
then DT
then Buttons.

kthorngren

Create a custom button, see this example. See the last example in the buttons.buttons.action docs to see how to call the export you are interested in. Before calling the export use search() or column().search() to apply the appropriate filter.

Kevin

AKASecret

Updated bin with jquery references in order now (thank you, @tangerine ).
https://live.datatables.net/gikufecu/7/edit?html,js,output

The buttons still don't work, though...I don't see the extended Excel button, and the custom button displays but doesn't do anything. (I don't know if that's because it's in the editor by design or if my code doesn't work.)

Anyway, @kthorngren I added the draw() and then called the Excel function, but I don't think this is what you meant. If I do it this way, it performs the search on the table first and then exports the file without the search filters applied. I added the last line to hopefully reset the table back to how it started after the export, but that doesn't seem to work, either.
I don't understand how to implement the file options once I call the excelHtml5 action. Where do I set what columns and headers I want to display in the output file, the title of the file, etc.?

kthorngren

Actually the first button with rows using a function as the selector is a good one. You didn't have the Excel and JSZip libraries. I used the download builder to generate the proper libraries:
https://live.datatables.net/gikufecu/8/edit

Kevin

AKASecret

@kthorngren Thank you for your prompt response, (and for correcting the button displays) but it still doesn't answer my question. The first button works except I need to perform the column().search() function before exporting, preferably without altering the table (like using draw()) except for the export.

The first button displays the columns I need, the title of the file, all of that, except it doesn't apply the search, so it exports all the data.
The second button applies the search, exports a file with no customization, and does not refresh the data to remove the search.
The intended behavior is to click a button that says "Export ___", and it would generate a file with specified columns, different header names, and only rows that fit a specific search.

For example, the Export SA button would export a file that looks like this:

And the Export SE button would export a file that looks like this:

kthorngren

The first button displays the columns I need, the title of the file, all of that, except it doesn't apply the search, so it exports all the data.

I might misunderstand what you mean but the first button exports only the rows with Software Architect. That would be the same as the search you have in the second button:

table
                        .columns(1)
                        .search('System Architect')

The second button applies the search, exports a file with no customization, and does not refresh the data to remove the search.

You can remove the draw(). You will need to reset the column search not the global search, like this: table.column(1).search('');. I'm not sure why the headers don't show with excelHtml5.action.call.

I still think the first button is the better option. This thread might help with customizing the header.

Kevin

AKASecret

Final working version:
https://live.datatables.net/gikufecu/14/edit?html,js,output

The only thing I'm having trouble with is with the filtering...it works in the example, but I can't get it to work in my environment. Each time, it exports the report with no data. It's not a HUGE deal because I have a workaround, but I can't figure out why I can't get it to work. I'm using server-side processing, could that be the issue?

I've tried updating Datatables, and even ended up commenting out all of my other JS libraries and instead just using the ones in the example, but I still can't get it to filter the column in the export.

I've tried:

rows: function (idx, data, node) {
    return data[1] === 'System Architect' ? true : false;
},

I've tried:

rows: function (idx, data, node) {
    return data["Position"] === 'System Architect' ? true : false;
},

I've tried:

rows: function (idx, data, node) {
    if (data[0] == "ACH") {
        return true;
    } else {
        return false;
    }
},

In my final version, I don't want to display the filtered column, so I've tried it with and without that column, and STILL I can't get it to work.
columns: [2, 3, 4],
and
columns: [1, 2, 3, 4],

At this point, I'm going to call it complete, but if anyone has anymore insight please let me know.
Thank you for your help.

kthorngren

With server side processing the only data that can be exported is the rows being shown on the page. If the data you are searching for isn't shown on the page it won't find anything. I updated your example to use server side processing:
https://live.datatables.net/gikufecu/15/edit

The Export ACH button exports no rows since System Architect is not on the page. But the Export EBT does work since Software Engineer is on the page. See this FAQ.

I don't want to display the filtered column

not sure if I understand the issue but in the test case you have columns: [1, 2, 3, 4],. The column indexes start at 0. If you don't want to export the Position column, in this case, then use something like columns: [0, 2, 3, 4],. This will export Name, skip Position, then the rest of the columns specified.

Kevin

AKASecret

@kthorngren This is the case with my data - although I'm using server side processing, both sets of data appear on the same page, and each time it exports all the data. (In other words, the datatable shows 10 rows. There are 6 rows of data: 4 rows have "ACH" and 2 rows have "EBT". Both buttons export all the data regardless of the filter.)

I'm bringing this up again because I've now run into a similar problem with removing the formatting of a column in the export. It's as if the "exportOptions" is not reading the data at all, and I don't know how to step through the process to find out what data is being returned.

Everything works as expected in this example:
https://live.datatables.net/gikufecu/18/edit?html,js,output

but in my environment, it's like the export doesn't read the "data" in the column, as if it's pointing to the wrong column or the data is not formatted in a way that can be read.

I have a column formatted as currency. On the export I want to remove the formatting. Using this example, I revised my code as follows:

exportOptions: {
    columns: [0, 2, 3, 4, 5],
    rows: function (idx, data, node) {
        return data[1] === 'Accountant' ? true : false;
    },
    format: {
        header: function (data, columnIdx) {
            // header options
            }
        },
        body: function (data, row, column, node) {
            // remove $ from Amount column to make it numeric
            return column === 5 ?
                data.replace(/[$,]/g, '') :
                data;
        }
    }
}

(The example in the test case does not have the Salary column formatted as currency, so I can only assume it works.)

I feel like it's the "data" that's the problem, because first I want to return the row if the data[1] === 'Accountant', and then I want to change the format of the data (in a different column). My current end result is returning all rows without the filter, and the Salary column still formatted as currency instead of a number. I tried changing
data[1] === 'Accountant'
to
data["Position"] === 'Accountant'
with the same result. I don't know what other tricks I can use to see what values the export is reading.

Can you think of any other troubleshooting steps I can take?

kthorngren

Use data[1] === 'Accountant' is you data is array based. Use data["Position"] === 'Accountant' if you data is object based, ie using columns.data. See the Data docs for details.

Can you think of any other troubleshooting steps I can take?

Use a browser break point or console.log statement in this code to see what data is.

    rows: function (idx, data, node) {
        return data[1] === 'Accountant' ? true : false;
    },

The example in the test case does not have the Salary column formatted as currency, so I can only assume it works.

It almost works. I updated the example to format the Salary column. The column parameter of body: function (data, row, column, node) { represents the column of the Excel spreadsheet not the table column. You are exporting this columns: [0, 2, 3, 4, 5],. The Salary column will be Excel column 4 not 5.

https://live.datatables.net/gikufecu/19/edit

Kevin

AKASecret

Adding a console.log statement to the function solved all my problems! I found out that:
* For return data[1] I need to use what the column is called in the database; not the column index, and not the column name. I should have made the connection that the "data" in the exportOptions body is the same as the "data" in the columns. It now filters my results as intended.
* I now understand what you were saying with your last statement. The exportOptions > body columns are NOT the same as the exportOptions columns; the exportOptions body columns are the columns in the export, not the columns in the table. In my environment, the "Amount" column is column 10. When I added the console.log statement, I found out that the export was never reaching column 10 so it wasn't getting formatted. This is because the Amount column is column 4 in the export.

Final working code with markup for my own understanding:
https://live.datatables.net/gikufecu/20/edit?html,js,output

Thank you so much for all of your help!