Class: Axlsx::Worksheet

Inherits:
Object
  • Object
show all
Defined in:
lib/axlsx/builder.rb

Instance Attribute Summary collapse

Instance Method Summary collapse

Instance Attribute Details

#blueprint ⇒ Object

Returns the value of attribute blueprint.



154
155
156
 
# File 'lib/axlsx/builder.rb', line 154

def blueprint
  @blueprint
end

#column_data ⇒ Object

Returns the value of attribute column_data.



154
155
156
 
# File 'lib/axlsx/builder.rb', line 154

def column_data
  @column_data
end

#column_title_indexes ⇒ Object

Returns the value of attribute column_title_indexes.



154
155
156
 
# File 'lib/axlsx/builder.rb', line 154

def column_title_indexes
  @column_title_indexes
end

#row_data ⇒ Object

Returns the value of attribute row_data.



154
155
156
 
# File 'lib/axlsx/builder.rb', line 154

def row_data
  @row_data
end

#row_title_indexes ⇒ Object

Returns the value of attribute row_title_indexes.



154
155
156
 
# File 'lib/axlsx/builder.rb', line 154

def row_title_indexes
  @row_title_indexes
end

Instance Method Details

#build! ⇒ Object 



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
 
# File 'lib/axlsx/builder.rb', line 156

def build!
  if self.column_data
    self.blueprint.column_data = self.column_data.map{|elem| Builder::Cell.new(elem)}
  end

  if self.row_data
    self.blueprint.row_data = self.row_data.map{|elem| Builder::Cell.new(elem)}
  end

  set_column_title_indexes

  set_max_row_and_column

  set_row_title_indexes

  set_column_widths

  set_row_heights

  place_elements

  place_column_titles

  set_lists_for_column_titles

  place_row_titles

  set_lists_for_row_titles

  set_column_title_row_height

  place_column_data if self.blueprint.column_data

  place_row_data if self.blueprint.row_data

  move_lists_sheet_to_end

  self
end

#column(index) ⇒ Object 



465
466
467
468
469
 
# File 'lib/axlsx/builder.rb', line 465

def column index
  column = (index % 26 + 65).chr
  column << (index / 26 + 64).chr if index > 25
  column.reverse
end

#current_lists_sheet ⇒ Object 



471
472
473
 
# File 'lib/axlsx/builder.rb', line 471

def current_lists_sheet
  self.workbook.sheet_by_name 'Lists'
end

#data(column: nil, row: nil) ⇒ Object 



477
478
479
480
481
 
# File 'lib/axlsx/builder.rb', line 477

def data column: nil, row: nil
  self.column_data = column
  self.row_data = row
  self.build!
end

#move_lists_sheet_to_end ⇒ Object 



453
454
455
456
457
458
459
460
461
462
463
 
# File 'lib/axlsx/builder.rb', line 453

def move_lists_sheet_to_end
  return false if self.name == 'Lists'
  sheets = self.workbook.worksheets
  lists_sheet_index = sheets.index { |sheet| sheet.name == 'Lists' }
  if lists_sheet_index
    lists_sheet = sheets[lists_sheet_index]
    sheets.delete_at lists_sheet_index
    sheets << lists_sheet
    sheets.each_with_index {|sheet, index| sheet.workbook.worksheets[index] = sheet}
  end
end

#place_column_data ⇒ Object 



425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
 
# File 'lib/axlsx/builder.rb', line 425

def place_column_data
  self.blueprint.column_data.each do |elem|
    index = self.column_title_indexes[elem.title]
    next unless index
    title_column = column index
    if elem.row
      self.blueprint.name_to_cell("#{title_column}#{elem.row + self.blueprint.column_titles_start[0] + 1}").value = elem.text
    else
      row = self.blueprint.column_titles_start[0] + 1
      while self.name_to_cell("#{title_column}#{row}").value
        row += 1
      end
      self.name_to_cell("#{title_column}#{row}").value = elem.text
    end
  end
end

#place_column_titles ⇒ Object 



300
301
302
303
304
305
306
307
308
309
 
# File 'lib/axlsx/builder.rb', line 300

