Class: OOXL

Inherits:
Object
  • Object
show all
Includes:
Enumerable, ListHelper
Defined in:
lib/ooxl/xl_objects/cell_style_reference.rb,
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/table.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/row_cache.rb,
lib/ooxl/xl_objects/relationships.rb,
lib/ooxl/xl_objects/number_formatting.rb,
lib/ooxl/xl_objects/sheet/data_validation.rb

Overview

<cellStyleXfs count=“4”>

<xf numFmtId="0" fontId="0" fillId="0" borderId="0" />
<xf numFmtId="0" fontId="1" fillId="0" borderId="0" />
<xf numFmtId="0" fontId="39" fillId="0" borderId="0" applyProtection="0" />
<xf numFmtId="9" fontId="1" fillId="0" borderId="0" applyFont="0" applyFill="0" applyBorder="0" applyAlignment="0" applyProtection="0" />

</cellStyleXfs>

Defined Under Namespace

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

Constant Summary collapse

VERSION = 
"0.0.1.5.4"

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Methods included from ListHelper

#list_values

Constructor Details

#initialize(filepath = nil, contents: nil, **options) ⇒ OOXL

Returns a new instance of OOXL.



6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
# File 'lib/ooxl/ooxl.rb', line 6

def initialize(filepath = nil, contents: nil, **options)
  @workbook = nil
  @sheets = {}
  @styles = []
  @comments = {}
  @workbook_relationships = nil
  @sheet_relationships = {}
  @options = options
  @tables = []

  @filename = filepath && File.basename(filepath)
  if contents.present?
    parse_spreadsheet_contents(contents)
  elsif filepath.present?
    parse_spreadsheet_file(filepath)
  else
    raise 'no file path or contents were provided'
  end
end

Instance Attribute Details

#filename ⇒ Object (readonly)

Returns the value of attribute filename.



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

def filename
  @filename
end

Class Method Details

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



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

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

.parse(spreadsheet_contents, options = {}) ⇒ Object 



30
31
32
33
 
# File 'lib/ooxl/ooxl.rb', line 30

def self.parse(spreadsheet_contents, options={})
  spreadsheet_contents.force_encoding('ASCII-8BIT') if spreadsheet_contents.respond_to?(:force_encoding)
  new(nil, contents: spreadsheet_contents, **options)
end

Instance Method Details

#[](text) ⇒ Object 



63
64
65
66
67
 
# File 'lib/ooxl/ooxl.rb', line 63

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 



42
43
44
45
46
 
# File 'lib/ooxl/ooxl.rb', line 42

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

#fetch_comments(sheet_index) ⇒ Object 



90
91
92
93
 
# File 'lib/ooxl/ooxl.rb', line 90

def fetch_comments(sheet_index)
  relationship = @sheet_relationships[sheet_index]
  @comments[relationship.comment_id] if relationship.present?
end

#load_cell_range(range_text) ⇒ Object 



81
82
83
84
85
86
87
88
 
# File 'lib/ooxl/ooxl.rb', line 81

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, clean_range: false) ⇒ Object 



69
70
71
72
73
74
75
 
# File 'lib/ooxl/ooxl.rb', line 69

def named_range(name, clean_range: false)
  # yes_no => 'Lists'!$A$1:$A$6
  defined_name = @workbook.defined_names[name]
  defined_name = defined_name.gsub(/\[.+\]/, '').squish if clean_range

  load_cell_range(defined_name) if defined_name.present?
end

#parse_spreadsheet_contents(file_contents) ⇒ Object 



99
100
101
102
 
# File 'lib/ooxl/ooxl.rb', line 99

def parse_spreadsheet_contents(file_contents)
  # open_buffer works for strings and IO streams
  Zip::File.open_buffer(file_contents) { |zip| parse_zip(zip) }
end

#parse_spreadsheet_file(file_path) ⇒ Object 



95
96
97
 
# File 'lib/ooxl/ooxl.rb', line 95

def parse_spreadsheet_file(file_path)
  Zip::File.open(file_path) { |zip| parse_zip(zip) }
end

#parse_zip(spreadsheet_zip) ⇒ Object 



104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
 
# File 'lib/ooxl/ooxl.rb', line 104

def parse_zip(spreadsheet_zip)
  shared_strings = []
  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, @options)
    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.xpath('r/t|t').map { |value_node| value_node.text}.join('')
      end
    when /xl\/tables\/.*?/i
      @tables << OOXL::Table.new(entry.get_input_stream.read)
    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
      @sheet_relationships[sheet_id] = Relationships.new(entry.get_input_stream.read)
    when /xl\/_rels\/workbook\.xml\.rels/
      @workbook_relationships = Relationships.new(entry.get_input_stream.read)
    else
      # unsupported for now..
    end
  end
end

#sheet(sheet_name) ⇒ Object 



48
49
50
51
52
53
54
55
56
57
58
59
60
61
 
# File 'lib/ooxl/ooxl.rb', line 48

def sheet(sheet_name)
  sheet_meta = @workbook.sheets.find { |sheet| sheet[:name] == sheet_name }
  raise "No #{sheet_name} in workbook." if sheet_meta.nil?

  sheet_index = @workbook_relationships[sheet_meta[:relationship_id]].scan(/\d+/).first
  sheet = @sheets.fetch(sheet_index)

  # 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 



35
36
37
38
39
40
 
# File 'lib/ooxl/ooxl.rb', line 35

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

#table(name) ⇒ Object 



77
78
79
 
# File 'lib/ooxl/ooxl.rb', line 77

def table(name)
  @tables.find { |tbl| tbl.name == name}
end