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

#######################################################################
#
# A demo of an various Excel chart data label features that are available
# via an WriteXLSX chart.
#
# Copyright 2000-2020, John McNamara, jmcnamara@cpan.org
# convert to ruby by Hideo NAKAMURA, nakamura.hideo@gmail.com
#

require 'write_xlsx'

workbook  = WriteXLSX.new('chart_data_labels.xlsx')
worksheet = workbook.add_worksheet
bold      = workbook.add_format(:bold => 1)

# Add the worksheet data that the charts will refer to.
headings = %w[Number Data Text]
data = [
  [2,  3,  4,  5,  6,  7],
  [20, 10, 20, 30, 40, 30],
  %w[Jan Feb Mar Apr May Jun]
]

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

#######################################################################
#
# Example with standard data labels.
#

# Create a Column chart.
chart1 = workbook.add_chart(:type => 'column', :embedded => 1)

# Configure the data series and add the data labels.
chart1.add_series(
  :categories  => '=Sheet1!$A$2:$A$7',
  :values      => '=Sheet1!$B$2:$B$7',
  :data_labels => { :value => 1 }
)

# Add a chart title.
chart1.set_title(:name => 'Chart with standard data labels')

# Turn off the chart legend.
chart1.set_legend(:none => 1)

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

#######################################################################
#
# Example with value and category data labels.
#

# Create a Column chart.
chart2 = workbook.add_chart(:type => 'column', :embedded => 1)

# Configure the data series and add the data labels.
chart2.add_series(
  :categories  => '=Sheet1!$A$2:$A$7',
  :values      => '=Sheet1!$B$2:$B$7',
  :data_labels => { :value => 1, :category => 1 }
)

# Add a chart title.
chart2.set_title(:name => 'Category and Value data labels')

# Turn off the chart legend.
chart2.set_legend(:none => 1)

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

#######################################################################
#
# Example with standard data labels with different font.
#

# Create a Column chart.
chart3 = workbook.add_chart(:type => 'column', :embedded => 1)

# Configure the data series and add the data labels.
chart3.add_series(
  :categories  => '=Sheet1!$A$2:$A$7',
  :values      => '=Sheet1!$B$2:$B$7',
  :data_labels => { :value => 1,
                    :font  => { :bold     => 1,
                                :color    => 'red',
                                :rotation => -30 } }
)

# Add a chart title.
chart3.set_title(:name => 'Data labels with user defined font')

# Turn off the chart legend.
chart3.set_legend(:none => 1)

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

#######################################################################
#
# Example with custom string data labels.
#

# Create a Column chart.
chart4 = workbook.add_chart(:type => 'column', :embedded => 1)

# Configure the data series and add the data labels.
chart4.add_series(
  :categories  => '=Sheet1!$A$2:$A$7',
  :values      => '=Sheet1!$B$2:$B$7',
  :data_labels => {
    :value  => 1,
    :border => { :color => 'red' },
    :fill   => { :color => 'yellow' }
  }
)

# Add a chart title.
chart4.set_title(:name => 'Data labels with formatting')

# Turn off the chart legend.
chart4.set_legend(:none => 1)

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

#######################################################################
#
# Example with custom string data labels.
#

# Create a Column chart.
chart5 = workbook.add_chart(:type => 'column', :embedded => 1)

# Some custom labels.
custom_labels = [
  { :value => 'Amy' },
  { :value => 'Bea' },
  { :value => 'Eva' },
  { :value => 'Fay' },
  { :value => 'Liv' },
  { :value => 'Una' }
]

# Configure the data series and add the data labels.
chart5.add_series(
  :categories  => '=Sheet1!$A$2:$A$7',
  :values      => '=Sheet1!$B$2:$B$7',
  :data_labels => { :value => 1, :custom => custom_labels }
)

# Add a chart title.
chart5.set_title(:name => 'Chart with custom string data labels')

# Turn off the chart legend.
chart5.set_legend(:none => 1)

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

