Calc will not save file after sheet deleted.

users
#1

Hi,

I have an old spreadsheet in ODS format. The file was originally an XLS file then converted to ODS format several years ago *and* has been used weekly over that period without any problems.

When I try an delete a sheet from the file I get an error "Error saving the document FILE: Write Error. The file could not be written."

If, prior to attempting the sheet removal, I change the contents of a cell the file is saved without any problems.

When the error occurs the saving progress bar appears, moves to ~20% completion stops for several seconds, then progresses to ~80% completion at normal saving speed, stops again for several seconds then crashes. This is in contrast to a normal save where the progress bar moves at a constant rate for the entire saving process.

If I attempt to insert a sheet I get the same problem.

*I should point out* here that the sheet being deleted and inserted are mid-file, that is I have 18 tabs and I am trying to delete sheet 7. When I insert a sheet I am inserting after sheet 7 or 8 (does not really matter).

What I have noticed is if I use the 'add sheet' tab -- that is the last tab with the green '+' sign instead of the name to add a sheet; a sheet can be added and the document saved. This same sheet can be deleted, although the progress bar behaves as if the error is going to happen, that is it pauses at 20% and 80% completion; except this time it finishes without the error dialog appearing.

My tests indicate that the last sheet of the original untouched file can be deleted. Like above the progress bar behaves an unusual way but does complete without the error dialog appearing.

I can not reproduce the error on a new clean file.

If I work back through the historical copies of the file, I can only reproduce the error in this years version. It is as if the file has become partially corrupted. The question is how and can it be easily fixed.

Before I spend the time recreating the file from scratch or modifying one of the old backups...

*Has anyone encountered or heard of a similar problem?*

*Anyone got some ideas?*

*Is it worth upgrading LibreOffice?*

I am running LibreOffice 3.4.1 OOO340m1 (Build:103) on Ubuntu 10.04 LTS. The java version used is Sun Microsystems Inc. 1.6.0_26, although I have also checked and verified the problem occurs on 1.6.0_20 (which just happened to be on the system).

LibreOffice was manually installed from downloaded DEB files *not* installed from the LibreOffice PPA repository or the Ubuntu Software Centre.

#2

Hi

  Check if the file is opened as "read only"

  If it is like that you have to save the file with new name for changing
it.

Regards,

Jorge Rodríguez

#3

Simon,

Hi,

I have an old spreadsheet in ODS format. The file was originally an XLS
file then converted to ODS format several years ago *and* has been used
weekly over that period without any problems.

When I try an delete a sheet from the file I get an error "Error saving
the document FILE: Write Error. The file could not be written."

If, prior to attempting the sheet removal, I change the contents of a
cell the file is saved without any problems.

When the error occurs the saving progress bar appears, moves to ~20%
completion stops for several seconds, then progresses to ~80% completion
at normal saving speed, stops again for several seconds then crashes.
This is in contrast to a normal save where the progress bar moves at a
constant rate for the entire saving process.

If I attempt to insert a sheet I get the same problem.

*I should point out* here that the sheet being deleted and inserted are
mid-file, that is I have 18 tabs and I am trying to delete sheet 7. When
I insert a sheet I am inserting after sheet 7 or 8 (does not really matter).

What I have noticed is if I use the 'add sheet' tab -- that is the last
tab with the green '+' sign instead of the name to add a sheet; a sheet
can be added and the document saved. This same sheet can be deleted,
although the progress bar behaves as if the error is going to happen,
that is it pauses at 20% and 80% completion; except this time it
finishes without the error dialog appearing.

My tests indicate that the last sheet of the original untouched file can
be deleted. Like above the progress bar behaves an unusual way but does
complete without the error dialog appearing.

I can not reproduce the error on a new clean file.

If I work back through the historical copies of the file, I can only
reproduce the error in this years version. It is as if the file has
become partially corrupted. The question is how and can it be easily fixed.

Before I spend the time recreating the file from scratch or modifying
one of the old backups...

*Has anyone encountered or heard of a similar problem?*

*Anyone got some ideas?*

*Is it worth upgrading LibreOffice?*

I am running LibreOffice 3.4.1 OOO340m1 (Build:103) on Ubuntu 10.04 LTS.
The java version used is Sun Microsystems Inc. 1.6.0_26, although I have
also checked and verified the problem occurs on 1.6.0_20 (which just
happened to be on the system).

LibreOffice was manually installed from downloaded DEB files *not*
installed from the LibreOffice PPA repository or the Ubuntu Software Centre.

--
Cheers Simon

    Simon Cropper
    Principal Consultant
    Botanicus Australia Pty Ltd
    PO Box 160, Sunshine, VIC
    W: www.botanicusaustralia.com.au

