CS

DataGrid data export

Home / Documentation / Administrator guide /

DataGrid data export

CS

1. General information

DataGrid data export allows users to download the data of a view table to a file. Export is started by an icon in the header of the view table and can be disabled in the DataGrid control settings. A detailed description of DataGrid is provided in a separate guide View page - DataGrid or Edit form - DataGrid.

Depending on the type of the view table, the export runs in one of three ways:

  • A row view table (on a view page as well as in an edit form) and the History control open the export dialog, in which the format and the options described in chapters 2 and 3 are chosen.
  • A statistical (aggregate) view table, including the statistical LiteDataGrid control, exports the data without a dialog directly to an XLSX file – see chapter 4.
  • The history of a single record exports the data without a dialog directly to an XLSX file – see chapter 5.

Export is not available in a portlet, in the picker, in the details panel, in the DataSet control and in the row LiteDataGrid control.

1.1. Exported records

  • Records from all pages of the view table are exported, not only from the displayed page. The condition of the database query, the time period, column filters, the composed filter, the search term, sorting and the “Load only first” setting apply.
  • Marking records with check boxes does not limit the export.
  • If “Show search results only” is checked and the query does not have “Load only first” set, the export contains at most one page of results; without a search term the export is empty.
  • If “Load only first” is set (and likewise in an instance without a valid license, where at most 100 records are loaded), the view table first loads the given number of records and applies column filters and the search term only to them. The export, on the contrary, filters first and only then limits the number of records. When a limit is combined with a filter or a search, the export may therefore contain different records than the view table displays.
  • If “Remove records with duplicate” is set in the database query, duplicate records are removed from the export as well.

1.2. Exported columns

  • Without the “All columns” option, the columns of the view table are exported as customized by the user.
  • With the “All columns” option, all database controls of the source table and of the tables joined in the query that the user has rights to are exported, plus the “(ID)” and “(PID)” columns.
  • Columns whose value is visible only to the owner or co-owner of the record are never exported.
  • Values of password columns (identifier starting with “password” or “md5”) are replaced by the characters “*****” in all formats.

2. Export formats

The following formats are offered in the export dialog:

2.1. Data (MS Excel)

  • The default format when the export dialog is opened.
  • Generates an Office Open XML file (XLSX).
  • The first row contains the name of the view table, followed by the column header. The header uses the short name of the control, if it is filled in.
  • A date is written as an MS Excel date value, including the time if the value contains it.
  • Values of encrypted TextArea and RichTextBox columns are replaced by the characters “*****”.
  • Supports the footer option with column totals.
  • After a successful export, the external function “NETGenium.DataGrid2Excel” is triggered with parameters: file path, query identifier in the form “Q” followed by the query ID, form ID, date from and date to of the time period of the view table.
  • If the number of columns or rows exceeds the limits defined in the “MaxExcelColumns.txt” and “MaxExcelRows.txt” configuration files located in the “Config” directory, the export automatically switches to the CSV format and the external function “NETGenium.DataGrid2Excel” is not triggered.

2.2. Data (MS Excel 97-2003)

  • Generates a BIFF8 file (XLS).
  • The content of the file is the same as for the XLSX format, but the external function “NETGenium.DataGrid2Excel” is not triggered.
  • Subject to the same automatic switch to CSV as the XLSX format when limits are exceeded.

2.3. Data (CSV)

  • Values are separated by a semicolon (;), rows end with the CR LF characters.
  • File encoding is UTF-8 with BOM.
  • The file header contains the short names of the controls, if they are filled in, otherwise their names.
  • Only a value that contains a semicolon or a line break, or that starts or ends with a quotation mark, is enclosed in quotation marks; quotation marks inside the value are doubled.
  • A date is written in the date format of the user, including the time with seconds.
  • CSV is also the target format for the automatic switch from XLSX or XLS.

2.4. Data (MS Word)

  • Generates an Office Open XML file (DOCX).
  • Each record is on a separate page as a two-column table – column name and value.

2.5. Data (MS Word 97-2003)

  • Generates a DOC file.
  • The content of the file is the same as for the DOCX format.

2.6. Data (HTML)

  • Generates an HTML file containing the view table data as an HTML table.
  • Values are converted according to the export options – foreign keys, attachments and HTML tags – before being written.
  • Values are written to the file as they are stored, without encoding HTML characters.

2.7. Data (TXT)

  • Generates a text file with the view table data as a table with fixed-width columns separated by the “|” character.
  • Values are converted according to the export options – foreign keys, attachments and HTML tags – before being written.
  • File encoding is UTF-8.

2.8. Data (SQL)

  • Generates a text file with SQL statements.
  • If the database query does not use joins (JOIN), INSERT statements are generated for each data row. Without the “All columns” option, the statements contain the “id” column and the exported columns of the view table; with the “All columns” option, all columns of the database table.
  • For each column with a non-empty database name, a listing is generated in the format “column = (value1, value2...)” – one line per column, values separated by a comma.
  • Supported value data types: String (quoted), Integer, Long, Double, Date.
  • A NULL value is written as the literal “NULL”.
  • File encoding is UTF-8.

