Class: Worksheet

Inherits:
BIFFWriter show all
Defined in:
lib/WriteExcel/worksheet.rb

Overview

class Worksheet

A new worksheet is created by calling the add_worksheet() method from a workbook object:

Examples:

workbook   = WriteExcel.new('file.xls')
worksheet1 = workbook.add_worksheet
worksheet2 = workbook.add_worksheet

Cell notation

Spreadsheet::WriteExcel supports two forms of notation to designate the position of cells: Row-column notation and A1 notation. Row-column notation uses a zero based index for both row and column while A1 notation uses the standard Excel alphanumeric sequence of column letter and 1-based row. For example:

(0, 0)      # The top left cell in row-column notation.
('A1')      # The top left cell in A1 notation.
(1999, 29)  # Row-Column notation.
('AD2000')  # The same cell in A1 notation.

Row-column notation is useful if you are refferring to cells programmatically.

0.upto(9) do |i|
  worksheet.write(i, 0, 'Hello')  # Cells A1 to A10
end

A1 notation is useful for setting up a worksheet manually and for working with formulas.

worksheet.write('H1', 200)
worksheet.write('H2', '=H1+1')

In formulas and applicable methods you can also use the A:A column notation.

worksheet.write('A1', '=SUM(B:B)')

Constant Summary collapse

