After an evaluation, GNOME has moved from Bugzilla to GitLab. Learn more about GitLab.
No new issues can be reported in GNOME Bugzilla anymore.
To report an issue in a GNOME project, go to GNOME GitLab.
Do not go to GNOME Gitlab for: Bluefish, Doxygen, GnuCash, GStreamer, java-gnome, LDTP, NetworkManager, Tomboy.
Bug 557555 - [XLS] Data Validation List not exported correctly
[XLS] Data Validation List not exported correctly
Product: Gnumeric
Classification: Applications
Component: import/export MS Excel (tm)
git master
Other All
: Low normal
: ---
Assigned To: Jody Goldberg
Jody Goldberg
Depends on:
Reported: 2008-10-23 06:54 UTC by Steven Lai
Modified: 2015-12-22 20:46 UTC
See Also:
GNOME target: ---
GNOME version: ---

Testcase (1.92 KB, application/x-gnumeric)
2015-12-22 00:43 UTC, Morten Welinder

Description Steven Lai 2008-10-23 06:54:56 UTC
1. Set B2=1, B3=2, B4=3
2. Go to some other cells (say E6)
3. In main menu, Data => Validate ...
4. Set Criteria: Allow = In a list, Source = $B$2:$B$4, click In-cell dropdown, Ok
5. A list popup is displayed when E6 is clicked
6. Now, export the spreadsheet to xls

When the spreadshset is opened in Excel 2003, the list popup button appears as expected when you click on E6. But the list doesn't show if you click on the list popup button. 

as a sidenote: opening the Data Validation Dialog in Excel and press Ok would make the list box work again.
Comment 1 Peter Newman 2011-02-13 02:44:54 UTC
For what it's worth, this is still broken in 1.8.2 and 1.10.1. The latter both via ssconvert and a direct save as with GNumeric. Exporting with ssconvert as both Gnumeric_Excel:excel_dsf and Gnumeric_Excel:excel_biff8.

The import aspect, via ssconvert, to Gnumeric works fine, and the validation settings are visible in the XML.

When doing the export via the GUI, the following gets output to the log:

** (gnumeric:6789): CRITICAL **: dependent_add_dynamic_dep: assertion `dep != NULL' failed

** (gnumeric:6789): WARNING **: Not exporting object ? of type GnmValidationCombo

Also as mentioned above, opening the dialog fixes it. Before the dialog has been opened, an empty tooltip is visible in Excel, as well as the drop down. Setting gnm:InputMessage manually in the XML fills in the tooltip, but doesn't solve the issue.

Let me know if there is anything else I can do to help diagnose this further.
Comment 2 Andreas J. Guelzow 2011-02-13 19:29:14 UTC
This is simply not implemented and there is currently no developer working on xls export (considering that that is a format even abandoned even by MS).
Comment 3 Morten Welinder 2011-02-16 14:49:17 UTC
The "CRITICAL" message is interesting, but I am unable to reproduce that
one (with current code, roughly 1.10.13)
Comment 4 Peter Newman 2011-02-20 22:09:27 UTC
(In reply to comment #2)
> This is simply not implemented and there is currently no developer working on
> xls export (considering that that is a format even abandoned even by MS).

Andreas, the partial success and the source code (line 1170 in plugins/excel/ms-excel-write.c), seem to disagree. Given just opening and closing the validation dialog is sufficient to fix it, it seems like a bug rather than lack of implementation (unless it's just the list validation that's not implemented). Do you know if the xlsx/2007 based export includes working list validation instead?

(In reply to comment #3)
> The "CRITICAL" message is interesting, but I am unable to reproduce that
> one (with current code, roughly 1.10.13)

Interesting Morten. Do you know if it works in 1.10.13 then? I'm happy to test if it helps. I can also send broken or working files if it helps (although the file grows significantly between the two, so I don't know if that helps or not).
Comment 5 Andreas J. Guelzow 2011-02-21 03:34:49 UTC
I think I looked at "** (gnumeric:6789): WARNING **: Not exporting object ? of type GnmValidationCombo" and there is no code tryig to export that object. Of course there isn't supposed to be any such code since the GnmValidationCombo are simply supposed to be ignored. (They are a consequence of the validation.)

Since 2007, the xlsx appears to have code exporting these validations. Of course I have no idea whether that works.
Comment 6 Morten Welinder 2015-12-22 00:43:17 UTC
Created attachment 317765 [details]

Testcase as described.

This might have been fixed in the meantime.  At least I observe that
converting to xls and importing into Gnumeric works.  I'll have to
check what Excel thinks of the situation, but I cannot do that from
Comment 7 Morten Welinder 2015-12-22 13:16:42 UTC
Ok, so the situation is that on load, Excel does see the validation, but the
drop-down is not populated.  I would guess we're missing some cache record.
Comment 8 Morten Welinder 2015-12-22 15:44:45 UTC
We are missing a combo object that Excel seems to insist on for this
situation.  Gnumeric and LO don't care.  The WriteExcel perl module
also doesn't write that object.
Comment 9 Morten Welinder 2015-12-22 20:46:38 UTC
This problem has been fixed in our software repository. The fix will go into the next software release. Once that release is available, you may want to check for a software upgrade provided by your Linux distribution.