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.



195
196
197
198
199
200
 
# File 'lib/liquid_xlsx/worksheet.rb', line 195

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.



300
301
302
303
304
305
306
307
308
309
310
311
312
 
# File 'lib/liquid_xlsx/worksheet.rb', line 300

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.



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

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.



236
237
238
239
240
241
242
 
# File 'lib/liquid_xlsx/worksheet.rb', line 236

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:



85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
 
# File 'lib/liquid_xlsx/worksheet.rb', line 85

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) 


104
105
106
107
108
109
 
# File 'lib/liquid_xlsx/worksheet.rb', line 104

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.

Returns:

  • (Array<Hash>) —

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



60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
 
# File 'lib/liquid_xlsx/worksheet.rb', line 60

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 = 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

#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



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

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.



270
271
272
273
274
275
276
 
# File 'lib/liquid_xlsx/worksheet.rb', line 270

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.



285
286
287
288
289
290
291
292
293
294
295
296
297
 
# File 'lib/liquid_xlsx/worksheet.rb', line 285

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) 


280
281
282
 
# File 'lib/liquid_xlsx/worksheet.rb', line 280

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) 


204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
 
# File 'lib/liquid_xlsx/worksheet.rb', line 204

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"]



246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
 
# File 'lib/liquid_xlsx/worksheet.rb', line 246

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