Class: Writeexcel::Chart::Stock

Inherits:
Writeexcel::Chart show all
Defined in:
lib/writeexcel/charts/stock.rb

Overview

SYNOPSIS

To create a simple Excel file with a Stock chart using WriteExcel:

#!/usr/bin/ruby -w

require 'writeexcel'

workbook  = WriteExcel.new('chart.xls')
worksheet = workbook.add_worksheet

chart     = workbook.add_chart(:type => 'Chart::Stock')

# Add a series for each Open-High-Low-Close.
chart.add_series(:categories => '=Sheet1!$A$2:$A$6', :values => '=Sheet1!$B$2:$B$6')
chart.add_series(:categories => '=Sheet1!$A$2:$A$6', :values => '=Sheet1!$C$2:$C$6')
chart.add_series(:categories => '=Sheet1!$A$2:$A$6', :values => '=Sheet1!$D$2:$D$6')
chart.add_series(:categories => '=Sheet1!$A$2:$A$6', :values => '=Sheet1!$E$2:$E$6')

# Add the worksheet data the chart refers to.
# ... See the full example below.

workbook.close

DESCRIPTION

This module implements Stock charts for WriteExcel. The chart object is created via the Workbook add_chart() method:

chart = workbook.add_chart(:type => 'Chart::Stock')

Once the object is created it can be configured via the following methods that are common to all chart classes:

chart.add_series
chart.set_x_axis
chart.set_y_axis
chart.set_title

These methods are explained in detail in Chart section of WriteExcel. Class specific methods or settings, if any, are explained below.

Stock Chart Methods

There aren’t currently any stock chart specific methods. See the TODO section of Chart section in WriteExcel.

The default Stock chart is an Open-High-Low-Close chart. A series must be added for each of these data sources.

The default Stock chart is in black and white. User defined colours will be added at a later stage.

EXAMPLE

Here is a complete example that demonstrates most of the available features when creating a Stock chart.

 #!/usr/bin/ruby -w

 require 'writeexcel'

 workbook    = WriteExcel.new('chart_stock_ex.xls')
 worksheet   = workbook.add_worksheet
 bold        = workbook.add_format(:bold => 1)
 date_format = workbook.add_format(:num_format => 'dd/mm/yyyy')

 # Add the worksheet data that the charts will refer to.
 headings = [ 'Date', 'Open', 'High', 'Low', 'Close' ]
 data = [
     [ '2009-08-23', 110.75, 113.48, 109.05, 109.40 ],
     [ '2009-08-24', 111.24, 111.60, 103.57, 104.87 ],
     [ '2009-08-25', 104.96, 108.00, 103.88, 106.00 ],
     [ '2009-08-26', 104.95, 107.95, 104.66, 107.91 ],
     [ '2009-08-27', 108.10, 108.62, 105.69, 106.15 ]
]

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

 row = 1
 data.each do |d|
     worksheet.write(row, 0, d[0], date_format)
     worksheet.write(row, 1, d[1])
     worksheet.write(row, 2, d[2])
     worksheet.write(row, 3, d[3])
     worksheet.write(row, 4, d[4])
     row += 1
 end

 # Create a new chart object. In this case an embedded chart.
 chart = workbook.add_chart(:type => 'Chart::Stock', ::embedded => 1)

 # Add a series for each of the Open-High-Low-Close columns.
 chart.add_series(
   :categories => '=Sheet1!$A$2:$A$6',
   :values     => '=Sheet1!$B$2:$B$6',
   :name       => 'Open'
)

 chart.add_series(
   :categories => '=Sheet1!$A$2:$A$6',
   :values     => '=Sheet1!$C$2:$C$6',
   :name       => 'High'
)

 chart.add_series(
   :categories => '=Sheet1!$A$2:$A$6',
   :values     => '=Sheet1!$D$2:$D$6',
   :name       => 'Low'
)

 chart.add_series(
   :categories => '=Sheet1!$A$2:$A$6',
   :values     => '=Sheet1!$E$2:$E$6',
   :name       => 'Close'
)

 # Add a chart title and some axis labels.
 chart.set_title(:name => 'Open-High-Low-Close')
 chart.set_x_axis(:name => 'Date')
 chart.set_y_axis(:name => 'Share price')

 # Insert the chart into the worksheet (with an offset).
 worksheet.insert_chart('F2', chart, 25, 10)

 workbook.close

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

Instance Method Summary collapse

Methods inherited from Writeexcel::Chart

#add_series, #close, #data=, #embedded, #embedded=, factory, #set_chartarea, #set_embedded_config_data, #set_legend, #set_plotarea, #set_title, #set_x_axis, #set_y_axis, #using_tmpfile=

Methods inherited from Worksheet

#activate, #autofilter, #autofilter_name_record_short, #boundsheet, #center_horizontally, #center_vertically, #charts_size, #cleanup, #close, #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, #prepend, #print_caller_info, #set_byte_order, #store_bof, #store_eof, #unpack_record

Methods included from CallerInfo

#caller_info

Methods inherited from WriteFile

#append, #prepend

Constructor Details

#initialize(*args) ⇒ Stock

new()



155
156
157
 
# File 'lib/writeexcel/charts/stock.rb', line 155

def initialize(*args)   # :nodoc:
  super
end

Instance Method Details

#store_chart_type ⇒ Object

_store_chart_type()

Implementation of the abstract method from the specific chart class.

Write the LINE chart BIFF record. A stock chart uses the same LINE record as a line chart but with additional DROPBAR and CHARTLINE records to define the stock style.



169
170
171
172
173
174
175
 
# File 'lib/writeexcel/charts/stock.rb', line 169

def store_chart_type   # :nodoc:
  record = 0x1018     # Record identifier.
  length = 0x0002     # Number of bytes to follow.
  grbit  = 0x0000     # Option flags.

  store_simple(record, length, grbit)
end

#store_marker_dataformat_stream ⇒ Object

_store_marker_dataformat_stream(). Overridden.

This is an implementation of the parent abstract method to define properties of markers, linetypes, pie formats and other.



184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
 
# File 'lib/writeexcel/charts/stock.rb', line 184

def store_marker_dataformat_stream   # :nodoc:
  store_dropbar
  store_begin
  store_lineformat(0x00000000, 0x0000, 0xFFFF, 0x0001, 0x004F)
  store_areaformat(0x00FFFFFF, 0x0000, 0x01, 0x01, 0x09, 0x08)
  store_end

  store_dropbar
  store_begin
  store_lineformat(0x00000000, 0x0000, 0xFFFF, 0x0001, 0x004F)
  store_areaformat(0x0000, 0x00FFFFFF, 0x01, 0x01, 0x08, 0x09)
  store_end

  store_chartline
  store_lineformat(0x00000000, 0x0000, 0xFFFF, 0x0000, 0x004F)


  store_dataformat(0x0000, 0xFFFD, 0x0000)
  store_begin
  store_3dbarshape
  store_lineformat(0x00000000, 0x0005, 0xFFFF, 0x0000, 0x004F)
  store_areaformat(0x00000000, 0x0000, 0x00, 0x01, 0x4D, 0x4D)
  store_pieformat
  store_markerformat(0x00, 0x00, 0x00, 0x00, 0x4D, 0x4D, 0x3C)
  store_end
end