Class: Writexlsx::Chart

Inherits:
Object
  • Object
show all
Includes:
Utility
Defined in:
lib/write_xlsx/chart.rb,
lib/write_xlsx/chart/bar.rb,
lib/write_xlsx/chart/pie.rb,
lib/write_xlsx/chart/area.rb,
lib/write_xlsx/chart/line.rb,
lib/write_xlsx/chart/radar.rb,
lib/write_xlsx/chart/stock.rb,
lib/write_xlsx/chart/column.rb,
lib/write_xlsx/chart/scatter.rb

Overview

SYNOPSIS

To create a simple Excel file with a chart using WriteXLSX:

require 'rubygems'
require 'write_xlsx'

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

# Add the worksheet data the chart refers to.
data = [
    [ 'Category', 2, 3, 4, 5, 6, 7 ],
    [ 'Value',    1, 4, 5, 2, 1, 5 ]
]

worksheet.write( 'A1', data )

# Add a worksheet chart.
chart = workbook.add_chart( type => 'column' )

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

workbook.close

DESCRIPTION

The Chart module is an abstract base class for modules that implement charts in WriteXLSX. The information below is applicable to all of the available subclasses.

The Chart module isn’t used directly. A chart object is created via the Workbook add_chart() method where the chart type is specified:

chart = workbook.add_chart( :type => 'column' )

Currently the supported chart types are:

area

Creates an Area (filled line) style chart. See Writexlsx::Chart::Area.

bar

Creates a Bar style (transposed histogram) chart. See Writexlsx::Chart::Bar.

column

Creates a column style (histogram) chart. See Writexlsx::Chart::Column.

line

Creates a Line style chart. See Writexlsx::Chart::Line.

pie

Creates a Pie style chart. See Writexlsx::Chart::Pie.

scatter

Creates a Scatter style chart. See Writexlsx::Chart::Scatter.

stock

Creates a Stock style chart. See Writexlsx::Chart::Stock.

radar

Creates a Radar style chart. See Writexlsx::Chart::Radar.

CHART FORMATTING

The following chart formatting properties can be set for any chart object that they apply to (and that are supported by WriteXLSX) such as chart lines, column fill areas, plot area borders, markers and other chart elements documented above.

line
border
fill
marker
trendline
data_labels

Chart formatting properties are generally set using hash refs.

chart.add_series(
    :values     => '=Sheet1!$B$1:$B$5',
    :line       => { color => 'blue' }
)

In some cases the format properties can be nested. For example a marker may contain border and fill sub-properties.

chart.add_series(
    :values     => '=Sheet1!$B$1:$B$5',
    :line       => { color => 'blue' },
    :marker     => {
        :type    => 'square',
        :size    => 5,
        :border  => { color => 'red' },
        :fill    => { color => 'yellow' }
    }
)

Line

The line format is used to specify properties of line objects that appear in a chart such as a plotted line on a chart or a border.

The following properties can be set for line formats in a chart.

none
color
width
dash_type

The none property is uses to turn the line off (it is always on by default except in Scatter charts). This is useful if you wish to plot a series with markers but without a line.

chart.add_series(
    :values     => '=Sheet1!$B$1:$B$5',
    :line       => { none => 1 }
)

The color property sets the color of the line.

chart.add_series(
    :values     => '=Sheet1!$B$1:$B$5',
    :line       => { color => 'red' }
)

The available colors are shown in the main WriteXLSX documentation. It is also possible to set the color of a line with a HTML style RGB color:

chart.add_series(
    :line       => { color => '#FF0000' }
)

The width property sets the width of the line. It should be specified in increments of 0.25 of a point as in Excel.

chart.add_series(
    :values     => '=Sheet1!$B$1:$B$5',
    :line       => { width => 3.25 }
)

The dash_type property sets the dash style of the line.

chart->add_series(
    :values     => '=Sheet1!$B$1:$B$5',
    :line       => { dash_type => 'dash_dot' }
)

The following dash_type values are available. They are shown in the order that they appear in the Excel dialog.