RowMax = 
65536
ColMax = 
256
StrMax = 
0
Buffer = 
4096
NonAscii = 
/[^!"#\$%&'\(\)\*\+,\-\.\/\:\;<=>\?@0-9A-Za-z_\[\\\]^` ~\0\n]/

Constants inherited from BIFFWriter

BIFFWriter::BIFF_Version, BIFFWriter::BigEndian

Instance Attribute Summary collapse

Attributes inherited from BIFFWriter

#byte_order, #data, #datasize

Instance Method Summary collapse

Methods inherited from BIFFWriter

#add_continue, #add_mso_generic, #append, #get_data, #prepend, #set_byte_order, #store_bof, #store_eof

Constructor Details

#initialize(workbook, name, index, encoding) ⇒ Worksheet

new()

Constructor. Creates a new Worksheet object from a BIFFwriter object



87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
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
210
211
212
213
214
215
216
217
218
219
220
221
222
223
 
# File 'lib/WriteExcel/worksheet.rb', line 87

def initialize(workbook, name, index, encoding)
  super()

  @workbook            = workbook
  @name                = name
  @index               = index
  @encoding            = encoding

  @url_format          = @workbook.url_format
  @parser              = @workbook.parser
  @tempdir             = @workbook.tempdir
  @date_1904           = @workbook.date_1904
  @compatibility       = @workbook.compatibility
  @str_table           = @workbook.str_table

  @table               = []
  @row_data            = {}

  @type                = 0x0000
  @ext_sheets          = []
  @fileclosed          = false
  @offset              = 0
  @xls_rowmax          = RowMax
  @xls_colmax          = ColMax
  @xls_strmax          = StrMax
  @dim_rowmin          = nil
  @dim_rowmax          = nil
  @dim_colmin          = nil
  @dim_colmax          = nil
  @colinfo             = []
  @selection           = [0, 0]
  @panes               = []
  @active_pane         = 3
  @frozen              = 0
  @frozen_no_split     = 1
  @selected            = 0
  @hidden              = 0
  @active              = 0
  @tab_color           = 0

  @first_row           = 0
  @first_col           = 0
  @display_formulas    = 0
  @display_headers     = 1
  @display_zeros       = 1
  @display_arabic      = 0

  @paper_size          = 0x0
  @orientation         = 0x1
  @header              = ''
  @footer              = ''
  @header_encoding     = 0
  @footer_encoding     = 0
  @hcenter             = 0
  @vcenter             = 0
  @margin_header       = 0.50
  @margin_footer       = 0.50
  @margin_left         = 0.75
  @margin_right        = 0.75
  @margin_top          = 1.00
  @margin_bottom       = 1.00

  @title_rowmin        = nil
  @title_rowmax        = nil
  @title_colmin        = nil
  @title_colmax        = nil
  @print_rowmin        = nil
  @print_rowmax        = nil
  @print_colmin        = nil
  @print_colmax        = nil

  @print_gridlines     = 1
  @screen_gridlines    = 1
  @print_headers       = 0

  @page_order          = 0
  @black_white         = 0
  @draft_quality       = 0
  @print_comments      = 0
  @page_start          = 1
  @custom_start        = 0

  @fit_page            = 0
  @fit_width           = 0
  @fit_height          = 0

  @hbreaks             = []
  @vbreaks             = []

  @protect             = 0
  @password            = nil

  @col_sizes           = {}
  @row_sizes           = {}

  @col_formats         = {}
  @row_formats         = {}

  @zoom                = 100
  @print_scale         = 100
  @page_view           = 0

  @leading_zeros       = false

  @outline_row_level   = 0
  @outline_style       = 0
  @outline_below       = 1
  @outline_right       = 1
  @outline_on          = 1

  @write_match         = []

  @object_ids          = []
  @images              = {}
  @images_array        = []
  @charts              = {}
  @charts_array        = []
  @comments            = {}
  @comments_array      = []
  @comments_author     = ''
  @comments_author_enc = 0
  @comments_visible    = 0

  @num_images          = 0
  @image_mso_size      = 0

  @filter_area         = []
  @filter_count        = 0
  @filter_on           = 0
  @filter_cols         = []

  @writing_url         = 0

  @db_indices          = []

  @validations         = []
end

Instance Attribute Details

#active ⇒ Object

Returns the value of attribute active.



76
77
78
 
# File 'lib/WriteExcel/worksheet.rb', line 76

def active
  @active
end

#colinfo ⇒ Object

Returns the value of attribute colinfo.



76
77
78
 
# File 'lib/WriteExcel/worksheet.rb', line 76

def colinfo
  @colinfo
end

#compatibility ⇒ Object (readonly)

Returns the value of attribute compatibility.



79
80
81
 
# File 'lib/WriteExcel/worksheet.rb', line 79

def compatibility
  @compatibility
end

#date_1904=(value) ⇒ Object (writeonly)

Sets the attribute date_1904

Parameters:

  • value —

    the value to set the attribute date_1904 to.



78
79
80
 
# File 'lib/WriteExcel/worksheet.rb', line 78

def date_1904=(value)
  @date_1904 = value
end

#encoding ⇒ Object (readonly)

Returns the value of attribute encoding.



72
73
74
 
# File 'lib/WriteExcel/worksheet.rb', line 72

def encoding
  @encoding
end

#filter_area ⇒ Object (readonly)

Returns the value of attribute filter_area.



73
74
75
 
# File 'lib/WriteExcel/worksheet.rb', line 73

def filter_area
  @filter_area
end

#filter_count ⇒ Object (readonly)

Returns the value of attribute filter_count.



73
74
75
 
# File 'lib/WriteExcel/worksheet.rb', line 73

def filter_count
  @filter_count
end

#hidden ⇒ Object

Returns the value of attribute hidden.



76
77
78
 
# File 'lib/WriteExcel/worksheet.rb', line 76

def hidden
  @hidden
end

#image_mso_size ⇒ Object

Returns the value of attribute image_mso_size.



77
78
79
 
# File 'lib/WriteExcel/worksheet.rb', line 77

def image_mso_size
  @image_mso_size
end

#images_array ⇒ Object (readonly)

Returns the value of attribute images_array.



72
73
74
 
# File 'lib/WriteExcel/worksheet.rb', line 72

def images_array
  @images_array
end

#index ⇒ Object

Returns the value of attribute index.



72
73
74
 
# File 'lib/WriteExcel/worksheet.rb', line 72

def index
  @index
end

#name ⇒ Object (readonly)

Returns the value of attribute name.



72
73
74
 
# File 'lib/WriteExcel/worksheet.rb', line 72

def name
  @name
end

#num_images ⇒ Object

Returns the value of attribute num_images.



77
78
79
 
# File 'lib/WriteExcel/worksheet.rb', line 77

def num_images
  @num_images
end

#object_ids ⇒ Object

Returns the value of attribute object_ids.



77
78
79
 
# File 'lib/WriteExcel/worksheet.rb', line 77

def object_ids
  @object_ids
end

#offset ⇒ Object

Returns the value of attribute offset.



76
77
78
 
# File 'lib/WriteExcel/worksheet.rb', line 76

def offset
  @offset
end

#print_colmax ⇒ Object (readonly)

Returns the value of attribute print_colmax.



75
76
77
 
# File 'lib/WriteExcel/worksheet.rb', line 75

def print_colmax
  @print_colmax
end

#print_colmin ⇒ Object (readonly)

Returns the value of attribute print_colmin.



75
76
77
 
# File 'lib/WriteExcel/worksheet.rb', line 75

def print_colmin
  @print_colmin
end

#print_rowmax ⇒ Object (readonly)

Returns the value of attribute print_rowmax.



75
76
77
 
# File 'lib/WriteExcel/worksheet.rb', line 75

def print_rowmax
  @print_rowmax
end

#print_rowmin ⇒ Object (readonly)

Returns the value of attribute print_rowmin.



75
76
77
 
# File 'lib/WriteExcel/worksheet.rb', line 75

def print_rowmin
  @print_rowmin
end

#selected ⇒ Object

Returns the value of attribute selected.



76
77
78
 
# File 'lib/WriteExcel/worksheet.rb', line 76

def selected
  @selected
end

#selection ⇒ Object

Returns the value of attribute selection.



76
77
78
 
# File 'lib/WriteExcel/worksheet.rb', line 76

def selection
  @selection
end

#title_colmax ⇒ Object (readonly)

Returns the value of attribute title_colmax.



74
75
76
 
# File 'lib/WriteExcel/worksheet.rb', line 74

def title_colmax
  @title_colmax
end

#title_colmin ⇒ Object (readonly)

Returns the value of attribute title_colmin.



74
75
76
 
# File 'lib/WriteExcel/worksheet.rb', line 74

def title_colmin
  @title_colmin
end

#title_rowmax ⇒ Object (readonly)

Returns the value of attribute title_rowmax.



74
75
76
 
# File 'lib/WriteExcel/worksheet.rb', line 74

def title_rowmax
  @title_rowmax
end

#title_rowmin ⇒ Object (readonly)

Returns the value of attribute title_rowmin.



74
75
76
 
# File 'lib/WriteExcel/worksheet.rb', line 74

def title_rowmin
  @title_rowmin
end

#type ⇒ Object (readonly)

Returns the value of attribute type.



72
73
74
 
# File 'lib/WriteExcel/worksheet.rb', line 72

def type
  @type
end

#xf_index ⇒ Object (readonly)

Returns the value of attribute xf_index.



72
73
74
 
# File 'lib/WriteExcel/worksheet.rb', line 72

def xf_index
  @xf_index
end

Instance Method Details

#activate ⇒ Object

activate()

Set this worksheet as the active worksheet, i.e. the worksheet that is displayed when the workbook is opened. Also set it as selected.



427
428
429
430
431
 
# File 'lib/WriteExcel/worksheet.rb', line 427

def activate
  @hidden      = 0  # Active worksheet can't be hidden.
  @selected    = 1
  set_activesheet(@index)
end

#add_write_handler(regexp, code_ref) ⇒ Object

add_write_handler($re, $code_ref)

Allow the user to add their own matches and handlers to the write() method.



1285
1286
1287
1288
1289
 
# File 'lib/WriteExcel/worksheet.rb', line 1285

def add_write_handler(regexp, code_ref)
  #       return unless ref $_[1] eq 'CODE';

  @write_match.push([regexp, code_ref])
end

#autofilter(*args) ⇒ Object

autofilter($first_row, $first_col, $last_row, $last_col)

Set the autofilter area in the worksheet.



846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
 
# File 'lib/WriteExcel/worksheet.rb', line 846

def autofilter(*args)
  # Check for a cell reference in A1 notation and substitute row and column
  if args[0] =~ /^\D/
    args = substitute_cellref(*args)
  end

  return if args.size != 4 # Require 4 parameters

  row1, col1, row2, col2 = args

  # Reverse max and min values if necessary.
  if row2 < row1
    tmp  = row1
    row1 = row2
    row2 = tmp
  end
  if col2 < col1
    tmp  = col1
    col1 = col2
    col2 = col1
  end

  # Store the Autofilter information
  @filter_area = [row1, row2, col1, col2]
  @filter_count = 1 + col2 -col1
end

#center_horizontally(hcenter = nil) ⇒ Object

center_horizontally()

Center the page horizontally.



690
691
692
693
694
695
696
 
# File 'lib/WriteExcel/worksheet.rb', line 690

def center_horizontally(hcenter = nil)
  if hcenter.nil?
    @hcenter = 1
  else
    @hcenter = hcenter
  end
end

#center_vertically(vcenter = nil) ⇒ Object

center_vertically()

Center the page horinzontally.



704
705
706
707
708
709
710
 
# File 'lib/WriteExcel/worksheet.rb', line 704

def center_vertically(vcenter = nil)
  if vcenter.nil?
    @vcenter = 1
  else
    @vcenter = vcenter
  end
end

#close(*sheetnames) ⇒ Object

_close()

Add data to the beginning of the workbook (note the reverse order) and to the end of the workbook.



282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
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
 
# File 'lib/WriteExcel/worksheet.rb', line 282

def close(*sheetnames)
  num_sheets = sheetnames.size

  ################################################
  # Prepend in reverse order!!
  #

  # Prepend the sheet dimensions
  store_dimensions

  # Prepend the autofilter filters.
  store_autofilters

  # Prepend the sheet autofilter info.
  store_autofilterinfo

  # Prepend the sheet filtermode record.
  store_filtermode

  # Prepend the COLINFO records if they exist
  unless @colinfo.empty?
    while (!@colinfo.empty?)
      arrayref = @colinfo.pop
      store_colinfo(*arrayref)
    end
  end

  # Prepend the DEFCOLWIDTH record
  store_defcol

  # Prepend the sheet password
  store_password

  # Prepend the sheet protection
  store_protect
  store_obj_protect

  # Prepend the page setup
  store_setup

  # Prepend the bottom margin
  store_margin_bottom

  # Prepend the top margin
  store_margin_top

  # Prepend the right margin
  store_margin_right

  # Prepend the left margin
  store_margin_left

  # Prepend the page vertical centering
  store_vcenter

  # Prepend the page horizontal centering
  store_hcenter

  # Prepend the page footer
  store_footer

  # Prepend the page header
  store_header

  # Prepend the vertical page breaks
  store_vbreak

  # Prepend the horizontal page breaks
  store_hbreak

  # Prepend WSBOOL
  store_wsbool

  # Prepend the default row height.
  store_defrow

  # Prepend GUTS
  store_guts

  # Prepend GRIDSET
  store_gridset

  # Prepend PRINTGRIDLINES
  store_print_gridlines

  # Prepend PRINTHEADERS
  store_print_headers

  #
  # End of prepend. Read upwards from here.
  ################################################
  # Append
  store_table
  store_images
  store_charts
  store_filters
  store_comments
  store_window2
  store_page_view
  store_zoom
  store_panes(*@panes) if !@panes.nil? && !@panes.empty?
  store_selection(*@selection)
  store_validation_count
  store_validations
  store_tab_color
  store_eof

  # Prepend the BOF and INDEX records
  store_index
  store_bof(0x0010)
end

#comment_params(row, col, string, options = {}) ⇒ Object

_comment_params()

This method handles the additional optional parameters to write_comment() as well as calculating the comment object position and vertices.



5746
5747
5748
5749
5750
5751
5752
5753
5754
5755
5756
5757
5758
5759
5760
5761
5762
5763
5764
5765
5766
5767
5768
5769
5770
5771
5772
5773
5774
5775
5776
5777
5778
5779
5780
5781
5782
5783
5784
5785
5786
5787
5788
5789
5790
5791
5792
5793
5794
5795
5796
5797
5798
5799
5800
5801
5802
5803
5804
5805
5806
5807
5808
5809
5810
5811
5812
5813
5814
5815
5816
5817
5818
5819
5820
5821
5822
5823
5824
5825
5826
5827
5828
5829
5830
5831
5832
5833
5834
5835
5836
5837
5838
5839
5840
5841
5842
5843
5844
5845
5846
5847
5848
5849
5850
5851
5852
5853
5854
5855
5856
5857
5858
5859
5860
5861
5862
5863
5864
5865
5866
5867
5868
5869
5870
5871
5872
5873
5874
5875
5876
5877
5878
5879
5880
5881
5882
5883
5884
5885
5886
5887
5888
5889
 
# File 'lib/WriteExcel/worksheet.rb', line 5746

def comment_params(row, col, string, options = {})   #:nodoc:
  params  = {
    :author          => '',
    :author_encoding => 0,
    :encoding        => 0,
    :color           => nil,
    :start_cell      => nil,
    :start_col       => nil,
    :start_row       => nil,
    :visible         => nil,
    :width           => 129,
    :height          => 75,
    :x_offset        => nil,
    :x_scale         => 1,
    :y_offset        => nil,
    :y_scale         => 1
  }

  # Overwrite the defaults with any user supplied values. Incorrect or
  # misspelled parameters are silently ignored.
  params.update(options)

  # Ensure that a width and height have been set.
  params[:width]  = 129 if params[:width].nil? || params[:width] == 0
  params[:height] = 75  if params[:height].nil? || params[:height] == 0

  # Check that utf16 strings have an even number of bytes.
  if params[:encoding] != 0
    raise "Uneven number of bytes in comment string" if string.length % 2 != 0

    # Change from UTF-16BE to UTF-16LE
    string = string.unpack('n*').pack('v*')
  end

  if params[:author_encoding] != 0
    raise "Uneven number of bytes in author string"  if params[:author] % 2 != 0

    # Change from UTF-16BE to UTF-16LE
    params[:author] = params[:author].unpack('n*').pack('v*')
  end

  # Handle utf8 strings
  if string =~ NonAscii
    string = NKF.nkf('-w16L0 -m0 -W', string)
    params[:encoding] = 1
  end
  if params[:author] =~ NonAscii
    params[:author] = NKF.nkf('-w16L0 -m0 -W', params[:author])
    params[:author_encoding] = 1
  end

  # Limit the string to the max number of chars (not bytes).
  max_len = 32767
  max_len = max_len * 2 if params[:encoding] != 0

  if string.length > max_len
    string = string[0 .. max_len]
  end

  # Set the comment background colour.
  color = params[:color]
  color = Format._get_color(color)
  color = 0x50 if color == 0x7FFF  # Default color.
  params[:color] = color

  # Convert a cell reference to a row and column.
  unless params[:start_cell].nil?
    row, col = substitute_cellref(params[:start_cell])
    params[:start_row] = row
    params[:start_col] = col
  end

  # Set the default start cell and offsets for the comment. These are
  # generally fixed in relation to the parent cell. However there are
  # some edge cases for cells at the, er, edges.
  #
  if params[:start_row].nil?
    case row
    when 0     then params[:start_row] = 0
    when 65533 then params[:start_row] = 65529
    when 65534 then params[:start_row] = 65530
    when 65535 then params[:start_row] = 65531
    else            params[:start_row] = row -1
    end
  end

  if params[:y_offset].nil?
    case row
    when 0     then params[:y_offset]  = 2
    when 65533 then params[:y_offset]  = 4
    when 65534 then params[:y_offset]  = 4
    when 65535 then params[:y_offset]  = 2
    else            params[:y_offset]  = 7
    end
  end

  if params[:start_col].nil?
    case col
    when 253   then params[:start_col] = 250
    when 254   then params[:start_col] = 251
    when 255   then params[:start_col] = 252
    else            params[:start_col] = col + 1
    end
  end

  if params[:x_offset].nil?
    case col
    when 253   then params[:x_offset] = 49
    when 254   then params[:x_offset] = 49
    when 255   then params[:x_offset] = 49
    else            params[:x_offset] = 15
    end
  end

  # Scale the size of the comment box if required. We scale the width and
  # height using the relationship d2 =(d1 -1)*s +1, where d is dimension
  # and s is scale. This gives values that match Excel's behaviour.
  #
  if params[:x_scale] != 0
    params[:width]  = ((params[:width]  -1) * params[:x_scale]) +1
  end

  if params[:y_scale] != 0
    params[:height] = ((params[:height] -1) * params[:y_scale]) +1
  end

  # Calculate the positions of comment object.
  vertices = position_object( params[:start_col],
    params[:start_row],
    params[:x_offset],
    params[:y_offset],
    params[:width],
    params[:height]
  )

  return [row, col, string,
    params[:encoding],
    params[:author],
    params[:author_encoding],
    params[:visible],
    params[:color],
    vertices
  ]
end

#compatibility_mode(compatibility = 1) ⇒ Object

_compatibility_mode()

Set the compatibility mode.

See the explanation in Workbook::compatibility_mode(). This private method is mainly used for test purposes.



403
404
405
 
# File 'lib/WriteExcel/worksheet.rb', line 403

def compatibility_mode(compatibility = 1)
  @compatibility = compatibility
end

#convert_date_time(date_time_string) ⇒ Object

convert_date_time($date_time_string)

The function takes a date and time in ISO8601 “yyyy-mm-ddThh:mm:ss.ss” format and converts it to a decimal number representing a valid Excel date.

Dates and times in Excel are represented by real numbers. The integer part of the number stores the number of days since the epoch and the fractional part stores the percentage of the day in seconds. The epoch can be either 1900 or 1904.

Parameter: Date and time string in one of the following formats:

yyyy-mm-ddThh:mm:ss.ss  # Standard
yyyy-mm-ddT             # Date only
          Thh:mm:ss.ss  # Time only

Returns:

A decimal number representing a valid Excel date, or
undef if the date is invalid.



2593
2594
2595
2596
2597
2598
2599
2600
2601
2602
2603
2604
2605
2606
2607
2608
2609
2610
2611
2612
2613
2614
2615
2616
2617
2618
2619
2620
2621
2622
2623
2624
2625
2626
2627
2628
2629
2630
2631
2632
2633
2634
2635
2636
2637
2638
2639
2640
2641
2642
2643
2644
2645
2646
2647
2648
2649
2650
2651
2652
2653
2654
2655
2656
2657
2658
2659
2660
2661
2662
2663
2664
2665
2666
2667
2668
2669
2670
2671
2672
2673
2674
2675
2676
2677
2678
2679
2680
2681
2682
2683
2684
2685
2686
2687
2688
2689
2690
2691
2692
 
# File 'lib/WriteExcel/worksheet.rb', line 2593

def convert_date_time(date_time_string)
  date_time = date_time_string

  days      = 0 # Number of days since epoch
  seconds   = 0 # Time expressed as fraction of 24h hours in seconds

  # Strip leading and trailing whitespace.
  date_time.sub!(/^\s+/, '')
  date_time.sub!(/\s+$/, '')

  # Check for invalid date char.
  return nil if date_time =~ /[^0-9T:\-\.Z]/

  # Check for "T" after date or before time.
  return nil unless date_time =~ /\dT|T\d/

  # Strip trailing Z in ISO8601 date.
  date_time.sub!(/Z$/, '')

  # Split into date and time.
  date, time = date_time.split(/T/)

  # We allow the time portion of the input DateTime to be optional.
  unless time.nil?
    # Match hh:mm:ss.sss+ where the seconds are optional
    if time =~ /^(\d\d):(\d\d)(:(\d\d(\.\d+)?))?/
      hour   = $1.to_i
      min    = $2.to_i
      sec    = $4.to_f || 0
    else
      return nil # Not a valid time format.
    end

    # Some boundary checks
    return nil if hour >= 24
    return nil if min  >= 60
    return nil if sec  >= 60

    # Excel expresses seconds as a fraction of the number in 24 hours.
    seconds = (hour * 60* 60 + min * 60 + sec) / (24.0 * 60 * 60)
  end

  # We allow the date portion of the input DateTime to be optional.
  return seconds if date == ''

  # Match date as yyyy-mm-dd.
  if date =~ /^(\d\d\d\d)-(\d\d)-(\d\d)$/
    year   = $1.to_i
    month  = $2.to_i
    day    = $3.to_i
  else
    return nil  # Not a valid date format.
  end

  # Set the epoch as 1900 or 1904. Defaults to 1900.
  # Special cases for Excel.
  unless @date_1904
    return      seconds if date == '1899-12-31' # Excel 1900 epoch
    return      seconds if date == '1900-01-00' # Excel 1900 epoch
    return 60 + seconds if date == '1900-02-29' # Excel false leapday
  end


  # We calculate the date by calculating the number of days since the epoch
  # and adjust for the number of leap days. We calculate the number of leap
  # days by normalising the year in relation to the epoch. Thus the year 2000
  # becomes 100 for 4 and 100 year leapdays and 400 for 400 year leapdays.
  #
  epoch   = @date_1904 ? 1904 : 1900
  offset  = @date_1904 ?    4 :    0
  norm    = 300
  range   = year -epoch

  # Set month days and check for leap year.
  mdays   = [31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31]
  leap    = 0
  leap    = 1  if year % 4 == 0 && year % 100 != 0 || year % 400 == 0
  mdays[1]   = 29 if leap != 0

  # Some boundary checks
  return nil if year  < epoch or year  > 9999
  return nil if month < 1     or month > 12
  return nil if day   < 1     or day   > mdays[month -1]

  # Accumulate the number of days since the epoch.
  days = day                               # Add days for current month
  (0 .. month-2).each do |m|
    days += mdays[m]                      # Add days for past months
  end
  days += range *365                       # Add days for past years
  days += ((range)                /  4)    # Add leapdays
  days -= ((range + offset)       /100)    # Subtract 100 year leapdays
  days += ((range + offset + norm)/400)    # Add 400 year leapdays
  days -= leap                             # Already counted above

  # Adjust for Excel erroneously treating 1900 as a leap year.
  days = days + 1 if !@date_1904 and days > 59

  return days + seconds
end

#data_validation(*args) ⇒ Object

data_validation($row, $col, …)

This method handles the interface to Excel data validation. Somewhat ironically the this requires a lot of validation code since the interface is flexible and covers a several types of data validation.

We allow data validation to be called on one cell or a range of cells. The hashref contains the validation parameters and must be the last param:

data_validation($row, $col, {...})
data_validation($first_row, $first_col, $last_row, $last_col, {...})

Returns 0 : normal termination

-1 : insufficient number of arguments
-2 : row or column out of range
-3 : incorrect parameter.



5914
5915
5916
5917
5918
5919
5920
5921
5922
5923
5924
5925
5926
5927
5928
5929
5930
5931
5932
5933
5934
5935
5936
5937
5938
5939
5940
5941
5942
5943
5944
5945
5946
5947
5948
5949
5950
5951
5952
5953
5954
5955
5956
5957
5958
5959
5960
5961
5962
5963
5964
5965
5966
5967
5968
5969
5970
5971
5972
5973
5974
5975
5976
5977
5978
5979
5980
5981
5982
5983
5984
5985
5986
5987
5988
5989
5990
5991
5992
5993
5994
5995
5996
5997
5998
5999
6000
6001
6002
6003
6004
6005
6006
6007
6008
6009
6010
6011
6012
6013
6014
6015
6016
6017
6018
6019
6020
6021
6022
6023
6024
6025
6026
6027
6028
6029
6030
6031
6032
6033
6034
6035
6036
6037
6038
6039
6040
6041
6042
6043
6044
6045
6046
6047
6048
6049
6050
6051
6052
6053
6054
6055
6056
6057
6058
6059
6060
6061
6062
6063
6064
6065
6066
6067
6068
6069
6070
6071
6072
6073
6074
6075
6076
6077
6078
6079
6080
6081
6082
6083
6084
6085
6086
6087
6088
6089
6090
6091
6092
6093
6094
6095
6096
6097
6098
6099
6100
6101
6102
6103
6104
6105
6106
6107
6108
6109
6110
6111
6112
6113
6114
6115
6116
6117
6118
6119
6120
6121
6122
6123
 
# File 'lib/WriteExcel/worksheet.rb', line 5914

def data_validation(*args)
  # Check for a cell reference in A1 notation and substitute row and column
  if args[0] =~ /^\D/
    args = substitute_cellref(*args)
  end

  # Check for a valid number of args.
  return -1 if args.size != 5 && args.size != 3

  # The final hashref contains the validation parameters.
  param = args.pop

  # Make the last row/col the same as the first if not defined.
  row1, col1, row2, col2 = args
  if row2.nil?
    row2 = row1
    col2 = col1
  end

  # Check that row and col are valid without storing the values.
  return -2 if check_dimensions(row1, col1, 1, 1) != 0
  return -2 if check_dimensions(row2, col2, 1, 1) != 0

  # Check that the last parameter is a hash list.
  unless param.kind_of?(Hash)
    #           carp "Last parameter '$param' in data_validation() must be a hash ref";
    return -3
  end

  # List of valid input parameters.
  valid_parameter = {
    :validate          => 1,
    :criteria          => 1,
    :value             => 1,
    :source            => 1,
    :minimum           => 1,
    :maximum           => 1,
    :ignore_blank      => 1,
    :dropdown          => 1,
    :show_input        => 1,
    :input_title       => 1,
    :input_message     => 1,
    :show_error        => 1,
    :error_title       => 1,
    :error_message     => 1,
    :error_type        => 1,
    :other_cells       => 1
  }

  # Check for valid input parameters.
  param.each_key do |param_key|
    unless valid_parameter.has_key?(param_key) 
      #               carp "Unknown parameter '$param_key' in data_validation()";
      return -3
    end
  end

  # Map alternative parameter names 'source' or 'minimum' to 'value'.
  param[:value] = param[:source]  unless param[:source].nil?
  param[:value] = param[:minimum] unless param[:minimum].nil?

  # 'validate' is a required paramter.
  unless param.has_key?(:validate)
    #           carp "Parameter 'validate' is required in data_validation()";
    return -3
  end

  # List of  valid validation types.
  valid_type = {
    'any'             => 0,
    'any value'       => 0,
    'whole number'    => 1,
    'whole'           => 1,
    'integer'         => 1,
    'decimal'         => 2,
    'list'            => 3,
    'date'            => 4,
    'time'            => 5,
    'text length'     => 6,
    'length'          => 6,
    'custom'          => 7
  }

  # Check for valid validation types.
  unless valid_type.has_key?(param[:validate].downcase)
    #           carp "Unknown validation type '$param->{validate}' for parameter " .
    #                "'validate' in data_validation()";
    return -3
  else
    param[:validate] = valid_type[param[:validate].downcase]
  end

  # No action is requied for validation type 'any'.
  # TODO: we should perhaps store 'any' for message only validations.
  return 0 if param[:validate] == 0

  # The list and custom validations don't have a criteria so we use a default
  # of 'between'.
  if param[:validate] == 3 || param[:validate] == 7
    param[:criteria]  = 'between'
    param[:maximum]   = nil
  end

  # 'criteria' is a required parameter.
  unless param.has_key?(:criteria)
    #           carp "Parameter 'criteria' is required in data_validation()";
    return -3
  end

  # List of valid criteria types.
  criteria_type = {
    'between'                     => 0,
    'not between'                 => 1,
    'equal to'                    => 2,
    '='                           => 2,
    '=='                          => 2,
    'not equal to'                => 3,
    '!='                          => 3,
    '<>'                          => 3,
    'greater than'                => 4,
    '>'                           => 4,
    'less than'                   => 5,
    '<'                           => 5,
    'greater than or equal to'    => 6,
    '>='                          => 6,
    'less than or equal to'       => 7,
    '<='                          => 7
  }

  # Check for valid criteria types.
  unless criteria_type.has_key?(param[:criteria].downcase)
    #           carp "Unknown criteria type '$param->{criteria}' for parameter " .
    #                "'criteria' in data_validation()";
    return -3
  else
    param[:criteria] = criteria_type[param[:criteria].downcase]
  end

  # 'Between' and 'Not between' criterias require 2 values.
  if param[:criteria] == 0 || param[:criteria] == 1
    unless param.has_key?(:maximum)
      #               carp "Parameter 'maximum' is required in data_validation() " .
      #                    "when using 'between' or 'not between' criteria";
      return -3
    end
  else
    param[:maximum] = nil
  end

  # List of valid error dialog types.
  error_type = {
    'stop'        => 0,
    'warning'     => 1,
    'information' => 2
  }

  # Check for valid error dialog types.
  if not param.has_key?(:error_type)
    param[:error_type] = 0
  elsif not error_type.has_key?(param[:error_type].downcase)
    #           carp "Unknown criteria type '$param->{error_type}' for parameter " .
    #                "'error_type' in data_validation()";
    return -3
  else
    param[:error_type] = error_type[param[:error_type].downcase]
  end

  # Convert date/times value sif required.
  if param[:validate] == 4 || param[:validate] == 5
    if param[:value] =~ /T/
      date_time = convert_date_time(param[:value])
      if date_time.nil?
        #                   carp "Invalid date/time value '$param->{value}' " .
        #                        "in data_validation()";
        return -3
      else
        param[:value] = date_time
      end
    end
    if !param[:maximum].nil? && param[:maximum] =~ /T/
      date_time = convert_date_time(param[:maximum])

      if date_time.nil?
        #                   carp "Invalid date/time value '$param->{maximum}' " .
        #                        "in data_validation()";
        return -3
      else
        param[:maximum] = date_time
      end
    end
  end

  # Set some defaults if they haven't been defined by the user.
  param[:ignore_blank]  = 1 if param[:ignore_blank].nil?
  param[:dropdown]      = 1 if param[:dropdown].nil?
  param[:show_input]    = 1 if param[:show_input].nil?
  param[:show_error]    = 1 if param[:show_error].nil?

  # These are the cells to which the validation is applied.
  param[:cells] = [[row1, col1, row2, col2]]

  # A (for now) undocumented parameter to pass additional cell ranges.
  if param.has_key?(:other_cells)

    param[:cells].push(param[:other_cells])
  end

  # Store the validation information until we close the worksheet.
  @validations.push(param)
end

#embed_chart(*args) ⇒ Object

embed_chart($row, $col, $filename, $x, $y, $scale_x, $scale_y)

Embed an extracted chart in a worksheet.



4050
4051
4052
4053
4054
4055
4056
4057
4058
4059
4060
4061
4062
4063
4064
4065
4066
4067
4068
4069
4070
 
# File 'lib/WriteExcel/worksheet.rb', line 4050

def embed_chart(*args)
  # Check for a cell reference in A1 notation and substitute row and column
  if args[0] =~ /^\D/
    args = substitute_cellref(*args)
  end

  row         = args[0]
  col         = args[1]
  chart       = args[2]
  x_offset    = args[3] || 0
  y_offset    = args[4] || 0
  scale_x     = args[5] || 1
  scale_y     = args[6] || 1

  raise "Insufficient arguments in embed_chart()" unless args.size >= 3
  #       raise "Couldn't locate $chart: $!"              unless -e $chart;

  @charts[row][col] =  [row, col, chart,
  x_offset, y_offset, scale_x, scale_y, ]

end

#encode_password(password) ⇒ Object

_encode_password($password)

Based on the algorithm provided by Daniel Rentz of OpenOffice.



1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
 
# File 'lib/WriteExcel/worksheet.rb', line 1625

def encode_password(password)
  i = 0
  chars = password.split(//)
  count = chars.size

  chars.each do |char|
    i += 1
    char     = char[0] << i
    low_15   = char & 0x7fff
    high_15  = char & 0x7fff << 15
    high_15  = high_15 >> 15
    char     = low_15 | high_15
  end

  encoded_password  = 0x0000
  chars.each { |c| encoded_password ^= c }
  encoded_password ^= count
  encoded_password ^= 0xCE4B
end

#extract_filter_tokens(expression = nil) ⇒ Object

_extract_filter_tokens($expression)

Extract the tokens from the filter expression. The tokens are mainly non- whitespace groups. The only tricky part is to extract string tokens that contain whitespace and/or quoted double quotes (Excel’s escaped quotes).

Examples: ‘x < 2000’

'x >  2000 and x <  5000'
'x = "foo"'
'x = "foo bar"'
'x = "foo "" bar"'



924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
 
# File 'lib/WriteExcel/worksheet.rb', line 924

def extract_filter_tokens(expression = nil)   #:nodoc:
  return [] unless expression

  #  @tokens = ($expression  =~ /"(?:[^"]|"")*"|\S+/g); #"

  tokens = []
  str = expression
  while str =~ /"(?:[^"]|"")*"|\S+/
    tokens << $&
    str = $~.post_match
  end

  # Remove leading and trailing quotes and unescape other quotes
  tokens.map! do |token|
    token.sub!(/^"/, '')
    token.sub!(/"$/, '')
    token.gsub!(/""/, '"')

    # if token is number, convert to numeric.
    if token =~ /^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/
      token.to_f == token.to_i ? token.to_i : token.to_f
    else
      token
    end
  end

  return tokens
end

#filter_column(col, expression) ⇒ Object

filter_column($column, $criteria, …)

Set the column filter criteria.



879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
 
# File 'lib/WriteExcel/worksheet.rb', line 879

def filter_column(col, expression)
  raise "Must call autofilter() before filter_column()" if @filter_count == 0
  #      raise "Incorrect number of arguments to filter_column()" unless @_ == 2

  # Check for a column reference in A1 notation and substitute.
  if col =~ /^\D/
    # Convert col ref to a cell ref and then to a col number.
    no_use, col = substitute_cellref(col + '1')
  end
  col_first = @filter_area[2]
  col_last  = @filter_area[3]

  # Reject column if it is outside filter range.
  if (col < col_first or col > col_last)
    raise "Column '#{col}' outside autofilter() column range " +
    "(#{col_first} .. #{col_last})";
  end

  tokens = extract_filter_tokens(expression)

  unless (tokens.size == 3 or tokens.size == 7)
    raise "Incorrect number of tokens in expression '#{expression}'"
  end


  tokens = parse_filter_expression(expression, tokens)

  @filter_cols[col] = Array.new(tokens)
  @filter_on        = 1
end

#fit_to_pages(width = 0, height = 0) ⇒ Object

fit_to_pages($width, $height)

Store the vertical and horizontal number of pages that will define the maximum area printed. See also _store_setup() and _store_wsbool() below.



1128
1129
1130
1131
1132
 
# File 'lib/WriteExcel/worksheet.rb', line 1128

def fit_to_pages(width = 0, height = 0)
  @fit_page      = 1
  @fit_width     = width
  @fit_height    = height
end

#freeze_panes(*args) ⇒ Object

freeze_panes()

Set panes and mark them as frozen. See also _store_panes().



550
551
552
553
554
555
556
557
558
559
560
 
# File 'lib/WriteExcel/worksheet.rb', line 550

def freeze_panes(*args)
  # Check for a cell reference in A1 notation and substitute row and column
  if args[0] =~ /^\D/
    args = substitute_cellref(*args)
  end
  # Extra flag indicated a split and freeze.
  @frozen_no_split = 0 if !args[4].nil? && args[4] != 0

  @frozen = 1
  @panes  = args
end

#hide ⇒ Object

hide()

Hide this worksheet.



440
441
442
443
444
445
446
447
 
# File 'lib/WriteExcel/worksheet.rb', line 440

def hide
  @hidden         = 1

  # A hidden worksheet shouldn't be active or selected.
  @selected       = 0
  set_activesheet(0)
  set_firstsheet(0)
end

#hide_gridlines(option = 1) ⇒ Object

hide_gridlines()

Set the option to hide gridlines on the screen and the printed page. There are two ways of doing this in the Excel BIFF format: The first is by setting the DspGrid field of the WINDOW2 record, this turns off the screen and subsequently the print gridline. The second method is to via the PRINTGRIDLINES and GRIDSET records, this turns off the printed gridlines only. The first method is probably sufficient for most cases. The second method is supported for backwards compatibility. Porters take note.



1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
 
# File 'lib/WriteExcel/worksheet.rb', line 1093

def hide_gridlines(option = 1)
  if option == 0
    @print_gridlines  = 1  # 1 = display, 0 = hide
    @screen_gridlines = 1
  elsif option == 1
    @print_gridlines  = 0
    @screen_gridlines = 1
  else
    @print_gridlines  = 0
    @screen_gridlines = 0
  end
end

#hide_zero(val = nil) ⇒ Object

hide_zero()

Hide cell zero values.



1237
1238
1239
 
# File 'lib/WriteExcel/worksheet.rb', line 1237

def hide_zero(val = nil)
  @display_zeros = val.nil? ? 0 : !val
end

#insert_image(*args) ⇒ Object

insert_image($row, $col, $filename, $x, $y, $scale_x, $scale_y)

Insert an image into the worksheet.



4078
4079
4080
4081
4082
4083
4084
4085
4086
4087
4088
4089
4090
4091
4092
4093
4094
4095
4096
4097
4098
4099
 
# File 'lib/WriteExcel/worksheet.rb', line 4078

def insert_image(*args)
  # Check for a cell reference in A1 notation and substitute row and column
  if args[0] =~ /^\D/
    args = substitute_cellref(*args)
  end

  row         = args[0]
  col         = args[1]
  image       = args[2]
  x_offset    = args[3] || 0
  y_offset    = args[4] || 0
  scale_x     = args[5] || 1
  scale_y     = args[6] || 1

  raise "Insufficient arguments in insert_image()" unless args.size >= 3
  raise "Couldn't locate #{image}: $!"             unless test(?e, image)

  @images[row] = {
    col => [ row, col, image, x_offset, y_offset, scale_x, scale_y]
  }

end

#keep_leading_zeros(val = true) ⇒ Object

keep_leading_zeros()

Causes the write() method to treat integers with a leading zero as a string. This ensures that any leading zeros such, as in zip codes, are maintained.



1196
1197
1198
 
# File 'lib/WriteExcel/worksheet.rb', line 1196

def keep_leading_zeros(val = true)
  @leading_zeros = val
end

#merge_cells(*args) ⇒ Object

merge_cells($first_row, $first_col, $last_row, $last_col)

This is an Excel97/2000 method. It is required to perform more complicated merging than the normal align merge in Format.pm



3531
3532
3533
3534
3535
3536
3537
3538
3539
3540
3541
3542
3543
3544
3545
3546
3547
3548
3549
3550
3551
3552
3553
3554
3555
3556
3557
 
# File 'lib/WriteExcel/worksheet.rb', line 3531

def merge_cells(*args)
  # Check for a cell reference in A1 notation and substitute row and column
  if args[0] =~ /^\D/
    args = substitute_cellref(*args)
  end

  record  = 0x00E5                    # Record identifier
  length  = 0x000A                    # Bytes to follow

  cref     = 1                        # Number of refs
  rwFirst  = args[0]                  # First row in reference
  colFirst = args[1]                  # First col in reference
  rwLast   = args[2] || rwFirst       # Last  row in reference
  colLast  = args[3] || colFirst      # Last  col in reference

  # Excel doesn't allow a single cell to be merged
  return if rwFirst == rwLast and colFirst == colLast

  # Swap last row/col with first row/col as necessary
  rwFirst,  rwLast  = rwLast,  rwFirst  if rwFirst  > rwLast
  colFirst, colLast = colLast, colFirst if colFirst > colLast

  header   = [record, length].pack("vv")
  data     = [cref, rwFirst, rwLast, colFirst, colLast].pack("vvvvv")

  append(header, data)
end

#merge_range(*args) ⇒ Object

merge_range($row1, $col1, $row2, $col2, $string, $format, $encoding)

This is a wrapper to ensure correct use of the merge_cells method, i.e., write the first cell of the range, write the formatted blank cells in the range and then call the merge_cells record. Failing to do the steps in this order will cause Excel 97 to crash.



3568
3569
3570
3571
3572
3573
3574
3575
3576
3577
3578
3579
3580
3581
3582
3583
3584
3585
3586
3587
3588
3589
3590
3591
3592
3593
3594
3595
3596
3597
3598
3599
3600
3601
3602
3603
3604
3605
3606
3607
3608
3609
3610
3611
3612
3613
3614
3615
3616
3617
3618
3619
3620
3621
 
# File 'lib/WriteExcel/worksheet.rb', line 3568

def merge_range(*args)
  # Check for a cell reference in A1 notation and substitute row and column
  if args[0] =~ /^\D/
    args = substitute_cellref(*args)
  end
  raise "Incorrect number of arguments" if args.size != 6 and args.size != 7
  raise "Format argument is not a format object" unless args[5].kind_of?(Format)

  rwFirst  = args[0]
  colFirst = args[1]
  rwLast   = args[2]
  colLast  = args[3]
  string   = args[4]
  format   = args[5]
  encoding = args[6] ? 1 : 0

  # Temp code to prevent merged formats in non-merged cells.
  error = "Error: refer to merge_range() in the documentation. " +
  "Can't use previously non-merged format in merged cells"

  raise error if format.used_merge == -1
  format.used_merge = 0   # Until the end of this function.

  # Set the merge_range property of the format object. For BIFF8+.
  format.set_merge_range

  # Excel doesn't allow a single cell to be merged
  raise "Can't merge single cell" if rwFirst  == rwLast and
  colFirst == colLast

  # Swap last row/col with first row/col as necessary
  rwFirst,  rwLast  = rwLast,  rwFirst  if rwFirst  > rwLast
  colFirst, colLast = colLast, colFirst if colFirst > colLast

  # Write the first cell
  if encoding != 0
    write_utf16be_string(rwFirst, colFirst, string, format)
  else
    write(rwFirst, colFirst, string, format)
  end

  # Pad out the rest of the area with formatted blank cells.
  (rwFirst .. rwLast).each do |row|
    (colFirst .. colLast).each do |col|
      next if row == rwFirst and col == colFirst
      write_blank(row, col, format)
    end
  end

  merge_cells(rwFirst, colFirst, rwLast, colLast)

  # Temp code to prevent merged formats in non-merged cells.
  format.used_merge = 1
end

#outline_settings(*args) ⇒ Object

outline_settings($visible, $symbols_below, $symbols_right, $auto_style)

This method sets the properties for outlining and grouping. The defaults correspond to Excel’s defaults.



1652
1653
1654
1655
1656
1657
1658
1659
1660
 
# File 'lib/WriteExcel/worksheet.rb', line 1652

def outline_settings(*args)
  @outline_on    = args[0] || 1
  @outline_below = args[1] || 1
  @outline_right = args[2] || 1
  @outline_style = args[3] || 0

  # Ensure this is a boolean vale for Window2
  @outline_on    = 1 if @outline_on == 0
end

#pack_dv_formula(formula = nil) ⇒ Object

_pack_dv_formula()

Pack the formula used in the DV record. This is the same as an cell formula with some additional header information. Note, DV formulas in Excel use relative addressing (R1C1 and ptgXxxN) however we use the Formula.pm’s default absoulute addressing (A1 and ptgXxx).



6329
6330
6331
6332
6333
6334
6335
6336
6337
6338
6339
6340
6341
6342
6343
6344
6345
6346
6347
6348
6349
6350
6351
6352
6353
6354
6355
6356
6357
6358
6359
6360
6361
6362
6363
6364
6365
6366
6367
6368
6369
6370
6371
6372
6373
6374
6375
 
# File 'lib/WriteExcel/worksheet.rb', line 6329

def pack_dv_formula(formula = nil)   #:nodoc:
  encoding    = 0
  length      = 0
  unused      = 0x0000
  tokens      = []

  # Return a default structure for unused formulas.
  if formula.nil? || formula == ''
    return [0, unused].pack('vv')
  end

  # Pack a list array ref as a null separated string.
  if formula.kind_of?(Array)
    formula   = formula.join("\0")
    formula   = '"' + formula + '"'
  end

  # Strip the = sign at the beginning of the formula string
  formula = formula.to_s unless formula.kind_of?(String)
  formula.sub!(/^=/, '')

  # Parse the formula using the parser in Formula.pm
  parser  = @parser

  # In order to raise formula errors from the point of view of the calling
  # program we use an eval block and re-raise the error from here.
  #
  tokens = parser.parse_formula(formula)   # ????

  #       if ($@) {
  #           $@ =~ s/\n$//;  # Strip the \n used in the Formula.pm die()
  #           croak $@;       # Re-raise the error
  #       }
  #       else {
  #           # TODO test for non valid ptgs such as Sheet2!A1
  #       }

  # Force 2d ranges to be a reference class.
  tokens.each do |t|
    t.sub!(/_range2d/, "_range2dR")
  end

  # Parse the tokens into a formula string.
  formula = parser.parse_tokens(tokens)

  return [formula.length, unused].pack('vv') + formula
end

#pack_dv_string(string = nil, max_length = 0) ⇒ Object

_pack_dv_string()

Pack the strings used in the input and error dialog captions and messages. Captions are limited to 32 characters. Messages are limited to 255 chars.



6291
6292
6293
6294
6295
6296
6297
6298
6299
6300
6301
6302
6303
6304
6305
6306
6307
6308
6309
6310
6311
6312
6313
6314
6315
6316
6317
 
# File 'lib/WriteExcel/worksheet.rb', line 6291

def pack_dv_string(string = nil, max_length = 0)   #:nodoc:
  str_length  = 0
  encoding    = 0

  # The default empty string is "\0".
  if string.nil? || string == ''
    string = "\0"
  end

  # Excel limits DV captions to 32 chars and messages to 255.
  if string.length > max_length
    string = string[0 .. max_length-1]
  end

  str_length = string.length

  # Handle utf8 strings
  if string =~ NonAscii
    require 'jcode'
    $KCODE = 'u'
    str_length = string.jlength
    string = NKF.nkf('-w16L0 -m0 -W', string)
    encoding = 1
  end

  return [str_length, encoding].pack('vC') + string
end

#parse_filter_expression(expression, tokens) ⇒ Object

_parse_filter_expression(expression, @token)

Converts the tokens of a possibly conditional expression into 1 or 2 sub expressions for further parsing.

Examples:

('x', '==', 2000) -> exp1
('x', '>',  2000, 'and', 'x', '<', 5000) -> exp1 and exp2



965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
 
# File 'lib/WriteExcel/worksheet.rb', line 965

def parse_filter_expression(expression, tokens)   #:nodoc:
  # The number of tokens will be either 3 (for 1 expression)
  # or 7 (for 2  expressions).
  #
  if (tokens.size == 7)
    conditional = tokens[3]
    if conditional =~ /^(and|&&)$/
      conditional = 0
    elsif conditional =~ /^(or|\|\|)$/
      conditional = 1
    else
      raise "Token '#{conditional}' is not a valid conditional " +
      "in filter expression '#{expression}'"
    end
    expression_1 = parse_filter_tokens(expression, tokens[0..2])
    expression_2 = parse_filter_tokens(expression, tokens[4..6])
    return [expression_1, conditional, expression_2].flatten
  else
    return parse_filter_tokens(expression, tokens)
  end
end

#position_object(col_start, row_start, x1, y1, width, height) ⇒ Object

_position_object()

Calculate the vertices that define the position of a graphical object within the worksheet.

      +------------+------------+
      |     A      |      B     |
+-----+------------+------------+
|     |(x1,y1)     |            |
|  1  |(A1)._______|______      |
|     |    |              |     |
|     |    |              |     |
+-----+----|    BITMAP    |-----+
|     |    |              |     |
|  2  |    |______________.     |
|     |            |        (B2)|
|     |            |     (x2,y2)|
+---- +------------+------------+

Example of a bitmap that covers some of the area from cell A1 to cell B2.

Based on the width and height of the bitmap we need to calculate 8 vars:

$col_start, $row_start, $col_end, $row_end, $x1, $y1, $x2, $y2.

The width and height of the cells are also variable and have to be taken into account. The values of $col_start and $row_start are passed in from the calling function. The values of $col_end and $row_end are calculated by subtracting the width and height of the bitmap from the width and height of the underlying cells. The vertices are expressed as a percentage of the underlying cell width as follows (rhs values are in pixels):

x1 = X / W *1024
y1 = Y / H *256
x2 = (X-1) / W *1024
y2 = (Y-1) / H *256

Where:  X is distance from the left side of the underlying cell
        Y is distance from the top of the underlying cell
        W is the width of the cell
        H is the height of the cell

Note: the SDK incorrectly states that the height should be expressed as a percentage of 1024.



4152
4153
4154
4155
4156
4157
4158
4159
4160
4161
4162
4163
4164
4165
4166
4167
4168
4169
4170
4171
4172
4173
4174
4175
4176
4177
4178
4179
4180
4181
4182
4183
4184
4185
4186
4187
4188
4189
4190
4191
4192
4193
4194
4195
4196
4197
4198
4199
4200
4201
4202
4203
4204
4205
4206
4207
4208
4209
4210
4211
4212
4213
4214
4215
4216
4217
4218
4219
4220
4221
4222
4223
4224
4225
 
# File 'lib/WriteExcel/worksheet.rb', line 4152

def position_object(col_start, row_start, x1, y1, width, height)   #:nodoc:
  # col_start;  # Col containing upper left corner of object
  # x1;         # Distance to left side of object

  # row_start;  # Row containing top left corner of object
  # y1;         # Distance to top of object

  # col_end;    # Col containing lower right corner of object
  # x2;         # Distance to right side of object

  # row_end;    # Row containing bottom right corner of object
  # y2;         # Distance to bottom of object

  # width;      # Width of image frame
  # height;     # Height of image frame

  # Adjust start column for offsets that are greater than the col width
  while x1 >= size_col(col_start)
    x1 = x1 - size_col(col_start)
    col_start = col_start + 1
  end

  # Adjust start row for offsets that are greater than the row height
  while y1 >= size_row(row_start)
    y1 = y1 - size_row(row_start)
    row_start = row_start + 1
  end

  # Initialise end cell to the same as the start cell
  col_end    = col_start
  row_end    = row_start

  width      = width  + x1 -1
  height     = height + y1 -1

  # Subtract the underlying cell widths to find the end cell of the image
  while width >= size_col(col_end)
    width   -= size_col(col_end)
    col_end += 1
  end

  # Subtract the underlying cell heights to find the end cell of the image
  while height >= size_row(row_end)
    height  -= size_row(row_end)
    row_end += 1
  end

  # Bitmap isn't allowed to start or finish in a hidden cell, i.e. a cell
  # with zero eight or width.
  #
  return if size_col(col_start) == 0
  return if size_col(col_end)   == 0
  return if size_row(row_start) == 0
  return if size_row(row_end)   == 0

  # Convert the pixel values to the percentage value expected by Excel
  x1 = 1024.0 * x1     / size_col(col_start)
  y1 =  256.0 * y1     / size_row(row_start)
  x2 = 1024.0 * width  / size_col(col_end)
  y2 =  256.0 * height / size_row(row_end)

  # Simulate ceil() without calling POSIX::ceil().
  x1 = (x1 +0.5).to_i
  y1 = (y1 +0.5).to_i
  x2 = (x2 +0.5).to_i
  y2 = (y2 +0.5).to_i

  return [
    col_start, x1,
    row_start, y1,
    col_end,   x2,
    row_end,   y2
  ]
end

#prepare_charts ⇒ Object

_prepare_charts()

Turn the HoH that stores the charts into an array for easier handling.



4705
4706
4707
4708
4709
4710
4711
4712
4713
4714
4715
4716
4717
4718
4719
4720
4721
4722
4723
 
# File 'lib/WriteExcel/worksheet.rb', line 4705

def prepare_charts   #:nodoc:
  count  = 0
  charts = []

  # We sort the charts by row and column but that isn't strictly required.
  #
  rows = @charts.keys.sort
  rows.each do |row|
    cols = @charts[row].keys.sort
    cols.each do |col|
      charts.push(@charts[row][col])
      count += 1
    end
  end

  @charts       = {}
  @charts_array = charts
  count
end

#prepare_comments ⇒ Object

_prepare_comments()

Turn the HoH that stores the comments into an array for easier handling.



4677
4678
4679
4680
4681
4682
4683
4684
4685
4686
4687
4688
4689
4690
4691
4692
4693
4694
4695
4696
 
# File 'lib/WriteExcel/worksheet.rb', line 4677

def prepare_comments   #:nodoc:
  count   = 0
  comments = []

  # We sort the comments by row and column but that isn't strictly required.
  #
  rows = @comments.keys.sort
  rows.each do |row|
    cols = @comments[row].keys.sort
    cols.each do |col|
      comments.push(@comments[row][col])
      count += 1
    end
  end

  @comments       = {}
  @comments_array = comments

  return count
end

#prepare_images ⇒ Object

_prepare_images()

Turn the HoH that stores the images into an array for easier handling.



4648
4649
4650
4651
4652
4653
4654
4655
4656
4657
4658
4659
4660
4661
4662
4663
4664
4665
4666
4667
4668
 
# File 'lib/WriteExcel/worksheet.rb', line 4648

def prepare_images   #:nodoc:
  count  = 0
  images = []

  # We sort the images by row and column but that isn't strictly required.
  #
  rows = @images.keys.sort

  rows.each do |row|
    cols = @images[row].keys.sort
    cols.each do |col|
      images.push(@images[row][col])
      count += 1
    end
  end

  @images       = {}
  @images_array = images

  return count
end

#print_across(val = nil) ⇒ Object

print_across()

Set the order in which pages are printed.



1247
1248
1249
 
# File 'lib/WriteExcel/worksheet.rb', line 1247

def print_across(val = nil)
  @page_order = val.nil? ? 1 : val
end

#print_area(*args) ⇒ Object

print_area($first_row, $first_col, $last_row, $last_col)

Set the area of each worksheet that will be printed. See also the _store_names() methods in Workbook.pm.



829
830
831
832
833
834
835
836
837
838
 
# File 'lib/WriteExcel/worksheet.rb', line 829

def print_area(*args)
  # Check for a cell reference in A1 notation and substitute row and column
  if args =~ /^\D/
    args = substitute_cellref(*args)
  end

  return if args.size != 4 # Require 4 parameters

  @print_rowmin, @print_colmin, @print_rowmax, @print_colmax = args
end

#print_row_col_headers(option = nil) ⇒ Object

print_row_col_headers()

Set the option to print the row and column headers on the printed page. See also the _store_print_headers() method below.



1113
1114
1115
1116
1117
1118
1119
 
# File 'lib/WriteExcel/worksheet.rb', line 1113

def print_row_col_headers(option = nil)
  if option.nil?
    @print_headers = 1
  else
    @print_headers = option
  end
end

#protect(password = nil) ⇒ Object

protect($password)

Set the worksheet protection flag to prevent accidental modification and to hide formulas if the locked and hidden format properties have been set.



471
472
473
474
 
# File 'lib/WriteExcel/worksheet.rb', line 471

def protect(password = nil)
  @protect   = 1
  @password  = encode_password(password) unless password.nil?
end

#repeat_columns(*args) ⇒ Object

repeat_columns($first_col, $last_col)

Set the columns to repeat at the left hand side of each printed page. See also the _store_names() methods in Workbook.pm.



808
809
810
811
812
813
814
815
816
817
818
819
820
 
# File 'lib/WriteExcel/worksheet.rb', line 808

def repeat_columns(*args)
  # Check for a cell reference in A1 notation and substitute row and column
  if args =~ /^\D/
    args = substitute_cellref(*args)

    # Returned values $row1 and $row2 aren't required here. Remove them.
    args.shift        # $row1
    args.delete_at(1) # $row2
  end

  @title_colmin  = args[0]
  @title_colmax  = args[1] || args[0] # Second col is optional
end

#repeat_formula(*args) ⇒ Object

repeat_formula($row, $col, $formula, $format, ($pattern => $replacement,…))

Write a formula to the specified row and column (zero indexed) by substituting $pattern $replacement pairs in the $formula created via store_formula(). This allows the user to repetitively rewrite a formula without the significant overhead of parsing.

Returns 0 : normal termination

-1 : insufficient number of arguments
-2 : row or column out of range



2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
2083
2084
2085
2086
2087
2088
2089
2090
2091
2092
2093
2094
2095
2096
2097
2098
2099
2100
2101
2102
2103
2104
2105
2106
2107
2108
2109
2110
2111
2112
2113
2114
2115
2116
2117
2118
2119
2120
2121
2122
2123
2124
2125
2126
2127
2128
2129
2130
2131
2132
2133
2134
2135
2136
2137
2138
2139
2140
2141
2142
2143
2144
 
# File 'lib/WriteExcel/worksheet.rb', line 2048

def repeat_formula(*args)
  # Check for a cell reference in A1 notation and substitute row and column
  if args[0] =~ /^\D/
    args = substitute_cellref(args)
  end

  return -1 if (args.size < 2)   # Check the number of args

  record      = 0x0006   # Record identifier
  # length                 # Bytes to follow

  row         = args.shift    # Zero indexed row
  col         = args.shift    # Zero indexed column
  formula_ref = args.shift    # Array ref with formula tokens
  format      = args.shift    # XF format
  pairs       = args          # Pattern/replacement pairs

  # Enforce an even number of arguments in the pattern/replacement list
  raise "Odd number of elements in pattern/replacement list" if pairs.size % 2 != 0

  # Check that formula is an array ref
  raise "Not a valid formula" unless formula_ref.kind_of?(Array)

  tokens  = formula_ref.join("\t").split("\t")

  # Ensure that there are tokens to substitute
  raise "No tokens in formula" if tokens.empty?


  # As a temporary and undocumented measure we allow the user to specify the
  # result of the formula by appending a result => $value pair to the end
  # of the arguments.
  value = nil
  if pairs[-2] == 'result'
    value = pairs.pop
    pairs.pop
  end

  while (!pairs.empty?)
    pattern = pairs.shift
    replace = pairs.shift

    tokens.each do |token|
      break if token.sub!(pattern, replace)
    end
  end

  # Change the parameters in the formula cached by the Formula.pm object
  formula   = @parser.parse_tokens(tokens)

  raise "Unrecognised token in formula" unless formula

  xf        = xf_record_index(row, col, format) # The cell format
  chn       = 0x0000                          # Must be zero
  is_string = 0                               # Formula evaluates to str
  #  num                                      # Current value of formula
  #  grbit                                    # Option flags

  # Excel normally stores the last calculated value of the formula in $num.
  # Clearly we are not in a position to calculate this "a priori". Instead
  # we set $num to zero and set the option flags in $grbit to ensure
  # automatic calculation of the formula when the file is opened.
  # As a workaround for some non-Excel apps we also allow the user to
  # specify the result of the formula.
  #
  num, grbit, is_string = encode_formula_result(value)

  # Check that row and col are valid and store max and min values
  return -2 if check_dimensions(row, col) != 0


  formlen   = formula.length     # Length of the binary string
  length    = 0x16 + formlen     # Length of the record data

  header    = [record, length].pack("vv")
  data      = [row, col, xf].pack("vvv") +
              num                        +
              [grbit, chn, formlen].pack('vVv')

  # The STRING record if the formula evaluates to a string.
  string  = ''
  string  = encode_formula_result(value) if is_string != 0


  # Store the data or write immediately depending on the compatibility mode.
  if @compatibility != 0
    string = ''
    string = get_formula_string(value) if is_string != 0
    tmp = []
    tmp[col] = header + data + formula + string
    @table[row] = tmp
  else
    append(header, data, formula, string)
  end

  return 0
end

#repeat_rows(first_row, last_row = nil) ⇒ Object

repeat_rows($first_row, $last_row)

Set the rows to repeat at the top of each printed page. See also the _store_name_xxxx() methods in Workbook.pm.



796
797
798
799
 
# File 'lib/WriteExcel/worksheet.rb', line 796

def repeat_rows(first_row, last_row = nil)
  @title_rowmin  = first_row
  @title_rowmax  = last_row || first_row # Second row is optional
end

#right_to_left(val = nil) ⇒ Object

right_to_left()

Display the worksheet right to left for some eastern versions of Excel.



1227
1228
1229
 
# File 'lib/WriteExcel/worksheet.rb', line 1227

def right_to_left(val = nil)
  @display_arabic = val.nil? ? 1 : val
end

#select ⇒ Object

select()

Set this worksheet as a selected worksheet, i.e. the worksheet has its tab highlighted.



414
415
416
417
 
# File 'lib/WriteExcel/worksheet.rb', line 414

def select
  @hidden         = 0  # Selected worksheet can't be hidden.
  @selected       = 1
end

#set_column(*args) ⇒ Object

set_column($first_col, $last_col, $width, $format, $hidden, $level, $collapsed) set_column($A1_notation, $width, $format, $hidden, $level, $collapsed)

Set the width of a single column or a range of columns. See also: _store_colinfo



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
518
519
520
521
522
523
524
525
526
 
# File 'lib/WriteExcel/worksheet.rb', line 484

def set_column(*args)
  data = args
  cell = data[0]

  # Check for a cell reference in A1 notation and substitute row and column
  if cell =~ /^\D/
    data = substitute_cellref(*args)

    # Returned values $row1 and $row2 aren't required here. Remove them.
    data.shift        # $row1
    data.delete_at(1) # $row2
  end

  return if data.size < 3  # Ensure at least $firstcol, $lastcol and $width
  return if data[0].nil?   # Columns must be defined.
  return if data[1].nil?

  # Assume second column is the same as first if 0. Avoids KB918419 bug.
  data[1] = data[0] if data[1] == 0

  # Ensure 2nd col is larger than first. Also for KB918419 bug.
  data[0], data[1] = data[1], data[0] if data[0] > data[1]

  # Limit columns to Excel max of 255.
  data[0] = ColMax - 1 if data[0] > ColMax - 1
  data[1] = ColMax - 1 if data[1] > ColMax - 1

  @colinfo.push(data)

  # Store the col sizes for use when calculating image vertices taking
  # hidden columns into account. Also store the column formats.
  #
  firstcol, lastcol, width, format, hidden = data

  width  ||= 0                    # Ensure width isn't undef.
  hidden ||= 0
  width = 0 if hidden > 1         # Set width to zero if col is hidden

  (firstcol .. lastcol).each do |col|
    @col_sizes[col]   = width
    @col_formats[col] = format unless format.nil?
  end
end

#set_comments_author(author = '', author_enc = 0) ⇒ Object

set_comments_author()

Set the default author of the cell comments.



1216
1217
1218
1219
 
# File 'lib/WriteExcel/worksheet.rb', line 1216

def set_comments_author(author = '', author_enc = 0)
  @comments_author     = author
  @comments_author_enc = author_enc
end

#set_first_row_column(row = 0, col = 0) ⇒ Object

set_first_row_column()

Set the topmost and leftmost visible row and column. TODO: Document this when tested fully for interaction with panes.



1271
1272
1273
1274
1275
1276
1277
 
# File 'lib/WriteExcel/worksheet.rb', line 1271

def set_first_row_column(row = 0, col = 0)
  row = RowMax - 1  if row > RowMax - 1
  col = ColMax - 1  if col > ColMax - 1

  @first_row = row
  @first_col = col
end

#set_first_sheet ⇒ Object

set_first_sheet()

Set this worksheet as the first visible sheet. This is necessary when there are a large number of worksheets and the activated worksheet is not visible on the screen.



458
459
460
461
 
# File 'lib/WriteExcel/worksheet.rb', line 458

def set_first_sheet
  @hidden      = 0  # Active worksheet can't be hidden.
  set_firstsheet(@index)
end

#set_footer(string = '', margin = 0.50, encoding = 0) ⇒ Object

set_footer()

Set the page footer caption and optional margin.



665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
 
# File 'lib/WriteExcel/worksheet.rb', line 665

def set_footer(string = '', margin = 0.50, encoding = 0)
  limit    = encoding != 0 ? 255 *2 : 255

  # Handle utf8 strings
  if string =~ NonAscii
    string = NKF.nkf('-w16B0 -m0 -W', string)
    encoding = 1
  end

  if string.length >= limit
    #           carp 'Header string must be less than 255 characters';
    return
  end

  @footer          = string
  @margin_footer   = margin
  @footer_encoding = encoding
end

#set_h_pagebreaks(breaks) ⇒ Object

set_h_pagebreaks(@breaks)

Store the horizontal page breaks on a worksheet.



1140
1141
1142
 
# File 'lib/WriteExcel/worksheet.rb', line 1140

def set_h_pagebreaks(breaks)
  @hbreaks.push(breaks)
end

#set_header(string = '', margin = 0.50, encoding = 0) ⇒ Object

set_header()

Set the page header caption and optional margin.



639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
 
# File 'lib/WriteExcel/worksheet.rb', line 639

def set_header(string = '', margin = 0.50, encoding = 0)
  limit    = encoding != 0 ? 255 *2 : 255

  # Handle utf8 strings
  if string =~ NonAscii
    string = NKF.nkf('-w16B0 -m0 -W', string)
    encoding = 1
  end
  
  if string.length >= limit
    #           carp 'Header string must be less than 255 characters';
    return
  end

  @header          = string
  @margin_header   = margin
  @header_encoding = encoding
end

#set_landscape ⇒ Object

set_landscape()

Set the page orientation as landscape.



595
596
597
 
# File 'lib/WriteExcel/worksheet.rb', line 595

def set_landscape
  @orientation = 0
end

#set_margin_bottom(margin = 1.00) ⇒ Object

set_margin_bottom()

Set the bottom margin in inches.



785
786
787
 
# File 'lib/WriteExcel/worksheet.rb', line 785

def set_margin_bottom(margin = 1.00)
  @margin_bottom = margin
end

#set_margin_left(margin = 0.75) ⇒ Object

set_margin_left()

Set the left margin in inches.



754
755
756
 
# File 'lib/WriteExcel/worksheet.rb', line 754

def set_margin_left(margin = 0.75)
  @margin_left = margin
end

#set_margin_right(margin = 0.75) ⇒ Object

set_margin_right()

Set the right margin in inches.



765
766
767
 
# File 'lib/WriteExcel/worksheet.rb', line 765

def set_margin_right(margin = 0.75)
  @margin_right = margin
end

#set_margin_top(margin = 1.00) ⇒ Object

set_margin_top()

Set the top margin in inches.



775
776
777
 
# File 'lib/WriteExcel/worksheet.rb', line 775

def set_margin_top(margin = 1.00)
  @margin_top = margin
end

#set_margins(margin) ⇒ Object

set_margins()

Set all the page margins to the same value in inches.



718
719
720
721
722
723
 
# File 'lib/WriteExcel/worksheet.rb', line 718

def set_margins(margin)
  set_margin_left(margin)
  set_margin_right(margin)
  set_margin_top(margin)
  set_margin_bottom(margin)
end

#set_margins_LR(margin) ⇒ Object

set_margins_LR()

Set the left and right margins to the same value in inches.



731
732
733
734
 
# File 'lib/WriteExcel/worksheet.rb', line 731

def set_margins_LR(margin)
  set_margin_left(margin)
  set_margin_right(margin)
end

#set_margins_TB(margin) ⇒ Object

set_margins_TB()

Set the top and bottom margins to the same value in inches.



742
743
744
745
 
# File 'lib/WriteExcel/worksheet.rb', line 742

def set_margins_TB(margin)
  set_margin_top(margin)
  set_margin_bottom(margin)
end

#set_page_view(val = nil) ⇒ Object

set_page_view()

Set the page view mode for Mac Excel.



606
607
608
 
# File 'lib/WriteExcel/worksheet.rb', line 606

def set_page_view(val = nil)
  @page_view = val.nil? ? 1 : val
end

#set_paper(paper_size = 0) ⇒ Object

set_paper()

Set the paper type. Ex. 1 = US Letter, 9 = A4



629
630
631
 
# File 'lib/WriteExcel/worksheet.rb', line 629

def set_paper(paper_size = 0)
  @paper_size = paper_size
end

#set_portrait ⇒ Object

set_portrait()

Set the page orientation as portrait.



584
585
586
 
# File 'lib/WriteExcel/worksheet.rb', line 584

def set_portrait
  @orientation = 1
end

#set_print_scale(scale = 100) ⇒ Object

set_print_scale($scale)

Set the scale factor for the printed page.



1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
 
# File 'lib/WriteExcel/worksheet.rb', line 1176

def set_print_scale(scale = 100)
  # Confine the scale to Excel's range
  if scale < 10 or scale > 400
    #           carp "Print scale $scale outside range: 10 <= zoom <= 400";
    scale = 100
  end

  # Turn off "fit to page" option
  @fit_page    = 0

  @print_scale = scale.to_i
end

#set_row(row, height = nil, format = nil, hidden = 0, level = 0, collapsed = 0) ⇒ Object

set_row($row, $height, $format, $hidden, $level, collapsed)

row       : Row Number
height    : Format object
format    : Format object
hidden    : Hidden flag
level     : Outline level
collapsed : Collapsed row

This method is used to set the height and XF format for a row. Writes the BIFF record ROW.



2706
2707
2708
2709
2710
2711
2712
2713
2714
2715
2716
2717
2718
2719
2720
2721
2722
2723
2724
2725
2726
2727
2728
2729
2730
2731
2732
2733
2734
2735
2736
2737
2738
2739
2740
2741
2742
2743
2744
2745
2746
2747
2748
2749
2750
2751
2752
2753
2754
2755
2756
2757
2758
2759
2760
2761
2762
2763
2764
2765
2766
2767
2768
2769
2770
2771
2772
2773
2774
2775
 
# File 'lib/WriteExcel/worksheet.rb', line 2706

def set_row(row, height = nil, format = nil, hidden = 0, level = 0, collapsed = 0)
  record      = 0x0208               # Record identifier
  length      = 0x0010               # Number of bytes to follow

  colMic      = 0x0000               # First defined column
  colMac      = 0x0000               # Last defined column
  # miyRw;                           # Row height
  irwMac      = 0x0000               # Used by Excel to optimise loading
  reserved    = 0x0000               # Reserved
  grbit       = 0x0000               # Option flags
  # ixfe;                            # XF index

  return if row.nil?

  # Check that row and col are valid and store max and min values
  return -2 if check_dimensions(row, 0, 0, 1) != 0

  # Check for a format object
  if format.kind_of?(Format)
    ixfe = format.get_xf_index
  else
    ixfe = 0x0F
  end

  # Set the row height in units of 1/20 of a point. Note, some heights may
  # not be obtained exactly due to rounding in Excel.
  #
  unless height.nil?
    miyRw = height *20
  else
    miyRw = 0xff # The default row height
    height = 0
  end

  # Set the limits for the outline levels (0 <= x <= 7).
  level = 0 if level < 0
  level = 7 if level > 7

  @outline_row_level = level if level > @outline_row_level

  # Set the options flags.
  # 0x10: The fCollapsed flag indicates that the row contains the "+"
  #       when an outline group is collapsed.
  # 0x20: The fDyZero height flag indicates a collapsed or hidden row.
  # 0x40: The fUnsynced flag is used to show that the font and row heights
  #       are not compatible. This is usually the case for WriteExcel.
  # 0x80: The fGhostDirty flag indicates that the row has been formatted.
  #
  grbit |= level
  grbit |= 0x0010 if collapsed != 0
  grbit |= 0x0020 if hidden    != 0
  grbit |= 0x0040
  grbit |= 0x0080 unless format.nil?
  grbit |= 0x0100

  header = [record, length].pack("vv")
  data   = [row, colMic, colMac, miyRw, irwMac, reserved, grbit, ixfe].pack("vvvvvvvv")

  # Store the data or write immediately depending on the compatibility mode.
  if @compatibility != 0
    @row_data[row] = header + data
  else
    append(header, data)
  end

  # Store the row sizes for use when calculating image vertices.
  # Also store the column formats.
  @row_sizes[row]   = height
  @row_formats[row] = format unless format.nil?
end

#set_selection(*args) ⇒ Object

set_selection()

Set which cell or cells are selected in a worksheet: see also the sub _store_selection



535
536
537
538
539
540
541
 
# File 'lib/WriteExcel/worksheet.rb', line 535

def set_selection(*args)
  # Check for a cell reference in A1 notation and substitute row and column
  if args[0] =~ /^\D/
    args = substitute_cellref(*args)
  end
  @selection = args
end

#set_start_page(start_page = nil) ⇒ Object

set_start_page()

Set the start page number.



1257
1258
1259
1260
1261
1262
 
# File 'lib/WriteExcel/worksheet.rb', line 1257

def set_start_page(start_page = nil)
  return if start_page.nil?

  @page_start    = start_page
  @custom_start  = 1
end

#set_tab_color(colour) ⇒ Object

set_tab_color()

Set the colour of the worksheet colour.



617
618
619
620
621
 
# File 'lib/WriteExcel/worksheet.rb', line 617

def set_tab_color(colour)
  color = Format._get_color(colour)
  color = 0 if color == 0x7FFF # Default color.
  @tab_color = color
end

#set_v_pagebreaks(breaks) ⇒ Object

set_v_pagebreaks(@breaks)

Store the vertical page breaks on a worksheet.



1150
1151
1152
 
# File 'lib/WriteExcel/worksheet.rb', line 1150

def set_v_pagebreaks(breaks)
  @vbreaks.push(breaks)
end

#set_zoom(scale = 100) ⇒ Object

set_zoom($scale)

Set the worksheet zoom factor.



1160
1161
1162
1163
1164
1165
1166
1167
1168
 
# File 'lib/WriteExcel/worksheet.rb', line 1160

def set_zoom(scale = 100)
  # Confine the scale to Excel's range
  if scale < 10 or scale > 400
    #           carp "Zoom factor $scale outside range: 10 <= zoom <= 400";
    scale = 100
  end

  @zoom = scale.to_i
end

#show_comments(val = nil) ⇒ Object

show_comments()

Make any comments in the worksheet visible.



1206
1207
1208
 
# File 'lib/WriteExcel/worksheet.rb', line 1206

def show_comments(val = nil)
  @comments_visible = val.nil? ? 1 : val
end

#split_panes(*args) ⇒ Object

split_panes()

Set panes and mark them as split. See also _store_panes().



569
570
571
572
573
 
# File 'lib/WriteExcel/worksheet.rb', line 569

def split_panes(*args)
  @frozen            = 0
  @frozen_no_split   = 0
  @panes             = args
end

#store_autofilter(index, operator_1, token_1, join = nil, operator_2 = nil, token_2 = nil) ⇒ Object

_store_autofilter()

my $index           = $_[0];
my $operator_1      = $_[1];
my $token_1         = $_[2];
my $join            = $_[3]; # And/Or
my $operator_2      = $_[4];
my $token_2         = $_[5];

Function to write worksheet AUTOFILTER records. These contain 2 Biff Doper structures to represent the 2 possible filter conditions.



4449
4450
4451
4452
4453
4454
4455
4456
4457
4458
4459
4460
4461
4462
4463
4464
4465
4466
4467
4468
4469
4470
4471
4472
4473
4474
4475
4476
4477
4478
4479
4480
4481
4482
4483
4484
4485
4486
4487
4488
4489
4490
4491
4492
4493
4494
4495
4496
4497
4498
4499
4500
4501
4502
4503
4504
4505
4506
4507
4508
4509
4510
4511
4512
4513
4514
4515
4516
4517
4518
4519
4520
4521
4522
4523
4524
4525
 
# File 'lib/WriteExcel/worksheet.rb', line 4449

def store_autofilter(index, operator_1, token_1,   #:nodoc:
                               join = nil, operator_2 = nil, token_2 = nil)
  record          = 0x009E
  length          = 0x0000

  top10_active    = 0
  top10_direction = 0
  top10_percent   = 0
  top10_value     = 101

  grbit       = join || 0
  optimised_1 = 0
  optimised_2 = 0
  doper_1     = ''
  doper_2     = ''
  string_1    = ''
  string_2    = ''

  # Excel used an optimisation in the case of a simple equality.
  optimised_1 = 1 if                      operator_1 == 2
  optimised_2 = 1 if !operator_2.nil? and operator_2 == 2

  # Convert non-simple equalities back to type 2. See  _parse_filter_tokens().
  operator_1 = 2 if                      operator_1 == 22
  operator_2 = 2 if !operator_2.nil? and operator_2 == 22

  # Handle a "Top" style expression.
  if operator_1 >= 30
    # Remove the second expression if present.
    operator_2 = nil
    token_2    = nil

    # Set the active flag.
    top10_active    = 1

    if (operator_1 == 30 or operator_1 == 31)
      top10_direction = 1
    end

    if (operator_1 == 31 or operator_1 == 33)
      top10_percent = 1
    end

    if (top10_direction == 1)
      operator_1 = 6
    else
      operator_1 = 3
    end

    top10_value     = token_1.to_i
    token_1         = 0
  end

  grbit     |= optimised_1      << 2
  grbit     |= optimised_2      << 3
  grbit     |= top10_active     << 4
  grbit     |= top10_direction  << 5
  grbit     |= top10_percent    << 6
  grbit     |= top10_value      << 7

  doper_1, string_1 = pack_doper(operator_1, token_1)
  doper_2, string_2 = pack_doper(operator_2, token_2)

  doper_1  = '' if doper_1.nil?
  doper_2  = '' if doper_2.nil?
  string_1 = '' if string_1.nil?
  string_2 = '' if string_2.nil?

  data = [index].pack('v')
  data = data + [grbit].pack('v')
  data = data + doper_1 + doper_2 + string_1 + string_2

  length  = data.length
  header  = [record, length].pack('vv')

  prepend(header, data)
end

#store_colinfo(firstcol = 0, lastcol = 0, width = 8.43, format = nil, hidden = 0, level = 0, collapsed = 0) ⇒ Object

_store_colinfo($firstcol, $lastcol, $width, $format, $hidden)

firstcol : First formatted column
lastcol  : Last formatted column
width    : Col width in user units, 8.43 is default
format   : format object
hidden   : hidden flag

Write BIFF record COLINFO to define column widths

Note: The SDK says the record length is 0x0B but Excel writes a 0x0C length record.



3030
3031
3032
3033
3034
3035
3036
3037
3038
3039
3040
3041
3042
3043
3044
3045
3046
3047
3048
3049
3050
3051
3052
3053
3054
3055
3056
3057
3058
3059
3060
3061
3062
3063
3064
3065
3066
3067
3068
3069
3070
3071
3072
 
# File 'lib/WriteExcel/worksheet.rb', line 3030

def store_colinfo(firstcol=0, lastcol=0, width=8.43, format=nil, hidden=0, level=0, collapsed=0)  #:nodoc:
  record   = 0x007D          # Record identifier
  length   = 0x000B          # Number of bytes to follow

  # Excel rounds the column width to the nearest pixel. Therefore we first
  # convert to pixels and then to the internal units. The pixel to users-units
  # relationship is different for values less than 1.
  #
  width ||= 8.43
  if width < 1
    pixels = width *12
  else
    pixels = width *7 +5
  end
  pixels = pixels.to_i

  coldx    = (pixels *256/7).to_i   # Col width in internal units
  grbit    = 0x0000               # Option flags
  reserved = 0x00                 # Reserved

  # Check for a format object
  if !format.nil? && format.kind_of?(Format)
    ixfe = format.get_xf_index
  else
    ixfe = 0x0F
  end

  # Set the limits for the outline levels (0 <= x <= 7).
  level = 0 if level < 0
  level = 7 if level > 7


  # Set the options flags. (See set_row() for more details).
  grbit |= 0x0001 if hidden != 0
  grbit |= level << 8
  grbit |= 0x1000 if collapsed != 0

  header = [record, length].pack("vv")
  data   = [firstcol, lastcol, coldx,
            ixfe, grbit, reserved].pack("vvvvvC")

  prepend(header, data)
end

#store_dimensions ⇒ Object

_store_dimensions()

Writes Excel DIMENSIONS to define the area in which there is cell data.

Notes:

Excel stores the max row/col as row/col +1.
Max and min values of 0 are used to indicate that no cell data.
We set the undef member data to 0 since it is used by _store_table().
Inserting images or charts doesn't change the DIMENSION data.



2860
2861
2862
2863
2864
2865
2866
2867
2868
2869
2870
2871
2872
2873
2874
2875
2876
2877
2878
2879
2880
2881
 
# File 'lib/WriteExcel/worksheet.rb', line 2860

def store_dimensions   #:nodoc:
  record    = 0x0200         # Record identifier
  length    = 0x000E         # Number of bytes to follow
  reserved  = 0x0000         # Reserved by Excel

  row_min = @dim_rowmin.nil? ? 0 : @dim_rowmin
  row_max = @dim_rowmax.nil? ? 0 : @dim_rowmax + 1
  col_min = @dim_colmin.nil? ? 0 : @dim_colmin
  col_max = @dim_colmax.nil? ? 0 : @dim_colmax + 1

  # Set member data to the new max/min value for use by _store_table().
  @dim_rowmin = row_min
  @dim_rowmax = row_max
  @dim_colmin = col_min
  @dim_colmax = col_max

  header = [record, length].pack("vv")
  fields = [row_min, row_max, col_min, col_max, reserved]
  data   = fields.pack("VVvvv")

  return prepend(header, data)
end

#store_dval(obj_id, dv_count) ⇒ Object

_store_dval()

my $obj_id      = $_[0];        # Object ID number.
my $dv_count    = $_[1];        # Count of DV structs to follow.

Store the DV record which contains the number of and information common to all DV structures.



6183
6184
6185
6186
6187
6188
6189
6190
6191
6192
6193
6194
6195
6196
 
# File 'lib/WriteExcel/worksheet.rb', line 6183

def store_dval(obj_id, dv_count)   #:nodoc:
  record      = 0x01B2       # Record identifier
  length      = 0x0012       # Bytes to follow

  flags       = 0x0004       # Option flags.
  x_coord     = 0x00000000   # X coord of input box.
  y_coord     = 0x00000000   # Y coord of input box.

  # Pack the record.
  header = [record, length].pack('vv')
  data   = [flags, x_coord, y_coord, obj_id, dv_count].pack('vVVVV')

  append(header, data)
end

#store_filtermode ⇒ Object

_store_filtermode()

Write BIFF record FILTERMODE to indicate that the worksheet contains AUTOFILTER record, ie. autofilters with a filter set.



3082
3083
3084
3085
3086
3087
3088
3089
3090
3091
3092
 
# File 'lib/WriteExcel/worksheet.rb', line 3082

def store_filtermode   #:nodoc:
  # Only write the record if the worksheet contains a filtered autofilter.
  return '' if @filter_on == 0

  record      = 0x009B      # Record identifier
  length      = 0x0000      # Number of bytes to follow

  header = [record, length].pack('vv')

  prepend(header)
end

#store_formula(formula) ⇒ Object

store_formula($formula)

my $formula = $_[0];      # The formula text string

Pre-parse a formula. This is used in conjunction with repeat_formula() to repetitively rewrite a formula without re-parsing it.



2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
 
# File 'lib/WriteExcel/worksheet.rb', line 2017

def store_formula(formula)
  # Strip the = sign at the beginning of the formula string
  formula.sub!(/^=/, '')

  # In order to raise formula errors from the point of view of the calling
  # program we use an eval block and re-raise the error from here.
  #
  tokens = @parser.parse_formula(formula)

  #       if ($@) {
  #           $@ =~ s/\n$//  # Strip the \n used in the Formula.pm die()
  #           croak $@       # Re-raise the error
  #       }

  # Return the parsed tokens in an anonymous array
  return [*tokens]
end

#store_mso_client_anchor(flag, col_start, x1, row_start, y1, col_end, x2, row_end, y2) ⇒ Object

_store_mso_client_anchor()

my flag         = shift;
my $col_start   = $_[0];    # Col containing upper left corner of object
my $x1          = $_[1];    # Distance to left side of object

my $row_start   = $_[2];    # Row containing top left corner of object
my $y1          = $_[3];    # Distance to top of object

my $col_end     = $_[4];    # Col containing lower right corner of object
my $x2          = $_[5];    # Distance to right side of object

my $row_end     = $_[6];    # Row containing bottom right corner of object
my $y2          = $_[7];    # Distance to bottom of object

Write the Escher ClientAnchor record that is part of MSODRAWING.



5345
5346
5347
5348
5349
5350
5351
5352
5353
5354
5355
 
# File 'lib/WriteExcel/worksheet.rb', line 5345

def store_mso_client_anchor(flag, col_start, x1, row_start, y1, col_end, x2, row_end, y2)   #:nodoc:
  type        = 0xF010
  version     = 0
  instance    = 0
  data        = ''
  length      = 18

  data = [flag, col_start, x1, row_start, y1, col_end, x2, row_end, y2].pack('v9')

  return add_mso_generic(type, version, instance, data, length)
end

#store_mso_client_data ⇒ Object

_store_mso_client_data()

Write the Escher ClientData record that is part of MSODRAWING.



5364
5365
5366
5367
5368
5369
5370
5371
5372
 
# File 'lib/WriteExcel/worksheet.rb', line 5364

def store_mso_client_data   #:nodoc:
  type        = 0xF011
  version     = 0
  instance    = 0
  data        = ''
  length      = 0

  return add_mso_generic(type, version, instance, data, length)
end

#store_mso_client_text_box ⇒ Object

_store_mso_client_text_box()

Write the Escher ClientTextbox record that is part of MSODRAWING.



5592
5593
5594
5595
5596
5597
5598
5599
5600
 
# File 'lib/WriteExcel/worksheet.rb', line 5592

def store_mso_client_text_box   #:nodoc:
  type        = 0xF00D
  version     = 0
  instance    = 0
  data        = ''
  length      = 0

  return add_mso_generic(type, version, instance, data, length)
end

#store_mso_dg(instance, num_shapes, max_spid) ⇒ Object

_store_mso_dg()

Write the Escher Dg record that is part of MSODRAWING.



5128
5129
5130
5131
5132
5133
5134
5135
5136
 
# File 'lib/WriteExcel/worksheet.rb', line 5128

def store_mso_dg(instance, num_shapes, max_spid)   #:nodoc:
  type        = 0xF008
  version     = 0
  data        = ''
  length      = 8
  data        = [num_shapes, max_spid].pack("VV")

  return add_mso_generic(type, version, instance, data, length)
end

#store_mso_dg_container(length) ⇒ Object

_store_mso_dg_container()

Write the Escher DgContainer record that is part of MSODRAWING.



5113
5114
5115
5116
5117
5118
5119
 
# File 'lib/WriteExcel/worksheet.rb', line 5113

def store_mso_dg_container(length)   #:nodoc:
  type        = 0xF002
  version     = 15
  instance    = 0
  data        = ''
  return add_mso_generic(type, version, instance, data, length)
end

#store_mso_drawing_text_box ⇒ Object

_store_mso_drawing_text_box()

Write the MSODRAWING ClientTextbox record that is part of comments.



5575
5576
5577
5578
5579
5580
5581
5582
5583
 
# File 'lib/WriteExcel/worksheet.rb', line 5575

def store_mso_drawing_text_box   #:nodoc:
  record      = 0x00EC           # Record identifier
  length      = 0x0008           # Bytes to follow

  data        = store_mso_client_text_box()
  header  = [record, length].pack('vv')

  append(header, data)
end

#store_mso_opt_chart ⇒ Object

_store_mso_opt_chart()

Write the Escher Opt record that is part of MSODRAWING.



5268
5269
5270
5271
5272
5273
5274
5275
5276
5277
5278
5279
5280
5281
5282
5283
5284
5285
5286
5287
5288
5289
5290
5291
5292
5293
5294
5295
5296
 
# File 'lib/WriteExcel/worksheet.rb', line 5268

def store_mso_opt_chart   #:nodoc:
  type        = 0xF00B
  version     = 3
  instance    = 9
  data        = ''
  length      = nil

  data = [0x007F].pack('v')       +        # Protection -> fLockAgainstGrouping
  [0x01040104].pack('V')       +
  [0x00BF].pack('v')           +        # Text -> fFitTextToShape
  [0x00080008].pack('V')       +
  [0x0181].pack('v')           +        # Fill Style -> fillColor
  [0x0800004E].pack('V')       +
  [0x0183].pack('v')           +        # Fill Style -> fillBackColor
  [0x0800004D].pack('V')       +

  [0x01BF].pack('v')           +         # Fill Style -> fNoFillHitTest
  [0x00110010].pack('V')       +
  [0x01C0].pack('v')           +        # Line Style -> lineColor
  [0x0800004D].pack('V')       +
  [0x01FF].pack('v')           +        # Line Style -> fNoLineDrawDash
  [0x00080008].pack('V')       +
  [0x023F].pack('v')            +        # Shadow Style -> fshadowObscured
  [0x00020000].pack('V')       +
  [0x03BF].pack('v')           +        # Group Shape -> fPrint
  [0x00080000].pack('V')

  return add_mso_generic(type, version, instance, data, length)
end

#store_mso_opt_comment(spid, visible = nil, colour = 0x50) ⇒ Object

_store_mso_opt_comment()

Write the Escher Opt record that is part of MSODRAWING.



5211
5212
5213
5214
5215
5216
5217
5218
5219
5220
5221
5222
5223
5224
5225
5226
5227
5228
5229
5230
5231
5232
5233
5234
5235
 
# File 'lib/WriteExcel/worksheet.rb', line 5211

def store_mso_opt_comment(spid, visible = nil, colour = 0x50)   #:nodoc:
  type        = 0xF00B
  version     = 3
  instance    = 9
  data        = ''
  length      = 54

  # Use the visible flag if set by the user or else use the worksheet value.
  # Note that the value used is the opposite of _store_note().
  #
  unless visible.nil?
    visible = visible           ? 0x0000 : 0x0002
  else
    visible = @comments_visible != 0 ? 0x0000 : 0x0002
  end

  data = [spid].pack('V')                            +
  ['0000BF00080008005801000000008101'].pack("H*") +
  [colour].pack("C")                              +
  ['000008830150000008BF011000110001'+'02000000003F0203000300BF03'].pack("H*")  +
  [visible].pack('v')                             +
  ['0A00'].pack('H*')

  return add_mso_generic(type, version, instance, data, length)
end

#store_mso_opt_filter ⇒ Object

_store_mso_opt_filter()

Write the Escher Opt record that is part of MSODRAWING.



5305
5306
5307
5308
5309
5310
5311
5312
5313
5314
5315
5316
5317
5318
5319
5320
5321
5322
5323
5324
 
# File 'lib/WriteExcel/worksheet.rb', line 5305

def store_mso_opt_filter   #:nodoc:
  type        = 0xF00B
  version     = 3
  instance    = 5
  data        = ''
  length      = nil

  data = [0x007F].pack('v')     +    # Protection -> fLockAgainstGrouping
  [0x01040104].pack('V')     +
  [0x00BF].pack('v')    +        # Text -> fFitTextToShape
  [0x00080008].pack('V')+
  [0x01BF].pack('v')    +        # Fill Style -> fNoFillHitTest
  [0x00010000].pack('V')+
  [0x01FF].pack('v')    +        # Line Style -> fNoLineDrawDash
  [0x00080000].pack('V')+
  [0x03BF].pack('v')    +        # Group Shape -> fPrint
  [0x000A0000].pack('V')

  return add_mso_generic(type, version, instance, data, length)
end

#store_mso_opt_image(spid) ⇒ Object

_store_mso_opt_image()

Write the Escher Opt record that is part of MSODRAWING.



5244
5245
5246
5247
5248
5249
5250
5251
5252
5253
5254
5255
5256
5257
5258
5259
 
# File 'lib/WriteExcel/worksheet.rb', line 5244

def store_mso_opt_image(spid)   #:nodoc:
  type        = 0xF00B
  version     = 3
  instance    = 3
  data        = ''
  length      = nil

  data = [0x4104].pack('v') +
  [spid].pack('V')        +
  [0x01BF].pack('v')      +
  [0x00010000].pack('V')  +
  [0x03BF].pack( 'v')     +
  [0x00080000].pack( 'V')

  return add_mso_generic(type, version, instance, data, length)
end

#store_mso_sp(instance, spid, options) ⇒ Object

_store_mso_sp()

Write the Escher Sp record that is part of MSODRAWING.



5194
5195
5196
5197
5198
5199
5200
5201
5202
 
# File 'lib/WriteExcel/worksheet.rb', line 5194

def store_mso_sp(instance, spid, options)   #:nodoc:
  type        = 0xF00A
  version     = 2
  data        = ''
  length      = 8
  data        = [spid, options].pack('VV')

  return add_mso_generic(type, version, instance, data, length)
end

#store_mso_sp_container(length) ⇒ Object

_store_mso_sp_container()

Write the Escher SpContainer record that is part of MSODRAWING.



5161
5162
5163
5164
5165
5166
5167
5168
 
# File 'lib/WriteExcel/worksheet.rb', line 5161

def store_mso_sp_container(length)   #:nodoc:
  type        = 0xF004
  version     = 15
  instance    = 0
  data        = ''

  return add_mso_generic(type, version, instance, data, length)
end

#store_mso_spgr ⇒ Object

_store_mso_spgr()

Write the Escher Spgr record that is part of MSODRAWING.



5177
5178
5179
5180
5181
5182
5183
5184
5185
 
# File 'lib/WriteExcel/worksheet.rb', line 5177

def store_mso_spgr   #:nodoc:
  type        = 0xF009
  version     = 1
  instance    = 0
  data        = [0, 0, 0, 0].pack("VVVV")
  length      = 16

  return add_mso_generic(type, version, instance, data, length)
end

#store_mso_spgr_container(length) ⇒ Object

_store_mso_spgr_container()

Write the Escher SpgrContainer record that is part of MSODRAWING.



5145
5146
5147
5148
5149
5150
5151
5152
 
# File 'lib/WriteExcel/worksheet.rb', line 5145

def store_mso_spgr_container(length)   #:nodoc:
  type        = 0xF003
  version     = 15
  instance    = 0
  data        = ''

  return add_mso_generic(type, version, instance, data, length)
end

#store_note(row, col, obj_id, author = nil, author_enc = nil, visible = nil) ⇒ Object

_store_note()

my $row         = $_[0];
my $col         = $_[1];
my $obj_id      = $_[2];
my $author      = $_[3] || $self->{_comments_author};
my $author_enc  = $_[4] || $self->{_comments_author_enc};
my $visible     = $_[5];

Write the worksheet NOTE record that is part of cell comments.



5705
5706
5707
5708
5709
5710
5711
5712
5713
5714
5715
5716
5717
5718
5719
5720
5721
5722
5723
5724
5725
5726
5727
5728
5729
5730
5731
5732
5733
5734
5735
5736
 
# File 'lib/WriteExcel/worksheet.rb', line 5705

def store_note(row, col, obj_id, author = nil, author_enc = nil, visible = nil)   #:nodoc:
  record      = 0x001C               # Record identifier
  length      = 0x000C               # Bytes to follow

  author     = @comments_author     if author.nil?
  author_enc = @comments_author_enc if author_enc.nil?

  # Use the visible flag if set by the user or else use the worksheet value.
  # The flag is also set in _store_mso_opt_comment() but with the opposite
  # value.
  unless visible.nil?
    visible = visible != 0           ? 0x0002 : 0x0000
  else
    visible = @comments_visible != 0 ? 0x0002 : 0x0000
  end

  # Get the number of chars in the author string (not bytes).
  num_chars  = author.length
  num_chars  = num_chars / 2 if author_enc != 0 && !author_enc.nil?

  # Null terminate the author string.
  author = author + "\0"


  # Pack the record.
  data    = [row, col, visible, obj_id, num_chars, author_enc].pack("vvvvvC")

  length  = data.length + author.length
  header  = [record, length].pack("vv")

  append(header, data, author)
end

#store_obj_chart(obj_id) ⇒ Object

_store_obj_chart()

my $obj_id      = $_[0];    # Object ID number.

Write the OBJ record that is part of chart records.



5478
5479
5480
5481
5482
5483
5484
5485
5486
5487
5488
5489
5490
5491
5492
5493
5494
5495
5496
5497
5498
5499
5500
5501
5502
5503
5504
5505
5506
5507
 
# File 'lib/WriteExcel/worksheet.rb', line 5478

def store_obj_chart(obj_id)   #:nodoc:
  record      = 0x005D   # Record identifier
  length      = 0x001A   # Bytes to follow

  obj_type    = 0x0005   # Object type (chart).
  data        = ''       # Record data.

  sub_record  = 0x0000   # Sub-record identifier.
  sub_length  = 0x0000   # Length of sub-record.
  sub_data    = ''       # Data of sub-record.
  options     = 0x6011
  reserved    = 0x0000

  # Add ftCmo (common object data) subobject
  sub_record  = 0x0015   # ftCmo
  sub_length  = 0x0012
  sub_data    = [obj_type, obj_id, options, reserved, reserved, reserved].pack('vvvVVV')
  data        = [sub_record, sub_length].pack('vv') + sub_data

  # Add ftEnd (end of object) subobject
  sub_record  = 0x0000   # ftNts
  sub_length  = 0x0000
  data        = data + [sub_record, sub_length].pack('vv')

  # Pack the record.
  header  = [record, length].pack('vv')

  append(header, data)

end

#store_obj_comment(obj_id) ⇒ Object

_store_obj_comment()

my $obj_id      = $_[0];    # Object ID number.

Write the OBJ record that is part of cell comments.



5382
5383
5384
5385
5386
5387
5388
5389
5390
5391
5392
5393
5394
5395
5396
5397
5398
5399
5400
5401
5402
5403
5404
5405
5406
5407
5408
5409
5410
5411
5412
5413
5414
5415
5416
5417
 
# File 'lib/WriteExcel/worksheet.rb', line 5382

def store_obj_comment(obj_id)   #:nodoc:
  record      = 0x005D   # Record identifier
  length      = 0x0034   # Bytes to follow

  obj_type    = 0x0019   # Object type (comment).
  data        = ''       # Record data.

  sub_record  = 0x0000   # Sub-record identifier.
  sub_length  = 0x0000   # Length of sub-record.
  sub_data    = ''       # Data of sub-record.
  options     = 0x4011
  reserved    = 0x0000

  # Add ftCmo (common object data) subobject
  sub_record     = 0x0015   # ftCmo
  sub_length     = 0x0012
  sub_data       = [obj_type, obj_id, options, reserved, reserved, reserved].pack( "vvvVVV")
  data           = [sub_record, sub_length].pack("vv") + sub_data

  # Add ftNts (note structure) subobject
  sub_record  = 0x000D   # ftNts
  sub_length  = 0x0016
  sub_data    = [reserved,reserved,reserved,reserved,reserved,reserved].pack( "VVVVVv")
  data        = data + [sub_record, sub_length].pack("vv") + sub_data

  # Add ftEnd (end of object) subobject
  sub_record  = 0x0000   # ftNts
  sub_length  = 0x0000
  data        = data + [sub_record, sub_length].pack("vv")

  # Pack the record.
  header      = [record, length].pack("vv")

  append(header, data)

end

#store_obj_filter(obj_id, col) ⇒ Object

_store_obj_filter()

my $obj_id      = $_[0];    # Object ID number.
my $col         = $_[1];

Write the OBJ record that is part of filter records.



5518
5519
5520
5521
5522
5523
5524
5525
5526
5527
5528
5529
5530
5531
5532
5533
5534
5535
5536
5537
5538
5539
5540
5541
5542
5543
5544
5545
5546
5547
5548
5549
5550
5551
5552
5553
5554
5555
5556
5557
5558
5559
5560
5561
5562
5563
5564
5565
5566
 
# File 'lib/WriteExcel/worksheet.rb', line 5518

def store_obj_filter(obj_id, col)   #:nodoc:
  record      = 0x005D   # Record identifier
  length      = 0x0046   # Bytes to follow

  obj_type    = 0x0014   # Object type (combo box).
  data        = ''       # Record data.

  sub_record  = 0x0000   # Sub-record identifier.
  sub_length  = 0x0000   # Length of sub-record.
  sub_data    = ''       # Data of sub-record.
  options     = 0x2101
  reserved    = 0x0000

  # Add ftCmo (common object data) subobject
  sub_record  = 0x0015   # ftCmo
  sub_length  = 0x0012
  sub_data    = [obj_type, obj_id, options, reserved, reserved, reserved].pack('vvvVVV')
  data        = [sub_record, sub_length].pack('vv') + sub_data

  # Add ftSbs Scroll bar subobject
  sub_record  = 0x000C   # ftSbs
  sub_length  = 0x0014
  sub_data    = ['0000000000000000640001000A00000010000100'].pack('H*')
  data        = data + [sub_record, sub_length].pack('vv') + sub_data

  # Add ftLbsData (List box data) subobject
  sub_record  = 0x0013   # ftLbsData
  sub_length  = 0x1FEE   # Special case (undocumented).

  # If the filter is active we set one of the undocumented flags.

  if @filter_cols[col]
    sub_data       = ['000000000100010300000A0008005700'].pack('H*')
  else
    sub_data       = ['00000000010001030000020008005700'].pack('H*')
  end

  data        = data + [sub_record, sub_length].pack('vv') + sub_data

  # Add ftEnd (end of object) subobject
  sub_record  = 0x0000   # ftNts
  sub_length  = 0x0000
  data        = data + [sub_record, sub_length].pack('vv')

  # Pack the record.
  header  = [record, length].pack('vv')

  append(header, data)
end

#store_obj_image(obj_id) ⇒ Object

_store_obj_image()

my $obj_id      = $_[0];    # Object ID number.

Write the OBJ record that is part of image records.



5427
5428
5429
5430
5431
5432
5433
5434
5435
5436
5437
5438
5439
5440
5441
5442
5443
5444
5445
5446
5447
5448
5449
5450
5451
5452
5453
5454
5455
5456
5457
5458
5459
5460
5461
5462
5463
5464
5465
5466
5467
5468
 
# File 'lib/WriteExcel/worksheet.rb', line 5427

def store_obj_image(obj_id)   #:nodoc:
  record      = 0x005D   # Record identifier
  length      = 0x0026   # Bytes to follow

  obj_type    = 0x0008   # Object type (Picture).
  data        = ''       # Record data.

  sub_record  = 0x0000   # Sub-record identifier.
  sub_length  = 0x0000   # Length of sub-record.
  sub_data    = ''       # Data of sub-record.
  options     = 0x6011
  reserved    = 0x0000

  # Add ftCmo (common object data) subobject
  sub_record  = 0x0015   # ftCmo
  sub_length  = 0x0012
  sub_data    = [obj_type, obj_id, options, reserved, reserved, reserved].pack('vvvVVV')
  data        = [sub_record, sub_length].pack('vv') + sub_data

  # Add ftCf (Clipboard format) subobject
  sub_record  = 0x0007   # ftCf
  sub_length  = 0x0002
  sub_data    = [0xFFFF].pack( 'v')
  data        = data + [sub_record, sub_length].pack('vv') + sub_data

  # Add ftPioGrbit (Picture option flags) subobject
  sub_record  = 0x0008   # ftPioGrbit
  sub_length  = 0x0002
  sub_data    = [0x0001].pack('v')
  data        = data + [sub_record, sub_length].pack('vv') + sub_data

  # Add ftEnd (end of object) subobject
  sub_record  = 0x0000   # ftNts
  sub_length  = 0x0000
  data        = data + [sub_record, sub_length].pack('vv')

  # Pack the record.
  header  = [record, length].pack('vv')

  append(header, data)

end

#store_selection(first_row = 0, first_col = 0, last_row = nil, last_col = nil) ⇒ Object

_store_selection($first_row, $first_col, $last_row, $last_col)

Write BIFF record SELECTION.



3122
3123
3124
3125
3126
3127
3128
3129
3130
3131
3132
3133
3134
3135
3136
3137
3138
3139
3140
3141
3142
3143
3144
3145
3146
3147
3148
3149
3150
3151
3152
3153
3154
3155
 
# File 'lib/WriteExcel/worksheet.rb', line 3122

def store_selection(first_row=0, first_col=0, last_row = nil, last_col =nil)   #:nodoc:
  record   = 0x001D                  # Record identifier
  length   = 0x000F                  # Number of bytes to follow

  pnn      = @active_pane   # Pane position
  rwAct    = first_row                   # Active row
  colAct   = first_col                   # Active column
  irefAct  = 0                       # Active cell ref
  cref     = 1                       # Number of refs

  rwFirst  = first_row                   # First row in reference
  colFirst = first_col                   # First col in reference
  rwLast   = last_row || rwFirst       # Last  row in reference
  colLast  = last_col || colFirst      # Last  col in reference

  # Swap last row/col for first row/col as necessary
  if rwFirst > rwLast
    tmp = rwFirst
    rwFirst = rwLast
    rwLast = tmp
  end

  if colFirst > colLast
    tmp = colFirst
    colFirst = colLast
    colLast = tmp
  end

  header = [record, length].pack('vv')
  data = [pnn, rwAct, colAct, irefAct, cref,
  rwFirst, rwLast, colFirst, colLast].pack('CvvvvvvCC')

  append(header, data)
end

#store_txo(string_len, format_len = 16, rotation = 0) ⇒ Object

_store_txo()

my $string_len  = $_[0];                # Length of the note text.
my $format_len  = $_[1] || 16;          # Length of the format runs.
my $rotation    = $_[2] || 0;           # Options

Write the worksheet TXO record that is part of cell comments.



5612
5613
5614
5615
5616
5617
5618
5619
5620
5621
5622
5623
5624
5625
 
# File 'lib/WriteExcel/worksheet.rb', line 5612

def store_txo(string_len, format_len = 16, rotation = 0)   #:nodoc:
  record      = 0x01B6               # Record identifier
  length      = 0x0012               # Bytes to follow

  grbit       = 0x0212               # Options
  reserved    = 0x0000               # Options

  # Pack the record.
  header  = [record, length].pack('vv')
  data    = [grbit, rotation, reserved, reserved,
  string_len, format_len, reserved].pack("vvVvvvV")

  append(header, data)
end

#store_txo_continue_1(string, encoding = 0) ⇒ Object

_store_txo_continue_1()

my $string      = $_[0];                # Comment string.
my $encoding    = $_[1] || 0;           # Encoding of the string.

Write the first CONTINUE record to follow the TXO record. It contains the text data.



5637
5638
5639
5640
5641
5642
5643
5644
5645
5646
5647
5648
5649
5650
5651
5652
5653
5654
5655
5656
5657
5658
5659
5660
5661
5662
5663
5664
 
# File 'lib/WriteExcel/worksheet.rb', line 5637

def store_txo_continue_1(string, encoding = 0)   #:nodoc:
  record      = 0x003C               # Record identifier

  # Split long comment strings into smaller continue blocks if necessary.
  # We can't let BIFFwriter::_add_continue() handled this since an extra
  # encoding byte has to be added similar to the SST block.
  #
  # We make the limit size smaller than the _add_continue() size and even
  # so that UTF16 chars occur in the same block.
  #
  limit = 8218
  while string.length > limit
    string[0 .. limit] = ""
    tmp_str = string
    data    = [encoding].pack("C") + tmp_str
    length  = data.length
    header  = [record, length].pack('vv')

    append(header, data)
  end

  # Pack the record.
  data    = [encoding].pack("C") + string
  length  = data.length
  header  = [record, length].pack('vv')

  append(header, data)
end

#store_txo_continue_2(formats) ⇒ Object

_store_txo_continue_2()

my $formats     = $_[0];                # Formatting information

Write the second CONTINUE record to follow the TXO record. It contains the formatting information for the string.



5675
5676
5677
5678
5679
5680
5681
5682
5683
5684
5685
5686
5687
5688
5689
5690
 
# File 'lib/WriteExcel/worksheet.rb', line 5675

def store_txo_continue_2(formats)   #:nodoc:
  record      = 0x003C               # Record identifier
  length      = 0x0000               # Bytes to follow

  # Pack the record.
  data = ''

  formats.each do |a_ref|
    data = data + [a_ref[0], a_ref[1], 0x0].pack('vvV')
  end

  length  = data.length
  header  = [record, length].pack("vv")

  append(header, data)
end

#substitute_cellref(cell, *args) ⇒ Object

_substitute_cellref()

Substitute an Excel cell reference in A1 notation for zero based row and column values in an argument list.

Ex: (“A4”, “Hello”) is converted to (3, 0, “Hello”).



1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
 
# File 'lib/WriteExcel/worksheet.rb', line 1532

def substitute_cellref(cell, *args)       #:nodoc:
  return [*args] if cell.kind_of?(Numeric)

  cell.upcase!

  # Convert a column range: 'A:A' or 'B:G'.
  # A range such as A:A is equivalent to A1:65536, so add rows as required
  if cell =~ /\$?([A-I]?[A-Z]):\$?([A-I]?[A-Z])/
    row1, col1 =  cell_to_rowcol($1 +'1')
    row2, col2 =  cell_to_rowcol($2 +'65536')
    return [row1, col1, row2, col2, *args]
  end

  # Convert a cell range: 'A1:B7'
  if cell =~ /\$?([A-I]?[A-Z]\$?\d+):\$?([A-I]?[A-Z]\$?\d+)/
    row1, col1 =  cell_to_rowcol($1)
    row2, col2 =  cell_to_rowcol($2)
    return [row1, col1, row2, col2, *args]
  end

  # Convert a cell reference: 'A1' or 'AD2000'
  if (cell =~ /\$?([A-I]?[A-Z]\$?\d+)/)
    row1, col1 =  cell_to_rowcol($1)
    return [row1, col1, *args]

  end

  raise("Unknown cell reference #{cell}")
end

#write(*args) ⇒ Object

write(row, col, token, format)

write(A1_notation, token, format)

Parse token and call appropriate write method. row and column are zero indexed. format is optional.

The write_url() methods have a flag to prevent recursion when writing a string that looks like a url.

Returns: return value of called subroutine

Excel makes a distinction between data types such as strings, numbers, blanks, formulas and hyperlinks. To simplify the process of writing data the write() method acts as a general alias for several more specific methods:

write_string()
write_number()
write_blank()
write_formula()
write_url()
write_row()
write_col()

The general rule is that if the data looks like a something then a something is written. Here are some examples in both row-column and A1 notation:

                                                     # Same as:
worksheet.write(0, 0, 'Hello'                     )  # write_string()
worksheet.write(1, 0, 'One'                       )  # write_string()
worksheet.write(2, 0,  2                          )  # write_number()
worksheet.write(3, 0,  3.00001                    )  # write_number()
worksheet.write(4, 0,  ""                         )  # write_blank()
worksheet.write(5, 0,  ''                         )  # write_blank()
worksheet.write(6, 0,  nil                        )  # write_blank()
worksheet.write(7, 0                              )  # write_blank()
worksheet.write(8, 0,  'http://www.ruby-lang.org/')  # write_url()
worksheet.write('A9',  'ftp://ftp.ruby-lang.org/' )  # write_url()
worksheet.write('A10', 'internal:Sheet1!A1'       )  # write_url()
worksheet.write('A11', 'external:c:\foo.xls'      )  # write_url()
worksheet.write('A12', '=A3 + 3*A4'               )  # write_formula()
worksheet.write('A13', '=SIN(PI()/4)'             )  # write_formula()
worksheet.write('A14', ['name', 'company']        )  # write_row()
worksheet.write('A15', [ ['name', 'company'] ]    )  # write_col()



1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
 
# File 'lib/WriteExcel/worksheet.rb', line 1337

def write(*args)
  # Check for a cell reference in A1 notation and substitute row and column
  if args[0] =~ /^\D/
    args = substitute_cellref(*args)
  end

  token = args[2]

  # Handle undefs as blanks
  token = '' if token.nil?

  # First try user defined matches.
  @write_match.each do |aref|
    re  = aref[0]
    sub = aref[1]

    if token =~ Regexp.new(re)
      match = eval("#{sub} self, args")
      return match unless match.nil?
    end
  end

  # Match an array ref.
  if token.kind_of?(Array)
    return write_row(*args)
  elsif token.kind_of?(Numeric)
    return write_number(*args)
    # Match http, https or ftp URL
  elsif token =~ %r|^[fh]tt?ps?://|    and @writing_url == 0
    return write_url(*args)
    # Match mailto:
  elsif token =~ %r|^mailto:|          and @writing_url == 0
    return write_url(*args)
    # Match internal or external sheet link
  elsif token =~ %r!^(?:in|ex)ternal:! and @writing_url == 0
    return write_url(*args)
    # Match formula
  elsif token =~ /^=/
    return write_formula(*args)
    # Match blank
  elsif token == ''
    args.delete_at(2)     # remove the empty string from the parameter list
    return write_blank(*args)
  else
    return write_string(*args)
  end
end

#write_blank(*args) ⇒ Object

write_blank($row, $col, $format)

Write a blank cell to the specified row and column (zero indexed). A blank cell is used to specify formatting without adding a string or a number.

A blank cell without a format serves no purpose. Therefore, we don’t write a BLANK record unless a format is specified. This is mainly an optimisation for the write_row() and write_col() methods.

Returns 0 : normal termination (including no format)

-1 : insufficient number of arguments
-2 : row or column out of range



1804
1805
1806
1807
1808
1809
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
1839
 
# File 'lib/WriteExcel/worksheet.rb', line 1804

def write_blank(*args)
  # Check for a cell reference in A1 notation and substitute row and column
  if args[0] =~ /^\D/
    args = substitute_cellref(*args)
  end

  # Check the number of args
  return -1 if args.size < 2

  # Don't write a blank cell unless it has a format
  return 0 if args[2].nil?

  record  = 0x0201                        # Record identifier
  length  = 0x0006                        # Number of bytes to follow

  row     = args[0]                       # Zero indexed row
  col     = args[1]                       # Zero indexed column
  xf      = xf_record_index(row, col, args[2])   # The cell format

  # Check that row and col are valid and store max and min values
  return -2 if check_dimensions(row, col) != 0

  header    = [record, length].pack('vv')
  data      = [row, col, xf].pack('vvv')

  # Store the data or write immediately depending    on the compatibility mode.
  if @compatibility != 0
    tmp = []
    tmp[col] = header + data
    @table[row] = tmp
  else
    append(header, data)
  end

  return 0
end

#write_col(*args) ⇒ Object

write_col($row, $col, $array_ref, $format)

Write a column of data starting from ($row, $col). Call write_row() if any of the elements of the array ref are in turn array refs. This allows the writing of 1D or 2D arrays of data in one go.

Returns: the first encountered error value or zero for no errors



1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
 
# File 'lib/WriteExcel/worksheet.rb', line 1437

def write_col(*args)
  # Check for a cell reference in A1 notation and substitute row and column
  if args[0] =~ /^\D/
    args = substitute_cellref(*args)
  end

  # Catch non array refs passed by user.
  unless args[2].kind_of?(Array)
    raise "Not an array ref in call to write_row()";
  end

  row, col, tokens, options = args
  error   = 0
  unless tokens.nil?
    tokens.each do |token|
      # write() will deal with any nested arrays
      ret = write(row, col, token, options)

      # Return only the first error encountered, if any.
      error ||= ret
      row += 1
    end
  end
  return error
end

#write_comment(*args) ⇒ Object

write_comment($row, $col, $comment[, optionhash(es)]) write_comment($A1_notation, $comment[, optionhash(es)])

Write a comment to the specified row and column (zero indexed).

Returns 0 : normal termination

-1 : insufficient number of arguments
-2 : row or column out of range



1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
 
# File 'lib/WriteExcel/worksheet.rb', line 1475

def write_comment(*args)
  # Check for a cell reference in A1 notation and substitute row and column
  if args[0] =~ /^\D/
    args = substitute_cellref(*args)
  end

  return -1 if args.size < 3   # Check the number of args

  row = args[0]
  col = args[1]

  # Check for pairs of optional arguments, i.e. an odd number of args.
  raise "Uneven number of additional arguments" if args.size % 2 == 0

  # Check that row and col are valid and store max and min values
  return -2 if check_dimensions(row, col) != 0

  # We have to avoid duplicate comments in cells or else Excel will complain.
  @comments[row] = { col => comment_params(*args) }
end

#write_date_time(*args) ⇒ Object

write_date_time ($row, $col, $string, $format)

Write a datetime string in ISO8601 “yyyy-mm-ddThh:mm:ss.ss” format as a number representing an Excel date. $format is optional.

Returns 0 : normal termination

-1 : insufficient number of arguments
-2 : row or column out of range
-3 : Invalid date_time, written as string



2544
2545
2546
2547
2548
2549
2550
2551
2552
2553
2554
2555
2556
2557
2558
2559
2560
2561
2562
2563
2564
2565
2566
2567
2568
2569
2570
 
# File 'lib/WriteExcel/worksheet.rb', line 2544

def write_date_time(*args)
  # Check for a cell reference in A1 notation and substitute row and column
  if args[0] =~ /^\D/
    args = substitute_cellref(*args)
  end

  return -1 if (args.size < 3)                 # Check the number of args

  row       = args[0]                           # Zero indexed row
  col       = args[1]                           # Zero indexed column
  str       = args[2]

  # Check that row and col are valid and store max and min values
  return -2 if check_dimensions(row, col) != 0

  error     = 0
  date_time = convert_date_time(str)

  unless date_time.nil?
    error = write_number(row, col, date_time, args[3])
  else
    # The date isn't valid so write it as a string.
    write_string(row, col, str, args[3])
    error = -3
  end
  return error
end

#write_formula(*args) ⇒ Object

write_formula($row, $col, $formula, $format, $value)

Write a formula to the specified row and column (zero indexed). The textual representation of the formula is passed to the parser in Formula.pm which returns a packed binary string.

$format is optional.

$value is an optional result of the formula that can be supplied by the user.

Returns 0 : normal termination

-1 : insufficient number of arguments
-2 : row or column out of range



1857
1858
1859
1860
1861
1862
1863
1864
1865
1866
1867
1868
1869
1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
1882
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
1910
1911
1912
1913
1914
1915
1916
1917
1918
1919
1920
1921
1922
1923
1924
1925
1926
1927
 
# File 'lib/WriteExcel/worksheet.rb', line 1857

def write_formula(*args)
  # Check for a cell reference in A1 notation and substitute row and column
  if (args[0] =~ /^\D/)
    args = substitute_cellref(args)
  end

  return -1 if args.size < 3   # Check the number of args

  record    = 0x0006     # Record identifier
  # length               # Bytes to follow

  row       = args[0]      # Zero indexed row
  col       = args[1]      # Zero indexed column
  formula   = args[2].dup  # The formula text string
  value     = args[4]      # The formula text string


  xf        = xf_record_index(row, col, args[3])  # The cell format
  chn       = 0x0000                         # Must be zero
  is_string = 0                              # Formula evaluates to str
  # num                                      # Current value of formula
  # grbi                                     # Option flags

  # Excel normally stores the last calculated value of the formula in $num.
  # Clearly we are not in a position to calculate this "a priori". Instead
  # we set $num to zero and set the option flags in $grbit to ensure
  # automatic calculation of the formula when the file is opened.
  # As a workaround for some non-Excel apps we also allow the user to
  # specify the result of the formula.
  #
  num, grbit, is_string = encode_formula_result(value)

  # Check that row and col are valid and store max and min values
  return -2 if check_dimensions(row, col) != 0

  # Strip the = sign at the beginning of the formula string
  formula.sub!(/^=/, '')

  # Parse the formula using the parser in Formula.pm
  # nakamura add:  to get byte_stream, set second arg TRUE
  # because ruby doesn't have Perl's "wantarray"
  formula = @parser.parse_formula(formula, true)

  #       if ($@) {
  #           $@ =~ s/\n$//  # Strip the \n used in the Formula.pm die()
  #           croak $@       # Re-raise the error
  #       }

  formlen = formula.length     # Length of the binary string
  length  = 0x16 + formlen     # Length of the record data

  header  = [record, length].pack("vv")
  data    = [row, col, xf].pack("vvv") +
  num +
  [grbit, chn, formlen].pack('vVv')

  # The STRING record if the formula evaluates to a string.
  string  = ''
  string  = get_formula_string(value) if is_string != 0

  # Store the data or write immediately depending on the compatibility mode.
  if @compatibility != 0
    tmp = []
    tmp[col] = header + data + formula + string
    @table[row] = tmp
  else
    append(header, data, formula, string)
  end

  return 0
end

#write_number(*args) ⇒ Object

write_number($row, $col, $num, $format)

Write a double to the specified row and column (zero indexed). An integer can be written as a double. Excel will display an integer. $format is optional.

Returns 0 : normal termination

-1 : insufficient number of arguments
-2 : row or column out of range



1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
 
# File 'lib/WriteExcel/worksheet.rb', line 1680

def write_number(*args)
  # Check for a cell reference in A1 notation and substitute row and column
  if args[0] =~ /^\D/
    args = substitute_cellref(*args)
  end

  return -1 if (args.size < 3)                # Check the number of args

  record  = 0x0203                        # Record identifier
  length  = 0x000E                        # Number of bytes to follow

  row     = args[0]                         # Zero indexed row
  col     = args[1]                         # Zero indexed column
  num     = args[2]
  xf      = xf_record_index(row, col, args[3]) # The cell format

  # Check that row and col are valid and store max and min values
  return -2 if check_dimensions(row, col) != 0

  header = [record, length].pack('vv')
  data   = [row, col, xf].pack('vvv')
  xl_double = [num].pack("d")

  xl_double.reverse! if @byte_order != 0 && @byte_order != ''

  # Store the data or write immediately depending on the compatibility mode.
  if @compatibility != 0
    tmp = []
    tmp[col] = header + data + xl_double
    @table[row] = tmp
  else
    append(header, data, xl_double)
  end

  return 0
end

#write_row(*args) ⇒ Object

write_row($row, $col, $array_ref, $format)

Write a row of data starting from ($row, $col). Call write_col() if any of the elements of the array ref are in turn array refs. This allows the writing of 1D or 2D arrays of data in one go.

Returns: the first encountered error value or zero for no errors



1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
 
# File 'lib/WriteExcel/worksheet.rb', line 1396

def write_row(*args)
  # Check for a cell reference in A1 notation and substitute row and column
  if args[0] =~ /^\D/
    args = substitute_cellref(*args)
  end

  # Catch non array refs passed by user.
  unless args[2].kind_of?(Array)
    raise "Not an array ref in call to write_row() #{$!}";
  end

  row, col, tokens, options = args
  error   = 0
  unless tokens.nil?
    tokens.each do |token|
      # Check for nested arrays
      if token.kind_of?(Array)
        ret = write_col(row, col, token, options)
      else
        ret = write(row, col, token, options)
      end

      # Return only the first error encountered, if any.
      error ||= ret
      col += 1
    end
  end
  return error
end

#write_string(*args) ⇒ Object

write_string ($row, $col, $string, $format)

Write a string to the specified row and column (zero indexed). NOTE: there is an Excel 5 defined limit of 255 characters. $format is optional. Returns 0 : normal termination

-1 : insufficient number of arguments
-2 : row or column out of range
-3 : long string truncated to 255 chars



1729
1730
1731
1732
1733
1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782
1783
1784
1785
1786
 
# File 'lib/WriteExcel/worksheet.rb', line 1729

def write_string(*args)
  # Check for a cell reference in A1 notation and substitute row and column
  if args[0] =~ /^\D/
    args = substitute_cellref(*args)
  end

  return -1 if (args.size < 3)                # Check the number of args

  record      = 0x00FD                        # Record identifier
  length      = 0x000A                        # Bytes to follow

  row         = args[0]                       # Zero indexed row
  col         = args[1]                       # Zero indexed column
  str         = args[2].to_s
  strlen      = str.length
  xf          = xf_record_index(row, col, args[3])   # The cell format
  encoding    = 0x0
  str_error   = 0

  # Handle utf8 strings
  if str =~ NonAscii
    return write_utf16le_string(row, col, NKF.nkf('-w16L0 -m0 -W', str), args[3])
  end

  # Check that row and col are valid and store max and min values
  return -2 if check_dimensions(row, col) != 0

  # Limit the string to the max number of chars.
  if (strlen > 32767)
    str       = substr(str, 0, 32767)
    str_error = -3
  end

  # Prepend the string with the type.
  str_header  = [str.length, encoding].pack('vC')
  str         = str_header + str

  if @str_table[str].nil?
    @str_table[str] = str_unique
    add_str_unique(1)
  end

  add_str_total(1)

  header = [record, length].pack('vv')
  data   = [row, col, xf, @str_table[str]].pack('vvvV')

  # Store the data or write immediately depending on the compatibility mode.
  if @compatibility != 0
    tmp = []
    tmp[col] = header + data
    @table[row] = tmp
  else
    append(header, data)
  end

  return str_error
end

#write_url(*args) ⇒ Object

write_url($row, $col, $url, $string, $format)

Write a hyperlink. This is comprised of two elements: the visible label and the invisible link. The visible label is the same as the link unless an alternative string is specified.

The parameters $string and $format are optional and their order is interchangeable for backward compatibility reasons.

The hyperlink can be to a http, ftp, mail, internal sheet, or external directory url.

Returns 0 : normal termination

-1 : insufficient number of arguments
-2 : row or column out of range
-3 : long string truncated to 255 chars



2165
2166
2167
2168
2169
2170
2171
2172
2173
2174
2175
2176
 
# File 'lib/WriteExcel/worksheet.rb', line 2165

def write_url(*args)
  # Check for a cell reference in A1 notation and substitute row and column
  if args[0] =~ /^\D/
    args = substitute_cellref(*args)
  end

  # Check the number of args
  return -1 if args.size < 3

  # Add start row and col to arg list
  return write_url_range(args[0], args[1], *args)
end

#write_url_range(*args) ⇒ Object

write_url_range($row1, $col1, $row2, $col2, $url, $string, $format)

This is the more general form of write_url(). It allows a hyperlink to be written to a range of cells. This function also decides the type of hyperlink to be written. These are either, Web (http, ftp, mailto), Internal (Sheet1!A1) or external (‘c:tempfoo.xls#Sheet1!A1’).

See also write_url() above for a general description and return values.



2189
2190
2191
2192
2193
2194
2195
2196
2197
2198
2199
2200
2201
2202
2203
2204
2205
2206
2207
2208
2209
2210
 
# File 'lib/WriteExcel/worksheet.rb', line 2189

def write_url_range(*args)
  # Check for a cell reference in A1 notation and substitute row and column
  if args[0] =~ /^\D/
    args = substitute_cellref(*args)
  end

  # Check the number of args
  return -1 if args.size < 5

  # Reverse the order of $string and $format if necessary. We work on a copy
  # in order to protect the callers args. We don't use "local @_" in case of
  # perl50005 threads.
  #
  args[5], args[6] = [ args[6], args[5] ] if args[5].kind_of?(Format)

  url = args[4]

  # Check for internal/external sheet links or default to web link
  return write_url_internal(*args) if url =~ /^internal:/
  return write_url_external(*args) if url =~ /^external:/
  return write_url_web(*args)
end

#write_utf16be_string(*args) ⇒ Object

write_utf16be_string($row, $col, $string, $format)

Write a Unicode string to the specified row and column (zero indexed). $format is optional. Returns 0 : normal termination

-1 : insufficient number of arguments
-2 : row or column out of range
-3 : long string truncated to 255 chars



4308
4309
4310
4311
4312
4313
4314
4315
4316
4317
4318
4319
4320
4321
4322
4323
4324
4325
4326
4327
4328
4329
4330
4331
4332
4333
4334
4335
4336
4337
4338
4339
4340
4341
4342
4343
4344
4345
4346
4347
4348
4349
4350
4351
4352
4353
4354
4355
4356
4357
4358
4359
4360
4361
4362
4363
4364
4365
4366
4367
4368
4369
 
# File 'lib/WriteExcel/worksheet.rb', line 4308

def write_utf16be_string(*args)
  # Check for a cell reference in A1 notation and substitute row and column
  if args[0] =~ /^\D/
    args = substitute_cellref(*args)
  end

  return -1 if (args.size < 3)                     # Check the number of args

  record      = 0x00FD                        # Record identifier
  length      = 0x000A                        # Bytes to follow

  row         = args[0]                         # Zero indexed row
  col         = args[1]                         # Zero indexed column
  strlen      = args[2].length
  str         = args[2]
  xf          = xf_record_index(row, col, args[3]) # The cell format
  encoding    = 0x1
  str_error   = 0

  # Check that row and col are valid and store max and min values
  return -2 if check_dimensions(row, col) != 0

  # Limit the utf16 string to the max number of chars (not bytes).
  if strlen > 32767* 2
    str       = str[0..32767*2]
    str_error = -3
  end

  num_bytes = str.length
  num_chars = (num_bytes / 2).to_i

  # Check for a valid 2-byte char string.
  raise "Uneven number of bytes in Unicode string" if num_bytes % 2 != 0

  # Change from UTF16 big-endian to little endian
  str = str.unpack('n*').pack('v*')

  # Add the encoding and length header to the string.
  str_header  = [num_chars, encoding].pack("vC")
  str         = str_header + str

  unless @str_table[str]
    @str_table[str] = str_unique
    add_str_unique(1)
  end

  add_str_total(1)
  
  header = [record, length].pack("vv")
  data   = [row, col, xf, @str_table[str]].pack("vvvV")

  # Store the data or write immediately depending on the compatibility mode.
  if @compatibility != 0
    tmp = []
    tmp[col] = header + data
    @table[row] = tmp
  else
    append(header, data)
  end

  return str_error
end

#write_utf16le_string(*args) ⇒ Object

write_utf16le_string($row, $col, $string, $format)

Write a UTF-16LE string to the specified row and column (zero indexed). $format is optional. Returns 0 : normal termination

-1 : insufficient number of arguments
-2 : row or column out of range
-3 : long string truncated to 255 chars



4382
4383
4384
4385
4386
4387
4388
4389
4390
4391
4392
4393
4394
4395
4396
4397
4398
4399
4400
4401
4402
 
# File 'lib/WriteExcel/worksheet.rb', line 4382

def write_utf16le_string(*args)
  # Check for a cell reference in A1 notation and substitute row and column
  if args[0] =~ /^\D/
    args = substitute_cellref(*args)
  end

  return -1 if (args.size < 3)                     # Check the number of args

  record      = 0x00FD                          # Record identifier
  length      = 0x000A                          # Bytes to follow

  row         = args[0]                         # Zero indexed row
  col         = args[1]                         # Zero indexed column
  str         = args[2]
  format      = args[3]                         # The cell format

  # Change from UTF16 big-endian to little endian
  str = str.unpack('n*').pack("v*")

  return write_utf16be_string(row, col, str, format)
end