Class: Writexlsx::Worksheet

Inherits:
Object
  • Object
show all
Includes:
Utility
Defined in:
lib/write_xlsx/worksheet.rb,
lib/write_xlsx/worksheet/cell_data.rb,
lib/write_xlsx/worksheet/hyperlink.rb,
lib/write_xlsx/worksheet/page_setup.rb,
lib/write_xlsx/worksheet/data_validation.rb

Direct Known Subclasses

Chartsheet

Defined Under Namespace

Classes: BlankCellData, BooleanCellData, COLINFO, CellData, DataValidation, DateTimeCellData, DynamicFormulaArrayCellData, EmbedImageCellData, ExternalHyperlink, FormulaArrayCellData, FormulaCellData, Hyperlink, InternalHyperlink, NumberCellData, PageSetup, RichStringCellData, StringCellData

Constant Summary

Constants included from Utility

Utility::CHAR_WIDTHS, Utility::COL_MAX, Utility::DEFAULT_COL_PIXELS, Utility::MAX_DIGIT_WIDTH, Utility::PADDING, Utility::PERL_TRUE_VALUES, Utility::ROW_MAX, Utility::SHEETNAME_MAX, Utility::STR_MAX

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods included from Utility

#absolute_char, #check_dimensions, #check_dimensions_and_update_max_min_values, #check_parameter, #color, #convert_date_time, #convert_font_args, #dash_types, delete_files, #escape_url, #fill_properties, #float_to_str, #get_font_latin_attributes, #get_font_style_attributes, #layout_properties, #legend_properties, #line_fill_properties, #line_properties, #palette_color_from_index, #params_to_font, #pattern_properties, #pixels_to_points, #ptrue?, #put_deprecate_message, #quote_sheetname, #r_id_attributes, #row_col_notation, #shape_style_base, #store_col_max_min_values, #store_row_max_min_values, #substitute_cellref, #underline_attributes, #v_shape_attributes_base, #v_shape_style_base, #value_or_raise, #write_a_body_pr, #write_a_def_rpr, #write_a_end_para_rpr, #write_a_lst_style, #write_a_p_formula, #write_a_p_pr_formula, #write_a_solid_fill, #write_a_srgb_clr, #write_anchor, #write_auto_fill, #write_color, #write_comment_path, #write_def_rpr_r_pr_common, #write_div, #write_fill, #write_font, #write_stroke, #write_tx_pr, #write_xml_declaration, #xl_cell_to_rowcol, #xl_col_to_name, #xl_range, #xl_range_formula, #xl_rowcol_to_cell, #xl_string_pixel_width, #xml_str

Constructor Details

#initialize(workbook, index, name) ⇒ Worksheet

:nodoc:



45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
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
 
# File 'lib/write_xlsx/worksheet.rb', line 45

def initialize(workbook, index, name) # :nodoc:
  rowmax   = 1_048_576
  colmax   = 16_384
  strmax   = 32_767

  @writer = Package::XMLWriterSimple.new

  @workbook = workbook
  @index = index
  @name = name
  @col_info = {}
  @cell_data_table = []
  @excel_version = 2007
  @palette = workbook.palette
  @default_url_format = workbook.default_url_format
  @max_url_length = workbook.max_url_length

  @page_setup = PageSetup.new

  @screen_gridlines     = true
  @show_zeros           = true

  @xls_rowmax           = rowmax
  @xls_colmax           = colmax
  @xls_strmax           = strmax
  @dim_rowmin           = nil
  @dim_rowmax           = nil
  @dim_colmin           = nil
  @dim_colmax           = nil
  @selections           = []
  @panes                = []
  @hide_row_col_headers = 0
  @top_left_cell        = ''

  @tab_color  = 0

  @set_cols = {}
  @set_rows = {}
  @col_size_changed = false
  @zoom = 100
  @zoom_scale_normal = true
  @right_to_left = false
  @leading_zeros = false

  @autofilter_area = nil
  @filter_on    = false
  @filter_range = []
  @filter_cols  = {}
  @filter_cells = {}
  @filter_type  = {}

  @row_sizes = {}

  @last_shape_id          = 1
  @rel_count              = 0
  @external_hyper_links   = []
  @external_drawing_links = []
  @external_comment_links = []
  @external_vml_links     = []
  @external_background_links = []
  @external_table_links   = []
  @drawing_links          = []
  @vml_drawing_links      = []
  @charts                 = []
  @images                 = []
  @tables                 = []
  @sparklines             = []
  @shapes                 = []
  @shape_hash             = {}
  @drawing_rels           = {}
  @drawing_rels_id        = 0
  @vml_drawing_rels       = {}
  @vml_drawing_rels_id    = 0
  @has_dynamic_functions  = false
  @has_embedded_images    = false

  @use_future_functions   = false

  @header_images          = []
  @footer_images          = []
  @background_image       = nil

  @outline_row_level      = 0
  @outline_col_level      = 0

  @original_row_height    = 15
  @default_row_height     = 15
  @default_row_pixels     = 20
  @default_col_width      = 8.43
  @default_row_rezoed     = 0
  @default_date_pixels    = 68

  @merge = []

  @has_vml  = false
  @comments = Package::Comments.new(self)
  @buttons_array          = []
  @header_images_array    = []
  @ignore_errors          = nil

  @validations = []

  @cond_formats   = {}
  @data_bars_2010 = []
  @dxf_priority   = 1

  @protected_ranges     = []
  @num_protected_ranges = 0

  if excel2003_style?
    @original_row_height      = 12.75
    @default_row_height       = 12.75
    @default_row_pixels       = 17
    self.margins_left_right   = 0.75
    self.margins_top_bottom   = 1
    @page_setup.margin_header = 0.5
    @page_setup.margin_footer = 0.5
    @page_setup.header_footer_aligns = false
  end

  @embedded_image_indexes = @workbook.embedded_image_indexes
end

Instance Attribute Details

#autofilter_area ⇒ Object (readonly)

:nodoc:



35
36
37
 
# File 'lib/write_xlsx/worksheet.rb', line 35

def autofilter_area
  @autofilter_area
end

#background_image ⇒ Object (readonly)

:nodoc:



31
32
33
 
# File 'lib/write_xlsx/worksheet.rb', line 31

def background_image
  @background_image
end

#charts ⇒ Object (readonly)

:nodoc:



30
31
32
 
# File 'lib/write_xlsx/worksheet.rb', line 30

def charts
  @charts
end

#col_info ⇒ Object (readonly)

:nodoc:



36
37
38
 
# File 'lib/write_xlsx/worksheet.rb', line 36

def col_info
  @col_info
end

#comments ⇒ Object (readonly)

:nodoc:



38
39
40
 
# File 'lib/write_xlsx/worksheet.rb', line 38

def comments
  @comments
end

#comments_author ⇒ Object

:nodoc:



38
39
40
 
# File 'lib/write_xlsx/worksheet.rb', line 38

def comments_author
  @comments_author
end

#data_bars_2010 ⇒ Object

:nodoc:



39
40
41
 
# File 'lib/write_xlsx/worksheet.rb', line 39

def data_bars_2010
  @data_bars_2010
end

#default_row_height ⇒ Object

:nodoc:



43
44
45
 
# File 'lib/write_xlsx/worksheet.rb', line 43

def default_row_height
  @default_row_height
end

#drawings ⇒ Object (readonly)

:nodoc:



30
31
32
 
# File 'lib/write_xlsx/worksheet.rb', line 30

def drawings
  @drawings
end

#dxf_priority ⇒ Object

:nodoc:



39
40
41
 
# File 'lib/write_xlsx/worksheet.rb', line 39

def dxf_priority
  @dxf_priority
end

#excel_version=(value) ⇒ Object (writeonly)

:nodoc:



41
42
43
 
# File 'lib/write_xlsx/worksheet.rb', line 41

def excel_version=(value)
  @excel_version = value
end

#filter_cells ⇒ Object (readonly)

:nodoc:



42
43
44
 
# File 'lib/write_xlsx/worksheet.rb', line 42

def filter_cells
  @filter_cells
end

#footer_images ⇒ Object (readonly)

:nodoc:



31
32
33
 
# File 'lib/write_xlsx/worksheet.rb', line 31

def footer_images
  @footer_images
end

#header_images ⇒ Object (readonly)

:nodoc:



31
32
33
 
# File 'lib/write_xlsx/worksheet.rb', line 31

def header_images
  @header_images
end

#images ⇒ Object (readonly)

:nodoc:



30
31
32
 
# File 'lib/write_xlsx/worksheet.rb', line 30

def images
  @images
end

#index ⇒ Object (readonly)

:nodoc:



29
30
31
 
# File 'lib/write_xlsx/worksheet.rb', line 29

def index
  @index
end

#name ⇒ Object (readonly)

:nodoc:



29
30
31
 
# File 'lib/write_xlsx/worksheet.rb', line 29

def name
  @name
end

#row_sizes ⇒ Object (readonly)

:nodoc:



36
37
38
 
# File 'lib/write_xlsx/worksheet.rb', line 36

def row_sizes
  @row_sizes
end

#set_rows ⇒ Object (readonly)

:nodoc:



36
37
38
 
# File 'lib/write_xlsx/worksheet.rb', line 36

def set_rows
  @set_rows
end

#shapes ⇒ Object (readonly)

:nodoc:



30
31
32
 
# File 'lib/write_xlsx/worksheet.rb', line 30

def shapes
  @shapes
end

#tables ⇒ Object (readonly)

:nodoc:



30
31
32
 
# File 'lib/write_xlsx/worksheet.rb', line 30

def tables
  @tables
end

#vba_codename ⇒ Object (readonly)

:nodoc:



40
41
42
 
# File 'lib/write_xlsx/worksheet.rb', line 40

def vba_codename
  @vba_codename
end

#vml_data_id ⇒ Object (readonly)

:nodoc:



33
34
35
 
# File 'lib/write_xlsx/worksheet.rb', line 33

def vml_data_id
  @vml_data_id
end

#vml_drawing_links ⇒ Object (readonly)

:nodoc:



32
33
34
 
# File 'lib/write_xlsx/worksheet.rb', line 32

def vml_drawing_links
  @vml_drawing_links
end

#vml_header_id ⇒ Object (readonly)

:nodoc:



34
35
36
 
# File 'lib/write_xlsx/worksheet.rb', line 34

def vml_header_id
  @vml_header_id
end

#vml_shape_id ⇒ Object (readonly)

:nodoc:



37
38
39
 
# File 'lib/write_xlsx/worksheet.rb', line 37

def vml_shape_id
  @vml_shape_id
end

#writer ⇒ Object (readonly)

:nodoc:



36
37
38
 
# File 'lib/write_xlsx/worksheet.rb', line 36

def writer
  @writer
end

Instance Method Details

#activate ⇒ Object

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



220
221
222
223
224
 
# File 'lib/write_xlsx/worksheet.rb', line 220

def activate
  @hidden = false
  @selected = true
  @workbook.activesheet = @index
end

#add_sparkline(param) ⇒ Object

:call-seq:

add_sparkline(properties)

Add sparklines to the worksheet.



2312
2313
2314
 
# File 'lib/write_xlsx/worksheet.rb', line 2312

def add_sparkline(param)
  @sparklines << Sparkline.new(self, param, quote_sheetname(@name))
end

#add_table(*args) ⇒ Object

:call-seq:

add_table(row1, col1, row2, col2, properties)

Add an Excel table to a worksheet.



2299
2300
2301
2302
2303
2304
 
# File 'lib/write_xlsx/worksheet.rb', line 2299

def add_table(*args)
  # Table count is a member of Workbook, global to all Worksheet.
  table = Package::Table.new(self, *args)
  @tables << table
  table
end

#assemble_xml_file ⇒ Object

:nodoc:



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
 
# File 'lib/write_xlsx/worksheet.rb', line 172

def assemble_xml_file # :nodoc:
  write_xml_declaration do
    @writer.tag_elements('worksheet', write_worksheet_attributes) do
      write_sheet_pr
      write_dimension
      write_sheet_views
      write_sheet_format_pr
      write_cols
      write_sheet_data
      write_sheet_protection
      write_protected_ranges
      # write_sheet_calc_pr
      write_phonetic_pr if excel2003_style?
      write_auto_filter
      write_merge_cells
      write_conditional_formats
      write_data_validations
      write_hyperlinks
      write_print_options
      write_page_margins
      write_page_setup
      write_header_footer
      write_row_breaks
      write_col_breaks
      write_ignored_errors
      write_drawings
      write_legacy_drawing
      write_legacy_drawing_hf
      write_picture
      write_table_parts
      write_ext_list
    end
  end
end

#autofilter(row1, col1 = nil, row2 = nil, col2 = nil) ⇒ Object

:call-seq:

autofilter(first_row, first_col, last_row, last_col)

Set the autofilter area in the worksheet.



2401
2402
2403
2404
2405
2406
2407
2408
2409
2410
2411
2412
2413
2414
2415
2416
2417
2418
2419
2420
2421
2422
2423
2424
 
# File 'lib/write_xlsx/worksheet.rb', line 2401

def autofilter(row1, col1 = nil, row2 = nil, col2 = nil)
  if (row_col_array = row_col_notation(row1))
    _row1, _col1, _row2, _col2 = row_col_array
  else
    _row1 = row1
    _col1 = col1
    _row2 = row2
    _col2 = col2
  end
  return if [_row1, _col1, _row2, _col2].include?(nil)

  # Reverse max and min values if necessary.
  _row1, _row2 = _row2, _row1 if _row2 < _row1
  _col1, _col2 = _col2, _col1 if _col2 < _col1

  @autofilter_area = convert_name_area(_row1, _col1, _row2, _col2)
  @autofilter_ref  = xl_range(_row1, _row2, _col1, _col2)
  @filter_range    = [_col1, _col2]

  # Store the filter cell positions for use in the autofit calculation.
  (_col1.._col2).each do |col|
    @filter_cells["#{_row1}:#{col}"] = 1
  end
