Using Exp to excel from tables with different number of columns

Learn2excel · January 11

Hello All,

I am new to development. I am trying to use the below piece of code for export to excel from datatable. There are two web pages that will be using this code for their datatables. The problem here is, the number of columns is not the same in both of these pages. In one of the page, there are 7 columns visible on the page, I need only from columns 2nd to 7th to be exported, the 1st column is not required. In the other page, there are 3 columns visible on the page, I need only from columns 2nd and 3rd to be exported, the 1st column is not required. I am not sure how to add a condition to fulfill this requirement.

Additionally, for your information, in the source file, separate table IDs have been given for the datatables in these two pages. For the page which has 7 columns, the table ID is tblPDM and for the one that has 3 columns is tblUtls.

Can someone let me know what additional piece of function or code is needed here so that the export to excel will work in both pages according to the columns on that page.

Thank you in advance.

buttons: [
{
extend: 'excelHtml5',
text: ' Export to Excel',
className: 'btn-sm btn_size',
titleAttr: 'Export to Excel',
exportOptions: {

allan · January 11

This example shows how you can determine which columns should be included in an export.

You could for example add an "export" class to the columns you want exported and then just use:

                exportOptions: {
                    columns: [ '.export' ]
                }

Allan

Learn2excel · January 12

Hi Allan

Thank you for your response.

I would like to understand a bit more how I am supposed to use the code provided by you. As I mentioned in my case, there are two datatables that use the code line for export to excel from the same file and I need to add some condition (may be if-else function which has instructions such that it will look for the table ID and accordingly run the code for the number of columns in that particular page).

Thank you

allan · January 12

If I've understood correctly, you want to export different columns from each table. Is that correct? Assign an export class to the columns you want to export (columns.className) and then use the code I suggested to tell the export button want columns to select.

If I've misunderstood, my apologies. Please link to a test case showing the issue so I can understand better.

Allan

Learn2excel · January 12

Hi Allan

Thanks for your response.

Just to be clear on what is my question here. There are two different webpages which are using datatables. The source code for both of them is in one JS file. The pages do not have the same number of columns. The step of adding the Export to Excel buttons in both of them is done. Had the number of columns been same, I would have used the standard code for Export to excel. But, the problem is the number of columns are not the same in both of the datatables as mentioned in my original question. To make the Export to Excel button to work on both of these pages, I need to add some condition (based on the table IDs for the individual datatables) inside the exportOptions property so that it needs to check from which page the Export to excel button is being used and accordingly run the code so the data is exported accordingly for that page. If you could give me the full code logic continuing with the below code that can be used, I would be able to check.

buttons: [
{
extend: 'excelHtml5',
text: ' Export to Excel',
className: 'btn-sm btn_size',
titleAttr: 'Export to Excel',
exportOptions: {

Thank you

kthorngren · January 12

Assuming you are using the same JS code to initialize Datatables you can use a Javascript ternary operator to set the columns array. I created an example for you that uses the same code on two different pages each having a different table ID. The example first gets the table ID on the page then uses it to determine the column indexes to use in exportOptions.

example exports 3 columns:

https://live.datatables.net/saboqepe/1/edit

example2 exports 2 columns:

https://live.datatables.net/yenuruke/1/edit

Is this what you are looking for?

Kevin

Learn2excel · January 12

Hi @kthorngren

Thank you for your response.

I tried the code given by you, but it only works partially.

Below is the code that I have used based on your suggestion. If I use tblPDM as tableID, I get only the first 2 columns exported for both the pages. If I use 'tblUtls' as the tableID and switch the column indexes the export works for all the columns of table with ID tblPDM, but for the other page the Export to Excel button does not run and gets stuck instead on clicking.

Using tableId as tblPDM

buttons: [
{
extend: 'excelHtml5',
text: ' Export to Excel',
className: 'btn-sm btn_size',
titleAttr: 'Export to Excel',
exportOptions: {
columns: tableId === 'tblPDM' ? [1, 2, 3, 4, 5, 6] : [1, 2],
orthogonal: null,
},

Using tableId as tblUtls
buttons: [
{
extend: 'excelHtml5',
text: ' Export to Excel',
className: 'btn-sm btn_size',
titleAttr: 'Export to Excel',
exportOptions: {
columns: tableId === 'tblUtls' ? [1, 2] : [1, 2, 3, 4, 5, 6],
orthogonal: null,
},

Also, the table id that I mentioned was taken from the Elements tab in the browser tools. But I don't see this id mentioned in the source file. I could just see this code related to tableId.

const tableId = "#" + $(this).attr("id");

So, not sure how can I add condition for tableID. Can you let me know if I am doing something wrong here? and what would be the fix for this.

Thank you!

kthorngren · January 12

other page the Export to Excel button does not run and gets stuck instead on clicking.

Take a look at the browser's console for errors.

You are prepending a # to the tableId variable:

const tableId = "#" + $(this).attr("id");

But are comparing without the #:

tableId === 'tblUtls' ? [1, 2] : [1, 2, 3, 4, 5, 6],

Make sure to use the#in both places or not at all. Note my example does not use the#. Does thetableId` variable get the proper table ID?

We don't have enough information about your solution to provide a specific solution. If you still need help then please provide a link to your page or build a simple test case containing one of the tables and your Datatables JS code so we can provide more specific help.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

Learn2excel · January 19

Hi @kthorngren

Thank you very much for your response. yes I used # key in the code and it made the code work for me.

Thank you

Best Regards
Rajagopallan

Learn2excel · January 31

Hi @kthorngren

In the previous approach, as you suggested I used the below code for export to excel button.

But if I am not to use the Table id in the code, but instead use a parameter to the function to pass the export columns, how it can be done. Also, I do not want the first column to be exported in both the tables.

Can you let me know how we can achieve this?

buttons: [
{
extend: 'excelHtml5',
text: ' Export to Excel',
className: 'btn-sm btn_size',
titleAttr: 'Export to Excel',
exportOptions: {
columns: tableId === '#tblPDM' ? [1, 2, 3, 4, 5, 6] : [1, 2],
orthogonal: null,

Thank you

Regards
Rajagopallan

kthorngren · January 31

instead use a parameter to the function to pass the export columns

I think you will want to call the function something like this:

      exportOptions: {
        columns: exportColumns( param )
      }

Then have the function return an array of columns to export. Use this example as a guide. Note that you can create an array of any combination of options documented in column-selector.

Kevin

Learn2excel · February 1

Hi Kevin

Thanks for your response.

Sorry but I didn't quite understand the step. Could you let me know how the function can be defined and what column selector needs to be used in one complete sample code, it would help me. Thanks.

Regards
Rajagopallan

rf1234 · February 1

This post covers pretty much everyhting:
https://datatables.net/forums/discussion/77996/more-flexibel-excel-export

kthorngren · February 1

Here is an example with a function based on using tableId === 'tblUtls' ? [1, 2] : [1, 2, 3, 4, 5, 6] that we discussed above.
https://live.datatables.net/gozisiwe/2/edit

You can change the function and the parameter sent to the function based on your specific requirements.

Kevin

Using Exp to excel from tables with different number of columns

Using Exp to excel from tables with different number of columns

Answers

example exports 3 columns:

example2 exports 2 columns:

Howdy, Stranger!

Categories

DataTables

Using Exp to excel from tables with different number of columns

Using Exp to excel from tables with different number of columns

Answers

example exports 3 columns:

example2 exports 2 columns:

Howdy, Stranger!

Quick Links

Categories

DataTables