Download it now!
Bug 60718 - FILEOPEN: protected workbook
Summary: FILEOPEN: protected workbook
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.0.0.3 release
Hardware: Other Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: BSA
Keywords:
Depends on:
Blocks:
 
Reported: 2013-02-12 10:00 UTC by CORRADO VIAZZI
Modified: 2017-05-18 10:03 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
example file (134.97 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2013-05-16 11:51 UTC, CORRADO VIAZZI
Details
original file (134.98 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2013-05-17 17:29 UTC, CORRADO VIAZZI
Details
protection removed with excel 2010 (138.45 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2013-05-17 17:31 UTC, CORRADO VIAZZI
Details
original with protection removed by LO (115.87 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2013-05-17 17:59 UTC, CORRADO VIAZZI
Details
original with protection removed by LO, then reopen with Excel and saved (105.19 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2013-05-17 18:00 UTC, CORRADO VIAZZI
Details

Note You need to log in before you can comment on or make changes to this bug.
Description CORRADO VIAZZI 2013-02-12 10:00:36 UTC
Problem description: 
I have a xlsx file (without macro).
The folder named "_RiservatoAxa_" is protected (without password).


Steps to reproduce:
1. just open the file with Calc

Current behavior:
Funtion CERCA.VERT (translated: FIND.VERT) change 
from the original:
=CERCA.VERT("CodicePerito";_RiservatoAxa_!A1:B210;2;FALSO)
to:
=CERCA.VERT("CodicePerito";$_RiservatoAxa_.A1:B210;2;0)

Consequently the function value is always "0"


Expected behavior:
Function "text" should not change, so the value returned will be the right one pointed in _RiservatoAxa_ folder

Note: if I remove protection from _RiservatoAxa_ folder everything works, but it's a workaround
              
Operating System: Windows 7
Version: 4.0.0.3 release
Comment 1 A (Andy) 2013-03-29 22:31:09 UTC
There is no sample file attached?
Comment 2 CORRADO VIAZZI 2013-05-16 11:51:59 UTC
Created attachment 79401 [details]
example file

See the attachment.
Error is in every sheet/cell which contain the "find.vert" function.

For example You can find it in:
sheet: Acqua condotta sintesi
cell: B5

Maybe that the problem is connected with the protection of the sheet _RiervatoAxa_.


Thanks for support!
Corrado
Comment 3 ign_christian 2013-05-17 15:53:31 UTC
Guess that CERCA.VERT = VLOOKUP, FALSO = FALSE

If I'm not wrong, there's a difference between LO & MS Office:
- in MSO using FALSE
- in LO using FALSE()

So that function should be: =CERCA.VERT("CodicePerito";_RiservatoAxa_!A1:B210;2;FALSO())

Please try again using that.
Comment 4 CORRADO VIAZZI 2013-05-17 16:33:22 UTC
Thanks for reply.

I think that the error is in the conversion done by LO.

Open the file with Excel 2010 and You will obtain:
=CERCA.VERT("CodicePerito";_RiservatoAxa_!A1:B210;2;FALSO)


Open the same file with LO and You will find:
=CERCA.VERT("CodicePerito";$_RiservatoAxa_.A1:B210;2;0)

If You look carefully You will see that:
_RiservatoAxa_!

becomes:
$_RiservatoAxa_.

a dollar "$" is added as first char and the end point "." becomes an exclamation mark "!"

Thanks,
Corrado
Comment 5 ign_christian 2013-05-17 16:42:12 UTC
Yes indeed there is a difference writing in VLOOKUP function between LO & MSO. About 'Protect Sheet' I think it's another issue.

Maybe someone can confirm this issue..
Comment 6 CORRADO VIAZZI 2013-05-17 17:29:42 UTC
Created attachment 79472 [details]
original file
Comment 7 CORRADO VIAZZI 2013-05-17 17:31:06 UTC
Created attachment 79473 [details]
protection removed with excel 2010

same file, but with sheet _RiservatoAXA_ protection removed
Comment 8 CORRADO VIAZZI 2013-05-17 17:54:38 UTC Comment hidden (obsolete)
Comment 9 CORRADO VIAZZI 2013-05-17 17:59:10 UTC
(In reply to comment #8)
> (In reply to comment #5)
> > Yes indeed there is a difference writing in VLOOKUP function between LO &
> > MSO. About 'Protect Sheet' I think it's another issue.
> > 
> > Maybe someone can confirm this issue..
> 
> Please look at the two files that I have just uploaded.
> 
> CASE 1:
> 1) open the file ORIGINAL with Excel 2010 and remove sheet _Riservato AXA_
> protection. 
> 2) Save the file in xlsx;
> 3) re-open it with LO and You will see that everything seems to work fine,
> even if the function is changed ad above stated;
> 4) if You try to save it in xlsx and reopen it, everything still seems fine,
> but cell bords became thicker;
> 
> CASE 2:
> Otherwise if You:
> 1) open the file ORIGINAL with LO and remove sheet _Riservato AXA_
> protection. 
> 2) Save the file in xlsx.
> 2) re-open it with LO and You will see that nothing works.
> 
> 
> Help!!!
> :-)
CASE3:
One more thing: if You:
1) open CASE2 file with Excel 2010
2) just save 
3) re-open it with LO... magic it works again!
Comment 10 CORRADO VIAZZI 2013-05-17 17:59:39 UTC
Created attachment 79476 [details]
original with protection removed by LO
Comment 11 CORRADO VIAZZI 2013-05-17 18:00:41 UTC
Created attachment 79477 [details]
original with protection removed by LO, then reopen with Excel and saved

