Class: Writeexcel::Chart

Inherits:
Worksheet show all
Defined in:
lib/writeexcel/chart.rb,
lib/writeexcel/charts/bar.rb,
lib/writeexcel/charts/pie.rb,
lib/writeexcel/charts/area.rb,
lib/writeexcel/charts/line.rb,
lib/writeexcel/charts/stock.rb,
lib/writeexcel/charts/column.rb,
lib/writeexcel/charts/scatter.rb

Direct Known Subclasses

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

Defined Under Namespace

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

Constant Summary

Constants inherited from Worksheet

Worksheet::Buffer, Worksheet::ColMax, Worksheet::RowMax, Worksheet::StrMax

Constants inherited from BIFFWriter

BIFFWriter::BIFF_Version, BIFFWriter::BigEndian

Instance Attribute Summary

Attributes inherited from Worksheet

#filter_area, #object_ids, #print_range, #title_range

Attributes inherited from BIFFWriter

#data, #datasize

Class Method Summary collapse

Instance Method Summary collapse

Methods inherited from Worksheet

#activate, #autofilter, #autofilter_name_record_short, #boundsheet, #center_horizontally, #center_vertically, #charts_size, #cleanup, #comments_size, #comments_visible?, #data_validation, #filter_column, #filter_count, #fit_to_pages, #freeze_panes, #hidden=, #hidden?, #hide, #hide_gridlines, #hide_zero, #image_mso_size, #image_mso_size=, #images_array, #images_size, #index, #insert_chart, #insert_image, #is_name_utf16be?, #keep_leading_zeros, #merge_range, #merge_range_with_date_time, #name, #name_record_short, #num_images, #num_images=, #num_shapes, #offset, #offset=, #outline_settings, #position_object, #print_across, #print_area, #print_area_name_record_short, #print_row_col_headers, #print_title_name_record_long, #print_title_name_record_short, #protect, #push_cluster, #push_object_ids, #repeat_columns, #repeat_formula, #repeat_rows, #right_to_left, #select, #selected=, #selected?, #set_column, #set_first_row_column, #set_first_sheet, #set_footer, #set_h_pagebreaks, #set_header, #set_landscape, #set_margin_bottom, #set_margin_left, #set_margin_right, #set_margin_top, #set_margins, #set_margins_LR, #set_margins_TB, #set_page_view, #set_paper, #set_portrait, #set_print_scale, #set_row, #set_selection, #set_start_page, #set_tab_color, #set_v_pagebreaks, #set_zoom, #show_comments, #split_panes, #store_formula, #store_mso_client_anchor, #store_mso_client_data, #store_mso_opt_image, #store_mso_sp, #store_mso_sp_container, #store_parent_mso_record, #type, #write, #write_blank, #write_col, #write_comment, #write_date_time, #write_formula, #write_number, #write_row, #write_string, #write_url, #write_url_range, #write_utf16be_string, #write_utf16le_string

Methods included from ConvertDateTime

#convert_date_time

Methods inherited from BIFFWriter

#add_continue, #add_mso_generic, #append, #cleanup, #clear_data_for_test, #get_data, #inspect, #not_using_tmpfile, #print_caller_info, #set_byte_order, #store_bof, #store_eof, #unpack_record

Methods included from CallerInfo

#caller_info

Methods inherited from WriteFile

#append

Constructor Details

#initialize(*args) ⇒ Chart

:call-seq:

new(filename, name, index, encoding, activesheet, firstsheet, external_bin = nil)

Default constructor for sub-classes.



89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
 
# File 'lib/writeexcel/chart.rb', line 89

def initialize(*args)       #:nodoc:
  super

  @type        = 0x0200
  @orientation = 0x0
  @series      = []
  @embedded    = false

  @external_bin = false
  @x_axis_formula = nil
  @x_axis_name = nil
  @y_axis_formula = nil
  @y_axis_name = nil
  @title_name = nil
  @title_formula = nil
  @vary_data_color = 0
  set_default_properties
  set_default_config_data
end

Class Method Details

.factory(type, *args) ⇒ Object

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



61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
 
# File 'lib/writeexcel/chart.rb', line 61

