Function Names
table save excel fileDescription
This function saves 1 or more tables into an Excel file (.xlsx) based on Open Office format. In addition to the contents,
defined styles will also be included. In order include the defined style information, call translate style attributes for excel()
immediately before saving.
The following type conversions will be made unless forced to string contents using table style(..., type, string) on selected
table contents.
- 'true' and 'false' will be saved as Boolean values
- Dates (YYYY-MM-DD) times (hh:mm:ss) or combination of both (YYYY-MM-DD hh:mm:ss) will be saved as dates.
- Dates and times in other formats (e.g. DD.MMM.YYYY, or YYYY-MM-DD hh:mm) will be treated as text.
- B4P date variables (regardless if with or without time included) written to tables will be written in the correct as specified above so the conversion takes place automatically.
- Text wrapping will be activated if contents contain line breaks so the whole text is visible
- All numbers will be saved as numeric data.
- Numbers containing scientific notation (e.g. 1E3 for 1000) will be saved using scientific notation format.
- Other number formats like percentages: Save the numbers as they are (e.g. 0.15 for 15%), then apply styles functions such as table style columns() with the attribute name number format followed by the attribute value as understood by Excel, e.g. "0.00%".
Call as: procedure
Restrictions
Indirect parameter passing is disabled
Parameter count
3-4
Parameters
| No. | Type | Description |
|---|---|---|
| 1 input |
parameter set or string | name(s) of table(s) Specify the table name directly, or to specify multiple tables:
|
| 2 input |
parameter set or string | Corresonding sheet names Specify the sheet names (as seen on the bottom tabs when opened in Excel). The number of sheet names must match with the number of tables specified in the 1st function parameter. |
| 3 input |
string | name of Excel file name The file name must contain the file type ending '.xlsx'. |
| Opt. 4 input |
string | Active sheet name You can specify the active sheet name which shall appear on the screen after opening the file in Excel. If no sheet name is specfied, then the first table specified will be opened. Default value: (1st sheet specified) |
Examples
table load( c, "Examples/Cities.csv" );
echo("Original table: ");
table list ( c );
table save excel file( c, Cities, "Examples/Cities.xlsx" );
table load excel file( d, "Examples/Cities.xlsx" );
echo("Table save in Excel format and loaded again: ");
table list ( d );Output
Original table:
0 : Country | City | State/Province | Inhabitants | Famous attraction | Alt. (m) | Famous cultural place | Moving along
1 : USA | New York City | New York | 8300000 | St. of Liberty | 10 | Guggenheim | Taxi
2 : USA | Washington | D.C. | 650000 | Lincoln Statue | 7 | Smithsonian Inst. | The Beast
3 : USA | Philadelphia | Pennsylvania | 1500000 | Independence hall | 12 | | PCC streetcar
4 : USA | Boston | Massachusetts | 620000 | Freedom trail | 43 | | Walking
5 : USA | San Francisco | California | 805000 | Golden Gate | 16 | SFMOMA | Cable car
6 : CAN | Montréal | Quebec | 1700000 | Hôtel du Parlement | | Rialto Theater | Skidoo
7 : DAN | Copenhagen | Hovedstaten | 580000 | Little Mermaid | 24 | Royal Theater | Bicycle
8 : ITA | Venice | Venetia | 260000 | Doge's Palace | 1 | Theatro la Fenice | Gondola
9 : USA | Los Angeles | California | 3800000 | Hollywood | 100 | Getty Center | Car
10 : AUT | Vienna | | 1800000 | Prater | | Opera | Fiacre
11 : THA | Bangkok | | 8250000 | Wat Phra Kaeo | 5 | National Museum | Longtail boat
12 : SWI | Zürich | Kt. Zürich | 404000 | Street Parade | 408 | Kunsthaus | Tram
13 : FRA | Paris | Île de France | 2240000 | Eiffel Tower | | Louvre | Métro
14 : SWI | Davos | Grisons | 11000 | Weissfluhgipfel | 1650 | Kirchner Museum | Ski lift
Saved table(s) to EXCEL file 'Examples/Cities.xlsx' containing 1 sheets.
Identified sheet number 1
Table save in Excel format and loaded again:
0 : Country | City | State/Province | Inhabitants | Famous attraction | Alt. (m) | Famous cultural place | Moving along
1 : USA | New York City | New York | 8300000 | St. of Liberty | 10 | Guggenheim | Taxi
2 : USA | Washington | D.C. | 650000 | Lincoln Statue | 7 | Smithsonian Inst. | The Beast
3 : USA | Philadelphia | Pennsylvania | 1500000 | Independence hall | 12 | | PCC streetcar
4 : USA | Boston | Massachusetts | 620000 | Freedom trail | 43 | | Walking
5 : USA | San Francisco | California | 805000 | Golden Gate | 16 | SFMOMA | Cable car
6 : CAN | Montréal | Quebec | 1700000 | Hôtel du Parlement | | Rialto Theater | Skidoo
7 : DAN | Copenhagen | Hovedstaten | 580000 | Little Mermaid | 24 | Royal Theater | Bicycle
8 : ITA | Venice | Venetia | 260000 | Doge's Palace | 1 | Theatro la Fenice | Gondola
9 : USA | Los Angeles | California | 3800000 | Hollywood | 100 | Getty Center | Car
10 : AUT | Vienna | | 1800000 | Prater | | Opera | Fiacre
11 : THA | Bangkok | | 8250000 | Wat Phra Kaeo | 5 | National Museum | Longtail boat
12 : SWI | Zürich | Kt. Zürich | 404000 | Street Parade | 408 | Kunsthaus | Tram
13 : FRA | Paris | Île de France | 2240000 | Eiffel Tower | | Louvre | Métro
14 : SWI | Davos | Grisons | 11000 | Weissfluhgipfel | 1650 | Kirchner Museum | Ski lift