Class: RubyExcel::Workbook

Inherits:
Object
  • Object
show all
Includes:
Enumerable
Defined in:
lib/rubyexcel.rb,
lib/rubyexcel/excel_tools.rb

Overview

A Workbook which can hold multiple Sheets

Constant Summary collapse

ExcelToolsMethods =

Names of methods which require win32ole

[ :disable_formulas!, :documents_path, :dump_to_sheet, :get_excel, :get_workbook, :import, :make_sheet_pretty, :save_excel, :to_excel, :to_safe_format, :to_safe_format! ]

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(name = 'Output') ⇒ Workbook

Creates a RubyExcel::Workbook instance.



46
47
48
49
 
# File 'lib/rubyexcel.rb', line 46

def initialize( name = 'Output' )
  @name = name
  @sheets = []
end

Dynamic Method Handling

This class handles dynamic methods through the method_missing method

#method_missing(m, *args, &block) ⇒ Object

Don’t require Windows-specific libraries unless the relevant methods are called



161
162
163
164
165
166
167
168
 
# File 'lib/rubyexcel.rb', line 161

def method_missing(m, *args, &block)
  if ExcelToolsMethods.include?( m )
    require_relative 'rubyexcel/excel_tools.rb'
    send( m, *args, &block )
  else
    super
  end
end

Instance Attribute Details

#name ⇒ Object

Get and set the Workbook name



40
41
42
 
# File 'lib/rubyexcel.rb', line 40

def name
  @name
end

Instance Method Details

#<<(other) ⇒ Object

Appends an object to the Workbook

Parameters:



57
58
59
60
61
62
63
64
65
 
# File 'lib/rubyexcel.rb', line 57

def <<( other )
  case other
  when Workbook ; other.each { |sht| sht.workbook = self; @sheets << sht }
  when Sheet    ; @sheets << other; other.workbook = self
  when Array    ; load( other )
  else          ; fail TypeError, "Unsupported Type: #{ other.class }"
  end
  self
end

#add(ref = nil) ⇒ RubyExcel::Sheet Also known as: add_sheet

Adds a Sheet to the Workbook.

If no argument is given, names the Sheet 'Sheet' + total number of Sheets

Examples:

sheet = workbook.add
#=> RubyExcel::Sheet:0x2b3a0b8: Sheet1

Parameters:

  • ref (nil, RubyExcel::Sheet, String) (defaults to: nil) —

    the identifier or Sheet to add

Returns:



78
79
80
81
82
83
84
85
86
87
 
# File 'lib/rubyexcel.rb', line 78

def add( ref=nil )
  case ref
  when nil    ; s = Sheet.new( 'Sheet' + ( @sheets.count + 1 ).to_s, self )
  when Sheet  ; ( s = ref ).workbook = self
  when String ; s = Sheet.new( ref, self )
  else        ; fail TypeError, "Unsupported Type: #{ ref.class }"
  end
  @sheets << s
  s
end

#clear_all ⇒ Object Also known as: delete_all

Removes all Sheets from the Workbook



94
95
96
 
# File 'lib/rubyexcel.rb', line 94

def clear_all
  @sheets = []; self
end

#delete(ref) ⇒ Object

Removes Sheet(s) from the Workbook

Parameters:



105
106
107
108
109
110
111
112
113
114
 
# File 'lib/rubyexcel.rb', line 105

def delete( ref )
  case ref
  when Fixnum ; @sheets.delete_at( ref - 1 )
  when String ; @sheets.reject! { |s| s.name == ref }
  when Regexp ; @sheets.reject! { |s| s.name =~ ref }
  when Sheet  ; @sheets.reject! { |s| s == ref }
  else        ; fail ArgumentError, 'Unrecognised Argument Type: ' + ref.class.to_s
  end
  self
end

#disable_formulas! ⇒ Object

Add a single quote before any equals sign in the data.

Disables any Strings which would have been interpreted as formulas by Excel



38
39
40
41
42
43
44
 
# File 'lib/rubyexcel/excel_tools.rb', line 38