I have two thoughts: first can you from a known well behaved file move
forward without any difficulties and the second is to either install
3.4.2 or 3.3.3 and see if the problem clears up. I believe you have a
corrupted file. If moving forward works, its a pain but it might be
easier to recreate what you need from the clean fork.

#4

Question -- Check if the file is opened as "read only"

    No it is not and permissions are all OK.

Question -- If it is like that you have to save the file with new name for changing it.

    The problem occurs on original, copies of original and when
    I try and save as different name.

#5

Hi.

Question -- Check if the file is opened as "read only"

   No it is not and permissions are all OK.

Question -- If it is like that you have to save the file with new name
for changing it.

   The problem occurs on original, copies of original and when
   I try and save as different name.

Hi

    Check if the file is opened as "read only"

    If it is like that you have to save the file with new name for
changing
it.

Regards,

Jorge Rodríguez

__________

Hi,

I have an old spreadsheet in ODS format. The file was originally an XLS
file then converted to ODS format several years ago *and* has been used
weekly over that period without any problems.

When I try an delete a sheet from the file I get an error "Error saving
the document FILE: Write Error. The file could not be written."

If, prior to attempting the sheet removal, I change the contents of a
cell the file is saved without any problems.

When the error occurs the saving progress bar appears, moves to ~20%
completion stops for several seconds, then progresses to ~80%
completion
at normal saving speed, stops again for several seconds then crashes.
This is in contrast to a normal save where the progress bar moves at a
constant rate for the entire saving process.

If I attempt to insert a sheet I get the same problem.

*I should point out* here that the sheet being deleted and inserted are
mid-file, that is I have 18 tabs and I am trying to delete sheet 7.
When
I insert a sheet I am inserting after sheet 7 or 8 (does not really
matter).

What I have noticed is if I use the 'add sheet' tab -- that is the last
tab with the green '+' sign instead of the name to add a sheet; a sheet
can be added and the document saved. This same sheet can be deleted,
although the progress bar behaves as if the error is going to happen,
that is it pauses at 20% and 80% completion; except this time it
finishes without the error dialog appearing.

My tests indicate that the last sheet of the original untouched file
can
be deleted. Like above the progress bar behaves an unusual way but does
complete without the error dialog appearing.

I can not reproduce the error on a new clean file.

If I work back through the historical copies of the file, I can only
reproduce the error in this years version. It is as if the file has
become partially corrupted. The question is how and can it be easily
fixed.

Before I spend the time recreating the file from scratch or modifying
one of the old backups...

*Has anyone encountered or heard of a similar problem?*

*Anyone got some ideas?*

*Is it worth upgrading LibreOffice?*

I am running LibreOffice 3.4.1 OOO340m1 (Build:103) on Ubuntu 10.04
LTS.
The java version used is Sun Microsystems Inc. 1.6.0_26, although I
have
also checked and verified the problem occurs on 1.6.0_20 (which just
happened to be on the system).

LibreOffice was manually installed from downloaded DEB files *not*
installed from the LibreOffice PPA repository or the Ubuntu Software
Centre.

--
Cheers Simon

     Simon Cropper
     Principal Consultant
     Botanicus Australia Pty Ltd
     PO Box 160, Sunshine, VIC
     W: www.botanicusaustralia.com.au

And do you get the same problem if you save as a new name without
deleting the sheet (so it saves OK) and exit running instances of LO and
then re-open it.
Are there references to/from the sheet you deleted from other sheets
that could be broken and causing problems.
steve

#6

1. I can but it will require effort. This is not a problem except that without knowing the problem I might spend a day cutting-and-pasting the formulas and macros only to find the problem is transferred.

2. Downloading 3.4.2 as we speak to see if the problem persists.

3. I also suspect I have a corrupt file but as it contains unique data and complex formula I will need to move these into the new file. As stated above I am reticent to do this without knowing where the error resides and why it occurred.

4. My preference is to recreate a "clean fork" -- assuming the file is actually corrupt, so I have confidence it using the file.

#7

...

*Has anyone encountered or heard of a similar problem?*

*Anyone got some ideas?*

*Is it worth upgrading LibreOffice?*

I am running LibreOffice 3.4.1 OOO340m1 (Build:103) on Ubuntu 10.04 LTS.
The java version used is Sun Microsystems Inc. 1.6.0_26, although I have
also checked and verified the problem occurs on 1.6.0_20 (which just
happened to be on the system).

LibreOffice was manually installed from downloaded DEB files *not*
installed from the LibreOffice PPA repository or the Ubuntu Software Centre.

I wonder if it's related to this bug:
https://bugs.freedesktop.org/show_bug.cgi?id=39236
[EDITING Calc crashes when deleting a sheet with a pivot table on it]

If so, you might want to test with 3.4.2 final (which is 3.4.2rc3):
http://www.libreoffice.org/download/