#######################################################################
#
# Example with custom data labels from cells.
#

# Create a Column chart.
chart6 = workbook.add_chart(:type => 'column', :embedded => 1)

# Some custom labels.
custom_labels = [
  { :value => '=Sheet1!$C$2' },
  { :value => '=Sheet1!$C$3' },
  { :value => '=Sheet1!$C$4' },
  { :value => '=Sheet1!$C$5' },
  { :value => '=Sheet1!$C$6' },
  { :value => '=Sheet1!$C$7' }
]

# Configure the data series and add the data labels.
chart6.add_series(
  :categories  => '=Sheet1!$A$2:$A$7',
  :values      => '=Sheet1!$B$2:$B$7',
  :data_labels => { :value => 1, :custom => custom_labels }
)

# Add a chart title.
chart6.set_title(:name => 'Chart with custom data labels from cells')

# Turn off the chart legend.
chart6.set_legend(:none => 1)

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

#######################################################################
#
# Example with custom and default data labels.
#

# Create a Column chart.
chart7 = workbook.add_chart(:type => 'column', :embedded => 1)

# Some custom labels. The nil items will get the default value.
# We also set a font for the custom items as an extra example.
custom_labels = [
  { :value => '=Sheet1!$C$2', :font => { :color => 'red' } },
  nil,
  { :value => '=Sheet1!$C$4', :font => { :color => 'red' } },
  { :value => '=Sheet1!$C$5', :font => { :color => 'red' } }
]

# Configure the data series and add the data labels.
chart7.add_series(
  :categories  => '=Sheet1!$A$2:$A$7',
  :values      => '=Sheet1!$B$2:$B$7',
  :data_labels => { :value => 1, :custom => custom_labels }
)

# Add a chart title.
chart7.set_title(:name => 'Mixed custom and default data labels')

# Turn off the chart legend.
chart7.set_legend(:none => 1)

# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D98', chart7, { :x_offset => 25, :y_offset => 10 })

#######################################################################
#
# Example with deleted custom data labels.
#

# Create a Column chart.
chart8 = workbook.add_chart(:type => 'column', :embedded => 1)

# Some deleted custom labels and defaults (nil). This allows us to
# highlight certain values such as the minimum and maximum.
custom_labels = [
  { :delete => 1 },
  nil,
  { :delete => 1 },
  { :delete => 1 },
  nil,
  { :delete => 1 }
]

# Configure the data series and add the data labels.
chart8.add_series(
  :categories  => '=Sheet1!$A$2:$A$7',
  :values      => '=Sheet1!$B$2:$B$7',
  :data_labels => { :value => 1, :custom => custom_labels }
)

# Add a chart title.
chart8.set_title(:name => 'Chart with deleted data labels')

# Turn off the chart legend.
chart8.set_legend(:none => 1)

# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D114', chart8, { :x_offset => 25, :y_offset => 10 })

#######################################################################
#
# Example with custom string data labels and formatting.
#

# Create a Column chart.
chart9 = workbook.add_chart(:type => 'column', :embedded => 1)

# Some custom labels.
custom_labels = [
  { :value => 'Amy', :border => { :color => 'blue' } },
  { :value => 'Bea' },
  { :value => 'Eva' },
  { :value => 'Fay' },
  { :value => 'Liv' },
  { :value => 'Una', :fill => { :color => 'green' } }
]

# Configure the data series and add the data labels.
chart9.add_series(
  :categories  => '=Sheet1!$A$2:$A$7',
  :values      => '=Sheet1!$B$2:$B$7',
  :data_labels => {
    :value  => 1,
    :custom => custom_labels,
    :border => { :color => 'red' },
    :fill   => { :color => 'yellow' }
  }
)

# Add a chart title.
chart9.set_title(:name => 'Chart with custom labels and formatting')

# Turn off the chart legend.
chart9.set_legend(:none => 1)

# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D130', chart9, { :x_offset => 25, :y_offset => 10 })

workbook.close