def disable_formulas!
  sheets { |s| s.rows { |r| r.each_cell { |ce|
    if ce.value.is_a?( String ) && ce.value[0] == '='
      ce.value = ce.value.sub( /\A=/,"'=" )
    end
  } } }; self
end

#documents_path ⇒ String

Find the Windows “Documents” or “My Documents” path, or return the present working directory if it can’t be found.

Returns:

  • (String) 


52
53
54
 
# File 'lib/rubyexcel/excel_tools.rb', line 52

def documents_path
  Win32::Registry::HKEY_CURRENT_USER.open( 'SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Explorer\\Shell Folders' )['Personal'] rescue Dir.pwd.gsub('/','\\')
end

#dump_to_sheet(data, sheet = nil) ⇒ WIN32OLE::Worksheet

Drop a multidimensional Array into an Excel Sheet

Parameters:

  • data (Array<Array>) —

    the data to place in the Sheet

  • sheet (WIN32OLE::Worksheet, nil) (defaults to: nil) —

    optional WIN32OLE Worksheet to use

Returns:

  • (WIN32OLE::Worksheet) —

    the Worksheet containing the data



64
65
66
67
68
69
 
# File 'lib/rubyexcel/excel_tools.rb', line 64

def dump_to_sheet( data, sheet=nil )
  data.is_a?( Array ) or fail ArgumentError, "Invalid data type: #{ data.class }"
  sheet ||= get_workbook.sheets(1)
  sheet.range( sheet.cells( 1, 1 ), sheet.cells( data.length, data.max_by(&:length).length ) ).value = data
  sheet
end

#dup ⇒ RubyExcel::Workbook

Return a copy of self

Returns:



122
123
124
125
126
 
# File 'lib/rubyexcel.rb', line 122

def dup
  wb = Workbook.new
  self.each { |s| wb.add s.dup }
  wb
end

#each ⇒ Object

Yields each Sheet.



132
133
134
135
 
# File 'lib/rubyexcel.rb', line 132

def each
  return to_enum( :each ) unless block_given?
  @sheets.each { |s| yield s }
end

#empty? ⇒ Boolean

Check whether the workbook has Sheets

Returns:

  • (Boolean) —

    if there are any Sheets in the Workbook



143
144
145
 
# File 'lib/rubyexcel.rb', line 143

def empty?
  @sheets.empty?
end

#get_excel(invisible = false) ⇒ WIN32OLE::Excel

Open or connect to an Excel instance

Parameters:

  • invisible (Boolean) (defaults to: false) —

    leave Excel invisible if creating a new instance

Returns:

  • (WIN32OLE::Excel) —

    the first available Excel application



78
79
80
81
82
 
# File 'lib/rubyexcel/excel_tools.rb', line 78

def get_excel( invisible = false )
  excel = WIN32OLE::connect( 'excel.application' ) rescue WIN32OLE::new( 'excel.application' )
  excel.visible = true unless invisible
  excel
end

#get_workbook(excel = nil, invisible = false) ⇒ WIN32OLE::Workbook

Create a new Excel Workbook

Parameters:

  • excel (WIN32OLE::Excel, nil) (defaults to: nil) —

    an Excel object to use

  • invisible (Boolean) (defaults to: false) —

    leave Excel invisible if creating a new instance

Returns:

  • (WIN32OLE::Workbook) —

    the new Excel Workbook



92
93
94
95
96
97
 
# File 'lib/rubyexcel/excel_tools.rb', line 92

def get_workbook( excel=nil, invisible = false )
  excel ||= get_excel( invisible )
  wb = excel.workbooks.add
  ( ( wb.sheets.count.to_i ) - 1 ).times { |time| wb.sheets(2).delete }
  wb
end

#import(other, sheetname = nil, keep_formulas = false) ⇒ self

Import a WIN32OLE Object as a Workbook or Sheet

Parameters:

  • other (WIN32OLE::Workbook, WIN32OLE::Sheet, String) —

    The WIN32OLE Object, either Sheet or Workbook, to import, or a path to the file.

  • sheetname (String) (defaults to: nil) —

    the name of a specific Sheet to import.

  • keep_formulas (Boolean) (defaults to: false) —

    Retain Excel formulas rather than importing their current values

