VLF Windows - Send to MS Excel example

This forum allows developers to post programming tips and coding techniques that may be useful to other Visual LANSA developers. The information contained in this forum has not been validated by LANSA and, as such, LANSA cannot guarantee the accuracy of the information.
Post Reply
User avatar
Jiri
Posts: 45
Joined: Thu Feb 11, 2016 11:41 pm

VLF Windows - Send to MS Excel example

Post by Jiri » Wed Mar 29, 2017 12:54 am

The built-in instance list browsers (for both WLF-WIN and VLF-One) have useful feature Allow Instance List to be sent to MS/Excel.
If we (from any reason) replace it with own snap-in Instance list browser, this option does not work probably because it is not defined in user code.
Can you provide any example how to implement it into VLF-WIN snap-in instance list as simple as possible ?

MarkD
Posts: 661
Joined: Wed Dec 02, 2015 9:56 am

Re: VLF Windows - Send to MS Excel example

Post by MarkD » Thu Mar 30, 2017 10:59 am

The code VLF-WIN uses is pretty simple – it just creates a simple CSV file.

I can give it to you if you want, but it uses an internal built-in function to write out the stream file.

In your version you would need to STM_FILE_OPEN, STM_FILE_WRITE, etc.

Key bits from the logic I noticed are ……………

Code: Select all