solid
round_dot
square_dot
dash
dash_dot
long_dash
long_dash_dot
long_dash_dot_dot

The default line style is solid.

More than one line property can be specified at time:

chart.add_series(
    :values     => '=Sheet1!$B$1:$B$5',
    :line       => {
        :color     => 'red',
        :width     => 1.25,
        :dash_type => 'square_dot'
    }
)

Border

The border property is a synonym for line.

It can be used as a descriptive substitute for line in chart types such as Bar and Column that have a border and fill style rather than a line style. In general chart objects with a border property will also have a fill property.

Fill

The fill format is used to specify filled areas of chart objects such as the interior of a column or the background of the chart itself.

The following properties can be set for fill formats in a chart.

none
color

The none property is uses to turn the fill property off (it is generally on by default).

chart.add_series(
    :values     => '=Sheet1!$B$1:$B$5',
    :fill       => { none => 1 }
)

The color property sets the color of the fill area.

chart.add_series(
    :values     => '=Sheet1!$B$1:$B$5',
    :fill       => { color => 'red' }
)

The available colors are shown in the main WriteXLSX documentation. It is also possible to set the color of a fill with a HTML style RGB color:

chart.add_series(
    :fill       => { color => '#FF0000' }
)

The fill format is generally used in conjunction with a border format which has the same properties as a line format.

chart.add_series(
    :values     => '=Sheet1!$B$1:$B$5',
    :border     => { color => 'red' },
    :fill       => { color => 'yellow' }
)

Marker

The marker format specifies the properties of the markers used to distinguish series on a chart. In general only Line and Scatter chart types and trendlines use markers.

The following properties can be set for marker formats in a chart.

type
size
border
fill

The type property sets the type of marker that is used with a series.

chart.add_series(
    :values     => '=Sheet1!$B$1:$B$5',
    :marker     => { type => 'diamond' }
)

The following type properties can be set for marker formats in a chart. These are shown in the same order as in the Excel format dialog.

automatic
none
square
diamond
triangle
x
star
short_dash
long_dash
circle
plus

The automatic type is a special case which turns on a marker using the default marker style for the particular series number.

chart.add_series(
    :values     => '=Sheet1!$B$1:$B$5',
    :marker     => { type => 'automatic' }
)

If automatic is on then other marker properties such as size, border or fill cannot be set.

The size property sets the size of the marker and is generally used in conjunction with type.

chart.add_series(
    :values     => '=Sheet1!$B$1:$B$5',
    :marker     => { type => 'diamond', size => 7 }
)

Nested border and fill properties can also be set for a marker. These have the same sub-properties as shown above.

chart.add_series(
    :values     => '=Sheet1!$B$1:$B$5',
    :marker     => {
        :type    => 'square',
        :size    => 5,
        :border  => { color => 'red' },
        :fill    => { color => 'yellow' }
    }
)

Trendline

A trendline can be added to a chart series to indicate trends in the data such as a moving average or a polynomial fit.

The following properties can be set for trendline formats in a chart.

type
order       (for polynomial trends)
period      (for moving average)
forward     (for all except moving average)
backward    (for all except moving average)
name
line

The type property sets the type of trendline in the series.

chart.add_series(
    :values     => '=Sheet1!$B$1:$B$5',
    :trendline  => { type => 'linear' }
)

The available trendline types are:

exponential
linear
log
moving_average
polynomial
power

A polynomial trendline can also specify the order of the polynomial. The default value is 2.

chart.add_series(
    :values    => '=Sheet1!$B$1:$B$5',
    :trendline => {
        :type  => 'polynomial',
        :order => 3
    }
)

A moving_average trendline can also the period of the moving average. The default value is 2.

chart.add_series(
    :values     => '=Sheet1!$B$1:$B$5',
    :trendline  => {
        :type   => 'moving_average',
        :period => 3
    }
)

The forward and backward properties set the forecast period of the trendline.

chart.add_series(
    :values    => '=Sheet1!$B$1:$B$5',
    :trendline => {
        :type     => 'linear',
        :forward  => 0.5,
        :backward => 0.5
    }
)