Returns:

  • (self) —

    self with the data and name(s) imported.



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
 
# File 'lib/rubyexcel/excel_tools.rb', line 108

def import( other, sheetname=nil, keep_formulas=false )
  operation = ( keep_formulas ? :formula : :value )

  if other.is_a?( String )
  
    # Filename

    File.exists?( other ) || fail( ArgumentError, "Unable to find file: #{ other }" )
    
    #Open the file with Excel

    excel = WIN32OLE.new( 'excel.application' )
    excel.displayalerts = false
    wb = excel.workbooks.open({'filename'=> other, 'readOnly' => true})
    
    # Only one sheet, or the entire Workbook?

    if sheetname
      add( sheetname ).load( wb.sheets( sheetname ).usedrange.send( operation ) )
    else
      self.name = File.basename( other, '.*' )
      wb.sheets.each { |sh| add( sh.name ).load( sh.usedrange.send( operation ) ) }
    end
    
    # Cleanup

    wb.close
    excel.quit
    
  elsif !other.respond_to?( :ole_respond_to? )
  
    fail ArgumentError, "Invalid input: #{other.class}"
    
  elsif other.ole_respond_to?( :sheets )
  
    # Workbook

    
    # Only one sheet, or the entire Workbook?

    if sheetname
      add( sheetname ).load( other.sheets( sheetname ).usedrange.send( operation ) )
    else
      self.name = File.basename( other.name, '.*' )
      other.sheets.each { |sh| add( sh.name ).load( sh.usedrange.send( operation ) ) }
    end
    
  elsif other.ole_respond_to?( :usedrange )
  
    # Sheet

    add( other.name ).load( other.usedrange.send( operation ) )
    
  else
  
    fail ArgumentError, "Object not recognised as a WIN32OLE Workbook or Sheet.\n#{other.inspect}"
    
  end
  
  self
end

#load(input_data, header_rows = 1) ⇒ Object

Shortcut to create a Sheet and fill it with data

Parameters:

  • input_data (Array<Array>, Hash<Hash>) —

    the data to fill the Sheet with

  • Fixnum (]) —

    ixnum] header_rows the number of Rows to be treated as headers



153
154
155
 
# File 'lib/rubyexcel.rb', line 153

def load( *args )
  add.load( *args )
end

#make_sheet_pretty(sheet) ⇒ WIN32OLE::Worksheet

Take an Excel Sheet and standardise some of the formatting

Parameters:

  • sheet (WIN32OLE::Worksheet) —

    the Sheet to add formatting to

Returns:

  • (WIN32OLE::Worksheet) —

    the sheet with formatting added



170
171
172
173
174
175
176
177
178
179
 
# File 'lib/rubyexcel/excel_tools.rb', line 170

def make_sheet_pretty( sheet )
  c = sheet.cells
  c.rowheight = 15
  c.entireColumn.autoFit
  c.horizontalAlignment = -4108
  c.verticalAlignment = -4108
  sheet.UsedRange.Columns.each { |col| col.ColumnWidth = 30 if col.ColumnWidth > 50 }
  RubyExcel.borders( sheet.usedrange, 1, true )
  sheet
end

#respond_to?(m) ⇒ Boolean

Allow for certain method_missing calls

Returns:

  • (Boolean) 


174
175
176
177
178
179
180
181
 
# File 'lib/rubyexcel.rb', line 174

def respond_to?( m )
  if ExcelToolsMethods.include?( m )
    true
  else
    super
  end
  
end

#save_excel(filename = nil, invisible = false) ⇒ WIN32OLE::Workbook

Save the RubyExcel::Workbook as an Excel Workbook

Parameters:

  • filename (String) (defaults to: nil) —

    the filename to save as

  • invisible (Boolean) (defaults to: false) —

    leave Excel invisible if creating a new instance

Returns:

  • (WIN32OLE::Workbook) —

    the Workbook, saved as filename.



189
190
191
192
193
194
195
196
197
198
 
