#!/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, cxn03651@msj.biglobe.ne.jp
#

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

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 column formats.';

# 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,
        :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