Subject
Dear Mrs...
Lucas ipsum dolor sit amet organa solo skywalker darth c-3p0 anakin jabba mara greedo skywalker.
Regards
Company
Firstname Name
Function
#. Create a T3 letter-`header` page (e.g. page slug: '/letterheader') , with only the header information: ::
', p.name AS '_+br', p.street AS '_+br', p.city AS '_+br', '
'
FROM Person AS p
WHERE p.id={{pId:S}}
Export area
^^^^^^^^^^^
This description might be interesting if a page can't be protected by SIP.
To offer protected pages, e.g. directly referenced files (remember: this is not recommended) in download links, the
regular FE_GROUPs can't be used, cause the download does not have the current user privileges (it's a separate process,
started as the webserver user).
Create a separated export tree in Typo3 Backend, which is IP access restricted. Only localhost or the FE_GROUP 'admin'
is allowed to access: ::
tmp.restrictedIPRange = 127.0.0.1,::1
[IP = {$tmp.restrictedIPRange} ][usergroup = admin]
page.10 < styles.content.get
[else]
page.10 = TEXT
page.10.value = Please access from localhost or log in as 'admin' user.
[global]
.. _excel-export:
Excel export
^^^^^^^^^^^^
This chapter explains how to create Excel files on the fly.
Hint: For just up/downloading of excel files (without modification), check the generic Form
:ref:`input-upload` element and the report 'download' (`column_pdf`_) function.
The Excel file is build in the moment when the user request it, by clicking on a
download link.
Mode building:
* `New`: The export file will be completely build from scratch.
* `Template`: The export file is based on an earlier uploaded xlsx file (template). The template itself is unchanged.
Injecting data into the Excel file is done in the same way in both modes: a Typo3 page (rendered without any HTML header
or tags) contains one or more Typo3 QFQ records. Those QFQ records will create plain ASCII output.
If the export file has to be customized (colors, pictures, headlines, ...), the `Template` mode is the preferred option.
It's much easier to do all customizations via Excel and creating a template than by coding in QFQ / Excel export notation.
Setup
"""""
* Create a special column name ``_excel`` (or ``_link``) in QFQ/Report. As a source, define a T3 PageContent, which has to
deliver the dynamic content (also :ref:`excel-export-sample
`). ::
SELECT CONCAT('d:final.xlsx|M:excel|s:1|t:Excel (new)|uid:') AS _link
* Create a T3 PageContent which delivers the content.
* It is recommended to use the ``uid:`` syntax for excel imports, because there should be no html code on the
resulting content. QFQ will retrieve the PageContent's bodytext from the Typo3 database, parse it, and pass the
result as the instructions for filling the excel file.
* Parameters can be passed: ``uid:?param=¶m2=`` and will be accessible in the SIP Store (S) in the
QFQ PageContent.
* Use the regular QFQ Report syntax to create output.
* The newline at the end of every line needs to be CHAR(10). To make it simpler, the special column name ``... AS _XLS``
(see _XLS, _XLSs, _XLSb, _XLSn) can be used.
* One option per line.
* Empty lines will be skipped.
* Lines starting with '#' will be skipped (comments). Inline comment signs are NOT recognized as comment sign.
* Separate and by '='.
+-------------+----------------------+---------------------------------------------------------------------------------------------------+
| Keyword | Example | Description |
+=============+======================+===================================================================================================+
| 'worksheet' | worksheet=main | Select a worksheet in case the excel file has multiple of them. |
+-------------+----------------------+---------------------------------------------------------------------------------------------------+
| 'mode' | mode=insert | Values: insert,overwrite. |
+-------------+----------------------+---------------------------------------------------------------------------------------------------+
| 'position' | position=A1 | Default is 'A1'. Use the excel notation. |
+-------------+----------------------+---------------------------------------------------------------------------------------------------+
| 'newline' | newline | Start a new row. The column will be the one of the last 'position' statement. |
+-------------+----------------------+---------------------------------------------------------------------------------------------------+
| 'str', 's' | s=hello world | Set the given string on the given position. The current position will be shifted one to the right.|
| | | If the string contains newlines, option 'b' (base64) should be used. |
+-------------+----------------------+---------------------------------------------------------------------------------------------------+
| 'b' | b=aGVsbG8gd29ybGQK | Same as 's', but the given string has to Base64 encoded and will be decoded before export. |
+-------------+----------------------+---------------------------------------------------------------------------------------------------+
| 'n' | n=123 | Set number on the given position. The current position will be shift one to the right. |
+-------------+----------------------+---------------------------------------------------------------------------------------------------+
| 'f' | f==SUM(A5:C6) | Set a formula on the given position. The current position will be shift one to the right. |
+-------------+----------------------+---------------------------------------------------------------------------------------------------+
Create a output like this: ::
position=D11
s=Hello
s=World
s=First Line
newline
s=Second line
n=123
This fills D11, E11, F11, D12
In Report Syntax::
# With ... AS _XLS (token explicit given)
10.sql = SELECT 'position=D10' AS _XLS
, 's=Hello' AS _XLS
, 's=World' AS _XLS
, 's=First Line' AS _XLS
, 'newline' AS _XLS
, 's=Second line' AS _XLS
, 'n=123' AS _XLS
# With ... AS _XLSs (token generated internally)
20.sql = SELECT 'position=D20' AS _XLS
, 'Hello' AS _XLSs
, 'World' AS _XLSs
, 'First Line' AS _XLSs
, 'newline' AS _XLS
, 'Second line' AS _XLSs
, 'n=123' AS _XLS
# With ... AS _XLSb (token generated internally and content is base64 encoded)
30.sql = SELECT 'position=D30' AS _XLS
, '' AS _XLSb
.. _`excel-export-sample`:
Excel export samples (54 is a example )::
# From scratch (both are the same, one with '_excel' the other with '_link')
SELECT CONCAT('d:new.xlsx|t:Excel (new)|uid:54') AS _excel
SELECT CONCAT('d:new.xlsx|t:Excel (new)|uid:54|M:excel|s:1') AS _link
# Template
SELECT CONCAT('d:final.xlsx|t:Excel (template)|F:fileadmin/template.xlsx|uid:54') AS _excel
# With parameter (via SIP) - get the Parameter on page 'exceldata' with '{{arg1:S}}' and '{{arg2:S}}'
SELECT CONCAT('d:final.xlsx|t:Excel (parameter)|uid:54&arg1=hello&arg2=world') AS _excel
Best practice
"""""""""""""
To keep the link of the Excel export close to the Excel export definition, the option :ref:`report-render` can be used.
On a **single** T3 page create **two** QFQ tt-content records:
tt-content record 1:
* Type: QFQ
* Content::
render = single
10.sql = SELECT CONCAT('d:new.xlsx|t:Excel (new)|uid:54|M:excel|s:1') AS _link
tt-content record 2 (uid=54):
* Type: QFQ
* Content::
render = api
10.sql = SELECT 'position=D10' AS _XLS
, 's=Hello' AS _XLS
, 's=World' AS _XLS
, 's=First Line' AS _XLS
, 'newline' AS _XLS
, 's=Second line' AS _XLS
, 'n=123' AS _XLS
.. _dropdownMenu:
Dropdown Menu
-------------
Creates a menu with custom links. The same notation and options are used as with regular QFQ links.
Format String::
||