class Axlsx::Worksheet

Attributes

blueprint[RW]
column_data[RW]
column_title_indexes[RW]
row_data[RW]
row_title_indexes[RW]

Public Instance Methods

build!() click to toggle source
# 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) click to toggle source
# 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() click to toggle source
# File lib/axlsx/builder.rb, line 471
def current_lists_sheet
  self.workbook.sheet_by_name 'Lists'
end
data(column: nil, row: nil) click to toggle source
# 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() click to toggle source
# 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() click to toggle source
# 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() click to toggle source
# 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() click to toggle source
# 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() click to toggle source
# 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() click to toggle source
# 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() click to toggle source
# 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() click to toggle source
# 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() click to toggle source
# 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() click to toggle source
# 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() click to toggle source
# 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() click to toggle source
# 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() click to toggle source
# 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() click to toggle source
# 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