class RubyExcel::Workbook

A Workbook which can hold multiple Sheets

Constants

ColumnWidth
ExcelToolsMethods

Names of methods which require win32ole

Attributes

name[RW]

Get and set the Workbook name

standalone[RW]

Set to true to cause the workbook to always create a new instance of Excel when exporting

Public Class Methods

new( name = 'Output' ) click to toggle source

Creates a RubyExcel::Workbook instance.

# File lib/rubyexcel.rb, line 62
def initialize( name = 'Output' )
  self.name = name
  @sheets = []
  self.standalone = false
end

Public Instance Methods

<<( other ) click to toggle source

Appends an object to the Workbook

@param [RubyExcel::Workbook, RubyExcel::Sheet, Array<Array>] other the object to append to the Workbook

# File lib/rubyexcel.rb, line 74
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 = false ) click to toggle source

Adds a Sheet to the Workbook.

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

@example

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

@param [nil, RubyExcel::Sheet, String] ref the identifier or Sheet to add @return [RubyExcel::Sheet] the Sheet which was added

# File lib/rubyexcel.rb, line 95
def add( ref = false )
  case ref
  when false    ; 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
Also aliased as: add_sheet
add_sheet( ref = false )
Alias for: add
clear_all() click to toggle source

Removes all Sheets from the Workbook

# File lib/rubyexcel.rb, line 111
def clear_all
  @sheets = []; self
end
Also aliased as: delete_all
delete( ref=nil ) { |sht| ... } click to toggle source

Removes Sheet(s) from the Workbook

@param [Fixnum, String, Regexp, RubyExcel::Sheet, NilClass] ref the reference or object to remove, or nil if passing a block @yield [RubyExcel::Sheet] yields each sheet, if there is no argument and a block is given

# File lib/rubyexcel.rb, line 123
def delete( ref=nil, &block )

  fail ArgumentError, 'Requires either an argument OR a block' if ref && block_given?
  
  case ref
  when nil    ; @sheets.reject! { |sht| yield sht }
  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
delete_all()
Alias for: clear_all
disable_formulas!() click to toggle source

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

Disables any Strings which would have been interpreted as formulas by Excel
# File lib/rubyexcel/excel_tools.rb, line 48
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() click to toggle source

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

@return [String]

# File lib/rubyexcel/excel_tools.rb, line 62
def documents_path
  RubyExcel.documents_path
end
dump_to_sheet( data, sheet=nil ) click to toggle source

Drop a multidimensional Array into an Excel Sheet

@param [Array<Array>] data the data to place in the Sheet @param [WIN32OLE::Worksheet, nil] sheet optional WIN32OLE Worksheet to use @return [WIN32OLE::Worksheet] the Worksheet containing the data

# File lib/rubyexcel/excel_tools.rb, line 74
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.cells.clear
  sheet.range( sheet.cells( 1, 1 ), sheet.cells( data.length, data.max_by(&:length).length ) ).value = data
  sheet
end
dup() click to toggle source

Return a copy of self

@return [RubyExcel::Workbook]

# File lib/rubyexcel.rb, line 144
def dup
  wb = Workbook.new
  self.each { |s| wb.add s.dup }
  wb
end
each() { |s| ... } click to toggle source

Yields each Sheet.

# File lib/rubyexcel.rb, line 154
def each
  return to_enum( :each ) unless block_given?
  @sheets.each { |s| yield s }
end
empty?() click to toggle source

Check whether the workbook has Sheets

@return [Boolean] if there are any Sheets in the Workbook

# File lib/rubyexcel.rb, line 165
def empty?
  @sheets.empty?
end
export( filename = nil ) click to toggle source

Save the RubyExcel::Workbook as an Excel Workbook and close Excel afterwards

@param [String] filename the filename to save as @return [String] the full filename

# File lib/rubyexcel/excel_tools.rb, line 89
def export( filename = nil )
  prev_standalone = standalone
  self.standalone = true
  filename ||= name
  filename = filename.gsub('/','\\')
  unless filename.include?('\\')
    filename = documents_path + '\\' + filename 
  end
  wb = to_excel( true )
  wb.saveas filename
  filename = wb.fullname
  excel = wb.application
  wb.close
  excel.quit
  self.standalone = prev_standalone
  filename
end
get_excel( invisible = false ) click to toggle source

Open or connect to an Excel instance

@param [Boolean] invisible leave Excel invisible if creating a new instance @return [WIN32OLE::Excel] the first available Excel application

# File lib/rubyexcel/excel_tools.rb, line 114
def get_excel( invisible = false )
  return WIN32OLE::new( 'excel.application' ) if standalone
  excel = WIN32OLE::connect( 'excel.application' ) rescue WIN32OLE::new( 'excel.application' )
  excel.visible = true unless invisible
  excel
end
get_workbook( excel=nil, invisible = false ) click to toggle source

Create a new Excel Workbook

@param [WIN32OLE::Excel, nil] excel an Excel object to use @param [Boolean] invisible leave Excel invisible if creating a new instance @return [WIN32OLE::Workbook] the new Excel Workbook

# File lib/rubyexcel/excel_tools.rb, line 129
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 ) click to toggle source

Import a WIN32OLE Object as a Workbook or Sheet

@param [WIN32OLE::Workbook, WIN32OLE::Sheet, String] other The WIN32OLE Object, either Sheet or Workbook, to import, or a path to the file. @param [String] sheetname the name of a specific Sheet to import. @param [Boolean] keep_formulas Retain Excel formulas rather than importing their current values @return [self] self with the data and name(s) imported.