end

#autofit ⇒ Object

autofit()

Simulate autofit based on the data, and datatypes in each column. We do this by estimating a pixel width for each cell data.



387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
 
# File 'lib/write_xlsx/worksheet.rb', line 387

def autofit
  col_width = {}

  # Iterate through all the data in the worksheet.
  (@dim_rowmin..@dim_rowmax).each do |row_num|
    # Skip row if it doesn't contain cell data.
    next unless @cell_data_table[row_num]

    (@dim_colmin..@dim_colmax).each do |col_num|
      length = 0
      case (cell_data = @cell_data_table[row_num][col_num])
      when StringCellData, RichStringCellData
        # Handle strings and rich strings.
        #
        # For standard shared strings we do a reverse lookup
        # from the shared string id to the actual string. For
        # rich strings we use the unformatted string. We also
        # split multiline strings and handle each part
        # separately.
        string = cell_data.raw_string

        if string =~ /\n/
          # Handle multiline strings.
          length = max = string.split("\n").collect do |str|
            xl_string_pixel_width(str)
          end.max
        else
          length = xl_string_pixel_width(string)
        end
      when DateTimeCellData

        # Handle dates.
        #
        # The following uses the default width for mm/dd/yyyy
        # dates. It isn't feasible to parse the number format
        # to get the actual string width for all format types.
        length = @default_date_pixels
      when NumberCellData

        # Handle numbers.
        #
        # We use a workaround/optimization for numbers since
        # digits all have a pixel width of 7. This gives a
        # slightly greater width for the decimal place and
        # minus sign but only by a few pixels and
        # over-estimation is okay.
        length = 7 * cell_data.token.to_s.length
      when BooleanCellData

        # Handle boolean values.
        #
        # Use the Excel standard widths for TRUE and FALSE.
        if ptrue?(cell_data.token)
          length = 31
        else
          length = 36
        end
      when FormulaCellData, FormulaArrayCellData, DynamicFormulaArrayCellData
        # Handle formulas.
        #
        # We only try to autofit a formula if it has a
        # non-zero value.
        if ptrue?(cell_data.data)
          length = xl_string_pixel_width(cell_data.data)
        end
      end

      # If the cell is in an autofilter header we add an
      # additional 16 pixels for the dropdown arrow.
      if length > 0 &&
         @filter_cells["#{row_num}:#{col_num}"]
        length += 16
      end

      # Add the string lenght to the lookup hash.
      max                = col_width[col_num] || 0
      col_width[col_num] = length if length > max
    end
  end

  # Apply the width to the column.
  col_width.each do |col_num, pixel_width|
    # Convert the string pixel width to a character width using an
    # additional padding of 7 pixels, like Excel.
    width = pixels_to_width(pixel_width + 7)

    # The max column character width in Excel is 255.
    width = 255.0 if width > 255.0

    # Add the width to an existing col info structure or add a new one.
    if @col_info[col_num]
      @col_info[col_num].width   = width
      @col_info[col_num].autofit = 1
    else
      @col_info[col_num] =
        COLINFO.new(width, nil, 0, 0, 0, 1)
    end
  end
end

#buttons_data ⇒ Object

:nodoc:



2654
2655
2656
 
# File 'lib/write_xlsx/worksheet.rb', line 2654

def buttons_data  # :nodoc:
  @buttons_array
end

#center_horizontally ⇒ Object

Center the worksheet data horizontally between the margins on the printed page:



703
704
705
 
# File 'lib/write_xlsx/worksheet.rb', line 703

def center_horizontally
  @page_setup.center_horizontally
end

#center_vertically ⇒ Object

Center the worksheet data vertically between the margins on the printed page:



710
711
712
 
# File 'lib/write_xlsx/worksheet.rb', line 710

def center_vertically
  @page_setup.center_vertically
end

#comments_visible? ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


2593
2594
2595
 
# File 'lib/write_xlsx/worksheet.rb', line 2593

def comments_visible? # :nodoc:
  !!@comments_visible
end

#conditional_formatting(*args) ⇒ Object

:call-seq:

conditional_formatting(cell_or_cell_range, options)

Conditional formatting is a feature of Excel which allows you to apply a format to a cell or a range of cells based on a certain criteria.



2287
2288
2289
2290
2291
 
# File 'lib/write_xlsx/worksheet.rb', line 2287

def conditional_formatting(*args)
  cond_format = Package::ConditionalFormat.factory(self, *args)
  @cond_formats[cond_format.range] ||= []
  @cond_formats[cond_format.range] << cond_format
end

#data_validation(*args) ⇒ Object

:call-seq:

data_validation(cell_or_cell_range, options)

Data validation is a feature of Excel which allows you to restrict the data that a users enters in a cell and to display help and warning messages. It also allows you to restrict input to values in a drop down list.



2348
2349
2350
2351
 
# File 'lib/write_xlsx/worksheet.rb', line 2348

def data_validation(*args)
  validation = DataValidation.new(*args)
  @validations << validation unless validation.validate_none?
end

#date_1904? ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


2634
2635
2636
 
# File 'lib/write_xlsx/worksheet.rb', line 2634

def date_1904? # :nodoc:
  @workbook.date_1904?
end

#drawing_links ⇒ Object 



2673
2674
2675
 
# File 'lib/write_xlsx/worksheet.rb', line 2673

def drawing_links
  [@drawing_links]
end

#embed_image(row, col, filename, options = nil) ⇒ Object

Embed an image into the worksheet.

Raises:



1921
1922
1923
1924
1925
1926
1927
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
1971
1972
1973
1974
1975
1976
 
# File 'lib/write_xlsx/worksheet.rb', line 1921

def embed_image(row, col, filename, options = nil)
  # Check for a cell reference in A1 notation and substitute row and column.
  if (row_col_array = row_col_notation(row))
    _row, _col = row_col_array
    image      = col
    _options   = filename
  else
    _row     = row
    _col     = col
    image    = filename
    _options = options
  end
  xf, url, tip, description, decorative = []

  raise WriteXLSXInsufficientArgumentError if [_row, _col, image].include?(nil)
  raise "Couldn't locate #{image}" unless File.exist?(image)

  # Check that row and col are valid and store max and min values
  check_dimensions(_row, _col)
  store_row_col_max_min_values(_row, _col)

  if options
    xf          = options[:cell_format]
    url         = options[:url]
    tip         = options[:tip]
    description = options[:description]
    decorative  = options[:decorative]
  end

  # Write the url without writing a string.
  if url
    xf ||= @default_url_format

    write_url(row, col, url, xf, nil, tip, true)
  end

  # Get the image properties, mainly for the type and checksum.
  image_property = ImageProperty.new(
    image, description: description, decorative: decorative
  )
  @workbook.store_image_types(image_property.type)

  # Check for duplicate images.
  image_index = @embedded_image_indexes[image_property.md5]

  unless ptrue?(image_index)
    @workbook.embedded_images << image_property

    image_index = @workbook.embedded_images.size
    @embedded_image_indexes[image_property.md5] = image_index
  end

  # Write the cell placeholder.
  store_data_to_table(EmbedImageCellData.new(image_index, xf), _row, _col)
  @has_embedded_images = true
end

#excel2003_style? ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


2638
2639
2640
 
# File 'lib/write_xlsx/worksheet.rb', line 2638

def excel2003_style? # :nodoc:
  @workbook.excel2003_style
end

#external_links ⇒ Object 



2662
2663
2664
2665
2666
2667
2668
2669
2670
2671
 
# File 'lib/write_xlsx/worksheet.rb', line 2662

def external_links
  [
    @external_hyper_links,
    @external_drawing_links,
    @external_vml_links,
    @external_background_links,
    @external_table_links,
    @external_comment_links
  ].reject { |a| a.empty? }
end

#filter_column(col, expression) ⇒ Object

Set the column filter criteria.

The filter_column method can be used to filter columns in a autofilter range based on simple conditions.



2432
2433
2434
2435
2436
2437
2438
2439
2440
2441
2442
2443
2444
2445
2446
2447
2448
2449
2450
2451
2452
2453
2454
2455
2456
2457
2458
 
# File 'lib/write_xlsx/worksheet.rb', line 2432

def filter_column(col, expression)
  raise "Must call autofilter before filter_column" unless @autofilter_area

  col = prepare_filter_column(col)

  tokens = extract_filter_tokens(expression)

  raise "Incorrect number of tokens in expression '#{expression}'" unless [3, 7].include?(tokens.size)

  tokens = parse_filter_expression(expression, tokens)

  # Excel handles single or double custom filters as default filters. We need
  # to check for them and handle them accordingly.
  if tokens.size == 2 && tokens[0] == 2
    # Single equality.
    filter_column_list(col, tokens[1])
  elsif tokens.size == 5 && tokens[0] == 2 && tokens[2] == 1 && tokens[3] == 2
    # Double equality with "or" operator.
    filter_column_list(col, tokens[1], tokens[4])
  else
    # Non default custom filter.
    @filter_cols[col] = Array.new(tokens)
    @filter_type[col] = 0
  end

  @filter_on = 1
end

#filter_column_list(col, *tokens) ⇒ Object

Set the column filter criteria in Excel 2007 list style.



2463
2464
2465
2466
2467
2468
2469
2470
2471
2472
2473
 
# File 'lib/write_xlsx/worksheet.rb', line 2463

def filter_column_list(col, *tokens)
  tokens.flatten!
  raise "Incorrect number of arguments to filter_column_list" if tokens.empty?
  raise "Must call autofilter before filter_column_list" unless @autofilter_area

  col = prepare_filter_column(col)

  @filter_cols[col] = tokens
  @filter_type[col] = 1           # Default style.
  @filter_on        = 1
end

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

The fit_to_pages() method is used to fit the printed area to a specific number of pages both vertically and horizontally. If the printed area exceeds the specified number of pages it will be scaled down to fit. This guarantees that the printed area will always appear on the specified number of pages even if the page size or margins change.



2388
2389
2390
2391
2392
2393
 
# File 'lib/write_xlsx/worksheet.rb', line 2388

def fit_to_pages(width = 1, height = 1)
  @page_setup.fit_page   = true
  @page_setup.fit_width  = width
  @page_setup.fit_height = height
  @page_setup.page_setup_changed = true
end

#freeze_panes(*args) ⇒ Object

:call-seq:

freeze_panes(row, col [ , top_row, left_col ] )

This method can be used to divide a worksheet into horizontal or vertical regions known as panes and to also “freeze” these panes so that the splitter bars are not visible. This is the same as the Window->Freeze Panes menu command in Excel



546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
 
# File 'lib/write_xlsx/worksheet.rb', line 546

def freeze_panes(*args)
  return if args.empty?

  # Check for a cell reference in A1 notation and substitute row and column.
  if (row_col_array = row_col_notation(args.first))
    row, col, top_row, left_col = row_col_array
    type = args[1]
  else
    row, col, top_row, left_col, type = args
  end

  col      ||= 0
  top_row  ||= row
  left_col ||= col
  type     ||= 0

  @panes   = [row, col, top_row, left_col, type]
end

#get_range_data(row_start, col_start, row_end, col_end) ⇒ Object

Returns a range of data from the worksheet _table to be used in chart cached data. Strings are returned as SST ids and decoded in the workbook. Return nils for data that doesn’t exist since Excel can chart series with data missing.



2568
2569
2570
2571
2572
2573
2574
2575
2576
2577
2578
2579
2580
2581
2582
2583
2584
2585
2586
2587
2588
2589
2590
2591
 
# File 'lib/write_xlsx/worksheet.rb', line 2568

def get_range_data(row_start, col_start, row_end, col_end) # :nodoc:
  # TODO. Check for worksheet limits.

  # Iterate through the table data.
  data = []
  (row_start..row_end).each do |row_num|
    # Store nil if row doesn't exist.
    unless @cell_data_table[row_num]
      data << nil
      next
    end

    (col_start..col_end).each do |col_num|
      cell = @cell_data_table[row_num][col_num]
      if cell
        data << cell.data
      else
        data << nil
      end
    end
  end

  data
end

#has_comments? ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


2521
2522
2523
 
# File 'lib/write_xlsx/worksheet.rb', line 2521

def has_comments? # :nodoc:
  !@comments.empty?
end

#has_dynamic_functions? ⇒ Boolean

Returns:

  • (Boolean) 


2789
2790
2791
 
# File 'lib/write_xlsx/worksheet.rb', line 2789

def has_dynamic_functions?
  @has_dynamic_functions
end

#has_embedded_images? ⇒ Boolean

Returns:

  • (Boolean) 


2793
2794
2795
 
# File 'lib/write_xlsx/worksheet.rb', line 2793

def has_embedded_images?
  @has_embedded_images
end

#has_header_vml? ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


2517
2518
2519
 
# File 'lib/write_xlsx/worksheet.rb', line 2517

def has_header_vml?  # :nodoc:
  !(@header_images.empty? && @footer_images.empty?)
end

#has_shapes? ⇒ Boolean

Returns:

  • (Boolean) 


2525
2526
2527
 
# File 'lib/write_xlsx/worksheet.rb', line 2525

def has_shapes?
  @has_shapes
end

#has_vml? ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


2513
2514
2515
 
# File 'lib/write_xlsx/worksheet.rb', line 2513

def has_vml?  # :nodoc:
  @has_vml
end

#header_images_data ⇒ Object

:nodoc:



2658
2659
2660
 
# File 'lib/write_xlsx/worksheet.rb', line 2658

def header_images_data  # :nodoc:
  @header_images_array
end

#hidden? ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


243
244
245
 
# File 'lib/write_xlsx/worksheet.rb', line 243

def hidden? # :nodoc:
  @hidden == :hidden
end

#hide(hidden = :hidden) ⇒ Object

Hide this worksheet.



229
230
231
232
233
234
 
# File 'lib/write_xlsx/worksheet.rb', line 229

def hide(hidden = :hidden)
  @hidden = hidden
  @selected = false
  @workbook.activesheet = 0 if @workbook.activesheet == @index
  @workbook.firstsheet  = 0 if @workbook.firstsheet  == @index
