class Sheet

A sheet in a spreadsheet workbook

Constants

Known_Expressions

Attributes

id[R]
name[R]
table[R]
zip_entry[R]

Public Class Methods

new(sheet, zip_entry, table, shared_strings) click to toggle source
# File sheet.rb, line 36
def initialize(sheet, zip_entry, table, shared_strings)
  @log = @@log
  @shared_strings = shared_strings
  # zip_entry is just a marker for the time, which allows the spreadsheet
  # to locate this sheet.
  @zip_entry = zip_entry
  @log.debug('creating sheet  ' << sheet)
  # find the file for this sheet
  node = Nokogiri::XML::fragment(sheet).at_xpath(".//sheet")
  @name = node.attribute('name').value
  # beware that xml attribute-values are Strings.
  sheetId = node.attribute('sheetId').value
  @id = sheetId.to_i
  # Its XML-content
  @table = Nokogiri::XML::Document.parse(table)
  # flag to negate expressions like
  # '... value in cell 1 is not empty' => '!cell.value.empty?'
  @negate = false;
end

Public Instance Methods

remove_line_be(reference, expr) click to toggle source

Delete the rows where the value in the referenced cell produces a true statement. expr must be one of the known expressions, comprising a numerical value, if any.

# File sheet.rb, line 63
def remove_line_be(reference, expr)
  @log.debug('original expression is ' << expr)
  expression = match_expression(expr)
  exit false if(!expression)
  negi = (@negate ? '!' : '' )
  expr_format = "%s (%s %s )" 

  @log.debug('full expression is ' << negi << expression)
  # Find the rows to delete
  # Translate value to the index of its entry in sharedStrings.xml, if possible.
  value = shared_string_index(value)
  # All rows
  rows = all_rows
  # @log.debug('rows is ' << rows.to_s )
  del_rows = Array.new
  rows.each do |row| 
    cell = row.css('c').detect {|c| c.attribute('r').value().start_with?(reference.upcase)  }
    if cell
      cell_value =  cell.at_css('v')
      cell_value = (cell_value ? cell_value.text : "''")
      @log.debug("cell_value is " << cell_value.to_s)
      # Use the Ruby-Force to evaluate the String expression on the cell-value
      comparison = expr_format %[negi, cell_value, expression]
      if eval comparison  
        # note the deleted row for later.
        del_rows.push(row)
        # unlink the row from its xml-context
        row.remove 
        @log.debug('He was only a ♫♪♬ ... but one more is gone!')
      end
    end
  end
  # ... later:
  # Remove the already unlinked nodes from the node-list.
  # I cannot say that I comprehend why this is necessary.
  delete_from_nodeset(all_rows, del_rows)
  adjust_rows
  @log.debug('Table is now ' << @table.to_s)
end
remove_line_bv(reference, value) click to toggle source

Delete the rows which contain the given value in the referenced cell. This is the same as remove_line_be(reference, '= value')

# File sheet.rb, line 105
def remove_line_bv(reference, value)
  # Find the rows to delete
  # Translate value to the index of its entry in sharedStrings.xml, if possible.
  value = shared_string_index(value)
  del_rows = Array.new
  @log.debug('have ' << all_rows.length.to_s << ' rows')
  all_rows.each do |row| 
    # take aim
    if (row.css('c').detect{|c| c.attribute('r').value().start_with?(reference.upcase) && c.at_css('v') && c.at_css('v').text == value.to_s} )
      # ... shoot
      row.remove 
      # ... move the body out of the way
      del_rows.push(row)
    end
  end
  # ... later:
  # Remove the already unlinked nodes from the node-list.
  # I cannot say that I comprehend why this is necessary.
  delete_from_nodeset(all_rows, del_rows)
  adjust_rows
  @log.debug('have now ' << all_rows.length.to_s << ' rows')
  @log.debug('Table is now ' << @table.to_s)
end
to_s() click to toggle source
# File sheet.rb, line 56
def to_s
  @table.to_s if @table 
end