Set "Fixed display of rows and columns" and "Validation rules" in rubyXL

code

require 'rubyXL'
require 'rubyXL/convenience_methods'

workbook = RubyXL::Workbook.new
sheet = workbook.first

sheet.add_cell(0, 0, 'ID')
sheet.add_cell(0, 1, 'name')
sheet.add_cell(0, 2, 'State')
sheet.add_cell(1, 0, 1)
sheet.add_cell(1, 1, 'foo')
sheet.add_cell(1, 2, 'Already')
sheet.add_cell(2, 0, 2)
sheet.add_cell(2, 1, 'bar')
sheet.add_cell(2, 2, 'Not yet')

#Fixed display of 1 row from the top and 2 columns from the left
view = RubyXL::WorksheetView.new
view.pane = RubyXL::Pane.new(
  top_left_cell: RubyXL::Reference.new(1, 2),
  y_split: 1,
  x_split: 2,
  state: 'frozenSplit',
  activePane: 'bottomRight'
)
views = RubyXL::WorksheetViews.new
views << view
sheet.sheet_views = views

#Set validation rules
formula = RubyXL::Formula.new(expression: "\"Not yet,Already\"")
range = RubyXL::Reference.new(1, 1048575, 2, 2) #Range to set validation rules. R2C3:R1048576C3
validation = RubyXL::DataValidation.new(
  sqref: range,
  formula1: formula,
  type: 'list',             #Other none, whole, decimal, date, time, textLength,custom. The default is none(All values)
  error_style: 'stop',      #Other warning,information. The default is stop(Stop)
  allow_blank: true,        #"Ignore blanks" is on when true. Default is false
  show_error_message: true, #If true, "Display error message when invalid data is entered" is on. Default is false
  show_drop_down: false     #For some reason, "Select from drop-down list" is turned on with false. Default is false
)
validations = RubyXL::DataValidations.new
validations << validation
sheet.data_validations = validations

workbook.write('out.xlsx')

Note that the true/false of show_drop_down is semantically opposite for some reason.

result

The image is the result of Microsoft Excel for Mac.

reference

-rubyXL official wiki

• https://github.com/weshatheleopard/rubyXL/issues/224#issuecomment-287355611
• http://www.datypic.com/sc/ooxml/e-ssml_dataValidation-1.html --You can also read here how to set other items (such as messages when an error occurs).