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

Instance Method Summary collapse

Constructor Details

#initialize ⇒ Workbook

Creates a RubyExcel::Workbook instance.



36
37
38
 
# File 'lib/rubyexcel.rb', line 36

def initialize
  @sheets = []
end

Instance Method Details

#<<(other) ⇒ Object

Appends an object to the Workbook

Parameters:



46
47
48
49
50
51
52
53
54
 
# File 'lib/rubyexcel.rb', line 46

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:



67
68
69
70
71
72
73
74
75
76
 
# File 'lib/rubyexcel.rb', line 67

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



83
84
85
 
# File 'lib/rubyexcel.rb', line 83

def clear_all
  @sheets = []; self
end

#delete(ref) ⇒ Object

Removes Sheet(s) from the Workbook

Parameters:



94
95
96
97
98
99
100
101
102
103
 
# File 'lib/rubyexcel.rb', line 94

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

#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



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

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:



111
112
113
114
115
 
# File 'lib/rubyexcel.rb', line 111

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

#each ⇒ Object

Yields each Sheet.



182
183
184
185
 
# File 'lib/rubyexcel.rb', line 182

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



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

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



54
55
56
57
58
 
# File 'lib/rubyexcel/excel_tools.rb', line 54

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



68
69
70
71
72
73
 
# File 'lib/rubyexcel/excel_tools.rb', line 68

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



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

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



82
83
84
85
86
87
88
89
90
91
 
# File 'lib/rubyexcel/excel_tools.rb', line 82

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

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

Save the RubyExcel::Workbook as an Excel Workbook

Parameters:

  • filename (String) (defaults to: 'Output') —

    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.



101
102
103
104
105
106
107
108
109
110
111
 
# File 'lib/rubyexcel/excel_tools.rb', line 101

def save_excel( filename = 'Output', invisible = false )
  filename = filename.gsub('/','\\')
  unless filename.include?('\\')
    keypath = 'SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Explorer\\Shell Folders'
    documents = Win32::Registry::HKEY_CURRENT_USER.open(keypath)['Personal'] rescue Dir.pwd.gsub('/','\\')
    filename = documents + '\\' + filename 
  end
  wb = to_excel( invisible )
  wb.saveas filename
  wb
end

#sheets(ref = nil) ⇒ RubyExcel::Sheet, Enumerator

Select a Sheet or iterate through them

Parameters:

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

    the reference to select a Sheet by

Returns:

  • (RubyExcel::Sheet) —

    if a search term was given

  • (Enumerator) —

    if nil or no argument given



145
146
147
148
 
# File 'lib/rubyexcel.rb', line 145

def sheets( ref=nil )
  return to_enum (:each) if ref.nil?
  ref.is_a?( Fixnum ) ? @sheets[ ref - 1 ] : @sheets.find { |s| s.name =~ /^#{ ref }$/i }
end

#sort(&block) ⇒ Object

#sort!



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

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

#sort!(&block) ⇒ Object

Sort Sheets according to a block



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

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

#sort_by(&block) ⇒ Object

#sort_by!



166
167
168
 
# File 'lib/rubyexcel.rb', line 166

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

#sort_by!(&block) ⇒ Object

Sort Sheets by an attribute given in a block



174
175
176
 
# File 'lib/rubyexcel.rb', line 174

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



120
121
122
123
124
125
126
127
128
129
130
131
132
133
 
# File 'lib/rubyexcel/excel_tools.rb', line 120

def to_excel( invisible = false )
  self.sheets.count == self.sheets.map(&:name).uniq.length or fail NoMethodError, 'Duplicate sheet name'
  wb = get_workbook( nil, true )
  wb.parent.displayAlerts = false
  first_time = true
  self.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