# File 'lib/rubyexcel/excel_tools.rb', line 189

def save_excel( filename = nil, invisible = false )
  filename ||= name
  filename = filename.gsub('/','\\')
  unless filename.include?('\\')
    filename = documents_path + '\\' + filename 
  end
  wb = to_excel( invisible )
  wb.saveas filename
  wb
end

#sheets(ref = nil) {|RubyExcel::Sheet| ... } ⇒ RubyExcel::Sheet, Enumerator

Select a Sheet or iterate through them

Parameters:

  • ref (Fixnum, String, Regexp, nil) (defaults to: nil) —

    the reference to select a Sheet by

Yields:

  • (RubyExcel::Sheet) —

    yields each sheet, if there is no argument and a block is given

Returns:

  • (RubyExcel::Sheet) —

    if a search term was given

  • (Enumerator) —

    if nil or no argument given



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

def sheets( ref=nil )
  if ref.nil?
    return to_enum (:each) unless block_given?
    each { |s| yield s }
  else
    case ref
    when Fixnum ; @sheets[ ref - 1 ]
    when String ; @sheets.find { |s| s.name =~ /^#{ ref }$/i }
    when Regexp ; @sheets.find { |s| s.name =~ ref }
    end
  end
end

#sort(&block) ⇒ Object

#sort!



207
208
209
 
# File 'lib/rubyexcel.rb', line 207

def sort( &block )
  dup.sort!( &block )
end

#sort!(&block) ⇒ Object

Sort Sheets according to a block



215
216
217
 
# File 'lib/rubyexcel.rb', line 215

def sort!( &block )
  @sheets = @sheets.sort( &block )
end

#sort_by(&block) ⇒ Object

#sort_by!



221
222
223
 
# File 'lib/rubyexcel.rb', line 221

def sort_by( &block )
  dup.sort_by!( &block )
end

#sort_by!(&block) ⇒ Object

Sort Sheets by an attribute given in a block



229
230
231
 
# File 'lib/rubyexcel.rb', line 229

def sort_by!( &block )
  @sheets = @sheets.sort_by( &block )
end

#to_excel(invisible = false) ⇒ WIN32OLE::Workbook

Output the RubyExcel::Workbook to Excel

Parameters:

  • invisible (Boolean) (defaults to: false) —

    leave Excel invisible if creating a new instance

Returns:

  • (WIN32OLE::Workbook) —

    the Workbook in Excel



207
208
209
210
211
212
213
214
215
216
217
218
219
220
 
# File 'lib/rubyexcel/excel_tools.rb', line 207

def to_excel( invisible = false )
  self.sheets.count == sheets.map(&:name).uniq.length or fail NoMethodError, 'Duplicate sheet name'
  wb = get_workbook( nil, true )
  wb.parent.displayAlerts = false
  first_time = true
  each do |s|
    sht = ( first_time ? wb.sheets(1) : wb.sheets.add( { 'after' => wb.sheets( wb.sheets.count ) } ) ); first_time = false
    sht.name = s.name
    make_sheet_pretty( dump_to_sheet( s.to_a, sht ) )
  end
  wb.sheets(1).select rescue nil
  wb.application.visible = true unless invisible
  wb
end

#to_html ⇒ Object

The Workbook as a group of HTML Tables



237
238
239
 
# File 'lib/rubyexcel.rb', line 237

def to_html
  map(&:to_html).join('</br>')
end

#to_safe_format ⇒ Object

#to_safe_format!



224
225
226
 
# File 'lib/rubyexcel/excel_tools.rb', line 224

def to_safe_format
  dup.to_safe_format!
end

#to_safe_format! ⇒ Object

Standardise the data for safe export to Excel.

Set each cell contents to a string and remove leading equals signs.



233
234
235
236
237
238
239
240
241
 
# File 'lib/rubyexcel/excel_tools.rb', line 233

def to_safe_format!
  sheets { |s| s.rows { |r| r.map! { |v|
    if v.is_a?( String )
      v[0] == '=' ? v.sub( /\A=/,"'=" ) : v
    else
      v.to_s
    end
  } } }; self
end