def place_column_titles
  self.blueprint.column_titles.each_with_index do |elem, index|
    cell = self.name_to_cell("#{column(index+self.blueprint.column_titles_start[1]-1)}#{self.blueprint.column_titles_start[0]}")
    cell.value = elem.text
    style = self.styles.add_style elem.combined_style
    cell.style = style
    self.add_hyperlink location: elem.hyperlink, ref: cell if elem.hyperlink
    self.add_comment ref: "#{column(index+self.blueprint.column_titles_start[1]-1)}#{self.blueprint.column_titles_start[0]}", text: "#{elem.comment}", author: elem.text, visible: false if elem.comment
  end
end

#place_elements ⇒ Object 



274
275
276
277
278
279
280
281
282
 
# File 'lib/axlsx/builder.rb', line 274

def place_elements
  self.blueprint.elements.each do |elem|
    self.name_to_cell("#{column(elem.col)}#{elem.row}").value = elem.text
    style = self.styles.add_style elem.combined_style
    self.name_to_cell("#{column(elem.col)}#{elem.row}").style = style
    self.add_comment ref: "#{column(elem.col)}#{elem.row}", text: "#{elem.comment}", author: elem.text, visible: false if elem.comment
    self.merge_cells "#{column(elem.col)}#{elem.row}:#{column(elem.col+elem.merge)}#{elem.row}" if elem.merge
  end
end

#place_row_data ⇒ Object 



442
443
444
445
446
447
448
449
450
451
 
# File 'lib/axlsx/builder.rb', line 442

def place_row_data
  self.blueprint.row_data.each do |elem|
    title_row = self.row_title_indexes[elem.title]
    column = self.blueprint.row_titles_start[1]
    while self.name_to_cell("#{column column}#{title_row+1}").value
      column += 1
    end
    self.name_to_cell("#{column column}#{title_row+1}").value = elem.text
  end
end

#place_row_titles ⇒ Object 



357
358
359
360
361
362
363
364
365
366
 
# File 'lib/axlsx/builder.rb', line 357

def place_row_titles
  self.blueprint.row_titles.each_with_index do |elem, index|
    cell = self.name_to_cell("#{column(self.blueprint.row_titles_start[1]-1)}#{self.blueprint.row_titles_start[0]+index}")
    cell.value = elem.text
    style = self.styles.add_style elem.combined_style
    cell.style = style
    self.add_hyperlink location: elem.hyperlink, ref: cell if elem.hyperlink
    self.add_comment ref: "#{column(self.blueprint.row_titles_start[1]-1)}#{self.blueprint.row_titles_start[0]+index}", text: "#{elem.comment}", author: elem.text, visible: false if elem.comment
  end
end

#set_column_title_indexes ⇒ Object 



196
197
198
199
 
# File 'lib/axlsx/builder.rb', line 196

def set_column_title_indexes
  self.column_title_indexes = {}
  self.blueprint.column_titles.each_with_index {|elem, index| self.column_title_indexes[elem.text] = index+self.blueprint.column_titles_start[1]-1}
end

#set_column_title_row_height ⇒ Object 



415
416
417
418
419
420
421
422
423
 
# File 'lib/axlsx/builder.rb', line 415

def set_column_title_row_height
  return false if self.blueprint.column_titles.empty?
  if self.blueprint.column_title_row_height
    self.rows[self.blueprint.column_titles_start[0]-1].height = column_title_row_height
  else
    most_lines = self.blueprint.column_titles.max_by{|elem| elem.text.split("\n").size}.text.split("\n").size
    self.rows[self.blueprint.column_titles_start[0]-1].height = most_lines * 10 + 10
  end
end

#set_column_widths ⇒ Object 



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
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
 
# File 'lib/axlsx/builder.rb', line 206

