Class: LiquidXlsx::Worksheet

Inherits:
Object
  • Object
show all
Defined in:
lib/liquid_xlsx/worksheet.rb

Overview

Represents and processes a single worksheet.

Constant Summary collapse

CT_WORKSHEET_ORDER =

Element order of CT_Worksheet children per the OOXML schema.

%w[
  sheetPr dimension sheetViews sheetFormatPr cols sheetData sheetCalcPr
  sheetProtection protectedRanges scenarios autoFilter sortState
  dataConsolidate customSheetViews mergeCells phoneticPr
  conditionalFormatting dataValidations hyperlinks printOptions pageMargins
  pageSetup headerFooter rowBreaks colBreaks customProperties cellWatches
  ignoredErrors smartTags drawing drawingHF picture oleObjects controls
  webPublishItems tableParts extLst
].freeze
EXCEL_MAX_CELL_LENGTH =

Excel's hard limit on cell text length.

32_767
ILLEGAL_XML_CHARS =

Characters invalid in XML 1.0 (except TAB, LF, CR which are legal).

/[\x00-\x08\x0B\x0C\x0E-\x1F]/

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(xml, name, shared_strings) ⇒ Worksheet

Returns a new instance of Worksheet.



51
52
53
54
55
56
 
# File 'lib/liquid_xlsx/worksheet.rb', line 51

def initialize(xml, name, shared_strings)
  @original_xml = xml
  @name = name
  @shared_strings = shared_strings
  @doc = Nokogiri::XML(xml)
end

Instance Attribute Details

#name ⇒ Object (readonly)

Returns the value of attribute name.



6
7
8
 
# File 'lib/liquid_xlsx/worksheet.rb', line 6

def name
  @name
end

#shared_strings ⇒ Object (readonly)

Returns the value of attribute shared_strings.



6
7
8
 
# File 'lib/liquid_xlsx/worksheet.rb', line 6

def shared_strings
  @shared_strings
end

#sheet_xml ⇒ Object (readonly)

Returns the value of attribute sheet_xml.



6
7
8
 
# File 'lib/liquid_xlsx/worksheet.rb', line 6

def sheet_xml
  @sheet_xml
end

Class Method Details

.insert_child_in_order(root, node) ⇒ Object

Insert a child element into a worksheet root at its schema-mandated position (before the first existing element that must come after it).



41
42
43
44
45
46
47
48
49
 
# File 'lib/liquid_xlsx/worksheet.rb', line 41

def self.insert_child_in_order(root, node)
  idx = CT_WORKSHEET_ORDER.index(node.name)
  anchor = idx && root.element_children.find do |child|
    ci = CT_WORKSHEET_ORDER.index(child.name)
    ci && ci > idx
  end
  anchor ? anchor.add_previous_sibling(node) : root.add_child(node)
  node
end

.sanitize_text(value) ⇒ Object

Make a string safe to embed in worksheet XML: valid UTF-8, no illegal control characters, within Excel's cell length limit.



27
28
29
30
31
32
33
34
35
36
37
 
# File 'lib/liquid_xlsx/worksheet.rb', line 27

def self.sanitize_text(value)
  s = value.to_s
  s = if s.encoding == Encoding::UTF_8
        s.valid_encoding? ? s : s.scrub("\u{FFFD}")
      else
        s.encode(Encoding::UTF_8, invalid: :replace, undef: :replace, replace: "\u{FFFD}")
      end
  s = s.gsub(ILLEGAL_XML_CHARS, "")
  s = s[0, EXCEL_MAX_CELL_LENGTH] if s.length > EXCEL_MAX_CELL_LENGTH
  s
end

Instance Method Details

#add_inline_str(c, value) ⇒ Object

Append ... to a cell element.



217
218
219
220
221
222
 
# File 'lib/liquid_xlsx/worksheet.rb', line 217

def add_inline_str(c, value)
  c["t"] = "inlineStr"
  is_el = Nokogiri::XML::Node.new("is", @doc)
  c.add_child(is_el)
  is_el.add_child(build_inline_text(value))