#8

1. do you get the same problem if you save as a new name without deleting the sheet... No. The file saves OK and behaves as per normal unless I delete or insert sheet mid-file.

2. Problem persists even if I reboot.

3. Are there references to/from the sheet... Yes, the file is riddled with cross-references. That said I have tried deleting sheets that are orphaned and the problem still occurs. If I insert a new sheet mid-file, the problem still occurs. I can chomp off the last sheet, which is cross-referenced in several locations elsewhere in the file and the problem *does not occur*.

#9

Check out the bug I just created at
  https://bugs.freedesktop.org/show_bug.cgi?id=39930

I've been fighting this corruption issue for YEARS and can't get anyone
to look at it seriously. They keep coming out with new releases, but
none of them ever solve these corruption problems.

When LO saves a file and then can't reopen it successfully, it is LO's
problem regardless of what an end user might have done.

They keep putting a new coat of paint on a rusty chassis and are
shocked, shocked when someone points out the rot.

#10

Hi Bill,

I was aware of your issue but don't believe we are talking about the same problem. My symptoms are quite different. I am not sure at present whether the problem is a corrupt file or wonky OO/LO code.

#11

Hi NoOp,

Unfortunately an upgrade did not help.

#12

Hi.

*Has anyone encountered or heard of a similar problem?*

*Anyone got some ideas?*

Check out the bug I just created at
    https://bugs.freedesktop.org/show_bug.cgi?id=39930

I've been fighting this corruption issue for YEARS and can't get anyone
to look at it seriously. They keep coming out with new releases, but
none of them ever solve these corruption problems.

When LO saves a file and then can't reopen it successfully, it is LO's
problem regardless of what an end user might have done.

They keep putting a new coat of paint on a rusty chassis and are
shocked, shocked when someone points out the rot.

--
Bill Gradwohl
Roatan, Honduras

Hi Bill,

I was aware of your issue but don't believe we are talking about the
same problem. My symptoms are quite different. I am not sure at
present whether the problem is a corrupt file or wonky OO/LO code.

I may be wrong, but I have always thought that a "Save As" saves in a
newly created file formatted from scratch. If a file that has been
"Saved As" still exhibits the problem I would think that 1. if the file
is corrupted LO is corrupting it and has a bug or 2. if the file is not
corrupted (you could delete a sheet in say OOO?) then the bug is in LO.
Either way LO has a bug.

steve

#13

Steve,

1. I am not sure about 'Save as' myself. My testing though suggests it only converts or resets the internal file structure on conversion to another format. ODS to ODS does not appear to rewrite the internal structure.

2. the files created with 'Save as' does exhibit the problem. This indicates the problem is file specific and able to be transmitted to derivatives.

3. The problem can not be recreated in a freshly created ODS file, so in essence you could argue it is not a bug.

4. My gut feeling is the file structure is somehow corrupted. Nothing that would affect normal operation but shows up when conducting broadscale sheet manipulations. How this change has come about is unclear.

OK, to prove a point, I stripped everything out of a copy of the file except the tabs and underlying structure. I opened each tab, deleted the text and cleared the font / background / linework formatting using the toolbar options. Removed the freeze panes and an object (images or textboxes) from the sheet. Saved. Tried to delete a sheet and behold, wouldn't you guess it, the file saved OK.

This is a Seinfield moment I am currently holding my head in my hands repeating "serenity now! serenity now!"

OK. OK. This indicates that something has been added to the file in recent months that is causing the problem -- an image, some imported text... hmmmm......

Really the only input to this file is text/numbers entered via the keyboard or the importation of text/numbers via CSV downloaded from a bank account. I stripped these but it made no change.

  *The short version*

To cut a long process short I deleted a sheet then proceeded to clear the contents of other sheets progressively *until* I was able to save. This identified the sheet.

I then repeated this process until I identified a single cell. In fact it was a 'duplicate cell'. This cell has an associated picklist -- that is cell range with a variety of legitimate values. It also has a comment inserted. This comment is simply an explanation and reminder to do something. *Delete the comment* and the sheet saves as expected; undo the change and the error appears. I say 'duplicate' because I copied and paste this block to another location on the same sheet as a 'fixed' copy (as the original has VLOOKUPs that change based on picklist value) but neglected to omit the comments so they were duplicated.

  *Some additional testing*

1. Going back to some 'uncorrupted' files from 2009 I am unable to reproduce the error.

2. If I create a new file I can not reproduce the error.

3. It is necessary to delete both the original and copy of the comment in question. Deleting one or the other makes no difference, both must be deleted. inspection of the contents.xml in the archive only shows one comment (I have no idea if this is normal; I presume a reference to the 'comment' object was cmpies not the object).

4. Once the offending comment-pair are deleted then the file works as expected.