end

#hide_gridlines(option = 1) ⇒ Object

Set the option to hide gridlines on the screen and the printed page.



2356
2357
2358
2359
2360
 
# File 'lib/write_xlsx/worksheet.rb', line 2356

def hide_gridlines(option = 1)
  @screen_gridlines = (option != 2)

  @page_setup.hide_gridlines(option)
end

#hide_row_col_headers ⇒ Object

Set the option to hide the row and column headers in Excel.



2377
2378
2379
 
# File 'lib/write_xlsx/worksheet.rb', line 2377

def hide_row_col_headers
  @hide_row_col_headers = 1
end

#hide_zero(flag = true) ⇒ Object

Hide cell zero values.



974
975
976
 
# File 'lib/write_xlsx/worksheet.rb', line 974

def hide_zero(flag = true)
  @show_zeros = !flag
end

#horizontal_dpi=(val) ⇒ Object 



2734
2735
2736
 
# File 'lib/write_xlsx/worksheet.rb', line 2734

def horizontal_dpi=(val)
  @page_setup.horizontal_dpi = val
end

#ignore_errors(ignores) ⇒ Object

Ignore worksheet errors/warnings in user defined ranges.



2752
2753
2754
2755
2756
2757
2758
2759
2760
2761
2762
2763
2764
2765
2766
2767
2768
2769
 
# File 'lib/write_xlsx/worksheet.rb', line 2752

def ignore_errors(ignores)
  # List of valid input parameters.
  valid_parameter_keys = %i[
    number_stored_as_text
    eval_error
    formula_differs
    formula_range
    formula_unlocked
    empty_cell_reference
    list_data_validation
    calculated_column
    two_digit_text_year
  ]

  raise "Unknown parameter '#{ignores.key - valid_parameter_keys}' in ignore_errors()." unless (ignores.keys - valid_parameter_keys).empty?

  @ignore_errors = ignores
end

#insert_button(row, col, properties = nil) ⇒ Object

:call-seq:

insert_button(row, col, properties)

The insert_button() method can be used to insert an Excel form button into a worksheet.



2323
2324
2325
2326
2327
2328
2329
2330
2331
2332
2333
2334
2335
2336
2337
 
# File 'lib/write_xlsx/worksheet.rb', line 2323

def insert_button(row, col, properties = nil)
  if (row_col_array = row_col_notation(row))
    _row, _col = row_col_array
    _properties = col
  else
    _row = row
    _col = col
    _properties = properties
  end

  @buttons_array << Writexlsx::Package::Button.new(
    self, _row, _col, _properties, @default_row_pixels, @buttons_array.size + 1
  )
  @has_vml = true
end

#insert_chart(row, col, chart = nil, *options) ⇒ Object

:call-seq:

insert_chart(row, column, chart [ , x, y, x_scale, y_scale ])

This method can be used to insert a Chart object into a worksheet. The Chart must be created by the add_chart() Workbook method and it must have the embedded option set.

Raises:



1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845
1846
1847
1848
1849
1850
1851
1852
1853
1854
1855
1856
1857
1858
1859
1860
1861
1862
1863
1864
1865
1866
1867
1868
1869
1870
1871
 
# File 'lib/write_xlsx/worksheet.rb', line 1819

def insert_chart(row, col, chart = nil, *options)
  # Check for a cell reference in A1 notation and substitute row and column.
  if (row_col_array = row_col_notation(row))
    _row, _col = row_col_array
    _chart     = col
    _options   = [chart] + options
  else
    _row = row
    _col = col
    _chart = chart
    _options = options
  end
  raise WriteXLSXInsufficientArgumentError if [_row, _col, _chart].include?(nil)

  if _options.first.instance_of?(Hash)
    params = _options.first
    x_offset    = params[:x_offset]
    y_offset    = params[:y_offset]
    x_scale     = params[:x_scale]
    y_scale     = params[:y_scale]
    anchor      = params[:object_position]
    description = params[:description]
    decorative  = params[:decorative]
  else
    x_offset, y_offset, x_scale, y_scale, anchor = _options
  end
  x_offset ||= 0
  y_offset ||= 0
  x_scale  ||= 1
  y_scale  ||= 1
  anchor   ||= 1

  raise "Not a Chart object in insert_chart()" unless _chart.is_a?(Chart) || _chart.is_a?(Chartsheet)
  raise "Not a embedded style Chart object in insert_chart()" if _chart.respond_to?(:embedded) && _chart.embedded == 0

  if _chart.already_inserted? || (_chart.combined && _chart.combined.already_inserted?)
    raise "Chart cannot be inserted in a worksheet more than once"
  else
    _chart.already_inserted          = true
    _chart.combined.already_inserted = true if _chart.combined
  end

  # Use the values set with chart.set_size, if any.
  x_scale  = _chart.x_scale  if _chart.x_scale  != 1
  y_scale  = _chart.y_scale  if _chart.y_scale  != 1
  x_offset = _chart.x_offset if ptrue?(_chart.x_offset)
  y_offset = _chart.y_offset if ptrue?(_chart.y_offset)

  @charts << InsertedChart.new(
    _row,    _col,    _chart, x_offset,    y_offset,
    x_scale, y_scale, anchor, description, decorative
  )
end

#insert_image(row, col, image = nil, *options) ⇒ Object

:call-seq:

insert_image(row, column, filename, options)

Raises:



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
 
# File 'lib/write_xlsx/worksheet.rb', line 1877

def insert_image(row, col, image = nil, *options)
  # Check for a cell reference in A1 notation and substitute row and column.
  if (row_col_array = row_col_notation(row))
    _row, _col = row_col_array
    _image     = col
    _options   = [image] + options
  else
    _row = row
    _col = col
    _image = image
    _options = options
  end
  raise WriteXLSXInsufficientArgumentError if [_row, _col, _image].include?(nil)

  if _options.first.instance_of?(Hash)
    # Newer hash bashed options
    params      = _options.first
    x_offset    = params[:x_offset]
    y_offset    = params[:y_offset]
    x_scale     = params[:x_scale]
    y_scale     = params[:y_scale]
    anchor      = params[:object_position]
    url         = params[:url]
    tip         = params[:tip]
    description = params[:description]
    decorative  = params[:decorative]
  else
    x_offset, y_offset, x_scale, y_scale, anchor = _options
  end
  x_offset ||= 0
  y_offset ||= 0
  x_scale  ||= 1
  y_scale  ||= 1
  anchor   ||= 2

  @images << Image.new(
    _row, _col, _image, x_offset, y_offset,
    x_scale, y_scale, url, tip, anchor, description, decorative
  )
end

#insert_shape(row_start, column_start, shape = nil, x_offset = nil, y_offset = nil, x_scale = nil, y_scale = nil, anchor = nil) ⇒ Object

:call-seq:

insert_shape(row, col, shape [ , x, y, x_scale, y_scale ])

Insert a shape into the worksheet.



1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
 
# File 'lib/write_xlsx/worksheet.rb', line 1984

def insert_shape(
      row_start, column_start, shape = nil, x_offset = nil, y_offset = nil,
      x_scale = nil, y_scale = nil, anchor = nil
    )
  # Check for a cell reference in A1 notation and substitute row and column.
  if (row_col_array = row_col_notation(row_start))
    _row_start, _column_start = row_col_array
    _shape    = column_start
    _x_offset = shape
    _y_offset = x_offset
    _x_scale  = y_offset
    _y_scale  = x_scale
    _anchor   = y_scale
  else
    _row_start = row_start
    _column_start = column_start
    _shape = shape
    _x_offset = x_offset
    _y_offset = y_offset
    _x_scale = x_scale
    _y_scale = y_scale
    _anchor = anchor
  end
  raise "Insufficient arguments in insert_shape()" if [_row_start, _column_start, _shape].include?(nil)

  _shape.set_position(
    _row_start, _column_start, _x_offset, _y_offset,
    _x_scale, _y_scale, _anchor
  )
  # Assign a shape ID.
  while true
    id = _shape.id || 0
    used = @shape_hash[id]

    # Test if shape ID is already used. Otherwise assign a new one.
    if !used && id != 0
      break
    else
      @last_shape_id += 1
      _shape.id = @last_shape_id
    end
  end

  # Allow lookup of entry into shape array by shape ID.
  @shape_hash[_shape.id] = _shape.element = @shapes.size

  insert = if ptrue?(_shape.stencil)
             # Insert a copy of the shape, not a reference so that the shape is
             # used as a stencil. Previously stamped copies don't get modified
             # if the stencil is modified.
             _shape.dup
           else
             _shape
           end

  # For connectors change x/y coords based on location of connected shapes.
  insert.auto_locate_connectors(@shapes, @shape_hash)

  # Insert a link to the shape on the list of shapes. Connection to
  # the parent shape is maintained.
  @shapes << insert
  insert
end

#is_chartsheet? ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


2529
2530
2531
 
# File 'lib/write_xlsx/worksheet.rb', line 2529

def is_chartsheet? # :nodoc:
  !!@is_chartsheet
end

#keep_leading_zeros(flag = true) ⇒ Object

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.



960
961
962
 
# File 'lib/write_xlsx/worksheet.rb', line 960

def keep_leading_zeros(flag = true)
  @leading_zeros = !!flag
end

#margin_bottom=(margin) ⇒ Object

Set the bottom margin in inches. See margins=()



770
771
772
 
# File 'lib/write_xlsx/worksheet.rb', line 770

def margin_bottom=(margin)
  @page_setup.margin_bottom = remove_white_space(margin)
end

#margin_left=(margin) ⇒ Object

Set the left margin in inches. See margins=()



746
747
748
 
# File 'lib/write_xlsx/worksheet.rb', line 746

def margin_left=(margin)
  @page_setup.margin_left = remove_white_space(margin)
end

#margin_right=(margin) ⇒ Object

Set the right margin in inches. See margins=()



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

def margin_right=(margin)
  @page_setup.margin_right = remove_white_space(margin)
end

#margin_top=(margin) ⇒ Object

Set the top margin in inches. See margins=()



762
763
764
 
# File 'lib/write_xlsx/worksheet.rb', line 762

def margin_top=(margin)
  @page_setup.margin_top = remove_white_space(margin)
end

#margins=(margin) ⇒ Object

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



717
718
719
720
721
722
 
# File 'lib/write_xlsx/worksheet.rb', line 717

def margins=(margin)
  self.margin_left   = margin
  self.margin_right  = margin
  self.margin_top    = margin
  self.margin_bottom = margin
end

#margins_left_right=(margin) ⇒ Object

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



728
729
730
731
 
# File 'lib/write_xlsx/worksheet.rb', line 728

def margins_left_right=(margin)
  self.margin_left  = margin
  self.margin_right = margin
end

#margins_top_bottom=(margin) ⇒ Object

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



737
738
739
740
 
# File 'lib/write_xlsx/worksheet.rb', line 737

def margins_top_bottom=(margin)
  self.margin_top    = margin
  self.margin_bottom = margin
end

#merge_range(*args) ⇒ Object

merge_range(first_row, first_col, last_row, last_col, string, format)

Merge a range of cells. The first cell should contain the data and the others should be blank. All cells should contain the same format.



2176
2177
2178
2179
2180
2181
2182
2183
2184
2185
2186
2187
2188
2189
2190
2191
2192
2193
2194
2195
2196
2197
2198
2199
2200
2201
2202
2203
2204
2205
2206
2207
 
# File 'lib/write_xlsx/worksheet.rb', line 2176

def merge_range(*args)
  if (row_col_array = row_col_notation(args.first))
    row_first, col_first, row_last, col_last = row_col_array
    string, format, *extra_args = args[1..-1]
  else
    row_first, col_first, row_last, col_last,
    string, format, *extra_args = args
  end

  raise "Incorrect number of arguments" if [row_first, col_first, row_last, col_last, format].include?(nil)
  raise "Fifth parameter must be a format object" unless format.respond_to?(:xf_index)
  raise "Can't merge single cell" if row_first == row_last && col_first == col_last

  # Swap last row/col with first row/col as necessary
  row_first,  row_last = row_last,  row_first  if row_first > row_last
  col_first, col_last = col_last, col_first if col_first > col_last

  # Check that the data range is valid and store the max and min values.
  check_dimensions(row_first, col_first)
  check_dimensions(row_last,  col_last)
  store_row_col_max_min_values(row_first, col_first)
  store_row_col_max_min_values(row_last,  col_last)

  # Store the merge range.
  @merge << [row_first, col_first, row_last, col_last]

  # Write the first cell
  write(row_first, col_first, string, format, *extra_args)

  # Pad out the rest of the area with formatted blank cells.
  write_formatted_blank_to_area(row_first, row_last, col_first, col_last, format)
end

#merge_range_type(type, *args) ⇒ Object

Same as merge_range() above except the type of #write() is specified.



2213
2214
2215
2216
2217
2218
2219
2220
2221
2222
2223
2224
2225
2226
2227
2228
2229
2230
2231
2232
2233
2234
2235
2236
2237
2238
2239
2240
2241
2242
2243
2244
2245
2246
2247
2248
2249
2250
2251
2252
2253
2254
2255
2256
2257
2258
2259
2260
2261
2262
2263
2264
2265
2266
2267
2268
2269
2270
2271
2272
2273
2274
2275
2276
2277
2278
 
# File 'lib/write_xlsx/worksheet.rb', line 2213