def self.factory(type, *args)       #:nodoc:
  klass =
  case type
  when 'Chart::Column'
    Chart::Column
  when 'Chart::Bar'
    Chart::Bar
  when 'Chart::Line'
    Chart::Line
  when 'Chart::Area'
    Chart::Area
  when 'Chart::Pie'
    Chart::Pie
  when 'Chart::Scatter'
    Chart::Scatter
  when 'Chart::Stock'
    Chart::Stock
  end

  klass.new(*args)
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 name that define which data is plotted. These settings are displayed when you select the Chart -> Source Data… menu option.

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

chart.add_series(
  :categories    => '=Sheet1!$A$2:$A$10',
  :values        => '=Sheet1!$B$2:$B$10',
  :name          => 'Series name',
  :name_formula  => '=Sheet1!$B$1'
)

The properties that can be set are:

:values        (required)
:categories    (optional for most chart types)
:name          (optional)
:name_formula  (optional)

* :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.

      chart.add_series(:values => '=Sheet1!$B$2:$B$10')

  Note the format that should be used for the formula. It is the same
  as is used in Excel. You must also add the worksheet that you are
  referring to before you link to it, via the workbook
  add_worksheet() method.

* :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.

      chart.add_series(
        :categories    => '=Sheet1!$A$2:$A$10',
        :values        => '=Sheet1!$B$2:$B$10'
      )

* :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 will default to Series 1 .. n.

      chart.add_series(
        ...
        :name          => 'Series name'
      )

* :name_formula

  Optional, can be used to link the name to a worksheet cell.
  See "Chart names and links".

      chart.add_series(
        ...
        :name          => 'Series name',
        :name_formula  => '=Sheet1!$B$1'
      )

You can add more than one series to a chart. The series numbering and order in the final chart is the same as the order in which that are added.

# 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. Category is the same but values are different.
chart.add_series(
  :categories => '=Sheet1!$A$2:$A$7',
  :values     => '=Sheet1!$C$2:$C$7',
  :name       => 'Test data series 2'
)



198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
 
# File 'lib/writeexcel/chart.rb', line 198

def add_series(params)
  raise "Must specify 'values' in add_series()" if params[:values].nil?

  # Parse the ranges to validate them and extract salient information.
  value_data    = parse_series_formula(params[:values])
  category_data = parse_series_formula(params[:categories])
  name_formula  = parse_series_formula(params[:name_formula])

  # Default category count to the same as the value count if not defined.
  category_data[1] = value_data[1] if category_data.size < 2

  # Add the parsed data to the user supplied data.
  params[:values]       = value_data
  params[:categories]   = category_data
  params[:name_formula] = name_formula

  # Encode the Series name.
  name, encoding = encode_utf16(params[:name], params[:name_encoding])

  params[:name]          = name
  params[:name_encoding] = encoding

  @series << params
end

#close ⇒ Object

Create and store the Chart data structures.



522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
 
# File 'lib/writeexcel/chart.rb', line 522

def close  # :nodoc:
  # Ignore any data that has been written so far since it is probably
  # from unwanted Worksheet method calls.
  @data = ''

  # TODO. Check for charts without a series?

  # Store the chart BOF.
  store_bof(0x0020)

  # Store the page header
  store_header

  # Store the page footer
  store_footer

  # Store the page horizontal centering
  store_hcenter

  # Store the page vertical centering
  store_vcenter

  # Store the left margin
  store_margin_left

  # Store the right margin
  store_margin_right

  # Store the top margin
  store_margin_top

  # Store the bottom margin
  store_margin_bottom

  # Store the page setup
  store_setup

  # Store the sheet password
  store_password

  # Start of Chart specific records.

  # Store the FBI font records.
  store_fbi(*@config[:font_numbers])
  store_fbi(*@config[:font_series])
  store_fbi(*@config[:font_title])
  store_fbi(*@config[:font_axes])

  # Ignore UNITS record.

  # Store the Chart sub-stream.
  store_chart_stream

  # Append the sheet dimensions
  store_dimensions

  # TODO add SINDEX and NUMBER records.

  store_window2 unless @embedded

  store_eof
