OOXL

$ bundle

$ gem install ooxl

ooxl = OOXL.new('example.xlsx')

or

ooxl = OOXL.open('example.xlsx')

ooxl.sheets # ['Test Sheet 1', 'Test Sheet 2']

sheet = ooxl.sheet('Test Sheet 1')

# Rows
sheet.rows[0] # Access the first row
sheet[0] # short version

# Cells
sheet.rows[0].cells # access the cells of the first row
sheet[0].cells # short version

sheet.rows[0].cells[0] # Access the first cell of the row
sheet[0][0] # short version

sheet.rows[0].cells[0].value # Access cell value
sheet[0][0].value# short version

# Fetch cell value using the short versions
ooxl['Test Sheet 1'][0][0].value

# fetching the cell directly
ooxl['Test Sheet 1'].cell('C1')

# Detecting merged cell
ooxl['Test Sheet 1'].in_merged_cells?('C1') # true/false

ooxl.sheet('Test Sheet 1').each do |row|
  row.each_with_index do |cell, cell_index|
    # do something here..
  end
end

ooxl.each do |sheet|
  sheet.each do |row|
    row.each do |cell|
      # do something here...
    end
  end
end

# Fetch all columns
ooxl.sheet('Test Sheet 1').columns

# Checking if the column is hidden
ooxl.sheet('Test Sheet 1').column(1).hidden? # column index
ooxl.sheet('Test Sheet 1').column('A').hidden? # column letter

# Font
font_object = ooxl.sheet('Test Sheet 1').font('A1')
font_object.bold? # false
font_object.name # Arial
font_object.rgb_color # FFE10000
font_object.size # 8

# Cell Fill
fill_object = ooxl.sheet('Test Sheet 1').fill('A1')
fill_object.bg_color # FFE10000
fill_object.fg_color # FFE10000

# named range
ooxl.named_range('my_named_range') # ['value' 'from', 'range']

# cell range
ooxml['Lists'!$A$1:$A$6] # ['1','2','3','4','5','6']

# or
ooxml['Lists'!A1:A6] # ['1','2','3','4','5','6']

# or loading a single value
ooxml['Lists'!A1] # ['1']

# or loading a box type values
ooxml['Lists!A1:B2'] # [['1', '2'], ['2','3']]

# loading all values of a column
ooxml['Lists!A:A'] # ['1', '2', '3', '4', '5', '6']

# All Validations
data_validations = ooxl.sheet('Test Sheet 1').data_validations

# Specific validation for cell
data_validation = ooxml.sheet('Input Sheet').data_validation('D4')

data_validation.prompt # "Sample Validation Message"
data_validation.formula # 20
data_validation.type #textLength