class AxlsxReport::Base

Public Class Methods

column(col_name, *args, &block) click to toggle source

Define report column.

Usage:

Human = Struct.new(:first_name, :last_name, :birthday)
class Report < AxlsxReport::Base
  # calculate column value with block:
  column 'First Name', width: 10 do |human|
    human.first_name
  end
  # or lambda in context of given object
  column 'Last Name', -> { last_name }, width: 15
  # or chagne context using with:
  column 'Age', -> { Date.today.year - year }, with: :birthday
end

@see Column#initialize for options description

@param [String] name - column header @param [Proc] callable - use proc or lambda instead of block to get cell value. Optional. @param [Hash] options - last param, @param block args: callable = nil, options = {}

# File lib/axlsx_report/base.rb, line 39
def self.column(col_name, *args, &block)
  @columns ||= []
  options =
    if args.last.is_a? Hash
      args.pop
    else
      {}
    end
  options[:group] = @current_group if @current_group
  callable = args.first || block
  @columns << Column.new(col_name, callable, options)
end
group(name, &block) click to toggle source

Define group of columns

Usage:

Human = Struct.new(:first_name, :last_name, :birthday)
class Report < AxlsxReport::Base
  group 'Name' do
    column 'First', &:first_name
    column 'Last',  &:last_name
  end
  column 'Birthday', &:birthday
end

@param [String] name group name

# File lib/axlsx_report/base.rb, line 65
def self.group(name, &block)
  @columns ||= []
  unless @groups
    @groups = []
    define_method :groups do
      self.class.instance_eval { @groups }
    end
  end
  raise 'Nested groups are not implemented yet' if @current_group
  @current_group = Group.new(name, @columns.length)
  @groups << @current_group
  instance_exec(&block)
  @current_group.end_index = @columns.length - 1
  @current_group = nil
end
group_height(num) click to toggle source

Define group row height. Used if any groups are defiend. Autoheight is used by default @param [Integer] num group row height

# File lib/axlsx_report/base.rb, line 84
def self.group_height(num)
  define_method(:group_height) { num }
end
head_height(num) click to toggle source

Define head row height. Autoheight is used by default @param [Integer] num head row height

# File lib/axlsx_report/base.rb, line 91
def self.head_height(num)
  define_method(:head_height) { num }
end
new(package = nil) click to toggle source

Creates new report. By default creates own Axlsx::Package. @param [Axlsx::Package] package - provide existing package to add report sheet to combined document.

# File lib/axlsx_report/base.rb, line 13
def initialize(package = nil)
  @package = package || Axlsx::Package.new
end
numerate_columns() click to toggle source

Add 1,2,3…N row below head row

# File lib/axlsx_report/base.rb, line 96
def self.numerate_columns
  define_method(:numerate_columns) { true }
end
sheet_name(name = nil, &block) click to toggle source

Define sheet name.

@param [String] name (nil) static sheet name. @param block dinamic sheet name (if given)

# File lib/axlsx_report/base.rb, line 104
def self.sheet_name(name = nil, &block)
  if block_given?
    define_method(:sheet_name, &block)
  else
    define_method(:sheet_name) { name }
  end
end

Public Instance Methods

<<(obj) click to toggle source

Add row to the report. @param [Any] obj object providing the data for columns

# File lib/axlsx_report/base.rb, line 114
def <<(obj)
  row = columns.map { |column| column.value(self, obj) }
  add_totals row
  sheet.add_row row, row_options
end
done() click to toggle source

Finalize document formatting after data collecting and before saving. @note Called during save. @note Don't call twice. Don't call before all data provided.

# File lib/axlsx_report/base.rb, line 163
def done
  apply_width(sheet)
  merge_same(sheet)
  sheet.add_row(@totals) if @totals
end
save(file) click to toggle source

Save xlsx file with provided data @param [String] file File name the data to be serialized

# File lib/axlsx_report/base.rb, line 149
def save(file)
  done
  begin
    @package.serialize(file)
  rescue Errno::EACCES => e
    puts "#{file} is protected!"
    file = file + ".tmp"
    @package.serialize(file)
  end
end
sheet() click to toggle source

Axlsx sheet. @return [Axlsx::Worksheet]

# File lib/axlsx_report/base.rb, line 171
def sheet
  @sheet ||= init_sheet(sheet_name)
end
sheet_name() click to toggle source

Returns sheet name.

May be overriden to provide custom sheet name @see Base.sheet_name

# File lib/axlsx_report/base.rb, line 124
def sheet_name
  'Sheet1'
end

Private Instance Methods

add_frozen_head(sheet, *header) click to toggle source
# File lib/axlsx_report/base.rb, line 329
def add_frozen_head(sheet, *header)
  row = add_head(sheet, *header)
  froze(sheet, sheet.rows.count)
  sheet.auto_filter = Axlsx::cell_range(row.cells, false)
end
add_group_head(sheet) click to toggle source
# File lib/axlsx_report/base.rb, line 247
def add_group_head(sheet)
  head_row = *columns.map{ |col| eval_name(col.name) }
  groups.each { |g| head_row[g.start_index] = g.name }
  add_head sheet, *head_row
  group_row = sheet.rows.length
  groups.map { |g| "#{column_num_to_name(g.start_index + 1)}#{group_row}:#{column_num_to_name(g.end_index + 1)}#{group_row}" }
    .each { |rng| sheet.merge_cells(rng) }
  sheet.rows.last.height = group_height if defined? group_height
  add_head sheet, *columns.map{ |col| eval_name(col.name) }
  sheet.rows.last.height = head_height if defined? head_height
  names_row = sheet.rows.length
  units = *columns.map(&:units)
  has_units = !units.all?(&:nil?)
  if has_units
    add_head sheet, *units
  end
  units_row = sheet.rows.length
  columns.map(&:options).each_with_index do |ops, index|
    row_name = column_num_to_name(index + 1)
    case
    when ops[:units] && ops[:group]
      nil
    when ops[:group]
      sheet.merge_cells("#{row_name}#{names_row}:#{row_name}#{units_row}") if has_units
    when ops[:units]
      sheet.merge_cells("#{row_name}#{group_row}:#{row_name}#{names_row}")
    else
      sheet.merge_cells("#{row_name}#{group_row}:#{row_name}#{units_row}")
    end
  end
