Class: Xlsxrb::Ooxml::WorksheetWriter

Inherits:
Object
  • Object
show all
Defined in:
lib/xlsxrb/ooxml/worksheet_writer.rb

Overview

Generates worksheet XML for a list of rows. Supports streaming: rows can be written one at a time.

Constant Summary collapse

SSML_NS = 
"http://schemas.openxmlformats.org/spreadsheetml/2006/main"
DOC_REL_NS = 
"http://schemas.openxmlformats.org/officeDocument/2006/relationships"

Instance Method Summary collapse

Constructor Details

#initialize(io) ⇒ WorksheetWriter

Returns a new instance of WorksheetWriter.



14
15
16
17
18
19
 
# File 'lib/xlsxrb/ooxml/worksheet_writer.rb', line 14

def initialize(io)
  @io = io
  @builder = XmlBuilder.new(@io)
  @started = false
  @finished = false
end

Instance Method Details

#finish(drawing_rid: nil, sheet_protection: nil, auto_filter: nil, filter_columns: nil, sort_state: nil, merge_cells: nil, conditional_formats: nil, data_validations: nil, hyperlinks: nil, print_options: nil, page_margins: nil, page_setup: nil, header_footer: nil, row_breaks: nil, col_breaks: nil, tables: nil, table_start_rid: nil, legacy_drawing_rid: nil, sparkline_groups: nil) ⇒ Object

Write the worksheet footer. Call once after all rows. Options for post-sheetData elements (in OOXML order):



388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
 
# File 'lib/xlsxrb/ooxml/worksheet_writer.rb', line 388

def finish(drawing_rid: nil, sheet_protection: nil, auto_filter: nil,
           filter_columns: nil, sort_state: nil, merge_cells: nil,
           conditional_formats: nil, data_validations: nil,
           hyperlinks: nil, print_options: nil, page_margins: nil,
           page_setup: nil, header_footer: nil, row_breaks: nil,
           col_breaks: nil, tables: nil, table_start_rid: nil,
           legacy_drawing_rid: nil, sparkline_groups: nil)
  return if @finished

  start unless @started
  @finished = true
  @builder.close_tag("sheetData")

  # Elements must appear in OOXML specification order after sheetData
  write_sheet_protection(sheet_protection) if sheet_protection
  write_auto_filter(auto_filter, filter_columns, sort_state) if auto_filter
  write_merge_cells(merge_cells) if merge_cells && !merge_cells.empty?
  write_conditional_formatting(conditional_formats) if conditional_formats && !conditional_formats.empty?
  write_data_validations(data_validations) if data_validations && !data_validations.empty?
  write_hyperlinks(hyperlinks) if hyperlinks && !hyperlinks.empty?
  write_print_options(print_options) if print_options && !print_options.empty?
  write_page_margins(page_margins) if page_margins
  write_page_setup(page_setup) if page_setup && !page_setup.empty?
  write_header_footer(header_footer) if header_footer && !header_footer.empty?
  write_row_breaks(row_breaks) if row_breaks && !row_breaks.empty?
  write_col_breaks(col_breaks) if col_breaks && !col_breaks.empty?
  @builder.empty_tag("drawing", { "r:id": drawing_rid }) if drawing_rid
  @builder.empty_tag("legacyDrawing", { "r:id": legacy_drawing_rid }) if legacy_drawing_rid
  write_table_parts(tables, table_start_rid) if tables && !tables.empty?
  write_sparklines(sparkline_groups) if sparkline_groups && !sparkline_groups.empty?
  @builder.close_tag("worksheet")
end

#start(columns: [], sheet_properties: nil, freeze_pane: nil, split_pane: nil, selection: nil, sheet_view: nil) ⇒ Object

Write the worksheet header. Call once before writing rows. Options for pre-sheetData elements:

sheet_properties: Hash of sheet-level properties (:tab_color, etc.)
freeze_pane: { row:, col:, state: :frozen }
split_pane: { x_split:, y_split:, top_left_cell: }
selection: { active_cell:, sqref:, pane: }
sheet_view: Hash of sheet view properties


28
29
30
31
32
33
34
35
36
37
38
39
40
 
# File 'lib/xlsxrb/ooxml/worksheet_writer.rb', line 28

def start(columns: [], sheet_properties: nil, freeze_pane: nil, split_pane: nil, selection: nil, sheet_view: nil)
  return if @started

  @started = true
  @builder.declaration
  @builder.open_tag("worksheet", { xmlns: SSML_NS, "xmlns:r": DOC_REL_NS })

  write_sheet_properties(sheet_properties) if sheet_properties && !sheet_properties.empty?
  write_sheet_views(freeze_pane: freeze_pane, split_pane: split_pane, selection: selection, sheet_view: sheet_view) if freeze_pane || split_pane || selection || (sheet_view && !sheet_view.empty?)
  write_columns(columns) unless columns.empty?

  @builder.open_tag("sheetData")
