Module: RubyXL::LegacyWorksheet

Includes:
Enumerable
Included in:
Worksheet
Defined in:
lib/rubyXL/worksheet.rb

Instance Method Summary collapse

Instance Method Details

#[](row = 0) ⇒ Object

allows for easier access to sheet_data



18
19
20
 
# File 'lib/rubyXL/worksheet.rb', line 18

def [](row = 0)
  sheet_data[row]
end

#add_cell(row = 0, column = 0, data = '', formula = nil, overwrite = true) ⇒ Object 



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

def add_cell(row = 0, column = 0, data = '', formula = nil, overwrite = true)
  validate_workbook
  ensure_cell_exists(row, column)

  if overwrite || sheet_data.rows[row].cells[column].nil?
    c = RubyXL::Cell.new
    c.worksheet = self
    c.row = row
    c.column = column
    c.raw_value = data
    c.datatype = RubyXL::DataType::RAW_STRING unless formula || data.is_a?(Numeric)
    c.formula = RubyXL::Formula.new(:expression => formula) if formula

    range = cols && cols.find(column)
    c.style_index = sheet_data.rows[row].style_index || (range && range.style_index) || 0

    sheet_data.rows[row].cells[column] = c
  end

  sheet_data.rows[row].cells[column]
end

#add_row(row = 0, params = {}) ⇒ Object 



354
355
356
357
358
 
# File 'lib/rubyXL/worksheet.rb', line 354

def add_row(row = 0, params = {})
  new_row = RubyXL::Row.new(params)
  new_row.worksheet = self
  sheet_data.rows[row] = new_row
end

#change_column_bold(column_index, bolded = false) ⇒ Object 



252
253
254
255
256
257
 
# File 'lib/rubyXL/worksheet.rb', line 252

def change_column_bold(column_index, bolded = false)
  xf = get_col_xf(column_index)
  font = @workbook.fonts[xf.font_id].dup
  font.set_bold(bolded)
  change_column_font(column_index, Worksheet::BOLD, bolded, font, xf)
end

#change_column_border(column_index, direction, weight) ⇒ Object 



334
335
336
337
338
339
340
341
342
343
344
 
# File 'lib/rubyXL/worksheet.rb', line 334

def change_column_border(column_index, direction, weight)
  validate_workbook
  ensure_cell_exists(0, column_index)

  cols.get_range(column_index).style_index = @workbook.modify_border(get_col_style(column_index), direction, weight)

  sheet_data.rows.each { |row|
    c = row.cells[column_index]
    c.change_border(direction, weight) unless c.nil?
  }
end

#change_column_border_bottom(column_index, weight = 'thin') ⇒ Object 



324
325
326
327
 
# File 'lib/rubyXL/worksheet.rb', line 324

def change_column_border_bottom(column_index, weight = 'thin')
  warn "[DEPRECATION] `#{__method__}` is deprecated.  Please use `change_column_border` instead."
  change_column_border(column_index, :bottom, weight)
end

#change_column_border_diagonal(column_index, weight = 'thin') ⇒ Object 



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

def change_column_border_diagonal(column_index, weight = 'thin')
  warn "[DEPRECATION] `#{__method__}` is deprecated.  Please use `change_column_border` instead."
  change_column_border(column_index, :diagonal, weight)
end

#change_column_border_left(column_index, weight = 'thin') ⇒ Object 



314
315
316
317
 
# File 'lib/rubyXL/worksheet.rb', line 314

def change_column_border_left(column_index, weight = 'thin')
  warn "[DEPRECATION] `#{__method__}` is deprecated.  Please use `change_column_border` instead."
  change_column_border(column_index, :left, weight)
end

#change_column_border_right(column_index, weight = 'thin') ⇒ Object 



319
320
321
322
 
# File 'lib/rubyXL/worksheet.rb', line 319

def change_column_border_right(column_index, weight = 'thin')
  warn "[DEPRECATION] `#{__method__}` is deprecated.  Please use `change_column_border` instead."
  change_column_border(column_index, :right, weight)
end

#change_column_border_top(column_index, weight = 'thin') ⇒ Object 