end

#apply_row_attributes(row_elem, attrs) ⇒ Object

Apply preserved row-level attributes to a rebuilt row element.



322
323
324
325
326
327
328
329
330
331
332
333
334
 
# File 'lib/liquid_xlsx/worksheet.rb', line 322

def apply_row_attributes(row_elem, attrs)
  return unless attrs

  row_elem["s"] = attrs[:s] if attrs[:s]
  row_elem["ht"] = attrs[:ht] if attrs[:ht]
  row_elem["customHeight"] = attrs[:customHeight] if attrs[:customHeight]
  row_elem["hidden"] = attrs[:hidden] if attrs[:hidden]
  row_elem["outlineLevel"] = attrs[:outlineLevel] if attrs[:outlineLevel]
  row_elem["customFormat"] = attrs[:customFormat] if attrs[:customFormat]
  row_elem["spans"] = attrs[:spans] if attrs[:spans]
  row_elem["thickTop"] = attrs[:thickTop] if attrs[:thickTop]
  row_elem["thickBot"] = attrs[:thickBot] if attrs[:thickBot]
end

#build_cell(cell_data, current_row_num) ⇒ Object

Build a single element for a rendered cell.



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
212
213
214
 
# File 'lib/liquid_xlsx/worksheet.rb', line 173

def build_cell(cell_data, current_row_num)
  c = Nokogiri::XML::Node.new("c", @doc)
  c["r"] = "#{cell_data[:col]}#{current_row_num}"
  c["s"] = cell_data[:style].to_s if cell_data[:style]

  if cell_data[:formula]
    # Only override type if original explicitly had t="str" (not for numeric formulas)
    c["t"] = "str" if cell_data[:original_type] == "str"
    f = Nokogiri::XML::Node.new("f", @doc)
    (cell_data[:formula_attrs] || {}).each { |k, val| f[k] = val if val }
    f.content = cell_data[:formula]
    c.add_child(f)
  elsif cell_data[:inline_str]
    add_inline_str(c, cell_data[:rendered_value])
  elsif cell_data[:type] == "s" && cell_data[:text]
    # Shared string reference — keep as is
    c["t"] = "s"
    v_el = Nokogiri::XML::Node.new("v", @doc)
    v_el.content = cell_data[:value].to_s if cell_data[:value]
    c.add_child(v_el)
  elsif cell_data[:rendered_value].is_a?(Numeric)
    v_el = Nokogiri::XML::Node.new("v", @doc)
    v_el.content = cell_data[:rendered_value].to_s
    c.add_child(v_el)
  elsif cell_data[:rendered_value].is_a?(TrueClass) || cell_data[:rendered_value].is_a?(FalseClass)
    c["t"] = "b"
    v_el = Nokogiri::XML::Node.new("v", @doc)
    v_el.content = cell_data[:rendered_value] ? "1" : "0"
    c.add_child(v_el)
  elsif cell_data[:original_value] && !%w[s inlineStr].include?(cell_data[:type])
    # Non-template value cell (numeric, boolean, raw) — preserve as-is
    c["t"] = cell_data[:type] if cell_data[:type]
    v_el = Nokogiri::XML::Node.new("v", @doc)
    v_el.content = cell_data[:original_value].to_s
    c.add_child(v_el)
  else
    # String — use inlineStr
    add_inline_str(c, cell_data[:rendered_value])
  end

  c
end

#build_inline_text(value) ⇒ Object

Build a sanitized element for inline strings, preserving significant whitespace via xml:space when needed.



258
259
260
261
262
263
264
 
# File 'lib/liquid_xlsx/worksheet.rb', line 258

def build_inline_text(value)
  t_el = Nokogiri::XML::Node.new("t", @doc)
  text = self.class.sanitize_text(value)
  t_el.content = text
  t_el["xml:space"] = "preserve" if text.match?(/\A\s|\s\z/) || text.include?("\n")
  t_el