end

#write_row(row_index, cells, attrs: {}, unmapped: [], sst_index: nil) ⇒ Object

Write a single row. Automatically calls start if needed.



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
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
 
# File 'lib/xlsxrb/ooxml/worksheet_writer.rb', line 43

def write_row(row_index, cells, attrs: {}, unmapped: [], sst_index: nil)
  start unless @started

  row_num = row_index + 1
  row_num_str = row_num.to_s
  io = @io
  io.write("<row r=\"")
  io.write(row_num_str)
  io.write('"')
  if attrs[:height]
    io.write(' ht="')
    io.write(attrs[:height].to_s)
    io.write('" customHeight="1"')
  elsif attrs[:custom_height]
    io.write(' customHeight="1"')
  end
  io.write(' hidden="1"') if attrs[:hidden]
  if attrs[:outline_level]
    io.write(' outlineLevel="')
    io.write(attrs[:outline_level].to_s)
    io.write('"')
  end
  io.write(">")

  cells.each do |cell|
    if cell.is_a?(Hash)
      value = cell[:value]
      style_id = cell[:style_index]
      col_ref = cell[:ref] || "#{column_letter(cell[:column_index])}#{row_num_str}"
      formula = cell[:formula]
      formula_ca = cell[:formula_ca]
      cell_type_val = cell[:type]
    else
      value = cell.value
      style_id = cell.style_index
      col_ref = cell.ref || "#{column_letter(cell.column_index)}#{row_num_str}"
      formula = cell.formula
      formula_ca = false
      cell_type_val = nil
    end

    if value.nil? && formula.nil?
      io.write('<c r="')
      io.write(col_ref)
      io.write('"')
      if style_id
        io.write(' s="')
        io.write(style_id.to_s)
        io.write('"')
      end
      io.write("/>")
      next
    end

    if value.is_a?(String) && value.start_with?("=") && !formula
      formula = value
      value = nil
    end

    xml_val = value
    type = cell_type_val
    formula_expr = nil

    if formula
      if formula.is_a?(Xlsxrb::Elements::Formula)
        formula_expr = formula.expression
        formula_ca = formula.calculate_always
        xml_val = formula.cached_value || nil
      else
        formula_expr = formula
      end
      formula_expr = formula_expr[1..] if formula_expr.start_with?("=")
    end

    if !formula_expr && !type
      case value
      when String
        if sst_index && (idx = sst_index[value])
          xml_val = idx
          type = "s"
        else
          type = "inlineStr"
        end
      when Xlsxrb::Elements::RichText
        if sst_index && (idx = sst_index[value])
          xml_val = idx
          type = "s"
        else
          type = "inlineStr"
          xml_val = value
        end
      when true
        xml_val = "1"
        type = "b"
      when false
        xml_val = "0"
        type = "b"
      when Date
        xml_val = Xlsxrb::Ooxml::Utils.date_to_serial(value)
      when Time
        xml_val = Xlsxrb::Ooxml::Utils.datetime_to_serial(value)
      end
    end

    io.write('<c r="')
    io.write(col_ref)
    io.write('"')
    if style_id
      io.write(' s="')
      io.write(style_id.to_s)
      io.write('"')
    end
    if type
      io.write(' t="')
      io.write(type)
      io.write('"')
    end

    if type == "inlineStr"
      if xml_val.is_a?(Xlsxrb::Elements::RichText)
        io.write("><is>")
        xml_val.runs.each do |run|
          font = run[:font]
          if font && !font.empty?
            io.write("<r><rPr>")
            io.write("<b/>") if font[:bold]
            io.write("<i/>") if font[:italic]
            io.write("<strike/>") if font[:strike]
            if font[:underline]
              if font[:underline] == true
                io.write("<u/>")
              else
                io.write("<u val=\"#{font[:underline]}\"/>")
              end
            end
            io.write("<vertAlign val=\"#{font[:vert_align]}\"/>") if font[:vert_align]
            io.write("<sz val=\"#{font[:sz]}\"/>") if font[:sz]
            if font[:color]
              io.write("<color rgb=\"#{font[:color]}\"/>")
            elsif font[:theme]
              tint_attr = font[:tint] ? " tint=\"#{font[:tint]}\"" : ""
              io.write("<color theme=\"#{font[:theme]}\"#{tint_attr}/>")
            end
            io.write("<rFont val=\"#{escape_xml(font[:name])}\"/>") if font[:name]
            io.write("<family val=\"#{font[:family]}\"/>") if font[:family]
            io.write("<scheme val=\"#{font[:scheme]}\"/>") if font[:scheme]
            io.write("</rPr><t>")
          else
            io.write("<r><t>")
          end
          io.write(escape_xml(run[:text]))
          io.write("</t></r>")
        end
        io.write("</is></c>")
      else
        io.write("><is><t>")
        io.write(escape_xml(xml_val.to_s))
        io.write("</t></is></c>")
      end
    elsif formula_expr
      if formula_ca
        io.write('><f ca="1">')
      else
        io.write("><f>")
      end
      io.write(escape_xml(formula_expr))
      io.write("</f>")
      if xml_val
        io.write("<v>")
        io.write(xml_val.to_s)
        io.write("</v></c>")
      else
        io.write("</c>")
      end
    else
      io.write("><v>")
      io.write(xml_val.to_s)
      io.write("</v></c>")
    end
  end

  unmapped.each { |node| @builder.write_unmapped(node) }

  io.write("</row>")