def merge_range_type(type, *args)
  case type
  when 'array_formula', 'blank', 'rich_string'
    if (row_col_array = row_col_notation(args.first))
      row_first, col_first, row_last, col_last = row_col_array
      *others = args[1..-1]
    else
      row_first, col_first, row_last, col_last, *others = args
    end
    format = others.pop
  else
    if (row_col_array = row_col_notation(args.first))
      row_first, col_first, row_last, col_last = row_col_array
      token, format, *others = args[1..-1]
    else
      row_first, col_first, row_last, col_last,
      token, format, *others = args
    end
  end

  raise "Format object missing or in an incorrect position" unless format.respond_to?(:xf_index)
  raise "Can't merge single cell" if row_first == row_last && col_first == col_last

  # Swap last row/col with first row/col as necessary
  row_first, row_last = row_last, row_first if row_first > row_last
  col_first, col_last = col_last, col_first if col_first > col_last

  # Check that the data range is valid and store the max and min values.
  check_dimensions(row_first, col_first)
  check_dimensions(row_last,  col_last)
  store_row_col_max_min_values(row_first, col_first)
  store_row_col_max_min_values(row_last,  col_last)

  # Store the merge range.
  @merge << [row_first, col_first, row_last, col_last]

  # Write the first cell
  case type
  when 'blank', 'rich_string', 'array_formula'
    others << format
  end

  case type
  when 'string'
    write_string(row_first, col_first, token, format, *others)
  when 'number'
    write_number(row_first, col_first, token, format, *others)
  when 'blank'
    write_blank(row_first, col_first, *others)
  when 'date_time'
    write_date_time(row_first, col_first, token, format, *others)
  when 'rich_string'
    write_rich_string(row_first, col_first, *others)
  when 'url'
    write_url(row_first, col_first, token, format, *others)
  when 'formula'
    write_formula(row_first, col_first, token, format, *others)
  when 'array_formula'
    write_formula_array(row_first, col_first, *others)
  else
    raise "Unknown type '#{type}'"
  end

  # Pad out the rest of the area with formatted blank cells.
  write_formatted_blank_to_area(row_first, row_last, col_first, col_last, format)
end

#num_comments_block ⇒ Object 



2726
2727
2728
 
# File 'lib/write_xlsx/worksheet.rb', line 2726

def num_comments_block
  @comments.size / 1024
end

#outline_settings(visible = 1, symbols_below = 1, symbols_right = 1, auto_style = false) ⇒ Object

The outline_settings() method is used to control the appearance of outlines in Excel.



1710
1711
1712
1713
1714
1715
1716
1717
 
# File 'lib/write_xlsx/worksheet.rb', line 1710

def outline_settings(visible = 1, symbols_below = 1, symbols_right = 1, auto_style = false)
  @outline_on    = visible
  @outline_below = symbols_below
  @outline_right = symbols_right
  @outline_style = auto_style

  @outline_changed = 1
end

#palette_color(index) ⇒ Object

Convert from an Excel internal colour index to a XML style #RRGGBB index based on the default or user defined values in the Workbook palette.



2646
2647
2648
2649
2650
2651
2652
 
# File 'lib/write_xlsx/worksheet.rb', line 2646

def palette_color(index) # :nodoc:
  if index.to_s =~ /^#([0-9A-F]{6})$/i
    "FF#{::Regexp.last_match(1).upcase}"
  else
    "FF#{palette_color_from_index(index)}"
  end
end

#paper=(paper_size) ⇒ Object

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



626
627
628
 
# File 'lib/write_xlsx/worksheet.rb', line 626

def paper=(paper_size)
  @page_setup.paper = paper_size
end

#position_object_pixels(col_start, row_start, x1, y1, width, height, anchor = nil) ⇒ Object

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



2863
2864
2865
2866
2867
2868
2869
2870
2871
2872
2873
2874
2875
2876
2877
2878
2879
2880
2881
2882
2883
2884
2885
2886
2887
2888
2889
2890
2891
2892
2893
2894
2895
2896
2897
2898
2899
2900
2901
2902
2903
2904
2905
2906
2907
2908
2909
2910
2911
2912
2913
2914
2915
2916
2917
2918
2919
2920
2921
2922
2923
2924
2925
2926
2927
2928
2929
2930
2931
2932
2933
2934
2935
2936
 
# File 'lib/write_xlsx/worksheet.rb', line 2863

def position_object_pixels(col_start, row_start, x1, y1, width, height, anchor = nil) # :nodoc:
  # Adjust start column for negative offsets.
  while x1 < 0 && col_start > 0
    x1 += size_col(col_start - 1)
    col_start -= 1
  end

  # Adjust start row for negative offsets.
  while y1 < 0 && row_start > 0
    y1 += size_row(row_start - 1)
    row_start -= 1
  end

  # Ensure that the image isn't shifted off the page at top left.
  x1 = 0 if x1 < 0
  y1 = 0 if y1 < 0

  # Calculate the absolute x offset of the top-left vertex.
  x_abs = if @col_size_changed
            (0..(col_start - 1)).inject(0) { |sum, col| sum += size_col(col, anchor) }
          else
            # Optimisation for when the column widths haven't changed.
            DEFAULT_COL_PIXELS * col_start
          end
  x_abs += x1

  # Calculate the absolute y offset of the top-left vertex.
  # Store the column change to allow optimisations.
  y_abs = if @row_size_changed
            (0..(row_start - 1)).inject(0) { |sum, row| sum += size_row(row, anchor) }
          else
            # Optimisation for when the row heights haven't changed.
            @default_row_pixels * row_start
          end
  y_abs += y1

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

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

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

  # Only offset the image in the cell if the row/col isn't hidden.
  width  += x1 if size_col(col_start, anchor) > 0
  height += y1 if size_row(row_start, anchor) > 0

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

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

  # The end vertices are whatever is left from the width and height.
  x2 = width
  y2 = height

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

#prepare_chart(index, chart_id, drawing_id) ⇒ Object

Set up chart/drawings.



2536
2537
2538
2539
2540
2541
2542
2543
2544
2545
2546
2547
2548
2549
2550
2551
2552
2553
2554
2555
2556
2557
2558
2559
2560
 
# File 'lib/write_xlsx/worksheet.rb', line 2536

def prepare_chart(index, chart_id, drawing_id) # :nodoc:
  drawing_type = 1

  inserted_chart = @charts[index]
  inserted_chart.chart.id = chart_id - 1

  dimensions = position_object_emus(inserted_chart)

  # Create a Drawing object to use with worksheet unless one already exists.
  drawing = Drawing.new(
    drawing_type, dimensions, 0, 0, nil, inserted_chart.anchor,
    drawing_rel_index, 0, nil, inserted_chart.name,
    inserted_chart.description, inserted_chart.decorative
  )
  if drawings?
    @drawings.add_drawing_object(drawing)
  else
    @drawings = Drawings.new
    @drawings.add_drawing_object(drawing)
    @drawings.embedded = true

    @external_drawing_links << ['/drawing', "../drawings/drawing#{drawing_id}.xml"]
  end
  @drawing_links << ['/chart', "../charts/chart#{chart_id}.xml"]
end

#prepare_drawings(drawing_id, chart_ref_id, image_ref_id, image_ids, header_image_ids, background_ids) ⇒ Object 



2802
2803
2804
2805
2806
2807
2808
2809
2810
2811
2812
2813
2814
2815
2816
2817
2818
2819
2820
2821
2822
2823
2824
2825
2826
2827
2828
2829
2830
2831
2832
2833
2834
2835
2836
2837
2838
2839
2840
2841
2842
2843
2844
2845
2846
2847
2848
 
# File 'lib/write_xlsx/worksheet.rb', line 2802

def prepare_drawings(drawing_id, chart_ref_id, image_ref_id, image_ids, header_image_ids, background_ids)
  has_drawings = false

  # Check that some image or drawing needs to be processed.
  unless some_image_or_drawing_to_be_processed?

    # Don't increase the drawing_id header/footer images.
    unless charts.empty? && images.empty? && shapes.empty?
      drawing_id += 1
      has_drawings = true
    end

    # Prepare the background images.
    image_ref_id = prepare_background_image(background_ids, image_ref_id)

    # Prepare the worksheet images.
    images.each do |image|
      image_ref_id = prepare_image(image, drawing_id, image_ids, image_ref_id)
    end

    # Prepare the worksheet charts.
    charts.each_with_index do |_chart, index|
      chart_ref_id += 1
      prepare_chart(index, chart_ref_id, drawing_id)
    end

    # Prepare the worksheet shapes.
    shapes.each_with_index do |_shape, index|
      prepare_shape(index, drawing_id)
    end

    # Prepare the header and footer images.
    [header_images, footer_images].each do |images|
      images.each do |image|
        image_ref_id = prepare_header_footer_image(
          image, header_image_ids, image_ref_id
        )
      end
    end

    if has_drawings
      @workbook.drawings << drawings
    end
  end

  [drawing_id, chart_ref_id, image_ref_id]
end

#prepare_formula(given_formula, expand_future_functions = nil) ⇒ Object

Utility method to strip equal sign and array braces from a formula and also expand out future and dynamic array formulas.



1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
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
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
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
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
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
1462
1463
1464
1465
1466
1467
 
# File 'lib/write_xlsx/worksheet.rb', line 1295

def prepare_formula(given_formula, expand_future_functions = nil)
  # Ignore empty/null formulas.
  return given_formula unless ptrue?(given_formula)

  # Remove array formula braces and the leading =.
  formula = given_formula.sub(/^\{(.*)\}$/, '\1').sub(/^=/, '')

  # # Don't expand formulas that the user has already expanded.
  return formula if formula =~ /_xlfn\./

  # Expand dynamic array formulas.
  formula = expand_formula(formula, 'ANCHORARRAY\(')
  formula = expand_formula(formula, 'BYCOL\(')
  formula = expand_formula(formula, 'BYROW\(')
  formula = expand_formula(formula, 'CHOOSECOLS\(')
  formula = expand_formula(formula, 'CHOOSEROWS\(')
  formula = expand_formula(formula, 'DROP\(')
  formula = expand_formula(formula, 'EXPAND\(')
  formula = expand_formula(formula, 'FILTER\(', '._xlws')
  formula = expand_formula(formula, 'HSTACK\(')
  formula = expand_formula(formula, 'LAMBDA\(')
  formula = expand_formula(formula, 'MAKEARRAY\(')
  formula = expand_formula(formula, 'MAP\(')
  formula = expand_formula(formula, 'RANDARRAY\(')
  formula = expand_formula(formula, 'REDUCE\(')
  formula = expand_formula(formula, 'SCAN\(')
  formula = expand_formula(formula, 'SEQUENCE\(')
  formula = expand_formula(formula, 'SINGLE\(')
  formula = expand_formula(formula, 'SORT\(', '._xlws')
  formula = expand_formula(formula, 'SORTBY\(')
  formula = expand_formula(formula, 'SWITCH\(')
  formula = expand_formula(formula, 'TAKE\(')
  formula = expand_formula(formula, 'TEXTSPLIT\(')
  formula = expand_formula(formula, 'TOCOL\(')
  formula = expand_formula(formula, 'TOROW\(')
  formula = expand_formula(formula, 'UNIQUE\(')
  formula = expand_formula(formula, 'VSTACK\(')
  formula = expand_formula(formula, 'WRAPCOLS\(')
  formula = expand_formula(formula, 'WRAPROWS\(')
  formula = expand_formula(formula, 'XLOOKUP\(')

  if !@use_future_functions && !ptrue?(expand_future_functions)
    return formula
  end

  # Future functions.
  formula = expand_formula(formula, 'ACOTH\(')
  formula = expand_formula(formula, 'ACOT\(')
  formula = expand_formula(formula, 'AGGREGATE\(')
  formula = expand_formula(formula, 'ARABIC\(')
  formula = expand_formula(formula, 'ARRAYTOTEXT\(')
  formula = expand_formula(formula, 'BASE\(')
  formula = expand_formula(formula, 'BETA.DIST\(')
  formula = expand_formula(formula, 'BETA.INV\(')
  formula = expand_formula(formula, 'BINOM.DIST.RANGE\(')
  formula = expand_formula(formula, 'BINOM.DIST\(')
  formula = expand_formula(formula, 'BINOM.INV\(')
  formula = expand_formula(formula, 'BITAND\(')
  formula = expand_formula(formula, 'BITLSHIFT\(')
  formula = expand_formula(formula, 'BITOR\(')
  formula = expand_formula(formula, 'BITRSHIFT\(')
  formula = expand_formula(formula, 'BITXOR\(')
  formula = expand_formula(formula, 'CEILING.MATH\(')
  formula = expand_formula(formula, 'CEILING.PRECISE\(')
  formula = expand_formula(formula, 'CHISQ.DIST.RT\(')
  formula = expand_formula(formula, 'CHISQ.DIST\(')
  formula = expand_formula(formula, 'CHISQ.INV.RT\(')
  formula = expand_formula(formula, 'CHISQ.INV\(')
  formula = expand_formula(formula, 'CHISQ.TEST\(')
  formula = expand_formula(formula, 'COMBINA\(')
  formula = expand_formula(formula, 'CONCAT\(')
  formula = expand_formula(formula, 'CONFIDENCE.NORM\(')
  formula = expand_formula(formula, 'CONFIDENCE.T\(')
  formula = expand_formula(formula, 'COTH\(')
  formula = expand_formula(formula, 'COT\(')
  formula = expand_formula(formula, 'COVARIANCE.P\(')
  formula = expand_formula(formula, 'COVARIANCE.S\(')
  formula = expand_formula(formula, 'CSCH\(')
  formula = expand_formula(formula, 'CSC\(')
  formula = expand_formula(formula, 'DAYS\(')
  formula = expand_formula(formula, 'DECIMAL\(')
  formula = expand_formula(formula, 'ERF.PRECISE\(')
  formula = expand_formula(formula, 'ERFC.PRECISE\(')
  formula = expand_formula(formula, 'EXPON.DIST\(')
  formula = expand_formula(formula, 'F.DIST.RT\(')
  formula = expand_formula(formula, 'F.DIST\(')
  formula = expand_formula(formula, 'F.INV.RT\(')
  formula = expand_formula(formula, 'F.INV\(')
  formula = expand_formula(formula, 'F.TEST\(')
  formula = expand_formula(formula, 'FILTERXML\(')
  formula = expand_formula(formula, 'FLOOR.MATH\(')
  formula = expand_formula(formula, 'FLOOR.PRECISE\(')
  formula = expand_formula(formula, 'FORECAST.ETS.CONFINT\(')
  formula = expand_formula(formula, 'FORECAST.ETS.SEASONALITY\(')
  formula = expand_formula(formula, 'FORECAST.ETS.STAT\(')
  formula = expand_formula(formula, 'FORECAST.ETS\(')
  formula = expand_formula(formula, 'FORECAST.LINEAR\(')
  formula = expand_formula(formula, 'FORMULATEXT\(')
  formula = expand_formula(formula, 'GAMMA.DIST\(')
  formula = expand_formula(formula, 'GAMMA.INV\(')
  formula = expand_formula(formula, 'GAMMALN.PRECISE\(')
  formula = expand_formula(formula, 'GAMMA\(')
  formula = expand_formula(formula, 'GAUSS\(')
  formula = expand_formula(formula, 'HYPGEOM.DIST\(')
  formula = expand_formula(formula, 'IFNA\(')
  formula = expand_formula(formula, 'IFS\(')
  formula = expand_formula(formula, 'IMAGE\(')
  formula = expand_formula(formula, 'IMCOSH\(')
  formula = expand_formula(formula, 'IMCOT\(')
  formula = expand_formula(formula, 'IMCSCH\(')
  formula = expand_formula(formula, 'IMCSC\(')
  formula = expand_formula(formula, 'IMSECH\(')
  formula = expand_formula(formula, 'IMSEC\(')
  formula = expand_formula(formula, 'IMSINH\(')
  formula = expand_formula(formula, 'IMTAN\(')
  formula = expand_formula(formula, 'ISFORMULA\(')
  formula = expand_formula(formula, 'ISOMITTED\(')
  formula = expand_formula(formula, 'ISOWEEKNUM\(')
  formula = expand_formula(formula, 'LET\(')
  formula = expand_formula(formula, 'LOGNORM.DIST\(')
  formula = expand_formula(formula, 'LOGNORM.INV\(')
  formula = expand_formula(formula, 'MAXIFS\(')
  formula = expand_formula(formula, 'MINIFS\(')
  formula = expand_formula(formula, 'MODE.MULT\(')
  formula = expand_formula(formula, 'MODE.SNGL\(')
  formula = expand_formula(formula, 'MUNIT\(')
  formula = expand_formula(formula, 'NEGBINOM.DIST\(')
  formula = expand_formula(formula, 'NORM.DIST\(')
  formula = expand_formula(formula, 'NORM.INV\(')
  formula = expand_formula(formula, 'NORM.S.DIST\(')
  formula = expand_formula(formula, 'NORM.S.INV\(')
  formula = expand_formula(formula, 'NUMBERVALUE\(')
  formula = expand_formula(formula, 'PDURATION\(')
  formula = expand_formula(formula, 'PERCENTILE.EXC\(')
  formula = expand_formula(formula, 'PERCENTILE.INC\(')
  formula = expand_formula(formula, 'PERCENTRANK.EXC\(')
  formula = expand_formula(formula, 'PERCENTRANK.INC\(')
  formula = expand_formula(formula, 'PERMUTATIONA\(')
  formula = expand_formula(formula, 'PHI\(')
  formula = expand_formula(formula, 'POISSON.DIST\(')
  formula = expand_formula(formula, 'QUARTILE.EXC\(')
  formula = expand_formula(formula, 'QUARTILE.INC\(')
  formula = expand_formula(formula, 'QUERYSTRING\(')
  formula = expand_formula(formula, 'RANK.AVG\(')
  formula = expand_formula(formula, 'RANK.EQ\(')
  formula = expand_formula(formula, 'RRI\(')
  formula = expand_formula(formula, 'SECH\(')
  formula = expand_formula(formula, 'SEC\(')
  formula = expand_formula(formula, 'SHEETS\(')
  formula = expand_formula(formula, 'SHEET\(')
  formula = expand_formula(formula, 'SKEW.P\(')
  formula = expand_formula(formula, 'STDEV.P\(')
  formula = expand_formula(formula, 'STDEV.S\(')
  formula = expand_formula(formula, 'T.DIST.2T\(')
  formula = expand_formula(formula, 'T.DIST.RT\(')
  formula = expand_formula(formula, 'T.DIST\(')
  formula = expand_formula(formula, 'T.INV.2T\(')
  formula = expand_formula(formula, 'T.INV\(')
  formula = expand_formula(formula, 'T.TEST\(')
  formula = expand_formula(formula, 'TEXTAFTER\(')
  formula = expand_formula(formula, 'TEXTBEFORE\(')
  formula = expand_formula(formula, 'TEXTJOIN\(')
  formula = expand_formula(formula, 'UNICHAR\(')
  formula = expand_formula(formula, 'UNICODE\(')
  formula = expand_formula(formula, 'VALUETOTEXT\(')
  formula = expand_formula(formula, 'VAR.P\(')
  formula = expand_formula(formula, 'VAR.S\(')
  formula = expand_formula(formula, 'WEBSERVICE\(')
  formula = expand_formula(formula, 'WEIBULL.DIST\(')
  formula = expand_formula(formula, 'XMATCH\(')
  formula = expand_formula(formula, 'XOR\(')
  expand_formula(formula, 'Z.TEST\(')
