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, :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.



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

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



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

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

#nameObject

Get and set the Workbook name



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

def name
  @name
end

Instance Method Details

#<<(other) ⇒ Object

Appends an object to the Workbook

Parameters:



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

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:



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

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_allObject Also known as: delete_all

Removes all Sheets from the Workbook



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

def clear_all
  @sheets = []; self
end

#delete(ref) ⇒ Object

Removes Sheet(s) from the Workbook

Parameters:



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

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_pathString

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

#dupRubyExcel::Workbook

Return a copy of self

Returns:



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

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

#eachObject

Yields each Sheet.



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

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



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

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

#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



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

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



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

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)


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

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.



125
126
127
128
129
130
131
132
133
134
# File 'lib/rubyexcel/excel_tools.rb', line 125

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



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

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



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

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

#sort!(&block) ⇒ Object

Sort Sheets according to a block



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

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

#sort_by(&block) ⇒ Object



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

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

#sort_by!(&block) ⇒ Object

Sort Sheets by an attribute given in a block



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

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



143
144
145
146
147
148
149
150
151
152
153
154
155
156
# File 'lib/rubyexcel/excel_tools.rb', line 143

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
  wb.application.visible = true unless invisible
  wb
end

#to_htmlObject

The Workbook as a group of HTML Tables



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

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

#to_safe_formatObject



160
161
162
# File 'lib/rubyexcel/excel_tools.rb', line 160

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.


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

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