Export to Excel

You can export reports to Excel from the designer or from the browser by three export methods: Unaltered Export, Pagination Export, and Pagination Export by Sheet.

This document introduces the differences between these three export methods.

Note:

1. Export to Excel is not supported on mobile terminals.

2: During the export to Excel:

Only display values in the template content can be exported, not actual values;

Only actual values in the widget content can be exported, not display values.

The following uses the report Repeat Title.cpt as an example to display the results of each export method.

Pagination Export

When you use Pagination Export, if the report template is paginated, the report is exported according to the pagination layout, with the header row repeated on each page.

Unaltered Export

If you use Unaltered Export, even if the report template is paginated, the report is exported as a continuous worksheet without page breaks. The header row is displayed only once.

Note:

Unaltered Export preserves the cell settings applied during template preview and query. However, the export speed is comparatively slow. Large Dataset Export is faster, but it does not preserve cell styles. You can choose the export method as needed.

Pagination Export by Sheet

When you use Pagination Export by Sheet, each page of the report result is saved as a separate sheet in the Excel file.

Note:

1. When the row-based engine is enabled, Pagination Export by Sheet to Excel is not supported, and Unaltered Export will generate a compressed package.

2. Sheet names in Excel should not be too long.

3. In Excel files exported using the Pagination Export by Sheet method, sheets are named according to page numbers. For example, the first page corresponds to the sheet name page1 in the exported Excel file, and the second page corresponds to the sheet name page2.

Users can customize the report toolbar as needed and add buttons for the desired export methods. For detailed steps, see Pagination Preview.

Mismatch Between Exported Report and Preview

Problem description

After a report is exported to Excel, the header position in the Excel print preview does not match that shown in the template preview.

Cause analysis

The top and bottom page margins set in the template are inconsistent with those in Excel.

Solution

Choose Template > Page Setting > Page and adjust the top and bottom margins to 12.7 mm to improve the compatibility between FineReport and Excel page layouts.

Digits Beyond the 15th Digit Displayed as Zero in Exported Data

Problem description

1. When cell data exceeds 15 digits, only the first 15 digits are displayed correctly in the exported Excel file, and digits beyond the 15th digit are displayed as 0, as shown in the following figure.

2. The cell's original value is 13096544898.674679. After applying the #,##0.00 format and exporting the report to Excel, the value displayed after double-clicking cell B2 in Excel changes to 13096544898.6747 — the value is automatically rounded by Excel, as shown in the following figure.

Cause:

1. This issue is caused by Excel's own logic, which can only recognize the first 15 digits; digits beyond the 15th are displayed as 0.

2. For numbers exceeding 15 digits, Excel rounds the value due to its precision limitation, causing digits beyond the supported precision to change. For example, xxxx.abcdefgh may be rounded to xxx.abcd01.

Solution:

1. Right-click the cell and click Edit.

2. Click the Advanced tab, and set the value of Custom Display to the formula CONCATENATE($$$,"").

Note:

The Advanced tab appears only when the cell is a data column.

Reduced Column Width After Unaltered Export

Problem description

After previewing the template and selecting Unaltered Export, the column width in the exported Excel file changes from 7 px to 6 px.

Cause analysis

FineReport and Excel use different row height and column width calculation methods. During export, the values are converted using a conversion factor.

Solution

1. Adjustment coefficient description

The default width adjustment coefficient is 34.742, and the default height adjustment coefficient is 20.1. These coefficients are based on the following conditions:

• A4 paper

• Page margins (top 6.85 mm, left 19.05 mm, bottom 6.85 mm, right 19.05 mm)

• SimSun (simsun)

If the template does not use these default settings, and you want to export it to fit a specific Excel paper size while keeping the export result consistent with the preview, you need to adjust this coefficient.

2. Method

Add or modify the following two fields to the fine_conf_entity table in FineDB.

Note:

For details, see FINE_CONF_ENTITY Visualization Configuration

• CustomConfig.excelAdjustHeight

• CustomConfig.excelAdjustWidth

If the row height or column width in the exported Excel file is insufficient, increase the corresponding coefficient; if it is excessive, decrease the corresponding coefficient.

Reduced Font Size in Exported Excel Files

Cause analysis

When cell content in the designer is set to wrap text and the font size is larger than 14, the font size in the report will be multiplied by 0.95 when exporting to Excel.

Solution

Adjust the font size to below 14 or modify the text control settings.