Index

Exporting to Excel File

To export the report to an Excel File, on the File menu, point to Export, and then click To Excel File. The Export to XLS dialog appears.

Specify the directory and file name, and then click OK. JReport will use the report name as the default XLS file name if not specified. A file with the extension ".xls" is generated.

Wordwrap
Keep Existing - Keep all the settings of each object's WordWrap property specified in the report.
Disable All - Disable the WordWrap property for all objects, the Wordwrap property will be made false for all the objects.
Enable All - Enable the WordWrap property for all objects, the Wordwrap property will be made true for all the objects.

Match Layout
Specifies whether the exported Excel layout should match the original report layout. Normally when a report is exported to Excel, some of the report elements could probably be severely re-positioned or misplaced. This depends on your actual report design. You can check this option to make sure the exported excel has the same layout as the original file. Please check out the notes using this option.

Has Shapes
Specifies whether to include shapes in the exported xls file.

Excel2000
This box is unchecked by default (suggested).

Notes: JReport Designer supports the following features when exporting reports to Excel format,

• Multiple colors in the report. Since Excel only can support 56 kinds of colors, so if the report has more than 56 colors, some colors will be merged.
• Multiple language of the report.
• Large report. When exporting a large report to Excel format, users can customize the number of the rows in one sheet of Excel and the size of the buffer.
• The report with chart. However, when exporting multiple pie charts to Excel, only the first pie chart will be exported.
• When the Columned property is set to be true, the image that is inserted into the text object will not be displayed in the exported Excel document.

Notes for Match Layout

• Stock chart and gauge chart don't support matching layout feature.
• The arc shape is not supported.
• Some shapes could distort when exporting to Excel. For example, a square might appear as a rectangle, and a circle might be changed to an ellipse. All shapes can be repositioned in Excel.
• For a report that contains crosstab, it's NOT recommended that you place an object in the crosstab area, since this may increase the chance of losing record data. JReport benefits from using layers where the objects can be overlapped without causing data loss problems in a JReport report. However, when in Excel, only one of the overlapped objects will be showed correctly, others might be cut off.
• Because of the differences between JReport functions and Excel formulas, some string functions that work well in a CLS file may not work well in a Excel report. For example, the MAX function in JReport will return the longest string when it's used for a string type field; however, this will lead to a meaningless value (0) when in Excel.
• The HorizontalGap and VerticalGap properties for a crosstab should be set to a value as small as possible, such as 0.01 inch, otherwise in Excel, some empty rows and columns will be used to represent these gaps.
• If the total number of records that a summary accumulates exceeds 30, in the exported excel report, the formula will not be retained, say, only the final result will be shown.

Set the Excel buffer size

You can store the report result in Excel buffer sheets when exporting to Excel format. To do this,

1. Select the Report node in Report Inspector.
2. In the Properties sheet, set the ExcelBufferSize property.
   
   The default size is 1, which indicates 1 sheet of the report result is allocated to the result buffer; other sheets will be stored on disk. If you have enough memory, to get better performance, you can increase the Excel buffer size to store more sheets in memory of the Excel format report result.

Set the maximal number of rows for every worksheet

You can control the maximal number of rows in every worksheet in the exported Excel report. To do this,

1. Select the Report node in Report Inspector.
2. In the Properties sheet, set the RowsPerSheet property.
   
   The default value is -1, which means a maximal number of 65000 rows is allowed in a single worksheet. The value range is 0 ~ 65000, values out of this range will be considered as 65000.

Set the Excel column width

You can set the width of each column for the to-be-exported report in JReport Designer. To do this,

1. Select the Report node in Report Inspector.
2. In the Properties sheet, set the ColumnWidthList property.
   
   Type the width for the columns in turn, using semicolon (;) to separate each value. If you don't want to specify the width for a certain column, omit the value, JReport will use the default value, which is 8, for that column.
   
   This property takes effect only when the Columned property is set to True.
   
   For example, the value list 12;15;;20 makes the first column width 12, the second 15, the third 8, which is the default value, and the fourth 20.

Note: The ColumnWidthList property for the subreport is disabled when exporting a report that contains a subreport to Excel.

Realign objects for better appearance in Excel

Normally, when a report is exported to an Excel file, the positions and sizes of the objects in the original report are automatically rearranged by JReport Designer. However, you can arrange most of objects manually in JReport Designer, specially for exporting to Excel, so that they can be well-aligned in the exported excel file.

