Class: RubyFromExcel::WorkbookPruner

Inherits:
Object
  • Object
show all
Defined in:
lib/optimiser/workbook_pruner.rb

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(workbook) ⇒ WorkbookPruner

Returns a new instance of WorkbookPruner.



6
7
8
9
 
# File 'lib/optimiser/workbook_pruner.rb', line 6

def initialize(workbook)
  @workbook = workbook
  @depends_on_input_sheet_cache = {}
end

Instance Attribute Details

#cells_to_keep ⇒ Object (readonly)

Returns the value of attribute cells_to_keep.



4
5
6
 
# File 'lib/optimiser/workbook_pruner.rb', line 4

def cells_to_keep
  @cells_to_keep
end

#output_sheet_names ⇒ Object (readonly)

Returns the value of attribute output_sheet_names.



4
5
6
 
# File 'lib/optimiser/workbook_pruner.rb', line 4

def output_sheet_names
  @output_sheet_names
end

#workbook ⇒ Object (readonly)

Returns the value of attribute workbook.



4
5
6
 
# File 'lib/optimiser/workbook_pruner.rb', line 4

def workbook
  @workbook
end

Instance Method Details

#convert_cells_to_values_when_independent_of_input_sheets(*input_sheet_names) ⇒ Object 



61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
 
# File 'lib/optimiser/workbook_pruner.rb', line 61

def convert_cells_to_values_when_independent_of_input_sheets(*input_sheet_names)
  input_sheet_names = input_sheet_names.map { |name| SheetNames.instance[name] }
  count = 0
  workbook.worksheets.each do |name,sheet|
    puts "Converting cells into values in #{name}"
    sheet.cells.each do |reference,cell|
      next if cell.is_a?(ValueCell)
      unless depends_on_input_sheets?(cell,input_sheet_names)
        count = count + 1
        RubyFromExcel.debug(:pruning_replace,"#{name}.#{reference} -> #{cell.original_formula.inspect} -> #{cell.value.inspect}")
        sheet.replace_cell(reference,ValueCell.for(cell))
      end
    end
  end
  puts "#{count} formula cells replaced with their values"
  GC.start
end

#delete_cells_that_we_dont_want_to_keep ⇒ Object 



46
47
48
49
50
51
52
53
54
55
56
57
58
59
 
# File 'lib/optimiser/workbook_pruner.rb', line 46

def delete_cells_that_we_dont_want_to_keep
  workbook.worksheets.each do |name,sheet|
    sheet.cells.delete_if do |reference,cell|
      if cells_to_keep.has_key?(cell.to_s)
        false
      #elsif cell.must_keep?
      #  false
      else
        RubyFromExcel.debug(:pruning_delete,"#{name}.#{reference}")
        true
      end
    end
  end
end

#depends_on_input_sheets?(cell, input_sheet_names, stack_level = 0) ⇒ Boolean

Returns:

  • (Boolean) 


79
80
81
82
83
 
# File 'lib/optimiser/workbook_pruner.rb', line 79

def depends_on_input_sheets?(cell,input_sheet_names,stack_level = 0)
  return @depends_on_input_sheet_cache[cell] if @depends_on_input_sheet_cache.has_key?(cell)
  @depends_on_input_sheet_cache[cell] = work_out_if_depends_on_input_sheets(cell,input_sheet_names,stack_level)
  work_out_if_depends_on_input_sheets(cell,input_sheet_names,stack_level)
end

#find_dependencies_of(output_sheet_names) ⇒ Object 



21
22
23
24
25
26
27
28
29
30
31
 
# File 'lib/optimiser/workbook_pruner.rb', line 21

def find_dependencies_of(output_sheet_names)
  output_sheet_names.each do |output_sheet_name|
    puts "Cascading dependencies for #{output_sheet_name}"
    output_sheet = workbook.worksheets[SheetNames.instance[output_sheet_name]] 
    raise Exception.new("#{output_sheet_name} not found #{SheetNames.instance.inspect} #{workbook.worksheets.keys.inspect}") unless output_sheet
    output_sheet.cells.each do |reference,cell|
      keep_dependencies_for(cell)
    end
  end
  puts "#{cells_to_keep.size} cells kept, #{workbook.total_cells - cells_to_keep.size} pruned"
end

#keep_dependencies_for(cell) ⇒ Object 



33
34
35
36
37
38
39
40
41
42
43
44
 
# File 'lib/optimiser/workbook_pruner.rb', line 33

def keep_dependencies_for(cell)
  # p [cell.to_s,cell.dependencies]
  return unless cell
  RubyFromExcel.debug(:pruning_keep,"#{cell.worksheet.name}.#{cell.reference}")
  return if cells_to_keep.has_key?(cell.to_s)
  cells_to_keep[cell.to_s] = cell
  cell.dependencies.each do |reference|
    c = workbook.cell(reference)
    RubyFromExcel.debug(:pruning_missing,"#{reference}")
    keep_dependencies_for c
  end
end

#prune_cells_not_needed_for_output_sheets(*output_sheet_names) ⇒ Object 



11
12
13
14
15
16
17
18
19
 
# File 'lib/optimiser/workbook_pruner.rb', line 11

def prune_cells_not_needed_for_output_sheets(*output_sheet_names)
  @cells_to_keep = {}
  @output_sheet_names = output_sheet_names
  find_dependencies_of output_sheet_names
  # breakpoint
  delete_cells_that_we_dont_want_to_keep
  cells_to_keep = nil # So that we can garbage collect
  GC.start
end

#work_out_if_depends_on_input_sheets(cell, input_sheet_names, stack_level) ⇒ Object 



85
86
87
88
89
90
91
92
93
94
95
 
# File 'lib/optimiser/workbook_pruner.rb', line 85

def work_out_if_depends_on_input_sheets(cell,input_sheet_names,stack_level)
  #p [cell.to_s,cell.dependencies]
  return true if stack_level > 100
  return false unless cell
  return true if input_sheet_names.include?(cell.worksheet.variable_name)
  return false unless cell.dependencies
  return true if cell.dependencies.any? do |reference|
    depends_on_input_sheets?(workbook.cell(reference),input_sheet_names,stack_level + 1)
  end
  return false
end