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'

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.



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
75
76
77
78
79
80
81
82
83
84
85
 
# File 'lib/rubyXL/workbook.rb', line 31

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

  # Order of sheets in the +worksheets+ array corresponds to the order of pages in Excel UI.
  # SheetId's, rId's, etc. are completely unrelated to ordering.
  @worksheets = worksheets || []

  @worksheets << Worksheet.new(self) if @worksheets.empty?

  @filepath           = filepath
  @creator            = creator
  @modifier           = modifier
  @company            = company
  @application        = application
  @appversion         = appversion
  @num_fmts           = []
  @num_fmts_by_id     = nil
  @fonts              = []
  @fills              = nil
  @borders            = []
  @cell_xfs           = []
  @cell_style_xfs     = []
  @cell_styles        = []
  @shared_strings     = RubyXL::SharedStrings.new
  @calc_chain         = nil #unnecessary?
  @date1904           = date1904 > 0
  @media              = RubyXL::GenericStorage.new(File.join('xl', 'media')).binary
  @external_links     = RubyXL::GenericStorage.new(File.join('xl', 'externalLinks'))
  @external_links_rels= RubyXL::GenericStorage.new(File.join('xl', 'externalLinks', '_rels'))
  @style_corrector    = nil
  @drawings           = RubyXL::GenericStorage.new(File.join('xl', 'drawings'))
  @drawings_rels      = RubyXL::GenericStorage.new(File.join('xl', 'drawings', '_rels'))
  @charts             = RubyXL::GenericStorage.new(File.join('xl', 'charts'))
  @chart_rels         = RubyXL::GenericStorage.new(File.join('xl', 'charts', '_rels'))
  @worksheet_rels     = RubyXL::GenericStorage.new(File.join('xl', 'worksheets', '_rels'))
  @theme              = RubyXL::GenericStorage.new(File.join('xl', 'theme'))
  @printer_settings   = RubyXL::GenericStorage.new(File.join('xl', 'printerSettings')).binary
  @macros             = RubyXL::GenericStorage.new('xl').binary
  @colors             = {}
  @shared_strings_XML = nil
  @defined_names      = []
  @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.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def application
  @application
end

#appversion ⇒ Object

Returns the value of attribute appversion.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def appversion
  @appversion
end

#borders ⇒ Object

Returns the value of attribute borders.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def borders
  @borders
end

#calc_chain ⇒ Object

Returns the value of attribute calc_chain.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def calc_chain
  @calc_chain
end

#cell_style_xfs ⇒ Object

Returns the value of attribute cell_style_xfs.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def cell_style_xfs
  @cell_style_xfs
end

#cell_styles ⇒ Object

Returns the value of attribute cell_styles.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def cell_styles
  @cell_styles
end

#cell_xfs ⇒ Object

Returns the value of attribute cell_xfs.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def cell_xfs
  @cell_xfs
end

#chart_rels ⇒ Object

Returns the value of attribute chart_rels.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def chart_rels
  @chart_rels
end

#charts ⇒ Object

Returns the value of attribute charts.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def charts
  @charts
end

#colors ⇒ Object

Returns the value of attribute colors.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def colors
  @colors
end

#column_lookup_hash ⇒ Object

Returns the value of attribute column_lookup_hash.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def column_lookup_hash
  @column_lookup_hash
end

#company ⇒ Object

Returns the value of attribute company.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def company
  @company
end

#created_at ⇒ Object

Returns the value of attribute created_at.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def created_at
  @created_at
end

#creator ⇒ Object

Returns the value of attribute creator.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def creator
  @creator
end

#date1904 ⇒ Object

Returns the value of attribute date1904.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def date1904
  @date1904
end

#defined_names ⇒ Object

Returns the value of attribute defined_names.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def defined_names
  @defined_names
end

#drawings ⇒ Object

Returns the value of attribute drawings.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def drawings
  @drawings
end

#drawings_rels ⇒ Object

Returns the value of attribute drawings_rels.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def drawings_rels
  @drawings_rels
end

#external_links ⇒ Object

Returns the value of attribute external_links.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def external_links
  @external_links
end

#external_links_rels ⇒ Object

Returns the value of attribute external_links_rels.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def external_links_rels
  @external_links_rels
end

#filepath ⇒ Object

Returns the value of attribute filepath.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def filepath
  @filepath
end

#fills ⇒ Object

Returns the value of attribute fills.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def fills
  @fills
end