The name property sets an optional name for the trendline that will appear in the chart legend. If it isn’t specified the Excel default name will be displayed. This is usually a combination of the trendline type and the series name.

chart.add_series(
    :values    => '=Sheet1!$B$1:$B$5',
    :trendline => {
        :type => 'linear',
        :name => 'Interpolated trend'
    }
)

Several of these properties can be set in one go:

chart.add_series(
    :values     => '=Sheet1!$B$1:$B$5',
    :trendline  => {
        :type     => 'linear',
        :name     => 'My trend name',
        :forward  => 0.5,
        :backward => 0.5,
        :line     => {
            :color     => 'red',
            :width     => 1,
            :dash_type => 'long_dash'
        }
    }
)

Trendlines cannot be added to series in a stacked chart or pie chart or (when implemented) to 3-D, radar, surface, or doughnut charts.

Data Labels

Data labels can be added to a chart series to indicate the values of the plotted data points.

The following properties can be set for data_labels formats in a chart.

:value
:category
:series_name
:position
:leader_lines
:percentage

The value property turns on the Value data label for a series.

chart.add_series(
    :values      => '=Sheet1!$B$1:$B$5',
    :data_labels => { :value => 1 }
)

The category property turns on the Category Name data label for a series.

chart.add_series(
    :values      => '=Sheet1!$B$1:$B$5',
    :data_labels => { :category => 1 }
)

The series_name property turns on the Series Name data label for a series.

chart.add_series(
    :values      => '=Sheet1!$B$1:$B$5',
    :data_labels => { :series_name => 1 }
)

The C<position> property is used to position the data label for a series.

chart.add_series(
    :values      => '=Sheet1!$B$1:$B$5',
    :data_labels => { :value => 1, :position => 'center' }
)

Valid positions are:

:center
:right
:left
:top
:bottom
:above           # Same as top
:below           # Same as bottom
:inside_end      # Pie chart mainly.
:outside_end     # Pie chart mainly.
:best_fit        # Pie chart mainly.

The C<percentage> property is used to turn on the I<Percentage> for the data label for a series. It is mainly used for pie charts.

chart.add_series(
     :values      => '=Sheet1!$B$1:$B$5',
     :data_labels => { :percentage => 1 }
)

The C<leader_lines> property is used to turn on I<Leader Lines> for the data label for a series. It is mainly used for pie charts.

chart.add_series(
     :values      => '=Sheet1!$B$1:$B$5',
     :data_labels => { :value => 1, :leader_lines => 1 }
)

Direct Known Subclasses

Area, Bar, Column, Line, Pie, Radar, Scatter, Stock

Defined Under Namespace

Classes: Area, Bar, Column, Line, Pie, Radar, Scatter, Stock

Constant Summary

Constants included from Utility

Utility::COL_MAX, Utility::ROW_MAX, Utility::SHEETNAME_MAX, Utility::STR_MAX

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Methods included from Utility

#absolute_char, #check_parameter, delete_files, #ptrue?, #put_deprecate_message, #substitute_cellref, #underline_attributes, #xl_cell_to_rowcol, #xl_col_to_name, #xl_range, #xl_range_formula, #xl_rowcol_to_cell, #xml_str

Constructor Details

#initialize(subtype) ⇒ Chart

:nodoc:



474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
 
# File 'lib/write_xlsx/chart.rb', line 474

def initialize(subtype)   # :nodoc:
  @writer = Package::XMLWriterSimple.new

  @subtype           = subtype
  @sheet_type        = 0x0200
  @orientation       = 0x0
  @series            = []
  @embedded          = 0
  @id                = ''
  @series_index      = 0
  @style_id          = 2
  @axis_ids          = []
  @axis2_ids         = []
  @cat_has_num_fmt   = false
  @requires_category = 0
  @legend_position   = 'right'
  @cat_axis_position = 'b'
  @val_axis_position = 'l'
  @formula_ids       = {}
  @formula_data      = []
  @horiz_cat_axis    = 0
  @horiz_val_axis    = 1
  @protection        = 0
  @x_axis            = {}
  @y_axis            = {}
  @x2_axis           = {}
  @y2_axis           = {}
  @name              = ''
  @show_blanks       = 'gap'
  @show_hidden_data  = false
  @show_crosses      = true

  set_default_properties
