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.
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.
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.
On Thu, Sep 29, 2011 at 10:49 PM, John Smith <spawn0...@gmail.com> wrote: > 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.
> 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.
> 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
> On Thu, Sep 29, 2011 at 10:49 PM, John Smith <spawn0...@gmail.com> wrote: > > 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.
> > 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.
On Fri, Sep 30, 2011 at 2:09 PM, talazac cedric <tala...@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.
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' ?
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.
On Fri, Sep 30, 2011 at 3:55 PM, John Smith <spawn0...@gmail.com> wrote: > 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' ?
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.
> 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
> On Fri, Sep 30, 2011 at 3:55 PM, John Smith <spawn0...@gmail.com> wrote: > > 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' ?
On Fri, Sep 30, 2011 at 4:38 PM, John Smith <spawn0...@gmail.com> wrote: > That seemed to work. Code obviously should validate data exists first, but > just for a quick test:
> 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.
On Fri, Sep 30, 2011 at 4:50 PM, talazac cedric <tala...@gmail.com> wrote: > Dear Zeno, John > I have just tried and its work. > By write something on all sheet.
>> 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
>> On Fri, Sep 30, 2011 at 3:55 PM, John Smith <spawn0...@gmail.com> wrote: >> > 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' ?
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
On Sep 30, 10:38 am, John Smith <spawn0...@gmail.com> wrote:
> 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.
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.
On Tue, Jul 24, 2012 at 10:35 AM, Nataly® <nataly.li...@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.
- 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?
Le mercredi 25 juillet 2012 09:58:17 UTC+2, Nataly® a écrit :
> 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.
Le mercredi 25 juillet 2012 09:58:17 UTC+2, Nataly® a écrit :
> 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.
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)
> 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)
