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
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
|
require 'forwardable'
require 'roo/excelx/extractor'
module Roo
class Excelx
class SheetDoc < Excelx::Extractor
extend Forwardable
delegate [:styles, :workbook, :shared_strings, :base_date] => :@shared
def initialize(path, relationships, shared, options = {})
super(path)
@shared = shared
@options = options
@relationships = relationships
end
def cells(relationships)
@cells ||= extract_cells(relationships)
end
def hyperlinks(relationships)
@hyperlinks ||= extract_hyperlinks(relationships)
end
# Get the dimensions for the sheet.
# This is the upper bound of cells that might
# be parsed. (the document may be sparse so cell count is only upper bound)
def dimensions
@dimensions ||= extract_dimensions
end
# Yield each row xml element to caller
def each_row_streaming(&block)
Roo::Utils.each_element(@path, 'row', &block)
end
# Yield each cell as Excelx::Cell to caller for given
# row xml
def each_cell(row_xml)
return [] unless row_xml
row_xml.children.each do |cell_element|
# If you're sure you're not going to need this hyperlinks you can discard it
hyperlinks = unless @options[:no_hyperlinks]
key = ::Roo::Utils.ref_to_key(cell_element['r'])
hyperlinks(@relationships)[key]
end
yield cell_from_xml(cell_element, hyperlinks)
end
end
private
def cell_value_type(type, format)
case type
when 's'.freeze
:shared
when 'b'.freeze
:boolean
when 'str'.freeze
:string
when 'inlineStr'.freeze
:inlinestr
else
Excelx::Format.to_type(format)
end
end
# Internal: Creates a cell based on an XML clell..
#
# cell_xml - a Nokogiri::XML::Element. e.g.
# <c r="A5" s="2">
# <v>22606</v>
# </c>
# hyperlink - a String for the hyperlink for the cell or nil when no
# hyperlink is present.
#
# Examples
#
# cells_from_xml(<Nokogiri::XML::Element>, nil)
# # => <Excelx::Cell::String>
#
# Returns a type of <Excelx::Cell>.
def cell_from_xml(cell_xml, hyperlink)
coordinate = extract_coordinate(cell_xml['r'])
return Excelx::Cell::Empty.new(coordinate) if cell_xml.children.empty?
# NOTE: This is error prone, to_i will silently turn a nil into a 0.
# This works by coincidence because Format[0] is General.
style = cell_xml['s'].to_i
format = styles.style_format(style)
value_type = cell_value_type(cell_xml['t'], format)
formula = nil
cell_xml.children.each do |cell|
case cell.name
when 'is'
content_arr = cell.search('t').map(&:content)
unless content_arr.empty?
return Excelx::Cell.create_cell(:string, content_arr.join(''), formula, style, hyperlink, coordinate)
end
when 'f'
formula = cell.content
when 'v'
return create_cell_from_value(value_type, cell, formula, format, style, hyperlink, base_date, coordinate)
end
end
Excelx::Cell::Empty.new(coordinate)
end
def create_cell_from_value(value_type, cell, formula, format, style, hyperlink, base_date, coordinate)
# NOTE: format.to_s can replace excelx_type as an argument for
# Cell::Time, Cell::DateTime, Cell::Date or Cell::Number, but
# it will break some brittle tests.
excelx_type = [:numeric_or_formula, format.to_s]
# NOTE: There are only a few situations where value != cell.content
# 1. when a sharedString is used. value = sharedString;
# cell.content = id of sharedString
# 2. boolean cells: value = 'TRUE' | 'FALSE'; cell.content = '0' | '1';
# But a boolean cell should use TRUE|FALSE as the formatted value
# and use a Boolean for it's value. Using a Boolean value breaks
# Roo::Base#to_csv.
# 3. formula
case value_type
when :shared
value = shared_strings.use_html?(cell.content.to_i) ? shared_strings.to_html[cell.content.to_i] : shared_strings[cell.content.to_i]
Excelx::Cell.create_cell(:string, value, formula, style, hyperlink, coordinate)
when :boolean, :string
value = cell.content
Excelx::Cell.create_cell(value_type, value, formula, style, hyperlink, coordinate)
when :time, :datetime
cell_content = cell.content.to_f
# NOTE: A date will be a whole number. A time will have be > 1. And
# in general, a datetime will have decimals. But if the cell is
# using a custom format, it's possible to be interpreted incorrectly.
# cell_content.to_i == cell_content && standard_style?=> :date
#
# Should check to see if the format is standard or not. If it's a
# standard format, than it's a date, otherwise, it is a datetime.
# @styles.standard_style?(style_id)
# STANDARD_STYLES.keys.include?(style_id.to_i)
cell_type = if cell_content < 1.0
:time
elsif (cell_content - cell_content.floor).abs > 0.000001
:datetime
else
:date
end
Excelx::Cell.create_cell(cell_type, cell.content, formula, excelx_type, style, hyperlink, base_date, coordinate)
when :date
Excelx::Cell.create_cell(value_type, cell.content, formula, excelx_type, style, hyperlink, base_date, coordinate)
else
Excelx::Cell.create_cell(:number, cell.content, formula, excelx_type, style, hyperlink, coordinate)
end
end
def extract_coordinate(coordinate)
row, column = ::Roo::Utils.split_coordinate(coordinate)
Excelx::Coordinate.new(row, column)
end
def extract_hyperlinks(relationships)
return {} unless (hyperlinks = doc.xpath('/worksheet/hyperlinks/hyperlink'))
Hash[hyperlinks.map do |hyperlink|
if hyperlink.attribute('id') && (relationship = relationships[hyperlink.attribute('id').text])
[::Roo::Utils.ref_to_key(hyperlink.attributes['ref'].to_s), relationship.attribute('Target').text]
end
end.compact]
end
def expand_merged_ranges(cells)
# Extract merged ranges from xml
merges = {}
doc.xpath('/worksheet/mergeCells/mergeCell').each do |mergecell_xml|
tl, br = mergecell_xml['ref'].split(/:/).map { |ref| ::Roo::Utils.ref_to_key(ref) }
for row in tl[0]..br[0] do
for col in tl[1]..br[1] do
next if row == tl[0] && col == tl[1]
merges[[row, col]] = tl
end
end
end
# Duplicate value into all cells in merged range
merges.each do |dst, src|
cells[dst] = cells[src]
end
end
def extract_cells(relationships)
extracted_cells = Hash[doc.xpath('/worksheet/sheetData/row/c').map do |cell_xml|
key = ::Roo::Utils.ref_to_key(cell_xml['r'])
[key, cell_from_xml(cell_xml, hyperlinks(relationships)[key])]
end]
expand_merged_ranges(extracted_cells) if @options[:expand_merged_ranges]
extracted_cells
end
def extract_dimensions
Roo::Utils.each_element(@path, 'dimension') do |dimension|
return dimension.attributes['ref'].value
end
end
end
end
end
|
