Class: RubyXL::Workbook

Inherits:
Object
  • Object
show all
Includes:
Enumerable
Defined in:
lib/rubyXL/workbook.rb

Constant Summary collapse

APPLICATION = 
'Microsoft Macintosh Excel'
APPVERSION = 
'12.0000'
SHEET_NAME = 
'Sheet1'

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(worksheets = [], filepath = nil, creator = nil, modifier = nil, created_at = nil, company = '', application = APPLICATION, appversion = APPVERSION, date1904 = 0) ⇒ Workbook

Returns a new instance of Workbook.



27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
 
# File 'lib/rubyXL/workbook.rb', line 27

def initialize(worksheets=[], filepath=nil, creator=nil, modifier=nil, created_at=nil,
               company='', application=APPLICATION,
               appversion=APPVERSION, date1904=0)
  if worksheets.nil? || worksheets.empty?
    @worksheets       = [Worksheet.new(self,SHEET_NAME)]
  else
    @worksheets       = worksheets
  end
  @filepath           = filepath
  @creator            = creator
  @modifier           = modifier
  @company            = company
  @application        = application
  @appversion         = appversion
  @num_fmts           = nil
  @fonts              = nil
  @fills              = nil
  @borders            = nil
  @cell_xfs           = nil
  @cell_style_xfs     = nil
  @cell_styles        = nil
  @shared_strings     = nil
  @calc_chain         = nil #unnecessary?
  @num_strings        = 0 #num strings total
  @size               = 0 #num strings in shared_strings array
  @date1904           = date1904 > 0
  @external_links     = nil
  @style_corrector    = nil
  @drawings           = nil
  @worksheet_rels     = nil
  @printer_settings   = nil
  @macros             = nil
  @colors             = nil
  @shared_strings_XML = nil
  @defined_names      = nil
  @column_lookup_hash = {}

  begin
    @created_at       = DateTime.parse(created_at).strftime('%Y-%m-%dT%TZ')
  rescue
    t = Time.now
    @created_at       = t.strftime('%Y-%m-%dT%TZ')
  end
  @modified_at        = @created_at

  fill_styles()
  fill_shared_strings()
end

Instance Attribute Details

#application ⇒ Object

Returns the value of attribute application.



17
18
19
 
# File 'lib/rubyXL/workbook.rb', line 17

def application
  @application
end

#appversion ⇒ Object

Returns the value of attribute appversion.



17
18
19
 
# File 'lib/rubyXL/workbook.rb', line 17

def appversion
  @appversion
end

#borders ⇒ Object

Returns the value of attribute borders.



17
18
19
 
# File 'lib/rubyXL/workbook.rb', line 17

def borders
  @borders
end

#calc_chain ⇒ Object

Returns the value of attribute calc_chain.



17
18
19
 
# File 'lib/rubyXL/workbook.rb', line 17

def calc_chain
  @calc_chain
end

#cell_style_xfs ⇒ Object

Returns the value of attribute cell_style_xfs.



17
18
19
 
# File 'lib/rubyXL/workbook.rb', line 17

def cell_style_xfs
  @cell_style_xfs
end

#cell_styles ⇒ Object

Returns the value of attribute cell_styles.



17
18
19
 
# File 'lib/rubyXL/workbook.rb', line 17

def cell_styles
  @cell_styles
end

#cell_xfs ⇒ Object

Returns the value of attribute cell_xfs.



17
18
19
 
# File 'lib/rubyXL/workbook.rb', line 17

def cell_xfs
  @cell_xfs
end

#colors ⇒ Object

Returns the value of attribute colors.



17
18
19
 
# File 'lib/rubyXL/workbook.rb', line 17

def colors
  @colors
end

#column_lookup_hash ⇒ Object

Returns the value of attribute column_lookup_hash.



17
18
19
 
# File 'lib/rubyXL/workbook.rb', line 17

def column_lookup_hash
  @column_lookup_hash
end

#company ⇒ Object

Returns the value of attribute company.



17
18
19
 
# File 'lib/rubyXL/workbook.rb', line 17

def company
  @company
end

#created_at ⇒ Object

Returns the value of attribute created_at.



17
18
19
 
# File 'lib/rubyXL/workbook.rb', line 17

def created_at
  @created_at
end

#creator ⇒ Object

Returns the value of attribute creator.



17
18
19
 
# File 'lib/rubyXL/workbook.rb', line 17

def creator
  @creator
end

#date1904 ⇒ Object

Returns the value of attribute date1904.



