GNOME Bugzilla – Bug 557555
[XLS] Data Validation List not exported correctly
Last modified: 2015-12-22 20:46:38 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.
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.
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).
The "CRITICAL" message is interesting, but I am unable to reproduce that one (with current code, roughly 1.10.13)
(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).
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.
Created attachment 317765 [details] Testcase 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 here.
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.
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.
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.