* Determine what the excel delimiter character is for this PC. For Excel, CSV does not necessarily mean comma delimited, particularly in Asian systems.
Use Builtin(GET_REGISTRY_VALUE) With_Args(HKEY_CURRENT_USER 'Control Panel\International' 'sList') To_Get(#delimiter #IO$STS)
And this little cell wrapper method

Code: Select all

* Wrap a field value for a CSV file in quotes and add a comma
Mthroutine Name(uCSVQuote)
Define_Map For(*both) Class(#prim_dc.UnicodeString) Name(#uString)
* Additional processing required for embedded "
* (embedded comma are handled by wrapping the entire string in quotes which we do anyway)
Define Field(#DblQuote) Reffld(#Quote) Default('"')
Define Field(#FourQuote) Type(*char) Length(2) Default('""')
If (#uString.Contains( #DblQuote ))
#uString := #uString.ReplaceAll( #DblQuote #FourQuote )
Endif
#uString := #DblQuote + #uString + #DblQuote + #delimiter
Endroutine

User avatar
Jiri
Posts: 45
Joined: Thu Feb 11, 2016 11:41 pm

Re: VLF Windows - Send to MS Excel example

Post by Jiri » Sat Apr 01, 2017 1:46 am

The stream file management is not problem - if we cannot use your internal BIF, we can create own (or reusable component) for it to allow share the logic for more instance lists on single place.
But for real implementation, and in aim to ensure similar behaviour for built-in Instance lists as well as for user defined we need much more infos - i.e.
- how to get (on Framework level predefined) file prefix
- what event - click on Menu item Send to MS Excell can we use as triggering the file creation (maybe defined in ancestor form ?)
- a simple method to collect fields (i.e. FOR EACH (...) cycle over the used grid or list cell.

I would like idea to place most of this logic into a common reusable component, which could get reference to used list /grid, some settings (via property or methods). All the others (STM_FILE_OPEN Cells /rows reading cycles read -> transformations / STM_FILE_WRITE /CLOSE ) should be shared here.
I suppose you probably have some logic like this, or not?

MarkD
Posts: 661
Joined: Wed Dec 02, 2015 9:56 am

Re: VLF Windows - Send to MS Excel example

Post by MarkD » Mon Apr 03, 2017 9:55 am

In your instance list browser globally declare a standard #uSystem access ........

Code: Select all

* Standard system access
Define_Com Class(#VF_SY001) Name(#USYSTEM) Scope(*Application) Reference(*dynamic)
Then overwrite method uSendtoXL with your logic ........ this example shows how the file name and folder are derived.

Code: Select all

* Handle a request to send the instance list to MS-Excel

Mthroutine Name(uSendtoXL) Options(*REDEFINE)

Define_Com Class(#PRIM_ALPH) Name(#ToFileName)

#ToFileName := #USYSTEM.uUser.uTempDirectory + #puVF_FP503Owner.uAssocVF_FP003.uSendtoCSVPrefix + '_' + #datetime.AsString + '.csv'

Use Builtin(MESSAGE_BOX_SHOW) With_Args(ok ok info *component #ToFileName)

Endroutine
 
Regarding the for loops over grid or list cells, that's really up to your code I think because it is what creates and visualizes your version of an instance list - as a grid or list for example? So if you are going to have multiple instance list browsers you would may need to design some way to pass your various visualizations, with some sort of "meta data", into your generic CSV creation logic.
Last edited by MarkD on Mon Apr 03, 2017 10:03 am, edited 1 time in total.

MarkD
Posts: 661
Joined: Wed Dec 02, 2015 9:56 am

Re: VLF Windows - Send to MS Excel example

Post by MarkD » Mon Apr 03, 2017 10:03 am

I guess the cells question is really about how to generically traverse the rows and column cell values of a VL-Windows list or grid passed into a method as parameter?

I don't have an code examples to do that, but someone else might or might know how to do that.

Maybe ask that as a new specific question on a new thread?

MarkD
Posts: 661
Joined: Wed Dec 02, 2015 9:56 am

Re: VLF Windows - Send to MS Excel example

Post by MarkD » Thu Apr 27, 2017 6:14 pm

I stumbled on this today ........... generically traversing the columns in a #PRIM_LIST.
It's searching, but same logic would apply to creating a CSV data set.

Code: Select all

* ---------------------------------------------------------------------------------------
* Handle quick searching
Mthroutine Name(uPerformQuickSearch) Desc('Search the instance list') Options(*REDEFINE)

Define_Com Class(#prim_dc.UnicodeString) Name(#CellUppercaseValue)
Define_Com Class(#prim_dc.UnicodeString) Name(#SearchUpper)
Define_Com Class(#prim_boln) Name(#RowVisible)

* Do not search unless enough characters entered
If (#SearchString.Trim.CurChars < #USystem.uCustomUF_OSYSTM.MinimumQuickSearchCharacters)
Return
Endif

* Uppercase the search string
#SearchUpper := #SearchString.Trim.UpperCase

* Read though the list and find if any of the words are in any cell in the row
Selectlist Named(#VisualizedInstanceList)

#RowVisible := False

For Each(#Column) In(#VisualizedInstanceList.Columns)
If (#Column.ColumnVisible)

If (#Column *Is #Prim_list.String)
#CellUppercaseValue := (#Column *As #Prim_list.String).CurrentItem.Value.UpperCase
Else
#CellUppercaseValue := (#Column *As #Prim_list.Number).CurrentItem.Value.Asstring
Endif

If (#CellUppercaseValue.Contains( #SearchUpper ))
#RowVisible := True
Leave
Endif

Endif
Endfor

#VisualizedInstanceList.CurrentItem.Visible := #RowVisible

Endselect

Endroutine

dhnaigles
Posts: 48
Joined: Wed Feb 03, 2016 1:34 am
Location: Marlborough, MA, USA

Re: VLF Windows - Send to MS Excel example

Post by dhnaigles » Thu Aug 03, 2017 7:19 am

Jiri
I know this is late to the discussion, but here are my routines to create CSV's from PRIM_GRID and PRIM_LTVW. I pass them by reference.

Mthroutine Name(BuildCSVFromGrid)
Define_Map For(*INPUT) Class(#PRIM_GRID) Name(#grid) Pass(*BY_REFERENCE)
Define_Map For(*INPUT) Class(#PRIM_BOLN) Name(#visibleOnly) Mandatory(False)
Define_Map For(*INPUT) Class(#PRIM_BOLN) Name(#ShowFile) Mandatory(True)
Define_Map For(*INPUT) Class(#PRIM_ALPH) Name(#outputFile) Mandatory(*NULL)
Define Field(#xlsrow) Type(*STRING) Length(32000)
* DEF_LIST Name(#headerlist) Fields(#F03FLD #F03TYP #F03DES #F14SEQ) Type(*Working) Entrys(*MAX)

Def_List Name(#xlslist) Fields(#xlsrow) Type(*Working) Entrys(*MAX)
#xlsrow := *NULL
For Each(#col) In(#grid.columns)
If Cond(#visibleOnly)
Continue If(#col.Visible = False)
Endif
#xlsrow := #xlsrow.TrimConcat( #col.CaptionText ).TrimConcat( "," )
Endfor
Add_Entry To_List(#xlslist)
For Each(#row) In(#grid.Items)
#xlsrow := *NULL
For Each(#col) In(#grid.Columns)
If Cond(#visibleOnly)
Continue If(#col.Visible = False)
Endif
#STD_STRNG := #col.Cell<#row.Entry>.Value
#xlsrow := #xlsrow.TrimConcat( #STD_STRNG.RemoveAll( "," ) ).TrimConcat( "," )
Endfor
Add_Entry To_List(#xlslist)
Endfor
If_Null Field(#outputFile)
#outputFile := *TEMP_DIR + #datetimec + ".csv"
Endif
Use Builtin(TRANSFORM_LIST) With_Args(#xlslist #outputFile T) To_Get(#VF_ELRETC)
Use Builtin(SYSTEM_COMMAND) With_Args(H #outputFile) To_Get(#std_num)
Endroutine

Mthroutine Name(BuildCSVFromList)
Define_Map For(*INPUT) Class(#PRIM_LTVW) Name(#grid) Pass(*BY_REFERENCE)
Define_Map For(*INPUT) Class(#PRIM_BOLN) Name(#visibleOnly) Mandatory(False)
Define_Map For(*INPUT) Class(#PRIM_BOLN) Name(#ShowFile) Mandatory(True)
Define_Map For(*INPUT) Class(#PRIM_ALPH) Name(#outputFile) Mandatory(*NULL)
* DEF_LIST Name(#headerlist) Fields(#F03FLD #F03TYP #F03DES #F14SEQ) Type(*Working) Entrys(*MAX)

#xlsrow := *NULL
For Each(#col) In(#grid.columns)
If Cond(#visibleOnly)
Continue If(#col.Visible = False)
Endif
#xlsrow := #xlsrow.TrimConcat( #col.CaptionText ).TrimConcat( "," )
Endfor
Add_Entry To_List(#xlslist)
For Each(#row) In(#grid.Items)
#xlsrow := *NULL
For Each(#col) In(#grid.Columns)
If Cond(#visibleOnly)
Continue If(#col.Visible = False)
Endif
#STD_STRNG := #col.ValueAt<#row.Entry>
#xlsrow := #xlsrow.TrimConcat( #STD_STRNG.RemoveAll( "," ) ).TrimConcat( "," )
Endfor
Add_Entry To_List(#xlslist)
Endfor
If_Null Field(#outputFile)
#outputFile := *TEMP_DIR + #datetimec + ".csv"
Endif
Use Builtin(TRANSFORM_LIST) With_Args(#xlslist #outputFile T) To_Get(#VF_ELRETC)
Use Builtin(SYSTEM_COMMAND) With_Args(H #outputFile) To_Get(#std_num)
Endroutine

Post Reply