Class: RubyXL::Workbook

Inherits:
Object
  • Object
show all
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.



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

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

  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.



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

def application
  @application
end

#appversion ⇒ Object

Returns the value of attribute appversion.



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

def appversion
  @appversion
end

#borders ⇒ Object

Returns the value of attribute borders.



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

def borders
  @borders
end

#calc_chain ⇒ Object

Returns the value of attribute calc_chain.



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

def calc_chain
  @calc_chain
end

#cell_style_xfs ⇒ Object

Returns the value of attribute cell_style_xfs.



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

def cell_style_xfs
  @cell_style_xfs
end

#cell_styles ⇒ Object

Returns the value of attribute cell_styles.



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

def cell_styles
  @cell_styles
end

#cell_xfs ⇒ Object

Returns the value of attribute cell_xfs.



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

def cell_xfs
  @cell_xfs
end

#colors ⇒ Object

Returns the value of attribute colors.



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

def colors
  @colors
end

#company ⇒ Object

Returns the value of attribute company.



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

def company
  @company
end

#created_at ⇒ Object

Returns the value of attribute created_at.



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

def created_at
  @created_at
end

#creator ⇒ Object

Returns the value of attribute creator.



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

def creator
  @creator
end

#date1904 ⇒ Object

Returns the value of attribute date1904.



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

def date1904
  @date1904
end

#defined_names ⇒ Object

Returns the value of attribute defined_names.



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

def defined_names
  @defined_names
end

#drawings ⇒ Object

Returns the value of attribute drawings.



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

def drawings
  @drawings
end

#external_links ⇒ Object

Returns the value of attribute external_links.



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

def external_links
  @external_links
end

#filepath ⇒ Object

Returns the value of attribute filepath.



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

def filepath
  @filepath
end

#fills ⇒ Object

Returns the value of attribute fills.



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

def fills
  @fills
end

#fonts ⇒ Object

Returns the value of attribute fonts.



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

def fonts
  @fonts
end

#macros ⇒ Object

Returns the value of attribute macros.



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

def macros
  @macros
end

#modified_at ⇒ Object

Returns the value of attribute modified_at.



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

def modified_at
  @modified_at
end

#modifier ⇒ Object

Returns the value of attribute modifier.



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

def modifier
  @modifier
end

#num_fmts ⇒ Object

Returns the value of attribute num_fmts.



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

def num_fmts
  @num_fmts
end

#num_strings ⇒ Object

Returns the value of attribute num_strings.



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

def num_strings
  @num_strings
end

#printer_settings ⇒ Object

Returns the value of attribute printer_settings.



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

def printer_settings
  @printer_settings
end

#shared_strings ⇒ Object

Returns the value of attribute shared_strings.



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

def shared_strings
  @shared_strings
end

#shared_strings_XML ⇒ Object

Returns the value of attribute shared_strings_XML.



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

def shared_strings_XML
  @shared_strings_XML
end

#size ⇒ Object

Returns the value of attribute size.



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

def size
  @size
end

#style_corrector ⇒ Object

Returns the value of attribute style_corrector.



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

def style_corrector
  @style_corrector
end

#worksheet_rels ⇒ Object

Returns the value of attribute worksheet_rels.



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

def worksheet_rels
  @worksheet_rels
end

#worksheets ⇒ Object

Returns the value of attribute worksheets.



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

def worksheets
  @worksheets
end

Instance Method Details

#[](worksheet) ⇒ Object

allows easier access to worksheets



74
75
76
 
# File 'lib/rubyXL/workbook.rb', line 74

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

#date_to_num(date) ⇒ Object 



195
196
197
198
199
200
201
202
203
204
 
# File 'lib/rubyXL/workbook.rb', line 195

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

#get_fill_color(xf_attributes) ⇒ Object 



241
242
243
244
245
246
247
 
# File 'lib/rubyXL/workbook.rb', line 241

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



218
219
220
221
222
223
224
225
226
227
228
 
# File 'lib/rubyXL/workbook.rb', line 218

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



233
234
235
236
237
238
239
 
# File 'lib/rubyXL/workbook.rb', line 233

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

#num_to_date(num) ⇒ Object 



206
207
208
209
210
211
212
213
214
215
 
# File 'lib/rubyXL/workbook.rb', line 206

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)



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

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