Failures editing XLS with multiple tabs

[John Smith]

Hello,

I have an issue where writing out a read XLS with changes results in an unreadable file.  The interesting bit is it only happens if I have more than 2 sheets. Only the first sheet is populated, but the presence of sheet #3 causes the corruption.


Consider this code, which works as long as '1.xls' contains only 1 or 2 sheets.

require 'spreadsheet'
book = Spreadsheet.open("1.xls")
book.worksheet(0).each {|r| r[0] = "hello"}
book.write("2.xls")

If I save a XLS which was entirely created with 'spreadsheet', it saves correctly regardless of the number of sheets.  This code is working as expected, but of course, all formatting is lost.

require 'spreadsheet'
data = Spreadsheet.open("1.xls").worksheet(0)
outf = Spreadsheet::Workbook.new
(1..4).each {|s| outf.create_worksheet :name => "Sheet#{s}"}

idx = -1
data.each do |r|
   r[0] = "hello" if idx > -1
   outf.worksheet(0).row(idx += 1).replace r
end
outf.write("3.xls")


Version Information:

Smoke:~$ gem --version
1.8.10

Smoke:~$ ruby --version
ruby 1.8.7 (2010-01-10 patchlevel 249) [universal-darwin11.0]

Smoke:~$ gem list 

*** LOCAL GEMS ***

builder (3.0.0)
fastercsv (1.5.4)
highline (1.6.2)
hpricot (0.8.4)
rally_rest_api (1.0.3)
rb-appscript (0.6.1)
ruby-ole (1.2.11.2)
rubygems-update (1.8.10)
spreadsheet (0.6.5.9)

Excel used: 2008 for Mac, version 12.3.1 (110725)

[Zeno Davatz]

Dear John

Yes, this is a known issue. Search the list please. If you modify an
XLS with several sheets but only modify one of the sheets (and don't
touch the other data), there is ow way, that spreadsheet "remembers"
what is in the other sheets. You will have to write the unmodified
sheets as well or otherwise unexpected things will happen.

Ergo: Write the modified sheet and also write the complete unmodified
sheets again, when modifying an XLS with spreadsheet with several
sheets.

If you have a patch for this issue, let me know.

Best
Zeno

[talazac cedric]

Dear Zeno
 
Can you send the patch.
I realy need to modify an excel file containing 3 sheet and I just edit 2 sheet.

Thanks in advance

Cedric

2011/9/30 Zeno Davatz <zda...@gmail.com>

[Zeno Davatz]

Dear Cedric

On Fri, Sep 30, 2011 at 2:09 PM, talazac cedric <tal...@gmail.com> wrote:

> I realy need to modify an excel file containing 3 sheet and I just edit 2 sheet.

In this case write the other sheets out as well. Spreadsheet only does
what you tell it to do.

If we have time we may finish a patch for Christmas.

Best
Zeno

[John Smith]

Hi Zeno, thanks for the reply.  I found the other thread but I'm not 100% clear.  Please correct any conclusions I drew.

- If I modify a book with 3 sheets, and the other 2 sheets contain no data, I can simply delete the empty sheets before I write, as mentioned in the other  thread.

- If I modify sheet(0) in a book and *do* have other sheets which I want preserved, I must also make a trivial modification to a cell in each sheet in order for them to be written correctly.

If I don't actually want to adjust any of the data from sheets(1..2), is it acceptable to read the contents of a cell and write back the same value (which would technically be a new ruby string object).  Would that be considered a valid 'change' ?

[Zeno Davatz]

Dear John

Yes, this sounds plausible. Have you tried that?

Think of spreadsheet, as in that it does what you tell it to do. If
you only tell spreadsheet to write one Sheet but the File contains
tree, then some strange things will happen. You have to rewrite every
sheet, no matter if you changed something in the sheet or not. If the
sheet is there, write it. Or you have to delete the sheets if they are
empty.

Spreadsheet does not guess what you want to do with the sheets that
you opened but did not edit.

Best
Zeno

[John Smith]

That seemed to work.  Code obviously should validate data exists first, but just for a quick test:

require 'spreadsheet'
book = Spreadsheet.open("1.xls")

data = book.worksheet(0)
data.each {|r| r[0] = "hello"}
book.worksheet(1).first[0] = book.worksheet(1).first[0].dup()
book.worksheet(2).first[0] = book.worksheet(1).first[0].dup()
book.write("2.xls")

You keep suggesting re-writing the sheet.  ("If the sheet is there, write it").  I'm inferring that these is a mechanism to tell the Writer explicitly to write the sheets out, but I'm not seeing how that is done.  The online docs reference a write instance method for worksheet (as well as for workbook) class, but I seem to be missing something.

