Module: Writexlsx::Worksheet::XmlWriter

Includes:
Utility::StringWidth
Included in:
Writexlsx::Worksheet
Defined in:
lib/write_xlsx/worksheet/xml_writer.rb

Constant Summary

Constants included from Utility::StringWidth

Utility::StringWidth::CHAR_WIDTHS, Utility::StringWidth::DEFAULT_COL_PIXELS, Utility::StringWidth::MAX_DIGIT_WIDTH, Utility::StringWidth::PADDING

Instance Method Summary collapse

Methods included from Utility::StringWidth

#xl_string_pixel_width

Instance Method Details

#assemble_xml_file ⇒ Object

:nodoc:



10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 10

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

#calculate_x_split_width(width) ⇒ Object

Convert column width from user units to pane split width.



199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 199

def calculate_x_split_width(width) # :nodoc:
  # Convert to pixels.
  pixels = if width < 1
             int((width * 12) + 0.5)
           else
             ((width * MAX_DIGIT_WIDTH) + 0.5).to_i + PADDING
           end

  # Convert to points.
  points = pixels * 3 / 4

  # Convert to twips (twentieths of a point).
  twips = points * 20

  # Add offset/padding.
  twips + 390
end

#col_info_attributes(args) ⇒ Object 



331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 331

def col_info_attributes(args)
  min       = args[0]           || 0 # First formatted column.
  max       = args[1]           || 0 # Last formatted column.
  width     = args[2].width          # Col width in user units.
  format    = args[2].format         # Format index.
  hidden    = args[2].hidden    || 0 # Hidden flag.
  level     = args[2].level     || 0 # Outline level.
  collapsed = args[2].collapsed || 0 # Outline Collapsed
  autofit   = args[2].autofit   || 0 # Best fit for autofit numbers.
  xf_index = format ? format.get_xf_index : 0

  custom_width = true
  custom_width = false if width.nil? && hidden == 0
  custom_width = false if !width.nil? && (width - 8.43).abs < 0.01

  width ||= hidden == 0 ? @default_col_width : 0

  # Convert column width from user units to character width.
  width = if width && width < 1
            (((width * (MAX_DIGIT_WIDTH + PADDING)) + 0.5).to_i / MAX_DIGIT_WIDTH.to_f * 256).to_i / 256.0
          else
            ((((width * MAX_DIGIT_WIDTH) + 0.5).to_i + PADDING).to_i / MAX_DIGIT_WIDTH.to_f * 256).to_i / 256.0
          end
  width = width.to_i if width - width.to_i == 0

  attributes = [
    ['min',   min + 1],
    ['max',   max + 1],
    ['width', width]
  ]

  attributes << ['style',        xf_index] if xf_index  != 0
  attributes << ['hidden',       1]        if hidden    != 0
  attributes << ['bestFit',      1]        if autofit   != 0
  attributes << ['customWidth',  1]        if custom_width
  attributes << ['outlineLevel', level]    if level     != 0
  attributes << ['collapsed',    1]        if collapsed != 0
  attributes
end

#increment_rel_id_and_write_r_id(tag) ⇒ Object 



905
906
907
908
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 905

def increment_rel_id_and_write_r_id(tag)
  @rel_count += 1
  write_r_id(tag, @rel_count)
end

#not_contain_formatting_or_data?(row_num) ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


409
410
411
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 409

def not_contain_formatting_or_data?(row_num) # :nodoc:
  !@set_rows[row_num] && !@cell_data_store[row_num] && !@comments.has_comment_in_row?(row_num)
end

#row_attributes(args) ⇒ Object 



437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 437

def row_attributes(args)
  r, spans, height, format, hidden, level, collapsed, _empty_row = args
  height    ||= @default_row_height
  hidden    ||= 0
  level     ||= 0
  xf_index = format ? format.get_xf_index : 0

  attributes = [['r',  r + 1]]
  attributes << ['spans',        spans]    if spans
  attributes << ['s',            xf_index] if ptrue?(xf_index)
  attributes << ['customFormat', 1]        if ptrue?(format)
  if height != @original_row_height || height != @default_row_height
    attributes << ['ht',           height]
  end
  attributes << ['hidden',       1]        if ptrue?(hidden)
  if height != @original_row_height || height != @default_row_height
    attributes << ['customHeight', 1]
  end
  attributes << ['outlineLevel', level]    if ptrue?(level)
  attributes << ['collapsed',    1]        if ptrue?(collapsed)

  attributes << ['x14ac:dyDescent', '0.25'] if @excel_version == 2010
  attributes
end

#sparkline_groups_attributes ⇒ Object

:nodoc:



1114
1115
1116
1117
1118
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 1114

def sparkline_groups_attributes  # :nodoc:
  [
    ['xmlns:xm', "#{OFFICE_URL}excel/2006/main"]
  ]
end

#write_auto_filter ⇒ Object

Write the <autoFilter> element.



542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 542

