lib/rubyXL/convenience_methods.rb in rubyXL-3.3.15 vs lib/rubyXL/convenience_methods.rb in rubyXL-3.3.16
- old
+ new
@@ -1,6 +1,7 @@
module RubyXL
+
module WorkbookConvenienceMethods
SHEET_NAME_TEMPLATE = 'Sheet%d'
# Finds worksheet by its name or numerical index
def [](ind)
@@ -206,11 +207,11 @@
}
else
raise 'invalid shift option'
end
- return add_cell(row,col,data,formula)
+ return add_cell(row, col, data, formula)
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
@@ -322,17 +323,17 @@
def delete_column(column_index = 0)
validate_workbook
validate_nonnegative(column_index)
- #delete column
+ # Delete column
sheet_data.rows.each { |row| row.cells.delete_at(column_index) }
- # Change column numbers for cells to the right of the deleted column
+ # Update 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)
+ row.cells.each_with_index { |c, ci|
+ c.column = ci if c.is_a?(Cell)
}
}
cols.each { |range| range.delete_column(column_index) }
end
@@ -378,11 +379,11 @@
def get_row_height(row = 0)
validate_workbook
validate_nonnegative(row)
row = sheet_data.rows[row]
- row && row.ht || 13
+ row && row.ht || RubyXL::Row::DEFAULT_HEIGHT
end
def get_row_border(row, border_direction)
validate_workbook
@@ -471,10 +472,25 @@
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
+ # Set raw column width value
+ 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
+
+ # Get column width measured in number of digits, as per
+ # http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.column%28v=office.14%29.aspx
+ 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
+
# Helper method to get the style index for a column
def get_col_style(column_index)
range = cols.locate_range(column_index)
(range && range.style_index) || 0
end
@@ -484,10 +500,23 @@
validate_nonnegative(col)
@workbook.get_fill_color(get_col_xf(col))
end
+ def change_column_fill(column_index, color_code = 'ffffff')
+ validate_workbook
+ RubyXL::Color.validate_color(color_code)
+ ensure_cell_exists(0, column_index)
+
+ cols.get_range(column_index).style_index = @workbook.modify_fill(get_col_style(column_index), color_code)
+
+ sheet_data.rows.each { |row|
+ c = row[column_index]
+ c.change_fill(color_code) if c
+ }
+ end
+
def get_column_border(col, border_direction)
validate_workbook
xf = @workbook.cell_xfs[get_cols_style_index(col)]
border = @workbook.borders[xf.border_id]
@@ -723,72 +752,18 @@
next if c.nil?
c.style_index = @workbook.modify_alignment(c.style_index, &block)
}
end
- # Returns 2D array of just the cell values (without style or formula information)
- def extract_data(args = {})
- warn "[DEPRECATION] `#{__method__}` is deprecated. Please access data directly by iterating through .sheet_data.rows"
- sheet_data.rows.map { |row|
- row.cells.map { |c| c && c.value(args) } unless row.nil?
- }
- end
-
- def get_table(headers = [], opts = {})
- warn "[DEPRECATION] `#{__method__}` is deprecated. Please access data directly by iterating through .sheet_data.rows"
+ # Merges cells within a rectangular area
+ def merge_cells(start_row, start_col, end_row, end_col)
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
+ self.merged_cells ||= RubyXL::MergedCells.new
+ # TODO: add validation to make sure ranges are not intersecting with existing ones
+ merged_cells << RubyXL::MergedCell.new(:ref => RubyXL::Reference.new(start_row, end_row, start_col, end_col))
end
-
end
-
module CellConvenienceMethods
def change_contents(data, formula_expression = nil)
validate_worksheet