end
add_head(sheet, *header) click to toggle source
# File lib/axlsx_report/base.rb, line 317
def add_head(sheet, *header)
  row = nil
  sheet.workbook.styles do |s|
    head_style = s.add_style :border => { :style => :thin, :color => "00" },
                             :alignment => { :horizontal => :center,
                                             :vertical => :center ,
                                             :wrap_text => true}
    row = sheet.add_row header, style: head_style
  end
  row
end
add_totals(row) click to toggle source
# File lib/axlsx_report/base.rb, line 279
def add_totals(row)
  return unless total_actions
  @totals ||= [nil] * columns.length
  total_actions.each_with_index do |action, index|
    case action
      when String
        @totals[index] = action
      when :sum
        if row[index]
          @totals[index] ||= 0.0
          @totals[index] += row[index]
        end
      when :count
        if row[index]
          @totals[index] ||= 0
          @totals[index] += 1
        end
      when Proc
        @totals[index] = action[row]
    end
  end
end
apply_width(sheet) click to toggle source
# File lib/axlsx_report/base.rb, line 212
def apply_width(sheet)
  sheet.column_widths *columns.map(&:width).map { |width| width || default_column_width }
end
auto_filter(sheet) click to toggle source
# File lib/axlsx_report/base.rb, line 208
def auto_filter(sheet)
  sheet.auto_filter = Axlsx::cell_range(sheet.rows.last.cells, false)
end
columns() click to toggle source
# File lib/axlsx_report/base.rb, line 177
def columns
  self.class.instance_eval { @columns }
end
default_column_width() click to toggle source
# File lib/axlsx_report/base.rb, line 243
def default_column_width
  16
end
eval_name(name) click to toggle source
# File lib/axlsx_report/base.rb, line 309
def eval_name(name)
  name.is_a?(Proc) ? instance_exec(&name) : name
end
froze(sheet, num_rows = 2) click to toggle source
# File lib/axlsx_report/base.rb, line 339
def froze(sheet, num_rows = 2)
  sheet.sheet_view do |vs|
    vs.pane do |pane|
      pane.state = :frozen
      pane.y_split = num_rows
    end
  end
end
init_sheet(name = 'Sheet1') click to toggle source
# File lib/axlsx_report/base.rb, line 181
def init_sheet(name = 'Sheet1')
  sheet = @package.workbook.add_worksheet(:name => sheetify_name(name))
  if self.respond_to? :title
    if title.is_a? Array
      title.each do |t|
        sheet.add_row [t]
      end
    else
      sheet.add_row [title]
    end
  end
  if defined? groups
    add_group_head sheet
  else
    add_head sheet, *columns.map{ |col| eval_name(col.name) }
    sheet.rows.last.height = head_height if defined? head_height
  end
  if defined? numerate_columns
    add_frozen_head sheet, *(1..(columns.length)).to_a
  else
    froze(sheet, sheet.rows.count)
    auto_filter(sheet)
  end
  @header_rows = sheet.rows.count
  sheet
end
merge_same(sheet) click to toggle source
# File lib/axlsx_report/base.rb, line 216
def merge_same(sheet)
  columns.map(&:options).each_with_index do |ops, col_num|
    next unless ops[:merge_same]
    merged_style ||= sheet.styles.add_style(:alignment => { :vertical => :center })
    col_char = column_num_to_name(col_num + 1)
    last, start_row = nil, 0
    sheet.rows.dup.each_with_index do |row, row_num|
      next if row_num < @header_rows
      col_value = row.cells[col_num].value
      if last && last != col_value && (row_num - start_row) > 1
        range = "#{col_char}#{start_row + 1}:#{col_char}#{row_num}"
        sheet.merge_cells(range)
        sheet[range].each { |c| c.style = merged_style }
      end
      if last != col_value
        last = col_value
        start_row = row_num
      end
    end
    if last && (sheet.rows.length - start_row) > 1
      range = "#{col_char}#{start_row + 1}:#{col_char}#{sheet.rows.length}"
      sheet.merge_cells(range)
      sheet[range].each { |c| c.style = merged_style }
    end
  end
end
row_options() click to toggle source
# File lib/axlsx_report/base.rb, line 348
def row_options
  options = {}
  types = columns.map { |c| c.options[:type] }
  options[:types] = types unless types.all?(&:nil?)
  options
end
separate_sheet(sheet, sheet_name = 'Sheet2') { |sheet| ... } click to toggle source
# File lib/axlsx_report/base.rb, line 335
def separate_sheet(sheet, sheet_name = 'Sheet2', &block)
  sheet.workbook.add_worksheet(name: sheet_name) { |sheet| yield sheet }
end
sheetify_name(name) click to toggle source
# File lib/axlsx_report/base.rb, line 313
def sheetify_name(name)
  name.gsub('/', '_')[0..30]
end
total_actions() click to toggle source
# File lib/axlsx_report/base.rb, line 302
def total_actions
  @total_actions ||=
    columns.map(&:options).map do |ops|
      ops[:total]
    end
end