def write_auto_filter # :nodoc:
  return unless autofilter_ref?

  attributes = [
    ['ref', @autofilter_ref]
  ]

  if filter_on?
    # Autofilter defined active filters.
    @writer.tag_elements('autoFilter', attributes) do
      write_autofilters
    end
  else
    # Autofilter defined without active filters.
    @writer.empty_tag('autoFilter', attributes)
  end
end

#write_autofilters ⇒ Object

Function to iterate through the columns that form part of an autofilter range and write the appropriate filters.



564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 564

def write_autofilters # :nodoc:
  col1, col2 = @filter_range

  (col1..col2).each do |col|
    # Skip if column doesn't have an active filter.
    next unless @filter_cols[col]

    # Retrieve the filter tokens and write the autofilter records.
    tokens = @filter_cols[col]
    type   = @filter_type[col]

    # Filters are relative to first column in the autofilter.
    write_filter_column(col - col1, type, *tokens)
  end
end

#write_breaks(tag) ⇒ Object

:nodoc:



812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 812

def write_breaks(tag) # :nodoc:
  case tag
  when 'rowBreaks'
    page_breaks = sort_pagebreaks(*@page_setup.hbreaks)
    max = 16383
  when 'colBreaks'
    page_breaks = sort_pagebreaks(*@page_setup.vbreaks)
    max = 1048575
  else
    raise "Invalid parameter '#{tag}' in write_breaks."
  end
  count = page_breaks.size

  return if page_breaks.empty?

  attributes = [
    ['count', count],
    ['manualBreakCount', count]
  ]

  @writer.tag_elements(tag, attributes) do
    page_breaks.each { |num| write_brk(num, max) }
  end
end

#write_brk(id, max) ⇒ Object

Write the <brk> element.



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

def write_brk(id, max) # :nodoc:
  attributes = [
    ['id',  id],
    ['max', max],
    ['man', 1]
  ]

  @writer.empty_tag('brk', attributes)
end

#write_cell_array_formula(formula, range) ⇒ Object

Write the cell array formula <f> element.



86
87
88
89
90
91
92
93
94
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 86

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

#write_cell_column_dimension(row_num) ⇒ Object

:nodoc:



420
421
422
423
424
425
426
427
428
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 420

def write_cell_column_dimension(row_num)  # :nodoc:
  row = @cell_data_store[row_num]
  row_name = (row_num + 1).to_s
  (@dim_colmin..@dim_colmax).each do |col_num|
    if (cell = row[col_num])
      cell.write_cell(self, row_num, row_name, col_num)
    end
  end
end

#write_cell_formula(formula = '') ⇒ Object

Write the cell formula <f> element.



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

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

#write_cell_value(value = '') ⇒ Object

Write the cell value <v> element.



66
67
68
69
70
71
72
73
74
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 66

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_breaks ⇒ Object

Write the <colBreaks> element.



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

def write_col_breaks # :nodoc:
  write_breaks('colBreaks')
end

#write_col_info(args) ⇒ Object

Write the <col> element.



327
328
329
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 327

def write_col_info(args) # :nodoc:
  @writer.empty_tag('col', col_info_attributes(args))
end

#write_cols ⇒ Object

Write the <cols> element and <col> sub elements.



283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 283

def write_cols # :nodoc:
  # Exit unless some column have been formatted.
  return if @col_info.empty?

  @writer.tag_elements('cols') do
    # Use the first element of the column informatin structure to set
    # the initial/previous properties.
    first_col           = @col_info.keys.min
    last_col            = first_col
    previous_options    = @col_info[first_col]
    deleted_col         = first_col
    deleted_col_options = previous_options

    @col_info.delete(first_col)

    @col_info.keys.sort.each do |col|
      col_options = @col_info[col]

      # Check if the column number is contiguous with the previous
      # column and if the properties are the same.
      if (col == last_col + 1) &&
         compare_col_info(col_options, previous_options)
        last_col = col
      else
        # If not contiguous/equal then we write out the current range
        # of columns and start again.
        write_col_info([first_col, last_col, previous_options])
        first_col = col
        last_col  = first_col
        previous_options = col_options
      end
    end

    # We will exit the previous loop with one unhandled column range.
    write_col_info([first_col, last_col, previous_options])

    # Put back the deleted first column information structure:
    @col_info[deleted_col] = deleted_col_options
  end
end

#write_conditional_formats ⇒ Object

Write the Worksheet conditional formats.



707
708
709
710
711
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 707

def write_conditional_formats  # :nodoc:
  @cond_formats.keys.sort.each do |range|
    write_conditional_formatting(range, @cond_formats[range])
  end
end

#write_conditional_formatting_2010(data_bar) ⇒ Object

Write the <x14:conditionalFormatting> element.



953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 953

