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%' => :percentage,
  '0.00%' => :percentage,
  '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.



79
80
81
82
83
84
 
# File 'lib/dullard/reader.rb', line 79

def initialize(file, user_defined_formats = {})
  @file = file
  @zipfs = Zip::File.open(@file)
  @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



131
132
133
134
135
136
137
138
139
140
141
142
 
# File 'lib/dullard/reader.rb', line 131

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 



159
160
161
 
# File 'lib/dullard/reader.rb', line 159

def close
  @zipfs.close
end

#format2type(format) ⇒ Object

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



145
146
147
148
149
150
151
152
153
 
# File 'lib/dullard/reader.rb', line 145

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 



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

def read_string_table
  @string_table = []
  entry = ''
  Nokogiri::XML::Reader(@zipfs.file.open("xl/sharedStrings.xml")).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 



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

def read_styles
  doc = Nokogiri::XML(@zipfs.file.open("xl/styles.xml"))
  
  @num_formats = {}
  @cell_xfs = []
  
  doc.css('/styleSheet/numFmts/numFmt').each do |numFmt|
    numFmtId = numFmt.attributes['numFmtId'].value.to_i
    formatCode = numFmt.attributes['formatCode'].value
    @num_formats[numFmtId] = formatCode
  end

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

#sheets ⇒ Object 



86
87
88
89
 
# File 'lib/dullard/reader.rb', line 86

def sheets
  workbook = Nokogiri::XML::Document.parse(@zipfs.file.open("xl/workbook.xml"))
  @sheets = workbook.css("sheet").each_with_index.map {|n,i| Dullard::Sheet.new(self, n.attr("name"), n.attr("sheetId"), i+1) }
end

#string_table ⇒ Object 



91
92
93
 
# File 'lib/dullard/reader.rb', line 91

def string_table
  @string_tabe ||= read_string_table
end

#zipfs ⇒ Object 



155
156
157
 
# File 'lib/dullard/reader.rb', line 155

def zipfs
  @zipfs
end