end

#prepare_header_vml_objects(vml_header_id, vml_drawing_id) ⇒ Object

Setup external linkage for VML header/footer images.



2698
2699
2700
2701
 
# File 'lib/write_xlsx/worksheet.rb', line 2698

def prepare_header_vml_objects(vml_header_id, vml_drawing_id)
  @vml_header_id = vml_header_id
  @external_vml_links << ['/vmlDrawing', "../drawings/vmlDrawing#{vml_drawing_id}.vml"]
end

#prepare_tables(table_id, seen) ⇒ Object

Set the table ids for the worksheet tables.



2706
2707
2708
2709
2710
2711
2712
2713
2714
2715
2716
2717
2718
2719
2720
2721
2722
2723
2724
 
# File 'lib/write_xlsx/worksheet.rb', line 2706

def prepare_tables(table_id, seen)
  if tables_count > 0
    id = table_id
    tables.each do |table|
      table.prepare(id)

      if seen[table.name]
        raise "error: invalid duplicate table name '#{table.name}' found."
      else
        seen[table.name] = 1
      end

      # Store the link used for the rels file.
      @external_table_links << ['/table', "../tables/table#{id}.xml"]
      id += 1
    end
  end
  tables_count || 0
end

#prepare_vml_objects(vml_data_id, vml_shape_id, vml_drawing_id, comment_id) ⇒ Object

Turn the HoH that stores the comments into an array for easier handling and set the external links for comments and buttons.



2681
2682
2683
2684
2685
2686
2687
2688
2689
2690
2691
2692
2693
 
# File 'lib/write_xlsx/worksheet.rb', line 2681

def prepare_vml_objects(vml_data_id, vml_shape_id, vml_drawing_id, comment_id)
  set_external_vml_links(vml_drawing_id)
  set_external_comment_links(comment_id) if has_comments?

  # The VML o:idmap data id contains a comma separated range when there is
  # more than one 1024 block of comments, like this: data="1,2".
  data = "#{vml_data_id}"
  (1..num_comments_block).each do |i|
    data += ",#{vml_data_id + i}"
  end
  @vml_data_id = data
  @vml_shape_id = vml_shape_id
end

#print_across(across = true) ⇒ Object

Set the order in which pages are printed.



981
982
983
984
985
986
987
988
 
# File 'lib/write_xlsx/worksheet.rb', line 981

def print_across(across = true)
  if across
    @page_setup.across             = true
    @page_setup.page_setup_changed = true
  else
    @page_setup.across = false
  end
end

#print_area(*args) ⇒ Object

:call-seq:

print_area(first_row, first_col, last_row, last_col)

This method is used to specify the area of the worksheet that will be printed. All four parameters must be specified. You can also use A1 notation.



887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
 
# File 'lib/write_xlsx/worksheet.rb', line 887

def print_area(*args)
  return @page_setup.print_area.dup if args.empty?

  if (row_col_array = row_col_notation(args.first))
    row1, col1, row2, col2 = row_col_array
  else
    row1, col1, row2, col2 = args
  end

  return if [row1, col1, row2, col2].include?(nil)

  # Ignore max print area since this is the same as no print area for Excel.
  return if row1 == 0 && col1 == 0 && row2 == ROW_MAX - 1 && col2 == COL_MAX - 1

  # Build up the print area range "=Sheet2!R1C1:R2C1"
  @page_setup.print_area = convert_name_area(row1, col1, row2, col2)
end

#print_black_and_white ⇒ Object

Set the option to print the worksheet in black and white.



951
952
953
954
 
# File 'lib/write_xlsx/worksheet.rb', line 951

def print_black_and_white
  @page_setup.black_white        = true
  @page_setup.page_setup_changed = true
end

#print_repeat_cols ⇒ Object

:nodoc:



875
876
877
 
# File 'lib/write_xlsx/worksheet.rb', line 875

def print_repeat_cols  # :nodoc:
  @page_setup.repeat_cols
end

#print_repeat_rows ⇒ Object

:nodoc:



853
854
855
 
# File 'lib/write_xlsx/worksheet.rb', line 853

def print_repeat_rows   # :nodoc:
  @page_setup.repeat_rows
end

#print_row_col_headers(headers = true) ⇒ Object

Set the option to print the row and column headers on the printed page.



2364
2365
2366
2367
2368
2369
2370
2371
2372
 
# File 'lib/write_xlsx/worksheet.rb', line 2364

def print_row_col_headers(headers = true)
  @page_setup.print_row_col_headers(headers)
  # if headers
  #   @print_headers         = 1
  #   @page_setup.print_options_changed = 1
  # else
  #   @print_headers = 0
  # end
end

#print_scale=(scale = 100) ⇒ Object

Set the scale factor of the printed page. Scale factors in the range 10 <= scale <= 400 are valid:



928
929
930
931
932
933
934
935
936
937
938
 
# File 'lib/write_xlsx/worksheet.rb', line 928

def print_scale=(scale = 100)
  scale_val = scale.to_i
  # Confine the scale to Excel's range
  scale_val = 100 if scale_val < 10 || scale_val > 400

  # Turn off "fit to page" option.
  @page_setup.fit_page = false

  @page_setup.scale              = scale_val
  @page_setup.page_setup_changed = true
end

#protect(password = nil, options = {}) ⇒ Object

Set the worksheet protection flags to prevent modification of worksheet objects.



265
266
267
268
269
270
271
272
273
274
 
# File 'lib/write_xlsx/worksheet.rb', line 265

def protect(password = nil, options = {})
  check_parameter(options, protect_default_settings.keys, 'protect')
  @protect = protect_default_settings.merge(options)

  # Set the password after the user defined values.
  if password && password != ''
    @protect[:password] =
      encode_password(password)
  end
end

#repeat_columns(*args) ⇒ Object

:call-seq:

repeat_columns(first_col, last_col = nil)

Set the columns to repeat at the left hand side of each printed page.



863
864
865
866
867
868
869
870
871
872
873
 
# File 'lib/write_xlsx/worksheet.rb', line 863

def repeat_columns(*args)
  if args[0] =~ /^\D/
    _dummy, first_col, _dummy, last_col = substitute_cellref(*args)
  else
    first_col, last_col = args
  end
  last_col ||= first_col

  area = "#{xl_col_to_name(first_col, 1)}:#{xl_col_to_name(last_col, 1)}"
  @page_setup.repeat_cols = "#{quote_sheetname(@name)}!#{area}"
end

#repeat_formula(row, col, formula, format, *pairs) ⇒ Object

:call-seq:

repeat_formula(row, column, formula [ , format ])

Deprecated. This is a writeexcel gem’s method that is no longer required by WriteXLSX.

Raises:



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
 
# File 'lib/write_xlsx/worksheet.rb', line 2055

def repeat_formula(row, col, formula, format, *pairs)
  # Check for a cell reference in A1 notation and substitute row and column.
  if (row_col_array = row_col_notation(row))
    _row, _col = row_col_array
    _formula   = col
    _format    = formula
    _pairs     = [format] + pairs
  else
    _row = row
    _col = col
    _formula = formula
    _format = format
    _pairs = pairs
  end
  raise WriteXLSXInsufficientArgumentError if [_row, _col].include?(nil)

  raise "Odd number of elements in pattern/replacement list" unless _pairs.size.even?
  raise "Not a valid formula" unless _formula.respond_to?(:to_ary)

  tokens  = _formula.join("\t").split("\t")
  raise "No tokens in formula" if tokens.empty?

  _value = nil
  if _pairs[-2] == 'result'
    _value = _pairs.pop
    _pairs.pop
  end
  until _pairs.empty?
    pattern = _pairs.shift
    replace = _pairs.shift

    tokens.each do |token|
      break if token.sub!(pattern, replace)
    end
  end
  _formula = tokens.join('')
  write_formula(_row, _col, _formula, _format, _value)
end

#repeat_rows(row_min, row_max = nil) ⇒ Object

Set the number of rows to repeat at the top of each printed page.



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

def repeat_rows(row_min, row_max = nil)
  row_max ||= row_min

  # Convert to 1 based.
  row_min += 1
  row_max += 1

  area = "$#{row_min}:$#{row_max}"

  # Build up the print titles "Sheet1!$1:$2"
  sheetname = quote_sheetname(@name)
  @page_setup.repeat_rows = "#{sheetname}!#{area}"
end

#right_to_left(flag = true) ⇒ Object

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



967
968
969
 
# File 'lib/write_xlsx/worksheet.rb', line 967

def right_to_left(flag = true)
  @right_to_left = !!flag
end

#select ⇒ Object

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



211
212
213
214
 
# File 'lib/write_xlsx/worksheet.rb', line 211

def select
  @hidden   = false  # Selected worksheet can't be hidden.
  @selected = true
end

#set_background(image) ⇒ Object

Set the background image for the worksheet.



2853
2854
2855
2856
2857
 
# File 'lib/write_xlsx/worksheet.rb', line 2853

