Considerations when exporting to formats such as Excel or CSV

To avoid unexpected behavior when exporting to formats such as Excel or CSV, the following important aspects need to be taken into account During report design:

Not all document formats are created equal

You can use many different elements to make the design. The data They can be presented in a clear tabular format, but usually carry formatted headings or titles and possibly graphic elements, tables, or Underreporting. A document with enough controls can be represented perfectly for a document-centric export format as a PDF. Some formats do a good job with document presentation, but they may have problems when exporting documents that need to be edited such as DOCX and RTF.

There is a group of formats with limited display that are used for the work interactive with integrated data. This is the case of XLS(X) and CSV. We will talk about these in this chapter.

Consider exporting the data separately

Consider implementing data export features in your applications by separate. The WYSIWYG reporting system aims to create the data exports for users who want to run their data analysis in a spreadsheet. It is essential that the structure of data export is as clean as possible, the visual elements of any type often offer no added value for this purpose.

Export data cleanly

Several export formats do not support intersecting elements. The Engine Export generates a tabular structure where rows and columns are used to place output items as required by report design Visual. Especially for Excel and CSV formats, this technique of Implementation can result in more columns or rows or even cells Combined, which makes it difficult to work with documents for Data analysis. It shows several scenarios of the problem, finding possible solutions or strategies for designing your reports to keep them compatible with data-centric exports.

Misaligned columns

The following image shows a report layout with a table. Maybe hard to see, but the columns in the header don’t line up perfectly with those of the rest of the table.

Here’s the result of exporting to XLSX:

You can see that only columns A, C, E, and G are fully visible, while that columns B, D, F, and H are almost unvisible. This happens because the columns in the report are misaligned, therefore the columns in the table header do not align correctly with those in the detail. The problem becomes more visible when enlarging the Excel file and Add some colors:

You can fix this problem by accurately aligning the header and the Table of the detail band of each cell, using vertical indicators in the editor.

This image shows the alignment guides in red.

Blank spaces

This report shows some graphical elements that are separated by space between them:

When exporting to XLSX, this is the result:

Additional rows and columns are generated because of the spaces in the the initial report. To determine the origin of such spaces it can be useful to determine the origin of such spaces Set item background colors in the report designer before export.

In some cases, it is recommended to disable the CanGrow option for the tables to prevent the cells from growing in size when exporting and an overflow in the number of rows in the XLS(X) document occurs.

The colors are displayed in Excel, making the cell boundaries visible:

As a solution, it is recommended to adjust the positions of each element so that they do not No empty space remains. You can use the TextAlignment and Padding to align them.

Header and footer

In reports, header and footer bands can also cause problems in the rows or columns of the export file, if your controls they do not align with those of other bands. The following is a report design that Displays this problem:

The resulting XLSX file looks like this:

Using background colors helps to understand exactly what causes the problem. This image shows red lines to illustrate the controls that influenced the creation of columns.

The recommendation is to align controls in headers and footers with those in the data bands of the report. In some cases it is advisable to remove the header or footer elements in case a better tabulation of the data and no additional columns appear.

Calculated and custom-formatted values

Exports to Excel and CSV will not work in the following cases:

• When the value of a cell comes from a calculated value.
• When a value in a cell is applied a formatting value derived from a personalized format.
• When the event is used BeforePrint to assign custom values to The property Text of labels or table cells.

This is because Excel and CSV are the only formats currently supported that distinguish between string values and other types of data (such as numbers, decimals etc.) Therefore, it is not possible to perform calculations involving this type of fields.

When exporting these types of fields is required, it is recommended to use the EvaluateBinding instead of BeforePrint. In this case, assign your field calculated to the variable e.Value and change the value of the XlsxExportOptions.TextExportMode to Text. The final data will always be a value text string, therefore, in the resulting Excel file you will not be able to perform calculations or operations, such as additions, etc.

CSV specifics

For CSV exports, there are two additional options that can help Avoid empty cells in the resulting file. These options are SkipEmptyColumns and SkipEmptyRows which are enabled in a default. The following is an example report layout to demonstrate The effect of these options:

When exported to CSV and uploaded to Excel with the default settings, Here’s the result:

And here’s what you get if you disable both properties:

Another option that can be useful for CSV exports is the static FollowReportLayout, which is enabled by default.

A report exported with those default settings will look like this:

Disabling that property will not observe certain design details in the resulting file. This means that the space can be eliminated An Export Void.

These additional options for CSV format exports are important since this format requires clean structures of data export so that they can be used in Automated data transfer.