309
310
311
312
 
# File 'lib/rubyXL/worksheet.rb', line 309

def change_column_border_top(column_index, weight = 'thin')
  warn "[DEPRECATION] `#{__method__}` is deprecated.  Please use `change_column_border` instead."
  change_column_border(column_index, :top, weight)
end

#change_column_fill(column_index, color_index = 'ffffff') ⇒ Object 



288
289
290
291
292
293
294
295
296
297
298
299
 
# File 'lib/rubyXL/worksheet.rb', line 288

def change_column_fill(column_index, color_index='ffffff')
  validate_workbook
  Color.validate_color(color_index)
  ensure_cell_exists(0, column_index)

  cols.get_range(column_index).style_index = @workbook.modify_fill(get_col_style(column_index), color_index)

  sheet_data.rows.each { |row|
    c = row[column_index]
    c.change_fill(color_index) if c
  }
end

#change_column_font_color(column_index, font_color = '000000') ⇒ Object

Changes font color of column



236
237
238
239
240
241
242
243
 
# File 'lib/rubyXL/worksheet.rb', line 236

def change_column_font_color(column_index, font_color='000000')
  Color.validate_color(font_color)

  xf = get_col_xf(column_index)
  font = @workbook.fonts[xf.font_id].dup
  font.set_rgb_color(font_color)
  change_column_font(column_index, Worksheet::COLOR, font_color, font, xf)
end

#change_column_font_name(column_index = 0, font_name = 'Verdana') ⇒ Object

Changes font name of column



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

def change_column_font_name(column_index = 0, font_name = 'Verdana')
  xf = get_col_xf(column_index)
  font = @workbook.fonts[xf.font_id].dup
  font.set_name(font_name)
  change_column_font(column_index, Worksheet::NAME, font_name, font, xf)
end

#change_column_font_size(column_index, font_size = 10) ⇒ Object

Changes font size of column



228
229
230
231
232
233
 
# File 'lib/rubyXL/worksheet.rb', line 228

def change_column_font_size(column_index, font_size=10)
  xf = get_col_xf(column_index)
  font = @workbook.fonts[xf.font_id].dup
  font.set_size(font_size)
  change_column_font(column_index, Worksheet::SIZE, font_size, font, xf)
end

#change_column_horizontal_alignment(column_index, alignment = 'center') ⇒ Object 



301
302
303
 
# File 'lib/rubyXL/worksheet.rb', line 301

def change_column_horizontal_alignment(column_index, alignment = 'center')
  change_column_alignment(column_index, alignment,true)
end

#change_column_italics(column_index, italicized = false) ⇒ Object 



245
246
247
248
249
250
 
# File 'lib/rubyXL/worksheet.rb', line 245

def change_column_italics(column_index, italicized = false)
  xf = get_col_xf(column_index)
  font = @workbook.fonts[xf.font_id].dup
  font.set_italic(italicized)
  change_column_font(column_index, Worksheet::ITALICS, italicized, font, xf)
end

#change_column_strikethrough(column_index, struckthrough = false) ⇒ Object 



266
267
268
269
270
271
 
# File 'lib/rubyXL/worksheet.rb', line 266

def change_column_strikethrough(column_index, struckthrough=false)
  xf = get_col_xf(column_index)
  font = @workbook.fonts[xf.font_id].dup
  font.set_strikethrough(struckthrough)
  change_column_font(column_index, Worksheet::STRIKETHROUGH, struckthrough, font, xf)
end

#change_column_underline(column_index, underlined = false) ⇒ Object 



259
260
261
262
263
264
 
# File 'lib/rubyXL/worksheet.rb', line 259

def change_column_underline(column_index, underlined = false)
  xf = get_col_xf(column_index)
  font = @workbook.fonts[xf.font_id].dup
  font.set_underline(underlined)
  change_column_font(column_index, Worksheet::UNDERLINE, underlined, font, xf)
end

#change_column_vertical_alignment(column_index, alignment = 'center') ⇒ Object 



305
306
307
 
# File 'lib/rubyXL/worksheet.rb', line 305