def set_background(image)
  raise "Couldn't locate #{image}: $!" unless File.exist?(image)

  @background_image = ImageProperty.new(image)
end

#set_column(*args) ⇒ Object

:call-seq:

set_column(firstcol, lastcol, width, format, hidden, level, collapsed)

This method can be used to change the default properties of a single column or a range of columns. All parameters apart from first_col and last_col are optional.



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
 
# File 'lib/write_xlsx/worksheet.rb', line 302

def set_column(*args)
  # Check for a cell reference in A1 notation and substitute row and column
  # ruby 3.2 no longer handles =~ for various types
  if args[0].respond_to?(:=~) && args[0].to_s =~ /^\D/
    _row1, firstcol, _row2, lastcol, *data = substitute_cellref(*args)
  else
    firstcol, lastcol, *data = args
  end

  # Ensure at least firstcol, lastcol and width
  return unless firstcol && lastcol && !data.empty?

  # Assume second column is the same as first if 0. Avoids KB918419 bug.
  lastcol = firstcol unless ptrue?(lastcol)

  # Ensure 2nd col is larger than first. Also for KB918419 bug.
  firstcol, lastcol = lastcol, firstcol if firstcol > lastcol

  width, format, hidden, level, collapsed = data
  autofit = 0

  # Check that cols are valid and store max and min values with default row.
  # NOTE: The check shouldn't modify the row dimensions and should only modify
  #       the column dimensions in certain cases.
  ignore_row = 1
  ignore_col = 1
  ignore_col = 0 if format.respond_to?(:xf_index)   # Column has a format.
  ignore_col = 0 if width && ptrue?(hidden)         # Column has a width but is hidden

  check_dimensions_and_update_max_min_values(0, firstcol, ignore_row, ignore_col)
  check_dimensions_and_update_max_min_values(0, lastcol,  ignore_row, ignore_col)

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

  # Excel has a maximum column width of 255 characters.
  width = 255.0 if width && width > 255.0

  @outline_col_level = level if level > @outline_col_level

  # Store the column data based on the first column. Padded for sorting.
  (firstcol..lastcol).each do |col|
    @col_info[col] =
      COLINFO.new(width, format, hidden, level, collapsed, autofit)
  end

  # Store the column change to allow optimisations.
  @col_size_changed = true
end

#set_column_pixels(*data) ⇒ Object

Set the width (and properties) of a single column or a range of columns in pixels rather than character units.



358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
 
# File 'lib/write_xlsx/worksheet.rb', line 358

def set_column_pixels(*data)
  cell = data[0]

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

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

  # Ensure at least $first_col, $last_col and $width
  return if data.size < 3

  first_col, last_col, pixels, format, hidden, level = data
  hidden ||= 0

  width = pixels_to_width(pixels) if ptrue?(pixels)

  set_column(first_col, last_col, width, format, hidden, level)
end

#set_comments_author(author) ⇒ Object

This method is deprecated. use comments_author=().



2508
2509
2510
2511
 
# File 'lib/write_xlsx/worksheet.rb', line 2508

def set_comments_author(author)
  put_deprecate_message("#{self}.set_comments_author")
  self.comments_author = author
end

#set_default_row(height = nil, zero_height = nil) ⇒ Object

Set the default row properties



2156
2157
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
2168
 
# File 'lib/write_xlsx/worksheet.rb', line 2156

def set_default_row(height = nil, zero_height = nil)
  height      ||= @original_row_height
  zero_height ||= 0

  if height != @original_row_height
    @default_row_height = height

    # Store the row change to allow optimisations.
    @row_size_changed = 1
  end

  @default_row_zeroed = 1 if ptrue?(zero_height)
end

#set_first_sheet ⇒ Object

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.



256
257
258
259
 
# File 'lib/write_xlsx/worksheet.rb', line 256

def set_first_sheet
  @hidden = false
  @workbook.firstsheet = @index
end

#set_footer(string = '', margin = 0.3, options = {}) ⇒ Object

Set the page footer caption and optional margin.



672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
 
# File 'lib/write_xlsx/worksheet.rb', line 672

def set_footer(string = '', margin = 0.3, options = {})
  raise 'Footer string must be less than 255 characters' if string.length > 255

  # Replace the Excel placeholder &[Picture] with the internal &G.
  @page_setup.footer = string.gsub("&[Picture]", '&G')

  @page_setup.header_footer_aligns = options[:align_with_margins] if options[:align_with_margins]

  @page_setup.header_footer_scales = options[:scale_with_doc] if options[:scale_with_doc]

  # Reset the array in case the function is called more than once.
  @footer_images = []

  [
    [:image_left, 'LF'], [:image_center, 'CF'], [:image_right, 'RF']
  ].each do |p|
    @footer_images << ImageProperty.new(options[p.first], position: p.last) if options[p.first]
  end

  # placeholeder /&G/ の数
  placeholder_count = @page_setup.footer.scan("&G").count

  raise "Number of footer image (#{@footer_images.size}) doesn't match placeholder count (#{placeholder_count}) in string: #{@page_setup.footer}" if @footer_images.size != placeholder_count

  @page_setup.margin_footer         = margin
  @page_setup.header_footer_changed = true
end

#set_h_pagebreaks(*args) ⇒ Object

Store the horizontal page breaks on a worksheet.



2478
2479
2480
2481
2482
2483
 
# File 'lib/write_xlsx/worksheet.rb', line 2478

def set_h_pagebreaks(*args)
  breaks = args.collect do |brk|
    Array(brk)
  end.flatten
  @page_setup.hbreaks += breaks
end

#set_header(string = '', margin = 0.3, options = {}) ⇒ Object

Set the page header caption and optional margin.



638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
 
# File 'lib/write_xlsx/worksheet.rb', line 638

def set_header(string = '', margin = 0.3, options = {})
  raise 'Header string must be less than 255 characters' if string.length > 255

  # Replace the Excel placeholder &[Picture] with the internal &G.
  header_footer_string = string.gsub("&[Picture]", '&G')
  # placeholeder /&G/ の数
  placeholder_count = header_footer_string.scan("&G").count
  @page_setup.header = header_footer_string

  @page_setup.header_footer_aligns = options[:align_with_margins] if options[:align_with_margins]

  @page_setup.header_footer_scales = options[:scale_with_doc] if options[:scale_with_doc]

  # Reset the array in case the function is called more than once.
  @header_images = []

  [
    [:image_left, 'LH'], [:image_center, 'CH'], [:image_right, 'RH']
  ].each do |p|
    @header_images << ImageProperty.new(options[p.first], position: p.last) if options[p.first]
  end

  # # placeholeder /&G/ の数
  # placeholder_count = @page_setup.header.scan("&G").count

  raise "Number of header image (#{@header_images.size}) doesn't match placeholder count (#{placeholder_count}) in string: #{@page_setup.header}" if @header_images.size != placeholder_count

  @page_setup.margin_header         = margin || 0.3
  @page_setup.header_footer_changed = true
end

#set_landscape ⇒ Object

Set the page orientation as landscape.



589
590
591
592
 
# File 'lib/write_xlsx/worksheet.rb', line 589

def set_landscape
  @page_setup.orientation         = false
  @page_setup.page_setup_changed  = true
end

#set_margin_bottom(margin = 0.75) ⇒ Object

this method is deprecated. use margin_bottom=() Set the bottom margin in inches.



831
832
833
834
 
# File 'lib/write_xlsx/worksheet.rb', line 831

def set_margin_bottom(margin = 0.75)
  put_deprecate_message("#{self}.set_margin_bottom")
  self.margin_bottom = margin
end

#set_margin_left(margin = 0.7) ⇒ Object

this method is deprecated. use margin_left=() Set the left margin in inches.



804
805
806
807
 
# File 'lib/write_xlsx/worksheet.rb', line 804

def set_margin_left(margin = 0.7)
  put_deprecate_message("#{self}.set_margin_left")
  self.margin_left = margin
end

#set_margin_right(margin = 0.7) ⇒ Object

this method is deprecated. use margin_right=() Set the right margin in inches.



813
814
815
816
 
# File 'lib/write_xlsx/worksheet.rb', line 813

def set_margin_right(margin = 0.7)
  put_deprecate_message("#{self}.set_margin_right")
  self.margin_right = margin
end

#set_margin_top(margin = 0.75) ⇒ Object

this method is deprecated. use margin_top=() Set the top margin in inches.



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

def set_margin_top(margin = 0.75)
  put_deprecate_message("#{self}.set_margin_top")
  self.margin_top = margin
end

#set_margins(margin) ⇒ Object

set_margin_* methods are deprecated. use margin_*=().



777
778
779
780
 
# File 'lib/write_xlsx/worksheet.rb', line 777

def set_margins(margin)
  put_deprecate_message("#{self}.set_margins")
  self.margins = margin
end

#set_margins_LR(margin) ⇒ Object

this method is deprecated. use margin_left_right=(). Set the left and right margins to the same value in inches.



786
787
788
789
 
# File 'lib/write_xlsx/worksheet.rb', line 786

def set_margins_LR(margin)
  put_deprecate_message("#{self}.set_margins_LR")
  self.margins_left_right = margin
end

#set_margins_TB(margin) ⇒ Object

this method is deprecated. use margin_top_bottom=(). Set the top and bottom margins to the same value in inches.



795
796
797
798
 
# File 'lib/write_xlsx/worksheet.rb', line 795

def set_margins_TB(margin)
  put_deprecate_message("#{self}.set_margins_TB")
  self.margins_top_bottom = margin
end

#set_page_view(flag = 1) ⇒ Object

This method is used to display the worksheet in “Page View/Layout” mode.



597
598
599
 
# File 'lib/write_xlsx/worksheet.rb', line 597

def set_page_view(flag = 1)
  @page_view = flag
end

#set_pagebreak_view ⇒ Object

set_pagebreak_view

Set the page view mode.



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

def set_pagebreak_view
  @page_view = 2
end

#set_paper(paper_size) ⇒ Object 



630
631
632
633
 
# File 'lib/write_xlsx/worksheet.rb', line 630

def set_paper(paper_size)
  put_deprecate_message("#{self}.set_paper")
  self.paper = paper_size
end

#set_portrait ⇒ Object

Set the page orientation as portrait. The default worksheet orientation is portrait, so you won’t generally need to call this method.



581
582
583
584
 
# File 'lib/write_xlsx/worksheet.rb', line 581

def set_portrait
  @page_setup.orientation        = true
  @page_setup.page_setup_changed = true
end

#set_print_scale(scale = 100) ⇒ Object

This method is deprecated. use print_scale=().



943
944
945
946
 
# File 'lib/write_xlsx/worksheet.rb', line 943

def set_print_scale(scale = 100)
  put_deprecate_message("#{self}.set_print_scale")
  self.print_scale = (scale)
end

#set_row(*args) ⇒ Object

:call-seq:

set_row(row [ , height, format, hidden, level, collapsed ])

This method can be used to change the default properties of a row. All parameters apart from row are optional.



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
 
# File 'lib/write_xlsx/worksheet.rb', line 2101

def set_row(*args)
  return unless args[0]

  row = args[0]
  height = args[1] || @default_height
  xf     = args[2]
  hidden = args[3] || 0
  level  = args[4] || 0
  collapsed = args[5] || 0

  # Use min col in check_dimensions. Default to 0 if undefined.
  min_col = @dim_colmin || 0

  # Check that row and col are valid and store max and min values.
  check_dimensions(row, min_col)
  store_row_col_max_min_values(row, min_col)

  height ||= @default_row_height

  # If the height is 0 the row is hidden and the height is the default.
  if height == 0
    hidden = 1
    height = @default_row_height
  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

  # Store the row properties.
  @set_rows[row] = [height, xf, hidden, level, collapsed]

  # Store the row change to allow optimisations.
  @row_size_changed = true

  # Store the row sizes for use when calculating image vertices.
  @row_sizes[row] = [height, hidden]
end

#set_row_pixels(*data) ⇒ Object

This method is used to set the height (in pixels) and the properties of the row.



2146
2147
2148
2149
2150
2151
 
# File 'lib/write_xlsx/worksheet.rb', line 2146

def set_row_pixels(*data)
  height = data[1]

  data[1] = pixels_to_height(height) if ptrue?(height)
  set_row(*data)
end

#set_selection(*args) ⇒ Object

:call-seq:

set_selection(cell_or_cell_range)

Set which cell or cells are selected in a worksheet.



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
 
# File 'lib/write_xlsx/worksheet.rb', line 493

def set_selection(*args)
  return if args.empty?

  if (row_col_array = row_col_notation(args.first))
    row_first, col_first, row_last, col_last = row_col_array
  else
    row_first, col_first, row_last, col_last = args
  end

  active_cell = xl_rowcol_to_cell(row_first, col_first)

  if row_last  # Range selection.
    # Swap last row/col for first row/col as necessary
    row_first, row_last = row_last, row_first if row_first > row_last
    col_first, col_last = col_last, col_first if col_first > col_last

    sqref = xl_range(row_first, row_last, col_first, col_last)
  else          # Single cell selection.
    sqref = active_cell
  end

  # Selection isn't set for cell A1.
  return if sqref == 'A1'

  @selections = [[nil, active_cell, sqref]]
end

#set_start_page(page_start) ⇒ Object 



998
999
1000
1001
 
# File 'lib/write_xlsx/worksheet.rb', line 998

def set_start_page(page_start)
  put_deprecate_message("#{self}.set_start_page")
  self.start_page = page_start
end

#set_tab_color(color) ⇒ Object

This method is deprecated. use tab_color=().



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

def set_tab_color(color)
  put_deprecate_message("#{self}.set_tab_color")
  self.tab_color = color
end

#set_top_left_cell(row, col = nil) ⇒ Object

set_top_left_cell()

Set the first visible cell at the top left of the worksheet.



526
527
528
529
530
531
532
533
534
535
 
# File 'lib/write_xlsx/worksheet.rb', line 526