def write_conditional_formatting_2010(data_bar)
  xmlns_xm = 'http://schemas.microsoft.com/office/excel/2006/main'

  attributes = [['xmlns:xm', xmlns_xm]]

  @writer.tag_elements('x14:conditionalFormatting', attributes) do
    # Write the '<x14:cfRule element.
    write_x14_cf_rule(data_bar)

    # Write the x14:dataBar element.
    write_x14_data_bar(data_bar)

    # Write the x14 max and min data bars.
    write_x14_cfvo(data_bar[:x14_min_type], data_bar[:min_value])
    write_x14_cfvo(data_bar[:x14_max_type], data_bar[:max_value])

    # Write the x14:borderColor element.
    write_x14_border_color(data_bar[:bar_border_color]) unless ptrue?(data_bar[:bar_no_border])

    # Write the x14:negativeFillColor element.
    write_x14_negative_fill_color(data_bar[:bar_negative_color]) unless ptrue?(data_bar[:bar_negative_color_same])

    # Write the x14:negativeBorderColor element.
    if !ptrue?(data_bar[:bar_no_border]) &&
       !ptrue?(data_bar[:bar_negative_border_color_same])
      write_x14_negative_border_color(
        data_bar[:bar_negative_border_color]
      )
    end

    # Write the x14:axisColor element.
    write_x14_axis_color(data_bar[:bar_axis_color]) if data_bar[:bar_axis_position] != 'none'

    # Write closing elements.
    @writer.end_tag('x14:dataBar')
    @writer.end_tag('x14:cfRule')

    # Add the conditional format range.
    @writer.data_element('xm:sqref', data_bar[:range])
  end
end

#write_custom_filter(operator, val) ⇒ Object

Write the <customFilter> element.



650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 650

def write_custom_filter(operator, val) # :nodoc:
  operators = {
    1  => 'lessThan',
    2  => 'equal',
    3  => 'lessThanOrEqual',
    4  => 'greaterThan',
    5  => 'notEqual',
    6  => 'greaterThanOrEqual',
    22 => 'equal'
  }

  # Convert the operator from a number to a descriptive string.
  if operators[operator]
    operator = operators[operator]
  else
    raise "Unknown operator = #{operator}\n"
  end

  # The 'equal' operator is the default attribute and isn't stored.
  attributes = []
  attributes << ['operator', operator] unless operator == 'equal'
  attributes << ['val', val]

  @writer.empty_tag('customFilter', attributes)
end

#write_custom_filters(*tokens) ⇒ Object

Write the <customFilters> element.



625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 625

def write_custom_filters(*tokens) # :nodoc:
  if tokens.size == 2
    # One filter expression only.
    @writer.tag_elements('customFilters') { write_custom_filter(*tokens) }
  else
    # Two filter expressions.

    # Check if the "join" operand is "and" or "or".
    attributes = if tokens[2] == 0
                   [['and', 1]]
                 else
                   [['and', 0]]
                 end

    # Write the two custom filters.
    @writer.tag_elements('customFilters', attributes) do
      write_custom_filter(tokens[0], tokens[1])
      write_custom_filter(tokens[3], tokens[4])
    end
  end
end

#write_data_validations ⇒ Object

Write the <dataValidations> element.



719
720
721
722
723
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 719

def write_data_validations # :nodoc:
  write_some_elements('dataValidations', @validations) do
    @validations.each { |validation| validation.write_data_validation(@writer) }
  end
end

#write_dimension ⇒ Object

Write the <dimension> element. This specifies the range of cells in the worksheet. As a special case, empty spreadsheets use ‘A1’ as a range.



1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 1180

def write_dimension # :nodoc:
  if !@dim_rowmin && !@dim_colmin
    # If the min dims are undefined then no dimensions have been set
    # and we use the default 'A1'.
    ref = 'A1'
  elsif !@dim_rowmin && @dim_colmin
    # If the row dims aren't set but the column dims are then they
    # have been changed via set_column().
    if @dim_colmin == @dim_colmax
      # The dimensions are a single cell and not a range.
      ref = xl_rowcol_to_cell(0, @dim_colmin)
    else
      # The dimensions are a cell range.
      cell_1 = xl_rowcol_to_cell(0, @dim_colmin)
      cell_2 = xl_rowcol_to_cell(0, @dim_colmax)
      ref = cell_1 + ':' + cell_2
    end
  elsif @dim_rowmin == @dim_rowmax && @dim_colmin == @dim_colmax
    # The dimensions are a single cell and not a range.
    ref = xl_rowcol_to_cell(@dim_rowmin, @dim_colmin)
  else
    # The dimensions are a cell range.
    cell_1 = xl_rowcol_to_cell(@dim_rowmin, @dim_colmin)
    cell_2 = xl_rowcol_to_cell(@dim_rowmax, @dim_colmax)
    ref = cell_1 + ':' + cell_2
  end
  @writer.empty_tag('dimension', [['ref', ref]])
end

#write_empty_row(*args) ⇒ Object

Write and empty <row> element, i.e., attributes only, no cell data.



433
434
435
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 433

def write_empty_row(*args) # :nodoc:
  @writer.empty_tag('row', row_attributes(args))
