0

Trying to style an excel following - ruby spreadsheet row background color but nothing is happening for me -

Here goes my code -

My formats:

pass_format = Spreadsheet::Format.new :color=> :blue, :pattern_fg_color => :green, :pattern => 1
fail_format = Spreadsheet::Format.new :color=> :blue, :pattern_fg_color => :red, :pattern => 1
skip_format = Spreadsheet::Format.new :color=> :blue, :pattern_fg_color => :yellow, :pattern => 1

Trying to use them here(just showing one rest are decided by if elses):

sheet1.row(counter).default_format = skip_format
sheet1[counter, 3] = 'Skipped'
sheet1.row(counter).default_format = skip_format
sheet1.row(counter).set_format(3, skip_format)

Counter is the row I am currently in. Here I am not sure whether I should format first or write first. What am I doing wrong? How to fix this?

Actually it's getting applied as I found from .inspect-

#<Spreadsheet::Format:0x007f082f9c1d58 @font=#<Spreadsheet::Font:0x007f082f9c1a88 @name="Arial", @color=:red, @previous_fast_key=nil, @size=nil, @weight=nil, @italic=nil, @strikeout=nil, @outline=nil, @shadow=nil, @escapement=nil, @underline=nil, @family=:swiss, @encoding=nil>, @number_format="GENERAL", @rotation=0, @pattern=1, @bottom_color=:black, @top_color=:black, @left_color=:black, @right_color=:black, @diagonal_color=:black, @pattern_fg_color=:yellow, @pattern_bg_color=:pattern_bg, @regexes={:date=>/[YMD]/, :date_or_time=>/[hmsYMD]/, :datetime=>/([YMD].*[HS])|([HS].*[YMD])/, :time=>/[hms]/, :number=>/[#]/}, @used_merge=0>

but even if it shows color red here in excel it's black. :(

I am editing the original file then writing in a new file as

 book.write "Result.xls"

Is it the wrong approach? I am going to try to make a new workbook before editing and update.

Community
  • 1
  • 1
some_other_guy
  • 3,364
  • 4
  • 37
  • 55
  • I am reading from a file and writing to a new file. – some_other_guy Jul 07 '14 at 12:13
  • copy-paste what `sheet1` is? – okliv Jul 07 '14 at 14:46
  • @okliv: sheet1 is the current sheet in my workbook that I am working on. Something like - http://stackoverflow.com/questions/2103521/ruby-spreadshet-gem-how-can-i-center-align-a-number?rq=1 – some_other_guy Jul 08 '14 at 06:53
  • try to create new workbook with new sheet and apply styles to new rows not existing ones... if it works - then you have to dig in this direction (maybe it is acceptable to save what you want to new file not modifying existing one or maybe you just forgot to make workbook.write =) you are lazy(? :)) to paste more code and not specific enough to be able to receive more specific answer, imo) – okliv Jul 08 '14 at 08:12
  • @okliv: Adding more details in edited question! Had commented that I am writing to a new file.. but may be my method is wrong as I am editing the original fine then I am writing to a new file as mentioned in the question. – some_other_guy Jul 08 '14 at 08:24

1 Answers1

0

Well, it was not possible to format the existing excel then write it as a new Excel. Formatting was lost in that. To overcome I created a new excel (populated with my existing data read from the old excel) formatted it as I want then used

book.write "xxx.xls"

some_other_guy
  • 3,364
  • 4
  • 37
  • 55