def change_column_vertical_alignment(column_index, alignment = 'center')
  change_column_alignment(column_index, alignment, false)
end

#change_column_width(column_index, width_in_chars = RubyXL::ColumnRange::DEFAULT_WIDTH) ⇒ Object

Get column width measured in number of digits, as per msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.column%28v=office.14%29.aspx



284
285
286
 
# File 'lib/rubyXL/worksheet.rb', line 284

def change_column_width(column_index, width_in_chars = RubyXL::ColumnRange::DEFAULT_WIDTH)
  change_column_width_raw(column_index, ((width_in_chars + (5.0 / RubyXL::Font::MAX_DIGIT_WIDTH)) * 256).to_i / 256.0)
end

#change_column_width_raw(column_index, width) ⇒ Object

Set raw column width value



274
275
276
277
278
279
280
 
# File 'lib/rubyXL/worksheet.rb', line 274

def change_column_width_raw(column_index, width)
  validate_workbook
  ensure_cell_exists(0, column_index)
  range = cols.get_range(column_index)
  range.width = width
  range.custom_width = true
end

#change_row_bold(row = 0, bolded = false) ⇒ Object 



141
142
143
144
145
146
 
# File 'lib/rubyXL/worksheet.rb', line 141

def change_row_bold(row = 0, bolded = false)
  ensure_cell_exists(row)
  font = row_font(row).dup
  font.set_bold(bolded)
  change_row_font(row, Worksheet::BOLD, bolded, font)
end

#change_row_border(row, direction, weight) ⇒ Object 



208
209
210
211
212
213
214
215
216
217
 
# File 'lib/rubyXL/worksheet.rb', line 208

def change_row_border(row, direction, weight)
  validate_workbook
  ensure_cell_exists(row)

  sheet_data.rows[row].style_index = @workbook.modify_border(get_row_style(row), direction, weight)

  sheet_data[row].cells.each { |c|
    c.change_border(direction, weight) unless c.nil?
  }
end

#change_row_border_bottom(row = 0, weight = 'thin') ⇒ Object 



198
199
200
201
 
# File 'lib/rubyXL/worksheet.rb', line 198

def change_row_border_bottom(row = 0, weight = 'thin')
  warn "[DEPRECATION] `#{__method__}` is deprecated.  Please use `change_row_border` instead."
  change_row_border(row, :bottom, weight)
end

#change_row_border_diagonal(row = 0, weight = 'thin') ⇒ Object 



203
204
205
206
 
# File 'lib/rubyXL/worksheet.rb', line 203

def change_row_border_diagonal(row = 0, weight = 'thin')
  warn "[DEPRECATION] `#{__method__}` is deprecated.  Please use `change_row_border` instead."
  change_row_border(row, :diagonal, weight)
end

#change_row_border_left(row = 0, weight = 'thin') ⇒ Object 



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

def change_row_border_left(row = 0, weight = 'thin')
  warn "[DEPRECATION] `#{__method__}` is deprecated.  Please use `change_row_border` instead."
  change_row_border(row, :left, weight)
end

#change_row_border_right(row = 0, weight = 'thin') ⇒ Object 



193
194
195
196
 
# File 'lib/rubyXL/worksheet.rb', line 193

def change_row_border_right(row = 0, weight = 'thin')
  warn "[DEPRECATION] `#{__method__}` is deprecated.  Please use `change_row_border` instead."
  change_row_border(row, :right, weight)
end

#change_row_border_top(row = 0, weight = 'thin') ⇒ Object 



183
184
185
186
 
# File 'lib/rubyXL/worksheet.rb', line 183

def change_row_border_top(row = 0, weight = 'thin')
  warn "[DEPRECATION] `#{__method__}` is deprecated.  Please use `change_row_border` instead."
  change_row_border(row, :top, weight)
end

#change_row_fill(row_index = 0, rgb = 'ffffff') ⇒ Object

changes color of fill in (zer0 indexed) row



103
104
105
106
107
108
109
110
 
# File 'lib/rubyXL/worksheet.rb', line 103

