module RubyExcel

Namespace for all RubyExcel Classes and Modules

Namespace for all RubyExcel Classes and Modules

Public Class Methods

borders( range, weight=1, inner=false ) click to toggle source

Add borders to an Excel Range

@param [WIN32OLE::Range] range the Excel Range to add borders to @param [Fixnum] weight the weight of the borders @param [Boolean] inner add inner borders @raise [ArgumentError] ‘First Argument must be WIN32OLE Range’ @return [WIN32OLE::Range] the range initially given

# File lib/rubyexcel/excel_tools.rb, line 19
def self.borders( range, weight=1, inner=false )
  range.ole_respond_to?( :borders ) or fail ArgumentError, 'First Argument must be WIN32OLE Range'
  [0,1,2,3].include?( weight ) or fail ArgumentError, "Invalid line weight #{ weight }. Must be from 0 to 3"
  defined?( ExcelConstants::XlEdgeLeft ) or WIN32OLE.const_load( range.application, ExcelConstants )
  consts = [ ExcelConstants::XlEdgeLeft, ExcelConstants::XlEdgeTop, ExcelConstants::XlEdgeBottom, ExcelConstants::XlEdgeRight, ExcelConstants::XlInsideVertical, ExcelConstants::XlInsideHorizontal ]
  inner or consts.pop(2)
  weight = [ 0, ExcelConstants::XlThin, ExcelConstants::XlMedium, ExcelConstants::XlThick ][ weight ]
  consts.each { |const| weight.zero? ? range.Borders( const ).linestyle = ExcelConstants::XlNone : range.Borders( const ).weight = weight }
  range
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 36
def self.documents_path
  Win32::Registry::HKEY_CURRENT_USER.open( 'SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Explorer\\Shell Folders' )['Personal'] rescue Dir.pwd.gsub('/','\\')
end
import( *args ) click to toggle source

Shortcut to import a WIN32OLE Workbook or Sheet

# File lib/rubyexcel/rubyexcel_components.rb, line 67
def self.import( *args )
  Workbook.new.import( *args )
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 33
def self.method_missing( method, *args, &block )
  if method == :documents_path
    require_relative 'rubyexcel/excel_tools.rb'
    send( method, *args, &block )
  else
    super
  end
end
sample_data() click to toggle source

Example data to use in tests / demos

# File lib/rubyexcel/rubyexcel_components.rb, line 14
def self.sample_data
  [
    [ 'Part',  'Ref1', 'Ref2', 'Qty', 'Cost' ],
    [ 'Type1', 'QT1',  '231',  1,     35.15  ], 
    [ 'Type2', 'QT3',  '123',  1,     40     ], 
    [ 'Type3', 'XT1',  '321',  3,     0.1    ], 
    [ 'Type1', 'XY2',  '132',  1,     30.00  ], 
    [ 'Type4', 'XT3',  '312',  2,     3      ], 
    [ 'Type2', 'QY2',  '213',  1,     99.99  ], 
    [ 'Type1', 'QT4',  '123',  2,     104    ]
  ]
  
end
sample_hash() click to toggle source

Example hash to demonstrate imports

# File lib/rubyexcel/rubyexcel_components.rb, line 32
def self.sample_hash

  {
    Part1: {
      Type1: {
        SubType1: 1, SubType2: 2, SubType3: 3
      },
      Type2: {
        SubType1: 4, SubType2: 5, SubType3: 6
      }
    },
    Part2: {
      Type1: {
        SubType1: 1, SubType2: 2, SubType3: 3
      },
      Type2: {
        SubType1: 4, SubType2: 5, SubType3: 6
      }
    }
  }

end
sample_sheet() click to toggle source

Shortcut to create a Sheet with example data

# File lib/rubyexcel/rubyexcel_components.rb, line 59
def self.sample_sheet
  Workbook.new.load RubyExcel.sample_data
end