How to export selected columns of a list/grid to a file

Date: 4 November 2005
Product/Release: Visual LANSA V11
Abstract: How to export specific columns from a list/grid (Version 11 only)
Submitted By: LANSA Technical Support

The Transform_List BIF is very useful for saving data from lists into files for later retrieval or for use by other programs (such as Microsoft Excel). However one drawback is that it will always export all columns from the list without the ability to exclude unwanted columns. One option is to define a second list which contains only the necessary columns and populate that from the full list before exporting to file.

However if you want to allow your users to select the columns which are to be exported/excluded dynamically, there is a simple way of doing this using some of the new intrinsic functionality from V11.

Example showing 3 columns that can be hidden and shown by selecting the checkboxes on the right.

In the above example there are 3 columns that can be hidden and shown by selecting the checkboxes on the right. The interesting part about this is that when you press the 'Transform to file' button only the visible columns will be exported. 

The way this works is to create a temporary list containing one large char field capable of storing the contents of all 3 columns. The Grid is read and for each row only the visible columns are appended to the large text field. The field contents are appended with a TAB character placed between them.

Then the Transform_List BIF is then called for the temporary list and 'I' is specified for the Unexpected Characters parameter. This causes unexpected characters (in this case the TAB characters) to be written directly to the output file without any correction. The result is a file with (possibly) multiple tab delimited columns, which are treated correctly as separate columns in Excel.

This is a simple example however the concept can easily be extended to provide more useful functionality.

In order to create the above example perform the following in version 11.0;

  • Create a form and ensure that the component or partition is enabled for full RDMLX 
  • Cut and paste the code example listed below 
  • Compile and run 
Function Options(*DIRECT)
Begin_Com Role(*EXTENDS #PRIM_FORM) Clientheight(261) Clientwidth(458) Height(295) Left(314) Top(194) Width(466)
Define_Com Class(#PRIM_GRID) Name(#GRID_1) Captionnoblanklines(True) Columnscroll(False) Componentversion(1) Displayposition(1) Dragcolumns(True) Height(224) Left(17) Parent(#COM_OWNER) Showbuttonselection(True) Showselection(True) Showselectionhilight(False) Showsortarrow(True) Tabposition(1) Top(17) Width(272)
Define_Com Class(#PRIM_GDCL) Name(#GDCL_1) Displayposition(1) Parent(#GRID_1) Readonly(False) Source(#STD_NUM)
Define_Com Class(#PRIM_GDCL) Name(#GDCL_2) Displayposition(2) Parent(#GRID_1) Readonly(False) Source(#STD_TEXT) Width(47)
Define_Com Class(#PRIM_GDCL) Name(#GDCL_3) Displayposition(3) Parent(#GRID_1) Readonly(False) Source(#STD_DESC) Width(30)
Define_Com Class(#PRIM_PHBN) Name(#PHBN_1) Caption('Transform to file') Displayposition(2) Left(320) Parent(#COM_OWNER) Tabposition(2) Top(216) Width(97)
Define_Com Class(#PRIM_GPBX) Name(#GPBX_1) Displayposition(3) Height(109) Left(301) Parent(#COM_OWNER) Tabposition(3) Tabstop(False) Top(20) Width(140)
Define_Com Class(#PRIM_CKBX) Name(#CKBX_1) Buttonstate(Checked) Caption('STD_NUM Field') Displayposition(1) Left(16) Parent(#GPBX_1) Tabposition(1) Top(24)
Define_Com Class(#PRIM_CKBX) Name(#CKBX_2) Buttonstate(Checked) Caption('STD_TEXT Field') Displayposition(2) Left(16) Parent(#GPBX_1) Tabposition(2) Top(48)
Define_Com Class(#PRIM_CKBX) Name(#CKBX_3) Buttonstate(Checked) Caption('STD_DESC Field') Displayposition(3) Left(16) Parent(#GPBX_1) Tabposition(3) Top(72)
Define_Com Class(#PRIM_LABL) Name(#LABL_1) Caption('Show Columns:') Displayposition(4) Height(13) Left(14) Parent(#GPBX_1) Tabposition(4) Tabstop(False) Top(12) Width(107)

Evtroutine Handling(#com_owner.Initialize)
Set Com(#com_owner) Caption(*component_desc)
Inz_List Named(#grid_1) Num_Entrys(10)

Evtroutine Handling(
* Big Text field to store the contents of the grid (both char and numeric)
Define Field(#bigfield) Type(*char) Length(1000)
Def_List Name(#outlist) Fields(#bigfield) Type(*Working) Entrys(999)

Selectlist Named(#grid_1)
#bigField := ''

For Each(#Column) In(#grid_1.Columns)
Continue If(*Not #Column.Visible)
* If the column is visible, append it's contents to the large string, followed by a TAB character. 
* The TAB character is specified as ASCII character 9, and this is emulated by using (09).AsChar
Case (#Column.DataClass.ComponentClassName)
* Look at the Data Type of the column to determine how to process
When (= STD_NUM)
#BigField += #std_num.AsString + (09).AsChar
When (= STD_Text)
#BigField += #std_text + (09).AsChar
When (= STD_Desc)
#BigField += #std_desc + (09).AsChar

Add_Entry To_List(#outlist)

* Transform the list to a file, using TAB delimiters, and Ignoring unexpected characters
* This will ensure that no quotes are placed around the Big Text field, and that the TAB
* Characters are saved correctly
Use Builtin(Transform_list) With_Args(#outlist 'C:\outlist.xls' T I) To_Get(#io$sts)

Evtroutine Handling(#CKBX_1.Click)
If Cond('#CKBX_1.ButtonState *eq Checked')
#GDCL_1.Visible := True
#GDCL_1.Visible := False

Evtroutine Handling(#CKBX_2.Click)
If Cond('#CKBX_2.ButtonState *eq Checked')
#GDCL_2.Visible := True
#GDCL_2.Visible := False

Evtroutine Handling(#CKBX_3.Click)
If Cond('#CKBX_3.ButtonState *eq Checked')
#GDCL_3.Visible := True
#GDCL_3.Visible := False