|
|
|
|
|
|
You can download the contents of lists to Excel and Word.
Click on the 
download button at the bottom right
of the currently open list. Alternatively press Ctrl
+ D on
the keyboard.
Option |
Description |
Download Non Table Data? |
Tick to include all fields in the download. This includes fields that are not visible on the selected screen. |
Download Table Headers? |
Choose if you want to include the headers in your download. |
Destination Options |
|
Clipboard |
This allows you to copy the data with the options chosen to the clipboard.
|
File |
Choose this option to download the data into a file using the browser’s download function. Click OK to initiate the file download. A spinning progress indicator is displayed while the download is being actioned. On completion the List Box Download dialogue will close automatically. The browser’s download function will present the file to the user. |
Open in Excel |
This option will automatically attempt to open Excel on the users desktop, (assuming it is installed), and create a worksheet file in the user’s temporary directory with an automatically assigned file reference along the lines of XXX123456.xls. A spinning progress indicator is displayed while the download is being actioned. On completion the List Box Download dialogue will close automatically. On completion, Excel will open with the data automatically downloaded to it. In this option there is no opportunity to merge the data with a file to get bold / italic / colour formatting etc. There is also no opportunity to define the file name / directory location. The data is automatically inserted into the sheet at row 1 and the system will attempt to “fit width” the downloaded column contents to the widest of content or header labels. |
Start Cell Reference |
The start cell reference gives an alternative cell position to start the output of data from, for example E10. |
Open using Excel Spreadsheet |
This option allows the you to select or input a local Excel file reference that can act as a Template file to which you can merge the data being extracted. The template file can either be:
|
Open in Excel using Onedrive |
This option will output the list box data to Microsoft Excel in the cloud using Onedrive. Excel will open in a new browser tab. |
Open in Google Sheets |
This option will output the list box data to Google Sheets in a new browser tab. |
Start Cell Reference |
The start cell reference gives an alternative cell position to start the output of data from, for example E10. |
Option |
Description |
Open in Word |
This option will attempt to create a Word zip file (assuming it is installed) in your temporary directory with an automatically assigned file reference. The option to open or save the .zip file is provided. |
Open using Word Template |
This option allows the user to select or input, a local Word file reference that can act as a Template file for which the data being extracted can be merged. |
Open in Word using Onedrive |
This option will output the list box data to Microsoft Word in the cloud using Onedrive. |
Open in Google Docs |
This option will output the list box data to Google Docs in a new browser tab. |
You can choose which columns you want to download.
When using an excel template with control commands the data extract may be set to work according to relative column values. If the you manually de-select certain columns you may get misleading results. Caution should therefore be used if you try to use the Column selection options in conjunction with an excel template file using control commands.Tick the columns you want to download. Only visible columns are available to be selected.
To de-select all columns click Select None. This will de-select all and change the button to Select All.
Select a column and click Move Up / Move Down to move its position in the download file.
Click Re-set Columns to reset the column selection and order back to that when the dialogue was first opened.
This tab provides options to influence the way the data is downloaded to file or the windows clipboard.
Option |
Description |
Data Separator Options |
The Data separator can be set to:
|
Surround all strings with quotes? |
This tick-box has the impact of surrounding all string values downloaded with quotation marks. Other fields such as dates and numerics will not have the quotation marks inserted. |
Advanced string formatting |
|
Keep control characters (tabs, line-feeds, etc) |
Ticking this option exports the control characters. Leaving this option unticked converts control characters like line-feed to spaces. Control character formatting is not compatible with Excel, and can disrupt the formatting if they are left in place. It is better to leave this option unticked if you are not sure. |
Prepend strings with this character sequence |
Allows you to nominate some characters, e.g. ab, which will be prepended to non–numeric data that is downloaded. The purpose is to make spreadsheets treat a value as a string rather than a number. |
Date formatting |
|
Download date format |
This option formats the date shown in the downloaded list contents to your requirements. The date format first shown is set in System Administration, but can be overridden. You can set the format to whatever you need, however you must follow these rules: Create the date using these formats:
You can only separate the date elements using:
If you enter an incorrect format, you will be warned when you click OK to export the list. |
Performance Options |
|
Compress Download |
Zip the download. |
To be recognised as a special e5H5 template file, with control commands, the Excel file must contain a worksheet titled E5_CTRLD_CMDS.
The special rules for data extraction are defined on this worksheet.
The data output will go to a sheet titled E5_CTRLD_OUTPUT if one is defined in the excel file, otherwise to the first available sheet. Otherwise one will be created. This target sheet, is usually where cell format rules would be defined in Excel.
Processing options will also allow output to go to the command sheet (so that formatting and commands can be on the same sheet). If this is not required, create an E5_CTRLD_OUTPUT sheet, or hide or protect the E5_CTRLD_CMDS sheet.
e5.cell(row# , col# | colname)
This command extracts a specific cell value from the "grid" of data available for download. If the command is in cell position A6 on the command sheet, the data value extracted is output in position A6 on the resulting output sheet. By a specific "cell" think of the grid of data in the example below and the highlighted individual cell.
The row and column numbers commence from 1, meaning that in a grid of data like this:
| Cost Centre | Account | Value | Transaction Date |
CC1 |
3000 |
100.54 |
01/01/2018 |
CC3 |
3000 |
98.45 | 31/12/2019 |
CC4 |
3000 |
7.83 |
01/01/2020 |
The syntax e5.cell(1,3) would extract the value 100.54 because it is extracting from row 1, column 3. (Header rows are not addressable this way, see later).
The syntax e5.cell(1,2) would extract the value 3000.
An alternative use of this syntax is to use the column heading description. For example,
e5.cell(1,Transaction Date) would extract 01/01/2018, i.e. the field named Transaction Date from row 1.
When the data is presented on the second Excel sheet any cell formatting associated with the cell on the template file is preserved, e.g. text size, font colour etc.
This mechanism is tedious to extract large volumes of data, as you are essentially mapping a single value at a time. It may however be useful to refer to a single cell, where for example you know the value is not going to vary and you simply want to extract and display it once on the output. An example may be to output the supplier name once as part of a spreadsheet title where a list of Purchase Orders is being downloaded.
e5.col(col# | colname)
This command extracts all column values for the selected column or range of columns. Where a single column is required it may be referred to be column number or column name.
Example syntax:
e5.col(1) or e5.col(Transaction Date)
Where a range of columns is required then column numbers MUST be used e.g.
Example syntax:
e5.col(1-7) would extract data from columns 1 to 7 inclusive.
e5.col(-9) would extract data up to column 9 inclusive.
e5.col(1-) would extract data from columns 1 onwards (effectively all columns).
These range options mean that it is possible to define a generic template that will work reasonably well across different list programs, as, in the first iteration of this enhancement, there is no capability to store different default templates by Map or user.
Referring to our grid of data above, the syntax e5.col(4), or e5.col(Transaction Date) would extract:
01/01/2018
31/12/2019
01/01/2020
The syntax e5.col(2-) or e5.col(2-4) would extract:
3000 |
100.54 |
01/01/2018 |
3000 |
98.45 |
31/12/2019 |
3000 |
7.83 |
01/01/2020 |
e5.col.insert(col# | colname)
This rule is similar to the e5.col command except that the repeated rows, pick up the formatting of the first declared row. This is extremely useful when the number of rows of data to be extracted is not known. It also allows excel functions to be used to insert calculations such as Sum declared columns to provide an overall total when required.
The command can also use column ranges such as:
e5.col.insert(1-)
When it is processed, the first column value is copied into the target sheet in the equivalent location. When the second value is to be inserted into the target sheet, a copy of the whole target row is inserted into the spreadsheet under the first target row, and all subsequent rows in the spreadsheet are moved down one row. Then the value is inserted into the cell in the newly created row.
This has the affect of copying the cell formatting from the first target row, to all newly populated rows. If there are rows with calculations or formatting defined under the columns, then these calculations are pushed down the spreadsheet and are updated to include the new rows. So tallies of the numeric columns can appear immediately after the down loaded values.
An alternative way to use the command is in conjunction with the e5.Col rule, where the insert command must be used on the same row and appear first. The second column in the table to be executed should specify the e5.col(col#) rule. This is because the first rule will have generated the new row with formatting. For example,
e5.col.insert(1) e5.col(2-)
e5.row(col# | colname)
This operates in the same way as the e5.col command, except that the selected column is displayed as a row, left to right across the page.
For example, using the following data (where the shaded cells are headings not data)
| Cost Centre | Account | Value | Transaction Date |
CC1 |
3000 |
100.54 |
01/01/2018 |
CC3 |
3000 |
98.45 |
31/12/2019 |
CC4 |
3000 |
7.83 |
01/01/2020 |
The command e5.row(3) would select the contents of column 3 (in this case the value column) and display it in the following fashion within the spreadsheet created:
100.54 |
98.45 |
7.83 |
Where single, or individual columns are required to be output as rows they may be referred to by column number or column name.
Where a range of columns is required to be output as rows then column numbers must be used. For example:
e5.row(1-7) would extract columns 1-7 and display them as rows.
e5.row(-9) would extract columns 1-9 and display them as rows.
e5.row(1-) would extract all columns and display them as rows.
Example: Referring to the grid of data displayed earlier, the command e5.row(3-), or e5.row(3-4) would return the following:
100.54 |
98.45 |
7.83 |
| 01/01/2018 | 31/12/2019 | 01/01/2020 |
e5.header(col# | colname)
This command allows the column header of a specified column to be extracted. The brackets may use a specified column number, name or range).
Example: Referring to the grid of data displayed earlier, the command e5.header(2-) would return the following:
Account |
Value |
Transaction Date |
e5.header.row(col# | colname)
This command allows the column header of a specified column to be extracted and displayed as a row label. The brackets may use a specified column number, name or range, so for example, the syntax e5.header.row(1-) would lay the column headers out as a row labels. Referring to our example table above the output spreadsheet would display something like:
Cost Centre |
| Account |
| Value |
| Transaction Date |
See also
|
© Advanced, Jan 2021 |
|