def set_column_widths
  col_widths = {}

  element_strings = []

  self.blueprint.elements.each do |element|
    longest_line = element.text.split("\n").max_by{|line| line.length}
    element_strings.push [element.col, longest_line || '']
    (1..element.merge).each {|column| col_widths[column] = 0} if element.merge
  end

  element_strings.each do |col, text|
    col_widths[col] ||= 0
    col_widths[col] = text.length if text.length > col_widths[col]
  end

  self.blueprint.column_titles.each_with_index do |elem, index|
    col_widths[index+self.blueprint.column_titles_start[1]-1] ||= 0
    col_widths[index+self.blueprint.column_titles_start[1]-1] = elem.text.length if elem.text.length > col_widths[index+self.blueprint.column_titles_start[1]-1]
  end

  unless self.blueprint.row_titles.empty?
    col_widths[self.blueprint.row_titles_start[1]-1] ||= 0
    max_row_title = self.blueprint.row_titles.max_by{|elem| elem.text.length}.text.length
    col_widths[self.blueprint.row_titles_start[1]-1] = max_row_title if max_row_title > col_widths[self.blueprint.row_titles_start[1]-1]
  end

  if self.blueprint.column_data
    self.blueprint.column_data.each do |elem|
      title_column = self.column_title_indexes[elem.title]
      col_widths[title_column] ||= 0
      col_widths[title_column] = elem.text.length if elem.text.length > col_widths[title_column]
    end
  end

  if self.blueprint.row_data
    self.blueprint.row_data.each do |elem|
      self.blueprint.row_data.select{|data| data.text == elem.text}.each_with_index do |title_data, index|
        column = self.blueprint.row_titles_start[1] + 1 + index
        col_widths[column] ||= 0
        col_widths[column] = title_data.text.length if title_data.text.length > col_widths[column]
      end
    end
  end

  widths = (0..self.blueprint.max[:col].to_i).map{|col| col_widths[col] ? col_widths[col]+4 : 0}
  self.column_widths *widths
end

#set_lists_for_column_titles ⇒ Object 



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
 
# File 'lib/axlsx/builder.rb', line 311

def set_lists_for_column_titles
  existing_titles_with_lists = self.blueprint.column_titles.select{|elem| elem.list}
  return false if existing_titles_with_lists.empty?
  if lists_sheet = current_lists_sheet
    pre_existing_titles_with_lists = []
    lists_sheet.column_title_indexes.each do |title, column|
      row = 2
      list = []
      while cell = lists_sheet.name_to_cell("#{column column}#{row}") and cell.value
        list << cell.value
        row += 1
      end
      pre_existing_titles_with_lists << Axlsx::Builder::Title.new(text: title, list: list)
    end
    titles_with_lists = pre_existing_titles_with_lists + existing_titles_with_lists
    lists_sheet_index = self.workbook.worksheets.index {|sheet| sheet.name == 'Lists'}
    self.workbook.worksheets.delete_at lists_sheet_index
  else
    titles_with_lists = existing_titles_with_lists
  end
  list_titles = []
  list_data = []
  titles_with_lists.each do |elem|
    list_titles = titles_with_lists.map{|elem| {text: elem.text}}
    elem.list.each {|list_item| list_data << {text: list_item, title: elem.text}}
  end
  list_titles.uniq!
  list_data.uniq!
  blueprint = Axlsx::Builder::Blueprint.new column_titles: list_titles
  lists_sheet = self.workbook.add_worksheet name: 'Lists', blueprint: blueprint
  lists_sheet.column_data= list_data
  existing_titles_with_lists.each do |elem|
    100.times do |row|
      list_column = current_lists_sheet.column_title_indexes[elem.text]
      self.add_data_validation("#{column (self.column_title_indexes[elem.text])}#{self.blueprint.column_titles_start[0]+row+1}", {
          type: :list,
          formula1: "Lists!#{column list_column}2:#{column list_column}#{elem.list.size+1}",
          showDropDown: false,
          showErrorMessage: true,
          errorTitle: '',
          errorStyle: :stop,
          showInputMessage: true})
    end
  end
end

#set_lists_for_row_titles ⇒ Object 



368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
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
 
# File 'lib/axlsx/builder.rb', line 368