>> book.worksheets[0].write()
NoMethodError: undefined method `write' for #<Spreadsheet::Excel::Worksheet:0x1024fac88>

Is there an iterative was to explicitly write each sheet as opposed to calling book.write ?  At any rate, the above workaround will fix me.  Just curious if I'm missing a 'cleaner' way.

Thanks again

[talazac cedric]

Dear Zeno, John
I have just tried and its work.
By write something on all sheet.

Cedric

2011/9/30 Zeno Davatz <zda...@gmail.com>

Dear John

[Zeno Davatz]

Dear John

What works, works! Thanks for sharing. If we find something better, I
will let you know.

Best
Zeno

[Zeno Davatz]

Dear Cedric

Thank John and if you have some time write a short writeup in a gist
with your sample file and the code of John.

Best
Zeno

[Philippe Rathe]

Rewriting the worksheets was not working for me. I had to use:

book.worksheet(1).each(0) { |row| row.replace row }
book.worksheet(2).each(0) { |row| row.replace row }
each(0) is if you would want to prevent skipping "empty" rows

[Zeno Davatz]

Dear Philippe

On Wed, Oct 26, 2011 at 10:31 PM, Philippe Rathe <pra...@gmail.com> wrote:
> Rewriting the worksheets was not working for me. I had to use:
>
> book.worksheet(1).each(0) { |row| row.replace row }
> book.worksheet(2).each(0) { |row| row.replace row }
> each(0) is if you would want to prevent skipping "empty" rows

Ok, thanks for the Feedback.

Best
Zeno

[Nataly®]

Hello Zeno,

Has this issue been corrected?
I'm having some problem with a 2 sheets excel file.
When i update the first sheet, and not the second one, then write the file everything is ok.
But if i update the second sheet (and not the first one) then the first sheet is still ok but the second one is empty.
Strange as it seems the contrary of what what explained in this thread.
I can try to update the first one too when i update the second one to see if this help.
I wanted to let you know about this problem if you are still working on it.

[Zeno Davatz]

Dear Nataly

On Tue, Jul 24, 2012 at 10:35 AM, Nataly® <nataly...@gmail.com> wrote:

> Has this issue been corrected?

What issue? Please link it here.

> I'm having some problem with a 2 sheets excel file.
> When i update the first sheet, and not the second one, then write the file
> everything is ok.
> But if i update the second sheet (and not the first one) then the first
> sheet is still ok but the second one is empty.

Have you tried always writing both sheets?

> Strange as it seems the contrary of what what explained in this thread.
> I can try to update the first one too when i update the second one to see if
> this help.

Yes, please try that and let us know if it works or not.

Best
Zeno

[Nataly®]

Hi Zeno,

 

What issue? Please link it here.

The one debated here : if you write in a sheet and not on the other(s), they will be empty.
But here my problem is a bit different as i explained it.

Have you tried always writing both sheets?

I'm trying but i have error when i try writting on the second one too.
I'm keep on doing tests to give you more details about that.

[Nataly®]

Ok here are the tests i've made :

- I write on the worksheet 0 only : everything is ok, worksheet 0 written, worksheet 1 unchanged.

- I write on the worksheet 1 only : worksheet 0 is ok (not emptied), but the worksheet 1 is empty. If i try a second time (so writting on the emptied worksheet 1) i have an error on the line of the Excel file write call ("NoMethodError (undedined method '>' for nil:NilClass)" in the rails console).
If i clear the worksheet 1 (even if it is already empty), i don't have the error, so something was surely written but not "visible".

- I tried writting on both worksheets but i have the same error than previously described.

When deleting and adding new worksheet on the Excel file, i use OpenOffice.
I switched the 2 worksheets but the problem is the same.

So for now i can only write on the worksheet 0, which is annoying.

Any idea?

[Nataly®]

Some news : i surely tried to write on the 2 worksheets at once when the second one was emptied and was making an error.
I've tried again to write on the 2 worksheets and it works.
Sorry for the bother.

Well if someone has the same problems i've described, it will maybe help him/her to find this thread and the solution ;0)

[Zeno Davatz]

Dear Nataly

It sure will!

Thanks for the documentation.

Best
Zeno

[Arjun Anand]

Hi

We were running into this issue as well and we made a pull request to make the code work for what we needed it to do. Please check out: https://github.com/zdavatz/spreadsheet/pull/47

With this, we are now able to do:

require 'spreadsheet'

book = Spreadsheet.open("1.xls")

book.worksheet(0).insert_row(<index>, [1,2,3,4])

book.write("2.xls")

Cheers

[Zeno Davatz]

Dear Arjun

Good stuff! Thank you!

I will check this and if no testcases break I will merge it in.

Best

Zeno

--
You received this message because you are subscribed to the Google Groups "spreadsheet" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rubyspreadshe...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Zeno Davatz]

Dear Arjun

spreadsheet-0.8.6.gem has been released.

Best
Zeno