Author: Chris Breaux
Updated: June 2024
Audience: Everyone
Environment: Replicated - KOTS, Jama Connect® v9.8, v9.9
Update: This issue was resolved with the release of Jama Connect® v9.10
Summary
A change was introduced in v9.8 that caused an issue with some 'Export to Excel' reports. This can occur when picklists with associated colors are included in the report. Some symptoms of this include:
(a) A significant increase in file size from previous versions (exporting the same items)
e.g., 5MB to 25MB
(b) Stalling/Hanging when attempting to open in Excel.
Solution
- Opening the file in another program (LibreOffice, Apple Numbers)
- Excluding the field with the picklists from the report
- Manual db update to null out the colors from the picklists
(Note: We'll want to back up the values before nulling them out so we can restore them once the defect is addressed. Also, it is best to null out all the options to be safe)
UPDATE lookup SET color = NULL WHERE color is not null;
Cause
"The cause is excessive creation of FormatConditionCollection and FormatCondition objects then applied to each cell. This is in ExcelValidationAndFormatting.addConditionalFormatForColor(). These format objects need to be created once (once per picklist+value?) and reused. Instead, we’re forever adding to the ConditionalFormattingCollection instance as we process rows/cells until we reach many objects in this collection (I saw over 50,000). When this file is opened, Excel tries to execute a gigantic number of rules on every cell, even though nearly all the rules are duplicates. If you have 30 values in a picklist, I’d expect there to only be 30 such conditions in one collection, and that applied across all cells in the column."
Prevention
The best way to prevent this is to avoid including picklists with colors in your exports. However, this is only feasible for some clients as this may encompass critical/required fields. Manual database intervention will be their best bet, as you can't make the necessary changes in the UI. (maybe you can in the API? not tested)
INTERNAL
Agents and Admins refer to #75904 and 75956
Please feel free to leave feedback in the comments below.
Related to
Comments
0 comments
Please sign in to leave a comment.