Class: BerkeleyLibrary::Util::XLSX::Spreadsheet

Inherits:
Object
  • Object
show all
Defined in:
lib/berkeley_library/util/xlsx/spreadsheet.rb

Overview

Convenience wrapper RubyXL::Workbook

Constant Summary collapse

MIME_TYPE_OOXML_WB =

.xlsx format, a.k.a. “Office Open XML Workbook” spreadsheet

'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'.freeze
RE_EXCEL_WORKSHEET_ZIP_ENTRY =

path to Excel worksheet file in zipped OOXML archive

%r{^xl/worksheets/[^/.]+\.xml$}
DEFAULT_WORKSHEET_NAME = 
'Sheet1'.freeze

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(xlsx_path = nil) ⇒ Spreadsheet

Returns a new instance of Spreadsheet.



26
27
28
29
 
# File 'lib/berkeley_library/util/xlsx/spreadsheet.rb', line 26

def initialize(xlsx_path = nil)
  @workbook = xlsx_path ? ensure_xlsx_workbook!(xlsx_path) : RubyXL::Workbook.new
  @xlsx_path = xlsx_path
end

Instance Attribute Details

#workbook ⇒ Object (readonly)

Returns the value of attribute workbook.



22
23
24
 
# File 'lib/berkeley_library/util/xlsx/spreadsheet.rb', line 22

def workbook
  @workbook
end

#xlsx_path ⇒ Object (readonly)

Returns the value of attribute xlsx_path.



22
23
24
 
# File 'lib/berkeley_library/util/xlsx/spreadsheet.rb', line 22

def xlsx_path
  @xlsx_path
end

Instance Method Details

#cell_at(r_index, c_index) ⇒ Object 



75
76
77
78
79
 
# File 'lib/berkeley_library/util/xlsx/spreadsheet.rb', line 75

def cell_at(r_index, c_index)
  return unless (row = worksheet[r_index])

  row[c_index]
end

#column_count(r_index = nil) ⇒ Object 



103
104
105
106
107
108
109
110
111
 
# File 'lib/berkeley_library/util/xlsx/spreadsheet.rb', line 103

def column_count(r_index = nil)
  if r_index
    return (row = worksheet[r_index]) ? row.size : 0
  end

  rows.inject(0) do |cc_max, r|
    r ? [r.size, cc_max].max : cc_max
  end
end

#each_value(c_index, include_header: true) ⇒ Object 



66
67
68
69
70
71
72
73
 
# File 'lib/berkeley_library/util/xlsx/spreadsheet.rb', line 66

def each_value(c_index, include_header: true)
  return to_enum(:each_value, c_index, include_header:) unless block_given?

  start_index = include_header ? 0 : 1
  (start_index...row_count).each do |r_index|
    yield value_at(r_index, c_index)
  end
end

#ensure_column!(header) ⇒ Object 



113
114
115
116
117
118
119
 
# File 'lib/berkeley_library/util/xlsx/spreadsheet.rb', line 113

def ensure_column!(header)
  c_index_existing = find_column_index_by_header(header)
  return c_index_existing if c_index_existing

  c_index_next = worksheet.first_blank_column_index
  c_index_next.tap { |cc| worksheet.add_cell(0, cc, header) }
end

#find_column_index(row, *args) ⇒ Object 



55
56
57
58
59
60
61
62
63
64
 
# File 'lib/berkeley_library/util/xlsx/spreadsheet.rb', line 55

def find_column_index(row, *args)
  case args.size
  when 0
    (0...row.size).find { |c_index| yield row[c_index] }
  when 1
    find_column_index(row) { |cell| cell&.value == args[0] }
  else
    raise ArgumentError, "Wrong number of arguments (given #{args.size}, expected 0..1"
  end
end

#find_column_index_by_header(header) ⇒ Object 



44
45
46
 
# File 'lib/berkeley_library/util/xlsx/spreadsheet.rb', line 44

def find_column_index_by_header(header)
  find_column_index(header_row, header)
end

#find_column_index_by_header!(header) ⇒ Object

Raises:

  • (ArgumentError) 


48
49
50
51
52
53
 
# File 'lib/berkeley_library/util/xlsx/spreadsheet.rb', line 48

def find_column_index_by_header!(header)
  c_index = find_column_index_by_header(header)
  return c_index if c_index

  raise ArgumentError, "#{header.inspect} column not found"
end

#header_row ⇒ Object 



40
41
42
 
# File 'lib/berkeley_library/util/xlsx/spreadsheet.rb', line 40

def header_row
  @header_row ||= (hr = worksheet[0]) ? hr : worksheet.add_row
end

#row_count ⇒ Object 



99
100
101
 
# File 'lib/berkeley_library/util/xlsx/spreadsheet.rb', line 99

def row_count
  sheet_data.size
end

#rows ⇒ Object 



95
96
97
 
# File 'lib/berkeley_library/util/xlsx/spreadsheet.rb', line 95

def rows
  sheet_data.rows
end

#save_as(new_xlsx_path) ⇒ Object 



31
32
33
34
 
# File 'lib/berkeley_library/util/xlsx/spreadsheet.rb', line 31

def save_as(new_xlsx_path)
  workbook.write(new_xlsx_path)
  @xlsx_path = new_xlsx_path
end

#set_value_at(r_index, c_index, value) ⇒ Object 



87
88
89
90
91
92
93
 
# File 'lib/berkeley_library/util/xlsx/spreadsheet.rb', line 87

def set_value_at(r_index, c_index, value)
  if (cell = cell_at(r_index, c_index))
    cell.change_contents(value)
  else
    worksheet.add_cell(r_index, c_index, value)
  end
end

#value_at(r_index, c_index) ⇒ Object 



81
82
83
84
85
 
# File 'lib/berkeley_library/util/xlsx/spreadsheet.rb', line 81

def value_at(r_index, c_index)
  return unless (cell = cell_at(r_index, c_index))

  cell.value
end

#worksheet ⇒ Object 



36
37
38
 
# File 'lib/berkeley_library/util/xlsx/spreadsheet.rb', line 36

def worksheet
  @worksheet ||= workbook.worksheets[0]
end