How to

Create an absolute reference for a given row and column

snippet

class RubyXL::Reference
  def self.ind2absref(row, column)
    ("$$".chars).zip(ind2ref(row, column).chars).join
  end
end

The above snippet extends the Reference class.

Example usage

RubyXL::Reference.ind2absref(2,2) 

returns

 "$C$3"

Define a name for a cell on a worksheet

snippet

class RubyXL::Worksheet
  def define_name(row, column, name)
    workbook.defined_names ||= RubyXL::DefinedNames.new
    reference = "'#{sheet_name}'!#{RubyXL::Reference.ind2absref(row, column)}"
    workbook.defined_names << RubyXL::DefinedName.new({:name => name, :reference => reference})
  end
end

The above snippet extends the Worksheet class. It assumes the ind2absref extension on Reference is present and defines a name for a given cell reference.

For example

worksheet.define_name(2,2, 'myname') 

will define name for 'myname' with an absolute reference to the provide cell, e.g.

'Sheet Name'!$C$3.

This named cell reference can then be used in cell formulas

 =myname*A3

Create a frozen split pane at row, column

snipet

class RubyXL::Worksheet
  def add_frozen_split(row:, column:)
    worksheetview = RubyXL::WorksheetView.new
    worksheetview.pane = RubyXL::Pane.new(:top_left_cell => RubyXL::Reference.new(row,column),
                                          :y_split => row,
                                          :x_split => column,
                                          :state => 'frozenSplit',
                                          :activePane => 'bottomRight')
    worksheetviews = RubyXL::WorksheetViews.new
    worksheetviews << worksheetview
    self.sheet_views = worksheetviews
  end
end

The above snippet adds a convenience method to the Worksheet class to configure a frozen split pane at a given row and column on the respective worksheet. Example use:

 worksheet.add_frozen_split(2,2)

Create a sheet with locked and unlocked cells

snippet

class RubyXL::Cell
  def unlock
    xf = get_cell_xf.dup
    xf.protection = xf.protection&.dup || RubyXL::Protection.new
    xf.protection.locked = false
    xf.apply_protection = true
    self.style_index = workbook.register_new_xf(xf)
  end
end

The above snippet adds an 'unlock' convenience method to a Cell. Cells are 'locked' by default in Excel. First step is to unlock cells that can be edited.

 worksheet[1][1].unlock
 worksheet[1][2].unlock
 ...

Second step is to enable protection for a given sheet. Do it like this:

 worksheet.sheet_protection = RubyXL::WorksheetProtection.new(sheet: true)