# File lib/rubyexcel/excel_tools.rb, line 145
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 Excel
    excel = WIN32OLE.new( 'excel.application' )
    excel.displayalerts = false
    
    # Open the file
    begin
      wb = excel.workbooks.open({'filename'=> other, 'readOnly' => true, 'UpdateLinks' => false})
    rescue WIN32OLERuntimeError
      excel.quit
      raise
    end
    
    # 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 ).tap{ |s| s.load( sh.usedrange.send( operation ) ) unless sh.application.worksheetfunction.counta(sh.cells).zero? } }
      
    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 ).tap{ |s| s.load( sh.usedrange.send( operation ) ) unless sh.application.worksheetfunction.counta(sh.cells).zero? }
    else
      self.name = File.basename( other.name, '.*' )
      other.sheets.each { |sh| add( sh.name ).tap{ |s| s.load( sh.usedrange.send( operation ) ) unless sh.application.worksheetfunction.counta(sh.cells).zero? } }
    end
    
  elsif other.ole_respond_to?( :usedrange )
  
    # Sheet
    add( other.name ).tap{ |s| s.load( sh.usedrange.send( operation ) ) unless sh.application.worksheetfunction.counta(sh.cells).zero? }
    
  else
  
    fail ArgumentError, "Object not recognised as a WIN32OLE Workbook or Sheet.\n#{other.inspect}"
    
  end
  
  self
end
load( *args ) click to toggle source

@overload load( input_data, header_rows=1 )

Shortcut to create a Sheet and fill it with data
@param [Array<Array>, Hash<Hash>] input_data the data to fill the Sheet with
@param Fixnum] header_rows the number of Rows to be treated as headers
# File lib/rubyexcel.rb, line 175
def load( *args )
  add.load( *args )
end
make_sheet_pretty( sheet ) click to toggle source

Take an Excel Sheet and standardise some of the formatting

@param [WIN32OLE::Worksheet] sheet the Sheet to add formatting to @return [WIN32OLE::Worksheet] the sheet with formatting added

# File lib/rubyexcel/excel_tools.rb, line 218
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
method_missing(method, *args, &block) click to toggle source

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

Calls superclass method
# File lib/rubyexcel.rb, line 183
def method_missing(method, *args, &block)
  if ExcelToolsMethods.include?( method )
    require_relative 'rubyexcel/excel_tools.rb'
    send( method, *args, &block )
  else
    super
  end
end
respond_to?( method, include_private = false ) click to toggle source

Allow for certain method_missing calls

Calls superclass method
# File lib/rubyexcel.rb, line 196
def respond_to?( method, include_private = false )
  if ExcelToolsMethods.include?( method )
    true
  else
    super
  end
  
end
save_excel( filename = nil, invisible = false ) click to toggle source

Save the RubyExcel::Workbook as an Excel Workbook

@param [String] filename the filename to save as @param [Boolean] invisible leave Excel invisible if creating a new instance @return [WIN32OLE::Workbook] the Workbook, saved as filename.

# File lib/rubyexcel/excel_tools.rb, line 237
def save_excel( filename = nil, invisible = false )
  filename ||= name
  filename = filename.gsub('/','\\')
  unless filename.include?('\\')
    filename = documents_path + '\\' + filename 
  end
  wb = to_excel( ( standalone ? true : invisible ) )
  wb.saveas filename
  wb
end
sheets( ref=nil ) { |s| ... } click to toggle source

Select a Sheet or iterate through them

@param [Fixnum, String, Regexp, nil] ref the reference to select a Sheet by @return [RubyExcel::Sheet] if a search term was given @return [Enumerator] if nil or no argument given @yield [RubyExcel::Sheet] yields each sheet, if there is no argument and a block is given

# File lib/rubyexcel.rb, line 214
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 ) click to toggle source

{Workbook#sort!}

# File lib/rubyexcel.rb, line 229
def sort( &block )
  dup.sort!( &block )
end
sort!( &block ) click to toggle source

Sort Sheets according to a block

# File lib/rubyexcel.rb, line 237
def sort!( &block )
  @sheets = @sheets.sort( &block )
end
sort_by( &block ) click to toggle source

{Workbook#sort_by!}

# File lib/rubyexcel.rb, line 243
def sort_by( &block )
  dup.sort_by!( &block )
end
sort_by!( &block ) click to toggle source

Sort Sheets by an attribute given in a block

# File lib/rubyexcel.rb, line 251
def sort_by!( &block )
  @sheets = @sheets.sort_by( &block )
end
to_excel( invisible = false ) click to toggle source

Output the RubyExcel::Workbook to Excel

@param [Boolean] invisible leave Excel invisible if creating a new instance @return [WIN32OLE::Workbook] the Workbook in Excel

# File lib/rubyexcel/excel_tools.rb, line 255
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() click to toggle source

The Workbook as a group of HTML Tables

# File lib/rubyexcel.rb, line 259
def to_html
  map(&:to_html).join('</br>')
end
to_safe_format() click to toggle source

{Workbook#to_safe_format!}

# File lib/rubyexcel/excel_tools.rb, line 272
def to_safe_format
  dup.to_safe_format!
end
to_safe_format!() click to toggle source

Standardise the data for safe export to Excel.

Set each cell contents to a string and remove leading equals signs.
# File lib/rubyexcel/excel_tools.rb, line 281
def to_safe_format!
  sheets &:to_safe_format!
  self
end