TechTip: Popup Form for exporting data from an existing Data Grid by A. Pfeiffer

[Andrea Guidi]

#57136

Many developers use the data grid in the Remote Form to display corresponding data. Now it would be great if there was a way to export exactly that data from the grid as a CSV file. It would be even better if the user could choose the order of the columns or if he wants to export them at all.

We use a remote form which is opened modal using the $clientcommand “subformdialogshow”. The user gets a list with all columns shown in the data grid, can change their order with drag & drop and click in the column “export” to omit columns. Prerequisite is that the data grid is $userdefined and that the individual columns of the data grid are named with the name of the respective column in the $columdatacol property.

Preparing the main window (or its superclass)

Our main window containing the data grid (or its superclass) should then have two public methods: $getGrid which returns the reference to the Grid object in the class (not the instance):

$getGrid which returns the reference to the grid object in the class (not the instance):

Quit method $cinst.$class().$objs.grid

And $getList which simply returns the list from the form:

Quit method iDataList

Behind a button control we can then open our modal remote form “jsExport” with the
$clientcommand:

On evClick

Do $cinst.$clientcommand(“subformdialogshow”,row(‘jsExport’,#NULL,’Export List’,
$classes.jsExport.$width,$classes.jsExport.$height))

Note: If the remote form has the $layouttype kLayoutTypeSingle, we can set the width and height of the instance using the $width and $height property of the class. If you are using the $layouttype kLayoutTypeResponsive, then please set the height and width manually.

The popup form “jsExport” creates a list of all columns from the calling window

The $construct method of the popup form then uses $cinst.$container to access the public
methods of the underlying form and creates a list of columns.

Do $cinst.$container().$getGrid Returns gridRef
If gridRef
Do method makeList (gridRef)
Else
Do $cinst.$showmessage(‘grid not valid’)
End If

“gridRef” is a local variable of type Item Reference.

Here is the code for the private method “makeList”, “pGridRef” is a parameter of type Item Reference:

Do iDataList.
$addcols(‘title’,kCharacter,kSimplechar,255,’export’,kBoolean,#NULL,#NULL,’colname’,kCharacter,kSimplechar,255)

For pGridRef.$currentcolumn from 1 to pGridRef.$designcols
Do iDataList.$add(pGridRef.$columnname,not(pGridRef.$columnhidden),pGridRef.
$columndatacol)
End For
The instance variable iDataList (type List) is then assigned to a two-column data grid so that the user can see the columns. The second column is called “export” and is of type Boolean. This then allows the user to select or hide the columns for export.

Hidden columns in the grid are also shown here but not initially checked because the $columnhidden property is used to set the checkmark when building the column list.

Drag & Drop the rows

If you set $dragmode to kDragData and $dropmode to kAcceptControl in the action properties of the grid, you can add code to the $event method of the grid that allows the user to change the order of the columns with drag & drop:

On evDrop
Do iDataList.$remove(iDataList.$line)
For i from pDragValue.$linecount to 1 step -1
Do iDataList.$addbefore(pDropId).$assignrow(pDragValue.[i])
End For
Do iDataList.$line.$assign(pDropId)

Preparing the export list

A button can now be used to get the columns from the list and make a list ready for export. We need the names of the columns used in the original data grid in the first line for the export.

On evClick
Do iExportList.$define() ## clear definition
Do iExportList.$add() ## line for the column header
For iDataList.$line from 1 to iDataList.$linecount
If iDataList.export
Do iExportList.$cols.$add(iDataList.colname,kCharacter,kSimplechar,255)
Calculate iExportList.1.[iDataList.colname] as iDataList.title ## column header
End If
End For
Do iExportList.$merge($cinst.$container().$getList(),kTrue)

The last line again uses the underlying form to load the data and add it to the export list.

Preparing an object class

For the export we need the list in the form of a tab-delimited text variable.
Here we use a function in an object class that writes the list to a text variable in CSV format.
“pDataList” is a parameter of type List, which contains the data to export, “pDelimiter” is a parameter of type Character which contains kTab as initial value.

$getStringFromList (in object class)

Begin text block
For pDataList.$line from 1 to pDataList.$linecount
For column from 1 to pDataList.$colcount
Text:[pDataList.c[column]][pDelimiter]
End For
Text: (Carriage return)
End For
End text block
Get text block returnString
Quit method returnString

Exporting the data via download

Here is the export function. It needs the file control which has assigned the instance variable “iJSFileRow” of type Row as $dataname. Furthermore a task variable “tJSFileBinData” of type Binary is used which holds the content to be exported in binary form:

Do stringObj.$getStringFromList(iExportList) Returns exportData
Calculate tJSFileBinData as chartoutf8(exportData)
Calculate iFileName as ‘export.csv’
Do iJSFileRow.$define(iJSFileName,iJSMediaType,iJSVariableName)
Do iJSFileRow.$assigncols(iFileName,’application/octet-stream’,’tJSFileBinData’)
Do $cinst.$objs.fileObj.$action.$assign(kJSFileActionDownload)

“stringObj” is an object variable that uses the object class with the $getStringFromList function as a subtype. The object then converts the list to be exported and writes the result to the character variable “exportData”. This content is in turn converted to UTF8 using the chartoutf8 function and written to the binary task variable. Finally the row variable iJSFileRow is prepared with the future file name for the download, the media type and the name of the task variable and the file control “fileObj” gets the instruction to start the download.

Andreas Pfeiffer

[Autore]

Post