Access a cell by name using the `spreadsheet` gem

Question

If I have a sheet variable that is of type Spreadsheet::Excel::Worksheet, right now we access B2 by doing:

MY_CUSTOM_CELL = [1, 1]
sheet[*MY_CUSTOM_CELL] # => Contents of B2

In LibreOffice (And I'm sure Excel), I can assign a name for that cell in the "Name Box". See the image below, I've given B2 a name of "CUSTOM_NAME".

Instead of accessing the contents of that cell using the row/column coordinates, is it possible to access it via the name? This would make it easier to adapt to future change if the cell changes location. I'd like to do something like:

sheet.find("CUSTOM_NAME") # => Contents of B2

I was looking at the documentation found here but was unable to find what I was looking for.

If the gem doesn't allow it already, how would I go about implementing it myself?

score 0 · Answer 1 · answered Jul 06 '16 at 23:09

Here is how to do it with the creek gem, which is much faster than the spreadsheet gem when parsing large xlsx files.

require 'creek'

workbook = Creek::Book.new some_file.xlsx # Change the file name to suit your needs
worksheet = workbook.sheets[0] # Change the worksheet index to suit your needs. 

# Create hash with cell names (e.g., A1, B5) as keys and cell values as values
cells = Hash.new
  worksheet.rows.each do |row|
    cells.merge!(row)
  end
end

# To access the cell values, just use the corresponding hash keys

# Set value for the A1 cell
cells["A1"] = "foo"

# Print value of C3 cell
puts cells["C3"]

score 0 · Accepted Answer · answered Jul 11 '16 at 13:41

Although the other answer may be a good alternative (I have not personally unverified), the answer to this question is no.

Spreadsheet Issue - Feature - Retrieve a cell by its name, not just row and column

There currently is no way to retrieve a cell by its name using the spreadsheet gem.

Access a cell by name using the `spreadsheet` gem

2 Answers2