TreeGrid documentation
This document contains information about TreeGrid to XLSX export.| Rows to export | Offers header and footer rows selection - only the checked rows will be exported. |
|---|---|
| Columns to export | Offers columns selection - only the checked columns will be exported. With AutoCols configuration (SpreadSheet scenarios) data columns aren't offered and always exported (up to GetLastDataCol). |
| Options: | |
| Export all rows expanded | Ignores Expanded and exports whole Row Tree(s) expanded. |
| Export all rows (unfiltered) | Ignores filter settings. |
| Export tree as Excel outline | Convers Row Tree to the Excel outline. |
| Indent tree in main column | Indents child rows of a Row Tree in the main column (according to their depth-level). |
| Export hidden rows / columns | Ignores hidden rows /columns setting. |
| Export columns in reversed order | Exports columns in reversed order (as a part of RTL support) |
| Freeze head rows | Freezes (anchors) header rows. |
| Freeze left columns | Freezes (anchors) left columns. |
| Export only selected rows | Exports only selected rows. |
| Export only selected columns | Exports only selected columns. |
| Export only selected cells | Exports only selected cells (fills the rest with blanks). |
| No HTML | Converts html as literal string if true |
opts parameter when instantiating the Converter object or by SetOptions() function.opts parameter.| valign | "top" | Default vertical align |
|---|---|---|
| fontSize | 10 | Default font size |
| fontFace | "Calibri" | Default font face |
| freezeHead | false | Freeze head rows |
| freezeLeft | false | Freeze left columns |
| onlySelRows | false | Export only selected rows |
| onlySelCols | false | Export only selected columns |
| onlySelCells | false | Export only selected cells |
| colorSelRows | "#ffff00" | Colorize selected rows (turned of if false) |
| colorSelCols | "#ffff00" | Colorize selected columns (turned of if false) |
| colorSelCells | "#00ffff" | Colorize selected cells (turned of if false) |
| emptyCells | true | Export empty cells as actual cells with empty string - overflowing text of preceeding cells will be hidden but the resulting Xlsx file of large Grids may be larger |
| showForcedExportCheckbox | true | Columns with CanExport='2' (export & don't show in the Export Menu) will be shown in the Export Menu with disabled checkbox |
| defTextShadowColor | "#999999" | Default TextShadowColor (RGB value) |
| defTextShadowPattern | "lightGray" | Default TextShadow emulation fill pattern (lightGray = mid-dense dots) |
| addTableBorder | true | Add border around the table and it's sections (header, footer, left and right columns) |
| nohtml | false | Converts html as literal string if true |
| Fixed rows & columns |
Any number of rows/cols connected to the left panel (with row numbers) resp. to the top bar (with col names) will be fixed in the resulting XLSX also. XLSX does not support fixing rows/cols in the middle, on the right side or at the bottom and such rows/cols will be converted as normal. |
| Row height & column width | Row height & col width are converted, do not expect exact match though (as sizes in XLSX are not stated in pixels). |
| Hidden rows & columns | Hidden rows/cols will be converted as expected. |
| Colspan & rowspan | Colspan & rowspan will be converted as expected except for spanning through row/col tree. |
| Row trees |
Row trees will be converted to row outlines (groups). Outline extent indicators with collapse/expand buttons will be shown in the left panel, independently on the position (col) of the original tree. Indentation of MainCol's content in child rows is supported. ReversedTree is supported. Expanded is supported. |
| Multi-row text | Endlines in cells of Lines and Html Type will be converted as expected. |
| Horizontal align |
Align is converted as expected, except "Scroll" is changed to ordinary "center". |
| Vertical align | VAlign is supported. |
| Images | See HTML tags: <img> |
| Filters | When the filter row is present (and exported) and a filter is active, it is exported as a cell with operator sign in bold red before a filter value. |
| Global attributes | ||
| <Cfg> | ExportAttrs new | Comma separated list of attribute names which will be applied during export (if present where relevant). |
| <Cfg> | ExportFormat | A new possible value 3 - XLSX. |
| <Cfg> | ExportType |
Filtered, Expanded, Hidden, Outline, Indent, Rtl (Strings, Dates are ignored) With the Hidden flag hidden rows are converted as hidden (otherwise they're just thrown out). |
| <Cfg> | ExportRound | |
| <Cfg> | ExportCols | Apart from column list the Xlsx export menu dialog contains also selects of Head and Foot rows to export (these with CanExport='1'). |
| <Cfg> | NoFormatEscape | Applied to Date, Int, Float, Text, Lines and Pass types (as original). |
| <Cfg> | BaseUrl | Applied to Img and Link types (as original). |
| <Cfg> | ReversedTree | |
| <Cfg> | DynamicBorder | Except option value 1 does the same as 2 (checks all rows and columns). |
| <Cfg> | FormulaEditing | If set and non-zero, convertor will search for formulas (EFormula attributes or cell values with '='). |
| <Cfg> | MainCol | Main column for indentation of child rows of a row Tree. |
| <Cfg> | MainCol | Main column for indentation of child rows of a row Tree. |
| Column / Row / cell attributes | ||
| <C> | CanExport | |
| <I> | CanExport | Except option value 2 - only its children. |
| <C> <cell> | ExportFormat | |
| <C> <cell> | ExportStyle | Supported CSS: background-color (colorname or #hex), text-align, vertical-align, white-space (wrap). |
| <cell> | ExportValue | |
| <cell> | EFormula | See Editable cell formulas - supported features. |
| <I cell> | Visible | |
| <Space cell> | Visible | |
| <I> | MidHtml | |
| <I> | MidHtml2 | |
| <I> | LeftHtml | |
| <I> | RightHtml | |
| <C> <cell> | Format | See Text format - supported features. |
| <C> <cell> | Format | See Number format - supported and unsupported features. |
| <C> <cell> | Format | See Date format - supported and unsupported features. |
| Fired API events | ||
| API event | OnGetExportValue | Called after the Xlsx exporter receives a cell value (or ExportValue if present), the value is passed as the 4th parameter. |
| <b> | bold text |
| <i> | italic style |
| <u> | underlined text |
| <s> | strikethrough |
| <span style="color: xxx"> | coloured text |
| <a href="xxx"> | link |
| <img> | image |
| 0 | Place for a digit, displays digit or 0. If there are more digits than count of 0 and # in integer part, all the rest digits are written left to the first 0 or #. |
| # | Place for a digit, displays digit or nothing if there are no more digits. Has sense only for decimal part or exponent definition. Note: Excel always displays the decimal separator even thought the decimal part is empty. |
| . |
(Dot) Decimal separator. Displays decimal separator defined in file Text.xml, tag <Format>, attribute DecimalSeparator. (dot by default). Can be only used only once. |
| , |
(Comma) This character has two meanings: If this is placed between any digits characters ('0' or '#') left to decimal separator, there will all three digits separated by thousand separator defined in file Text.xml, tag <Format>, attribute GroupSeparator. (comma by default). Unsupported: If this is placed between last digit and decimal separator (or to the end if decimal separator is not used), the value will be divided by 1000. |
| % |
Place for '%' character (it is defined in file Text.xml, tag <Format>, attribute Percent). If it is present, the number is also multiplied by 100. |
| E, e, E-, E+, e-, e+ Result will use exponential notation. To this location is placed 'E' or 'e' letter followed by digits of exponent. | |
| ; |
(Semicolon) There can be up to three different formats separated by semicolon. The first format is used for positive numbers (and for 0 if the third format missing), second for negative numbers (without minus sign) and third for 0. Remember, from the negative part is read only string before number and after the number, all other (the number format) is used always from positive part. So the negative part can be used only to remove, replace or change position of the minus or change CSS attribute like color by the prefix and postfix. |
| \ |
Escape character, the next character is placed to output without any changes. Use to place format characters as 0 # . , E e + - \ " ' %. |
| 'xxx' or "xxx"
Characters in quotes or double quotes are placed to output without any changes. Use to place format characters as 0 # . , E e + - \ " ' %. | |
| d | Short date | ("M/d/yyyy") | |
| D | Long date | ("d. MMMM yyyy") | |
| t | Short time | ("H:mm") | |
| T | Long time | ("H:mm:ss") | |
| f | Long date + short time | ("d. MMMM yyyy H:mm") | |
| F | Long date + long time | ("d. MMMM yyyy H:mm:ss") | |
| g | Short date + short time | ("M/d/yyyy H:mm") | |
| G | Short date + long time | ("M/d/yyyy H:mm:ss") | |
| m, M | Day and month | ("d. MMMM") | |
| s | Sortable date time | ("yyyy-MM-ddTHH:mm:ss") | |
| u | Universal sortable date time | ("yyyy-MM-dd HH:mm:ssZ") | Note: timezone offset is not supported, the "Z" will be literal |
| r, R | RFC1123 | for example: Tue, 13 Jan 2004 18:12:34 GMT |
| y, Y | Month and year | for example: January 2004. |
| U | Universal date time | for example: 13. January 2004 17:12:34. This date time is converted to GMT ! |
| : | Place for time separator, defined in file Text.xml, tag <Format>, attribute TimeSeparator. (colon by default). |
| / | Place for date separator, defined in file Text.xml, tag <Format>, attribute DateSeparator. (slash by default). |
| d | Month days 1 - 31 |
| dd | Month days 01 - 31 |
| ddd | Weekdays Sun - Sat |
| dddd | Weekdays Sunday - Saturday |
| dddddd | Month days 1st - 31st |
| M | Month 1 - 12 |
| MM | Month 01 - 12 |
| MMM | Month Jan - Dec |
| MMMM | January - December |
| yy | Year 00 - 99 |
| yyyy | Year 0000 - 9999 |
| h | Hours 1 - 12 |
| hh | Hours 01 - 12 |
| H | Hours 0 - 23 |
| HH | Hours 00 - 23 |
| m | Minutes 0 - 59 |
| mm | Minutes 00 - 59 |
| s | Seconds 0 - 59 |
| ss | Seconds 00 - 59 |
| t | First letter of AM/PM designator, dependent on local language settings. |
| tt | AM/PM designator, dependent on local language settings. |
| \ | Escape character. |
| 'xxx' or "xxx" | Characters in quotes or double quotes are placed to output without any changes. |
| % | Place before format when use one character user format like 'd' (=> '%d') to not be taken as standard format. |
| Custom text in panels & headers | XLSX does not support changing values in the left panel (with row numbers) resp. the top bar (with col names), TreeGrid's left panel resp. top bar will be converted as a normal row/col. |
| Cell types |
Enums, Checkboxes, Buttons as cell types are not supported by XLSX. Enum will be converted to an actual (selected) value (as a literal). Checkbox will be converted to 0/1 according to its state (as a number). Button will be converted to a literal if it's textual or an image if it's image-based. |
| HTML tags | Nested tags are not supported yet. |
| 8 | Hidden digit, the digit will not be displayed. |
| , |
The second meaning is not supported: If this is placed between last digit and decimal separator (or to the end if decimal separator is not used), the value will be divided by 1000. |
| u | Universal sortable date time | ("yyyy-MM-dd HH:mm:ssZ") | Note: timezone offset is not supported, the "Z" will be literal |
| ddddd | Weekdays S - S |
| ddddddd | Year weeks 1 - 52 |
| dddddddd | Year weeks 01 - 52 (new 9.0) |
| D | not used |
| DD | The rest of days in week, in count of weeks from 1/1/1970 (it is not weekday!) (new 6.1) |
| DDD | Year day 0 - 365 (new 6.1) |
| DDDD | Count of days from 1/1/1970, for duration in days (new 6.1) |
| DDDDD | Count of weeks from 1/1/1970, for duration in weeks, see DD (new 6.1) |
| DDDDDD | Tenths of month days: empty,1,2,3 (new 6.3) |
| DDDDDDD | Month days: 1-9,0-9,0-9,0-1 (new 6.3) |
| MMMMM | Year quarters, I, II, III, IV |
| MMMMMM | Year halves, I, II |
| y | Year 0 - 99 |
| yyy | Count of years from 1/1/1970, for duration in days (new 6.1) |
| hhh | Count of hours from 1/1/1970 00:00:00, for duration in hours (new 6.1) |
| hhhh | Tenths of hours empty or 1 (new 6.3) |
| hhhhh | Hours 1-9, 0-2 (new 6.3) |
| HHH | not used |
| HHHH | Tenths of hours empty or 1 (new 6.3) |
| HHHHH | Hours 0-9, 0-9, 0-3 (new 6.3) |
| mmm | Count of minutes from 1/1/1970 00:00:00, for duration in minutes (new 6.1) |
| mmmm | Tenths of minutes 0 - 5 (new 6.3) |
| mmmmm | Minutes 0 - 9 (new 6.3) |
| sss | Count of seconds from 1/1/1970 00:00:00, for duration in seconds (new 6.1) |
| ssss | Tenths of seconds 0 - 5 (new 6.3) |
| sssss | Seconds 0 - 9 (new 6.3) |
| f | Hundreds of milliseconds, 0 - 9 (new 6.3) |
| ff | Tenths of milliseconds 00 - 99 (new 6.3) |
| fff | Milliseconds - 000 - 999 (new 6.3) |
| ffff | Tenths of milliseconds 0 - 9 (new 6.3) |
| fffff | Milliseconds - 0 - 9 (new 6.3) |
| z | Timezone hours -12, +0, +13 |
| zz | Timezone hours -12, +00, +13 |
| zzz | Timezone hours and minutes, -12:00, +0:00, +13:00 |
| Text wrapping | XLSX supports wrapping text according to column width (or not at all) - apart from HTML, text cannot be wrapped according to newlines only. |