TreeGrid v6.0

Export to Excel and PDF

TreeGrid documentation index

 

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.

 

Export to Excel

 

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

 

Export to PDF

 

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.

 

 

Export API

 

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.

 

 

Client export

 

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"),"&lt;","<"),"&gt;",">"),"&amp;","&") %>

 

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("&lt;","<").replaceAll("&gt;",">").replaceAll("&amp;","&").replaceAll("&quot;","\"").replaceAll("&apos;","'")); %>

 

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              [“&#x0a;”]                                                                             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’

 

Server export

 

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>