#fonts ⇒ Object

Returns the value of attribute fonts.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def fonts
  @fonts
end

#macros ⇒ Object

Returns the value of attribute macros.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def macros
  @macros
end

#media ⇒ Object

Returns the value of attribute media.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def media
  @media
end

#modified_at ⇒ Object

Returns the value of attribute modified_at.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def modified_at
  @modified_at
end

#modifier ⇒ Object

Returns the value of attribute modifier.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def modifier
  @modifier
end

#num_fmts ⇒ Object

Returns the value of attribute num_fmts.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def num_fmts
  @num_fmts
end

#printer_settings ⇒ Object

Returns the value of attribute printer_settings.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def printer_settings
  @printer_settings
end

#shared_strings ⇒ Object (readonly)

Returns the value of attribute shared_strings.



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

def shared_strings
  @shared_strings
end

#shared_strings_XML ⇒ Object

Returns the value of attribute shared_strings_XML.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def shared_strings_XML
  @shared_strings_XML
end

#style_corrector ⇒ Object

Returns the value of attribute style_corrector.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def style_corrector
  @style_corrector
end

#theme ⇒ Object

Returns the value of attribute theme.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def theme
  @theme
end

#worksheet_rels ⇒ Object

Returns the value of attribute worksheet_rels.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def worksheet_rels
  @worksheet_rels
end

#worksheets ⇒ Object

Returns the value of attribute worksheets.



19
20
21
 
# File 'lib/rubyXL/workbook.rb', line 19

def worksheets
  @worksheets
end

Instance Method Details

#[](ind) ⇒ Object

Finds worksheet by its name or numerical index



88
89
90
91
92
93
 
# File 'lib/rubyXL/workbook.rb', line 88

def [](ind)
  case ind
  when Integer then worksheets[ind]
  when String  then worksheets.find { |ws| ws.sheet_name == ind }
  end
end

#add_worksheet(name = nil) ⇒ Object

Create new simple worksheet and add it to the workbook worksheets

Parameters:

  • The (String) —

    name for the new worksheet



98
99
100
101
102
 
# File 'lib/rubyXL/workbook.rb', line 98

def add_worksheet(name = nil)
  new_worksheet = Worksheet.new(self, name)
  worksheets << new_worksheet
  new_worksheet
end

#date_num_fmt?(num_fmt) ⇒ Boolean

Returns:

  • (Boolean) 


170
171
172
173
174
175
176
 
# File 'lib/rubyXL/workbook.rb', line 170

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 



162
163
164
 
# File 'lib/rubyXL/workbook.rb', line 162

def date_to_num(date)
  date && (date.ajd - base_date().ajd).to_i
end

#each ⇒ Object 



104
105
106
 
# File 'lib/rubyXL/workbook.rb', line 104

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

#get_fill_color(xf) ⇒ Object 



239
240
241
242
243
244
 
# File 'lib/rubyXL/workbook.rb', line 239

def get_fill_color(xf)
  fill = @fills[xf.fill_id]
  pattern = fill && fill.pattern_fill
  color = pattern && pattern.fg_color
  color && color.rgb || 'ffffff'
end

#is_date_format?(num_fmt) ⇒ Boolean

Returns:

  • (Boolean) 


178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
 
# File 'lib/rubyXL/workbook.rb', line 178

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

  state = 0
  s = ''

  num_fmt.split(//).each do |c|
    case state 
    when 0 then
      if c == '"'
        state = 1
      elsif ['\\', '_', '*'].include?(c)
        state = 2
      elsif skip_chars.include?(c)
        next
      else
        s << c
      end
    when 1 then
      state = 0 if c == '"'
    when 2 then
      state = 0
    end
  end

  s.gsub!(/\[[^\]]*\]/, '')

  return false if non_date_formats.include?(s)

  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_fmts_by_id ⇒ Object 



108
109
110
111
112
113
114
115
116
 
# File 'lib/rubyXL/workbook.rb', line 108

def num_fmts_by_id
  return @num_fmts_by_id unless @num_fmts_by_id.nil?

  @num_fmts_by_id = {}

  num_fmts.each { |fmt| @num_fmts_by_id[fmt.num_fmt_id] = fmt }

  @num_fmts_by_id
end

#num_to_date(num) ⇒ Object 



166
167
168
 
# File 'lib/rubyXL/workbook.rb', line 166

def num_to_date(num)
  num && (base_date + num)
end

#register_new_border(new_border, old_xf) ⇒ Object 



