Class: OoxmlParser::XLSXWorkbook

Inherits:
CommonDocumentStructure show all
Includes:
WorkbookHelpers
Defined in:
lib/ooxml_parser/xlsx_parser/workbook.rb

Overview

Class for storing XLSX Workbook

Instance Attribute Summary collapse

Attributes inherited from CommonDocumentStructure

#content_types, #default_font_size, #default_font_style, #default_font_typeface, #file_path, #root_subfolder, #unpacked_folder, #xmls_stack

Attributes inherited from OOXMLDocumentObject

#parent

Instance Method Summary collapse

Methods included from WorkbookHelpers

#with_data?

Methods inherited from CommonDocumentStructure

#add_to_xmls_stack, #current_xml, #get_link_from_rels

Methods inherited from OOXMLDocumentObject

#==, #boolean_attribute_value, #parse_xml, #with_data?

Methods included from OoxmlObjectAttributeHelper

#attribute_enabled?, #option_enabled?

Methods included from OoxmlDocumentObjectHelper

#to_hash

Constructor Details

#initialize(params = {}) ⇒ XLSXWorkbook

Returns a new instance of XLSXWorkbook.



40
41
42
43
44
45
46
47
 
# File 'lib/ooxml_parser/xlsx_parser/workbook.rb', line 40

def initialize(params = {})
  @worksheets = []
  @sheets = []
  @pivot_caches = []
  @pivot_table_definitions = []
  @defined_names = []
  super
end

Instance Attribute Details

#defined_names ⇒ Array<DefinedName> (readonly)

Returns list of defined names.

Returns:



34
35
36
 
# File 'lib/ooxml_parser/xlsx_parser/workbook.rb', line 34

def defined_names
  @defined_names
end

#pivot_caches ⇒ Array<PivotCache>

Returns list of pivot caches.

Returns:



30
31
32
 
# File 'lib/ooxml_parser/xlsx_parser/workbook.rb', line 30

def pivot_caches
  @pivot_caches
end

#pivot_table_definitions ⇒ Array<PivotTableDefintion>

Returns list of pivot table defitions.

Returns:

  • (Array<PivotTableDefintion>) —

    list of pivot table defitions



32
33
34
 
# File 'lib/ooxml_parser/xlsx_parser/workbook.rb', line 32

def pivot_table_definitions
  @pivot_table_definitions
end

#relationships ⇒ Relationships

Returns rels of book.

Returns:



24
25
26
 
# File 'lib/ooxml_parser/xlsx_parser/workbook.rb', line 24

def relationships
  @relationships
end

#shared_strings_table ⇒ SharedStringTable

Returns styles of book.

Returns:



28
29
30
 
# File 'lib/ooxml_parser/xlsx_parser/workbook.rb', line 28

def shared_strings_table
  @shared_strings_table
end

#sheets ⇒ Array<Sheet> (readonly)

Returns list of sheets.

Returns:

  • (Array<Sheet>) —

    list of sheets



20
21
22
 
# File 'lib/ooxml_parser/xlsx_parser/workbook.rb', line 20

def sheets
  @sheets
end

#style_sheet ⇒ StyleSheet

Returns styles of book.

Returns:



26
27
28
 
# File 'lib/ooxml_parser/xlsx_parser/workbook.rb', line 26

def style_sheet
  @style_sheet
end

#theme ⇒ PresentationTheme

Returns theme of Workbook.

Returns:



22
23
24
 
# File 'lib/ooxml_parser/xlsx_parser/workbook.rb', line 22

def theme
  @theme
end

#workbook_properties ⇒ WorkbookProperties (readonly)

Returns workbook properties.

Returns:



36
37
38
 
# File 'lib/ooxml_parser/xlsx_parser/workbook.rb', line 36

def workbook_properties
  @workbook_properties
end

#workbook_protection ⇒ WorkbookProtection (readonly)

Returns protection of workbook structure.

Returns:



38
39
40
 
# File 'lib/ooxml_parser/xlsx_parser/workbook.rb', line 38

def workbook_protection
  @workbook_protection
end

#worksheets ⇒ Object

Returns the value of attribute worksheets.



18
19
20
 
# File 'lib/ooxml_parser/xlsx_parser/workbook.rb', line 18

def worksheets
  @worksheets
end

Instance Method Details

