Grid Component - Export to Excel, Ascii or Custom Format  Feature Pack

To purchase this Feature Pack, please click here.

IMPORTANT: This feature pack requires Alpha Five V10.5 Build 3015-3567 or above. If you do not have this release installed, please select the Help/Download Patches menu item in Alpha Five.

 

The 'Export to Excel, Ascii or Custom Format'  Feature Pack allows you to export data from a Grid component to a file that can then be downloaded from the server to the user's machine where the file can be saved to user's machine.

The Feature Pack allows you to:

Once the Feature Pack is installed, its functionality is exposed in two ways:

  1. A new action is available in Action Javascript
  2. A new property is available in the Record Navigator/Grid Toolbar section.

Watch Video - Part 1

Watch Video - Part 2

Watch Video - Part 3

Watch Video - Part 4

 

 

'Export to Excel' Property

If the Feature Pack is installed, you will notice a new property in the Grid builder:

The 'Export to Excel' property will be visible. If you turn on this property, then two additional properties are shown that allow you to customize the icon and bubble help for the icon. The customized bubble help can include the <a5:r> </a5:r> language tags if you want to automatically translate the text into different languages.

 

 

 

 

If you turn on the 'Export to Excel' property, the toolbar of the Grid will be rendered as shown below:

 

Note: When you turn on the 'Export to Excel' property you are in effect creating an export definition with these pre-selected choices:

 

 

If any of these choices do not suit you, then use Action Javascript to create a custom action.

 

'Export to Excel or  Ascii File' Action in Action Javascript

 

 

 

Export Type

Data an be exported as an Excel file, comma delimited ascii file, tab delimited ascii file, or 'Custom'.

If you choose 'Custom', then you must specify the name of an Xbasic function which will create the file that you want to export.

This Xbasic function is typically defined in the 'Xbasic Function Declarations' section. To see the prototype of this function click the 'Show Function Prototypes' hyperlink.

See 'Custom Export Formats' before for more information.

 

Source

The Source property allows you to select the data source for the export.

 

In some cases the fields in the Grid query (option 2) and the fields in the Grid component (option 1) will be the same. However, in the case of a Grid that is based on a DBF table, the table might have many more fields than are actually selected to display on the Grid. This could also be true in the case of a Grid that was based on a "Select * from tablename" query.

Here is an example of how you might use the 'Custom' option. Say you have a Grid that shows records for the 'Invoice Header' table. You can put a button in each row of the Grid that will export the records from the related 'Invoice Items' table.

 

 

Client-side Filename

After the file has been downloaded to the user's machine, the browser will prompt the user to save the file. It will suggest a filename. You can set the name that the browser will suggest.

Zip File

You can specify if the file should be zipped on the server before it is downloaded.

Record Selection

If you are exporting data from the current Grid, or current Grid query, then you can specify that all records in the Grid should be exported, or only those records that are currently selected by the last run search that you performed.

Maximum number or records

This specifies the maximum number of records to export. By default this property is set to 10,000. Set it to 0 if you don't wish to impose any limit. This property allows you to protect against a situation where you have a Grid that is based on a huge number of records. Exporting a large number of records could tie up the server for an extended period of time, causing other users to experience a slowdown in the application.

Events - Client Side - Before Export

This is a Javascript event that is defined in the 'Javascript Function Declarations' section. It is called before the Ajax callback to export the data is  made. It allows you abort the callback if the function returns false.

The function is typically used to lock the UI and tell the user to wait while the export is executing. For example:

A5.msgBox.show('Exporting Data...','<div style=\'padding: 20px;\'>Please wait the records are exported.</div>','none',function() {});

 

Events - Client Side - After Export

This is a Javascript event that is defined in the 'Javascript Function Declarations' section. It is called before the Ajax callback to export the data has been completed. If you put up a message box in the Before Export event, then you can clear the message in this event. For example:

A5.msgBox.hide();

 

Events - Server Side - After Export

This is an Xbasic event that is defined in the 'Xbasic Function Declarations' section. It is called after the export file has been created, but before the file has been downloaded to the user's machine. This event can abort operation (i.e. cause the file not to be sent to the user's machine). It can also abort the firing of the After Export client side event. The function prototype explains which properties need to be set in order to abort these actions.

This event is not typically needed, but it does allow you, for example, to create logging entries on the server.

 

Custom Export Formats

When you select the 'Custom' export format, you define an Xbasic function that will be called to create the export file. The function prototype for this function (which you can generate automatically by clicking the 'Show Function Prototypes' hyperlink) is shown below.

 

You will note that the function has a single input parameter, 'e', which is an object with several properties, the most important of which is e.tbl, a pointer to the table (in the case where data is being exported from a .dbf table) or resultset (in the case where data is being exported from a SQL query) from which the data is to be exported.

For example, assume you selected as your Export Source all fields in the Grid, and you selected 'Records in current query'. In this case, 'e.tbl' is a pointer to the resultset that has all of fields in the Grid and all of the records in the current grid query. Your Xbasic code that then loop through the records in the resultset and create the custom export file.

The 'e' object also contains 'e.exportFilename'. This is the name of the file that your function must create.

 

function myCustomeExport as v (e as p)
'This function is called when you set the export type to 'Custom'.
'This function will create the file that will be downloaded to the client.
'The filename for the file you create is passed in as e.exportfilename.


'What's in e:
'e.tbl - Pointer to the table or record set of the from which data is to be exported.
'e.itbl - In the case of DBF data, a pointer to the index after the table has been queried
'e.datatype - Either 'sql' or 'dbf', depending on what type of table you are exporting data from.
'e.exportfilename - Name of the file you must create with the exported data.

'The function can set:
'e.abort - set to .t. to abort the file download.

'Example (for SQL data):
''Loop through all records in the result set to create the custom export file.
'dim txt as c = ""
'dim flagLoop as l = .t.
'While flagLoop
' txt = txt + e.tbl.data("field1") + e.tbl.data("field2") + crlf()
' flagLoop = e.tbl.nextRow()
'End While
'file.from_string(e.exportFilename,txt)

'Example (for DBF data):
''Loop through all records in the table to create the custom export file.
'dim txt as c = ""
'While .not. e.tbl.fetch_eof()
' txt = txt + e.tbl.data("field1") + e.tbl.data("field2") + crlf()
' e.tbl.fetch_next()
'End While
'file.from_string(e.exportFilename,txt)




end function