def change_row_fill(row_index = 0, rgb = 'ffffff')
  validate_workbook
  ensure_cell_exists(row_index)
  Color.validate_color(rgb)

  sheet_data.rows[row_index].style_index = @workbook.modify_fill(get_row_style(row_index), rgb)
  sheet_data[row_index].cells.each { |c| c.change_fill(rgb) unless c.nil? }
end

#change_row_font_color(row = 0, font_color = '000000') ⇒ Object 



126
127
128
129
130
131
132
 
# File 'lib/rubyXL/worksheet.rb', line 126

def change_row_font_color(row = 0, font_color = '000000')
  ensure_cell_exists(row)
  Color.validate_color(font_color)
  font = row_font(row).dup
  font.set_rgb_color(font_color)
  change_row_font(row, Worksheet::COLOR, font_color, font)
end

#change_row_font_name(row = 0, font_name = 'Verdana') ⇒ Object 



112
113
114
115
116
117
 
# File 'lib/rubyXL/worksheet.rb', line 112

def change_row_font_name(row = 0, font_name = 'Verdana')
  ensure_cell_exists(row)
  font = row_font(row).dup
  font.set_name(font_name)
  change_row_font(row, Worksheet::NAME, font_name, font)
end

#change_row_font_size(row = 0, font_size = 10) ⇒ Object 



119
120
121
122
123
124
 
# File 'lib/rubyXL/worksheet.rb', line 119

def change_row_font_size(row = 0, font_size=10)
  ensure_cell_exists(row)
  font = row_font(row).dup
  font.set_size(font_size)
  change_row_font(row, Worksheet::SIZE, font_size, font)
end

#change_row_height(row = 0, height = 10) ⇒ Object 



162
163
164
165
166
167
168
169
 
# File 'lib/rubyXL/worksheet.rb', line 162

def change_row_height(row = 0, height = 10)
  validate_workbook
  ensure_cell_exists(row)

  c = sheet_data.rows[row]
  c.ht = height
  c.custom_height = true
end

#change_row_horizontal_alignment(row = 0, alignment = 'center') ⇒ Object 



171
172
173
174
175
 
# File 'lib/rubyXL/worksheet.rb', line 171

def change_row_horizontal_alignment(row = 0, alignment = 'center')
  validate_workbook
  validate_nonnegative(row)
  change_row_alignment(row, alignment, true)
end

#change_row_italics(row = 0, italicized = false) ⇒ Object 



134
135
136
137
138
139
 
# File 'lib/rubyXL/worksheet.rb', line 134

def change_row_italics(row = 0, italicized = false)
  ensure_cell_exists(row)
  font = row_font(row).dup
  font.set_italic(italicized)
  change_row_font(row, Worksheet::ITALICS, italicized, font)
end

#change_row_strikethrough(row = 0, struckthrough = false) ⇒ Object 



155
156
157
158
159
160
 
# File 'lib/rubyXL/worksheet.rb', line 155

def change_row_strikethrough(row = 0, struckthrough=false)
  ensure_cell_exists(row)
  font = row_font(row).dup
  font.set_strikethrough(struckthrough)
  change_row_font(row, Worksheet::STRIKETHROUGH, struckthrough, font)
end

#change_row_underline(row = 0, underlined = false) ⇒ Object 



148
149
150
151
152
153
 
# File 'lib/rubyXL/worksheet.rb', line 148

def change_row_underline(row = 0, underlined=false)
  ensure_cell_exists(row)
  font = row_font(row).dup
  font.set_underline(underlined)
  change_row_font(row, Worksheet::UNDERLINE, underlined, font)
end

#change_row_vertical_alignment(row = 0, alignment = 'center') ⇒ Object 



177
178
179
180
181
 
# File 'lib/rubyXL/worksheet.rb', line 177

def change_row_vertical_alignment(row = 0, alignment = 'center')
  validate_workbook
  validate_nonnegative(row)
  change_row_alignment(row, alignment, false)
end

#delete_cell(row = 0, col = 0, shift = nil) ⇒ Object

by default, only sets cell to nil if :left is specified, method will shift row contents to the right of the deleted cell to the left if :up is specified, method will shift column contents below the deleted cell upward