#all_formula_values(precision = 14) ⇒ Array, String

Get all values of formulas.

Parameters:

  • precision (Fixnum) (defaults to: 14) —

    of formulas counting

Returns:

  • (Array, String) —

    all formulas



77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
 
# File 'lib/ooxml_parser/xlsx_parser/workbook.rb', line 77

def all_formula_values(precision = 14)
  formulas = []
  worksheets.each do |c_sheet|
    next unless c_sheet

    c_sheet.rows.each do |c_row|
      next unless c_row

      c_row.cells.each do |c_cell|
        next unless c_cell
        next unless c_cell.formula
        next if c_cell.formula.empty?

        text = c_cell.raw_text
        if StringHelper.numeric?(text)
          text = text.to_f.round(10).to_s[0..precision]
        elsif StringHelper.complex?(text)
          complex_number = Complex(text.tr(',', '.'))
          real_part = complex_number.real
          real_rounded = real_part.to_f.round(10).to_s[0..precision].to_f

          imag_part = complex_number.imag
          imag_rounded = imag_part.to_f.round(10).to_s[0..precision].to_f
          complex_rounded = Complex(real_rounded, imag_rounded)
          text = complex_rounded.to_s
        end
        formulas << text
      end
    end
  end
  formulas
end

#cell(column, row, sheet = 0) ⇒ XlsxCell

Return cell by coordinates

Parameters:

  • column (String, Integer) —

    number or numeric digit of column

  • row (Integer) —

    row to find

  • sheet (Integer) (defaults to: 0) —

    number of sheet

Returns:



54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
 
# File 'lib/ooxml_parser/xlsx_parser/workbook.rb', line 54

def cell(column, row, sheet = 0)
  column = Coordinates.new(row, column).column_number unless StringHelper.numeric?(column.to_s)

  if StringHelper.numeric?(sheet.to_s)
    row = @worksheets[sheet].rows[row.to_i - 1]
    return nil if row.nil?

    return row.cells[column.to_i - 1]
  elsif sheet.is_a?(String)
    @worksheets.each do |worksheet|
      next unless worksheet.name == sheet
      next unless worksheet.rows[row.to_i - 1]

      return worksheet.rows[row.to_i - 1].cells[column.to_i - 1]
    end
    return nil
  end
  raise "Error. Wrong sheet value: #{sheet}"
end

#parse ⇒ XLSXWorkbook

Parse content of Workbook

Returns:



121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
 
# File 'lib/ooxml_parser/xlsx_parser/workbook.rb', line 121

def parse
  @content_types = ContentTypes.new(parent: self).parse
  @relationships = Relationships.new(parent: self).parse_file("#{root_object.unpacked_folder}xl/_rels/workbook.xml.rels")
  parse_shared_strings
  @root_subfolder = 'xl/'
  root_object.add_to_xmls_stack('xl/workbook.xml')
  @doc = parse_xml(root_object.current_xml)
  @theme = PresentationTheme.new(parent: self).parse("xl/#{link_to_theme_xml}") if link_to_theme_xml
  @style_sheet = StyleSheet.new(parent: self).parse
  @doc.xpath('xmlns:workbook/xmlns:sheets/xmlns:sheet').each do |sheet|
    @sheets << Sheet.new(parent: self).parse(sheet)
    file = @relationships.target_by_id(@sheets.last.id)
    if file.start_with?('worksheets')
      @worksheets << Worksheet.new(parent: self).parse(file)
      @worksheets.last.name = @sheets.last.name
    elsif file.start_with?('chartsheets')
      @worksheets << Chartsheet.new(parent: self).parse(file)
    end
  end
  parse_pivot_cache
  parse_pivot_table
  parse_defined_names
  parse_workbook_properties
  parse_workbook_protection
  root_object.xmls_stack.pop
  self
end

#parse_shared_strings ⇒ Object

Do work for parsing shared strings file



111
112
113
114
115
116
117
 
# File 'lib/ooxml_parser/xlsx_parser/workbook.rb', line 111

def parse_shared_strings
  shared_strings_target = relationships.target_by_type('sharedString')
  return if shared_strings_target.empty?

  shared_string_file = "#{root_object.unpacked_folder}/xl/#{shared_strings_target.first}"
  @shared_strings_table = SharedStringTable.new(parent: self).parse(shared_string_file)
end