Works again!
Comment 12 ign_christian 2013-05-18 04:05:22 UTC
(In reply to comment #8)
> 
> CASE 1:
> 1) open the file ORIGINAL with Excel 2010 and remove sheet _Riservato AXA_
> protection. 
> 2) Save the file in xlsx;
> 3) re-open it with LO and You will see that everything seems to work fine,
> even if the function is changed ad above stated;
> 4) if You try to save it in xlsx and reopen it, everything still seems fine,
> but cell bords became thicker;

Seems that I often saw that bug reported. You can search & comment on that bug previously reported.

> CASE 2:
> Otherwise if You:
> 1) open the file ORIGINAL with LO and remove sheet _Riservato AXA_
> protection. 
> 2) Save the file in xlsx.
> 2) re-open it with LO and You will see that nothing works.

Not reproducible on LO 4.0.3.3 (Win7 32bit). Could you please test again using 4.0.3 release?

I found another issue that 'Protect Sheet' removed after saving & reopening that XLSX file. Please confirm if you also find that.
Comment 13 CORRADO VIAZZI 2013-05-18 06:48:31 UTC
(In reply to comment #12)
> (In reply to comment #8)
> > 
> > CASE 1:
> > 1) open the file ORIGINAL with Excel 2010 and remove sheet _Riservato AXA_
> > protection. 
> > 2) Save the file in xlsx;
> > 3) re-open it with LO and You will see that everything seems to work fine,
> > even if the function is changed ad above stated;
> > 4) if You try to save it in xlsx and reopen it, everything still seems fine,
> > but cell bords became thicker;
> 
> Seems that I often saw that bug reported. You can search & comment on that
> bug previously reported.
> 
> > CASE 2:
> > Otherwise if You:
> > 1) open the file ORIGINAL with LO and remove sheet _Riservato AXA_
> > protection. 
> > 2) Save the file in xlsx.
> > 2) re-open it with LO and You will see that nothing works.
> 
> Not reproducible on LO 4.0.3.3 (Win7 32bit). Could you please test again
> using 4.0.3 release?
> 
> I found another issue that 'Protect Sheet' removed after saving & reopening
> that XLSX file. Please confirm if you also find that.

CASE 1: please, what is the bug report number?
CASE 2: I confirm the procedure that I reported is fully reproducible even on LO 4.0.3.3, I need more info from You.
Comment 14 ign_christian 2013-05-18 09:56:12 UTC
I'm sorry if not describing clearly. Try to describe this issue(s):

CASE 1 (borders become thicker after saving & reopening XLSX produced by MSO)
See Bug 58356 , Bug 52578 , maybe related too with Bug 53287

CASE 2 
- Open attachment 79472 [details] & follow your instructions, you was right..no change with VLOOKUP function & result on cell B5. I'm sorry, Comment #12 tested using attachment 79473 [details]