end

Instance Attribute Details

#embedded ⇒ Object (readonly)

:nodoc:



440
441
442
 
# File 'lib/write_xlsx/chart.rb', line 440

def embedded
  @embedded
end

#formula_data ⇒ Object (readonly)

:nodoc:



440
441
442
 
# File 'lib/write_xlsx/chart.rb', line 440

def formula_data
  @formula_data
end

#formula_ids ⇒ Object (readonly)

:nodoc:



440
441
442
 
# File 'lib/write_xlsx/chart.rb', line 440

def formula_ids
  @formula_ids
end

#id ⇒ Object

:nodoc:



438
439
440
 
# File 'lib/write_xlsx/chart.rb', line 438

def id
  @id
end

#index=(value) ⇒ Object (writeonly)

:nodoc:



439
440
441
 
# File 'lib/write_xlsx/chart.rb', line 439

def index=(value)
  @index = value
end

#name ⇒ Object

:nodoc:



438
439
440
 
# File 'lib/write_xlsx/chart.rb', line 438

def name
  @name
end

#palette=(value) ⇒ Object (writeonly)

:nodoc:



439
440
441
 
# File 'lib/write_xlsx/chart.rb', line 439

def palette=(value)
  @palette = value
end

#protection=(value) ⇒ Object (writeonly)

:nodoc:



439
440
441
 
# File 'lib/write_xlsx/chart.rb', line 439

def protection=(value)
  @protection = value
end

Class Method Details

.factory(current_subclass, subtype = nil) ⇒ Object

Factory method for returning chart objects based on their class type.



445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
 
# File 'lib/write_xlsx/chart.rb', line 445

def self.factory(current_subclass, subtype = nil) # :nodoc:
  case current_subclass.downcase.capitalize
  when 'Area'
    require 'write_xlsx/chart/area'
    Chart::Area.new(subtype)
  when 'Bar'
    require 'write_xlsx/chart/bar'
    Chart::Bar.new(subtype)
  when 'Column'
    require 'write_xlsx/chart/column'
    Chart::Column.new(subtype)
  when 'Line'
    require 'write_xlsx/chart/line'
    Chart::Line.new(subtype)
  when 'Pie'
    require 'write_xlsx/chart/pie'
    Chart::Pie.new(subtype)
  when 'Radar'
    require 'write_xlsx/chart/radar'
    Chart::Radar.new(subtype)
  when 'Scatter'
    require 'write_xlsx/chart/scatter'
    Chart::Scatter.new(subtype)
  when 'Stock'
    require 'write_xlsx/chart/stock'
    Chart::Stock.new(subtype)
  end
end

Instance Method Details

#add_series(params) ⇒ Object

Add a series and it’s properties to a chart.

In an Excel chart a “series” is a collection of information such as values, x-axis labels and the formatting that define which data is plotted.

With a WriteXLSX chart object the add_series() method is used to set the properties for a series:

chart.add_series(
    :categories => '=Sheet1!$A$2:$A$10', # Optional.
    :values     => '=Sheet1!$B$2:$B$10', # Required.
    :line       => { color => 'blue' }
)

The properties that can be set are:

:values

This is the most important property of a series and must be set for every chart object. It links the chart with the worksheet data that it displays. A formula or array ref can be used for the data range, see below.

:categories

This sets the chart category labels. The category is more or less the same as the X-axis. In most chart types the categories property is optional and the chart will just assume a sequential series from 1 .. n.

:name

Set the name for the series. The name is displayed in the chart legend and in the formula bar. The name property is optional and if it isn’t supplied it will default to Series 1 .. n.

:line

