Function Names
table load excel fileDescription
This function loads Excel files (Open Office format) directly into one or more B4P tables with a single function call. It provides the choice of loading
- Complete sheets specified by name or number
- Excel tables
- Pivot tables
B4P is able to distinguish properly between strings (strings), numerals, boolean values (true, false), dates, times and date-time combinations
(YYYY-MM-DD hh:mm:ss format). Other information such as formulas, cell formatting, color information, etc. are not loaded into the target tables,
however additional function parameters allows to load Excel formulas into separate B4P tables.
Note: In Excel files, various numeric data are shown in various elaborate formats, e.g. monetary values with only two digits behind
decimal point (e.g. 'EUR 1.25'), thousand separators, currency symbols, etc. B4P loads the original and unformatted value not constrained
by the output format.
Example: If 'EUR 1.25' is seen, but the actual value is 1.2488, then 1.2488 will be loaded. The same applies to percentages, e.g. 15% is loaded as 0.15.
In fields which contain formulas, the calculated value will be loaded.
Attention: Encrypted Excel files (e.g. marked confidential with the confidentiality-stamp where supported), binary and proprietary legacy file
types (e.g. .xlsb, .xls) cannot be loaded processed. File types .xlsx, .xlsm and .xlst are fine.
Loading Excel formulas:
You can optionally load the underlying Excel formulas if two additional parameters are added: "formulas" followed
by name of table which will be initialized and populated with formulas at the corresponding coordinates to the main
Excel table. For example, if row 5 / column 3 contains 10, the formula table may contain the formula "=5*2", always beginning with equal signs.
Note: The excel formulas are kept as they are. No coordinates are modified, e.g. into B4P table coordinates.
Note: If formulas are entreed once in Excel and then copied and pasted horizontally or vertically across the table, the Excel
has the lazy habit of writing the formula only 1-3 times, followed by a shared notification, probably attempting to save disk space.
In these cases, the string value "(Shared)" will be written into the coresponding table location.
Loading pivot tables:
B4P loads the pivot table including all headers, but not including the filter settings typically listed above the pivot table.
The pivot tables are loaded as seen, i.e. only contents as seen on Excel and then saved will be loaded.
To avoid any ambiguities, I suggest you to specify sheet and table names togetherand separated with a shals '/'.
You can use the more simple approch by specifying pivot:Pivot table name if the pivot table name is fully unique, e.g.
defined only once.
Call as: procedure
Restrictions
Indirect parameter passing is disabled
Parameter count
2-4
Parameters
| No. | Type | Description |
|---|---|---|
| 1 input |
string set |
name of target table(s) Specify the B4P table names for the Excel sheets and/or tables to be loaded. Use a string if only one table name needs to be loaded. Use a set containing strigs for mulitple table names. Every table name must be unique. B4P will create or initialize these tables before loading the contents. In case an empty set (zero tables) is provided, then nothing will be loaded. |
| 2 input |
string | name of Excel file name It must be an existing Excel file in Open Office format, e.g. with file type .xlsx, .xlsm, .xlst.
|
| Opt. 3 input |
string numeral set |
Excel sheet and table names This parameter specifies the Excel sheets, tables and pivot tables to be loaded. in the workbook to be loaded.
If more than 1 item shall be loaded, then specify the names and numbers in a set. The number of elements specified here must match
with the number of B4P tables specified in the 1st function parameter.
|
| Opt. 4 input |
string set |
Name of target tables for formulas Specify the B4P table names for the Excel sheets and/or tables to be loaded with formulas. Use a string if only one table name needs to be loaded. Use a set containing strigs for mulitple table names. Every table name must be unique. B4P will create or initialize these tables before loading the contents. In case an empty set (zero tables) is provided, then nothing will be loaded. The number of tables specified must equal to the number of tables in the first function parameter. If formulas are needed for fewer tables, then specify blank strings ('') for tables to skip. |
table load excel file( football, "Examples\Football Membership List.xlsx" );
table list( football ); 0 : First Name | Family Name | City | Level
1 : Abel | Amberstone | Amsterdam | Beginner
2 : Beata | Berghill | Barcelona | Experienced
3 : Corinne | Carlson | Copenhagen | Beginner
4 : Dietmar | Davis | Dublin | Beginner
5 : Ellen | Evans | Essen | Beginner
6 : Fred | Fisher | Frankfurt | Experienced
7 : Gregory | Green | Gaza City | Experienced
8 : Henry | Hansson | Hamburg | Experienced
9 : Ida | Ingelberg | Ingolstadt | Beginner
10 : John | Janssen | Johannesburg | Beginner
11 : Karl | Karlsson | Kansas City | Experienced
Excel file with formulas
table load excel file( table, "Examples\Formulas_Inside.xlsx", 1, formula table);
echo("Values: ");
table list( table );
echo("Formulas: ");
table list( formula table );
Values:
0 : Today: | 2021-03-13 | | | Numbers repeated
1 : Values | Double+1 | Sq-Roots | Text | (with matrix formula)
2 : 1 | 3 | 1 | Text | 1
3 : 5 | 11 | 2.2360679775 | Text | 5
4 : 25 | 51 | 5 | Text | 25
5 : 50 | 101 | 7.0710678119 | Text | 50
6 : 100 | 201 | 10 | Text | 100
7 : | | | | Sum of products
8 : Totals: | | | | (Intermediate vectors)
9 : 181 | 367 | 25.3071357894 | 0 | 181
Formulas:
0 : | =TODAY() | | |
1 : | | | |
2 : | =A3*2+1 | =SQRT(A3) | =D2 | =A3:A7
3 : | =A4*2+1 | =SQRT(A4) | =D3 |
4 : | (Shared) | (Shared) | (Shared) |
5 : | (Shared) | (Shared) | (Shared) |
6 : | (Shared) | (Shared) | (Shared) |
7 : | | | |
8 : | | | |
9 : =SUM(A3:A7) | =SUM(B3:B7) | (Shared) | (Shared) | (Shared)
See also
excel list sheets
excel list tables
table save excel file
table load