class TableTransform::ExcelCreator

Creates excel file

Public Class Methods

new(filename) click to toggle source
# File lib/table_transform/excel_creator.rb, line 9
def initialize(filename)
  @workbook = WriteXLSX.new(filename)
  @formats = {}
end

Private Class Methods

column_width(table, auto_filter_correct = true, max_width = 100) click to toggle source

@return array with column width per column

# File lib/table_transform/excel_creator.rb, line 43
def self.column_width(table, auto_filter_correct = true, max_width = 100)
  return [] unless table.is_a? TableTransform::Table
  data = table.to_a

  auto_filter_size_correction = auto_filter_correct ? 3 : 0
  res = Array.new(data.first.map { |name|
    [name.to_s.size + auto_filter_size_correction, format_column_size(table.column_properties[name][:format])].max
  })
  data.each { |row|
    row.each_with_index { |cell, column_no|
      res[column_no] = [cell.to_s.size, res[column_no]].max
    }
  }
  res.map! { |x| [x, max_width].min }
end
default_properties(table_name) click to toggle source
# File lib/table_transform/excel_creator.rb, line 26
def self.default_properties(table_name)
  TableTransform::Table::TableProperties.new(
      {
          name: table_name.tr(' ', '_'),
          auto_filter: true
      }
  )
end
format_column_size(format) click to toggle source

estimated column width of format

# File lib/table_transform/excel_creator.rb, line 36
def self.format_column_size(format)
  return 0 if format.nil?
  f = format.gsub(/\[.*?\]/, '').split(';')
  f.map{|x| x.size}.max
end

Public Instance Methods

add_tab(name, table) click to toggle source
# File lib/table_transform/excel_creator.rb, line 18
def add_tab(name, table)
  set_formats(table.column_properties) if table.is_a?(TableTransform::Table)
  create_table(name, table)
end
create!() click to toggle source
# File lib/table_transform/excel_creator.rb, line 14
def create!
  @workbook.close
end

Private Instance Methods

create_column_metadata(column_properties, formats, formulas) click to toggle source
# File lib/table_transform/excel_creator.rb, line 67
def create_column_metadata(column_properties, formats, formulas)
  res = []
  column_properties.each{ |header_name, data|
    col_props = TableTransform::Properties.new data.to_h

    #format (replace str format with excel representation)
    col_props.update({format: formats[col_props[:format]]}) unless col_props[:format].nil?

    #formula
    formula = formulas[header_name]
    col_props.update({formula: formula}) unless formula.nil?

    #header
    col_props.update({header: header_name})
    res << col_props.to_h
  }
  res
end
create_table(name, table) click to toggle source
# File lib/table_transform/excel_creator.rb, line 86
def create_table(name, table)
  worksheet = @workbook.add_worksheet(name)
  data = table.to_a
  return if data.nil? or data.empty? # Create empty worksheet if no data

  table_properties = ExcelCreator::default_properties(name).update(table.table_properties.to_h)
  col_width = ExcelCreator::column_width(table, table_properties[:auto_filter])

  header = data.shift
  data << [nil] * header.count if data.empty? # Add extra row if empty

  worksheet.add_table(
      0, 0, data.count, header.count - 1,
      {
          :name => table_properties[:name],
          :data => data,
          :autofilter => table_properties[:auto_filter] ? 1 : 0,
          :columns => create_column_metadata(table.column_properties, @formats, table.formulas)
      }
  )

  # Set column width
  col_width.each_with_index { |size, column| worksheet.set_column(column, column, size) }
end
set_formats(column_properties) click to toggle source
# File lib/table_transform/excel_creator.rb, line 59
def set_formats(column_properties)
  # find all :formats across all columns
  column_properties.each{|_,v|
    f = v[:format]
    @formats[f] ||= @workbook.add_format(:num_format => f) unless f.nil?
  }
end