Excel Output For Large Number Of Columns With Formatting

 

Sometimes we get a reporting requirement where we have for example 30 plus excel columns in output ,columns need to be formatted and more that 65536 records in output.

Below are the 3 options with limitations that come to our mind to achieve the above solution:

1). RTF Template - The limit of MS Word page width is 22 inches, so it cannot   

                             accommodate 30 plus columns perfectly.

2). Online Template from BI publisher screen - It has limited formatting options.

3). Excel Template - This would have been the obvious choice if ".xls" would not have limit

                               of 65536 rows.

The Solution: XSL-FO Stylesheet

• To overcome the above limitations we can go with another approach that is Exporting RTF to "XSL-FO Stylesheet" template.
• We do not need to create the XSL template from scratch, rather we create a RTF template and we export it to the XSL.
• When we create template in RTF with 30 plus columns, the column width will be very less in RTF but after exporting to XSL we can increase the page and column width. We need very less knowledge of XSL to edit the exported  XSL from RTF.
• With this we get output in Excel(.xlsx with 1048576 rows) format and desired look and feel. 
• The columns won't be shrinking unlike RFT output to Excel for these many columns.

Below are the screenshots to achieve the same.

The exported code would look like below. Edit the code as per requirement and save it with .xsl extension.

Once tested locally, upload the template to BI publisher with type as "XSL Stylesheet(FO) and select the default output as Excel.