end

#merge_cells ⇒ Array<Hash>

Get merge cells from the worksheet.

Returns:

  • (Array<Hash>) —

    each: "A3:B3", start_row:, end_row:



107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
 
# File 'lib/liquid_xlsx/worksheet.rb', line 107

def merge_cells
  return [] unless merge_elem

  merge_elem["count"]&.to_i
  merge_elem.xpath("xmlns:mergeCell").map do |mc|
    ref = mc["ref"]
    parts = ref.split(":")
    start = CellReference.new(parts[0])
    end_ref = CellReference.new(parts[1] || parts[0])
    {
      ref: ref,
      start_row: start.row,
      end_row: end_ref.row
    }
  end
end

#merge_elem ⇒ Nokogiri::XML::Element?

Get the mergeCells element.

Returns:

  • (Nokogiri::XML::Element, nil) 


126
127
128
129
130
131
 
# File 'lib/liquid_xlsx/worksheet.rb', line 126

def merge_elem
  ns = @doc.root&.namespace
  return nil unless ns

  @doc.at_xpath("//xmlns:mergeCells", "xmlns" => ns.href)
end

#parse_rows ⇒ Array<Hash>

Parse rows from the worksheet.

Spreadsheet applications omit empty rows from sheetData, so row numbers come with gaps. Those gaps are not nothing: an empty row still takes up vertical space on the sheet, and templates use them as spacers. Since the renderer renumbers output rows contiguously, the gaps are materialized here as empty placeholder rows — otherwise every spacer collapses and the whole document creeps upwards.

Returns:

  • (Array<Hash>) —

    rows with :row_number, :row_xml, :cells, :row_attrs



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
 
# File 'lib/liquid_xlsx/worksheet.rb', line 68

def parse_rows
  rows = []
  ns = @doc.root&.namespace
  return rows unless ns

  collect_shared_formula_masters(ns)

  last_row_num = 0
  @doc.xpath("//xmlns:sheetData/xmlns:row", "xmlns" => ns.href).each do |row_elem|
    row_num = row_elem["r"]&.to_i
    row_num = last_row_num + 1 if row_num.nil? || row_num < 1

    ((last_row_num + 1)...row_num).each { |missing| rows << placeholder_row(missing) }

    last_row_num = row_num
    cells = parse_cells(row_elem, row_num)
    rows << {
      row_number: row_num,
      row_xml: row_elem.to_xml(indent: 0),
      cells: cells,
      row_attrs: row_attributes(row_elem)
    }
  end
  rows
end

#placeholder_row(row_num) ⇒ Object

An empty row omitted from sheetData. No attributes — the sheet's defaultRowHeight applies, exactly as the spreadsheet application shows it.



96
97
98
99
100
101
102
103
 
# File 'lib/liquid_xlsx/worksheet.rb', line 96

def placeholder_row(row_num)
  {
    row_number: row_num,
    row_xml: nil,
    cells: [],
    row_attrs: {}
  }
end

#rebuild_sheet_data(rendered_rows, start_row = 1) ⇒ Object

Rebuild sheetData with rendered rows.

Parameters:

  • rendered_rows (Array<Hash>) —

    array of original_row_num:, row_attrs:

  • start_row (Integer) (defaults to: 1) —

    first row number in the output



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
 
# File 'lib/liquid_xlsx/worksheet.rb', line 136

def rebuild_sheet_data(rendered_rows, start_row = 1)
  ns = @doc.root&.namespace
  return unless ns

  sheet_data = @doc.at_xpath("//xmlns:sheetData", "xmlns" => ns.href)
  unless sheet_data
    sheet_data = Nokogiri::XML::Node.new("sheetData", @doc)
    @doc.root.add_child(sheet_data)
  end

  # Remove all existing rows
  sheet_data.xpath("xmlns:row").each(&:remove)

  current_row_num = start_row
  sheet_data.namespace

  rendered_rows.each do |row_info|
    next if row_info.nil?

    row_elem = Nokogiri::XML::Node.new("row", @doc)
    row_elem["r"] = current_row_num.to_s

    # Restore original row attributes (height, hidden, etc.)
    apply_row_attributes(row_elem, row_info[:row_attrs])

    (row_info[:cells] || []).each do |cell_data|
      next unless cell_data

      row_elem.add_child(build_cell(cell_data, current_row_num))
    end

    sheet_data.add_child(row_elem)
    current_row_num += 1
  end