end

#write_ext(url, &block) ⇒ Object 



942
943
944
945
946
947
948
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 942

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

#write_ext_list ⇒ Object

Write the <extLst> element for data bars and sparklines.



917
918
919
920
921
922
923
924
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 917

def write_ext_list  # :nodoc:
  return if @data_bars_2010.empty? && @assets.sparklines.empty?

  @writer.tag_elements('extLst') do
    write_ext_list_data_bars  unless @data_bars_2010.empty?
    write_ext_list_sparklines unless @assets.sparklines.empty?
  end
end

#write_ext_list_data_bars ⇒ Object

Write the Excel 2010 data_bar subelements.



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

def write_ext_list_data_bars
  # Write the ext element.
  write_ext('{78C0D931-6437-407d-A8EE-F0AAD7539E65}') do
    @writer.tag_elements('x14:conditionalFormattings') do
      # Write each of the Excel 2010 conditional formatting data bar elements.
      @data_bars_2010.each do |data_bar|
        # Write the x14:conditionalFormatting element.
        write_conditional_formatting_2010(data_bar)
      end
    end
  end
end

#write_ext_list_sparklines ⇒ Object

Write the sparkline subelements.



1096
1097
1098
1099
1100
1101
1102
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 1096

def write_ext_list_sparklines
  # Write the ext element.
  write_ext('{05C60535-1F16-4fd2-B633-F4F36F0B64E0}') do
    # Write the x14:sparklineGroups element.
    write_sparkline_groups
  end
end

#write_filter(val) ⇒ Object

Write the <filter> element.



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

def write_filter(val) # :nodoc:
  @writer.empty_tag('filter', [['val', val]])
end

#write_filter_column(col_id, type, *filters) ⇒ Object

Write the <filterColumn> element.



583
584
585
586
587
588
589
590
591
592
593
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 583

def write_filter_column(col_id, type, *filters) # :nodoc:
  @writer.tag_elements('filterColumn', [['colId', col_id]]) do
    if type == 1
      # Type == 1 is the new XLSX style filter.
      write_filters(*filters)
    else
      # Type == 0 is the classic "custom" filter.
      write_custom_filters(*filters)
    end
  end
end

#write_filters(*filters) ⇒ Object

Write the <filters> element.



598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 598

def write_filters(*filters) # :nodoc:
  non_blanks = filters.reject { |filter| filter.to_s =~ /^blanks$/i }
  attributes = []

  attributes = [['blank', 1]] if filters != non_blanks

  if filters.size == 1 && non_blanks.empty?
    # Special case for blank cells only.
    @writer.empty_tag('filters', attributes)
  else
    # General case.
    @writer.tag_elements('filters', attributes) do
      non_blanks.sort.each { |filter| write_filter(filter) }
    end
  end
end

#write_freeze_panes(row, col, top_row, left_col, type) ⇒ Object

Write the <pane> element for freeze panes.



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

def write_freeze_panes(row, col, top_row, left_col, type) # :nodoc:
  y_split       = row
  x_split       = col
  top_left_cell = xl_rowcol_to_cell(top_row, left_col)

  # Move user cell selection to the panes.
  unless @selections.empty?
    _dummy, active_cell, sqref = @selections[0]
    @selections = []
  end

  active_cell ||= nil
  sqref       ||= nil
  active_pane = set_active_pane_and_cell_selections(row, col, row, col, active_cell, sqref)

  # Set the pane type.
  state = if type == 0
            'frozen'
          elsif type == 1
            'frozenSplit'
          else
            'split'
          end

  attributes = []
  attributes << ['xSplit',      x_split] if x_split > 0
  attributes << ['ySplit',      y_split] if y_split > 0
  attributes << ['topLeftCell', top_left_cell]
  attributes << ['activePane',  active_pane]
  attributes << ['state',       state]

  @writer.empty_tag('pane', attributes)
end

#write_header_footer ⇒ Object

Write the <headerFooter> element.



794
795
796
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 794

def write_header_footer # :nodoc:
  @page_setup.write_header_footer(@writer, excel2003_style?)
end

#write_hyperlinks ⇒ Object

Process any sored hyperlinks in row/col order and write the <hyperlinks> element. The attributes are different for internal and external links.



729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 729

def write_hyperlinks # :nodoc:
  return unless @hyperlinks

  hlink_attributes = []
  @hyperlinks.keys.sort.each do |row_num|
    # Sort the hyperlinks into column order.
    col_nums = @hyperlinks[row_num].keys.sort
    # Iterate over the columns.
    col_nums.each do |col_num|
      # Get the link data for this cell.
      link = @hyperlinks[row_num][col_num]

      # If the cell isn't a string then we have to add the url as
      # the string to display
      if ptrue?(@cell_data_store)                   &&
         ptrue?(@cell_data_store[row_num])          &&
         ptrue?(@cell_data_store[row_num][col_num]) &&
         @cell_data_store[row_num][col_num].display_url_string?
        link.display_on
      end

      if link.respond_to?(:external_hyper_link)
        # External link with rel file relationship.
        @rel_count += 1
        # Links for use by the packager.
        @external_hyper_links << link.external_hyper_link
      end
      hlink_attributes << link.attributes(row_num, col_num, @rel_count)
    end
  end

  return if hlink_attributes.empty?

  # Write the hyperlink elements.
  @writer.tag_elements('hyperlinks') do
    hlink_attributes.each do |attributes|
      @writer.empty_tag('hyperlink', attributes)
    end
  end