17
18
19
 
# File 'lib/rubyXL/workbook.rb', line 17

def date1904
  @date1904
end

#defined_names ⇒ Object

Returns the value of attribute defined_names.



17
18
19
 
# File 'lib/rubyXL/workbook.rb', line 17

def defined_names
  @defined_names
end

#drawings ⇒ Object

Returns the value of attribute drawings.



17
18
19
 
# File 'lib/rubyXL/workbook.rb', line 17

def drawings
  @drawings
end

#external_links ⇒ Object

Returns the value of attribute external_links.



17
18
19
 
# File 'lib/rubyXL/workbook.rb', line 17

def external_links
  @external_links
end

#filepath ⇒ Object

Returns the value of attribute filepath.



17
18
19
 
# File 'lib/rubyXL/workbook.rb', line 17

def filepath
  @filepath
end

#fills ⇒ Object

Returns the value of attribute fills.



17
18
19
 
# File 'lib/rubyXL/workbook.rb', line 17

def fills
  @fills
end

#fonts ⇒ Object

Returns the value of attribute fonts.



17
18
19
 
# File 'lib/rubyXL/workbook.rb', line 17

def fonts
  @fonts
end

#macros ⇒ Object

Returns the value of attribute macros.



17
18
19
 
# File 'lib/rubyXL/workbook.rb', line 17

def macros
  @macros
end

#modified_at ⇒ Object

Returns the value of attribute modified_at.



17
18
19
 
# File 'lib/rubyXL/workbook.rb', line 17

def modified_at
  @modified_at
end

#modifier ⇒ Object

Returns the value of attribute modifier.



17
18
19
 
# File 'lib/rubyXL/workbook.rb', line 17

def modifier
  @modifier
end

#num_fmts ⇒ Object

Returns the value of attribute num_fmts.



17
18
19
 
# File 'lib/rubyXL/workbook.rb', line 17

def num_fmts
  @num_fmts
end

#num_strings ⇒ Object

Returns the value of attribute num_strings.



17
18
19
 
# File 'lib/rubyXL/workbook.rb', line 17

def num_strings
  @num_strings
end

#printer_settings ⇒ Object

Returns the value of attribute printer_settings.



17
18
19
 
# File 'lib/rubyXL/workbook.rb', line 17

def printer_settings
  @printer_settings
end

#shared_strings ⇒ Object

Returns the value of attribute shared_strings.



17
18
19
 
# File 'lib/rubyXL/workbook.rb', line 17

def shared_strings
  @shared_strings
end

#shared_strings_XML ⇒ Object

Returns the value of attribute shared_strings_XML.



17
18
19
 
# File 'lib/rubyXL/workbook.rb', line 17

def shared_strings_XML
  @shared_strings_XML
end

#size ⇒ Object

Returns the value of attribute size.



17
18
19
 
# File 'lib/rubyXL/workbook.rb', line 17

def size
  @size
end

#style_corrector ⇒ Object

Returns the value of attribute style_corrector.



17
18
19
 
# File 'lib/rubyXL/workbook.rb', line 17

def style_corrector
  @style_corrector
end

#worksheet_rels ⇒ Object

Returns the value of attribute worksheet_rels.



17
18
19
 
# File 'lib/rubyXL/workbook.rb', line 17

def worksheet_rels
  @worksheet_rels
end

#worksheets ⇒ Object

Returns the value of attribute worksheets.



17
18
19
 
# File 'lib/rubyXL/workbook.rb', line 17

def worksheets
  @worksheets
end

Instance Method Details

#[](worksheet) ⇒ Object

allows easier access to worksheets



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

def [](worksheet)
  return worksheets[worksheet]
end

#date_num_fmt?(num_fmt) ⇒ Boolean

Returns:

  • (Boolean) 


224
225
226
227
228
229
230
 
# File 'lib/rubyXL/workbook.rb', line 224

def date_num_fmt?(num_fmt)
  @num_fmt_date_hash ||= {}
  if @num_fmt_date_hash[num_fmt].nil?
    @num_fmt_date_hash[num_fmt] = is_date_format?(num_fmt)
  end
  return @num_fmt_date_hash[num_fmt]
end

#date_to_num(date) ⇒ Object 



202
203
204
205
206
207
208
209
210
211
 
# File 'lib/rubyXL/workbook.rb', line 202