end

#remove_cached_formula_values ⇒ Object

Remove calc chain cached values from formula cells.



292
293
294
295
296
297
298
 
# File 'lib/liquid_xlsx/worksheet.rb', line 292

def remove_cached_formula_values
  @doc.xpath("//xmlns:f").each do |f|
    # Remove sibling <v> elements
    parent = f.parent
    parent.xpath("xmlns:v").each(&:remove)
  end
end

#row_attributes(row_elem) ⇒ Object

Extract row-level attributes to preserve.



307
308
309
310
311
312
313
314
315
316
317
318
319
 
# File 'lib/liquid_xlsx/worksheet.rb', line 307

def row_attributes(row_elem)
  {
    s: row_elem["s"],
    ht: row_elem["ht"],
    customHeight: row_elem["customHeight"],
    hidden: row_elem["hidden"],
    outlineLevel: row_elem["outlineLevel"],
    customFormat: row_elem["customFormat"],
    spans: row_elem["spans"],
    thickTop: row_elem["thickTop"],
    thickBot: row_elem["thickBot"]
  }
end

#to_xml ⇒ String

Serialize back to XML string.

Returns:

  • (String) 


302
303
304
 
# File 'lib/liquid_xlsx/worksheet.rb', line 302

def to_xml
  @doc.to_xml(indent: 0, encoding: "UTF-8")
end

#update_dimension(total_rows) ⇒ Object

Update the dimension element.

Parameters:

  • total_rows (Integer) 


226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
 
# File 'lib/liquid_xlsx/worksheet.rb', line 226

def update_dimension(total_rows)
  ns = @doc.root&.namespace
  return unless ns

  dim = @doc.at_xpath("//xmlns:dimension", "xmlns" => ns.href)
  unless dim
    dim = Nokogiri::XML::Node.new("dimension", @doc)
    @doc.root.children.first&.add_previous_sibling(dim)
  end

  # Find max column using column index (not string comparison)
  max_col = "A"
  max_col_idx = 0
  @doc.xpath("//xmlns:c").each do |c|
    ref = c["r"]
    col = ref&.gsub(/\d+/, "")
    next unless col

    col_idx = CellReference.col_to_index(col)
    if col_idx > max_col_idx
      max_col_idx = col_idx
      max_col = col
    end
  end

  total_rows = 1 if total_rows < 1
  dim["ref"] = "A1:#{max_col}#{total_rows}"
  dim
end

#update_merge_cells(merge_ranges) ⇒ Object

Update merged cells with new ranges.

Parameters:

  • merge_ranges (Array<String>) —

    e.g. ["A3:B3", "A4:B4"]



268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
 
# File 'lib/liquid_xlsx/worksheet.rb', line 268

def update_merge_cells(merge_ranges)
  ns = @doc.root&.namespace
  return unless ns

  # Remove existing mergeCells
  existing = @doc.at_xpath("//xmlns:mergeCells", "xmlns" => ns.href)
  existing&.remove

  return if merge_ranges.empty?

  mc_elem = Nokogiri::XML::Node.new("mergeCells", @doc)
  mc_elem["count"] = merge_ranges.length.to_s

  merge_ranges.each do |ref|
    mc = Nokogiri::XML::Node.new("mergeCell", @doc)
    mc["ref"] = ref
    mc_elem.add_child(mc)
  end

  # Insert at the schema-mandated position within CT_Worksheet
  self.class.insert_child_in_order(@doc.root, mc_elem)
end