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.



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

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
  @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.



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

def application
  @application
end

#appversion ⇒ Object

Returns the value of attribute appversion.



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

def appversion
  @appversion
end

#borders ⇒ Object

Returns the value of attribute borders.



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

def borders
  @borders
end

#calc_chain ⇒ Object

Returns the value of attribute calc_chain.



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

def calc_chain
  @calc_chain
end

#cell_style_xfs ⇒ Object

Returns the value of attribute cell_style_xfs.



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

def cell_style_xfs
  @cell_style_xfs
end

#cell_styles ⇒ Object

Returns the value of attribute cell_styles.



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

def cell_styles
  @cell_styles
end

#cell_xfs ⇒ Object

Returns the value of attribute cell_xfs.



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

def cell_xfs
  @cell_xfs
end

#colors ⇒ Object

Returns the value of attribute colors.



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

def colors
  @colors
end

#company ⇒ Object

Returns the value of attribute company.



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

def company
  @company
end

#created_at ⇒ Object

Returns the value of attribute created_at.



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

def created_at
  @created_at
end

#creator ⇒ Object

Returns the value of attribute creator.



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

def creator
  @creator
end

#date1904 ⇒ Object

Returns the value of attribute date1904.



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

def date1904
  @date1904
end

#defined_names ⇒ Object

Returns the value of attribute defined_names.



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

def defined_names
  @defined_names
end

#drawings ⇒ Object

Returns the value of attribute drawings.



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

def drawings
  @drawings
end

#external_links ⇒ Object

Returns the value of attribute external_links.



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

def external_links
  @external_links
end

#filepath ⇒ Object

Returns the value of attribute filepath.



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

def filepath
  @filepath
end

#fills ⇒ Object

Returns the value of attribute fills.



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

def fills
  @fills
end

#fonts ⇒ Object

Returns the value of attribute fonts.



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

def fonts
  @fonts
end

#macros ⇒ Object

Returns the value of attribute macros.



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

def macros
  @macros
end

#modified_at ⇒ Object

Returns the value of attribute modified_at.



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

def modified_at
  @modified_at
end

#modifier ⇒ Object

Returns the value of attribute modifier.



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

def modifier
  @modifier
end

#num_fmts ⇒ Object

Returns the value of attribute num_fmts.



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

def num_fmts
  @num_fmts
end

#num_strings ⇒ Object

Returns the value of attribute num_strings.



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

def num_strings
  @num_strings
end

#printer_settings ⇒ Object

Returns the value of attribute printer_settings.



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

def printer_settings
  @printer_settings
end

#shared_strings ⇒ Object

Returns the value of attribute shared_strings.



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

def shared_strings
  @shared_strings
end

#shared_strings_XML ⇒ Object

Returns the value of attribute shared_strings_XML.



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

def shared_strings_XML
  @shared_strings_XML
end

#size ⇒ Object

Returns the value of attribute size.



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

def size
  @size
end

#style_corrector ⇒ Object

Returns the value of attribute style_corrector.



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

def style_corrector
  @style_corrector
end

#worksheet_rels ⇒ Object

Returns the value of attribute worksheet_rels.



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

def worksheet_rels
  @worksheet_rels
end

#worksheets ⇒ Object

Returns the value of attribute worksheets.



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

def worksheets
  @worksheets
end

Instance Method Details

#[](worksheet) ⇒ Object

allows easier access to worksheets



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

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

#get_fill_color(xf_attributes) ⇒ Object 



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

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



192
193
194
195
196
197
198
199
200
201
202
 
# File 'lib/rubyXL/workbook.rb', line 192

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



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

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

#write(filepath = @filepath) ⇒ Object

filepath of xlsx file (including file itself)



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

def write(filepath=@filepath)
  validate_before_write
  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