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

#######################################################################
#
# A demo of an various Excel chart data tools that are available via
# an WriteXLSX chart.
#
# These include, Trendlines, Data Labels, Error Bars, Drop Lines,
# High-Low Lines and Up-Down Bars.
#
# reverse ('(c)'), December 2012, 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('chart_data_tools.xlsx')
worksheet = workbook.add_worksheet
bold      = workbook.add_format(bold: 1)

# Add the worksheet data that the charts will refer to.
headings = ['Number', 'Data 1', 'Data 2']
data = [
  [2,  3,  4,  5,  6,  7],
  [10, 40, 50, 20, 10, 50],
  [30, 60, 70, 50, 40, 30]

]

worksheet.write('A1', headings, bold)
worksheet.write('A2', data)

#######################################################################
#
# Trendline example.
#

# Create a Line chart.
chart1 = workbook.add_chart(type: 'line', embedded: 1)

# Configure the first series with a polynomial trendline.
chart1.add_series(
  categories: '=Sheet1!$A$2:$A$7',
  values:     '=Sheet1!$B$2:$B$7',
  trendline:  {
    type:  'polynomial',
    order: 3
  }
)

# Configure the second series with a moving average trendline.
chart1.add_series(
  categories: '=Sheet1!$A$2:$A$7',
  values:     '=Sheet1!$C$2:$C$7',
  trendline:  { type: 'linear' }
)

# Add a chart title. and some axis labels.
chart1.set_title(name: 'Chart with Trendlines')

# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart(
  'D2', chart1,
  x_offset: 25, y_offset: 10
)

#######################################################################
#
# Data Labels and Markers example.
#

# Create a Line chart.
chart2 = workbook.add_chart(type: 'line', embedded: 1)

# Configure the first series.
chart2.add_series(
  categories:  '=Sheet1!$A$2:$A$7',
  values:      '=Sheet1!$B$2:$B$7',
  data_labels: { value: 1 },
  marker:      { type: 'automatic' }
)

# Configure the second series.
chart2.add_series(
  categories: '=Sheet1!$A$2:$A$7',
  values:     '=Sheet1!$C$2:$C$7'
)

# Add a chart title. and some axis labels.
chart2.set_title(name: 'Chart with Data Labels and Markers')

# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart(
  'D18', chart2,
  x_offset: 25, y_offset: 10
)

#######################################################################
#
# Error Bars example.
#

# Create a Line chart.
chart3 = workbook.add_chart(type: 'line', embedded: 1)

# Configure the first series.
chart3.add_series(
  categories:   '=Sheet1!$A$2:$A$7',
  values:       '=Sheet1!$B$2:$B$7',
  y_error_bars: { type: 'standard_error' }
)

# Configure the second series.
chart3.add_series(
  categories: '=Sheet1!$A$2:$A$7',
  values:     '=Sheet1!$C$2:$C$7'
)

# Add a chart title. and some axis labels.
chart3.set_title(name: 'Chart with Error Bars')

# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart(
  'D34', chart3,
  x_offset: 25, y_offset: 10
)

#######################################################################
#
# Up-Down Bars example.
#

# Create a Line chart.
chart4 = workbook.add_chart(type: 'line', embedded: 1)

# Add the Up-Down Bars.
chart4.set_up_down_bars

# Configure the first series.
chart4.add_series(
  categories: '=Sheet1!$A$2:$A$7',
  values:     '=Sheet1!$B$2:$B$7'
)

# Configure the second series.
chart4.add_series(
  categories: '=Sheet1!$A$2:$A$7',
  values:     '=Sheet1!$C$2:$C$7'
)

# Add a chart title. and some axis labels.
chart4.set_title(name: 'Chart with Up-Down Bars')

# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart(
  'D50', chart4,
  x_offset: 25, y_offset: 10
)

#######################################################################
#
# High-Low Lines example.
#

# Create a Line chart.
chart5 = workbook.add_chart(type: 'line', embedded: 1)

# Add the High-Low lines.
chart5.set_high_low_lines

# Configure the first series.
chart5.add_series(
  categories: '=Sheet1!$A$2:$A$7',
  values:     '=Sheet1!$B$2:$B$7'
)

# Configure the second series.
chart5.add_series(
  categories: '=Sheet1!$A$2:$A$7',
  values:     '=Sheet1!$C$2:$C$7'
)

# Add a chart title. and some axis labels.
chart5.set_title(name: 'Chart with High-Low Lines')

# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart(
  'D66', chart5,
  x_offset: 25, y_offset: 10
)

#######################################################################
#
# Drop Lines example.
#

# Create a Line chart.
chart6 = workbook.add_chart(type: 'line', embedded: 1)

# Add Drop Lines.
chart6.set_drop_lines

# Configure the first series.
chart6.add_series(
  categories: '=Sheet1!$A$2:$A$7',
  values:     '=Sheet1!$B$2:$B$7'
)

# Configure the second series.
chart6.add_series(
  categories: '=Sheet1!$A$2:$A$7',
  values:     '=Sheet1!$C$2:$C$7'
)

# Add a chart title. and some axis labels.
chart6.set_title(name: 'Chart with Drop Lines')

# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart(
  'D82', chart6,
  x_offset: 25, y_offset: 10
)

workbook.close