Module: WorkerTools::XlsxOutput

Defined in:
lib/worker_tools/xlsx_output.rb

Overview

rubocop:disable Metrics/ModuleLength

Instance Method Summary collapse

Instance Method Details

#xlsx_output_add_attachment ⇒ Object 



139
140
141
142
143
144
145
 
# File 'lib/worker_tools/xlsx_output.rb', line 139

def xlsx_output_add_attachment
  model.add_attachment(
    xlsx_output_tmp_file,
    file_name: xlsx_output_file_name,
    content_type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
  )
end

#xlsx_output_column_format ⇒ Object 



41
42
43
44
45
46
47
48
49
50
51
52
53
 
# File 'lib/worker_tools/xlsx_output.rb', line 41

def xlsx_output_column_format
  # These columns are used to set the headers, also
  # to set the row values depending on your implementation.
  #
  # To ignore them set it to _false_
  #
  # Ex:
  # @xlsx_output_column_format ||= {
  #   foo: { width: 10, text_wrap: true },
  #   bar: { width: 20, text_wrap: false }
  # }
  {}
end

#xlsx_output_column_headers ⇒ Object 



11
12
13
14
15
16
17
18
19
20
21
22
23
 
# File 'lib/worker_tools/xlsx_output.rb', line 11

def xlsx_output_column_headers
  # These columns are used to set the headers, also
  # to set the row values depending on your implementation.
  #
  # To ignore them set it to _false_
  #
  # Ex:
  # @xlsx_output_column_headers ||= {
  #   foo: 'Foo Header',
  #   bar: 'Bar Header'
  # }
  raise "xlsx_output_column_headers has to be defined in #{self}"
end

#xlsx_output_content ⇒ Object 



25
26
27
28
29
30
31
32
33
34
35
 
# File 'lib/worker_tools/xlsx_output.rb', line 25

def xlsx_output_content
  {
    sheet1: {
      label: 'Sheet 1',
      headers: xlsx_output_column_headers,
      rows: xlsx_output_entries.lazy.map { |entry| xlsx_output_row_values(entry) },
      column_style: xlsx_output_column_format,
      number_format: xlsx_output_number_format
    }
  }
end

#xlsx_output_entries ⇒ Object 



7
8
9
 
# File 'lib/worker_tools/xlsx_output.rb', line 7

def xlsx_output_entries
  raise "xlsx_output_entries has to be defined in #{self}"
end

#xlsx_output_file_name ⇒ Object 



135
136
137
 
# File 'lib/worker_tools/xlsx_output.rb', line 135

def xlsx_output_file_name
  "#{model_kind}.xlsx"
end

#xlsx_output_insert_headers(spreadsheet, headers) ⇒ Object 



70
71
72
73
74
75
76
77
78
79
80
81
82
 
# File 'lib/worker_tools/xlsx_output.rb', line 70

def xlsx_output_insert_headers(spreadsheet, headers)
  return unless headers

  iterator =
    if headers.is_a? Hash
      headers.values
    else
      headers
    end
  iterator.each_with_index do |header, index|
    spreadsheet.add_cell(0, index, header.to_s)
  end
end

#xlsx_output_insert_rows(spreadsheet, rows, headers, number_formats) ⇒ Object

rubocop:disable Metrics/AbcSize rubocop:disable Metrics/MethodLength



86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
 
# File 'lib/worker_tools/xlsx_output.rb', line 86

def xlsx_output_insert_rows(spreadsheet, rows, headers, number_formats)
  number_format_by_col_index = Hash(number_formats).each_with_object({}) do |(col, number_format), hash|
    hash[headers.keys.index(col)] = number_format
  end

  rows.each_with_index do |row, row_index|
    xlsx_output_iterators(row, headers).each_with_index do |value, col_index|
      number_format = number_format_by_col_index[col_index]

      case number_format
      when :auto, 'auto'
        spreadsheet.add_cell(row_index + 1, col_index, value)
      when nil
        spreadsheet.add_cell(row_index + 1, col_index, value.to_s)
      else
        spreadsheet.add_cell(row_index + 1, col_index, value).set_number_format(number_format)
      end
    end
  end
