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

Stai visualizzando 1 post (di 1 totali)
  • Autore
    Post
  • Andrea GuidiAndrea Guidi
    Amministratore del forum
    • Intermediate
    • ★★
    Post totali: 42

    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.

    Omnis Studio

    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

Stai visualizzando 1 post (di 1 totali)
  • Devi essere connesso per rispondere a questo topic.