Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Output a spreadsheet to act as a reference for various color symbols that can be used in formatting cells with the ruby spreadsheet GEM.
require 'rubygems'
require 'bundler/setup'
Bundler.require
def mark_column_color(sheet, row, col)
symbol_name = sheet[row,col].slice(1..100)
color = :white
color = :black if (col == 1 && [1,2,9].include?(row)) || (col == 2 && [5,7,9].include?(row) || (col == 3 && [1,2,5,6,7,9,10,11].include?(row)))
color_column_fmt = Spreadsheet::Format.new :pattern => 1, :pattern_fg_color => symbol_name.to_sym, :vertical_align => :middle, :size => 14, :align => :center, :color => color
sheet.row(row).set_format(col, color_column_fmt)
end
book = Spreadsheet::Workbook.new
header_format = Spreadsheet::Format.new :vertical_align => :middle, :size => 16, :color => :green, :weight => :bold, :align => :center
sheet = book.create_worksheet :name => 'Colors'
columns = (0..3).to_a
row = 0
sheet.row(row).concat ['Symbol', 'Symbol', 'Symbol', 'Symbol']
sheet.row(row).default_format = header_format
row = 1
color_offset = 0
while row <= 14
color_row = []
columns.each{|col| color_row << ":xls_color_#{color_offset + col}"}
sheet.row(row).concat color_row
columns.each {|col| mark_column_color(sheet, row, col)}
sheet.row(row).height = 30
color_offset = color_offset + columns.size
row = row + 1
end
columns.each{|col| sheet.column(col).width = 25 }
book.write 'colors.xls'
@brampey

This comment has been minimized.

Copy link
Owner Author

@brampey brampey commented Nov 8, 2013

To use. Create a Gemfile that contains:

source 'https://rubygems.org'
gem 'spreadsheet', '~> 0.9.1'

Then pull the source file above into the same folder as the Gemfile and:

bundle install
bundle exec ruby color_ref_spreadsheet.rb

This will output a colors.xls file that can be used as a reference for the various
color symbols to use with the ruby spreadsheet gem.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.