#!/usr/bin/env ruby
# -*- coding: utf-8 -*-

#######################################################################
#
# Example of how to add tables to an WriteXLSX worksheet.
#
# Tables in Excel are use to group rows and columns of data into a single
# structure that can be references in a formula or formatted collectively.
#
# reverse(c), March 2001, John McNamara, jmcnamara@cpan.org
# convert to ruby by Hideo NAKAMURA, nakamura.hideo@gmail.com
#

require 'write_xlsx'

# Create a new workbook called simple.xls and add a worksheet
workbook    = WriteXLSX.new('tables.xlsx')
worksheet1  = workbook.add_worksheet
worksheet2  = workbook.add_worksheet
worksheet3  = workbook.add_worksheet
worksheet4  = workbook.add_worksheet
worksheet5  = workbook.add_worksheet
worksheet6  = workbook.add_worksheet
worksheet7  = workbook.add_worksheet
worksheet8  = workbook.add_worksheet
worksheet9  = workbook.add_worksheet
worksheet10 = workbook.add_worksheet
worksheet11 = workbook.add_worksheet
worksheet12 = workbook.add_worksheet
worksheet13 = workbook.add_worksheet

currency_format = workbook.add_format(num_format: '$#,##0')

# Some sample data for the table.
data = [
  ['Apples',  10000, 5000, 8000, 6000],
  ['Pears',   2000,  3000, 4000, 5000],
  ['Bananas', 6000,  6000, 6500, 6000],
  ['Oranges', 500,   300,  200,  700]
]

###############################################################################
#
# Example 1.
#
caption = 'Default table with no data.'

# Set the columns widths.
worksheet1.set_column('B:G', 12)

# Write the caption.
worksheet1.write('B1', caption)

# Add a table to the worksheet.
worksheet1.add_table('B3:F7')

###############################################################################
#
# Example 2.
#
caption = 'Default table with data.'

# Set the columns widths.
worksheet2.set_column('B:G', 12)

# Write the caption.
worksheet2.write('B1', caption)

# Add a table to the worksheet.
worksheet2.add_table('B3:F7', { data: data })

###############################################################################
#
# Example 3.
#
caption = 'Table without default autofilter.'

# Set the columns widths.
worksheet3.set_column('B:G', 12)

# Write the caption.
worksheet3.write('B1', caption)

# Add a table to the worksheet.
worksheet3.add_table('B3:F7', { autofilter: 0 })

# Table data can also be written separately, as an array or individual cells.
worksheet3.write_col('B4', data)

###############################################################################
#
# Example 4.
#
caption = 'Table without default header row.'

# Set the columns widths.
worksheet4.set_column('B:G', 12)

# Write the caption.
worksheet4.write('B1', caption)

# Add a table to the worksheet.
worksheet4.add_table('B4:F7', { header_row: 0 })

# Table data can also be written separately, as an array or individual cells.
worksheet4.write_col('B4', data)

###############################################################################
#
# Example 5.
#
caption = 'Default table with "First Column" and "Last Column" options.'

# Set the columns widths.
worksheet5.set_column('B:G', 12)

# Write the caption.
worksheet5.write('B1', caption)

# Add a table to the worksheet.
worksheet5.add_table('B3:F7', { first_column: 1, last_column: 1 })

# Table data can also be written separately, as an array or individual cells.
worksheet5.write_col('B4', data)

###############################################################################
#
# Example 6.
#
caption = 'Table with banded columns but without default banded rows.'

# Set the columns widths.
worksheet6.set_column('B:G', 12)

# Write the caption.
worksheet6.write('B1', caption)

# Add a table to the worksheet.
worksheet6.add_table('B3:F7', { banded_rows: 0, banded_columns: 1 })

# Table data can also be written separately, as an array or individual cells.
worksheet6.write_col('B4', data)

###############################################################################
#
# Example 7.
#
caption = 'Table with user defined column headers'

# Set the columns widths.
worksheet7.set_column('B:G', 12)

# Write the caption.
worksheet7.write('B1', caption)

# Add a table to the worksheet.
worksheet7.add_table(
  'B3:F7',
  {
    data:    data,
    columns: [
      { header: 'Product' },
      { header: 'Quarter 1' },
      { header: 'Quarter 2' },
      { header: 'Quarter 3' },
      { header: 'Quarter 4' }
    ]
  }
)

###############################################################################
#
# Example 8.
#
caption = 'Table with user defined column headers'

# Set the columns widths.
worksheet8.set_column('B:G', 12)

# Write the caption.
worksheet8.write('B1', caption)

