Module: Writexlsx::Worksheet::Formatting

Includes:
Constants
Included in:
Writexlsx::Worksheet
Defined in:
lib/write_xlsx/worksheet/formatting.rb

Constant Summary

Constants included from Constants

Constants::COL_MAX, Constants::ROW_MAX, Constants::SHEETNAME_MAX, Constants::STR_MAX

Instance Method Summary collapse

Instance Method Details

#center_horizontally ⇒ Object

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



104
105
106
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 104

def center_horizontally
  @page_setup.center_horizontally
end

#center_vertically ⇒ Object

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



111
112
113
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 111

def center_vertically
  @page_setup.center_vertically
end

#hide_zero(flag = true) ⇒ Object

Hide cell zero values.



376
377
378
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 376

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

#margin_bottom=(margin) ⇒ Object

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



171
172
173
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 171

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=()



147
148
149
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 147

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=()



155
156
157
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 155

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=()



163
164
165
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 163

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.



118
119
120
121
122
123
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 118

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



129
130
131
132
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 129

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



138
139
140
141
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 138

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

#paper=(paper_size) ⇒ Object

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



383
384
385
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 383

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

#print_across(across = true) ⇒ Object

Set the order in which pages are printed.



395
396
397
398
399
400
401
402
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 395

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.



293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 293

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.



361
362
363
364
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 361

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

#print_repeat_cols ⇒ Object

:nodoc:



281
282
283
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 281

def print_repeat_cols  # :nodoc:
  @page_setup.repeat_cols
end

#print_repeat_rows ⇒ Object

:nodoc:



259
260
261
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 259

def print_repeat_rows   # :nodoc:
  @page_setup.repeat_rows
end

#print_scale=(scale = 100) ⇒ Object

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



338
339
340
341
342
343
344
345
346
347
348
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 338

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

#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.



269
270
271
272
273
274
275
276
277
278
279
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 269

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_rows(row_min, row_max = nil) ⇒ Object

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



245
246
247
248
249
250
251
252
253
254
255
256
257
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 245

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.



369
370
371
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 369

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

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

Set the page footer caption and optional margin.



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

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.
  @assets.reset_footer_images

  [
    [:image_left, 'LF'], [:image_center, 'CF'], [:image_right, 'RF']
  ].each do |p|
    next unless options[p.first]

    @assets.add_footer_image(
      ImageProperty.new(options[p.first], position: p.last)
    )
  end

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

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

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

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

Set the page header caption and optional margin.



27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 27

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.
  @assets.reset_header_images

  [
    [:image_left, 'LH'], [:image_center, 'CH'], [:image_right, 'RH']
  ].each do |p|
    next unless options[p.first]

    @assets.add_header_image(
      ImageProperty.new(options[p.first], position: p.last)
    )
  end

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

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

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

#set_margin_bottom(margin = 0.75) ⇒ Object

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



233
234
235
236
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 233

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.



206
207
208
209
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 206

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.



215
216
217
218
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 215

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.



224
225
226
227
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 224

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

#set_margins(margin) ⇒ Object

# deprecations for set_* wrapper methods

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



179
180
181
182
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 179

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.



188
189
190
191
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 188

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.



197
198
199
200
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 197

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

#set_paper(paper_size) ⇒ Object 



387
388
389
390
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 387

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

#set_print_scale(scale = 100) ⇒ Object

This method is deprecated. use print_scale=().



353
354
355
356
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 353

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

#set_start_page(page_start) ⇒ Object 



412
413
414
415
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 412

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

#set_zoom(scale) ⇒ Object

This method is deprecated. use zoom=().



329
330
331
332
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 329

def set_zoom(scale)
  put_deprecate_message("#{self}.set_zoom")
  self.zoom = scale
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.



408
409
410
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 408

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

#zoom=(scale) ⇒ Object

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



318
319
320
321
322
323
324
325
326
 
# File 'lib/write_xlsx/worksheet/formatting.rb', line 318

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