TreeGrid v6.0
Export to Excel and PDF
Export of grid data to MS Excel in HTML format, to CSV, to PDF or any other format.
It requires communication with server, because JavaScript can never create files on client side because of security restrictions.
Basic definition of export to MS Excel XLS, CSV or any other custom type.
It can be client export (only XLS or CSV) or server export (XLS, CSV or any custom type).
<treegrid,bdo> string Export_Url .
Destination for page that generates data to export to XLS, CSV or any other format.
This server URL should return the exported data as XLS file in HTTP content-type="application/vnd.ms-excel" or CSV as “text/csv” or PDF as “application/pdf”
This communication is not done by AJAX, but always by hidden form, so it can be used even if no ActiveX is supported.
Export cannot use Soap or Get communication.
If you encounter problems with export of international (non-English) characters, add to your page, to <head> section:
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
In client export (Export_Type=’XLS’ or ‘CSV’) to this URL is uploaded complete exported data and the server script should just return them back.
In server export (Export_Type=’Settings’ or other value) to this URL is uploaded actual grid configuration and the server script should create and return the exported data by itself.
<treegrid,bdo> string Export_Data [“Data”] .
The name of form input in that will be uploaded exported data or configuration to Export_Url
<treegrid,bdo> string Export_Type [“”] chg 6.7 .
What and how will be send to server to export, case insensitive.
It specifies the format of client export or the configuration sent to server for server export.
empty
To server will be send full report in XLS/CSV format (chosen by ExportFormat), generated according to Client export settings.
XLS
To server will be send full report in XLS format, generated according to Client export settings.
It replaces Export_Type=”0” and <Cfg ExportFormat=”1”/> from versions prior 6.7.
CSV
To server will be send full report in CSV format, generated according to Client export settings.
It replaces Export_Type=”0” and <Cfg ExportFormat=”2”/> from versions prior 6.7.
Settings
To server will be send only configuration (sorting, grouping, filters) and columns (positions, visibility and widths) in XML.
The server must generate the requested report in XLS or CSV according to the settings and stored data and return it to client.
See Server export.
It replaces Export_Type=”1” from versions prior 6.7.
Expanded
Sends ids of all expanded rows in <Cfg Expanded/> attribute, comma separated.
Changes
Used usually with Settings for server export.
Sends modified rows - rows with set attribute Added, Deleted, Moved or Changed (modified values). The rows are sent in <Changes> tag.
When set, the export does not save the changes to server before export, but includes them in the export request.
This attribute supports also all other settings like Cookie or Data defined for _Type, see Upload_Type. Also Export_Flags are supported if defined, see Upload_Flags.
<Cfg> int ExportCols [0] new 6.0;upd 6.7.
If and which menu will be shown before doing export.
0 – no menu
1 – menu with columns to select which columns will be exported. Only columns with CanExport=’1’ are listed.
2 – (new 6.7) menu with column and also with options for export
<Actions> Export Attached to event OnClickButtonExport .
Creates report in XLS or CSV or a request for the server report and sends it to server
PDF report can be done only on server, as server export. It is similar to other server export types (Excel), except it uses its own data source ExportPDF and has its own action ExportPDF that fills some other options for generating PDF.
The client sends also these parameters (along with configuration in XML in Data parameter):
PDFPageSize (0 = Auto, 1 = Letter, 2 = Note, 3 = Legal, 4 = A0, 5 = A1, 6 = A2, 7 = A3, 8 = A4, 9 = A5, 10 = A6, 11 = A7, ... )
PDFPageOrientation (0 = Portrait, 1 = Landscape)
PDFFitPage (0 = Real size, 1 = Shrink to width, 2 = Resize to width, 3 = Single page)
PDFText (0 = Image only, 1 = Selectable text, 2 = Embedded fonts)
ExportFormat = "pdf"
The parameter values are chosen in PDF print dialog, their options are defined in Text.xml and can be redefined to any other, because these parameters are not used by TreeGrid itself.
TreeGrid sends a request to server for PDF file and server creates and returns it. And on server side there is similar “client” application with TreeGrid that prints the grid into browser on server, prints it to PDF and returns the PDF back to client. For printing grid synchronously is possible to use PrintTreeGrid global function. To accept the sent settings, the server side html page with TreeGrid should get the XML configuration sent from client, usually in Data_Bonus.
For printing on server side see the printing options.
<treegrid,bdo> string ExportPDF_Url new 6.7 .
Destination for page that generates data to export to PDF or any other format.
This server URL should return the exported data as PDF file in HTTP content-type="application/pdf"
This communication is not done by AJAX, but always by hidden form, so it can be used even if no ActiveX is supported.
Export cannot use Soap or Get communication.
To this URL is uploaded actual grid configuration and the server script should create and return the exported data by itself.
<treegrid,bdo> string ExportPDF_Data [“Data”] new 6.7 .
The name of form input in that will be uploaded exported data or configuration to Export_Url
<treegrid,bdo> string ExportPDF_Type [“settings”] new 6.7 .
Which configuration will be send to server for export, case insensitive.
Settings
To server will be send configuration (sorting, grouping, filters) and columns (positions, visibility and widths) and print settings in XML.
The server must generate the requested report in PDF according to the settings and stored data and return it to client.
Expanded
Sends ids of all expanded rows in <Cfg Expanded/> attribute, comma separated.
Changes
Sends modified rows - rows with set attribute Added, Deleted, Moved or Changed (modified values). The rows are sent in <Changes> tag.
When set, the export does not save the changes to server before export, but includes them in the export request.
This attribute supports also all other settings like Cookie or Data defined for _Type, see Upload_Type. Also Export_Flags are supported if defined, see Upload_Flags.
<Cfg> int ExportPDFCols [0] new 6.7.
If and which menu will be shown before doing export to PDF.
0 – no menu
1 – menu with columns to select which columns will be exported. Only columns with CanExport=’1’ are listed.
2 – (new 6.7) menu with column and also with options for export to PDF
<Actions> ExportPDF Attached to event OnClickButtonExportPDF new 6.7 .
Creates a request for report in PDF and sends it to server
<Cfg> int PDFPageSize [8] Saved to cookies, to not load it, set PrintLap=’1’ new 6.7.
It can be set also in Print to PDF menu (when ExportPDFCols = 2) and is sent to server along with the request. It is not used by TreeGrid.
The options are defined in Text.xml, by default are: 0 = Auto, 1 = Letter, 2 = Note, 3 = Legal, 4 = A0, 5 = A1, 6 = A2, 7 = A3, 8 = A4, 9 = A5, 10 = A6, 11 = A7, ...
<Cfg> int PDFPageOrientation [0] Saved to cookies, to not load it, set PrintLap=’1’ new 6.7.
It can be set also in Print to PDF menu (when ExportPDFCols = 2) and is sent to server along with the request. It is not used by TreeGrid.
The options are defined in Text.xml, by default are: 0 = Portrait, 1 = Landscape
<Cfg> int PDFFitPage [1] Saved to cookies, to not load it, set PrintLap=’1’ new 6.7.
It can be set also in Print to PDF menu (when ExportPDFCols = 2) and is sent to server along with the request. It is not used by TreeGrid.
The options are defined in Text.xml, by default are: 0 = Real size, 1 = Shrink to width, 2 = Resize to width, 3 = Single page
It is supported only by some PDF converters, namely by Winnovative HtmlToPdf Converter.
<Cfg> int PDFText [1 Saved to cookies, to not load it, set PrintLap=’1’ new 6.7.
It can be set also in Print to PDF menu (when ExportPDFCols = 2) and is sent to server along with the request. It is not used by TreeGrid.
The options are defined in Text.xml, by default are: 0 = Image only, 1 = Selectable text, 2 = Selectable text with embedded fonts
It is supported only by some PDF converters, namely by Winnovative HtmlToPdf Converter.
API event bool OnExportStart (TGrid grid, bool pdf) new 6.0; upd 7.0.
Called before the export report is generated.
(since 7.0) pdf is true for export to PDF
Return true to cancel export.
API event string OnExport (TGrid grid, string data, bool pdf) upd 7.0 .
Called after the export or configuration is created and before it is sent to server.
data is the created export or configuration.
(since 7.0) pdf is true for export to PDF
Return true to suppress the export.
Or return new or updated data to send.
API method void SendExport (string data, string url, string name) .
Sends the data to server as export or configuration.
Calls also OnExport.
To server (to Export_Url) is sent whole prepared report that can just be returned by this page back to Excel.
In “custom” parameter “ExportFormat” is sent chosen file extension “xls” or “csv”
Can be used only for small grids, because to server are send all data back.
Cannot be used with server paging or server child paging.
Space rows are not exported.
The server script just returns the data from Data attribute back to client with changed content type.
For MS Excel use ContentType="application/vnd.ms-excel" and charset: utf-8.
Examples of server script for client export
Point Export_Url to simple server script that will just return the received data:
ASP.NET
<%@ Page Language="C#" ContentType="application/vnd.ms-excel" %>
<% Response.AppendHeader("Content-Disposition","attachment; filename=\"Export.xls\"");
Response.AppendHeader("Cache-Control","max-age=1, must-revalidate");
Response.Write(HttpUtility.HtmlDecode(Request["Data"])); %>
ASP
<% Session.Codepage=65001
Response.ContentType = "application/vnd.ms-excel"
Response.Charset= "utf-8"
Response.AddHeader "Cache-Control","max-age=1, must-revalidate"
Response.AddHeader "Content-Disposition", "attachment; filename=""Export.xls"""
Response.Write Replace(Replace(Replace(Request.Form("Data"),"<","<"),">",">"),"&","&") %>
JSP
<%@page contentType="application/vnd.ms-excel"%><%@page pageEncoding="UTF-8"%><%
request.setCharacterEncoding("utf-8");
response.addHeader("Content-Disposition","attachment; filename=\"Export.xls\"");
response.addHeader("Cache-Control","max-age=1, must-revalidate");
out.print(request.getParameter("Data").replaceAll("<","<").replaceAll(">",">").replaceAll("&","&").replaceAll(""","\"").replaceAll("'","'")); %>
PHP
<?php
header("Content-Type: application/vnd.ms-excel; charset=utf-8");
header("Content-Disposition: attachment; filename=\"Export.xls\"");
header("Cache-Control: max-age=1; must-revalidate");
$XML = array_key_exists("Data",$_REQUEST) ? $_REQUEST["Data"] : "";
if(get_magic_quotes_gpc()) $XML = stripslashes($XML);
echo html_entity_decode($XML); ?>
Any other language can generate the export similarly
Client export settings
<Cfg> int ExportFormat [1] new 6.0;chg 6.7.
Since 6.7 it is predefined format in Export options menu and predefined format when Export_Type is empty.
Possible values: 1 – XLS, 2 – CSV.
<Cfg> string ExportPrefix .
HTML code added into Excel report between Excel header and TreeGrid table
<Cfg> string ExportPostfix .
HTML code added into Excel report between exported TreeGrid table and enclosing </body> tag
<Cfg> string[] ExportType [“Expanded,Indent”] upd 7.1 .
The way of client export (or server export via TreeGrid.dll/so) to Excel, comma separated list of keywords, case insensitive
Filtered export also filtered rows
Expanded exports all rows expanded
Hidden export all columns, including hidden (but only columns with CanExport=’1’)
Outline export tree as Excel outline, for CSV adds new column with the outline as characters ‘#’
Indent indent levels in main column
Strings (XLS) store strings in file twice to preserve their type a spaces, set it if you want to export all strings as strings and not chosen by Excel.
Dates (XLS) export dates as strings only (useful for exporting Hirji dates). In this case the <C> ExportFormat formats directly the date string
Rtl (new 7.1) The columns will be exported in reversed order when the grid is in Rtl mode
<Cfg> int ExportRows [500] .
For how many rows will be displayed and updated progress bar. Displaying progress bar suppresses also message about slow script.
0 never shows progress bar. The progress bar is shown only if the count of exported rows is higher than the ExportRows value.
When shown the progress bar in IE there is shown message after finish and a user must click to download button – otherwise IE blocks the file download.
Too small value can slow down the export, reasonable value is about 100.
<cell> string ExportValue .
Cell value used for export to Excel, see also OnGetExportValue event.
<C><cell> string ExportStyle new 7.0 .
The CSS style added to the cell when exported.
For example to align the cell vertically to top, set ExportStyle=’vertical-align:top’.
API event string OnGetExportValue (TGrid grid, TRow row, string col, string str) .
Called to get value for export to Excel
str is html tag ending character (‘>’), so to return just string return str+your_value and to return formatted number return your_format+str+your_value.
<C> bool CanExport [1/0] .
If the column will be exported
By default Panel has 0 and other columns 1.
<I> bool CanExport [1] .
If row will be exported to Excel.
Space rows are never exported.
<Cfg> int ExportRound .
If set, the Float number will be rounded to this count of decimal digits.
<Cfg> string CSVValueSeparator [“;”] new 7.1 .
String or character that separates individual values in export to CSV
<Cfg> string CSVRowSeparator [“
”] new 7.1 .
String or character that separates individual rows in export to CSV
<Cfg> string CSVQuote [‘”’] new 7.1 .
Character that quotes value in export to CSV, if it contains one of the ValueSeparator, RowSeparator or Quote.
The quote characters are doubled in the value
<Cfg> string CSVLevel [“#”] new 7.1 .
String or character to show level deep, when ExportType=’Outline’
To server is sent just actual grid configuration – sorting, grouping, filters, search and column width, positions and visibility.
In “custom” parameter “ExportFormat” is sent chosen file extension “xls”, “csv” or “pdf"
For Excel export - in “custom” parameter “ExportType” is sent chosen ExportType flags.
For PDF export – in “custom” parameters PDFPageSize, PDFPageOrientation, PDFFitPage and PDFText are sent chosen PDF options.
It is possible to upload also other data from client like changes, set Export_Type=”Settings,Changes”.
The configuration is sent as standard TreeGrid XML in Data attribute.
Server must generate the report from data on server by itself and return report to client.
Use this way for larger grid and/or for grids with server paging or server child paging.
Use it also if you want to generate different then standard report or you want to generate report for another program then MS Excel.
XML structure of request for server Export
Used as request sent to data source Export to get the exported grid when set server side export (Export_Type=”Settings”).
It contains session identification and grid configuration to sort, group, filter and search exported data on server.
It contains also columns settings – width, position and visibility.
<Grid>
<IO ... attributes with request settings ... /> (Session attribute)
<Cfg ... attributes with grid settings read from cookies ... /> (Sort, Group, ReSort, ReCalc, TimeZone attributes)
(SearchAction, SearchExpression, SearchType, SearchMethod, SearchDefs, SearchCols attributes)
(Focused, FocusedCol, FocusedPos attributes)
(PrintPageBreaks, PrintRows, PrintExpanded, PrintFiltered)
<Filters> (list of filters)
<I ... row and cell attributes ... /> (id attribute, cell values and cell Filter attributes)
... More tags <I> if more filters are in grid ...
</Filters>
<LeftCols>
<C Name='column name' Width=’...’ Visible=’0/1’ />
... more tags <C> for other columns ...
</LeftCols>
<Cols>
<C Name='column name' Width=’...’ Visible=’0/1’ />
... more tags <C> for other columns ...
</Cols>
<RightCols>
<C Name='column name' Width=’...’ Visible=’0/1’ />
... more tags <C> for other columns ...
</RightCols>
</Grid>