Before you can resize or reposition an object, you should set the Columned property to True. Select the report node in Report Inspector, scroll the Properties sheet to find the Columned property, and then set its value to be true. This makes the object aligned according to the cells in Excel.

To relocate an object for exporting to Excel,

1. Select the object in Report Inspector.
2. In the Properties sheet, set the ColumnIndex and RowIndex properties.
   
   The ColumnIndex and RowIndex properties determine the object's location in the exported XLS file.
   
   

   Property	Data Type	Description
   ColumnIndex	Integer	Specifies the X coordinate of the selected object in the exported XLS file, measured in cells.
   RowIndex	Integer	Specifies the Y coordinate of the selected object in the exported XLS file, measured in cells.

To resize an object for exporting to Excel,

1. Select the object in Report Inspector.
2. In the Properties sheet, set the ColumnNumber and RowNumber properties.
   
   The ColumnNumber and RowNumber properties determine the objects size in the exported XLS file.
   
   

   Property	Data Type	Description
   ColumnNumber	Integer	Specifies the width of the selected object in the exported XLS file, measured in cells.
   RowNumber	Integer	Specifies the height of the selected object in the exported XLS file, measured in cells.

   
   Only a few objects, such as images, charts, and user defined objects, have these two properties.

The alignment for the shape objects is different from that for normal objects. To realign a shape object for exporting to Excel,

1. Select the shape object in Report Inspector.
2. In the Properties sheet, set the properties: TopAttachCol, TopAttachRow, BottomAttachCol, BottomAttachRow.
   
   These four properties together determine the coordinates and size of the shape object.
   
   

   Property	Data Type	Description
   TopAttachCol	Integer	Specifies the X coordinate of the upper-left corner of the shape in the exported XLS file, measured in cells.
   TopAttachRow	Integer	Specifies the Y coordinate of the upper-left corner of the shape in the exported XLS file, measured in cells.
   BottomAttachCol	Integer	Specifies the X coordinate of the lower-right corner of the shape in the exported XLS file, measured in cells.
   BottomAttachRow	Integer	Specifies the Y coordinate of the lower-right corner of the shape in the exported XLS file, measured in cells.

   
   Actually, the first pair of properties (TopAttachCol, TopAttachRow) locate a cell and use its upper-left corner as the upper-left corner of the shape, while the second pair (BottomAttachCol, BottomAttachRow) locate another cell and use its upper-left corner as the lower-right corner of the shape.
   
   For example, if the properties for an oval is set as:
   
   TopAttachCol: 1
   TopAttachRow: 2
   BottomAttachCol: 3
   BottomAttachRow: 4
   
   Then, the upper-left cell will be A2 and the lower-right cell will be C4. The exported shape will be like below:
   
   
   
   Note: The value you set for the TopAttachCol (TopAttachRow) property should be always larger than that for the BottomAttachCol (BottomAttachRow) property.

Improving the performance when exporting Excel reports

Normally, when a report is being exported to the Excel file, the coordinates (column index and row index) of the report objects will be calculated by JReport engine, which may take a great deal of processing time. You can have these report objects arranged at design time so that the extra position calculating time can be saved. To do this,

1. Select the Report node in Report Inspector.
2. In the Properties sheet, set the Columned property to true.
   
   This property improves the performance when exporting the report to CSV format or Excel format if its value is true, in this case, you can define the position of the object in the CSV format file or Excel format by setting the values of ColumnIndex and RowIndex.

Example: Exporting a report to Excel file

1. Open the report to be exported. Here we select <install_root>\demo\reports\Bar_Line.cls. The report with a bar_line chart will be shown like this,
   
   
2. On the File menu, point to Export, and then click To Excel File. Finish the Export to XLS dialog. Here, we specify the directory as C:\.
3. Go to C:\ to open the Bar_Line.xls file, the result will be shown like this,
   
   

In addition, JReport Designer enables you to export only the objects that you need to the Excel file. To do this, before exporting, select all the unwanted objects and set their ExportToXLS property to false. After saving the changes, export the report to an Excel file. On the Export to XLS dialog, make sure Excel2000 is checked. You will get the result you want.

Note:

If you select Excel2000 check box and set the ExportToXLS property of a page section (Report Header Section, Page Header Section, Detail Section, and so on) to False, all the objects in this section will not be exported, regardless of their ExportToXLS properties.

Related Topics

Exporting to JReport Result
Exporting to Text
Exporting to HTML
Exporting to PDF
Exporting to Postscript File
Exporting to Mail
Exporting to Applet
Exporting to RTF
Exporting to XML

Index