1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
|
require 'forwardable'
module Roo
class Excelx
class Sheet
extend Forwardable
delegate [:styles, :workbook, :shared_strings, :rels_files, :sheet_files, :comments_files] => :@shared
def initialize(name, shared, sheet_index, options = {})
@name = name
@shared = shared
@rels = Relationships.new(rels_files[sheet_index])
@comments = Comments.new(comments_files[sheet_index])
@sheet = SheetDoc.new(sheet_files[sheet_index], @rels, shared, options)
end
def cells
@cells ||= @sheet.cells(@rels)
end
def present_cells
@present_cells ||= cells.select { |_, cell| cell && !cell.empty? }
end
# Yield each row as array of Excelx::Cell objects
# accepts options max_rows (int) (offset by 1 for header),
# pad_cells (boolean) and offset (int)
def each_row(options = {}, &block)
row_count = 0
options[:offset] ||= 0
@sheet.each_row_streaming do |row|
break if options[:max_rows] && row_count == options[:max_rows] + options[:offset] + 1
if block_given? && !(options[:offset] && row_count < options[:offset])
block.call(cells_for_row_element(row, options))
end
row_count += 1
end
end
def row(row_number)
first_column.upto(last_column).map do |col|
cells[[row_number, col]]
end.map { |cell| cell && cell.value }
end
def column(col_number)
first_row.upto(last_row).map do |row|
cells[[row, col_number]]
end.map { |cell| cell && cell.value }
end
# returns the number of the first non-empty row
def first_row
@first_row ||= present_cells.keys.map { |row, _| row }.min
end
def last_row
@last_row ||= present_cells.keys.map { |row, _| row }.max
end
# returns the number of the first non-empty column
def first_column
@first_column ||= present_cells.keys.map { |_, col| col }.min
end
# returns the number of the last non-empty column
def last_column
@last_column ||= present_cells.keys.map { |_, col| col }.max
end
def excelx_format(key)
cell = cells[key]
styles.style_format(cell.style).to_s if cell
end
def hyperlinks
@hyperlinks ||= @sheet.hyperlinks(@rels)
end
def comments
@comments.comments
end
def dimensions
@sheet.dimensions
end
private
# Take an xml row and return an array of Excelx::Cell objects
# optionally pad array to header width(assumed 1st row).
# takes option pad_cells (boolean) defaults false
def cells_for_row_element(row_element, options = {})
return [] unless row_element
cell_col = 0
cells = []
@sheet.each_cell(row_element) do |cell|
cells.concat(pad_cells(cell, cell_col)) if options[:pad_cells]
cells << cell
cell_col = cell.coordinate.column
end
cells
end
def pad_cells(cell, last_column)
pad = []
(cell.coordinate.column - 1 - last_column).times { pad << nil }
pad
end
end
end
end
|
