Class: RubyFromExcel::Workbook

Inherits:
Object
  • Object
show all
Defined in:
lib/excelfile/workbook.rb

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(filename) ⇒ Workbook

Returns a new instance of Workbook.



12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
 
# File 'lib/excelfile/workbook.rb', line 12

def initialize(filename)
  @worksheets = {}
  @worksheet_array = []
  @named_references = {}
  @indirects_used = false
  @relationships = Relationships.for_file(filename)
  xml = File.open(filename) { |f| Nokogiri::XML(f) }.root
  load_shared_strings
  puts "\nLoaded shared strings"
  load_worksheets_from xml
  puts "Loaded #{worksheets.size} worksheets with #{total_cells} cells in total."
  work_out_named_references_from(xml)
  puts "Loaded named references"
  GC.start
end

Instance Attribute Details

#indirects_used ⇒ Object

Returns the value of attribute indirects_used.



10
11
12
 
# File 'lib/excelfile/workbook.rb', line 10

def indirects_used
  @indirects_used
end

#named_references ⇒ Object (readonly)

Returns the value of attribute named_references.



9
10
11
 
# File 'lib/excelfile/workbook.rb', line 9

def named_references
  @named_references
end

#relationships ⇒ Object (readonly)

Returns the value of attribute relationships.



6
7
8
 
# File 'lib/excelfile/workbook.rb', line 6

def relationships
  @relationships
end

#worksheet_array ⇒ Object (readonly)

Returns the value of attribute worksheet_array.



8
9
10
 
# File 'lib/excelfile/workbook.rb', line 8

def worksheet_array
  @worksheet_array
end

#worksheets ⇒ Object (readonly)

Returns the value of attribute worksheets.



7
8
9
 
# File 'lib/excelfile/workbook.rb', line 7

def worksheets
  @worksheets
end

Instance Method Details

#cell(reference) ⇒ Object 



84
85
86
87
 
# File 'lib/excelfile/workbook.rb', line 84

def cell(reference)
  reference =~ /^(sheet\d+)\.([a-z]+\d+)$/
  worksheets[$1].cell($2)
end

#convert_cells_to_values_when_independent_of_input_sheets(*input_sheets) ⇒ Object 



80
81
82
 
# File 'lib/excelfile/workbook.rb', line 80

def convert_cells_to_values_when_independent_of_input_sheets(*input_sheets)
  workbook_pruner.convert_cells_to_values_when_independent_of_input_sheets(*input_sheets)
end

#load_shared_strings ⇒ Object 



28
29
30
31
 
# File 'lib/excelfile/workbook.rb', line 28

def load_shared_strings
  return unless relationships.shared_strings
  SharedStrings.instance.load_strings_from_xml(File.open(relationships.shared_strings) { |f| Nokogiri::XML(f) }.root)    
end

#load_worksheets_from(xml) ⇒ Object 



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

def load_worksheets_from(xml)
  xml.css("sheet").each do |s|
    worksheet_filename = relationships[s['id']]
    worksheet = Worksheet.from_file(worksheet_filename,self)
    worksheets[worksheet.name] = worksheet
    SheetNames.instance[s['name']] = worksheet.name
    RubyFromExcel.debug(:worksheet_names,"#{worksheet.name}: #{s['name'].inspect}")
    worksheet_array << worksheet
    puts "Loaded #{worksheet.name} with #{worksheet.cells.size} cells"
  end
end

#prune_cells_not_needed_for_output_sheets(*output_sheets) ⇒ Object 



76
77
78
 
# File 'lib/excelfile/workbook.rb', line 76

def prune_cells_not_needed_for_output_sheets(*output_sheets)
  workbook_pruner.prune_cells_not_needed_for_output_sheets(*output_sheets)
end

#to_ruby(r = RubyScriptWriter.new) ⇒ Object 



89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
 
# File 'lib/excelfile/workbook.rb', line 89

def to_ruby(r = RubyScriptWriter.new)
  r.put_coding
  r.puts "require 'rubyfromexcel'"
  r.puts
  r.put_class 'Spreadsheet' do
    r.puts 'include RubyFromExcel::ExcelFunctions'
    r.puts
    if indirects_used
      r.put_method "initialize" do
        r.puts "@worksheet_names = #{Hash[SheetNames.instance.sort]}"
        r.puts "@workbook_tables = #{Hash[Table.tables.sort]}"
      end
      named_references.each do |name,reference|
        r.put_simple_method name.downcase.gsub(/[^\p{word}]/,'_'), reference
      end
    end
  end
  r.puts 'Dir[File.join(File.dirname(__FILE__),"sheets/","sheet*.rb")].each {|f| Spreadsheet.autoload(File.basename(f,".rb").capitalize,f)}'
  r.to_s
end

#total_cells ⇒ Object 



110
111
112
 
# File 'lib/excelfile/workbook.rb', line 110

def total_cells
  worksheets.inject(0) { |total,a| a.last.cells.size + total }
end

#work_out_dependencies ⇒ Object 



63
64
65
66
67
68
69
70
 
# File 'lib/excelfile/workbook.rb', line 63

def work_out_dependencies
  puts "Working out dependencies..."
  worksheets.each do |name,worksheet|
    puts "Working out dependencies for #{name}"
    worksheet.work_out_dependencies
  end
  puts "Finished working out dependencies"
end

#work_out_named_references_from(xml) ⇒ Object 



45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
 
# File 'lib/excelfile/workbook.rb', line 45

def work_out_named_references_from(xml)
  xml.css('definedName').each do |defined_name_xml|
    reference_name = defined_name_xml['name'].downcase # .gsub(/[^a-z0-9_]/,'_')
    reference_value = defined_name_xml.content
    if reference_value.start_with?('[')
      puts "Sorry, #{reference_name} (#{reference_value}) has a link to an external workbook. Skipping."
      next
    end
    reference = Formula.parse(reference_value).visit(FormulaBuilder.new)
    if defined_name_xml["localSheetId"]
      worksheet_array[defined_name_xml["localSheetId"].to_i].named_references[reference_name] = reference
    else
      named_references[reference_name] = reference
    end
    RubyFromExcel.debug(:named_references,"#{defined_name_xml['name'].inspect} (#{defined_name_xml["localSheetId"]}) -> #{reference_name.inspect} (#{defined_name_xml["localSheetId"] ? worksheet_array[defined_name_xml["localSheetId"].to_i].name.inspect : ""}) -> #{reference_value.inspect}")
  end
end

#workbook_pruner ⇒ Object 



72
73
74
 
# File 'lib/excelfile/workbook.rb', line 72

def workbook_pruner
  @workbook_pruner ||= WorkbookPruner.new(self)
end