end

#data=(val) ⇒ Object

:nodoc:



468
469
470
 
# File 'lib/writeexcel/chart.rb', line 468

def data=(val)  # :nodoc:
  @data = val
end

#embedded ⇒ Object

:nodoc:



472
473
474
 
# File 'lib/writeexcel/chart.rb', line 472

def embedded  # :nodoc:
  @embedded
end

#embedded=(val) ⇒ Object

:nodoc:



476
477
478
 
# File 'lib/writeexcel/chart.rb', line 476

def embedded=(val)  # :nodoc:
  @embedded = val
end

#set_chartarea(params = {}) ⇒ Object

Set the properties of the chart chartarea.



408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
 
# File 'lib/writeexcel/chart.rb', line 408

def set_chartarea(params = {})
  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.has_key?(:color)
    index, rgb = get_color_indices(params[:color])
    if !index.nil?
      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.has_key?(:line_color)
    index, rgb = get_color_indices(params[:line_color])
    if !index.nil?
      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.has_key?(:line_pattern)
    pattern = get_line_pattern(params[:line_pattern])
    area[:line_pattern]     = pattern
    area[:line_options]     = 0x0000
    area[:line_color_index] = 0x4F unless params.has_key?(:line_color)
    area[:visible]          = 1
  end

  # Set the border line weight.
  if params.has_key?(:line_weight)
      weight = get_line_weight(params[:line_weight])
      area[:line_weight]      = weight
      area[:line_options]     = 0x0000
      area[:line_pattern]     = 0x00 unless params.has_key?(:line_pattern)
      area[:line_color_index] = 0x4F unless params.has_key?(:line_color)
      area[:visible]          = 1
  end
end

#set_embedded_config_data ⇒ Object

Setup the default configuration data for an embedded chart.



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
508
509
510
511
512
513
514
515
516
517
 
# File 'lib/writeexcel/chart.rb', line 483

def set_embedded_config_data   # :nodoc:
  @embedded = true

  @chartarea = {
    :visible          => 1,
    :fg_color_index   => 0x4E,
    :fg_color_rgb     => 0xFFFFFF,
    :bg_color_index   => 0x4D,
    :bg_color_rgb     => 0x000000,
    :area_pattern     => 0x0001,
    :area_options     => 0x0001,
    :line_pattern     => 0x0000,
    :line_weight      => 0x0000,
    :line_color_index => 0x4D,
    :line_color_rgb   => 0x000000,
    :line_options     => 0x0009,
  }

  @config = default_config_data.merge({
      :axisparent      => [ 0, 0x01D8, 0x031D, 0x0D79, 0x07E9              ],
      :axisparent_pos  => [ 2, 2, 0x010C, 0x0292, 0x0E46, 0x09FD           ],
      :chart           => [ 0x0000, 0x0000, 0x01847FE8, 0x00F47FE8         ],
      :font_numbers    => [ 5, 10, 0x1DC4, 0x1284, 0x0000                  ],
      :font_series     => [ 6, 10, 0x1DC4, 0x1284, 0x0001                  ],
      :font_title      => [ 7, 12, 0x1DC4, 0x1284, 0x0000                  ],
      :font_axes       => [ 8, 10, 0x1DC4, 0x1284, 0x0001                  ],
      :legend          => [ 0x044E, 0x0E4A, 0x088D, 0x0123, 0x0, 0x1, 0xF  ],
      :legend_pos      => [ 5, 2, 0x044E, 0x0E4A, 0, 0                     ],
      :legend_text     => [ 0xFFFFFFD9, 0xFFFFFFC1, 0, 0, 0x00B1, 0x0000   ],
      :series_text     => [ 0xFFFFFFD9, 0xFFFFFFC1, 0, 0, 0x00B1, 0x1020   ],
      :title_text      => [ 0x060F, 0x004C, 0x038A, 0x016F, 0x0081, 0x1030 ],
      :x_axis_text     => [ 0x07EF, 0x0C8F, 0x153, 0x123, 0x81, 0x00       ],
      :y_axis_text     => [ 0x0057, 0x0564, 0xB5, 0x035D, 0x0281, 0x00, 90 ],
  })
