Module: RubyXL::LegacyWorksheet
Instance Method Summary collapse
-
#[](row = 0) ⇒ Object
allows for easier access to sheet_data.
- #add_cell(row = 0, column = 0, data = '', formula = nil, overwrite = true) ⇒ Object
- #add_row(row = 0, params = {}) ⇒ Object
- #change_column_fill(column_index, color_index = 'ffffff') ⇒ Object
-
#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.
-
#change_column_width_raw(column_index, width) ⇒ Object
Set raw column width value.
-
#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.
- #delete_column(column_index = 0) ⇒ Object
- #delete_row(row_index = 0) ⇒ Object
- #each ⇒ Object
-
#extract_data(args = {}) ⇒ Object
returns 2d array of just the cell values (without style or formula information).
- #get_table(headers = [], opts = {}) ⇒ Object
- #initialize(params = {}) ⇒ Object
- #insert_cell(row = 0, col = 0, data = nil, formula = nil, shift = nil) ⇒ Object
-
#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”. -
#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”.
-
#merge_cells(row1 = 0, col1 = 0, row2 = 0, col2 = 0) ⇒ Object
merges cells within a rectangular range.
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
144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 |
# File 'lib/rubyXL/worksheet.rb', line 144 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.locate_range(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
138 139 140 141 142 |
# File 'lib/rubyXL/worksheet.rb', line 138 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_fill(column_index, color_index = 'ffffff') ⇒ Object
117 118 119 120 121 122 123 124 125 126 127 128 |
# File 'lib/rubyXL/worksheet.rb', line 117 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_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
113 114 115 |
# File 'lib/rubyXL/worksheet.rb', line 113 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
103 104 105 106 107 108 109 |
# File 'lib/rubyXL/worksheet.rb', line 103 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 |
#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
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 |
# File 'lib/rubyXL/worksheet.rb', line 285 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
215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 |
# File 'lib/rubyXL/worksheet.rb', line 215 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.each { |range| range.delete_column(column_index) } end |
#delete_row(row_index = 0) ⇒ Object
166 167 168 169 170 171 172 173 174 175 176 177 178 179 |
# File 'lib/rubyXL/worksheet.rb', line 166 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) # 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_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
262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 |
# File 'lib/rubyXL/worksheet.rb', line 262 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”
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 |
# File 'lib/rubyXL/worksheet.rb', line 235 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”
183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 |
# File 'lib/rubyXL/worksheet.rb', line 183 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 |
#merge_cells(row1 = 0, col1 = 0, row2 = 0, col2 = 0) ⇒ Object
merges cells within a rectangular range
131 132 133 134 135 136 |
# File 'lib/rubyXL/worksheet.rb', line 131 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 |