Module: Workbook::Writers::XlsWriter

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

Instance Method Summary collapse

Instance Method Details

#format_to_xls_format(f) ⇒ Spreadsheet::Format

Generates an Spreadsheet (from the spreadsheet gem) in order to build an XlS



64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
# File 'lib/workbook/writers/xls_writer.rb', line 64

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 = parse_font_family(f) if f[:font_family]
    color = html_color_to_xls_color(f[:color])
    xlsfmt.font.color = color if color
    f.add_raw xlsfmt
  end
  return xlsfmt
end

#init_spreadsheet_templateObject



131
132
133
134
135
136
137
138
139
# File 'lib/workbook/writers/xls_writer.rb', line 131

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

#parse_font_family(format) ⇒ Object

Parses right font-family name



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 89

def parse_font_family(format)
  font = format[:font_family].to_s.split.last
  valid_values = [:none,:roman,:swiss,:modern,:script,:decorative]
  if valid_values.include?(font)
    return font
  elsif valid_values.include?(font.to_s.downcase.to_sym)
    return font.to_s.downcase.to_sym
  else
    font = font.to_s.downcase.strip
    translation = {
      "arial"=>:swiss,
      "times"=>:roman,
      "times new roman"=>:roman
    }
    tfont = translation[font]
    return tfont ? tfont : :none
  end
end

#to_xls(options = {}) ⇒ Spreadsheet

Generates an Spreadsheet (from the spreadsheet gem) in order to build an xls



12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
# File 'lib/workbook/writers/xls_writer.rb', line 12

def to_xls options={}
  book = init_spreadsheet_template
  self.each_with_index do |s,si|
    xls_sheet = xls_sheet(si)
    xls_sheet.name = s.name

    s.table.each_with_index do |r, ri|
      xls_sheet.row(ri).height= r.format[:height] if r.format
      r.each_with_index do |c, ci|
        if c
          if r.first?
            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
    (xls_sheet.last_row_index + 1 - s.table.count).times do |time|
      row_to_remove = s.table.count+time
      remove_row(xls_sheet,row_to_remove)
    end
    xls_sheet.updated_from(s.table.count)
    xls_sheet.dimensions

  end
  # kind of a hack, deleting by popping from xls worksheet results in errors in MS Excel (not LibreOffice)
  # book.worksheets.pop(book.worksheets.count - self.count) if book.worksheets and book.worksheets.count > self.count
  book.worksheets.each_with_index do |xls_sheet, si|
    if self[si]
      xls_sheet.visibility = :visible
    else
      xls_sheet.visibility = :strong_hidden
      #also make sure all data is removed, in case someone finds out about this 'trick'
      xls_sheet.name = "RemovedSheet#{si}"
      (xls_sheet.last_row_index + 1).times do |row_index|
        remove_row(xls_sheet,row_index)
      end
    end
  end
  # even after removing the worksheet's content... which solved some incompatibilities, but not for popping worksheets
  # book.worksheets.pop(book.worksheets.count - self.count) if book.worksheets and book.worksheets.count > self.count
  book
end

#write_to_xls(filename = "#{title}.xls", options = {}) ⇒ Object

Write the current workbook to Microsoft Excel format (using the spreadsheet gem)



112
113
114
115
116
# File 'lib/workbook/writers/xls_writer.rb', line 112

def write_to_xls filename="#{title}.xls", options={}
  if to_xls(options).write(filename)
    return filename
  end
end

#xls_sheet(a) ⇒ Object



118
119
120
121
122
123
124
125
# File 'lib/workbook/writers/xls_writer.rb', line 118

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

#xls_templateObject



127
128
129
# File 'lib/workbook/writers/xls_writer.rb', line 127

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