search

gorn / rspreadsheet

Manipulating LibreOffice Calc (OpenDocument Spreadsheet) files from Ruby (read / create / modify).
Other
49 stars 17 forks source link

Setting background color get more cells colorized #40

Closed twnaing closed 5 years ago

twnaing commented 5 years ago

I am trying to set background color using the following code.

cell = sheet.cell("#{col}#{row}")
cell.format.background_color = '#EEEEEE'

Please see the below screenshot to see the column I want to colorize

col and row are generated in nested loop. The following code correctly set the cell with value 'S' (so the col and row calculation is correct)

cell.value = 'S'

I have tried accessing the cells with various methods and rspreadsheet consistently (not random) mis-colorize the background-color of (unintended) cells.

Have anyone experience this problem? Any direction to fix this is much appreciated.

gorn commented 5 years ago

Can you provide a failing test? I.e. piece of code, which does not behave the way you would like?

twnaing commented 5 years ago

@gorn, thank you for quick response.

Frankly, I do not have test. I am just doing manual testing.

I updated the issue post with screenshots.

gorn commented 5 years ago

I do not need any sofisticated test. I just need to reproduce the error to be able to correct it.

When I run the following code

require 'rspreadsheet'

file = Rspreadsheet.new
sheet = file.create_worksheet

sheet.cell(1,1).value = 'A'
sheet.cell(1,1).format.background_color = '#EEEEEE'

file.save('test.ods')

It saves correct document with gray background of cell A1. Can you alter the code so it fails? Or post the file which causes the failure + the code?

twnaing commented 5 years ago

@gorn, sorry, I want to apologize you for wasting your time.

I opened ods file converted form xlsx and tried to do the operation. I found out that the ods file has lot of errors. (I tested it with jing according to this page)

ods file converted using the following software/service has the problem discussed in this issue

At least, I could advise other users not to use ods file converted from other file format.

Thank you for your time and your wonderful gem.

gorn commented 5 years ago

You are welcomed, BTW the easiest way to convert is to install LibreOffice. Cals is also superior to Microsoft Spreadsheet tool.

twnaing commented 5 years ago

@gorn, I did convert using LibreOffice. Even with LibreOffice, I still experiencing background color bleeding to other unintended cells. I am still testing and couldn't pinpoint the cause.

The worksheet needs to have

I will report back here if I found specific feature causing the background-color bleeding.

gorn commented 5 years ago

It would be helpful if you are able to isolate reproducible case. I.e. something that fails everytime. Reopen the issue than.

gorn commented 5 years ago

@twnaing even if you do not isolate the problem, it is sufficient for me to have any reproducible failing case. Even complicated one, but it should be reproducible on my side, as a first step to correct it.

twnaing commented 5 years ago

@gorn,

When I changed the background color of the cell (I wanted) the cells (I do not wanted) are also changed if they have the same format. It is not specific to feature.

example 1: if A1 and C10 are bordered in LibreOffice, they will have same background color if I change the background color of A1 by opening the file and saving the file again with rspreadsheet.

steps to reproduce

  1. create new ods file in LibreOffice
  2. set borders of A1 and C10
  3. using rspreadsheet, open the file, change the color of A1 and save the file.
  4. see both A1 and C10 are changed.

Not related to this one, if I 'clear direct formatting' the cell in LibreOffice, rspreadsheet gives error with something like 'no style node'.

I am testing with rspreadsheet 0.4.5, LibreOffice 6.0.5.2 on Mac and the ods file is ODF schema version 1.2

gorn commented 5 years ago

That has some logic ... I will try to prepare the file with which it fails

gorn commented 5 years ago

Submitted failing test, see also https://travis-ci.org/gorn/rspreadsheet/builds/537741387

gorn commented 5 years ago

@twnaing: I have found and corrected the bug. There is a new release ver 0.4.8 for that. Can you upgrade and test it on your side?

twnaing commented 5 years ago

@gorn, confirmed. It is working as expected with gem version 0.4.8.