Set the properties of the series line type such as colour and width. See the “CHART FORMATTING” section below.

:border

Set the border properties of the series such as colour and style. See the “CHART FORMATTING” section below.

:fill

Set the fill properties of the series such as colour. See the “CHART FORMATTING” section below.

:marker

Set the properties of the series marker such as style and color. See the “CHART FORMATTING” section below.

:trendline

Set the properties of the series trendline such as linear, polynomial and moving average types. See the “CHART FORMATTING” section below.

:data_labels

Set data labels for the series. See the “CHART FORMATTING” section below.

:invert_if_negative

Invert the fill colour for negative values. Usually only applicable to column and bar charts.

The categories and values can take either a range formula such as =Sheet1!$A$2:$A$7 or, more usefully when generating the range programmatically, an array ref with zero indexed row/column values:

[ sheetname, row_start, row_end, col_start, col_end ]

The following are equivalent:

chart.add_series( categories => '=Sheet1!$A$2:$A$7'      ) # Same as ...
chart.add_series( categories => [ 'Sheet1', 1, 6, 0, 0 ] ) # Zero-indexed.

You can add more than one series to a chart. In fact, some chart types such as stock require it. The series numbering and order in the Excel chart will be the same as the order in which that are added in WriteXLSX.

# Add the first series.
chart.add_series(
    :categories => '=Sheet1!$A$2:$A$7',
    :values     => '=Sheet1!$B$2:$B$7',
    :name       => 'Test data series 1'
)

# Add another series. Same categories. Different range values.
chart.add_series(
    :categories => '=Sheet1!$A$2:$A$7',
    :values     => '=Sheet1!$C$2:$C$7',
    :name       => 'Test data series 2'
)



636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
 
# File 'lib/write_xlsx/chart.rb', line 636

def add_series(params)
  # Check that the required input has been specified.
  unless params.has_key?(:values)
    raise "Must specify ':values' in add_series"
  end

  if @requires_category != 0 && !params.has_key?(:categories)
    raise  "Must specify ':categories' in add_series for this chart type"
  end

  # Convert aref params into a formula string.
  values     = aref_to_formula(params[:values])
  categories = aref_to_formula(params[:categories])

  # Switch name and name_formula parameters if required.
  name, name_formula = process_names(params[:name], params[:name_formula])

  # Get an id for the data equivalent to the range formula.
  cat_id  = get_data_id(categories,   params[:categories_data])
  val_id  = get_data_id(values,       params[:values_data])
  name_id = get_data_id(name_formula, params[:name_data])

  # Set the line properties for the series.
  line = get_line_properties(params[:line])

  # Allow 'border' as a synonym for 'line' in bar/column style charts.
  line = get_line_properties(params[:border]) if params[:border]

  # Set the fill properties for the series.
  fill = get_fill_properties(params[:fill])

  # Set the marker properties for the series.
  marker = get_marker_properties(params[:marker])

  # Set the trendline properties for the series.
  trendline = get_trendline_properties(params[:trendline])

  # Set the labels properties for the series.
  labels = get_labels_properties(params[:data_labels])

  # Set the "invert if negative" fill property.
  invert_if_neg = params[:invert_if_negative]

  # Set the secondary axis properties.
  x2_axis = params[:x2_axis]
  y2_axis = params[:y2_axis]

  # Add the user supplied data to the internal structures.
  @series << {
    :_values        => values,
    :_categories    => categories,
    :_name          => name,
    :_name_formula  => name_formula,
    :_name_id       => name_id,
    :_val_data_id   => val_id,
    :_cat_data_id   => cat_id,
    :_line          => line,
    :_fill          => fill,
    :_marker        => marker,
    :_trendline     => trendline,
    :_labels        => labels,
    :_invert_if_neg => invert_if_neg,
    :_x2_axis       => x2_axis,
    :_y2_axis       => y2_axis
  }
end

#assemble_xml_file ⇒ Object

Assemble and write the XML file.



516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
 
# File 'lib/write_xlsx/chart.rb', line 516