502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
 
# File 'lib/rubyXL/worksheet.rb', line 502

def delete_cell(row = 0, col=0, shift=nil)
  validate_workbook
  validate_nonnegative(row)
  validate_nonnegative(col)

  return nil unless row_exists(row) && column_exists(col)

  cell = sheet_data[row][col]

  case shift
  when nil then
    sheet_data.rows[row].cells[col] = nil
  when :left then
    sheet_data.rows[row].delete_cell_shift_left(col)
  when :up then
    (row...(sheet_data.size - 1)).each { |index|
      c = sheet_data.rows[index].cells[col] = sheet_data.rows[index + 1].cells[col]
      c.row -= 1 if c.is_a?(Cell)
    }
  else
    raise 'invalid shift option'
  end

  return cell
end

#delete_column(column_index = 0) ⇒ Object 



432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
 
# File 'lib/rubyXL/worksheet.rb', line 432

def delete_column(column_index = 0)
  validate_workbook
  validate_nonnegative(column_index)

  return nil unless column_exists(column_index)

  sheet_data.rows.each { |row| row.cells.delete_at(column_index) }

  # Change column numbers for cells to the right of the deleted column
  sheet_data.rows.each_with_index { |row, row_index|
    row.cells.each_with_index { |c, column_index|
      c.column = column_index if c.is_a?(Cell)
    }
  }

  cols.column_ranges.each { |range| range.delete_column(column_index) }
end

#delete_row(row_index = 0) ⇒ Object 



382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
 
# File 'lib/rubyXL/worksheet.rb', line 382

def delete_row(row_index=0)
  validate_workbook
  validate_nonnegative(row_index)
  return nil unless row_exists(row_index)

  deleted = sheet_data.rows.delete_at(row_index)
  row_num = row_index+1

  # Change cell row numbers
  row_index.upto(sheet_data.size - 1) { |index|
    sheet_data[index].cells.each{ |c| c.row -= 1 unless c.nil? }
  }

  return deleted
end

#each ⇒ Object 



22
23
24
 
# File 'lib/rubyXL/worksheet.rb', line 22

def each
  sheet_data.rows.each { |row| yield(row) }
end

#extract_data(args = {}) ⇒ Object

returns 2d array of just the cell values (without style or formula information)



27
28
29
30
31
 
# File 'lib/rubyXL/worksheet.rb', line 27

def extract_data(args = {})
  sheet_data.rows.map { |row|
    row.cells.map { |c| c && c.value(args) } unless row.nil?
  }
end

#get_column_border_bottom(col = 0) ⇒ Object 



679
680
681
 
# File 'lib/rubyXL/worksheet.rb', line 679

def get_column_border_bottom(col=0)
  get_column_border(col, :bottom)
end

#get_column_border_diagonal(col = 0) ⇒ Object 



683
684
685
 
# File 'lib/rubyXL/worksheet.rb', line 683

def get_column_border_diagonal(col=0)
  get_column_border(col, :diagonal)
end

#get_column_border_left(col = 0) ⇒ Object 



671
672
673
 
# File 'lib/rubyXL/worksheet.rb', line 671

def get_column_border_left(col=0)
  get_column_border(col, :left)
end

#get_column_border_right(col = 0) ⇒ Object 



675
676
677
 
# File 'lib/rubyXL/worksheet.rb', line 675

def get_column_border_right(col=0)
  get_column_border(col, :right)
end

#get_column_border_top(col = 0) ⇒ Object 



667
668
669
 
# File 'lib/rubyXL/worksheet.rb', line 667

def get_column_border_top(col=0)
  get_column_border(col, :top)
end

#get_column_fill(col = 0) ⇒ Object 



651
652
653
654
655
656
657
 
# File 'lib/rubyXL/worksheet.rb', line 651

def get_column_fill(col=0)
  validate_workbook
  validate_nonnegative(col)
  return nil unless column_exists(col)

  @workbook.get_fill_color(get_col_xf(col))
end

#get_column_font_color(col = 0) ⇒ Object 



608
609
610
611
 
# File 'lib/rubyXL/worksheet.rb', line 608