end

#write_row_values(row_index, values, styles: nil, style_map: nil, sst: nil, sst_index: nil, attrs: nil) ⇒ Object

Highly optimized row writing for StreamWriter that avoids allocating intermediate Hashes.



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
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
 
# File 'lib/xlsxrb/ooxml/worksheet_writer.rb', line 230

def write_row_values(row_index, values, styles: nil, style_map: nil, sst: nil, sst_index: nil, attrs: nil)
  start unless @started

  row_num = row_index + 1
  row_num_str = row_num.to_s
  style_lookup_enabled = styles && style_map && (styles.is_a?(Array) || styles.is_a?(Hash))
  io = @io
  io.write("<row r=\"")
  io.write(row_num_str)
  io.write('"')
  if attrs
    if attrs[:height]
      io.write(' ht="')
      io.write(attrs[:height].to_s)
      io.write('" customHeight="1"')
    end
    io.write(' hidden="1"') if attrs[:hidden]
    if attrs[:outline_level]
      io.write(' outlineLevel="')
      io.write(attrs[:outline_level].to_s)
      io.write('"')
    end
  end
  io.write(">")

  col_index = 0
  while col_index < values.length
    value = values[col_index]
    style_id = nil
    if style_lookup_enabled
      style_name = styles[col_index]
      style_id = style_map[style_name] if style_name
    end

    col_ref = column_letter(col_index)

    if value.nil?
      if style_id
        io.write('<c r="')
        io.write(col_ref)
        io.write(row_num_str)
        io.write('" s="')
        io.write(style_id.to_s)
        io.write('"/>')
      end
      col_index += 1
      next
    end

    # Auto-convert string starting with '=' to formula
    if value.is_a?(String) && value.start_with?("=")
      formula_expr = value
      xml_val = nil
    else
      formula_expr = nil
      xml_val = value
    end

    type = nil
    formula_ca = false

    case value
    when Xlsxrb::Elements::Formula
      formula_expr = value.expression
      formula_ca = value.calculate_always
      xml_val = value.cached_value
      case value.cached_value
      when String
        type = "str"
      when true
        type = "b"
        xml_val = "1"
      when false
        type = "b"
        xml_val = "0"
      end
    when String
      if value.start_with?("=")
        # already set formula_expr
      else
        idx = sst_index[value]
        unless idx
          sst << value
          idx = sst.size - 1
          sst_index[value] = idx
        end
        xml_val = idx
        type = "s"
      end
    when Xlsxrb::Elements::RichText
      idx = sst_index[value]
      unless idx
        sst << value
        idx = sst.size - 1
        sst_index[value] = idx
      end
      xml_val = idx
      type = "s"
    when true
      xml_val = "1"
      type = "b"
    when false
      xml_val = "0"
      type = "b"
    when Date
      xml_val = Xlsxrb::Ooxml::Utils.date_to_serial(value)
    when Time
      xml_val = Xlsxrb::Ooxml::Utils.datetime_to_serial(value)
    when Xlsxrb::Elements::CellError
      xml_val = value.code
      type = "e"
    end

    formula_expr = formula_expr[1..] if formula_expr&.start_with?("=")

    io.write('<c r="')
    io.write(col_ref)
    io.write(row_num_str)
    io.write('"')
    if style_id
      io.write(' s="')
      io.write(style_id.to_s)
      io.write('"')
    end
    if type
      io.write(' t="')
      io.write(type)
      io.write('"')
    end
    if formula_expr
      if formula_ca
        io.write('><f ca="1">')
      else
        io.write("><f>")
      end
      io.write(escape_xml(formula_expr))
      io.write("</f>")
      if xml_val
        io.write("<v>")
        io.write(xml_val.to_s)
        io.write("</v></c>")
      else
        io.write("</c>")
      end
    else
      io.write("><v>")
      io.write(xml_val.to_s)
      io.write("</v></c>")
    end

    col_index += 1
  end

  io.write("</row>")
end