def set_lists_for_row_titles
  existing_titles_with_lists = self.blueprint.row_titles.select{|elem| elem.list}
  return false if existing_titles_with_lists.empty?
  if lists_sheet = current_lists_sheet
    pre_existing_titles_with_lists = []
    lists_sheet.column_title_indexes.each do |title, column|
      row = 2
      list = []
      while cell = lists_sheet.name_to_cell("#{column column}#{row}") and cell.value
        list << cell.value
        row += 1
      end
      pre_existing_titles_with_lists << Builder::Title.new(text: title, list: list)
    end
    titles_with_lists = pre_existing_titles_with_lists + existing_titles_with_lists
    lists_sheet_index = self.workbook.worksheets.index {|sheet| sheet.name == 'Lists'}
    self.workbook.worksheets.delete_at lists_sheet_index
  else
    titles_with_lists = existing_titles_with_lists
  end
  list_titles = []
  list_data = []
  titles_with_lists.each do |elem|
    list_titles = titles_with_lists.map{|elem| {text: elem.text}}
    elem.list.each {|list_item| list_data << {text: list_item, title: elem.text}}
  end
  list_titles.uniq!
  list_data.uniq!
  blueprint = Axlsx::Builder::Blueprint.new column_titles: list_titles
  lists_sheet = self.workbook.add_worksheet name: 'Lists', blueprint: blueprint
  lists_sheet.data column: list_data
  existing_titles_with_lists.each do |elem|
    100.times do |column|
      list_column = current_lists_sheet.column_title_indexes[elem.text]
      self.add_data_validation("#{column self.blueprint.row_titles_start[1]+column}#{self.row_title_indexes[elem.text]+1}", {
          type: :list,
          formula1: "Lists!#{column list_column}2:#{column list_column}#{elem.list.size+1}",
          showDropDown: false,
          showErrorMessage: true,
          errorTitle: '',
          errorStyle: :stop,
          showInputMessage: true})
    end
  end
end

#set_max_row_and_column ⇒ Object 



284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
 
# File 'lib/axlsx/builder.rb', line 284

def set_max_row_and_column
  self.blueprint.max = {col: 0, row: 0}
  self.blueprint.max[:row] = self.blueprint.elements.max_by{|elem| elem.row}.row unless self.blueprint.elements.empty?
  self.blueprint.max[:col] = self.blueprint.elements.max_by{|elem| elem.col}.col unless self.blueprint.elements.empty?
  self.blueprint.max[:col] = self.blueprint.column_titles.count if self.blueprint.column_titles and self.blueprint.column_titles.count > self.blueprint.max[:col]
  self.blueprint.max[:row] = self.blueprint.row_titles.count if self.blueprint.row_titles and self.blueprint.row_titles.count > self.blueprint.max[:row]
  self.blueprint.max[:row] += self.blueprint.column_titles_start[0] if self.blueprint.column_titles_start
  self.blueprint.max[:col] += self.blueprint.column_titles_start[1] if self.blueprint.column_titles_start
  self.blueprint.max[:row] += self.blueprint.row_titles_start[0] if self.blueprint.row_titles_start
  self.blueprint.max[:col] += self.blueprint.row_titles_start[1] if self.blueprint.row_titles_start
  self.blueprint.max[:row] += self.blueprint.column_data.size if self.blueprint.column_data
  self.blueprint.max[:col] += self.blueprint.row_data.size if self.blueprint.row_data
  self.blueprint.max[:col] += self.blueprint.column_titles.select{|elem| elem.list}.size + self.blueprint.row_titles.select{|elem| elem.list}.size
  (0..self.blueprint.max[:row]+1).each {self.add_row(Array.new(self.blueprint.max[:col].to_i, nil))}
end

#set_row_heights ⇒ Object 



255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
 
# File 'lib/axlsx/builder.rb', line 255

def set_row_heights
  row_heights = {}

  self.blueprint.elements.each do |elem|
    row_heights[elem.row] ||= 0
    height = elem.text.split("\n").count * 10 + (elem.font.size / 3)
    row_heights[elem.row] = height if height > row_heights[elem.row]
  end

  unless self.blueprint.column_titles.empty?
    max_column_title_height = self.blueprint.column_titles.map{|elem| elem.text.split("\n").size}.max * 10 + 10
    row_heights[self.blueprint.column_titles_start[0]] ||= 0
    row_heights[self.blueprint.column_titles_start[0]] = max_column_title_height if max_column_title_height > row_heights[self.blueprint.column_titles_start[0]]
  end


  (0..self.blueprint.max[:row].to_i).each {|row| self.rows[row-1].height = row_heights[row] || 20}
end

#set_row_title_indexes ⇒ Object 



201
202
203
204
 
# File 'lib/axlsx/builder.rb', line 201

def set_row_title_indexes
  self.row_title_indexes = {}
  self.blueprint.row_titles.each_with_index {|elem, index| self.row_title_indexes[elem.text] = index+self.blueprint.row_titles_start[0]-1}
end