Class: RubyExcel::Workbook
- Inherits:
-
Object
- Object
- RubyExcel::Workbook
- 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
-
#name ⇒ Object
Get and set the Workbook name.
Instance Method Summary collapse
-
#<<(other) ⇒ Object
Appends an object to the Workbook.
-
#add(ref = false) ⇒ RubyExcel::Sheet
(also: #add_sheet)
Adds a Sheet to the Workbook.
-
#clear_all ⇒ Object
(also: #delete_all)
Removes all Sheets from the Workbook.
-
#delete(ref = nil) {|RubyExcel::Sheet| ... } ⇒ Object
Removes Sheet(s) from the Workbook.
-
#disable_formulas! ⇒ Object
Add a single quote before any equals sign in the data.
-
#documents_path ⇒ String
Find the Windows “Documents” or “My Documents” path, or return the present working directory if it can’t be found.
-
#dump_to_sheet(data, sheet = nil) ⇒ WIN32OLE::Worksheet
Drop a multidimensional Array into an Excel Sheet.
-
#dup ⇒ RubyExcel::Workbook
Return a copy of self.
-
#each ⇒ Object
Yields each Sheet.
-
#empty? ⇒ Boolean
Check whether the workbook has Sheets.
-
#get_excel(invisible = false) ⇒ WIN32OLE::Excel
Open or connect to an Excel instance.
-
#get_workbook(excel = nil, invisible = false) ⇒ WIN32OLE::Workbook
Create a new Excel Workbook.
-
#import(other, sheetname = nil, keep_formulas = false) ⇒ self
Import a WIN32OLE Object as a Workbook or Sheet.
-
#initialize(name = 'Output') ⇒ Workbook
constructor
Creates a RubyExcel::Workbook instance.
-
#load(input_data, header_rows = 1) ⇒ Object
Shortcut to create a Sheet and fill it with data.
-
#make_sheet_pretty(sheet) ⇒ WIN32OLE::Worksheet
Take an Excel Sheet and standardise some of the formatting.
-
#method_missing(method, *args, &block) ⇒ Object
Don’t require Windows-specific libraries unless the relevant methods are called.
-
#respond_to?(method, include_private = false) ⇒ Boolean
Allow for certain method_missing calls.
-
#save_excel(filename = nil, invisible = false) ⇒ WIN32OLE::Workbook
Save the RubyExcel::Workbook as an Excel Workbook.
-
#sheets(ref = nil) {|RubyExcel::Sheet| ... } ⇒ RubyExcel::Sheet, Enumerator
Select a Sheet or iterate through them.
- #sort(&block) ⇒ Object
-
#sort!(&block) ⇒ Object
Sort Sheets according to a block.
- #sort_by(&block) ⇒ Object
-
#sort_by!(&block) ⇒ Object
Sort Sheets by an attribute given in a block.
-
#to_excel(invisible = false) ⇒ WIN32OLE::Workbook
Output the RubyExcel::Workbook to Excel.
-
#to_html ⇒ Object
The Workbook as a group of HTML Tables.
- #to_safe_format ⇒ Object
-
#to_safe_format! ⇒ Object
Standardise the data for safe export to Excel.
Constructor Details
#initialize(name = 'Output') ⇒ Workbook
Creates a RubyExcel::Workbook instance.
59 60 61 62 |
# File 'lib/rubyexcel.rb', line 59 def initialize( name = 'Output' ) @name = name @sheets = [] end |
Dynamic Method Handling
This class handles dynamic methods through the method_missing method
#method_missing(method, *args, &block) ⇒ Object
Don’t require Windows-specific libraries unless the relevant methods are called
179 180 181 182 183 184 185 186 |
# File 'lib/rubyexcel.rb', line 179 def method_missing(method, *args, &block) if ExcelToolsMethods.include?( method ) require_relative 'rubyexcel/excel_tools.rb' send( method, *args, &block ) else super end end |
Instance Attribute Details
#name ⇒ Object
Get and set the Workbook name
53 54 55 |
# File 'lib/rubyexcel.rb', line 53 def name @name end |
Instance Method Details
#<<(other) ⇒ Object
Appends an object to the Workbook
70 71 72 73 74 75 76 77 78 |
# File 'lib/rubyexcel.rb', line 70 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) ⇒ 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
91 92 93 94 95 96 97 98 99 100 |
# File 'lib/rubyexcel.rb', line 91 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 |
#clear_all ⇒ Object Also known as: delete_all
Removes all Sheets from the Workbook
107 108 109 |
# File 'lib/rubyexcel.rb', line 107 def clear_all @sheets = []; self end |
#delete(ref = nil) {|RubyExcel::Sheet| ... } ⇒ Object
Removes Sheet(s) from the Workbook
119 120 121 122 123 124 125 126 127 128 129 130 131 132 |
# File 'lib/rubyexcel.rb', line 119 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 |
#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
48 49 50 51 52 53 54 |
# 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 ⇒ String
Find the Windows “Documents” or “My Documents” path, or return the present working directory if it can’t be found.
62 63 64 |
# File 'lib/rubyexcel/excel_tools.rb', line 62 def documents_path RubyExcel.documents_path end |
#dump_to_sheet(data, sheet = nil) ⇒ WIN32OLE::Worksheet
Drop a multidimensional Array into an Excel Sheet
74 75 76 77 78 79 |
# 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.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
140 141 142 143 144 |
# File 'lib/rubyexcel.rb', line 140 def dup wb = Workbook.new self.each { |s| wb.add s.dup } wb end |
#each ⇒ Object
Yields each Sheet.
150 151 152 153 |
# File 'lib/rubyexcel.rb', line 150 def each return to_enum( :each ) unless block_given? @sheets.each { |s| yield s } end |
#empty? ⇒ Boolean
Check whether the workbook has Sheets
161 162 163 |
# File 'lib/rubyexcel.rb', line 161 def empty? @sheets.empty? end |
#get_excel(invisible = false) ⇒ WIN32OLE::Excel
Open or connect to an Excel instance
88 89 90 91 92 |
# File 'lib/rubyexcel/excel_tools.rb', line 88 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
102 103 104 105 106 107 |
# File 'lib/rubyexcel/excel_tools.rb', line 102 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
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 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 |
# File 'lib/rubyexcel/excel_tools.rb', line 118 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}) 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(input_data, header_rows = 1) ⇒ Object
171 172 173 |
# File 'lib/rubyexcel.rb', line 171 def load( *args ) add.load( *args ) end |
#make_sheet_pretty(sheet) ⇒ WIN32OLE::Worksheet
Take an Excel Sheet and standardise some of the formatting
191 192 193 194 195 196 197 198 199 200 |
# File 'lib/rubyexcel/excel_tools.rb', line 191 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?(method, include_private = false) ⇒ Boolean
Allow for certain method_missing calls
192 193 194 195 196 197 198 199 |
# File 'lib/rubyexcel.rb', line 192 def respond_to?( method, include_private = false ) if ExcelToolsMethods.include?( method ) true else super end end |
#save_excel(filename = nil, invisible = false) ⇒ WIN32OLE::Workbook
Save the RubyExcel::Workbook as an Excel Workbook
210 211 212 213 214 215 216 217 218 219 |
# File 'lib/rubyexcel/excel_tools.rb', line 210 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
210 211 212 213 214 215 216 217 218 219 220 221 |
# File 'lib/rubyexcel.rb', line 210 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
225 226 227 |
# File 'lib/rubyexcel.rb', line 225 def sort( &block ) dup.sort!( &block ) end |
#sort!(&block) ⇒ Object
Sort Sheets according to a block
233 234 235 |
# File 'lib/rubyexcel.rb', line 233 def sort!( &block ) @sheets = @sheets.sort( &block ) end |
#sort_by(&block) ⇒ Object
239 240 241 |
# File 'lib/rubyexcel.rb', line 239 def sort_by( &block ) dup.sort_by!( &block ) end |
#sort_by!(&block) ⇒ Object
Sort Sheets by an attribute given in a block
247 248 249 |
# File 'lib/rubyexcel.rb', line 247 def sort_by!( &block ) @sheets = @sheets.sort_by( &block ) end |
#to_excel(invisible = false) ⇒ WIN32OLE::Workbook
Output the RubyExcel::Workbook to Excel
228 229 230 231 232 233 234 235 236 237 238 239 240 241 |
# File 'lib/rubyexcel/excel_tools.rb', line 228 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
255 256 257 |
# File 'lib/rubyexcel.rb', line 255 def to_html map(&:to_html).join('</br>') end |
#to_safe_format ⇒ Object
245 246 247 |
# File 'lib/rubyexcel/excel_tools.rb', line 245 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.
254 255 256 257 |
# File 'lib/rubyexcel/excel_tools.rb', line 254 def to_safe_format! sheets &:to_safe_format! self end |