def get_column_font_color(col = 0)
  font = column_font(col)
  font && (font.get_rgb_color || '000000')
end

#get_column_font_name(col = 0) ⇒ Object 



598
599
600
601
 
# File 'lib/rubyXL/worksheet.rb', line 598

def get_column_font_name(col = 0)
  font = column_font(col)
  font && font.get_name
end

#get_column_font_size(col = 0) ⇒ Object 



603
604
605
606
 
# File 'lib/rubyXL/worksheet.rb', line 603

def get_column_font_size(col = 0)
  font = column_font(col)
  font && font.get_size
end

#get_column_horizontal_alignment(col = 0) ⇒ Object 



659
660
661
 
# File 'lib/rubyXL/worksheet.rb', line 659

def get_column_horizontal_alignment(col=0)
  get_column_alignment(col, :horizontal)
end

#get_column_vertical_alignment(col = 0) ⇒ Object 



663
664
665
 
# File 'lib/rubyXL/worksheet.rb', line 663

def get_column_vertical_alignment(col=0)
  get_column_alignment(col, :vertical)
end

#get_column_width(column_index = 0) ⇒ Object

Get column width measured in number of digits, as per msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.column%28v=office.14%29.aspx



645
646
647
648
649
 
# File 'lib/rubyXL/worksheet.rb', line 645

def get_column_width(column_index = 0)
  width = get_column_width_raw(column_index)
  return RubyXL::ColumnRange::DEFAULT_WIDTH if width.nil?
  (width - (5.0 / RubyXL::Font::MAX_DIGIT_WIDTH)).round
end

#get_column_width_raw(column_index = 0) ⇒ Object

Get raw column width value as stored in the file



634
635
636
637
638
639
640
641
 
# File 'lib/rubyXL/worksheet.rb', line 634

def get_column_width_raw(column_index = 0)
  validate_workbook
  validate_nonnegative(column_index)
  return nil unless column_exists(column_index)

  range = cols.find(column_index)
  range && range.width
end

#get_row_border_bottom(row = 0) ⇒ Object 



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

def get_row_border_bottom(row = 0)
  return get_row_border(row, :bottom)
end

#get_row_border_diagonal(row = 0) ⇒ Object 



594
595
596
 
# File 'lib/rubyXL/worksheet.rb', line 594

def get_row_border_diagonal(row = 0)
  return get_row_border(row, :diagonal)
end

#get_row_border_left(row = 0) ⇒ Object 



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

def get_row_border_left(row = 0)
  return get_row_border(row, :left)
end

#get_row_border_right(row = 0) ⇒ Object 



586
587
588
 
# File 'lib/rubyXL/worksheet.rb', line 586

def get_row_border_right(row = 0)
  return get_row_border(row, :right)
end

#get_row_border_top(row = 0) ⇒ Object 



578
579
580
 
# File 'lib/rubyXL/worksheet.rb', line 578

def get_row_border_top(row = 0)
  return get_row_border(row, :top)
end

#get_row_fill(row = 0) ⇒ Object 



528
529
530
 
# File 'lib/rubyXL/worksheet.rb', line 528

def get_row_fill(row = 0)
  (row = sheet_data.rows[row]) && row.get_fill_color
end

#get_row_font_color(row = 0) ⇒ Object 



540
541
542
543
544
 
# File 'lib/rubyXL/worksheet.rb', line 540

def get_row_font_color(row = 0)
  font = row_font(row)
  color = font && font.color
  color && (color.rgb || '000000')
end

#get_row_font_name(row = 0) ⇒ Object 



532
533
534
 
# File 'lib/rubyXL/worksheet.rb', line 532

def get_row_font_name(row = 0)
  (font = row_font(row)) && font.get_name
end

#get_row_font_size(row = 0) ⇒ Object 



536
537
538
 
# File 'lib/rubyXL/worksheet.rb', line 536

def get_row_font_size(row = 0)
  (font = row_font(row)) && font.get_size
end

#get_row_height(row = 0) ⇒ Object 



562
563
564
565
566
567
568
 
# File 'lib/rubyXL/worksheet.rb', line 562

