Bug 130247 - Excel workbook with multiple worksheets changes sheet reference in form control when importing to LibreOffice Calc.
Summary: Excel workbook with multiple worksheets changes sheet reference in form contr...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: filters and storage (show other bugs)
Version:
(earliest affected)
6.2.8.2 release
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: XLSX-Form-Controls
  Show dependency treegraph
 
Reported: 2020-01-28 23:47 UTC by Willis
Modified: 2020-06-15 12:00 UTC (History)
0 users

See Also:
Crash report or crash signature:


Attachments
sample MS Excel file (34.11 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2020-01-28 23:50 UTC, Willis
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Willis 2020-01-28 23:47:55 UTC
Description:
Form control references created in a multiple worksheet MS Excel workbook are imported incorrectly by Calc. Sheet references are changed in the Input Range and Cell Link fields of the Format Control for a Combo Box.

Steps to Reproduce:
1.  0pen a new workbook in MS Excel
2.  add a second worksheet - it will be automatically named Sheet 2
3.  on Sheet 2, create a table of values to populate a dropdown menu
4.  select Developer tab in the ribbon
5.  select Combo Box
6.  draw a rectangle to create a Combo Box anywhere on Sheet 2
7.  right-click on newly created Combo Box
8.  select Format Control from context menu
9.  click on Input Range field
10. highlight table of values created in step 3
11. click on Cell Link field
12. click on any unused cell in Sheet 2
13. click OK
14. click on any unused cell in Sheet 2
15. save, name and close the Excel workbook
16. open workbook in MS Excel
17. if not already visible, navigate to Sheet 2
18. click on Combo Box
19. select an option from the dropdown menu
20. result - values entered in step 3 are displayed, and selection is shown in cell designated in step 12
21. close the Excel workbook without saving changes
22. open workbook in LibreOffice Calc
23. if not already visible, navigate to Sheet 2
24. click on Combo Box to select an option from the dropdown menu
25. result - no data is displayed in dropdown menu
26. expected result - data from table of values created in step 3 should be displayed
27. right-click on Sheet 1 tab
28. select Delete Sheet option from context menu
29. click Yes when asked to confirm deletion
30. Sheet 2 is now the only active worksheet
31. click on Combo Box
32. select an option from the dropdown menu
33. result - item is selected as expected, and selection is shown in cell selected in step 12
34. close workbook without saving changes
35. open workbook in LibreOffice Calc
36. if not already visible, navigate to Sheet 2
37. display the Form Design Toolbar - View > Toolbars > Form Design
38. activate Design Mode by clicking on edit tool in the Form Design toolbar
39. right-click on Combo Box to display Properties dialog box
40. select Data tab in Properties dialog box
41. view Linked Cell field
42. result - shows Sheet1.[cell designated in step 12]
43. expected result - should show Sheet2.[cell designated in step 12]
44. view Source Cell Range field
45. result - shows Sheet1.[cell range designated in step 3]
46. expected result - should show Sheet2.[cell range designated in step 3]

Actual Results:
see actual results described in steps 25, 42 and 45

Expected Results:
see expected results described in steps 26, 43 and 46


Reproducible: Always


User Profile Reset: Yes



Additional Info:
there are no relevant LibreOffice help references
Comment 1 Willis 2020-01-28 23:50:32 UTC
Created attachment 157486 [details]
sample MS Excel file
Comment 2 Alex Thurgood 2020-01-29 08:40:00 UTC
Confirming.

Opened test file in latest version of MSExcel for Mac (16.33)
Notice how dropdown box functions as explained by OP.

Opened test file in LO:

Version: 7.0.0.0.alpha0+
Build ID: 9a2fc64b49b4a3067ba78648e3fcc73390717c80
CPU threads: 4; OS: Mac OS X 10.15.2; UI render: default; VCL: osx; 
Locale: fr-FR (fr_FR.UTF-8); UI-Language: en-US
Calc: threaded

Observe that dropdown box fails to either display or select any of the values from the previously configured range. Cell D3 does not update automatically when any selection made in dropdown.

The dropdown menu displays a list of blank entries that can be selected with the mouse, but the values are simply not there.
Comment 3 Alex Thurgood 2020-01-29 08:49:06 UTC
Also confirming that, with the file loaded in Calc, and Form Design mode activated, the control in Sheet 2 points to cell references and ranges in Sheet1:

Linked cell : Sheet1.D3
Source cell range : Sheet1.B11:B16


I also notice a further difference, FWIW :
- the value displayed in the linked cell appears to be different between Excel and Calc. Excel displays the index number of the entry selected (so entry "Selected" = 1, entry 1=2, entry 2=3, etc) , whereas Calc displays the actual selected value from the list.
Comment 4 eisa01 2020-02-15 23:35:33 UTC
Also a bug on Windows

Form control not supported in LO 3.3, so not inherited

Version: 6.4.0.3 (x64)
Build ID: b0a288ab3d2d4774cb44b62f04d5d28733ac6df8
CPU threads: 2; OS: Windows 10.0 Build 18363; UI render: default; VCL: win; 
Locale: en-US (en_US); UI-Language: en-US
Calc: threaded
Comment 5 Willis 2020-02-17 21:59:59 UTC
(In reply to Alex Thurgood from comment #3)
> Also confirming that, with the file loaded in Calc, and Form Design mode
> activated, the control in Sheet 2 points to cell references and ranges in
> Sheet1:
> 
> Linked cell : Sheet1.D3
> Source cell range : Sheet1.B11:B16
> 
> 
> I also notice a further difference, FWIW :
> - the value displayed in the linked cell appears to be different between
> Excel and Calc. Excel displays the index number of the entry selected (so
> entry "Selected" = 1, entry 1=2, entry 2=3, etc) , whereas Calc displays the
> actual selected value from the list.

Responding to Alex's comment about different values being displayed in the linked cell (Sheet2, D3 in the sample file). LibreOffice Calc includes a feature that allows the linked cell to display either the selected entry, or the numerical position of the selected entry. The default appears to display the selected entry. 

MS Excel does not include an equivalent feature to choose what to display in the linked cell - it displays the position of the selected entry in the linked cell. To make the linked cell in LibreOffice Calc behave like MS Excel, do the following:

1.  open test file in LibreOffice Calc
2.  if not already visible, navigate to Sheet 2
3.  display the Form Design Toolbar - View > Toolbars > Form Design
4.  activate Design Mode by clicking on edit tool in the Form Design toolbar
5.  right-click on Combo Box to display Properties dialog box
6.  select Data tab in Properties dialog box
7.  next to the second line 'Contents of the linked cell...' click dropdown menu to reveal a choice between 'The selected entry' OR 'The position of the selected entry'
8.  from the dropdown menu, select 'The position of the selected entry'
9.  deselect the edit tool in the Form Design toolbar

It would seem that compatibility when opening a document created in MS Excel would be best served by LibreOffice Calc defaulting to displaying the position of the selected entry in the linked cell. 

This topic seems to be a separate issue from my original bug submission. In fact, what is displayed in a linked cell for a Combo Box might not be a bug, but a feature request. I am a newbie - the original post is my first ever bug submission. Any advice about how best to submit this second issue would be most appreciated.