Access hidden columns excel

Isabelgh

I'm using the colvis button as well as the excel button in a Datatable. The problem I'm facing is when I hide columns with the colvis button and then want to export the column cell data in the excel export customization option the columns in the excel sheet changes.

Beefore I export I need to modify some of the cells in a certain column. It is when I try to access the column where the problem lies:

let idColumn = $('c[r^="B"]', this);

Since I have hidden fields, the excel sheet columns letter changes and the id column is not found.

So I still need to access this field in the excel sheet to save the data but I don't want to export the column, it should still be hidden when downloading the file when pressing the button. Is there a way to do this?

I would very much appreciate any help I can get!

colin

Just to be clear, do you want the column to be present but hidden within the Excel spreadsheet, or do you just want to use a hidden column in the DataTable to calculate a value in the spreadsheet?

Colin

Isabelgh

Yes when I hide columns with for example: table.column( 1 ).visible( false );
I do not want that column to be displayed when exporting, but I still need the hidden column value when performing calculations in the spreadsheet, since the cell colors of a certain column depend on another column's cell value (even though it is hidden). If that makes sense?

/Isabel

kthorngren

I wonder if it would be easier to use orthogonal data to perform the calculations. See this example:
https://datatables.net/extensions/buttons/examples/html5/outputFormat-orthogonal.html

Kevin

colin

Yep, I agree with Kevin - as you don't need the column in the spreadsheet, you can use columns.render to calculate it for the export.

Colin

Isabelgh

Thank you for you answers! columns.render looks like something that might be useful, but I'm still wondering if the export will happen correctly since I currently access the columns this way from the sheet:

var sheet = xlsx.xl.worksheets['sheet1.xml'];

$('row', sheet).each( function () {

                let idColumn = $('c[r^="B"]', this);
                let rfColumn = $('c[r^="I"]', this);

                let dataRow = tableData.find(function(row) {
                  return row.dataid == idColumn.text();
                });

var rfColor = evaluateStatus(dataRow.relativehumidity, dataRow.back_min, dataRow.back_max);

So I get them directly from the sheet so the xlsx file will be exported with colored cell values. If I use the orthogonal data to perform the same calculation I'm not sure how it will work and retrieve the correct values.

kthorngren

One option would be to use columns().visible(). From this you can set variables that contain the column numbers for idColumn and rfColumn, more specifically the "B" and "I" in your above code. I updated an example to show this:
http://live.datatables.net/gazebagu/1/edit

Kevin

Isabelgh

Thank you for the example! Yes the columns().visible() API could be possible to use, although there might be a lot of cases if more than 1 of the fields are hidden.

There is still a problem though when I hide the idcolumn of the table. The whole field will disappear from the sheet and I still need the cell values of the id column when performing the calculations for the RF column.

So when I hide the idcolumn I will not be able to set the column number for it in excel?

/Isabel

kthorngren

Yes the columns().visible() API could be possible to use, although there might be a lot of cases if more than 1 of the fields are hidden.

Yep. You will need to take that into consideration in your code.

The whole field will disappear from the sheet and I still need the cell values of the id column when performing the calculations for the RF column.

This is why we suggested using orthogonal data to perform that calculation for the "export" (from the example) type in the RF column.

So when I hide the idcolumn I will not be able to set the column number for it in excel?

Do you want to export this column when its hidden? This example may help.

I think the easiest way to help you now is for you to put together a simple example that represents your data. This will allow us to give specific answers. Feel free to update my example.

Kevin

Isabelgh

Thank you for all your help I really appreciate it, I will go through it all and try to provide an example that represents my data.

Another quick question that I have is that when exporting to excel, will the table row ordering always be kept in the excel sheet? So the order of the rows in the exported excel table will be the same as it is in the Datatable?

colin

This thread should help, it's asking the same thing.

Cheers,

Colin