TableTransform

gem 'table_transform'

$ bundle

$ gem install table_transform

# Create from file
t = TableTransform::Table::create_from_file('data.csv')

# Create empty table with given column names 
t = TableTransform::Table::create_empty(%w(Col1 Col2))
t << {'Col1' => 1, 'Col2' => 2}

# Create from an array of arrays. First row column names
data = [ %w(Name Age), %w(Jane 22)]
t = TableTransform::Table.new(data)

# Add a new column to the far right
t.add_column('NameLength'){|row| row['Name'].size}

# Change values in existing column
t.change_column('Age'){|row| row['Age'].to_i}

# Will remove given columns. One or several can be specified.
t.delete_column('Name', 'Address')

# Rename column
r.rename_column('Age', 'Years')

# Create a new Table with given column in specified order
t2 = t.extract(%w(Length Name))

# Filter to a new table 
t2 = t.filter{|row| row['Age'].to_i > 20}

# Adds rows of two tables with same header
t1 = TableTransform::Table::create_empty(%w(Col1 Col2))
t2 = TableTransform::Table::create_empty(%w(Col1 Col2))
t3 = t1 + t2

# Loop through all rows
t.each_row{|row| puts row['Rebate'].to_f * row['Price'].to_f}

# Table::Cell < String
row['operating_system'].downcase == 'centos'

# include_any?
c = row['Col1']                # c = 'CHECK'
c.include_any?(%w(AA EC DD)))  # true

# Update - add or update :name property in existing p
p.update({name: 'xxx'})

# Reset - resets p with given property, clearing previous properties 
p.reset({name: 'xxx'})

# Delete
p.delete(:name)

# Each
p.each{|x| ...}

# Access
p[:name]

# Set table name - override default name
t.table_properties.update({name: 'Table1'})

# Set turn on/off auto filter button
t.table_properties.update({auto_filter: false})

# Set format for one column
t.column_properties['Tax'].update({format: '0.0%'})

# Extract properties
t.column_properties['Tax'] # {format: '0.0%'}

# Add property during add_column
t.add_column('Tax', {format: '0.0%'}){|row| 0.25}

# Add formula to column
t.add_column_formula('OnePlusOne', '1+1')

# Add formula to column with meta data
t.add_column_formula('OnePlusOne', '1+1', {format: '0.0'})

f = TableTransform::FormulaHelper # Namespace alias

# Column
f::column('price') # <=> '[price]'
t.add_column_formula('Total value', "#{f::column('price')} * #{f::column('volume')}")

# Table
f::table('Table1') # <=> 'Table1[]'

# Text (will avoid the clutter of protecting " character)
f::text('No') # <=> '"No"'

# VLOOKUP, convenience function to extract values from another table
# Finds the street name in table Address for name specified in column Name
f::vlookup(f::column('Name'), 'Address', 'Street'))
f::vlookup('[Name]', 'Address', 'Street'))

# Finds the number of pets in Table Pets for name specified in column Name. If name does not exist it defaults to 0
f::vlookup(f::column('Name'), 'Pets', 'nPets', 0))
f::vlookup('[Name]', 'Pets', 'nPets', 0))

# Export as array
t.to_a

# Excel sheet
excel = TableTransform::ExcelCreator.new('output.xlsx')
excel.add_tab('Result', t)
excel.create!