def assemble_xml_file   # :nodoc:
  @writer.xml_decl

  # Write the c:chartSpace element.
  write_chart_space do

    # Write the c:lang element.
    write_lang

    # Write the c:style element.
    write_style

    # Write the c:protection element.
    write_protection

    # Write the c:chart element.
    write_chart

    # Write the c:printSettings element.
    write_print_settings if @embedded && @embedded != 0
  end

  # Close the XML writer object and filehandle.
  @writer.crlf
  @writer.close
end

#set_chartarea(params) ⇒ Object

Set the properties of the chart chartarea.

The set_chartarea() method is used to set the properties of the chart area.

This method isn’t implemented yet and is only available in writeexcel gem. However, it can be simulated using the set_style() method.



958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
 
# File 'lib/write_xlsx/chart.rb', line 958

def set_chartarea(params)
  # TODO. Need to refactor for XLSX format.
  return

  return if params.empty?

  area = @chartarea

  # Embedded automatic line weight has a different default value.
  area[:_line_weight] = 0xFFFF if @embedded

  # Set the chart background colour.
  if params[:color]
    index, rgb = get_color_indices(params[:color])
    if index
      area[:_fg_color_index] = index
      area[:_fg_color_rgb]   = rgb
      area[:_bg_color_index] = 0x08
      area[:_bg_color_rgb]   = 0x000000
      area[:_area_pattern]   = 1
      area[:_area_options]   = 0x0000 if @embedded
      area[:_visible]        = 1
    end
  end

  # Set the border line colour.
  if params[:line_color]
    index, rgb = get_color_indices(params[:line_color])
    if index
      area[:_line_color_index] = index
      area[:_line_color_rgb]   = rgb
      area[:_line_pattern]     = 0x00
      area[:_line_options]     = 0x0000
      area[:_visible]          = 1
    end
  end

  # Set the border line pattern.
  if params[:line_pattern]
    pattern = get_line_pattern(params[:line_pattern])
    area[:_line_pattern]     = pattern
    area[:_line_options]     = 0x0000
    area[:_line_color_index] = 0x4F unless params[:line_color]
    area[:_visible]          = 1
  end

  # Set the border line weight.
  if params[:line_weight]
    weight = get_line_weight(params[:line_weight])
    area[:_line_weight]      = weight
    area[:_line_options]     = 0x0000
    area[:_line_pattern]     = 0x00 unless params[:line_pattern]
    area[:_line_color_index] = 0x4F unless params[:line_color]
    area[:_visible]          = 1
  end
end

#set_embedded_config_data ⇒ Object

Setup the default configuration data for an embedded chart.



1061
1062
1063
1064
1065
1066
1067
 
# File 'lib/write_xlsx/chart.rb', line 1061

def set_embedded_config_data
  @embedded = 1

  # TODO. We may be able to remove this after refactoring.

  @chartarea = default_chartarea_property_for_embedded
end

#set_legend(params) ⇒ Object

Set the properties of the chart legend.

The set_legend() method is used to set properties of the chart legend.

chart.set_legend( :position => 'none' )

The properties that can be set are:

:position

Set the position of the chart legend.

chart.set_legend( :position => 'bottom' )

The default legend position is right. The available positions are:

none
top
bottom
left
right
overlay_left
overlay_right

:delete_series

This allows you to remove 1 or more series from the the legend (the series will still display on the chart). This property takes an array ref as an argument and the series are zero indexed:

# Delete/hide series index 0 and 2 from the legend.
chart.set_legend( :delete_series => [0, 2] )



881
882
883
884
 
# File 'lib/write_xlsx/chart.rb', line 881

def set_legend(params)
  @legend_position = params[:position] || 'right'
  @legend_delete_series = params[:delete_series]
end

#set_plotarea(params) ⇒ Object

Set the properties of the chart plotarea.

The set_plotarea() method is used to set properties of the plot area of a chart.

This method isn’t implemented yet and is only available in writeexcel gem. However, it can be simulated using the set_style() method.



896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
 
