Author Topic: Export to excel  (Read 4770 times)

queensgambit9

  • Pro Ultimate
  • Sr. Member
  • *
  • Posts: 341
    • View Profile
Export to excel
« on: December 05, 2018, 08:47:26 pm »
Would it be possible to only export visible columns (5.6.0)?

paramvir

  • Administrator
  • Hero Member
  • *****
  • Posts: 6113
    • View Profile
Re: Export to excel
« Reply #1 on: December 05, 2018, 09:02:55 pm »
By default, visible columns are visible and hidden columns are hidden in Excel export.

If you want the hidden columns to remove altogether from Excel export, then

  1) for non - grouped columns: you have to add copy: false to the hidden columns.

  2) for grouped columns, you have to remove hidden columns from colModel in beforeExport event and restore original colModel in export event.
« Last Edit: December 05, 2018, 09:06:41 pm by paramquery »

queensgambit9

  • Pro Ultimate
  • Sr. Member
  • *
  • Posts: 341
    • View Profile
Re: Export to excel
« Reply #2 on: December 07, 2018, 12:52:32 pm »
Having issue to get it right (column titles seems mixed up)...

beforeExport: function( event, ui ) {

var Cols = grid.Columns();
Cols.alter(function(){
    Cols.find(function(column){
        return (column.hidden==true);
    }).copy = false;
})
}

paramvir

  • Administrator
  • Hero Member
  • *****
  • Posts: 6113
    • View Profile
Re: Export to excel
« Reply #3 on: December 07, 2018, 03:27:50 pm »
Yes it's not working right.

Please use hideCols event which is fired when columns are shown/hidden via dropdown menu.

Code: [Select]
hideCols: function(evt, ui){
   var grid = this;
   ui.diHide.forEach(function(di){
grid.getColumn({dataIndx: di}).copy = false;
   })
   ui.diShow.forEach(function(di){
grid.getColumn({dataIndx: di}).copy = true;
   })
},

Note that the columns which are initially hidden should also be marked as copy: false.
« Last Edit: December 07, 2018, 04:27:13 pm by paramquery »

queensgambit9

  • Pro Ultimate
  • Sr. Member
  • *
  • Posts: 341
    • View Profile
Re: Export to excel
« Reply #4 on: December 07, 2018, 04:30:12 pm »
Thanks works fine with 5.6.0.
Is there a solution for version 5.1.0 (old columnselector)...doesn't update the copy value (true, false)?
« Last Edit: December 07, 2018, 04:38:16 pm by queensgambit9 »

queensgambit9

  • Pro Ultimate
  • Sr. Member
  • *
  • Posts: 341
    • View Profile
Re: Export to excel
« Reply #5 on: January 10, 2019, 03:34:03 pm »
When loading a saved state the copy value for the columns doesnt seem to be updated correctly...
How can I update when the state is loaded?

Thanks in advance.

paramvir

  • Administrator
  • Hero Member
  • *****
  • Posts: 6113
    • View Profile
Re: Export to excel
« Reply #6 on: January 10, 2019, 03:40:02 pm »
1) column.copy values can also be saved in state by adding copy: 1 in stateColKeys

https://paramquery.com/pro/api#option-stateColKeys

2) using beforeExport event to set copy: false to columns is also fixed in v5.6.1

queensgambit9

  • Pro Ultimate
  • Sr. Member
  • *
  • Posts: 341
    • View Profile
Re: Export to excel
« Reply #7 on: January 10, 2019, 05:16:55 pm »
Trying:

Code: [Select]
beforeExport: function(evt, ui ) {


ui.diHide.forEach(function(di){
grid.getColumn({dataIndx: di}).copy = false;
})
ui.diShow.forEach(function(di){
grid.getColumn({dataIndx: di}).copy = true;
})

},

But ui.diHide etc doesn't seem to be available...?

paramvir

  • Administrator
  • Hero Member
  • *****
  • Posts: 6113
    • View Profile
Re: Export to excel
« Reply #8 on: January 10, 2019, 09:47:22 pm »
ui.diHide is available in hideCols event only.

beforeExport event ( alternative way to set copy: false ) is used to set copy:false on hidden columns just before exporting. Please refer previous messages in this post.
« Last Edit: January 11, 2019, 10:48:49 am by paramquery »

queensgambit9

  • Pro Ultimate
  • Sr. Member
  • *
  • Posts: 341
    • View Profile
Re: Export to excel
« Reply #9 on: January 11, 2019, 02:55:02 pm »
Trying:

Code: [Select]
beforeExport: function( event, ui ) {

var Cols = grid.Columns();

Cols.alter(function(){
    Cols.find(function(column){
        return (column.hidden==true);
    }).copy = false;
})
}

But it only seems to be applied to first column.

paramvir

  • Administrator
  • Hero Member
  • *****
  • Posts: 6113
    • View Profile
Re: Export to excel
« Reply #10 on: January 11, 2019, 04:44:38 pm »
That's understandable considering Cols.find returns a single column.

Please use each method.

Code: [Select]
Cols.each(function(column){
        column.copy = !column.hidden;
})

https://paramquery.com/pro/api#method-Columns

PS: Please refer the docs first when in doubt.
« Last Edit: January 11, 2019, 04:47:27 pm by paramquery »

queensgambit9

  • Pro Ultimate
  • Sr. Member
  • *
  • Posts: 341
    • View Profile
Re: Export to excel
« Reply #11 on: January 11, 2019, 05:33:08 pm »
I will, thanks for help.