class RubyXL::Worksheet
Constants
- BOLD
- COLOR
- ITALICS
- NAME
- SHEET_NAME_TEMPLATE
- SIZE
- STRIKETHROUGH
- UNDERLINE
Attributes
Public Class Methods
# File lib/rubyXL/worksheet.rb, line 11 def initialize(workbook, sheet_name = nil, sheet_data= [[nil]], cols=[], merged_cells=[]) @workbook = workbook @sheet_name = sheet_name || get_default_name @sheet_id = nil @sheet_data = sheet_data @column_ranges = cols @merged_cells = merged_cells || [] @row_styles = [] @sheet_views = [ RubyXL::SheetView.new ] @extLst = nil @legacy_drawings = [] @drawings = [] @validations = [] end
Public Instance Methods
allows for easier access to sheet_data
# File lib/rubyXL/worksheet.rb, line 39 def [](row = 0) @sheet_data[row] end
# File lib/rubyXL/worksheet.rb, line 350 def add_cell(row=0, column=0, data='', formula=nil,overwrite=true) validate_workbook validate_nonnegative(row) validate_nonnegative(column) ensure_cell_exists(row, column) datatype = (formula.nil?) ? RubyXL::Cell::RAW_STRING : '' if overwrite || @sheet_data[row][column].nil? @sheet_data[row][column] = Cell.new(self,row,column,data,formula,datatype) if (data.is_a?Integer) || (data.is_a?Float) @sheet_data[row][column].datatype = '' end col = RubyXL::ColumnRange.find(column, @column_ranges) if @row_styles[row+1] != nil @sheet_data[row][column].style_index = @row_styles[row+1][:style] elsif col != nil @sheet_data[row][column].style_index = col.style_index end end @sheet_data[row][column].style_index ||= 0 add_cell_style(row,column) return @sheet_data[row][column] end
# File lib/rubyXL/worksheet.rb, line 380 def add_cell_obj(cell, overwrite=true) validate_workbook if cell.nil? return cell end row = cell.row column = cell.column validate_nonnegative(row) validate_nonnegative(column) ensure_cell_exists(row, column) if overwrite || @sheet_data[row][column].nil? @sheet_data[row][column] = cell end add_cell_style(row,column) return @sheet_data[row][column] end
# File lib/rubyXL/worksheet.rb, line 265 def change_column_bold(col = 0, bolded = false) xf = get_col_xf(col) font = @workbook.fonts[xf.font_id].dup font.set_bold(bolded) change_column_font(col, Worksheet::BOLD, bolded, font, xf) end
# File lib/rubyXL/worksheet.rb, line 336 def change_column_border_bottom(col=0,weight = 'thin') change_column_border(col, :bottom, weight) end
# File lib/rubyXL/worksheet.rb, line 340 def change_column_border_diagonal(col=0,weight = 'thin') change_column_border(col, :diagonal, weight) end
# File lib/rubyXL/worksheet.rb, line 328 def change_column_border_left(col=0,weight = 'thin') change_column_border(col, :left, weight) end
# File lib/rubyXL/worksheet.rb, line 332 def change_column_border_right(col=0,weight = 'thin') change_column_border(col, :right, weight) end
# File lib/rubyXL/worksheet.rb, line 324 def change_column_border_top(col=0,weight = 'thin') change_column_border(col, :top, weight) end
# File lib/rubyXL/worksheet.rb, line 299 def change_column_fill(col=0, color_index='ffffff') validate_workbook validate_nonnegative(col) Color.validate_color(color_index) ensure_cell_exists(0, col) new_style_index = modify_fill(@workbook, get_column_style_index(col), color_index) RubyXL::ColumnRange.update(col, @column_ranges, { 'style' => new_style_index }) @sheet_data.each { |row| c = row[col] c.change_fill(color_index) if c } end
Changes font color of column
# File lib/rubyXL/worksheet.rb, line 249 def change_column_font_color(col=0, font_color='000000') Color.validate_color(font_color) xf = get_col_xf(col) font = @workbook.fonts[xf.font_id].dup font.set_rgb_color(font_color) change_column_font(col, Worksheet::COLOR, font_color, font, xf) end
Changes font name of column
# File lib/rubyXL/worksheet.rb, line 233 def change_column_font_name(col = 0, font_name = 'Verdana') xf = get_col_xf(col) font = @workbook.fonts[xf.font_id].dup font.set_name(font_name) change_column_font(col, Worksheet::NAME, font_name, font, xf) end
Changes font size of column
# File lib/rubyXL/worksheet.rb, line 241 def change_column_font_size(col=0, font_size=10) xf = get_col_xf(col) font = @workbook.fonts[xf.font_id].dup font.set_size(font_size) change_column_font(col, Worksheet::SIZE, font_size, font, xf) end
# File lib/rubyXL/worksheet.rb, line 314 def change_column_horizontal_alignment(col=0,alignment='center') validate_horizontal_alignment(alignment) change_column_alignment(col,alignment,true) end
# File lib/rubyXL/worksheet.rb, line 258 def change_column_italics(col = 0, italicized = false) xf = get_col_xf(col) font = @workbook.fonts[xf.font_id].dup font.set_italic(italicized) change_column_font(col, Worksheet::ITALICS, italicized, font, xf) end
# File lib/rubyXL/worksheet.rb, line 279 def change_column_strikethrough(col=0, struckthrough=false) xf = get_col_xf(col) font = @workbook.fonts[xf.font_id].dup font.set_strikethrough(struckthrough) change_column_font(col, Worksheet::STRIKETHROUGH, struckthrough, font, xf) end
# File lib/rubyXL/worksheet.rb, line 272 def change_column_underline(col = 0, underlined = false) xf = get_col_xf(col) font = @workbook.fonts[xf.font_id].dup font.set_underline(underlined) change_column_font(col, Worksheet::UNDERLINE, underlined, font, xf) end
# File lib/rubyXL/worksheet.rb, line 319 def change_column_vertical_alignment(col=0,alignment='center') validate_vertical_alignment(alignment) change_column_alignment(col,alignment,false) end
# File lib/rubyXL/worksheet.rb, line 286 def change_column_width(col = 0, width = 13) validate_workbook validate_nonnegative(col) ensure_cell_exists(0, col) RubyXL::ColumnRange.update(col, @column_ranges, { 'width' => width, 'customWidth' => 1 }) end
# File lib/rubyXL/worksheet.rb, line 155 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
# File lib/rubyXL/worksheet.rb, line 224 def change_row_border_bottom(row = 0, weight = 'thin') change_row_border(row, :bottom, weight) end
# File lib/rubyXL/worksheet.rb, line 228 def change_row_border_diagonal(row = 0, weight = 'thin') change_row_border(row, :diagonal, weight) end
# File lib/rubyXL/worksheet.rb, line 216 def change_row_border_left(row = 0, weight = 'thin') change_row_border(row, :left, weight) end
# File lib/rubyXL/worksheet.rb, line 220 def change_row_border_right(row = 0, weight = 'thin') change_row_border(row, :right, weight) end
# File lib/rubyXL/worksheet.rb, line 212 def change_row_border_top(row = 0, weight = 'thin') change_row_border(row, :top, weight) end
changes color of fill in (zer0 indexed) row
# File lib/rubyXL/worksheet.rb, line 106 def change_row_fill(row = 0, rgb = 'ffffff') validate_workbook validate_nonnegative(row) ensure_cell_exists(row) Color.validate_color(rgb) if @row_styles[(Integer(row)+1)].nil? @row_styles[(Integer(row)+1)] = {} @row_styles[(Integer(row)+1)][:style] = 0 end @row_styles[(Integer(row)+1)][:style] = modify_fill(@workbook,Integer(@row_styles[(Integer(row)+1)][:style]),rgb) @sheet_data[Integer(row)].each do |c| unless c.nil? c.change_fill(rgb) end end end
# File lib/rubyXL/worksheet.rb, line 140 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
# File lib/rubyXL/worksheet.rb, line 126 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
# File lib/rubyXL/worksheet.rb, line 133 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
# File lib/rubyXL/worksheet.rb, line 176 def change_row_height(row = 0, height=10) validate_workbook validate_nonnegative(row) ensure_cell_exists(row) if height.to_i.to_s == height.to_s height = Integer(height) elsif height.to_f.to_s == height.to_s height = Float(height) else raise 'You must enter a number for the height' end if @row_styles[(row+1)].nil? @row_styles[(row+1)] = {} @row_styles[(row+1)][:style] = 0 end @row_styles[(row+1)][:height] = height @row_styles[(row+1)][:customHeight] = '1' end
# File lib/rubyXL/worksheet.rb, line 198 def change_row_horizontal_alignment(row = 0,alignment='center') validate_workbook validate_nonnegative(row) validate_horizontal_alignment(alignment) change_row_alignment(row,alignment,true) end
# File lib/rubyXL/worksheet.rb, line 148 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
# File lib/rubyXL/worksheet.rb, line 169 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
# File lib/rubyXL/worksheet.rb, line 162 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
# File lib/rubyXL/worksheet.rb, line 205 def change_row_vertical_alignment(row = 0,alignment='center') validate_workbook validate_nonnegative(row) validate_vertical_alignment(alignment) change_row_alignment(row,alignment,false) end
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
# File lib/rubyXL/worksheet.rb, line 569 def delete_cell(row = 0, col=0, shift=nil) validate_workbook validate_nonnegative(row) validate_nonnegative(col) return nil if @sheet_data.size <= row || @sheet_data[row].size <= col cell = @sheet_data[row][col] case shift when nil then @sheet_data[row][col] = nil when :left then @sheet_data[row].delete_at(col) @sheet_data[row] << nil (col...(@sheet_data[row].size)).each { |index| if @sheet_data[row][index].is_a?(Cell) @sheet_data[row][index].column -= 1 end } when :up then (row...(@sheet_data.size-1)).each { |index| @sheet_data[index][col] = @sheet_data[index+1][col] if @sheet_data[index][col].is_a?(Cell) @sheet_data[index][col].row -= 1 end } @sheet_data.last[col].row -= 1 if @sheet_data.last[col].is_a?(Cell) else raise 'invalid shift option' end return cell end
# File lib/rubyXL/worksheet.rb, line 482 def delete_column(col_index=0) validate_workbook validate_nonnegative(col_index) if col_index >= @sheet_data[0].size return nil end #delete column @sheet_data.map {|r| r.delete_at(col_index)} #change column numbers for cells to right of deleted column @sheet_data.each_with_index do |row,row_index| (col_index...(row.size)).each do |index| if @sheet_data[row_index][index].is_a?(Cell) @sheet_data[row_index][index].column -= 1 end end end @column_ranges.each { |range| range.delete_column(col_index) } end
# File lib/rubyXL/worksheet.rb, line 403 def delete_row(row_index=0) validate_workbook validate_nonnegative(row_index) if row_index >= @sheet_data.size return nil end deleted = @sheet_data.delete_at(row_index) row_num = row_index+1 @row_styles.delete_at(row_index) # Change cell row numbers row_index.upto(@sheet_data.size - 1) { |index| @sheet_data[index].each{ |c| c.row -= 1 unless c.nil? } } return deleted end
# File lib/rubyXL/worksheet.rb, line 43 def each @sheet_data.each { |row| yield(row) } end
returns 2d array of just the cell values (without style or formula information)
# File lib/rubyXL/worksheet.rb, line 48 def extract_data(args = {}) raw_values = args.delete(:raw) || false return @sheet_data.map {|row| row.map {|c| if c.is_a?(Cell) then c.value(:raw => raw_values) else nil end}} end
# File lib/rubyXL/worksheet.rb, line 776 def get_column_border_bottom(col=0) get_column_border(col, :bottom) end
# File lib/rubyXL/worksheet.rb, line 780 def get_column_border_diagonal(col=0) get_column_border(col, :diagonal) end
# File lib/rubyXL/worksheet.rb, line 768 def get_column_border_left(col=0) get_column_border(col, :left) end
# File lib/rubyXL/worksheet.rb, line 772 def get_column_border_right(col=0) get_column_border(col, :right) end
# File lib/rubyXL/worksheet.rb, line 764 def get_column_border_top(col=0) get_column_border(col, :top) end
# File lib/rubyXL/worksheet.rb, line 749 def get_column_fill(col=0) validate_workbook validate_nonnegative(col) return nil if @sheet_data[0].size <= col @workbook.get_fill_color(get_col_xf(col)) end
# File lib/rubyXL/worksheet.rb, line 711 def get_column_font_color(col = 0) font = column_font(col) font && (font.get_rgb_color || '000000') end
# File lib/rubyXL/worksheet.rb, line 701 def get_column_font_name(col = 0) font = column_font(col) font && font.get_name end
# File lib/rubyXL/worksheet.rb, line 706 def get_column_font_size(col = 0) font = column_font(col) font && font.get_size end
# File lib/rubyXL/worksheet.rb, line 756 def get_column_horizontal_alignment(col=0) get_column_alignment(col, :horizontal) end
# File lib/rubyXL/worksheet.rb, line 294 def get_column_style_index(col) range = RubyXL::ColumnRange.find(col, @column_ranges) (range && range.style_index) || 0 end
# File lib/rubyXL/worksheet.rb, line 760 def get_column_vertical_alignment(col=0) get_column_alignment(col, :vertical) end
# File lib/rubyXL/worksheet.rb, line 736 def get_column_width(col=0) validate_workbook validate_nonnegative(col) if @sheet_data[0].size <= col return nil end range = RubyXL::ColumnRange.find(col, @column_ranges) (range && range.width) || 10 end
# File lib/rubyXL/worksheet.rb, line 693 def get_row_border_bottom(row = 0) return get_row_border(row, :bottom) end
# File lib/rubyXL/worksheet.rb, line 697 def get_row_border_diagonal(row = 0) return get_row_border(row, :diagonal) end
# File lib/rubyXL/worksheet.rb, line 685 def get_row_border_left(row = 0) return get_row_border(row, :left) end
# File lib/rubyXL/worksheet.rb, line 689 def get_row_border_right(row = 0) return get_row_border(row, :right) end
# File lib/rubyXL/worksheet.rb, line 681 def get_row_border_top(row = 0) return get_row_border(row, :top) end
# File lib/rubyXL/worksheet.rb, line 605 def get_row_fill(row = 0) validate_workbook validate_nonnegative(row) if @sheet_data.size <= row return nil end if @row_styles[(row+1)].nil? return "ffffff" #default, white end xf = get_row_xf(row) return @workbook.get_fill_color(xf) end
# File lib/rubyXL/worksheet.rb, line 632 def get_row_font_color(row = 0) font = row_font(row) color = font && font.color color && (color.rgb || '000000') end
# File lib/rubyXL/worksheet.rb, line 622 def get_row_font_name(row = 0) font = row_font(row) font && font.get_name end
# File lib/rubyXL/worksheet.rb, line 627 def get_row_font_size(row = 0) font = row_font(row) font && font.get_size end
# File lib/rubyXL/worksheet.rb, line 658 def get_row_height(row = 0) validate_workbook validate_nonnegative(row) if @sheet_data.size <= row return nil end if @row_styles[(row+1)].nil? return 13 else @row_styles[(row+1)][:height] end end
# File lib/rubyXL/worksheet.rb, line 673 def get_row_horizontal_alignment(row = 0) return get_row_alignment(row,true) end
# File lib/rubyXL/worksheet.rb, line 677 def get_row_vertical_alignment(row = 0) return get_row_alignment(row,false) end
# File lib/rubyXL/worksheet.rb, line 53 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.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 cell = @sheet_data[current_row][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[current_row].nil? then cell = nil else cell = @sheet_data[current_row][index] end cell_test = (!cell.nil? && !cell.value.nil?) end } return table_hash end
# File lib/rubyXL/worksheet.rb, line 539 def insert_cell(row = 0, col = 0, data = nil, formula = nil, shift = nil) validate_workbook validate_nonnegative(row) validate_nonnegative(col) ensure_cell_exists(row, col) case shift when nil then # No shifting at all when :right then @sheet_data[row].insert(col,nil) (row...(@sheet_data[row].size)).each { |index| if @sheet_data[row][index].is_a?(Cell) @sheet_data[row][index].column += 1 end } when :down then @sheet_data << Array.new(@sheet_data[row].size) (@sheet_data.size-1).downto(row+1) { |index| @sheet_data[index][col] = @sheet_data[index-1][col] } else raise 'invalid shift option' end return add_cell(row,col,data,formula) end
inserts column at col_index, pushes everything right, takes styles from column to left USE OF THIS METHOD will break formulas which reference cells which are being “pushed down”
# File lib/rubyXL/worksheet.rb, line 507 def insert_column(col_index = 0) validate_workbook validate_nonnegative(col_index) ensure_cell_exists(0, col_index) old_range = col_index > 0 ? RubyXL::ColumnRange.find(col_index, @column_ranges) : RubyXL::ColumnRange.new #go through each cell in column @sheet_data.each_with_index do |row, row_index| old_cell = row[col_index] new_cell = nil if old_cell && old_cell.style_index != 0 && old_range && old_range.style_index != old_cell.style_index.to_i then new_cell = Cell.new(self, row_index, col_index) new_cell.style_index = old_cell.style_index end row.insert(col_index, new_cell) end ColumnRange.insert_column(col_index, @column_ranges) #update column numbers @sheet_data.each { |row| (col_index + 1).upto(row.size) { |col| row[col].column = col unless row[col].nil? } } end
inserts row at row_index, pushes down, copies style from below (row previously at that index) USE OF THIS METHOD will break formulas which reference cells which are being “pushed down”
# File lib/rubyXL/worksheet.rb, line 426 def insert_row(row_index=0) validate_workbook validate_nonnegative(row_index) ensure_cell_exists(row_index) @sheet_data.insert(row_index,Array.new(@sheet_data[row_index].size)) row_num = row_index+1 #copy cell styles from row above, (or below if first row) @sheet_data[row_index].each_index do |i| if row_index > 0 old_cell = @sheet_data[row_index-1][i] else old_cell = @sheet_data[row_index+1][i] end unless old_cell.nil? #only add cell if style exists, not copying content if @row_styles[(row_num+1)].nil? @row_styles[(row_num+1)] = {:style=>0} end if old_cell.style_index != 0 && old_cell.style_index != @row_styles[(row_num+1)][:style] c = Cell.new(self,row_index,i) c.style_index = old_cell.style_index @sheet_data[row_index][i] = c end end end #copy row styles from row above, (or below if first row) (@row_styles.size+1).downto(row_num+1) do |i| @row_styles[i] = @row_styles[(i-1)] end if row_index > 0 @row_styles[row_num] = @row_styles[(row_num-1)] else @row_styles[row_num] = nil #@row_styles[(row_num+1).to_s] end #update row value for all rows below (row_index+1).upto(@sheet_data.size-1) do |i| row = @sheet_data[i] row.each do |c| unless c.nil? c.row += 1 end end end return @sheet_data[row_index] end
# File lib/rubyXL/worksheet.rb, line 721 def is_column_bolded(col = 0) font = column_font(col) font && font.is_bold end
# File lib/rubyXL/worksheet.rb, line 716 def is_column_italicized(col = 0) font = column_font(col) font && font.is_italic end
# File lib/rubyXL/worksheet.rb, line 731 def is_column_struckthrough(col = 0) font = column_font(col) font && font.is_strikethrough end
# File lib/rubyXL/worksheet.rb, line 726 def is_column_underlined(col = 0) font = column_font(col) font && font.is_underlined end
# File lib/rubyXL/worksheet.rb, line 643 def is_row_bolded(row = 0) font = row_font(row) font && font.is_bold end
# File lib/rubyXL/worksheet.rb, line 638 def is_row_italicized(row = 0) font = row_font(row) font && font.is_italic end
# File lib/rubyXL/worksheet.rb, line 653 def is_row_struckthrough(row = 0) font = row_font(row) font && font.is_strikethrough end
# File lib/rubyXL/worksheet.rb, line 648 def is_row_underlined(row = 0) font = row_font(row) font && font.is_underlined end
merges cells within a rectangular range
# File lib/rubyXL/worksheet.rb, line 345 def merge_cells(row1 = 0, col1 = 0, row2 = 0, col2 = 0) validate_workbook @merged_cells << RubyXL::Reference.new(row1, row2, col1, col2) end
Private Instance Methods
# File lib/rubyXL/worksheet.rb, line 1095 def add_cell_style(row,column) xf = @workbook.cell_xfs[@sheet_data[row][column].style_index] @workbook.fonts[xf.font_id].count += 1 @workbook.fills[xf.fill_id].count += 1 @workbook.borders[xf.border_id].count += 1 end
# File lib/rubyXL/worksheet.rb, line 1021 def change_column_alignment(col,alignment,is_horizontal) validate_workbook validate_nonnegative(col) ensure_cell_exists(0, col) new_style_index = modify_alignment(@workbook, get_column_style_index(col), is_horizontal, alignment) RubyXL::ColumnRange.update(col, @column_ranges, { 'style' => new_style_index }) @sheet_data.each { |row| c = row[col] next if c.nil? if is_horizontal c.change_horizontal_alignment(alignment) else c.change_vertical_alignment(alignment) end } end
# File lib/rubyXL/worksheet.rb, line 1066 def change_column_border(col, direction, weight) col = Integer(col) validate_workbook validate_nonnegative(col) validate_border(weight) ensure_cell_exists(0, col) xf = get_col_xf(col) border = @workbook.borders[xf.border_id].dup border.set_edge_style(direction, weight) xf = workbook.register_new_border(border, get_col_xf(col)) new_style_index = workbook.register_new_xf(xf, get_col_style(col)) RubyXL::ColumnRange.update(col, @column_ranges, { 'style' => new_style_index }) @sheet_data.each { |row| c = row[col] next if c.nil? case direction when :top then c.change_border_top(weight) when :left then c.change_border_left(weight) when :right then c.change_border_right(weight) when :bottom then c.change_border_bottom(weight) when :diagonal then c.change_border_diagonal(weight) else raise 'invalid direction' end } end
Helper method to update the fonts and cell styles array main method to change font, called from each separate font mutator method
# File lib/rubyXL/worksheet.rb, line 901 def change_column_font(col, change_type, arg, font, xf) validate_workbook validate_nonnegative(col) ensure_cell_exists(0, col) xf = workbook.register_new_font(font, xf) new_style_index = workbook.register_new_xf(xf, get_col_style(col)) RubyXL::ColumnRange.update(col, @column_ranges, { 'style' => new_style_index }) @sheet_data.each { |row| c = row[col] font_switch(c, change_type, arg) unless c.nil? } end
# File lib/rubyXL/worksheet.rb, line 997 def change_row_alignment(row,alignment,is_horizontal) validate_workbook validate_nonnegative(row) ensure_cell_exists(row) if @row_styles[(row+1)].nil? @row_styles[(row+1)] = {} @row_styles[(row+1)][:style] = 0 end @row_styles[(row+1)][:style] = modify_alignment(@workbook,@row_styles[(row+1)][:style],is_horizontal,alignment) @sheet_data[row].each do |c| unless c.nil? if is_horizontal c.change_horizontal_alignment(alignment) else c.change_vertical_alignment(alignment) end end end end
# File lib/rubyXL/worksheet.rb, line 1040 def change_row_border(row, direction, weight) validate_workbook validate_nonnegative(row) validate_border(weight) ensure_cell_exists(row) xf = get_row_xf(row) border = @workbook.borders[xf.border_id].dup border.set_edge_style(direction, weight) xf = workbook.register_new_border(border, xf) @row_styles[(row+1)][:style] = workbook.register_new_xf(xf, @row_styles[(row+1)][:style]) @sheet_data[row].each { |c| next if c.nil? case direction when :top then c.change_border_top(weight) when :left then c.change_border_left(weight) when :right then c.change_border_right(weight) when :bottom then c.change_border_bottom(weight) when :diagonal then c.change_border_diagonal(weight) else raise 'invalid direction' end } end
Helper method to update the row styles array change_type - NAME
or SIZE
or COLOR
etc main method to change font, called from each separate font mutator method
# File lib/rubyXL/worksheet.rb, line 885 def change_row_font(row, change_type, arg, font) validate_workbook validate_nonnegative(row) ensure_cell_exists(row) xf = workbook.register_new_font(font, get_row_xf(row)) @row_styles[(row+1)][:style] = workbook.register_new_xf(xf, @row_styles[(row+1)][:style]) @sheet_data[row] ||= [] @sheet_data[Integer(row)].each { |c| font_switch(c, change_type, arg) unless c.nil? } end
# File lib/rubyXL/worksheet.rb, line 838 def column_font(col) validate_workbook validate_nonnegative(col) return nil if @sheet_data[0].size <= col style_index = get_cols_style_index(col) @workbook.fonts[@workbook.cell_xfs[style_index].font_id] end
Ensures that cell with row_index
and col_index
exists in
+sheet_data+ arrays, growing them up if necessary.
# File lib/rubyXL/worksheet.rb, line 959 def ensure_cell_exists(row_index, col_index = 0) # Writing anything to a cell in the array automatically creates all the members # with lower indices, filling them with +nil+s. But, we can't just write +nil+ # to +col_index+ because it may be less than +size+! So we read from that index # (if it didn't exist, we will get nil) and write right back. @sheet_data.each { |r| r[col_index] = r[col_index] } col_size = @sheet_data[0].size # Doing +.downto()+ here so the reallocation of row array has to only happen once, # when it is extended to max size; after that, we will be writing into existing # (but empty) members. Additional checks are not necessary, because if +row_index+ # is less than +size+, then +.downto()+ will not execute, and if it equals +size+, # then the block will be invoked exactly once, which takes care of the case when # +row_index+ is greater than the current max index by exactly 1. row_index.downto(@sheet_data.size) { |r| @sheet_data[r] = Array.new(col_size) } end
finds first row which contains at least all strings in cells_content
# File lib/rubyXL/worksheet.rb, line 1103 def find_first_row_with_content(cells_content) validate_workbook index = nil @sheet_data.each_with_index do |row, index| cells_content = cells_content.map { |header| header.to_s.downcase.strip } original_cells_content = row.map { |cell| cell.nil? ? '' : cell.value.to_s.downcase.strip } if (cells_content & original_cells_content).size == cells_content.size return index end end return nil end
performs correct modification based on what type of change_type is specified
# File lib/rubyXL/worksheet.rb, line 917 def font_switch(c,change_type,arg) case change_type when Worksheet::NAME unless arg.is_a?String raise 'Not a String' end c.change_font_name(arg) when Worksheet::SIZE unless arg.is_a?(Integer) || arg.is_a?(Float) raise 'Not a Number' end c.change_font_size(arg) when Worksheet::COLOR Color.validate_color(arg) c.change_font_color(arg) when Worksheet::ITALICS unless arg == !!arg raise 'Not a boolean' end c.change_font_italics(arg) when Worksheet::BOLD unless arg == !!arg raise 'Not a boolean' end c.change_font_bold(arg) when Worksheet::UNDERLINE unless arg == !!arg raise 'Not a boolean' end c.change_font_underline(arg) when Worksheet::STRIKETHROUGH unless arg == !!arg raise 'Not a boolean' end c.change_font_strikethrough(arg) else raise 'Invalid change_type' end end
Helper method to get the style index for a column
# File lib/rubyXL/worksheet.rb, line 988 def get_col_style(col) range = RubyXL::ColumnRange.find(col, @column_ranges) (range && range.style_index) || 0 end
# File lib/rubyXL/worksheet.rb, line 993 def get_col_xf(col) @workbook.cell_xfs[get_col_style(col)] end
# File lib/rubyXL/worksheet.rb, line 877 def get_cols_style_index(col) range = RubyXL::ColumnRange.find(col, @column_ranges) (range && range.style_index) || 0 end
# File lib/rubyXL/worksheet.rb, line 847 def get_column_alignment(col, type) validate_workbook validate_nonnegative(col) return nil if @sheet_data[0].size <= col xf = @workbook.cell_xfs[get_cols_style_index(col)] xf.alignment && xf.alignment.send(type) end
# File lib/rubyXL/worksheet.rb, line 856 def get_column_border(col, border_direction) validate_workbook validate_nonnegative(col) return nil if @sheet_data[0].size <= col xf = @workbook.cell_xfs[get_cols_style_index(col)] border = @workbook.borders[xf.border_id] border && border.get_edge_style(border_direction) end
# File lib/rubyXL/worksheet.rb, line 27 def get_default_name n = 0 begin name = SHEET_NAME_TEMPLATE % (n += 1) end until @workbook[name].nil? name end
# File lib/rubyXL/worksheet.rb, line 809 def get_row_alignment(row, is_horizontal) validate_workbook validate_nonnegative(row) if @sheet_data.size <= row || @row_styles[(row+1)].nil? return nil end xf_obj = @workbook.cell_xfs[@row_styles[(row+1)][:style]] return nil if xf_obj.alignment.nil? if is_horizontal then return xf_obj.alignment.horizontal else return xf_obj.alignment.vertical end end
# File lib/rubyXL/worksheet.rb, line 828 def get_row_border(row, border_direction) validate_workbook validate_nonnegative(row) return nil if @sheet_data.size <= row || @row_styles[(row+1)].nil? border = @workbook.borders[get_row_xf(row).border_id] border && border.get_edge_style(border_direction) end
Helper method to get the style index for a row
# File lib/rubyXL/worksheet.rb, line 978 def get_row_style(row) if @row_styles[(row+1)].nil? @row_styles[(row+1)] = {} @row_styles[(row+1)][:style] = 0 @workbook.fonts[0].count += 1 end return @row_styles[(row+1)][:style] end
row_styles
is assumed to not be nil at specified row
# File lib/rubyXL/worksheet.rb, line 796 def get_row_xf(row) @row_styles[(row+1)] ||= { :style => 0 } @workbook.cell_xfs[@row_styles[(row+1)][:style]] end
# File lib/rubyXL/worksheet.rb, line 801 def row_font(row) validate_workbook validate_nonnegative(row) xf = get_row_xf(row) return nil if @sheet_data.size <= row @workbook.fonts[xf.font_id] end
validates Workbook
, ensures that this worksheet is in @workbook
# File lib/rubyXL/worksheet.rb, line 869 def validate_workbook() unless @workbook.nil? || @workbook.worksheets.nil? return if @workbook.worksheets.include?(self) end raise "This worksheet #{self} is not in workbook #{@workbook}" end