chart_pareto.rb

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

#######################################################################
#
# A demo of a Pareto chart in Excel::Writer::XLSX.
#
# reverse ('(c)'), March 2015, John McNamara, jmcnamara@cpan.org
# convert to ruby by Hideo NAKAMURA, nakamura.hideo@gmail.com
#

require 'write_xlsx'

workbook  = WriteXLSX.new('chart_pareto.xlsx')
worksheet = workbook.add_worksheet

# Formats used in the workbook.
bold           = workbook.add_format(bold: 1)
percent_format = workbook.add_format(num_format: '0.0%')

# Widen the columns for visibility.
worksheet.set_column('A:A', 15)
worksheet.set_column('B:C', 10)

# Add the worksheet data that the charts will refer to.
headings = %w[Reason Number Percentage]

reasons = [
  'Traffic',   'Child care', 'Public Transport', 'Weather',
  'Overslept', 'Emergency'
]

numbers  = [60,    40,  20,  15,    10, 5]
percents = [0.44, 0.667, 0.8, 0.9, 0.967, 1]

worksheet.write_row('A1', headings, bold)
worksheet.write_col('A2', reasons)
worksheet.write_col('B2', numbers)
worksheet.write_col('C2', percents, percent_format)

# Create a new column chart. This will be the primary chart.
column_chart = workbook.add_chart(type: 'column', embedded: 1)

# Add a series
column_chart.add_series(
  categories: 'Sheet1!$A$2:$A$7',
  values:     'Sheet1!$B$2:$B$7'
)

# Add a chart title.
column_chart.set_title(name: 'Reasons for lateness')

# Turn off the chart legend.
column_chart.set_legend(position: 'none')

# Set the title and scale of the Y axes. Note, the secondary axis is set from
# the primary chart.
column_chart.set_y_axis(
  name: 'Respondents (number)',
  min:  0,
  max:  120
)
column_chart.set_y2_axis(max: 1)

# Create a new line chart. This will be the secondary chart.
line_chart = workbook.add_chart(type: 'line', embedded: 1)

# Add a series, on the secondary axis.
line_chart.add_series(
  categories: '=Sheet1!$A$2:$A$7',
  values:     '=Sheet1!$C$2:$C$7',
  marker:     { type: 'automatic' },
  y2_axis:    1
)

# Combine the charts.
column_chart.combine(line_chart)

# Insert the chart into the worksheet.
worksheet.insert_chart('F2', column_chart)

workbook.close