-> I'm not sure, seems the real problem is difference between VLOOKUP function written with MSO & LO (see Bug 54657)
* 'FALSE' written in MSO -> change to '0' in LO
* 'FALSE' in MSO = 'FALSE()' in LO

@Julien Nabet, maybe you could confirm this issue?
Comment 15 ign_christian 2013-05-18 10:37:15 UTC
> -> I'm not sure, seems the real problem is difference between VLOOKUP
> function written with MSO & LO (see Bug 54657)
> * 'FALSE' written in MSO -> change to '0' in LO
> * 'FALSE' in MSO = 'FALSE()' in LO

But it's ok if 'Protect Sheet' disabled from MSO.
Comment 16 Julien Nabet 2013-05-18 13:22:28 UTC
On pc Debian x86-64 with master sources updated today, I gave a try to the first file for the moment.
I noticed that if in B5, I put 1 instead of 0, then put back 0, it's ok.
So I try to force recalculation with Ctrl/Shift/F9 (see https://help.libreoffice.org/Calc/Recalculate)

I'll try tests on other files
Comment 17 Julien Nabet 2013-05-18 13:34:48 UTC
I tried the second file then I leafed through the other comments, it seems related to recalculation issue each time.

IHMO, the cell borders problem is another issue.

Kohei/Markus/Eike: one for you?
Comment 18 CORRADO VIAZZI 2013-05-19 14:45:42 UTC
(In reply to comment #15)
> > -> I'm not sure, seems the real problem is difference between VLOOKUP
> > function written with MSO & LO (see Bug 54657)
> > * 'FALSE' written in MSO -> change to '0' in LO
> > * 'FALSE' in MSO = 'FALSE()' in LO
> 
> But it's ok if 'Protect Sheet' disabled from MSO.


Yes, Your last row is right! It's a really behavior, and that is why I think that it's not connected with FALSE.
Thanks for help!!!
Comment 19 CORRADO VIAZZI 2013-05-19 14:48:36 UTC
(In reply to comment #17)
> I tried the second file then I leafed through the other comments, it seems
> related to recalculation issue each time.
> 
> IHMO, the cell borders problem is another issue.
> 
> Kohei/Markus/Eike: one for you?

I can confirm that the borders problem is not collegued. I thin it is the same bug nĀ°53287.
Thanks guys!
Comment 20 Julien Nabet 2013-05-19 15:14:44 UTC
Corrado: the version is the earliest one known, not the more recent. (see https://wiki.documentfoundation.org/QA/Bugzilla/Fields/Version)
Comment 21 CORRADO VIAZZI 2013-05-19 15:27:14 UTC
(In reply to comment #20)
> Corrado: the version is the earliest one known, not the more recent. (see
> https://wiki.documentfoundation.org/QA/Bugzilla/Fields/Version)
Sorry!
:-(
Comment 22 Julien Nabet 2013-05-19 16:51:56 UTC
No problem Corrado, now you'll know :-)
Comment 23 QA Administrators 2015-03-04 02:23:20 UTC Comment hidden (obsolete)
Comment 24 tommy27 2016-04-16 07:25:37 UTC Comment hidden (obsolete)
Comment 25 Timur 2017-05-18 09:43:21 UTC
This bug needed a minimal test case and a simple explanation. 

If I understood it well: open attachment 79401 [details] and see sheet: "Acqua condotta sintesi", cell: B5 which is in MSO:
=VLOOKUP("CodicePerito";_RiservatoAxa_!A1:B210;2;FALSE) with value 20445.
LO opens it as: 
=VLOOKUP("CodicePerito";$_RiservatoAxa_.A1:B210;2;0) with value 0 BUT recalculation also shows 20445.

Recalculation is F9 on that cell or Shift+Ctrl+F9 to recalculate all formulas in the document.
And from LO 4.0 that we get with: "Menu/Tools/Options/LibreOffice calc/Formula - Recalculation on file load" setting Excel 2007 and newer to always recalculate or better prompt user.
Default option is to "Never recalculate". Previously LO 3.6 and earlier it was "Always" so that's why this bug is repro from 4.0.

So it's not about "Fileopen: protected workbook", it's about "Fileopen: XLSX needs hard recalculation". 
I close this as NotABug with the intention to open a new one with a general problem about recalculations which are not transparent to the user. Even help page https://help.libreoffice.org/Calc/Recalculate is wrong.