end

#write_ignored_error(type, sqref) ⇒ Object

Write the <ignoredError> element.



878
879
880
881
882
883
884
885
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 878

def write_ignored_error(type, sqref)
  attributes = [
    ['sqref', sqref],
    [type, 1]
  ]

  @writer.empty_tag('ignoredError', attributes)
end

#write_ignored_errors ⇒ Object

Write the <ignoredErrors> element.



853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 853

def write_ignored_errors
  return unless @ignore_errors

  ignore = @ignore_errors

  @writer.tag_elements('ignoredErrors') do
    {
      number_stored_as_text: 'numberStoredAsText',
      eval_error:            'evalError',
      formula_differs:       'formula',
      formula_range:         'formulaRange',
      formula_unlocked:      'unlockedFormula',
      empty_cell_reference:  'emptyCellReference',
      list_data_validation:  'listDataValidation',
      calculated_column:     'calculatedColumn',
      two_digit_text_year:   'twoDigitTextYear'
    }.each do |key, value|
      write_ignored_error(value, ignore[key]) if ignore[key]
    end
  end
end

#write_merge_cell(merged_range) ⇒ Object

Write the <mergeCell> element.



694
695
696
697
698
699
700
701
702
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 694

def write_merge_cell(merged_range) # :nodoc:
  row_min, col_min, row_max, col_max = merged_range

  # Convert the merge dimensions to a cell range.
  cell_1 = xl_rowcol_to_cell(row_min, col_min)
  cell_2 = xl_rowcol_to_cell(row_max, col_max)

  @writer.empty_tag('mergeCell', [['ref', "#{cell_1}:#{cell_2}"]])
end

#write_merge_cells ⇒ Object

Write the <mergeCells> element.



679
680
681
682
683
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 679

def write_merge_cells # :nodoc:
  write_some_elements('mergeCells', @merge) do
    @merge.each { |merged_range| write_merge_cell(merged_range) }
  end
end

#write_outline_pr ⇒ Object

Write the <outlinePr> element.



1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 1158

def write_outline_pr
  return unless outline_changed?

  attributes = []
  attributes << ["applyStyles",  1] if @outline_style
  attributes << ["summaryBelow", 0] if @outline_below == 0
  attributes << ["summaryRight", 0] if @outline_right == 0
  attributes << ["showOutlineSymbols", 0] if @outline_on == 0

  @writer.empty_tag('outlinePr', attributes)
end

#write_page_margins ⇒ Object

Write the <pageMargins> element.



780
781
782
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 780

def write_page_margins # :nodoc:
  @page_setup.write_page_margins(@writer)
end

#write_page_set_up_pr ⇒ Object

Write the <pageSetUpPr> element.



1173
1174
1175
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 1173

def write_page_set_up_pr # :nodoc:
  @writer.empty_tag('pageSetUpPr', [['fitToPage', 1]]) if fit_page?
end

#write_page_setup ⇒ Object

Write the <pageSetup> element.



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

def write_page_setup # :nodoc:
  @page_setup.write_page_setup(@writer)
end

#write_panes ⇒ Object

Write the frozen or split <pane> elements.



99
100
101
102
103
104
105
106
107
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 99

def write_panes # :nodoc:
  return if @panes.empty?

  if @panes[4] == 2
    write_split_panes
  else
    write_freeze_panes(*@panes)
  end
end

#write_phonetic_pr ⇒ Object

Write the <phoneticPr> element.



530
531
532
533
534
535
536
537
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 530

def write_phonetic_pr # :nodoc:
  attributes = [
    ['fontId', 0],
    %w[type noConversion]
  ]

  @writer.empty_tag('phoneticPr', attributes)
end

#write_print_options ⇒ Object

Write the <printOptions> element.



773
774
775
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 773

def write_print_options # :nodoc:
  @page_setup.write_print_options(@writer)
end

#write_protected_range(sqref, name, password) ⇒ Object

Write the <protectedRange> element.



510
511
512
513
514
515
516
517
518
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 510

def write_protected_range(sqref, name, password)
  attributes = []

  attributes << ['password', password] if password
  attributes << ['sqref',    sqref]
  attributes << ['name',     name]

  @writer.empty_tag('protectedRange', attributes)
end

#write_protected_ranges ⇒ Object

Write the <protectedRanges> element.



497
498
499
500
501
502
503
504
505
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 497