def get_row_height(row = 0)
  validate_workbook
  validate_nonnegative(row)
  return nil unless row_exists(row)
  row = sheet_data.rows[row]
  row && row.ht || 13
end

#get_row_horizontal_alignment(row = 0) ⇒ Object 



570
571
572
 
# File 'lib/rubyXL/worksheet.rb', line 570

def get_row_horizontal_alignment(row = 0)
  return get_row_alignment(row, true)
end

#get_row_vertical_alignment(row = 0) ⇒ Object 



574
575
576
 
# File 'lib/rubyXL/worksheet.rb', line 574

def get_row_vertical_alignment(row = 0)
  return get_row_alignment(row, false)
end

#get_table(headers = [], opts = {}) ⇒ Object 



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
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
 
# File 'lib/rubyXL/worksheet.rb', line 33

def get_table(headers = [], opts = {})
  validate_workbook

  headers = [headers] unless headers.is_a?(Array)
  row_num = find_first_row_with_content(headers)
  return nil if row_num.nil?

  table_hash = {}
  table_hash[:table] = []

  header_row = sheet_data[row_num]
  header_row.cells.each_with_index { |header_cell, index|
    break if index>0 && !opts[:last_header].nil? && !header_row[index-1].nil? && !header_row[index-1].value.nil? && header_row[index-1].value.to_s==opts[:last_header]
    next if header_cell.nil? || header_cell.value.nil?
    header = header_cell.value.to_s
    table_hash[:sorted_headers]||=[]
    table_hash[:sorted_headers] << header
    table_hash[header] = []

    original_row = row_num + 1
    current_row = original_row

    row = sheet_data.rows[current_row]
    cell = row && row.cells[index]

    # makes array of hashes in table_hash[:table]
    # as well as hash of arrays in table_hash[header]
    table_index = current_row - original_row
    cell_test = (!cell.nil? && !cell.value.nil?)

    while cell_test || (table_hash[:table][table_index] && !table_hash[:table][table_index].empty?)
      table_hash[header] << cell.value if cell_test
      table_index = current_row - original_row

      if cell_test then
        table_hash[:table][table_index] ||= {}
        table_hash[:table][table_index][header] = cell.value
      end

      current_row += 1
      if sheet_data.rows[current_row].nil? then
        cell = nil
      else
        cell = sheet_data.rows[current_row].cells[index]
      end
      cell_test = (!cell.nil? && !cell.value.nil?)
    end
  }

  return table_hash
end

#initialize(params = {}) ⇒ Object 



5
6
7
8
9
10
11
12
13
14
15
 
# File 'lib/rubyXL/worksheet.rb', line 5

def initialize(params = {})
  super
  self.workbook   = params[:workbook]
  self.sheet_name = params[:sheet_name]
  self.sheet_id   = params[:sheet_id]
  self.sheet_data = RubyXL::SheetData.new
  self.cols = RubyXL::ColumnRanges.new
  @comments = [] # Do not optimize! These are arrays, so they will share the pointer!
  @printer_settings = []
  @generic_storage = []
end

#insert_cell(row = 0, col = 0, data = nil, formula = nil, shift = nil) ⇒ Object 



479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
 
# File 'lib/rubyXL/worksheet.rb', line 479

def insert_cell(row = 0, col = 0, data = nil, formula = nil, shift = nil)
  validate_workbook
  ensure_cell_exists(row, col)

  case shift
  when nil then # No shifting at all
  when :right then
    sheet_data.rows[row].insert_cell_shift_right(nil, col)
  when :down then
    add_row(sheet_data.size, :cells => Array.new(sheet_data.rows[row].size))
    (sheet_data.size - 1).downto(row+1) { |index|
      sheet_data.rows[index].cells[col] = sheet_data.rows[index-1].cells[col]
    }
  else
    raise 'invalid shift option'
  end

  return add_cell(row,col,data,formula)
end

#insert_column(column_index = 0) ⇒ Object

Inserts column at column_index, pushes everything right, takes styles from column to left NOTE: use of this method will break formulas which reference cells which are being “pushed right”



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

