Class: Dullard::Workbook

Inherits:
Object
  • Object
show all
Defined in:
lib/dullard/reader.rb

Constant Summary collapse

FORMATS =

Code borrowed from Roo (github.com/hmcgowan/roo/blob/master/lib/roo/excelx.rb) Some additional formats added by Paul Hendryx ([email protected]) that are common in LibreOffice.

{
  'general' => :float,
  '0' => :float,
  '0.00' => :float,
  '#,##0' => :float,
  '#,##0.00' => :float,
  '0%' => :float,
  '0.00%' => :float,
  '0.00E+00' => :float,
  '# ?/?' => :float, #??? TODO:
  '# ??/??' => :float, #??? TODO:
  'mm-dd-yy' => :date,
  'd-mmm-yy' => :date,
  'd-mmm' => :date,
  'mmm-yy' => :date,
  'h:mm am/pm' => :date,
  'h:mm:ss am/pm' => :date,
  'h:mm' => :time,
  'h:mm:ss' => :time,
  'm/d/yy h:mm' => :date,
  '#,##0 ;(#,##0)' => :float,
  '#,##0 ;[red](#,##0)' => :float,
  '#,##0.00;(#,##0.00)' => :float,
  '#,##0.00;[red](#,##0.00)' => :float,
  'mm:ss' => :time,
  '[h]:mm:ss' => :time,
  'mmss.0' => :time,
  '##0.0e+0' => :float,
  '@' => :float,
  #-- zusaetzliche Formate, die nicht standardmaessig definiert sind:
  "yyyy\\-mm\\-dd" => :date,
  'dd/mm/yy' => :date,
  'hh:mm:ss' => :time,
  "dd/mm/yy\\ hh:mm" => :datetime,
  'm/d/yy' => :date,
  'mm/dd/yy' => :date,
  'mm/dd/yyyy' => :date,
}
STANDARD_FORMATS = 
{ 
  0 => 'General',
  1 => '0',
  2 => '0.00',
  3 => '#,##0',
  4 => '#,##0.00',
  9 => '0%',
  10 => '0.00%',
  11 => '0.00E+00',
  12 => '# ?/?',
  13 => '# ??/??',
  14 => 'mm-dd-yy',
  15 => 'd-mmm-yy',
  16 => 'd-mmm',
  17 => 'mmm-yy',
  18 => 'h:mm AM/PM',
  19 => 'h:mm:ss AM/PM',
  20 => 'h:mm',
  21 => 'h:mm:ss',
  22 => 'm/d/yy h:mm',
  37 => '#,##0 ;(#,##0)',
  38 => '#,##0 ;[Red](#,##0)',
  39 => '#,##0.00;(#,##0.00)',
  40 => '#,##0.00;[Red](#,##0.00)',
  45 => 'mm:ss',
  46 => '[h]:mm:ss',
  47 => 'mmss.0',
  48 => '##0.0E+0',
  49 => '@',
}

Instance Method Summary collapse

Constructor Details

#initialize(file, user_defined_formats = {}) ⇒ Workbook

Returns a new instance of Workbook.



84
85
86
87
88
89
90
91
92
93
 
# File 'lib/dullard/reader.rb', line 84

def initialize(file, user_defined_formats = {})
  @file = file
  begin
    @zipfs = Zip::File.open(@file)
  rescue Zip::Error => e
    raise Dullard::Error, e.message
  end
  @user_defined_formats = user_defined_formats
  read_styles
end

Instance Method Details

#attribute2format(s) ⇒ Object

Code borrowed from Roo (github.com/hmcgowan/roo/blob/master/lib/roo/excelx.rb) convert internal excelx attribute to a format



163
164
165
166
167
168
169
170
171
172
173
174
 
# File 'lib/dullard/reader.rb', line 163

def attribute2format(s)
  id = @cell_xfs[s.to_i].to_i
  result = @num_formats[id]

  if result == nil
    if STANDARD_FORMATS.has_key? id
      result = STANDARD_FORMATS[id]
    end
  end

  result.downcase
end

#close ⇒ Object 



191
192
193
 
# File 'lib/dullard/reader.rb', line 191

def close
  @zipfs.close
end

#format2type(format) ⇒ Object

Code borrowed from Roo (github.com/hmcgowan/roo/blob/master/lib/roo/excelx.rb)



177
178
179
180
181
182
183
184
185
 
# File 'lib/dullard/reader.rb', line 177

def format2type(format)
  if FORMATS.has_key? format
    FORMATS[format]
  elsif @user_defined_formats.has_key? format
    @user_defined_formats[format]
  else
    :float
  end
end

#read_string_table ⇒ Object 



110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
 
# File 'lib/dullard/reader.rb', line 110

def read_string_table
  return [] unless @zipfs.file.exist? Dullard::SharedStringPath

  begin
    shared_string = @zipfs.file.open(Dullard::SharedStringPath)
  rescue Zip::Error
    raise Dullard::Error, 'Invalid file, could not open shared string file.'
  end

  entry = ''
  @string_table = []
  Nokogiri::XML::Reader(shared_string).each do |node|
    if node.name == "si" and node.node_type == Nokogiri::XML::Reader::TYPE_ELEMENT
      entry = ''
    elsif node.name == "si" and node.node_type == Nokogiri::XML::Reader::TYPE_END_ELEMENT
      @string_table << entry
    elsif node.value?
      entry << node.value
    end
  end
  @string_table
end

#read_styles ⇒ Object 



133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
 
# File 'lib/dullard/reader.rb', line 133

def read_styles
  @num_formats = {}
  @cell_xfs = []
  return unless @zipfs.file.exist? Dullard::StylesPath

  begin
    doc = Nokogiri::XML(@zipfs.file.open(Dullard::StylesPath))
  rescue Zip::Error
    raise Dullard::Error, 'Invalid file, could not open styles'
  end
  
  doc.css('/styleSheet/numFmts/numFmt').each do |numFmt|
    if numFmt.attributes['numFmtId'] && numFmt.attributes['formatCode']
      numFmtId = numFmt.attributes['numFmtId'].value.to_i
      formatCode = numFmt.attributes['formatCode'].value
      @num_formats[numFmtId] = formatCode
    end
  end

  doc.css('/styleSheet/cellXfs/xf').each do |xf|
    if xf.attributes['numFmtId']
      numFmtId = xf.attributes['numFmtId'].value.to_i
      @cell_xfs << numFmtId
    end
  end
end

#sheets ⇒ Object 



95
96
97
98
99
100
101
102
103
104
 
# File 'lib/dullard/reader.rb', line 95

def sheets
  begin
    workbook = Nokogiri::XML::Document.parse(@zipfs.file.open('xl/workbook.xml'))
  rescue Zip::Error
    raise Dullard::Error, 'Invalid file, could not open xl/workbook.xml'
  end
  @sheets = workbook.css('sheet').each_with_index.map do |n, i|
    Dullard::Sheet.new(self, n.attr('name'), n.attr('sheetId'), i+1)
  end
end

#string_table ⇒ Object 



106
107
108
 
# File 'lib/dullard/reader.rb', line 106

def string_table
  @string_table ||= read_string_table
end

#zipfs ⇒ Object 



187
188
189
 
# File 'lib/dullard/reader.rb', line 187

def zipfs
  @zipfs
end