def write_protected_ranges
  return if @num_protected_ranges == 0

  @writer.tag_elements('protectedRanges') do
    @protected_ranges.each do |protected_range|
      write_protected_range(*protected_range)
    end
  end
end

#write_r_id(tag, id) ⇒ Object 



910
911
912
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 910

def write_r_id(tag, id)
  @writer.empty_tag(tag, [r_id_attributes(id)])
end

#write_row_breaks ⇒ Object

Write the <rowBreaks> element.



801
802
803
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 801

def write_row_breaks # :nodoc:
  write_breaks('rowBreaks')
end

#write_row_element(*args, &block) ⇒ Object

Write the <row> element.



416
417
418
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 416

def write_row_element(*args, &block)  # :nodoc:
  @writer.tag_elements('row', row_attributes(args), &block)
end

#write_rows ⇒ Object

Write out the worksheet data as a series of rows and cells.



386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 386

def write_rows # :nodoc:
  calculate_spans

  (@dim_rowmin..@dim_rowmax).each do |row_num|
    # Skip row if it doesn't contain row formatting or cell data.
    next if not_contain_formatting_or_data?(row_num)

    span_index = row_num / 16
    span       = @row_spans[span_index]

    # Write the cells if the row contains data.
    if @cell_data_store[row_num]
      args = @set_rows[row_num] || []
      write_row_element(row_num, span, *args) do
        write_cell_column_dimension(row_num)
      end
    else
      # Row attributes only.
      write_empty_row(row_num, span, *@set_rows[row_num])
    end
  end
end

#write_selection(pane, active_cell, sqref) ⇒ Object

Write the <selection> element.



1219
1220
1221
1222
1223
1224
1225
1226
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 1219

def write_selection(pane, active_cell, sqref) # :nodoc:
  attributes  = []
  attributes << ['pane', pane]              if pane
  attributes << ['activeCell', active_cell] if active_cell
  attributes << ['sqref', sqref]            if sqref

  @writer.empty_tag('selection', attributes)
end

#write_selections ⇒ Object

Write the <selection> elements.



1212
1213
1214
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 1212

def write_selections # :nodoc:
  @selections.each { |selection| write_selection(*selection) }
end

#write_sheet_calc_pr ⇒ Object

Write the <sheetCalcPr> element for the worksheet calculation properties.



523
524
525
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 523

def write_sheet_calc_pr # :nodoc:
  @writer.empty_tag('sheetCalcPr', [['fullCalcOnLoad', 1]])
end

#write_sheet_data ⇒ Object

Write the <sheetData> element.



374
375
376
377
378
379
380
381
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 374

def write_sheet_data # :nodoc:
  if @dim_rowmin
    @writer.tag_elements('sheetData') { write_rows }
  else
    # If the dimensions aren't defined then there is no data to write.
    @writer.empty_tag('sheetData')
  end
end

#write_sheet_format_pr ⇒ Object

Write the <sheetFormatPr> element.



1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 1231

def write_sheet_format_pr # :nodoc:
  attributes = [
    ['defaultRowHeight', @default_row_height]
  ]
  attributes << ['customHeight', 1] if @default_row_height != @original_row_height

  attributes << ['zeroHeight', 1] if ptrue?(@default_row_zeroed)

  attributes << ['outlineLevelRow', @outline_row_level] if @outline_row_level > 0
  attributes << ['outlineLevelCol', @outline_col_level] if @outline_col_level > 0
  attributes << ['x14ac:dyDescent', '0.25'] if @excel_version == 2010
  @writer.empty_tag('sheetFormatPr', attributes)
end

#write_sheet_pr ⇒ Object

Write the <sheetPr> element for Sheet level properties.



1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 1123

def write_sheet_pr # :nodoc:
  return unless tab_outline_fit? || vba_codename? || filter_on?

  attributes = []
  attributes << ['codeName',   @vba_codename] if vba_codename?
  attributes << ['filterMode', 1]             if filter_on?

  if tab_outline_fit?
    @writer.tag_elements('sheetPr', attributes) do
      write_tab_color
      write_outline_pr
      write_page_set_up_pr
    end
  else
    @writer.empty_tag('sheetPr', attributes)
  end
end

#write_sheet_protection ⇒ Object

Write the <sheetProtection> element.



465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 465