278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
 
# File 'lib/rubyXL/workbook.rb', line 278

def register_new_border(new_border, old_xf)
  new_xf = old_xf.dup

  unless borders[old_xf.border_id].count == 1 && old_xf.border_id > 0 # If the old border not used anymore, just replace it
    new_xf.border_id = borders.find_index { |x| x == new_border } # Use existing border, if it exists
    new_xf.border_id ||= borders.size # If this border has never existed before, add it to collection.
  end

  borders[old_xf.border_id].count -= 1
  new_border.count += 1
  borders[new_xf.border_id] = new_border

  new_xf.apply_border = true
  new_xf
end

#register_new_fill(new_fill, old_xf) ⇒ Object 



246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
 
# File 'lib/rubyXL/workbook.rb', line 246

def register_new_fill(new_fill, old_xf)
  new_xf = old_xf.dup

  unless fills[old_xf.fill_id].count == 1 && old_xf.fill_id > 2 # If the old fill is not used anymore, just replace it
    new_xf.fill_id = fills.find_index { |x| x == new_fill } # Use existing fill, if it exists
    new_xf.fill_id ||= fills.size # If this fill has never existed before, add it to collection.
  end

  fills[old_xf.fill_id].count -= 1
  new_fill.count += 1
  fills[new_xf.fill_id] = new_fill

  new_xf.apply_fill = true
  new_xf
end

#register_new_font(new_font, old_xf) ⇒ Object 



262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
 
# File 'lib/rubyXL/workbook.rb', line 262

def register_new_font(new_font, old_xf)
  new_xf = old_xf.dup

  unless fonts[old_xf.font_id].count == 1 && old_xf.font_id > 1 # If the old font is not used anymore, just replace it
    new_xf.font_id = fonts.find_index { |x| x == new_font } # Use existing font, if it exists
    new_xf.font_id ||= fonts.size # If this font has never existed before, add it to collection.
  end

  fonts[old_xf.font_id].count -= 1
  new_font.count += 1
  fonts[new_xf.font_id] = new_font

  new_xf.apply_font = true
  new_xf
end

#register_new_xf(new_xf, old_style_index) ⇒ Object 



294
295
296
297
298
299
300
301
302
303
 
# File 'lib/rubyXL/workbook.rb', line 294

def register_new_xf(new_xf, old_style_index)
  new_xf_id = cell_xfs.find_index { |xf| xf == new_xf } # Use existing XF, if it exists
  new_xf_id ||= cell_xfs.size # If this XF has never existed before, add it to collection.

  cell_xfs[old_style_index].count -= 1
  new_xf.count += 1
  cell_xfs[new_xf_id] = new_xf

  new_xf_id
end

#write(filepath = @filepath) ⇒ Object

filepath of xlsx file (including file itself)



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
 
# File 'lib/rubyXL/workbook.rb', line 119

def write(filepath = @filepath)
  validate_before_write

  extension = File.extname(filepath)
  unless %w{.xlsx .xlsm}.include?(extension)
    raise "Only xlsx and xlsm files are supported. Unsupported extension: #{extension}"
  end

  dirpath  = File.dirname(filepath)
  temppath = File.join(dirpath, Dir::Tmpname.make_tmpname([ File.basename(filepath), '.tmp' ], nil))
  FileUtils.mkdir_p(temppath)
  zippath  = File.join(temppath, 'file.zip')

  Zip::File.open(zippath, Zip::File::CREATE) { |zipfile|
    [ Writer::ContentTypesWriter, Writer::RootRelsWriter, Writer::AppWriter, Writer::CoreWriter,
      Writer::ThemeWriter, Writer::WorkbookRelsWriter, Writer::WorkbookWriter, Writer::StylesWriter
    ].each { |writer_class| writer_class.new(self).add_to_zip(zipfile) }
    
    Writer::SharedStringsWriter.new(self).add_to_zip(zipfile) unless @shared_strings.empty?

    [ @media, @external_links, @external_links_rels,
      @drawings, @drawings_rels, @charts, @chart_rels,
      @printer_settings, @worksheet_rels, @macros ].each { |s| s.add_to_zip(zipfile) }

    @worksheets.each_index { |i| Writer::WorksheetWriter.new(self, i).add_to_zip(zipfile) }
  }

  FileUtils.mv(zippath, filepath)
  FileUtils.rm_rf(temppath) if File.exist?(filepath)

  return filepath
end