end

#set_legend(params = {}) ⇒ Object

Set the properties of the chart legend.



347
348
349
350
351
352
353
 
# File 'lib/writeexcel/chart.rb', line 347

def set_legend(params = {})
  if params.has_key?(:position)
    if params[:position].downcase == 'none'
      @legend[:visible] = 0
    end
  end
end

#set_plotarea(params = {}) ⇒ Object

Set the properties of the chart plotarea.



358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
 
# File 'lib/writeexcel/chart.rb', line 358

def set_plotarea(params = {})
  return if params.empty?

  area = @plotarea

  # Set the plotarea visibility.
  if params.has_key?(:visible)
    area[:visible] = params[:visible]
    return if area[:visible] == 0
  end

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

  # Set the chart background colour.
  if params.has_key?(:color)
    index, rgb = get_color_indices(params[:color])
    if !index.nil?
      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.has_key?(:line_color)
    index, rgb = get_color_indices(params[:line_color])
    if !index.nil?
      area[:line_color_index] = index
      area[:line_color_rgb]   = rgb
    end
  end

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

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

#set_title(params) ⇒ Object

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          (optional)
:name_formula  (optional)

* :name

  Set the name (title) for the chart. The name is displayed above the
  chart. This property is optional. The default is to have no chart
  title.

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

* :name_formula

  Optional, can be used to link the name to a worksheet cell.
  See "Chart names and links".

      chart.set_title(
        :name          => 'Year End Results',
        :name_formula  => '=Sheet1!$C$1'
      )



334
335
336
337
338
339
340
341
342
 
# File 'lib/writeexcel/chart.rb', line 334

def set_title(params)
  name, encoding = encode_utf16( params[:name], params[:name_encoding])

  formula = parse_series_formula(params[:name_formula])

  @title_name     = name
  @title_encoding = encoding
  @title_formula  = formula
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 => 'Sample length (m)' )

The properties that can be set are:

:name          (optional)
:name_formula  (optional)

* :name

  Set the name (title or caption) for the axis. The name is displayed
  below the X axis. This property is optional. The default is to have
  no axis name.

      chart.set_x_axis( :name => 'Sample length (m)' )

* :name_formula

  Optional, can be used to link the name to a worksheet cell.
  See "Chart names and links".

      chart.set_x_axis(
        :name          => 'Sample length (m)',
        :name_formula  => '=Sheet1!$A$1'
      )

Additional axis properties such as range, divisions and ticks will be made available in later releases.



255
256
257
258
259
260
261
262
 
# File 'lib/writeexcel/chart.rb', line 255

def set_x_axis(params)
  name, encoding = encode_utf16(params[:name], params[:name_encoding])
  formula = parse_series_formula(params[:name_formula])

  @x_axis_name     = name
  @x_axis_encoding = encoding
  @x_axis_formula  = formula
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.

chart.set_y_axis(:name => 'Sample weight (kg)' )

The properties that can be set are:

:name          (optional)
:name_formula  (optional)

* :name

  Set the name (title or caption) for the axis. The name is displayed
  to the left of the Y axis. This property is optional. The default
  is to have no axis name.

      chart.set_y_axis(:name => 'Sample weight (kg)' )

* :name_formula

  Optional, can be used to link the name to a worksheet cell.
  See "Chart names and links".

      chart.set_y_axis(
        :name          => 'Sample weight (kg)',
        :name_formula  => '=Sheet1!$B$1'
      )

Additional axis properties such as range, divisions and ticks will be made available in later releases.



297
298
299
300
301
302
303
304
 
# File 'lib/writeexcel/chart.rb', line 297

def set_y_axis(params)
  name, encoding = encode_utf16(params[:name], params[:name_encoding])
  formula = parse_series_formula(params[:name_formula])

  @y_axis_name     = name
  @y_axis_encoding = encoding
  @y_axis_formula  = formula
end

#using_tmpfile=(val) ⇒ Object

:nodoc:



464
465
466
 
# File 'lib/writeexcel/chart.rb', line 464

def using_tmpfile=(val)  # :nodoc:
  @using_tmpfile = val
end