def date_to_num(date)
  return nil if date.nil?
  if @date1904
    compare_date = DateTime.parse('December 31, 1903')
  else
    compare_date = DateTime.parse('December 31, 1899')
  end
  # add one day to compare date for erroneous 1900 leap year compatibility
  date.ajd + 1 - compare_date.ajd
end

#each ⇒ Object 



81
82
83
 
# File 'lib/rubyXL/workbook.rb', line 81

def each
  worksheets.each{|i| yield i}
end

#get_fill_color(xf_attributes) ⇒ Object 



312
313
314
315
316
317
318
 
# File 'lib/rubyXL/workbook.rb', line 312

def get_fill_color(xf_attributes)
  if @fills[xf_attributes[:fillId]].nil? || @fills[xf_attributes[:fillId]][:fill].nil? || @fills[xf_attributes[:fillId]][:fill][:patternFill].nil? || @fills[xf_attributes[:fillId]][:fill][:patternFill][:fgColor].nil?
    'ffffff' #white
  else
    @fills[xf_attributes[:fillId]][:fill][:patternFill][:fgColor][:attributes][:rgb]
  end
end

#get_style(style_index) ⇒ Object

gets style object from style array given index



289
290
291
292
293
294
295
296
297
298
299
 
# File 'lib/rubyXL/workbook.rb', line 289

def get_style(style_index)
  if !@cell_xfs[:xf].is_a?Array
    @cell_xfs[:xf] = [@cell_xfs[:xf]]
  end

  xf_obj = @cell_xfs[:xf]
  if xf_obj.is_a?Array
    xf_obj = xf_obj[Integer(style_index)]
  end
  xf_obj
end

#get_style_attributes(xf_obj) ⇒ Object

gets attributes of above style object necessary because can take the form of hash or array, based on odd behavior of Nokogiri



304
305
306
307
308
309
310
 
# File 'lib/rubyXL/workbook.rb', line 304

def get_style_attributes(xf_obj)
  if xf_obj.is_a?Array
    xf = xf_obj[1]
  else
    xf = xf_obj[:attributes]
  end
end

#is_date_format?(num_fmt) ⇒ Boolean

Returns:

  • (Boolean) 


232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
 
# File 'lib/rubyXL/workbook.rb', line 232

