Comments in Calc always showing in xlsx file

21 replies [Last post]
frankster
Offline
Last seen: 2 years 25 weeks ago
Title:
Joined: 17 Mar 2012
Posts: 7

I have an xlsx file that has comments in the cells. It is always visible. I can do a right-click on the cell and select "Show Comment" and it will work properly by displaying only when I hover over the cell. However, if I save it and close it the next time I open it the comments are always visible again. I can save it as an ods file and it'll work properly. I was able to duplicate this problem from a new spreadsheet. I would insert a comment on a couple of cells and it has the same problems if it's saved as an xlsx document.

Is there some preference or setting to make all the comments show up only when I hover over the cell.

I'm using LiberOffice 3.5.1 on Mac, OS X 10.6.8

n/a
PeterRoots
Offline
Last seen: 50 weeks 4 days ago
Title: ==Moderator==
Joined: 25 Oct 2011
Posts: 941
try saving it as .ods and see
try saving it as .ods and see if that helps

Linux Mint 15 LibreOffice 4.1.1.2 : OpenSUSE 12.3 LibreOffice 4.0.3

frankster
Offline
Last seen: 2 years 25 weeks ago
Title:
Joined: 17 Mar 2012
Posts: 7
Thanks. I did try that and it
Thanks. I did try that and it does work. However, this is a shared document and I need to save it as an xlsx document to share it with my co-workers. I tried to open it in OpenOffice 3.3, which is the latest. That works ok but unfortunately it won't allow me to save it as an xlsx file. It's not one of the available format options.
n/a
karolus
Offline
Last seen: 46 min 45 sec ago
Title: ★★★★★
Joined: 11 Mar 2012
Posts: 177
Hi I'm sure, your co-workers

Hi

I'm sure, your co-workers can also open .xls files!

Karo


PeterRoots
Offline
Last seen: 50 weeks 4 days ago
Title: ==Moderator==
Joined: 25 Oct 2011
Posts: 941
xlsx is not a format designed

xlsx is not a format designed for sharing, it was designed to be microsoft compatible. OOXML, the standard MS pushed through (when ODF already existed), is also not designed to make sharing easy but to make conversion of old, proprietary, MS file formats reliable. To make matters worse, MS office 2007 and 2010 do not comply with the OOXML standard.

Which all means?

You are out of luck if you are stuck with using Microsoft format documents. MS office 2010 can work with ods (as can 2007 with service pack 2) so you might be in luck if you try using ods (but MS probably has issues with the conversion of ods -> xlsx in the same way as LO does the other way round)

Linux Mint 15 LibreOffice 4.1.1.2 : OpenSUSE 12.3 LibreOffice 4.0.3

frankster
Offline
Last seen: 2 years 25 weeks ago
Title:
Joined: 17 Mar 2012
Posts: 7
Ok, thanks for the

Ok, thanks for the information. Unfortunately it seems right now using Microsoft format documents is the standard.

It wouldn't be so bad if I can easily toggle "Show Comments" easily. Is there a way to toggle the Comments for all the cells at once instead of one at a time?

There are many cells that has Comments so it makes it hard to see the cells without turning it off. Reading the documents, as far as I can tell I have to do this one cell at a time by right-clicking on the cell.

n/a
karolus
Offline
Last seen: 46 min 45 sec ago
Title: ★★★★★
Joined: 11 Mar 2012
Posts: 177
Hi Here are a Basic macro to

Hi

Here are a Basic macro to hide all Annotations in a snatch:


sub hideAnnotations
    with thisComponent.sheets
    for i = 0 to .count-1
        annocells =    .getbyindex(i).querycontentcells(8).createEnumeration
        do while annocells.hasmoreElements()
            annocells.nextElement.getAnnotation.setisVisible( false )
        loop
    next i
    end with
end sub


Karo



frankster
Offline
Last seen: 2 years 25 weeks ago
Title:
Joined: 17 Mar 2012
Posts: 7
Thanks, however I'm not

Thanks, however I'm not familiar with using macros.

