grid and download it to Excel

[Kurt Schrauwen]

Dears,

There is an example to take any grid and download it to Excel.
This works great but is it also possible to download only the visible columns to the excel file and not all the columns ?
When I take that example and I put one column invisible it still appears in the excel file.

Greetings,
Kurt

[sean.lanktree]

Yes - it is possible, but not without adding any code. In order to accomplish this, we would need to do the following:


Determine the visible columns on the screen and store them into an array
Pass this array to the back-end program
Pull the array into the back-end program
Build the sql statement only selecting the fields passed in the array


By the way, I assume that you were referring to the front-end example "csvdownload.html" and back-end program EXCSVDLOAD. All new code is red.

Front end HTML changes (csvdownload.html)...

var exportToCSV = function() {
// create an array to old the visible column objects...
//
var visibleColumnObjects = [];

// create an array to hold the "data indexes" of each visible column, this
// array will be passed to the back-end program...
//
var postColumnArray = [];

// retrieve all of the visible objects...
//
visibleColumnObjects = colModelCustomers.getColumnsBy(function(c){
return !c.hidden;
});

// for each visible object, extract the dataIndex and place it into
// our postColumnArray...
//
Ext.each(visibleColumnObjects,function(obj){
postColumnArray.push(obj.dataIndex);
});

Ext.DomHelper.append(document.body, {
tag: 'iframe',
frameBorder: 0,
width: 0,
height: 0,
css: 'display:none;visibility:hidden;height:1px;',
src: 'vvcall.pgm?sid='+sid+'&opt='+opt+'&pgm=excsvdload&action=getCustRecsCSV'+'&columns='+'['+postColumnArray+']'
});
};



Now the changes on the back-end program EXCSVDLOAD...


d post_Action s 20a
d stmt s 200a varying
d columnArray s 10a dim(20)
d colCnt s 2 0
d ii s 2 0

/copy qcpylesrc,vvDspec
**--------------------------------------------
** program start
**--------------------------------------------
/free
post_Action = vvIn_char('action');
stmt = 'select cusno,cname,ccity,cstate from democmast '+
'order by cusno';

if post_Action='getCustRecs';
vvOut.rootName = 'DEMOCMAST';
vvOut_execSqlToJson(vvOut:stmt);
elseif post_Action = 'getCustRecsCSV';

// retrieve the column array...
//
colCnt = vvIn_array('columns'
:%addr(columnArray)
:%size(columnArray:*all)
:%size(columnArray));

// rebuild the sql statement, only using the columns from the array...
//
stmt = 'select';
for ii=1 to colCnt;
stmt+=' '+%trim(columnArray(ii));
if ii < colCnt;
stmt+=COMMA;
endif;
endfor;
stmt+=' from democmast order by cusno';
vvOut.download = 'Y';
vvOut.file = 'csvExample.csv';
vvOut_execSqlToCSV(vvOut:stmt);
endif;
*inlr=TRUE;

[Kurt Schrauwen]

Hai sean.lanktree,

Thanks for the info, yes it was that example where I started from.
But I'm still not able to get it working, I think I have a problem to get the parameter in the RPG source.


I get the visible columns in the 'postColumnArray' variable, when I look at the dat in this variable it looks like :",TKNAAM,TKVNAM"
Is it normal that it starts with a "," ?

And I call the program with this code :

src: 'vvcall.pgm?sid='+sid+'&opt='+opt+'&pgm=VAL_COM001&action=getTelephoneListCSV&search='+Ext.getCmp("search").getValue()+'&columns='+'['+postColumnArray+']'

Is this the code to get the array ?

colCnt = vvIn_array('columns'
:%addr(columnArray)
:%size(columnArray:*all)
:%size(columnArray));

It returns an excel file with al the columns as headers but with no data.

Any hint is welcom :-)

Greetings,
Kurt

[sean.lanktree]

Hi Kurt -

No, you should not be seeing a comma in the first position of the array. Are you seeing this in Firebug?

Did you copy the code exactly as from the previous post?