Function Names
table load excel fileDescription
This function loads Excel files (Open Office format) directly into B4P tables. Following features are supported:
- One or multiple Excel sheets can be loaded into one or multiple B4P tables using one function call only. In this case, an equal number of tables and Excel sheet names need to be specified. Loading multiple sheets with one function call takes less time than calling this function multiple times for every table.
Example: table load excel file( { table 1, table 2 }, "Example.xlsx", { Base Data, My Calculations } ); - Instead of sheet names, sheet numbers (The left-most sheet seen in Excel begins with 1) can be loaded instead.
- The sheet number 0 refers to the active (visible) sheet in Excel at the time the Excel file has been saved
- If no sheet name or number is specified, then the active (visible) sheet will be loaded, too.
- If only one table is loaded from one Excel sheet, then additional options can be applied, such as loading formulas into a separate table.
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.
Note: In Excel files, various numeric data are shown in different 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.
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 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.
Call as: procedure
Restrictions
Indirect parameter passing is disabled
Parameter count
2,3,5,7,9,...(odd numbers)
Parameters
| No. | Type | Description |
|---|---|---|
| 1 input |
string set |
name of target table If only one table needs to be loaded, then it is OK to provide the table name as a string. If more tables need to be loaded from the file, then include the names into a set. In case more than 1 table shall be loaded, then precisely 3 function paramters need to be provided, not more and not less. In case an empty set (zero tables) is provided, then nothing will be loaded. If target tables are existing, then they will be initialized before loading. |
| 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 name This parameter specifies the Excel sheets in the workbook to be loaded. It is optional when loading only one sheet.
in this case, the active (visible) Excel sheet will be loaded.
Otherwise, the name number of sheet names or sheet numbers specified must equal to the number of target tables specified.
|
| Opt. 4 input |
string | Attribute name Following attribute names are supported:
Note: Attribute value in the next function parameter is required. |
| Opt. 5 input |
string | Attribute value Specify the attribute value as defined by the previous function parameter attribute name.
|
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", 0, formulas, 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)