I selected Tools - Macros - Organize Macros - LibreOffice Basic. I selected my document and the "New" button to put your code

I get the following error

*********************

BASIC runtime error.

Property or method not found:getAnnotation.

n/a
karolus
Offline
Last seen: 46 min 45 sec ago
Title: ★★★★★
Joined: 11 Mar 2012
Posts: 177
Hi The Code above works fine

Hi

The Code above works fine here, what tell your Interpreter if you change to:


sub hideAnnotations
    with thisComponent.sheets
    for i = 0 to .count-1
        annocells =    .getbyindex(i).querycontentcells(8).createEnumeration
        do while annocells.hasmoreElements()
            cell = annocells.nextElement

            cell.Annotation.setisVisible( false )
        loop
    next i
    end with
end sub


Karo



frankster
Offline
Last seen: 2 years 25 weeks ago
Title:
Joined: 17 Mar 2012
Posts: 7
Sorry for the delay in

Sorry for the delay in responding.

I had tried this on a Windows 7 64 bit machine and I couldn't even run it saying I had a bad JRE. I do have the Oracle 7.03 installed.

On the Mac OS X 10.6.8 machine where I originally had the error, unfortunately I see the same error message

*********************

BASIC runtime error.

Property or method not found:getAnnotation.

*********************

I don't know if it matters but on OS X 10.6.8

java version "1.6.0_29"

Java(TM) SE Runtime Environment (build 1.6.0_29-b11-402-10M3527)

Java HotSpot(TM) 64-Bit Server VM (build 20.4-b02-402, mixed mode)

n/a
karolus
Offline
Last seen: 46 min 45 sec ago
Title: ★★★★★
Joined: 11 Mar 2012
Posts: 177
Hi Ok, my fault i don't test

Hi

Ok, my fault i don't test against consecutive cells with Comments.

Here it is:


sub hideAnnotations
    with thisComponent.sheets
    for i = 0 to .count-1
        annocells =    .getbyindex(i).querycontentcells(8).createEnumeration
        do while annocells.hasmoreElements()
            part() = annocells.nextElement
            if not part.supportsService("com.sun.star.table.Cell") then
                for r = 0 to part.getRows.Count -1
                    for c = 0 to part.getColumns.Count -1
                        part.getCellByPosition(c, r).Annotation.setisVisible( false )
                    next c
                next r
            else
                part.Annotation.setisVisible( false )
            end if
        loop
    next i
    end with
end sub


Karo


frankster
Offline
Last seen: 2 years 25 weeks ago
Title:
Joined: 17 Mar 2012
Posts: 7
Thank you, this works. There

Thank you, this works.

There is a related problem. After creating the macro and testing that it works I saved it. After I close the document and reopen it the macro is not there anymore. Apparently it's not being saved.

There is also another unrelated issue that I just discovered that will force me to use MS Office. If I edit something in that document and someone uses MS Office to open it, they get a message that some cells are corrupted and needs to be rebuilt. I don't see this error when opening the same document in LibreOffice

n/a
i22yb
Offline
Last seen: 2 years 25 weeks ago
Title:
Joined: 26 Apr 2012
Posts: 1
Comments Showing All The Time - The Fix
I just ran into this same problem today.  The solution I have found is to open the file in Excel and save it as an Excel '97/2003 .xls file.  Do this and then share the XLS file with everyone.  Both LibreOffice and MS Office will then open the file with comments working properly.  Just tell all the MS Office users NOT to update the format to .xlsx.  If you have access to the people's computers that use MS Office, you could go in and change their default file save format to .xls.
frankster
Offline
Last seen: 2 years 25 weeks ago
Title:
Joined: 17 Mar 2012
Posts: 7
I agree that this could be a

I agree that this could be a solution but unfortunately it's not a realistic solution in our company.

First of all, I would not want other people accessing my computer and change settings even if they knew my user id and password. MS Office is what the majority of the people in our company uses and we distribute templates to use. These templates are in .xlsx format. There are also macros and Excel Add-ins that are used.