# File 'lib/write_xlsx/chart.rb', line 896

def set_plotarea(params)

  # TODO. Need to refactor for XLSX format.
  return

  return if params.empty?

  area = @plotarea

  # Set the plotarea visibility.
  if params[:visible]
    area[:_visible] = params[:visible]
    return unless area[:_visible]
  end

  # TODO. could move this out of if statement.
  area[:_bg_color_index] = 0x08

  # Set the chart background colour.
  if params[:color]
    index, rgb = get_color_indices(params[:color])
    if index
      area[:_fg_color_index] = index
      area[:_fg_color_rgb]   = rgb
      area[:_bg_color_index] = 0x08
      area[:_bg_color_rgb]   = 0x000000
    end

  end

  # Set the border line colour.
  if params[:line_color]
    index, rgb = get_color_indices(params[:line_color])
    if index
      area[:_line_color_index] = index
      area[:_line_color_rgb]   = rgb
    end
  end

  # Set the border line pattern.
  if params[:line_pattern]
    pattern = get_line_pattern(params[:line_pattern])
    area[:_line_pattern] = pattern
  end

  # Set the border line weight.
  if params[:line_weight]
    weight = get_line_weight(params[:line_weight])
    area[:_line_weight] = weight
  end
end

#set_style(style_id = 2) ⇒ Object

Set on of the 42 built-in Excel chart styles. The default style is 2.

The set_style() method is used to set the style of the chart to one of the 42 built-in styles available on the ‘Design’ tab in Excel:

chart.set_style( 4 )



1023
1024
1025
1026
 
# File 'lib/write_xlsx/chart.rb', line 1023

def set_style(style_id = 2)
  style_id = 2 if style_id < 0 || style_id > 42
  @style_id = style_id
end

#set_title(params) ⇒ Object

Set the properties of the chart title.

The set_title() method is used to set properties of the chart title.

chart.set_title( :name => 'Year End Results' )

The properties that can be set are:

:name

Set the name (title) for the chart. The name is displayed above the chart. The name can also be a formula such as =Sheet1!$A$1. The name property is optional. The default is to have no chart title.



839
840
841
842
843
844
845
846
847
848
849
850
 
# File 'lib/write_xlsx/chart.rb', line 839

def set_title(params)
  name, name_formula = process_names(params[:name], params[:name_formula])

  data_id = get_data_id(name_formula, params[:data])

  @title_name    = name
  @title_formula = name_formula
  @title_data_id = data_id

  # Set the font properties if present.
  @title_font = convert_font_args(params[:name_font])
end

#set_x2_axis(params = {}) ⇒ Object

Set the properties of the secondary X-axis.



815
816
817
 
# File 'lib/write_xlsx/chart.rb', line 815

def set_x2_axis(params = {})
  @x2_axis = convert_axis_args(@x2_axis, params)
end

#set_x_axis(params = {}) ⇒ Object

Set the properties of the X-axis.

The set_x_axis() method is used to set properties of the X axis.

chart.set_x_axis( :name => 'Quarterly results' )

The properties that can be set are:

:name
:min
:max
:minor_unit
:major_unit
:crossing
:reverse
:log_base
:label_position

These are explained below. Some properties are only applicable to value or category axes, as indicated. See “Value and Category Axes” for an explanation of Excel’s distinction between the axis types.

:name

Set the name (title or caption) for the axis. The name is displayed below the X axis. The name property is optional. The default is to have no axis name. (Applicable to category and value axes).

chart.set_x_axis( :name => 'Quarterly results' )

The name can also be a formula such as =Sheet1!$A$1.

:min

Set the minimum value for the axis range. (Applicable to value axes only).

chart.set_x_axis( :min => 20 )

:max

Set the maximum value for the axis range. (Applicable to value axes only).

chart.set_x_axis( :max => 80 )

:minor_unit

Set the increment of the minor units in the axis range. (Applicable to value axes only).

chart.set_x_axis( :minor_unit => 0.4 )

:major_unit

Set the increment of the major units in the axis range. (Applicable to value axes only).

