Class: OOXL

Inherits:
Object
  • Object
show all
Includes:
Enumerable, ListHelper
Defined in:
lib/ooxl/ooxl.rb,
lib/ooxl/util.rb,
lib/ooxl/version.rb,
lib/ooxl/list_helper.rb,
lib/ooxl/xl_objects/row.rb,
lib/ooxl/xl_objects/cell.rb,
lib/ooxl/xl_objects/fill.rb,
lib/ooxl/xl_objects/font.rb,
lib/ooxl/xl_objects/sheet.rb,
lib/ooxl/xl_objects/column.rb,
lib/ooxl/xl_objects/styles.rb,
lib/ooxl/xl_objects/comments.rb,
lib/ooxl/xl_objects/workbook.rb,
lib/ooxl/xl_objects/relationships.rb,
lib/ooxl/xl_objects/number_formatting.rb,
lib/ooxl/xl_objects/cell_style_reference.rb,
lib/ooxl/xl_objects/sheet/data_validation.rb

Defined Under Namespace

Modules: ListHelper, Util Classes: BlankCell, Cell, CellStyleReference, Column, Comments, Fill, Font, NumberFormatting, Relationships, Row, Sheet, Styles, Workbook

Constant Summary collapse

VERSION = 
"0.0.1.4.8.1"

Class Method Summary collapse

Instance Method Summary collapse

Methods included from ListHelper

#list_values

Constructor Details

#initialize(spreadsheet_filepath, options = {}) ⇒ OOXL

Returns a new instance of OOXL.



5
6
7
8
9
10
11
12
13
 
# File 'lib/ooxl/ooxl.rb', line 5

def initialize(spreadsheet_filepath, options={})
  @workbook = nil
  @sheets = {}
  @styles = []
  @comments = {}
  @relationships = {}
  @options = options
  parse_spreadsheet_contents(spreadsheet_filepath)
end

Class Method Details

.open(spreadsheet_filepath, options = {}) ⇒ Object 



15
16
17
 
# File 'lib/ooxl/ooxl.rb', line 15

def self.open(spreadsheet_filepath, options={})
  new(spreadsheet_filepath, options)
end

Instance Method Details

#[](text) ⇒ Object 



45
46
47
48
49
 
# File 'lib/ooxl/ooxl.rb', line 45

def [](text)
  # immediately treat as cell range if an exclamation point is detected
  # otherwise, normally load a sheet
  text.include?('!') ? load_cell_range(text) : sheet(text)
end

#each ⇒ Object 



26
27
28
29
30
 
# File 'lib/ooxl/ooxl.rb', line 26

def each
  sheets.each do |sheet_name|
    yield sheet(sheet_name)
  end
end

#fetch_comments(sheet_index) ⇒ Object 



66
67
68
69
70
 
# File 'lib/ooxl/ooxl.rb', line 66

def fetch_comments(sheet_index)
  final_sheet_index = sheet_index+1
  relationship = @relationships[final_sheet_index.to_s]
  @comments[relationship.comment_id] if relationship.present?
end

#load_cell_range(range_text) ⇒ Object 



57
58
59
60
61
62
63
64
 
# File 'lib/ooxl/ooxl.rb', line 57

def load_cell_range(range_text)
  # get the sheet name => 'Lists'
  sheet_name = range_text.gsub(/[\$\']/, '').scan(/^[^!]*/).first
  # fetch the cell range => '$A$1:$A$6'
  cell_range = range_text.gsub(/\$/, '').scan(/(?<=!).+/).first
  # get the sheet object and fetch the cells in range
  sheet(sheet_name).list_values_from_cell_range(cell_range)
end

#named_range(name) ⇒ Object 



51
52
53
54
55
 
# File 'lib/ooxl/ooxl.rb', line 51

def named_range(name)
  # yes_no => 'Lists'!$A$1:$A$6
  defined_name = @workbook.defined_names[name]
  load_cell_range(defined_name) if defined_name.present?
end

#parse_spreadsheet_contents(spreadsheet) ⇒ Object 



72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
 
# File 'lib/ooxl/ooxl.rb', line 72

def parse_spreadsheet_contents(spreadsheet)
  shared_strings = []
  Zip::File.open(spreadsheet) do |spreadsheet_zip|
    spreadsheet_zip.each do |entry|
      case entry.name
      when /xl\/worksheets\/sheet(\d+)?\.xml/
        sheet_id = entry.name.scan(/xl\/worksheets\/sheet(\d+)?\.xml/).flatten.first
        @sheets[sheet_id] = OOXL::Sheet.new(entry.get_input_stream.read, shared_strings)
      when /xl\/styles\.xml/
        @styles = OOXL::Styles.load_from_stream(entry.get_input_stream.read)
      when /xl\/comments(\d+)?\.xml/
        comment_id = entry.name.scan(/xl\/comments(\d+)\.xml/).flatten.first
        @comments[comment_id] = OOXL::Comments.load_from_stream(entry.get_input_stream.read)
      when "xl/sharedStrings.xml"
        Nokogiri.XML(entry.get_input_stream.read).remove_namespaces!.xpath('sst/si').each do |shared_string_node|
          shared_strings << shared_string_node.at('t').text
        end
      when "xl/workbook.xml"
        @workbook = OOXL::Workbook.load_from_stream(entry.get_input_stream.read)
      when /xl\/worksheets\/_rels\/sheet\d+\.xml\.rels/
        sheet_id = entry.name.scan(/sheet(\d+)/).flatten.first
        @relationships[sheet_id] = Relationships.new(entry.get_input_stream.read)
      else
        # unsupported for now..
      end
    end
  end
end

#sheet(sheet_name) ⇒ Object 



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

def sheet(sheet_name)
  sheet_index = @workbook.sheets.index { |sheet| sheet[:name] == sheet_name}
  raise "No #{sheet_name} in workbook." if sheet_index.nil?
  sheet = @sheets.fetch((sheet_index+1).to_s)

  # shared variables
  sheet.name = sheet_name
  sheet.comments = fetch_comments(sheet_index)
  sheet.styles = @styles
  sheet.defined_names = @workbook.defined_names
  sheet
end

#sheets(skip_hidden: false) ⇒ Object 



19
20
21
22
23
24
 
# File 'lib/ooxl/ooxl.rb', line 19

def sheets(skip_hidden: false)
  @workbook.sheets.map do |sheet|
    next if sheet[:state] != 'visible' &&  (@options[:skip_hidden_sheets] || skip_hidden)
    sheet[:name]
  end.compact
end