• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:June 17, 2020
  • Reading time:6 mins read
You are currently viewing Auto Fit Contents in Excel output with RTF template in BIP
Auto Fit Contents in Excel output with RTF template in BIP

In this article we will look into how to set the auto-width for a column in Excel output using RTF template in BI Publisher.

We create RTF templates for most of the reports and the output can be retrieved in PDF, Excel, HTML, etc.

Most of the end users are well versed with excel and they want the data in excel format to apply filters and manipulate data as per their convenience.

Hence getting data in excel format with formatting correctly is of importance in BI Report.

In one of our previous article, we have looked into how to prevent merging of columns in Excel output using RTF template?

Apart from Merging of columns, the end user will also be interested in if the columns text is clearly visible to him. Most of the columns will get smaller width if we download the excel output directly from RTF template.

There is a setting that we can use to set the auto-width for a column based on the data in the column. With this, we are not setting the fixed width for a column but based on the data dynamically. So this approach will give better experience to the end users to view data in excel.

Navigate to BI Report
Click on Edit
And then click on Properties

bi properties - Auto Fit Contents in Excel output with RTF template in BIP
BI Report Properties

Then go to Formatting tab and scroll down to EXCEL 2007 Output section

auto layout - Auto Fit Contents in Excel output with RTF template in BIP
Table Auto Layout setting for excel output

We are going to use the Table Auto Layout feature to achieve our requirement.

Table Auto Layout Setting explanation:

Valid Values: Specify a conversion ratio in points and a maximum length in points, for example 6.5,150

Note: For this property to take effect, the property “Keep values in same column” must be set to True which will set the column value into a single cell instead of merging columns.

This property expands the table column width to fit the contents. The column width is expanded based on the character count and conversion ratio up to the maximum specification.

Example: Assume a report with two columns of Excel data — Column 1 contains a text string that is 18 characters and Column 2 is 30 characters long. When the value of this property is set to 6.5,150, the following calculations are performed:

Column 1 is 18 characters:
Apply the calculation: 18 * 6.5pts = 117 pts
The column in the Excel output will be 117 pts wide.

Column 2 is 30 characters:
Apply the calculation: 30 * 6.5 pts = 195 pts
Because 195 pts is greater than the specified maximum of 150, Column 2 will be 150 pts wide in the Excel output. And the data will appear in two lines

auto layout filled - Auto Fit Contents in Excel output with RTF template in BIP
Auto Layout setting filled in

Once we set this Table Auto Layout, the columns will get expanded based upon the field lengths dynamically upto the extent listed in the setting.

We can also set the “Minimum column width” and “Minimum row height” in the settings if there is a requirement to increase the widths.

Minimum column width: When the column width is less than the specified minimum and it contains no data, the column is merged with the preceding column. The value must be set in points. The valid range for this property is 0.5 to 20 points. Default value: 3 (in points, 0.04 inch)

Minimum row height: When the row height is less than the specified minimum and it contains no data, the row is removed. The value must be set in points. The valid range for this property is .001 to 5 points. Default Value: 1 (in points, 0.01 inch)

Hope these settings help when you have similar requirements. Even though these are simple settings, they make wonders to the user experience.

Tip: Table Auto Layout setting will not work individually. It works in conjunction with “Keep Values in Same Column” value of True. Use 6.5,150 without worrying about any logic.