n/a
razor7_996
Offline
Last seen: 1 year 37 weeks ago
Title: ★★
Joined: 27 Jun 2012
Posts: 10
LO 3.6.0 macro broken

Hi, I was a happy user using the macro above, but after upgrading to LO 3.6.0 it stopped working. Maybe someone can fix it for me.

Thanks a lot!

Visit, http://www.mgscreativa.com Joomla Experts

karolus
Offline
Last seen: 46 min 46 sec ago
Title: ★★★★★
Joined: 11 Mar 2012
Posts: 177
Hallo razor7_996 wrote: Hi,

Hallo

razor7_996 wrote:

Hi, I was a happy user using the macro above, but after upgrading to LO 3.6.0 it stopped working. Maybe someone can fix it for me.

Thanks a lot!


sub hideAnnotations
    with thisComponent.sheets()
    for i = 0 to .count -1
        annos = .getbyIndex(i).Annotations
        for n = 0 to annos.count -1
            annos.getbyIndex(n).setisVisible(0)
        next n
    next i
    end with   
end sub


Karolus


razor7_996
Offline
Last seen: 1 year 37 weeks ago
Title: ★★
Joined: 27 Jun 2012
Posts: 10
Thanks a lot!, will test it
Thanks a lot!, will test it ASAP

Visit, http://www.mgscreativa.com Joomla Experts

gbpacheco
Offline
Last seen: 2 years 2 weeks ago
Title:
Joined: 2 Oct 2012
Posts: 5
An additional info… It was

An additional info… It was useful for me.
You can try this extension to show or hide all comments:
http://extensions.libreoffice.org/extension-center/shownotes

azertyh
Offline
Last seen: 25 weeks 4 hours ago
Title: ★★
Joined: 27 May 2011
Posts: 17
thanks! i will try it when i

thanks! i will try it when i have time.

ubuntu 12.04 / libreoffice 3.5.4

skepticloud
Offline
Last seen: 1 year 31 weeks ago
Title:
Joined: 17 Mar 2013
Posts: 1
Bug now fixed

This thread is still the top hit on Google for this issue so folks will be going away thinking this is the status quo. Feel free to attack this post to an old thread if you think that is more important.

This behaviour was a bug in LibreOffice. It was introduced quite recently (3.5.x I think), was fairly quickly identified and is fixed in the 4.0 release (certainly on Ubuntu, probably on Windows etc as well). Anyone having this problem with xlsx files might want to consider upgrading to 4.0. See bug tracking here: https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/990868
I run LibreOffice on an Ubuntu server which isn’t always up to date and thus caught me out, I suspect I’m not the only one. Nice work on the macros btw, but novices will find it easier to upgrade to 4.0 now.

Some of the posts on here sound like LibreOffice is about trying to build the Betamax of office suites. It’s not. No-one wants to be a footnote in history saying “LibreOffice was better in theory but now we all use MS Office”. Working with xlsx is important, the developers are smart and they know this and I think they work pretty hard to make sure it keeps working.

oweng
oweng's picture
Offline
Last seen: 16 hours 24 min ago
Title: ==Moderator==
Joined: 26 Jan 2012
Posts: 2595
resolved bug

As per the prior comment, the original (now resolved) bug for this issue is FDO#46278. The problem has been fixed for the v3.6 and v4.0 series, but not v3.5.7.2 (comments state v3.5.8, but this was never released).

nrnugteren
Offline
Last seen: 26 weeks 3 days ago
Title:
Joined: 23 Apr 2014
Posts: 1
Comments still visible by default

Realizing I am reviving an old post, but as mentioned earlier this is still the top result for Google.

Currently using LO 4.2.3.3 and all comments are still visible by default – which I assume is not the preferred and compatible way.

From the Calc options -> View, one has the possibility to hide comment indicators but no option is offered to hide or show comments by default.

Any insights of how/when this should be possible, without standard users having to apply some rocket science?

Your ideas are very much appreciated.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.