def insert_column(column_index = 0)
  validate_workbook
  ensure_cell_exists(0, column_index)

  old_range = cols.get_range(column_index)

  #go through each cell in column
  sheet_data.rows.each_with_index { |row, row_index|
    old_cell = row[column_index]
    c = nil

    if old_cell && old_cell.style_index != 0 &&
         old_range && old_range.style_index != old_cell.style_index then

      c = RubyXL::Cell.new(:style_index => old_cell.style_index, :worksheet => self,
                           :row => row_index, :column => column_index,
                           :datatype => RubyXL::DataType::SHARED_STRING)
    end

    row.insert_cell_shift_right(c, column_index)
  }

  cols.insert_column(column_index)

  # TODO: update column numbers
end

#insert_row(row_index = 0) ⇒ Object

Inserts row at row_index, pushes down, copies style from the row above (that’s what Excel 2013 does!) NOTE: use of this method will break formulas which reference cells which are being “pushed down”



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

def insert_row(row_index = 0)
  validate_workbook
  ensure_cell_exists(row_index)

  old_row = new_cells = nil

  if row_index > 0 then
    old_row = sheet_data.rows[row_index - 1]
    if old_row then
      new_cells = old_row.cells.collect { |c|
                                          if c.nil? then nil
                                          else RubyXL::Cell.new(:style_index => c.style_index)
                                          end }
    end
  end

  row0 = sheet_data.rows[0]
  new_cells ||= Array.new((row0 && row0.cells.size) || 0)

  sheet_data.rows.insert(row_index, nil)
  new_row = add_row(row_index, :cells => new_cells, :style_index => old_row && old_row.style_index)

  # Update row values for all rows below
  row_index.upto(sheet_data.rows.size - 1) { |i|
    row = sheet_data.rows[i]
    next if row.nil?
    row.cells.each { |c| c.row = i unless c.nil? }
  }

  return new_row
end

#is_column_bolded(col = 0) ⇒ Object 



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

def is_column_bolded(col = 0)
  font = column_font(col)
  font && font.is_bold
end

#is_column_italicized(col = 0) ⇒ Object 



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

def is_column_italicized(col = 0)
  font = column_font(col)
  font && font.is_italic
end

#is_column_struckthrough(col = 0) ⇒ Object 



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

def is_column_struckthrough(col = 0)
  font = column_font(col)
  font && font.is_strikethrough
end

#is_column_underlined(col = 0) ⇒ Object 



623
624
625
626
 
# File 'lib/rubyXL/worksheet.rb', line 623

def is_column_underlined(col = 0)
  font = column_font(col)
  font && font.is_underlined
end

#is_row_bolded(row = 0) ⇒ Object 



550
551
552
 
# File 'lib/rubyXL/worksheet.rb', line 550

def is_row_bolded(row = 0)
  (font = row_font(row)) && font.is_bold
end

#is_row_italicized(row = 0) ⇒ Object 



546
547
548
 
# File 'lib/rubyXL/worksheet.rb', line 546

def is_row_italicized(row = 0)
  (font = row_font(row)) && font.is_italic
end

#is_row_struckthrough(row = 0) ⇒ Object 



558
559
560
 
# File 'lib/rubyXL/worksheet.rb', line 558

def is_row_struckthrough(row = 0)
  (font = row_font(row)) && font.is_strikethrough
end

#is_row_underlined(row = 0) ⇒ Object 



554
555
556
 
# File 'lib/rubyXL/worksheet.rb', line 554

def is_row_underlined(row = 0)
  (font = row_font(row)) && font.is_underlined
end

#merge_cells(row1 = 0, col1 = 0, row2 = 0, col2 = 0) ⇒ Object

merges cells within a rectangular range



347
348
349
350
351
352
 
# File 'lib/rubyXL/worksheet.rb', line 347

def merge_cells(row1 = 0, col1 = 0, row2 = 0, col2 = 0)
  validate_workbook

  self.merged_cells ||= RubyXL::MergedCells.new
  merged_cells << RubyXL::MergedCell.new(:ref => RubyXL::Reference.new(row1, row2, col1, col2))
end