2.9. Data (C#)

  • Generates a text file with C# code representing the data as a List<object[]> collection.
  • Each data row is converted to array.Add(new object[] { ... });.
  • Supported value data types: String (escaped quotes), Integer, Long, Double, DateTime.
  • A NULL value is written as DBNull.Value.
  • The resulting file is saved with the “txt” extension.
  • File encoding is UTF-8.

2.10. Attachments (files/images)

  • Exports the physical attachment files from the exported File and Image columns.
  • Files are packed into a ZIP archive.
  • The name of each file in the archive follows the format: ID-{record id}-{column name}-{file name}.
  • Diacritics are automatically removed from file names in the archive.
  • If no column contains attachments or all attachments are missing on disk, the export ends with an error.

2.11. Data (XML)

  • Generates an XML file with a schema.
  • Images inserted into the values of the RichTextBox control are exported together with the text.
  • For File and Image columns, a helper column “{column name} (Path + FileName)” is added, containing the full path to the file on the server and the file name separated by a tab.
  • Column names in the XML are adjusted to be unique.
  • The file contains the record ID only with the “All columns” option.

2.12. Data + Attachments (XML)

  • Available only to users with administrator rights.
  • Works the same as the XML format, but for File and Image columns it adds a helper column “{column name} (Path + FileName + Base64)” containing the file path, file name and Base64 file content, all separated by a tab.
  • If the attachment file cannot be read, the Base64 part is omitted and the column contains only the path and name.
  • The resulting file is saved with the “xml” extension.

2.13. Data (JSON)

  • Generates a JSON file with the view table data as an array of objects.
  • Images inserted into the values of the RichTextBox control are exported together with the text.
  • For File and Image columns, a helper column “{column name} (Path + FileName)” is added, containing the full path to the file on the server and the file name separated by a tab.
  • Column names in the JSON are adjusted to be unique.
  • The file contains the record ID only with the “All columns” option.
  • File encoding is UTF-8.

2.14. Data + Attachments (JSON)

  • Available only to users with administrator rights.
  • Works the same as the JSON format, but for File and Image columns it adds a helper column “{column name} (Path + FileName + Base64)” containing the file path, file name and Base64 file content, all separated by a tab.
  • If the attachment file cannot be read, the Base64 part is omitted and the column contains only the path and name.
  • The resulting file is saved with the “json” extension.

3. Export options

The following options are available in the export dialog:

3.1. All columns

  • If checked, all columns described in chapter 1.2 are exported, not only the columns currently displayed in the view table.
  • For the History control, this option is always enabled and cannot be changed. For the history of a whole form, the columns customized by the user in the history are exported.
  • If the view table has “Deny customization” checked, this option is automatically disabled regardless of the user’s choice.

3.2. Disable evaluation of external functions

  • If checked, the external function “NETGenium.DataTable”, which may modify the loaded data, is not triggered, and values in the form “ngef2(...)” of hidden read-only text fields are not evaluated.

3.3. Remove HTML tags

  • If checked, HTML tags are removed from values before they are written to the output file.
  • Applies to formats: XLSX, XLS, CSV, HTML, TXT.
  • In the XLSX and XLS formats, it additionally translates the values of the ComboBox, ListBox and Radio controls into the language of the user and rounds Currency values to two decimal places.

3.4. Foreign keys as numbers (no text value)

  • If checked, values of foreign key (ForeignKey) columns that have “Show value in the view table without the need to join” checked are exported as numeric identifiers instead of the display text. Other foreign keys are always exported as numeric identifiers.

3.5. Attachments as numbers (no file name)

  • If checked, values of attachment columns (File and Image) are exported as numeric identifiers instead of file names.

3.6. Show sums of columns

  • Available for the XLSX and XLS formats.
  • If checked, a footer row with aggregated values (totals etc.) is added to the exported file for each column whose footer is not hidden. The row is added only if the export contains more than one record.

4. Export of a statistical view table

  • The export icon downloads the data directly to an XLSX file, without the export dialog.
  • The file contains the name of the view table, the time period and the applied filters, followed by the data and always a totals row, even if the view table does not display it.
  • The “Load only first” setting limits only the exported rows; the totals include all rows.
  • The automatic switch to the CSV format does not apply.
  • After the export, the external function “NETGenium.DataGrid2Excel” is triggered with the same parameters as for the XLSX format.
  • The export can be disabled by the “Deny data export” field of the statistical view table. For the statistical LiteDataGrid control, this field is not offered in the control settings.

5. Export of the history of a single record

  • In the change history of a single record, the export icon downloads the data directly to an XLSX file, without the export dialog.
  • The export denial does not apply to this export, the automatic switch to the CSV format does not apply, and the export is not recorded in the export log.

6. Automatic format switch

  • If the XLSX or XLS format is selected in the export dialog and at least one of the following conditions is met:
    • The number of exported columns exceeds the limit defined in the “MaxExcelColumns.txt” configuration file in the “Config” directory.
    • The number of exported rows exceeds the limit defined in the “MaxExcelRows.txt” configuration file in the “Config” directory.
  • The export automatically switches to the CSV format.
  • The switch happens before the file is generated.

7. Export logging

  • Every export from the export dialog and from the “MS Excel Import/Export” dialog is recorded in the “Export-{year}-{month}.log” file in the “Logs” directory.
  • Two records are logged: one after the database query is executed (with the number of rows and the query text), and one after the file is generated (with the number of rows and the file name).
  • The following is recorded: user name, duration of the operation, number of affected rows, query text or name of the generated file.
  • The export of a statistical view table is recorded by a single record.

8. Security

  • The export dialog receives the database query of the view table in encrypted form, bound to the view table from which it was opened. The dialog rejects the query of a different view table.
  • Login and Session Fixation protection are checked on every request.
  • Export can be disabled at the view table level by checking the “Deny data export” field in the DataGrid control settings. The denial is checked on the server as well and does not apply to users with administrator rights.
  • An anonymous user can also export, unless the export is disabled.
  • The XML and JSON formats with Base64 attachment content are available exclusively to users with administrator rights; the rights are checked on the server as well.
  • The “DenyExport.txt” configuration file in the “Config” directory applies only to the export of applications and has no effect on the export from a view table.