def write_sheet_protection # :nodoc:
  return unless protect?

  attributes = []
  attributes << ["password",         @protect[:password]] if ptrue?(@protect[:password])
  attributes << ["sheet",            1] if ptrue?(@protect[:sheet])
  attributes << ["content",          1] if ptrue?(@protect[:content])
  attributes << ["objects",          1] unless ptrue?(@protect[:objects])
  attributes << ["scenarios",        1] unless ptrue?(@protect[:scenarios])
  attributes << ["formatCells",      0] if ptrue?(@protect[:format_cells])
  attributes << ["formatColumns",    0] if ptrue?(@protect[:format_columns])
  attributes << ["formatRows",       0] if ptrue?(@protect[:format_rows])
  attributes << ["insertColumns",    0] if ptrue?(@protect[:insert_columns])
  attributes << ["insertRows",       0] if ptrue?(@protect[:insert_rows])
  attributes << ["insertHyperlinks", 0] if ptrue?(@protect[:insert_hyperlinks])
  attributes << ["deleteColumns",    0] if ptrue?(@protect[:delete_columns])
  attributes << ["deleteRows",       0] if ptrue?(@protect[:delete_rows])

  attributes << ["selectLockedCells", 1] unless ptrue?(@protect[:select_locked_cells])

  attributes << ["sort",        0] if ptrue?(@protect[:sort])
  attributes << ["autoFilter",  0] if ptrue?(@protect[:autofilter])
  attributes << ["pivotTables", 0] if ptrue?(@protect[:pivot_tables])

  attributes << ["selectUnlockedCells", 1] unless ptrue?(@protect[:select_unlocked_cells])

  @writer.empty_tag('sheetProtection', attributes)
end

#write_sheet_view ⇒ Object

:nodoc:



224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 224

def write_sheet_view # :nodoc:
  attributes = []
  # Hide screen gridlines if required.
  attributes << ['showGridLines', 0] unless @screen_gridlines

  # Hide the row/column headers.
  attributes << ['showRowColHeaders', 0] if ptrue?(@hide_row_col_headers)

  # Hide zeroes in cells.
  attributes << ['showZeros', 0] unless show_zeros?

  # Display worksheet right to left for Hebrew, Arabic and others.
  attributes << ['rightToLeft', 1] if @right_to_left

  # Show that the sheet tab is selected.
  attributes << ['tabSelected', 1] if @selected

  # Turn outlines off. Also required in the outlinePr element.
  attributes << ["showOutlineSymbols", 0] if @outline_on

  # Set the page view/layout mode if required.
  case @page_view
  when 1
    attributes << %w[view pageLayout]
  when 2
    attributes << %w[view pageBreakPreview]
  end

  # Set the first visible cell.
  attributes << ['topLeftCell', @top_left_cell] if ptrue?(@top_left_cell)

  # Set the zoom level.
  if @zoom != 100
    attributes << ['zoomScale', @zoom]

    if @page_view == 1
      attributes << ['zoomScalePageLayoutView', @zoom]
    elsif @page_view == 2
      attributes << ['zoomScaleSheetLayoutView', @zoom]
    elsif ptrue?(@zoom_scale_normal)
      attributes << ['zoomScaleNormal', @zoom]
    end
  end

  attributes << ['workbookViewId', 0]

  if @panes.empty? && @selections.empty?
    @writer.empty_tag('sheetView', attributes)
  else
    @writer.tag_elements('sheetView', attributes) do
      write_panes
      write_selections
    end
  end
end

#write_sheet_views ⇒ Object

Write the <sheetViews> element.



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

def write_sheet_views # :nodoc:
  @writer.tag_elements('sheetViews', []) { write_sheet_view }
end

#write_some_elements(tag, container, &block) ⇒ Object 



685
686
687
688
689
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 685

def write_some_elements(tag, container, &block)
  return if container.empty?

  @writer.tag_elements(tag, [['count', container.size]], &block)
end

#write_sparkline_groups ⇒ Object 



1104
1105
1106
1107
1108
1109
1110
1111
1112
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 1104

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

#write_split_panes ⇒ Object

Write the <pane> element for split panes.

See also, implementers note for split_panes().



151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 151

def write_split_panes # :nodoc:
  row, col, top_row, left_col = @panes
  has_selection = false
  y_split = row
  x_split = col

  # Move user cell selection to the panes.
  unless @selections.empty?
    _dummy, active_cell, sqref = @selections[0]
    @selections = []
    has_selection = true
  end

  # Convert the row and col to 1/20 twip units with padding.
  y_split = ((20 * y_split) + 300).to_i if y_split > 0
  x_split = calculate_x_split_width(x_split) if x_split > 0

  # For non-explicit topLeft definitions, estimate the cell offset based
  # on the pixels dimensions. This is only a workaround and doesn't take
  # adjusted cell dimensions into account.
  if top_row == row && left_col == col
    top_row  = (0.5 + ((y_split - 300) / 20 / 15)).to_i
    left_col = (0.5 + ((x_split - 390) / 20 / 3 * 4 / 64)).to_i
  end

  top_left_cell = xl_rowcol_to_cell(top_row, left_col)

  # If there is no selection set the active cell to the top left cell.
  unless has_selection
    active_cell = top_left_cell
    sqref       = top_left_cell
  end
  active_pane = set_active_pane_and_cell_selections(
    row, col, top_row, left_col, active_cell, sqref
  )

  attributes = []
  attributes << ['xSplit', x_split] if x_split > 0
  attributes << ['ySplit', y_split] if y_split > 0
  attributes << ['topLeftCell', top_left_cell]
  attributes << ['activePane', active_pane] if has_selection

  @writer.empty_tag('pane', attributes)