def set_top_left_cell(row, col = nil)
  if (row_col_array = row_col_notation(row))
    _row, _col = row_col_array
  else
    _row = row
    _col = col
  end

  @top_left_cell = xl_rowcol_to_cell(_row, _col)
end

#set_v_pagebreaks(*args) ⇒ Object

Store the vertical page breaks on a worksheet.



2488
2489
2490
 
# File 'lib/write_xlsx/worksheet.rb', line 2488

def set_v_pagebreaks(*args)
  @page_setup.vbreaks += args
end

#set_vba_name(vba_codename = nil) ⇒ Object

set the vba name for the worksheet



2745
2746
2747
 
# File 'lib/write_xlsx/worksheet.rb', line 2745

def set_vba_name(vba_codename = nil)
  @vba_codename = vba_codename || @name
end

#set_xml_writer(filename) ⇒ Object

:nodoc:



168
169
170
 
# File 'lib/write_xlsx/worksheet.rb', line 168

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

#set_zoom(scale) ⇒ Object

This method is deprecated. use zoom=().



919
920
921
922
 
# File 'lib/write_xlsx/worksheet.rb', line 919

def set_zoom(scale)
  put_deprecate_message("#{self}.set_zoom")
  self.zoom = scale
end

#show_comments(visible = true) ⇒ Object

This method is used to make all cell comments visible when a worksheet is opened.



2496
2497
2498
 
# File 'lib/write_xlsx/worksheet.rb', line 2496

def show_comments(visible = true)
  @comments_visible = visible
end

#some_image_or_drawing_to_be_processed? ⇒ Boolean

Check that some image or drawing needs to be processed.

Returns:

  • (Boolean) 


2798
2799
2800
 
# File 'lib/write_xlsx/worksheet.rb', line 2798

def some_image_or_drawing_to_be_processed?
  charts.size + images.size + shapes.size + header_images.size + footer_images.size + (background_image ? 1 : 0) == 0
end

#sorted_comments ⇒ Object

:nodoc:



2597
2598
2599
 
# File 'lib/write_xlsx/worksheet.rb', line 2597

def sorted_comments # :nodoc:
  @comments.sorted_comments
end

#split_panes(*args) ⇒ Object

:call-seq:

split_panes(y, x, top_row, left_col)

Set panes and mark them as split.



571
572
573
574
 
# File 'lib/write_xlsx/worksheet.rb', line 571

def split_panes(*args)
  # Call freeze panes but add the type flag for split panes.
  freeze_panes(args[0], args[1], args[2], args[3], 2)
end

#start_page=(page_start) ⇒ Object

The start_page=() method is used to set the number of the starting page when the worksheet is printed out.



994
995
996
 
# File 'lib/write_xlsx/worksheet.rb', line 994

def start_page=(page_start)
  @page_setup.page_start = page_start
end

#store_formula(string) ⇒ Object

Deprecated. This is a writeexcel method that is no longer required by WriteXLSX. See below.



1723
1724
1725
 
# File 'lib/write_xlsx/worksheet.rb', line 1723

def store_formula(string)
  string.split(/(\$?[A-I]?[A-Z]\$?\d+)/)
end

#tab_color=(color) ⇒ Object

Set the colour of the worksheet tab.



613
614
615
 
# File 'lib/write_xlsx/worksheet.rb', line 613

def tab_color=(color)
  @tab_color = Colors.new.color(color)
end

#tables_count ⇒ Object 



2730
2731
2732
 
# File 'lib/write_xlsx/worksheet.rb', line 2730

def tables_count
  @tables.size
end

#unprotect_range(range, range_name = nil, password = nil) ⇒ Object

Unprotect ranges within a protected worksheet.



279
280
281
282
283
284
285
286
287
288
289
290
291
292
 
# File 'lib/write_xlsx/worksheet.rb', line 279

def unprotect_range(range, range_name = nil, password = nil)
  if range.nil?
    raise "The range must be defined in unprotect_range())\n"
  else
    range = range.gsub("$", "")
    range = range.sub(/^=/, "")
    @num_protected_ranges += 1
  end

  range_name ||= "Range#{@num_protected_ranges}"
  password   &&= encode_password(password)

  @protected_ranges << [range, range_name, password]
end

#update_format_with_params(row, col, params = nil) ⇒ Object

:call-seq:

update_format_with_params(row, col, format_params)

Update formatting of the cell to the specified row and column (zero indexed).

Raises:



1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
 
# File 'lib/write_xlsx/worksheet.rb', line 1627

def update_format_with_params(row, col, params = nil)
  if (row_col_array = row_col_notation(row))
    _row, _col = row_col_array
    _params = args[1]
  else
    _row = row
    _col = col
    _params = params
  end
  raise WriteXLSXInsufficientArgumentError if _row.nil? || _col.nil? || _params.nil?

  # Check that row and col are valid and store max and min values
  check_dimensions(_row, _col)
  store_row_col_max_min_values(_row, _col)

  format = nil
  cell_data = nil
  if @cell_data_table[_row].nil? || @cell_data_table[_row][_col].nil?
    format = @workbook.add_format(_params)
    write_blank(_row, _col, format)
  else
    if @cell_data_table[_row][_col].xf.nil?
      format = @workbook.add_format(_params)
      cell_data = @cell_data_table[_row][_col]
    else
      format = @workbook.add_format
      cell_data = @cell_data_table[_row][_col]
      format.copy(cell_data.xf)
      format.set_format_properties(_params)
    end
    # keep original value of cell
    value = if cell_data.is_a? FormulaCellData
              "=#{cell_data.token}"
            elsif cell_data.is_a? FormulaArrayCellData
              "{=#{cell_data.token}}"
            elsif cell_data.is_a? StringCellData
              @workbook.shared_strings.string(cell_data.data[:sst_id])
            else
              cell_data.data
            end
    write(_row, _col, value, format)
  end
end

#update_range_format_with_params(row_first, col_first, row_last = nil, col_last = nil, params = nil) ⇒ Object

:call-seq:

update_range_format_with_params(row_first, col_first, row_last, col_last, format_params)

Update formatting of cells in range to the specified row and column (zero indexed).

Raises:



1677
1678
1679
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
 
# File 'lib/write_xlsx/worksheet.rb', line 1677

def update_range_format_with_params(row_first, col_first, row_last = nil, col_last = nil, params = nil)
  if (row_col_array = row_col_notation(row_first))
    _row_first, _col_first, _row_last, _col_last = row_col_array
    params = args[1..-1]
  else
    _row_first = row_first
    _col_first = col_first
    _row_last  = row_last
    _col_last  = col_last
    _params    = params
  end

  raise WriteXLSXInsufficientArgumentError if [_row_first, _col_first, _row_last, _col_last, _params].include?(nil)

  # Swap last row/col with first row/col as necessary
  _row_first, _row_last = _row_last, _row_first if _row_first > _row_last
  _col_first, _col_last = _col_last, _col_first if _col_first > _col_last

  # Check that column number is valid and store the max value
  check_dimensions(_row_last, _col_last)
  store_row_col_max_min_values(_row_last, _col_last)

  (_row_first.._row_last).each do |row|
    (_col_first.._col_last).each do |col|
      update_format_with_params(row, col, _params)
    end
  end
end

#vertical_dpi=(val) ⇒ Object 



2738
2739
2740
 
# File 'lib/write_xlsx/worksheet.rb', line 2738

def vertical_dpi=(val)
  @page_setup.vertical_dpi = val
end

#very_hidden ⇒ Object

Hide this worksheet. This can only be unhidden from VBA.



239
240
241
 
# File 'lib/write_xlsx/worksheet.rb', line 239

def very_hidden
  hide(:very_hidden)
end

#very_hidden? ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


247
248
249
 
# File 'lib/write_xlsx/worksheet.rb', line 247

def very_hidden? # :nodoc:
  @hidden == :very_hidden
end

#write(row, col, token = nil, format = nil, value1 = nil, value2 = nil) ⇒ Object

:call-seq:

write(row, column [ , token [ , format ] ])

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:



1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
 
# File 'lib/write_xlsx/worksheet.rb', line 1012

