Clarity: Unable to open Excel file after Export to Excel (Data Only) when the number of records greater than 3000 rows in Weekly Detail portlet

Document ID : KB000049987
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

After upgrading to CA Clarity 12.1 from 12.0, in the Portlet "Weekly Details" portlet, when exporting data to excel in Data Only mode for 8500 records, the file size grows to become between 24-38 mb (file size does vary when the export is done). Prior to the upgrade the export file size was less than 10 mb. Please note this issue cannot be reproduced when the number of records is reduced within hundreds of rows. The issue can be reproduced with different combinations of TSV Columns configured and rows present in the portlet.

Steps to Reproduce:

  1. Login to Clarity

  2. Home > Resource Planning > Allocation Tab

  3. Weekly Details Portlet > [--Actions--] > Export to Excel (Data Only) option

  4. Open the Excel file directly or save the file to a location and then open file.

    You get this prompt:

    'The file you are trying to open is in a different format then specified by the file extension.
    Verify that the file is not corrupted and is from a trusted source before opening the file.
    Do you want to open the file now?'

    If you click 'Yes' to open, then you are presented with the following message:

    Error: Problems came up in the following areas during load: Unknown

Solution:

Workaround:
This issue is caused by a Microsoft Excel limitation for the number of hyperlinks in a file. If the portlet configuration has columns with hyperlinks and the number of hyperlinks exceeds 65530, the file will not open as reported.

The workaround is to change the portlet configuration to remove hyperlinks or to reduce the number of rows to be exported.

Status/Resolution:
Resolved in Clarity 12.0.6 Generic Patch. Reference TEC522707
Resolved in Clarity 12.1.1 Generic Patch. Reference TEC553491
Resolved in Clarity 13.1.0

Resolution Details: The Export to Excel action will generate a message in the Excel Log as follows:

Hyperlinked cells with count more than 65530 would not be displayed as linkable in the Excel spreadsheet.
This is because of an undocumented Excel limitation for Href links.

Keywords: CLARITYKB, CLRT-65938, clarity12resolved, clarity13resolved, clarity1310resolved.