def is_date_format?(num_fmt)
  skip_chars = ['$', '-', '+', '/', '(', ')', ':', ' ']
  num_chars = ['0', '#', '?']
  non_date_formats = ['0.00E+00', '##0.0E+0', 'General', 'GENERAL', 'general', '@']
  date_chars = ['y','m','d','h','s']

  state = 0
  s = ''
  num_fmt.split(//).each do |c|
    if state == 0
      if c == '"'
        state = 1
      elsif ['\\', '_', '*'].include?(c)
        state = 2
      elsif skip_chars.include?(c)
        next
      else
        s << c
      end
    elsif state == 1
      if c == '"'
        state = 0
      end
    elsif state == 2
      state = 0
    end
  end
  s.gsub!(/\[[^\]]*\]/, '')
  if non_date_formats.include?(s)
    return false
  end
  separator = ';'
  got_sep = 0
  date_count = 0
  num_count = 0
  s.split(//).each do |c|
    if date_chars.include?(c)
      date_count += 1
    elsif num_chars.include?(c)
      num_count += 1
    elsif c == separator
      got_sep = 1
    end
  end
  if date_count > 0 && num_count == 0
    return true
  elsif num_count > 0 && date_count == 0
    return false
  elsif date_count
    # ambiguous result
  elsif got_sep == 0
    # constant result
  end
  return date_count > num_count
end

#num_to_date(num) ⇒ Object 



213
214
215
216
217
218
219
220
221
222
 
# File 'lib/rubyXL/workbook.rb', line 213

def num_to_date(num)
  return nil if num.nil?
  if @date1904
    compare_date = DateTime.parse('December 31, 1903')
  else
    compare_date = DateTime.parse('December 31, 1899')
  end
  # subtract one day to compare date for erroneous 1900 leap year compatibility
  compare_date - 1 + num
end

#write(filepath = @filepath) ⇒ Object

filepath of xlsx file (including file itself)



86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
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
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
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
 
# File 'lib/rubyXL/workbook.rb', line 86

def write(filepath=@filepath)
  validate_before_write
  if !(filepath =~ /(.+)\.xls(x|m)/)
    raise "Only xlsx and xlsm files are supported. Unsupported type for file: #{filepath}"
  end
  dirpath = ''
  extension = 'xls'
  if(filepath =~ /((.|\s)*)\.xls(x|m)$/)
    dirpath = $1.to_s()
    extension += $3.to_s
  end
  filename = ''
  if(filepath =~ /\/((.|\s)*)\/((.|\s)*)\.xls(x|m)$/)
    filename = $3.to_s()
  end

  #creates zip file, writes each type of file to zip folder
  #zips package and renames it to xlsx.
  zippath = File.join(dirpath, filename + '.zip')
  File.unlink(zippath) if File.exists?(zippath)
  FileUtils.mkdir_p(File.join(dirpath,zippath))
  Zip::ZipFile.open(zippath, Zip::ZipFile::CREATE) do |zipfile|
    writer = Writer::ContentTypesWriter.new(dirpath,self)
    zipfile.get_output_stream('[Content_Types].xml') {|f| f.puts(writer.write())}

    writer = Writer::RootRelsWriter.new(dirpath,self)
    zipfile.get_output_stream(File.join('_rels','.rels')) {|f| f.puts(writer.write())}

    writer = Writer::AppWriter.new(dirpath,self)
    zipfile.get_output_stream(File.join('docProps','app.xml')) {|f| f.puts(writer.write())}

    writer = Writer::CoreWriter.new(dirpath,self)
    zipfile.get_output_stream(File.join('docProps','core.xml')) {|f| f.puts(writer.write())}

    writer = Writer::ThemeWriter.new(dirpath,self)
    zipfile.get_output_stream(File.join('xl','theme','theme1.xml')) {|f| f.puts(writer.write())}

    writer = Writer::WorkbookRelsWriter.new(dirpath,self)
    zipfile.get_output_stream(File.join('xl','_rels','workbook.xml.rels')) {|f| f.puts(writer.write())}

    writer = Writer::WorkbookWriter.new(dirpath,self)
    zipfile.get_output_stream(File.join('xl','workbook.xml')) {|f| f.puts(writer.write())}

    writer = Writer::StylesWriter.new(dirpath,self)
    zipfile.get_output_stream(File.join('xl','styles.xml')) {|f| f.puts(writer.write())}

    unless @shared_strings.nil?
      writer = Writer::SharedStringsWriter.new(dirpath,self)
      zipfile.get_output_stream(File.join('xl','sharedStrings.xml')) {|f| f.puts(writer.write())}
    end

    #preserves external links (exactly, no modification allowed)
    unless @external_links.nil?
      #-1 because of rels
      1.upto(@external_links.size-1) do |i|
        zipfile.get_output_stream(
          File.join('xl','externalLinks',"externalLink#{i}.xml")) {|f|
            f.puts(@external_links[i])
          }
      end
      @external_links['rels'].each_index do |i|
        unless @external_links['rels'][i].nil?
          zipfile.get_output_stream(
            File.join('xl','externalLinks','_rels',"externalLink#{i}.xml.rels")) {|f|
              f.puts(@external_links['rels'][i])
            }
        end
      end
    end

    #preserves drawings (exactly, no modification allowed)
    unless @drawings.nil?
      1.upto(@drawings.size) do |i|
        zipfile.get_output_stream(
        File.join('xl','drawings',"vmlDrawing#{i}.vml")) {|f|
          f.puts(@drawings[i])
        }
      end
    end

    unless @printer_settings.nil?
      1.upto(@printer_settings.size) do |i|
        zipfile.get_output_stream(
        File.join('xl','printerSettings',"printerSettings#{i}.bin")) {|f|
          f.puts(@printer_settings[i])
        }
      end
    end

    unless @worksheet_rels.nil?
      1.upto(@worksheet_rels.size) do |i|
        zipfile.get_output_stream(
        File.join('xl','worksheets','_rels',"sheet#{i}.xml.rels")) {|f|
          f.puts(@worksheet_rels[i])
        }
      end
    end

    unless @macros.nil?
      zipfile.get_output_stream(File.join('xl','vbaProject.bin')) {|f| f.puts(@macros)}
    end

    @worksheets.each_with_index do |sheet,i|
      writer = Writer::WorksheetWriter.new(dirpath,self,i)
      zipfile.get_output_stream(File.join('xl','worksheets',"sheet#{i+1}.xml")) {|f| f.puts(writer.write())}
    end
  end

  FileUtils.cp(zippath,File.join(dirpath,filename+".#{extension}"))
  FileUtils.cp(File.join(dirpath,filename+".#{extension}"),filepath)
  if File.exist?(filepath)
    FileUtils.rm_rf(dirpath)
  end
  return filepath
end