end

#xlsx_output_iterators(iterable, compare_hash = nil) ⇒ Object

rubocop:enable Metrics/AbcSize rubocop:enable Metrics/MethodLength



109
110
111
112
113
114
115
116
117
 
# File 'lib/worker_tools/xlsx_output.rb', line 109

def xlsx_output_iterators(iterable, compare_hash = nil)
  if iterable.is_a? Hash
    raise 'parameter compare_hash should be a hash, too.' if compare_hash.nil? || !compare_hash.is_a?(Hash)

    iterable.values_at(*compare_hash.keys)
  else
    iterable
  end
end

#xlsx_output_number_format ⇒ Object 



55
56
57
58
59
60
61
62
63
64
65
66
67
68
 
# File 'lib/worker_tools/xlsx_output.rb', line 55

def xlsx_output_number_format
  # set the cell number format for a given column
  # number format also applies to dates, see the excel documentation:
  #  https://support.microsoft.com/en-us/office/number-format-codes-in-excel-for-mac-5026bbd6-04bc-48cd-bf33-80f18b4eae68
  #  https://www.rubydoc.info/gems/rubyXL/3.3.21/RubyXL/NumberFormats
  #
  # Ex:
  # @xlsx_output_number_format ||= {
  #   foo: :auto,
  #   bar: '0.00',
  #   # nothing for baz, it will go through `.to_s`
  # }
  {}
end

#xlsx_output_row_values(entry) ⇒ Object 



37
38
39
 
# File 'lib/worker_tools/xlsx_output.rb', line 37

def xlsx_output_row_values(entry)
  entry.values_at(*xlsx_output_column_headers.keys)
end

#xlsx_output_style_columns(spreadsheet, styles, headers) ⇒ Object 



119
120
121
122
123
124
125
126
127
128
129
 
# File 'lib/worker_tools/xlsx_output.rb', line 119

def xlsx_output_style_columns(spreadsheet, styles, headers)
  return false unless headers

  xlsx_output_iterators(styles, headers).each_with_index do |format, index|
    next unless format

    spreadsheet.change_column_width(index, format[:width])
    spreadsheet.change_text_wrap(index, format[:text_wrap])
  end
  true
end

#xlsx_output_tmp_file ⇒ Object 



131
132
133
 
# File 'lib/worker_tools/xlsx_output.rb', line 131

def xlsx_output_tmp_file
  @xlsx_output_tmp_file ||= Tempfile.new(['output', '.xlsx'])
end

#xlsx_output_write_file ⇒ Object

rubocop:enable Metrics/AbcSize



159
160
161
162
163
164
165
166
167
168
 
# File 'lib/worker_tools/xlsx_output.rb', line 159

def xlsx_output_write_file
  book = RubyXL::Workbook.new
  xlsx_output_content.each_with_index do |(_, object), index|
    xlsx_output_write_sheet(book, object, index)
  end

  book.write xlsx_output_tmp_file

  xlsx_output_add_attachment
end

#xlsx_output_write_sheet(workbook, sheet_content, index) ⇒ Object

rubocop:disable Metrics/AbcSize



148
149
150
151
152
153
154
155
156
 
# File 'lib/worker_tools/xlsx_output.rb', line 148

def xlsx_output_write_sheet(workbook, sheet_content, index)
  sheet = workbook.worksheets[index]
  sheet = workbook.add_worksheet(sheet_content[:label]) if sheet.nil?

  sheet.sheet_name = sheet_content[:label]
  xlsx_output_style_columns(sheet, sheet_content[:column_style], sheet_content[:headers])
  xlsx_output_insert_headers(sheet, sheet_content[:headers])
  xlsx_output_insert_rows(sheet, sheet_content[:rows], sheet_content[:headers], sheet_content[:number_format])
end