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

Parameters:

  • f (Workbook::Format, Hash) —

    A Workbook::Format or hash with format-options (:font_weight, :rotation, :background_color, :number_format, :text_direction, :color, :font_family)

Returns:

  • (Spreadsheet::Format) —

    A Spreadsheet format-object, ready for writing or more lower level operations



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

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_template ⇒ Object 



134
135
136
137
138
139
140
141
142
 
# File 'lib/workbook/writers/xls_writer.rb', line 134

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

Parameters:



92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
 
# File 'lib/workbook/writers/xls_writer.rb', line 92

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

Parameters:

  • options (Hash) (defaults to: {}) —

    A hash with options (unused so far)

Returns:

  • (Spreadsheet) —

    A Spreadsheet object, ready for writing or more lower level operations



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
57
58
59
 
# File 'lib/workbook/writers/xls_writer.rb', line 15

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)

Parameters:

  • filename (String) (defaults to: "#{title}.xls")
  • options (Hash) (defaults to: {}) —

    see #to_xls



115
116
117
118
119
 
# File 'lib/workbook/writers/xls_writer.rb', line 115

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

#xls_sheet(a) ⇒ Object 



121
122
123
124
125
126
127
128
 
# File 'lib/workbook/writers/xls_writer.rb', line 121

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_template ⇒ Object 



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

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