T4 tags: Convert Excel to HTML
Description
This tag takes an Excel spreadsheet in the Media Library and publishes the data within that file as an HTML table on the page. The Excel to HTML tag is added to a Content Layout (to create the table) and to a Page Layout (to format the table) and content is added to the site using the Content Type.
There's more than one way to integrate external data in TERMINALFOUR and the method you choose can depend on the type of data and what you want to do with it.
Content Layout tag
For the example we are using a very basic spreadsheet:
Create a Content Type with a media element. In the Content Layout, add the tag. There are two attributes for this tag:
| Item | Description |
|---|---|
| name | Name of the media element in the content type that contains the Excel file. |
| output-sheet-name | Setting this attribute to true will output the Excel sheet names. If this attribute is not included in the tag, sheet names will not be output on the page. |
Examples
<t4 type="exceltohtml" name="media-file" />
<t4 type="exceltohtml" name="media-file" output-sheet-name="true" />
The output generated would be:
<div id="excel-285-media-file">
<div id="table-285-1-media-file">
<table class="excel-285-media-t1">
<colgroup>
<col width="144">
<col width="144">
<col width="56">
</colgroup>
<tbody>
<tr class="excel-285-media-r1">
<td class="excel-285-media-c1">Fruit</td>
<td class="excel-285-media-c1">Vegetables</td>
<td class="excel-285-media-c1"> </td>
</tr>
<tr class="excel-285-media-r1">
<td>Apple</td><td>Parsnip</td>
</tr>
<tr class="excel-285-media-r1">
<td>Orange</td><td>Carrot</td>
</tr>
<tr class="excel-285-media-r1">
<td>Banana</td><td>Spinach</td>
</tr>
</tbody>
</table>
</div>
</div>
Page Layout tag
A tag is added to the header of the Page Layout within the <style type="text/css"> tag in the header.
| Item | Description |
|---|---|
| name | Name of the media element in the Content Type that contains the Excel file. |
| default-font |
If set, this allows a font-family and font-size to be configured and used for all tables in the Excel spreadsheet. |
| sheet-numbers-element | if set, this allows specific sheets in the Excel spreadsheet to be published to the page. By default, all sheets within the Excel file are published onto the page. To specify the sheets to be published, create a plain text element in the content type (for example, called "Sheet Numbers") and specify this element name in the sheet-numbers-element attribute. When adding the content using this template the ‘Sheet Numbers’ element should be populated with a comma separated list of sheet numbers example: 1,3,4, |
Example
<style type="text/css">
<t4 type="exceltohtml" name="media-file" default-font="font-family:arial;font-size:8pt;" sheet-numbers-element="Sheet Numbers" />
</style>
<style type="text/css">
.b1{white-space-collapsing:preserve;}
.excel-285-media-t1{border-collapse:collapse;border-spacing:0;}
.excel-285-media-r1{height:14.5pt;}
.excel-285-media-c1{white-space: pre-wrap; text-align: left; vertical-align: bottom; font-weight: bold; color: black; font-family:arial;font-size:8pt; }
</style>
Notes
- If a title is in one cell in the Excel file, it appears in one cell in the HTML table. If you want a title to span over more than one column in the HTML table then cells must be merged in the Excel file.
- Rows merged in an Excel file can cause problems as there is no way to merge rows in a HTML table. The formatting of these rows must be corrected before they is displayed in the HTML correctly.