5. If you reinsert a comment in the sheet -- after saving, closing and reopening the 'corrupted' file, you get an error again.

6. If you insert a comment in other sheet. In most the save works but for a couple it does not. I can not find any visible similarity in the data, format or objects on these pages (the error is triggered on certain sheets only).

7. If you create a new sheet, copy the text/numbers/format//formulas to the new sheet and delete the offending 'corrupt sheet'. Deleting another sheet does not trigger the error. If you insert a comment however in the new sheet the error appears again.

8. If I create a new sheet and do nothing except insert a comment the error occurs.

  *The upshot*

Unless someone recognised these symptoms and can put forward some ideas, I am treating this as a corrupt file. I have drawn this conclusion because (a) I can only reproduce the error on the effected file, (b) the comment insert/delete trick is suggestive but not consistent (or at least from what I can tell) - random sheets cause the file save error if comments are inserted; inserting comments into new sheets also trigger the error.

I just hope that recreating this file does not corrupt the new file as I will need to copy blocks of text, numbers and formulas.

#14

Am 10.08.2011 01:46, Simon Cropper wrote:

Hi,

I have an old spreadsheet in ODS format. The file was originally an XLS
file then converted to ODS format several years ago *and* has been used
weekly over that period without any problems.

Since the days of OpenOffice.org 1.0 I use the same dBase file together with Calc data pilots as reporting engine.
I'm pretty sure that this file will take another 50,000 records during the next 10 years without any problems.

Spreadsheets are the worst data storage, regardless if Excel, ODF or whatever. Even a collection of plain text tables is far better than spreadsheets.

#15

Unless someone recognised these symptoms and can put forward some ideas,
I am treating this as a corrupt file.

When LO can't read its own handwriting, so to speak, does that not prove
LO has a serious problem?
When you use only the tools provided by LO to manipulate your document
and that document gets trashed due to corruption, does that not prove LO
has a serious problem?

Now that you've concluded that "corruption" has occurred, what do you
think should happen to the software that did the corrupting?

or
B: ) Should it stay broken, and in return for not expending the effort
to fix the underlying rot, you'll accept the next releases 3 new bells
and 4 whistles?

Check the archives and you'll note that file corruption is a frequent
problem. Its as though giving buggy software the label "file corruption"
absolves the developers of investigating and eliminating the underlying
code bugs.

I've been reporting corruption issues for years and in that time only
one developer (with an oracle email address) ever showed any interest.
Even he dropped off the radar when Oracle lost interest in the office suite.

Does LO provide a needed service? Yes it does, just as a Ford coasting
down a highway provides a service. The difference is that if a Ford gas
tank blew up periodically, Ford would fix it.

- --
Bill Gradwohl
Roatan, Honduras

#16

Hi :slight_smile:
Please post a new bug-report against LibreOffice rather than against
OpenOffice. It stands a good chance of getting soem attention now.

A lot of stuff just got sat on by Sun forever it seems. LibreOffice is vastly
more interested in actually fixing things and TDF doesn't block the devs from
doing stuff even if they are voluntary devs rather employees. As long as stuff
passes quality control it's allowed in.

Regards from
Tom :slight_smile:

#17

I already did:
https://bugs.freedesktop.org/show_bug.cgi?id=39930

I'm in the process of once again rewriting my spreadsheet from scratch
and importing my macro code into it. At least 2 days worth of tedious work.

The bug report I submitted was to help someone else avoid my problems in
the future should the underlying root cause of the corruption I'm seeing
get fixed.

A fix won't help me now, as I don't trust the .ods file contents.

- --
Bill Gradwohl
Roatan, Honduras

#18

Hi :slight_smile:
+1
Hmm, well maybe not the absolute worst. A sieve or broken floppy disc or an
ancient format that no program can read might be worse but yes, databases with
an audit-trail are much more secure and plain text such as Csv ensure that there
will always be some program somewhere that can at least access the data.

Regards from
Tom :slight_smile:

#19

Hi :slight_smile:
I was wondering if it's to do with permissions of the folder you are trying to
save into. In linux systems it's difficult to save in folders such as

/root
/boot
and sometimes into other users or guests folders on a multi-user system. I have
a feeling that you have already tried saving into another folder or onto your
own desktop but i am not certain.

Regards from
Tom :slight_smile:

#20

Bill,

It is obvious that you do not like OO or LO Calc, and that you don't like the fact that your data is continually corrupted.

You also say you continuously have to rebuild your spreadsheet and that the process can take several days.

If you are so unhappy with the format, corruption and dirty code, my question is "why do you persist?" Change packages. With the time and effort you have already put into recreating corrupted spreadsheets you could of written your own application or mastered alternative packages.

I am not trying to be rude here it just if I had a package that caused me so much wasted time I would just move on.