# Add a table to the worksheet.
worksheet8.add_table(
  'B3:G7',
  {
    data:    data,
    columns: [
      { header: 'Product' },
      { header: 'Quarter 1' },
      { header: 'Quarter 2' },
      { header: 'Quarter 3' },
      { header: 'Quarter 4' },
      {
        header:  'Year',
        formula: '=SUM(Table8[@[Quarter 1]:[Quarter 4]])'
      }
    ]
  }
)

###############################################################################
#
# Example 9.
#
caption = 'Table with totals row (but no caption or totals).'

# Set the columns widths.
worksheet9.set_column('B:G', 12)

# Write the caption.
worksheet9.write('B1', caption)

# Add a table to the worksheet.
worksheet9.add_table(
  'B3:G8',
  {
    data:      data,
    total_row: 1,
    columns:   [
      { header: 'Product' },
      { header: 'Quarter 1' },
      { header: 'Quarter 2' },
      { header: 'Quarter 3' },
      { header: 'Quarter 4' },
      {
        header:  'Year',
        formula: '=SUM(Table8[@[Quarter 1]:[Quarter 4]])'
      }
    ]
  }
)

###############################################################################
#
# Example 10.
#
caption = 'Table with totals row with user captions and functions.'

# Set the columns widths.
worksheet10.set_column('B:G', 12)

# Write the caption.
worksheet10.write('B1', caption)

# Add a table to the worksheet.
worksheet10.add_table(
  'B3:G8',
  {
    data:      data,
    total_row: 1,
    columns:   [
      { header: 'Product',   total_string: 'Totals' },
      { header: 'Quarter 1', total_function: 'sum' },
      { header: 'Quarter 2', total_function: 'sum' },
      { header: 'Quarter 3', total_function: 'sum' },
      { header: 'Quarter 4', total_function: 'sum' },
      {
        header:         'Year',
        formula:        '=SUM(Table8[@[Quarter 1]:[Quarter 4]])',
        total_function: 'sum'
      }
    ]
  }
)

###############################################################################
#
# Example 11.
#
caption = 'Table with alternative Excel style.'

# Set the columns widths.
worksheet11.set_column('B:G', 12)

# Write the caption.
worksheet11.write('B1', caption)

# Add a table to the worksheet.
worksheet11.add_table(
  'B3:G8',
  {
    data:      data,
    style:     'Table Style Light 11',
    total_row: 1,
    columns:   [
      { header: 'Product',   total_string: 'Totals' },
      { header: 'Quarter 1', total_function: 'sum' },
      { header: 'Quarter 2', total_function: 'sum' },
      { header: 'Quarter 3', total_function: 'sum' },
      { header: 'Quarter 4', total_function: 'sum' },
      {
        header:         'Year',
        formula:        '=SUM(Table8[@[Quarter 1]:[Quarter 4]])',
        total_function: 'sum'
      }
    ]
  }
)

###############################################################################
#
# Example 12.
#
caption = 'Table with no Excel style.'

# Set the columns widths.
worksheet12.set_column('B:G', 12)

# Write the caption.
worksheet12.write('B1', caption)

# Add a table to the worksheet.
worksheet12.add_table(
  'B3:G8',
  {
    data:      data,
    style:     'None',
    total_row: 1,
    columns:   [
      { header: 'Product',   total_string: 'Totals' },
      { header: 'Quarter 1', total_function: 'sum' },
      { header: 'Quarter 2', total_function: 'sum' },
      { header: 'Quarter 3', total_function: 'sum' },
      { header: 'Quarter 4', total_function: 'sum' },
      {
        header:         'Year',
        formula:        '=SUM(Table12[@[Quarter 1]:[Quarter 4]])',
        total_function: 'sum'
      }
    ]
  }
)

###############################################################################
#
# Example 13.
#
caption = 'Table with column formats.'

# Set the columns widths.
worksheet13.set_column('B:G', 12)

# Write the caption.
worksheet13.write('B1', caption)

# Add a table to the worksheet.
worksheet13.add_table(
  'B3:G8',
  {
    data:      data,
    total_row: 1,
    columns:   [
      { header: 'Product', total_string: 'Totals' },
      {
        header:         'Quarter 1',
        total_function: 'sum',
        format:         currency_format
      },
      {
        header:         'Quarter 2',
        total_function: 'sum',
        format:         currency_format
      },
      {
        header:         'Quarter 3',
        total_function: 'sum',
        format:         currency_format
      },
      {
        header:         'Quarter 4',
        total_function: 'sum',
        format:         currency_format
      },
      {
        header:         'Year',
        formula:        '=SUM(Table8[@[Quarter 1]:[Quarter 4]])',
        total_function: 'sum',
        format:         currency_format
      }
    ]
  }
)

workbook.close