def write(row, col, token = nil, format = nil, value1 = nil, value2 = nil)
  # Check for a cell reference in A1 notation and substitute row and column
  if (row_col_array = row_col_notation(row))
    _row, _col = row_col_array
    _token     = col
    _format    = token
    _value1    = format
    _value2    = value1
  else
    _row = row
    _col = col
    _token = token
    _format = format
    _value1 = value1
    _value2 = value2
  end
  _token ||= ''
  _token = _token.to_s if token.instance_of?(Time) || token.instance_of?(Date)

  if _format.respond_to?(:force_text_format?) && _format.force_text_format?
    write_string(_row, _col, _token, _format) # Force text format
  # Match an array ref.
  elsif _token.respond_to?(:to_ary)
    write_row(_row, _col, _token, _format, _value1, _value2)
  elsif _token.respond_to?(:coerce)  # Numeric
    write_number(_row, _col, _token, _format)
  elsif _token.respond_to?(:=~)  # String
    # Match integer with leading zero(s)
    if @leading_zeros && _token =~ /^0\d*$/
      write_string(_row, _col, _token, _format)
    elsif _token =~ /\A([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?\Z/
      write_number(_row, _col, _token, _format)
    # Match formula
    elsif _token =~ /^=/
      write_formula(_row, _col, _token, _format, _value1)
    # Match array formula
    elsif _token =~ /^\{=.*\}$/
      write_formula(_row, _col, _token, _format, _value1)
    # Match blank
    elsif _token == ''
      #        row_col_args.delete_at(2)     # remove the empty string from the parameter list
      write_blank(_row, _col, _format)
    elsif @workbook.strings_to_urls
      # Match http, https or ftp URL
      if _token =~ %r{\A[fh]tt?ps?://}
        write_url(_row, _col, _token, _format, _value1, _value2)
      # Match mailto:
      elsif _token =~ /\Amailto:/
        write_url(_row, _col, _token, _format, _value1, _value2)
      # Match internal or external sheet link
      elsif _token =~ /\A(?:in|ex)ternal:/
        write_url(_row, _col, _token, _format, _value1, _value2)
      else
        write_string(_row, _col, _token, _format)
      end
    else
      write_string(_row, _col, _token, _format)
    end
  else
    write_string(_row, _col, _token, _format)
  end
end

#write_array_formula(row1, col1, row2 = nil, col2 = nil, formula = nil, format = nil, value = nil) ⇒ Object

write_array_formula(row1, col1, row2, col2, formula, format)

Write an array formula to the specified row and column (zero indexed).



1579
1580
1581
 
# File 'lib/write_xlsx/worksheet.rb', line 1579

def write_array_formula(row1, col1, row2 = nil, col2 = nil, formula = nil, format = nil, value = nil)
  write_array_formula_base('a', row1, col1, row2, col2, formula, format, value)
end

#write_array_formula_base(type, *args) ⇒ Object

Internal method shared by the write_array_formula() and write_dynamic_array_formula() methods.

Raises:



1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
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
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
 
# File 'lib/write_xlsx/worksheet.rb', line 1515

def write_array_formula_base(type, *args)
  # Check for a cell reference in A1 notation and substitute row and column
  # Convert single cell to range
  if args.first.to_s =~ /^([A-Za-z]+[0-9]+)$/
    range = "#{::Regexp.last_match(1)}:#{::Regexp.last_match(1)}"
    params = [range] + args[1..-1]
  else
    params = args
  end

  if (row_col_array = row_col_notation(params.first))
    row1, col1, row2, col2 = row_col_array
    formula, xf, value = params[1..-1]
  else
    row1, col1, row2, col2, formula, xf, value = params
  end
  raise WriteXLSXInsufficientArgumentError if [row1, col1, row2, col2, formula].include?(nil)

  # Swap last row/col with first row/col as necessary
  row1, row2 = row2, row1 if row1 > row2
  col1, col2 = col2, col1 if col1 > col2

  # Check that row and col are valid and store max and min values
  check_dimensions(row1, col1)
  check_dimensions(row2, col2)
  store_row_col_max_min_values(row1, col1)
  store_row_col_max_min_values(row2, col2)

  # Define array range
  range = if row1 == row2 && col1 == col2
            xl_rowcol_to_cell(row1, col1)
          else
            "#{xl_rowcol_to_cell(row1, col1)}:#{xl_rowcol_to_cell(row2, col2)}"
          end

  # Modify the formula string, as needed.
  formula = prepare_formula(formula, 1)

  store_data_to_table(
    if type == 'a'
      FormulaArrayCellData.new(formula, xf, range, value)
    elsif type == 'd'
      DynamicFormulaArrayCellData.new(formula, xf, range, value)
    else
      raise "invalid type in write_array_formula_base()."
    end,
    row1, col1
  )

  # Pad out the rest of the area with formatted zeroes.
  (row1..row2).each do |row|
    (col1..col2).each do |col|
      next if row == row1 && col == col1

      write_number(row, col, 0, xf)
    end
  end
end

#write_blank(row, col, format = nil) ⇒ Object

:call-seq:

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.

Raises:



1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
 
# File 'lib/write_xlsx/worksheet.rb', line 1269

def write_blank(row, col, format = nil)
  # Check for a cell reference in A1 notation and substitute row and column
  if (row_col_array = row_col_notation(row))
    _row, _col = row_col_array
    _format = col
  else
    _row = row
    _col = col
    _format = format
  end
  raise WriteXLSXInsufficientArgumentError if [_row, _col].include?(nil)

  # Don't write a blank cell unless it has a format
  return unless _format

  # Check that row and col are valid and store max and min values
  check_dimensions(_row, _col)
  store_row_col_max_min_values(_row, _col)

  store_data_to_table(BlankCellData.new(_format), _row, _col)
end

#write_boolean(row, col, val = nil, format = nil) ⇒ Object

write_boolean(row, col, val, format)

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

Raises:



1598
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
 
# File 'lib/write_xlsx/worksheet.rb', line 1598

def write_boolean(row, col, val = nil, format = nil)
  if (row_col_array = row_col_notation(row))
    _row, _col = row_col_array
    _val       = col
    _format    = val
  else
    _row = row
    _col = col
    _val = val
    _format = format
  end
  raise WriteXLSXInsufficientArgumentError if _row.nil? || _col.nil?

  _val = _val ? 1 : 0  # Boolean value.
  # xf : cell format.

  # Check that row and col are valid and store max and min values
  check_dimensions(_row, _col)
  store_row_col_max_min_values(_row, _col)

  store_data_to_table(BooleanCellData.new(_val, _format), _row, _col)
end

#write_cell_array_formula(formula, range) ⇒ Object

Write the cell array formula <f> element.



2624
2625
2626
2627
2628
2629
2630
2631
2632
 
# File 'lib/write_xlsx/worksheet.rb', line 2624

def write_cell_array_formula(formula, range) # :nodoc:
  @writer.data_element(
    'f', formula,
    [
      %w[t array],
      ['ref', range]
    ]
  )
end

#write_cell_formula(formula = '') ⇒ Object

Write the cell formula <f> element.



2617
2618
2619
 
# File 'lib/write_xlsx/worksheet.rb', line 2617

def write_cell_formula(formula = '') # :nodoc:
  @writer.data_element('f', formula)
end

#write_cell_value(value = '') ⇒ Object

Write the cell value <v> element.



2604
2605
2606
2607
2608
2609
2610
2611
2612
 
# File 'lib/write_xlsx/worksheet.rb', line 2604

def write_cell_value(value = '') # :nodoc:
  return write_cell_formula('=NA()') if value.is_a?(Float) && value.nan?

  value ||= ''

  int_value = value.to_i
  value = int_value if value == int_value
  @writer.data_element('v', value)
end

#write_col(row, col, tokens = nil, *options) ⇒ Object

:call-seq:

write_col(row, col, array [ , format ])

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



1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
 
# File 'lib/write_xlsx/worksheet.rb', line 1116

def write_col(row, col, tokens = nil, *options)
  if (row_col_array = row_col_notation(row))
    _row, _col = row_col_array
    _tokens    = col
    _options   = [tokens] + options if options
  else
    _row = row
    _col = col
    _tokens = tokens
    _options = options
  end

  _tokens.each do |_token|
    # write() will deal with any nested arrays
    write(_row, _col, _token, *_options)
    _row += 1
  end
end

#write_comment(row, col, string = nil, options = nil) ⇒ Object

:call-seq:

write_comment(row, column, string, options = {})

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

Raises:



1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
 
# File 'lib/write_xlsx/worksheet.rb', line 1141

def write_comment(row, col, string = nil, options = nil)
  # Check for a cell reference in A1 notation and substitute row and column
  if (row_col_array = row_col_notation(row))
    _row, _col = row_col_array
    _string    = col
    _options   = string
  else
    _row = row
    _col = col
    _string = string
    _options = options
  end
  raise WriteXLSXInsufficientArgumentError if [_row, _col, _string].include?(nil)

  # Check that row and col are valid and store max and min values
  check_dimensions(_row, _col)
  store_row_col_max_min_values(_row, _col)

  @has_vml = true

  # Process the properties of the cell comment.
  @comments.add(@workbook, self, _row, _col, _string, _options)
end

#write_date_time(row, col, str, format = nil) ⇒ Object

:call-seq:

write_date_time (row, col, date_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.

Raises:



1783
1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
 
# File 'lib/write_xlsx/worksheet.rb', line 1783

def write_date_time(row, col, str, format = nil)
  # Check for a cell reference in A1 notation and substitute row and column
  if (row_col_array = row_col_notation(row))
    _row, _col = row_col_array
    _str       = col
    _format    = str
  else
    _row = row
    _col = col
    _str = str
    _format = format
  end
  raise WriteXLSXInsufficientArgumentError if [_row, _col, _str].include?(nil)

  # Check that row and col are valid and store max and min values
  check_dimensions(_row, _col)
  store_row_col_max_min_values(_row, _col)

  date_time = convert_date_time(_str)

  if date_time
    store_data_to_table(DateTimeCellData.new(date_time, _format), _row, _col)
  else
    # If the date isn't valid then write it as a string.
    write_string(_row, _col, _str, _format)
  end
end

#write_dynamic_array_formula(row1, col1, row2 = nil, col2 = nil, formula = nil, format = nil, value = nil) ⇒ Object

write_dynamic_array_formula(row1, col1, row2, col2, formula, format)

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



1588
1589
1590
1591
 
# File 'lib/write_xlsx/worksheet.rb', line 1588

def write_dynamic_array_formula(row1, col1, row2 = nil, col2 = nil, formula = nil, format = nil, value = nil)
  write_array_formula_base('d', row1, col1, row2, col2, formula, format, value)
  @has_dynamic_functions = true
end

#write_ext(url, &block) ⇒ Object 



2771
2772
2773
2774
2775
2776
2777
 
# File 'lib/write_xlsx/worksheet.rb', line 2771

def write_ext(url, &block)
  attributes = [
    ['xmlns:x14', "#{OFFICE_URL}spreadsheetml/2009/9/main"],
    ['uri',       url]
  ]
  @writer.tag_elements('ext', attributes, &block)
end

#write_formula(row, col, formula = nil, format = nil, value = nil) ⇒ Object

:call-seq:

write_formula(row, column, formula [ , format [ , value ] ])

Write a formula or function to the cell specified by row and column:

Raises:



1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
 
# File 'lib/write_xlsx/worksheet.rb', line 1475

def write_formula(row, col, formula = nil, format = nil, value = nil)
  # Check for a cell reference in A1 notation and substitute row and column
  if (row_col_array = row_col_notation(row))
    _row, _col = row_col_array
    _formula   = col
    _format    = formula
    _value     = format
  else
    _row = row
    _col = col
    _formula = formula
    _format = format
    _value = value
  end
  raise WriteXLSXInsufficientArgumentError if [_row, _col, _formula].include?(nil)

  # Check for dynamic array functions.
  regex = /\bANCHORARRAY\(|\bBYCOL\(|\bBYROW\(|\bCHOOSECOLS\(|\bCHOOSEROWS\(|\bDROP\(|\bEXPAND\(|\bFILTER\(|\bHSTACK\(|\bLAMBDA\(|\bMAKEARRAY\(|\bMAP\(|\bRANDARRAY\(|\bREDUCE\(|\bSCAN\(|\bSEQUENCE\(|\bSINGLE\(|\bSORT\(|\bSORTBY\(|\bSWITCH\(|\bTAKE\(|\bTEXTSPLIT\(|\bTOCOL\(|\bTOROW\(|\bUNIQUE\(|\bVSTACK\(|\bWRAPCOLS\(|\bWRAPROWS\(|\bXLOOKUP\(/
  if _formula =~ regex
    return write_dynamic_array_formula(
      _row, _col, _row, _col, _formula, _format, _value
    )
  end

  # Hand off array formulas.
  if _formula =~ /^\{=.*\}$/
    write_array_formula(_row, _col, _row, _col, _formula, _format, _value)
  else
    check_dimensions(_row, _col)
    store_row_col_max_min_values(_row, _col)
    _formula = prepare_formula(_formula)

    store_data_to_table(FormulaCellData.new(_formula, _format, _value), _row, _col)
  end
end

#write_number(row, col, number, format = nil) ⇒ Object

:call-seq:

write_number(row, column, number [ , format ])

Write an integer or a float to the cell specified by row and column:

Raises:



1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
 
# File 'lib/write_xlsx/worksheet.rb', line 1171

def write_number(row, col, number, format = nil)
  # Check for a cell reference in A1 notation and substitute row and column
  if (row_col_array = row_col_notation(row))
    _row, _col = row_col_array
    _number = col
    _format = number
  else
    _row = row
    _col = col
    _number = number
    _format = format
  end
  raise WriteXLSXInsufficientArgumentError if _row.nil? || _col.nil? || _number.nil?

  # Check that row and col are valid and store max and min values
  check_dimensions(_row, _col)
  store_row_col_max_min_values(_row, _col)

  store_data_to_table(NumberCellData.new(_number, _format), _row, _col)
end

#write_rich_string(row, col, *rich_strings) ⇒ Object

:call-seq:

write_rich_string(row, column, (string | format, string)+,  [,cell_format])

The write_rich_string() method is used to write strings with multiple formats. The method receives string fragments prefixed by format objects. The final format object is used as the cell format.

Raises:



1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
 
# File 'lib/write_xlsx/worksheet.rb', line 1231

def write_rich_string(row, col, *rich_strings)
  # Check for a cell reference in A1 notation and substitute row and column
  if (row_col_array = row_col_notation(row))
    _row, _col    = row_col_array
    _rich_strings = [col] + rich_strings
  else
    _row = row
    _col = col
    _rich_strings = rich_strings
  end
  raise WriteXLSXInsufficientArgumentError if [_row, _col, _rich_strings[0]].include?(nil)

  _xf = cell_format_of_rich_string(_rich_strings)

  # Check that row and col are valid and store max and min values
  check_dimensions(_row, _col)
  store_row_col_max_min_values(_row, _col)

  _fragments, _raw_string = rich_strings_fragments(_rich_strings)
  # can't allow 2 formats in a row
  return -4 unless _fragments

  # Check that the string si < 32767 chars.
  return 3 if _raw_string.size > @xls_strmax

  index = shared_string_index(xml_str_of_rich_string(_fragments))

  store_data_to_table(RichStringCellData.new(index, _xf, _raw_string), _row, _col)
end

#write_row(row, col, tokens = nil, *options) ⇒ Object

:call-seq:

write_row(row, col, array [ , format ])

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



1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
 
# File 'lib/write_xlsx/worksheet.rb', line 1083

def write_row(row, col, tokens = nil, *options)
  # Check for a cell reference in A1 notation and substitute row and column
  if (row_col_array = row_col_notation(row))
    _row, _col = row_col_array
    _tokens    = col
    _options   = [tokens] + options
  else
    _row = row
    _col = col
    _tokens = tokens
    _options = options
  end
  raise "Not an array ref in call to write_row()$!" unless _tokens.respond_to?(:to_ary)

  _tokens.each do |_token|
    # Check for nested arrays
    if _token.respond_to?(:to_ary)
      write_col(_row, _col, _token, *_options)
    else
      write(_row, _col, _token, *_options)
    end
    _col += 1
  end
end

#write_sparkline_groups ⇒ Object 



2779
2780
2781
2782
2783
2784
2785
2786
2787
 
# File 'lib/write_xlsx/worksheet.rb', line 2779

def write_sparkline_groups
  # Write the x14:sparklineGroups element.
  @writer.tag_elements('x14:sparklineGroups', sparkline_groups_attributes) do
    # Write the sparkline elements.
    @sparklines.reverse.each do |sparkline|
      sparkline.write_sparkline_group(@writer)
    end
  end
end

#write_string(row, col, string = nil, format = nil) ⇒ Object

:call-seq:

write_string(row, column, string [, format ])

Write a string to the specified row and column (zero indexed). format is optional.

Raises:



1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
 
# File 'lib/write_xlsx/worksheet.rb', line 1199

def write_string(row, col, string = nil, format = nil)
  # Check for a cell reference in A1 notation and substitute row and column
  if (row_col_array = row_col_notation(row))
    _row, _col = row_col_array
    _string = col
    _format = string
  else
    _row = row
    _col = col
    _string = string
    _format = format
  end
  _string &&= _string.to_s
  raise WriteXLSXInsufficientArgumentError if _row.nil? || _col.nil? || _string.nil?

  # Check that row and col are valid and store max and min values
  check_dimensions(_row, _col)
  store_row_col_max_min_values(_row, _col)

  index = shared_string_index(_string.length > STR_MAX ? _string[0, STR_MAX] : _string)

  store_data_to_table(StringCellData.new(index, _format, _string), _row, _col)
end

#write_url(row, col, url = nil, format = nil, str = nil, tip = nil, ignore_write_string = false) ⇒ Object

:call-seq:

write_url(row, column, url [ , format, label, tip ])

Write a hyperlink to a URL in the cell specified by row and column. The hyperlink is comprised of two elements: the visible label and the invisible link. The visible label is the same as the link unless an alternative label is specified. The label parameter is optional. The label is written using the #write() method. Therefore it is possible to write strings, numbers or formulas as labels.

Raises:



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
 
# File 'lib/write_xlsx/worksheet.rb', line 1738

def write_url(row, col, url = nil, format = nil, str = nil, tip = nil, ignore_write_string = false)
  # Check for a cell reference in A1 notation and substitute row and column
  if (row_col_array = row_col_notation(row))
    _row, _col           = row_col_array
    _url                 = col
    _format              = url
    _str                 = format
    _tip                 = str
    _ignore_write_string = tip
  else
    _row                 = row
    _col                 = col
    _url                 = url
    _format              = format
    _str                 = str
    _tip                 = tip
    _ignore_write_string = ignore_write_string
  end

  _format, _str = _str, _format if _str.respond_to?(:xf_index) || (_format && !_format.respond_to?(:xf_index))
  raise WriteXLSXInsufficientArgumentError if [_row, _col, _url].include?(nil)

  # Check that row and col are valid and store max and min values
  check_dimensions(_row, _col)
  store_row_col_max_min_values(_row, _col)

  hyperlink = Hyperlink.factory(_url, _str, _tip, @max_url_length)
  store_hyperlink(_row, _col, hyperlink)

  raise "URL '#{url}' added but URL exceeds Excel's limit of 65,530 URLs per worksheet." if hyperlinks_count > 65_530

  # Add the default URL format.
  _format ||= @default_url_format

  # Write the hyperlink string.
  write_string(_row, _col, hyperlink.str, _format) unless _ignore_write_string
end

#zoom=(scale) ⇒ Object

Set the worksheet zoom factor in the range 10 <= scale <= 400:



908
909
910
911
912
913
914
915
916
 
# File 'lib/write_xlsx/worksheet.rb', line 908

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