Module: Workbook::Writers::XlsWriter

Included in:
Book
Defined in:
lib/workbook/writers/xls_writer.rb

Constant Summary

XLS_COLORS =

TODO: find better way to dupe

{:xls_color_1=>'#000000',
:xls_color_2=>'#FFFFFF',
:xls_color_3=>'#FF0000',
:xls_color_4=>'#00FF00',
:xls_color_5=>'#0000FF',
:xls_color_6=>'#FFFF00',
:xls_color_7=>'#FF00FF',
:xls_color_8=>'#00FFFF',
:xls_color_9=>'#800000',
:xls_color_10=>'#008000',
:xls_color_11=>'#000080',
:xls_color_12=>'#808000',
:xls_color_13=>'#800080',
:xls_color_14=>'#008080',
:xls_color_15=>'#C0C0C0',
:xls_color_16=>'#808080',
:xls_color_17=>'#9999FF',
:xls_color_18=>'#993366',
:xls_color_19=>'#FFFFCC',
:xls_color_20=>'#CCFFFF',
:xls_color_21=>'#660066',
:xls_color_22=>'#FF8080',
:xls_color_23=>'#0066CC',
:xls_color_24=>'#CCCCFF',
:xls_color_25=>'#000080',
:xls_color_26=>'#FF00FF',
:xls_color_27=>'#FFFF00',
:xls_color_28=>'#00FFFF',
:xls_color_29=>'#800080',
:xls_color_30=>'#800000',
:xls_color_31=>'#008080',
:xls_color_32=>'#0000FF',
:xls_color_33=>'#00CCFF',
:xls_color_34=>'#CCFFFF',
:xls_color_35=>'#CCFFCC',
:xls_color_36=>'#FFFF99',
:xls_color_37=>'#99CCFF',
:xls_color_38=>'#FF99CC',
:xls_color_39=>'#CC99FF',
:xls_color_40=>'#FFCC99',
:xls_color_41=>'#3366FF',
:xls_color_42=>'#33CCCC',
:xls_color_43=>'#99CC00',
:xls_color_44=>'#FFCC00',
:xls_color_45=>'#FF9900',
:xls_color_46=>'#FF6600',
:xls_color_47=>'#666699',
:xls_color_48=>'#969696',
:xls_color_49=>'#003366',
:xls_color_50=>'#339966',
:xls_color_51=>'#003300',
:xls_color_52=>'#333300',
:xls_color_53=>'#993300',
:xls_color_54=>'#993366',
:xls_color_55=>'#333399',
:xls_color_56=>'#333333',
:black=>'#000000',
:white=>'#FFFFFF',
:red=>'#FF0000',
:green=>'#00FF00',
:blue=>'#0000FF',
:yellow=>'#FFFF00',
:magenta=>'#FF00FF',
:cyan=>'#00FFFF',
:border=>'#FFFFFF',
:text=>'#000000',
:lime=>'#00f94c'}

Instance Method Summary (collapse)

Instance Method Details

- (Object) format_to_xls_format(f)



108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
# File 'lib/workbook/writers/xls_writer.rb', line 108

def format_to_xls_format f
  xlsfmt = nil
  unless f.is_a? Workbook::Format
    f = Workbook::Format.new f
  end
  xlsfmt = f.return_raw_for Spreadsheet::Format
  unless xlsfmt
    xlsfmt=Spreadsheet::Format.new :weight=>f[:font_weight]
    xlsfmt.rotation = f[:rotation] if f[:rotation] 
    xlsfmt.pattern_fg_color = html_color_to_xls_color(f[:background_color]) if html_color_to_xls_color(f[:background_color])
    xlsfmt.pattern = 1 if html_color_to_xls_color(f[:background_color])
    xlsfmt.number_format = strftime_to_ms_format(f[:number_format]) if f[:number_format]
    xlsfmt.text_direction = f[:text_direction] if f[:text_direction]
    xlsfmt.font.name = f[:font_family].split.first if f[:font_family]
    xlsfmt.font.family = f[:font_family].split.last if f[:font_family]
    xlsfmt.font.color = html_color_to_xls_color(f[:color]) if f[:color]
    f.add_raw xlsfmt
  end
  return xlsfmt
end

- (Object) html_color_to_xls_color(hex)



129
130
131
132
133
134
# File 'lib/workbook/writers/xls_writer.rb', line 129

def html_color_to_xls_color hex
  XLS_COLORS.each do |k,v|
    return k if (v == hex or (hex and hex != "" and k == hex.to_sym))
  end
  return nil
end

- (Object) init_spreadsheet_template



161
162
163
164
165
166
167
168
169
# File 'lib/workbook/writers/xls_writer.rb', line 161

def init_spreadsheet_template
  if self.xls_template.is_a? Spreadsheet::Workbook
    return self.xls_template
  else
    t = Spreadsheet::Workbook.new
    template.add_raw t
    return t
  end
end

- (Object) strftime_to_ms_format(numberformat)



136
137
138
139
# File 'lib/workbook/writers/xls_writer.rb', line 136

def strftime_to_ms_format numberformat
  return nil if numberformat.nil?
  numberformat.gsub('%Y','yyyy').gsub('%A','dddd').gsub('%B','mmmm').gsub('%a','ddd').gsub('%b','mmm').gsub('%y','yy').gsub('%d','dd').gsub('%m','mm').gsub('%y','y').gsub('%y','%%y').gsub('%e','d')
end

- (Object) to_xls(options = {})



75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
# File 'lib/workbook/writers/xls_writer.rb', line 75

def to_xls options={}
  options = {:rewrite_header=>default_rewrite_header?}.merge options
  book = init_spreadsheet_template
  self.each_with_index do |s,si|
    xls_sheet = book.worksheet si
    xls_sheet = book.create_worksheet if xls_sheet == nil
    s.table.each_with_index do |r, ri|
      write_row = false
      if r.header?
        if options[:rewrite_header] == true
          write_row = true
        end
      else
        write_row = true
      end
      if write_row
        xls_sheet.row(ri).height= r.format[:height] if r.format
        r.each_with_index do |c, ci|
          if c
            if r.header?
              xls_sheet.columns[ci] ||= Spreadsheet::Column.new(ci,nil)
              xls_sheet.columns[ci].width= c.format[:width]
            end
            xls_sheet.row(ri)[ci] = c.value
            xls_sheet.row(ri).set_format(ci, format_to_xls_format(c.format))
          end
        end
      end
    end
  end
  book
end

- (Object) write_to_xls(options = {})



141
142
143
144
145
146
# File 'lib/workbook/writers/xls_writer.rb', line 141

def write_to_xls options={}
  filename = options[:filename] ? options[:filename] : "#{title}.xls"
  if to_xls(options).write(filename)
    return filename
  end
end

- (Object) xls_sheet(a)



148
149
150
151
152
153
154
155
# File 'lib/workbook/writers/xls_writer.rb', line 148

def xls_sheet a
  if xls_template.worksheet(a)
    return xls_template.worksheet(a)
  else
    xls_template.create_worksheet
    self.xls_sheet a
  end
end

- (Object) xls_template



157
158
159
# File 'lib/workbook/writers/xls_writer.rb', line 157

def xls_template
  return template.raws[Spreadsheet::Excel::Workbook] ? template.raws[Spreadsheet::Excel::Workbook] : template.raws[Spreadsheet::Workbook]
end