end

#write_tab_color ⇒ Object

Write the <tabColor> element.



1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 1144

def write_tab_color # :nodoc:
  return unless tab_color?

  @writer.empty_tag(
    'tabColor',
    [
      ['rgb', palette_color(@tab_color)]
    ]
  )
end

#write_table_part(id) ⇒ Object

Write the <tablePart> element.



901
902
903
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 901

def write_table_part(id)
  @writer.empty_tag('tablePart', [r_id_attributes(id)])
end

#write_table_parts ⇒ Object

Write the <tableParts> element.



890
891
892
893
894
895
896
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 890

def write_table_parts
  return if @assets.tables.empty?

  @writer.tag_elements('tableParts', [['count', tables_count]]) do
    tables_count.times { increment_rel_id_and_write_r_id('tablePart') }
  end
end

#write_worksheet_attributes ⇒ Object

Write the <worksheet> element. This is the root element of Worksheet.



48
49
50
51
52
53
54
55
56
57
58
59
60
61
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 48

def write_worksheet_attributes # :nodoc:
  schema = 'http://schemas.openxmlformats.org/'
  attributes = [
    ['xmlns',    "#{schema}spreadsheetml/2006/main"],
    ['xmlns:r',  "#{schema}officeDocument/2006/relationships"]
  ]

  if @excel_version == 2010
    attributes << ['xmlns:mc',     "#{schema}markup-compatibility/2006"]
    attributes << ['xmlns:x14ac',  "#{OFFICE_URL}spreadsheetml/2009/9/ac"]
    attributes << ['mc:Ignorable', 'x14ac']
  end
  attributes
end

#write_x14_axis_color(rgb) ⇒ Object

Write the <x14:axisColor> element.



1087
1088
1089
1090
1091
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 1087

def write_x14_axis_color(rgb)
  attributes = [['rgb', rgb]]

  @writer.empty_tag('x14:axisColor', attributes)
end

#write_x14_border_color(rgb) ⇒ Object

Write the <x14:borderColor> element.



1060
1061
1062
1063
1064
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 1060

def write_x14_border_color(rgb)
  attributes = [['rgb', rgb]]

  @writer.empty_tag('x14:borderColor', attributes)
end

#write_x14_cf_rule(data_bar) ⇒ Object

Write the <‘<x14:cfRule> element.



1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 1013

def write_x14_cf_rule(data_bar)
  type = 'dataBar'
  id   = data_bar[:guid]

  attributes = [
    ['type', type],
    ['id',   id]
  ]

  @writer.start_tag('x14:cfRule', attributes)
end

#write_x14_cfvo(type, value) ⇒ Object

Write the <cfvo> element.



998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 998

def write_x14_cfvo(type, value)
  attributes = [['type', type]]

  if %w[min max autoMin autoMax].include?(type)
    @writer.empty_tag('x14:cfvo', attributes)
  else
    @writer.tag_elements('x14:cfvo', attributes) do
      @writer.data_element('xm:f', value)
    end
  end
end

#write_x14_data_bar(data_bar) ⇒ Object

Write the <x14:dataBar> element.



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

def write_x14_data_bar(data_bar)
  min_length = 0
  max_length = 100

  attributes = [
    ['minLength', min_length],
    ['maxLength', max_length]
  ]

  attributes << ['border',   1] unless ptrue?(data_bar[:bar_no_border])
  attributes << ['gradient', 0] if ptrue?(data_bar[:bar_solid])

  attributes << %w[direction leftToRight] if data_bar[:bar_direction] == 'left'
  attributes << %w[direction rightToLeft] if data_bar[:bar_direction] == 'right'

  attributes << ['negativeBarColorSameAsPositive', 1] if ptrue?(data_bar[:bar_negative_color_same])

  if !ptrue?(data_bar[:bar_no_border]) &&
     !ptrue?(data_bar[:bar_negative_border_color_same])
    attributes << ['negativeBarBorderColorSameAsPositive', 0]
  end

  attributes << %w[axisPosition middle] if data_bar[:bar_axis_position] == 'middle'

  attributes << %w[axisPosition none] if data_bar[:bar_axis_position] == 'none'

  @writer.start_tag('x14:dataBar', attributes)
end

#write_x14_negative_border_color(rgb) ⇒ Object

Write the <x14:negativeBorderColor> element.



1078
1079
1080
1081
1082
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 1078

def write_x14_negative_border_color(rgb)
  attributes = [['rgb', rgb]]

  @writer.empty_tag('x14:negativeBorderColor', attributes)
end

#write_x14_negative_fill_color(rgb) ⇒ Object

Write the <x14:negativeFillColor> element.



1069
1070
1071
1072
1073
 
# File 'lib/write_xlsx/worksheet/xml_writer.rb', line 1069

def write_x14_negative_fill_color(rgb)
  attributes = [['rgb', rgb]]

  @writer.empty_tag('x14:negativeFillColor', attributes)
end