OBIEE 11g: Issue with Excel 2007 Export - Merging Columns,Adding Extra Columns,Extra Blank Spaces, Cell Size Changes When Exporting to Excel 2007+

Scenario:

When you try to export a report in Excel 2007 or Excel 2007+ format, the report data and layout is not properly exported. We see difference when you compare the exported report with the report on the dashboard. Some of the formatting issues you will find are

1) Merged Columns
2) Cell format like large cell size
3) Some extra blank spaces
4) Extra columns which are not showing on the dashboard.

Root Cause:

This is a bug in 11.1.1.7 and later versions.

Solution:

We can fix this issue by making a small change to the config file xdo.cfg. Please follow the below steps to know how to make the changes.
 

1. Take a back up copy of <Middleware_Home>/instances/instance1/config/OracleBIJavaHostComponent/coreapplication_obijh1/xdo.cfg
   
2. Open xdo.cfg for editing.
   
3. Add the following: 
   
   <config version="1.0.0" xmlns="
     http://xmlns.oracle.com/oxp/config/">
         <properties>
          <property name="xlsx-keep-values-in-same-column">true</property>
         </properties>
     </config>
4.  Save your changes.
   
5. Restart all OBIEE Services (OPMN Services).