chart.set_x_axis( :major_unit => 2 )

:crossing

Set the position where the y axis will cross the x axis. (Applicable to category and value axes).

The crossing value can either be the string ‘max’ to set the crossing at the maximum axis value or a numeric value.

chart.set_x_axis( :crossing => 3 )
# or
chart.set_x_axis( :crossing => 'max' )

For category axes the numeric value must be an integer to represent the category number that the axis crosses at. For value axes it can have any value associated with the axis.

If crossing is omitted (the default) the crossing will be set automatically by Excel based on the chart data.

:reverse

Reverse the order of the axis categories or values. (Applicable to category and value axes).

chart.set_x_axis( :reverse => 1 )

:log_base

Set the log base of the axis range. (Applicable to value axes only).

chart.set_x_axis( :log_base => 10 )

:label_position

Set the “Axis labels” position for the axis. The following positions are available:

next_to (the default)
high
low
none

More than one property can be set in a call to set_x_axis:

chart.set_x_axis(
    :name => 'Quarterly results',
    :min  => 10,
    :max  => 80
)



798
799
800
 
# File 'lib/write_xlsx/chart.rb', line 798

def set_x_axis(params = {})
  @x_axis = convert_axis_args(@x_axis, params)
end

#set_xml_writer(filename) ⇒ Object

:nodoc:



509
510
511
 
# File 'lib/write_xlsx/chart.rb', line 509

def set_xml_writer(filename)   # :nodoc:
  @writer.set_xml_writer(filename)
end

#set_y2_axis(params = {}) ⇒ Object

Set the properties of the secondary Y-axis.



822
823
824
 
# File 'lib/write_xlsx/chart.rb', line 822

def set_y2_axis(params = {})
  @y2_axis = convert_axis_args(@y2_axis, params)
end

#set_y_axis(params = {}) ⇒ Object

Set the properties of the Y-axis.

The set_y_axis() method is used to set properties of the Y axis. The properties that can be set are the same as for set_x_axis,



808
809
810
 
# File 'lib/write_xlsx/chart.rb', line 808

def set_y_axis(params = {})
  @y_axis = convert_axis_args(@y_axis, params)
end

#show_blanks_as(option) ⇒ Object

Set the option for displaying blank data in a chart. The default is ‘gap’.

The show_blanks_as method controls how blank data is displayed in a chart.

chart.show_blanks_as('span')

The available options are:

gap    # Blank data is show as a gap. The default.
zero   # Blank data is displayed as zero.
span   # Blank data is connected with a line.



1041
1042
1043
1044
1045
1046
1047
1048
1049
 
# File 'lib/write_xlsx/chart.rb', line 1041

def show_blanks_as(option)
  return unless option

  unless [:gap, :zero, :span].include?(option.to_sym)
    raise "Unknown show_blanks_as() option '#{option}'\n"
  end

  @show_blanks = option
end

#show_hidden_data ⇒ Object

Display data in hidden rows or columns on the chart.



1054
1055
1056
 
# File 'lib/write_xlsx/chart.rb', line 1054

def show_hidden_data
  @show_hidden_data = true
end

#write_bar_chart(params) ⇒ Object

Write the <c:barChart> element.



1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
 
# File 'lib/write_xlsx/chart.rb', line 1072

def write_bar_chart(params)   # :nodoc:
  if ptrue?(params[:primary_axes])
    series = get_primary_axes_series
  else
    series = get_secondary_axes_series
  end
  return if series.empty?

  subtype = @subtype
  subtype = 'percentStacked' if subtype == 'percent_stacked'

  @writer.tag_elements('c:barChart') do
    # Write the c:barDir element.
    write_bar_dir
    # Write the c:grouping element.
    write_grouping(subtype)
    # Write the c:ser elements.
    series.each {|s| write_ser(s)}

    # write the c:marker element.
    write_marker_value

    # write the c:overlap element.
    write_overlap if @subtype =~ /stacked/

    # Write the c:axId elements
    write_axis_ids(params)
  end
end