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.
-
#standalone ⇒ Object
Set to true to cause the workbook to always create a new instance of Excel when exporting.
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.
-
#export(filename = nil) ⇒ String
Save the RubyExcel::Workbook as an Excel Workbook and close Excel afterwards.
-
#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.
62 63 64 65 66 |
# File 'lib/rubyexcel.rb', line 62 def initialize( name = 'Output' ) self.name = name @sheets = [] self.standalone = false 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
183 184 185 186 187 188 189 190 |
# 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 |
Instance Attribute Details
#name ⇒ Object
Get and set the Workbook name
53 54 55 |
# File 'lib/rubyexcel.rb', line 53 def name @name end |
#standalone ⇒ Object
Set to true to cause the workbook to always create a new instance of Excel when exporting
56 57 58 |
# File 'lib/rubyexcel.rb', line 56 def standalone @standalone end |
Instance Method Details
#<<(other) ⇒ Object
Appends an object to the Workbook
74 75 76 77 78 79 80 81 82 |
# 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) ⇒ 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
95 96 97 98 99 100 101 102 103 104 |
# 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 |
#clear_all ⇒ Object Also known as: delete_all
Removes all Sheets from the Workbook
111 112 113 |
# File 'lib/rubyexcel.rb', line 111 def clear_all @sheets = []; self end |
#delete(ref = nil) {|RubyExcel::Sheet| ... } ⇒ Object
Removes Sheet(s) from the Workbook
123 124 125 126 127 128 129 130 131 132 133 134 135 136 |
# 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 |
#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 80 |
# 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 ⇒ RubyExcel::Workbook
Return a copy of self
144 145 146 147 148 |
# File 'lib/rubyexcel.rb', line 144 def dup wb = Workbook.new self.each { |s| wb.add s.dup } wb end |
#each ⇒ Object
Yields each Sheet.
154 155 156 157 |
# File 'lib/rubyexcel.rb', line 154 def each return to_enum( :each ) unless block_given? @sheets.each { |s| yield s } end |
#empty? ⇒ Boolean
Check whether the workbook has Sheets
165 166 167 |
# File 'lib/rubyexcel.rb', line 165 def empty? @sheets.empty? end |
#export(filename = nil) ⇒ String
Save the RubyExcel::Workbook as an Excel Workbook and close Excel afterwards
89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 |
# 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) ⇒ WIN32OLE::Excel
Open or connect to an Excel instance
114 115 116 117 118 119 |
# 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) ⇒ WIN32OLE::Workbook
Create a new Excel Workbook
129 130 131 132 133 134 |
# 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) ⇒ self
Import a WIN32OLE Object as a Workbook or Sheet
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 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 |
# 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(input_data, header_rows = 1) ⇒ Object
175 176 177 |
# File 'lib/rubyexcel.rb', line 175 def load( *args ) add.load( *args ) end |
#make_sheet_pretty(sheet) ⇒ WIN32OLE::Worksheet
Take an Excel Sheet and standardise some of the formatting
218 219 220 221 222 223 224 225 226 227 |
# 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 |
#respond_to?(method, include_private = false) ⇒ Boolean
Allow for certain method_missing calls
196 197 198 199 200 201 202 203 |
# 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) ⇒ WIN32OLE::Workbook
Save the RubyExcel::Workbook as an Excel Workbook
237 238 239 240 241 242 243 244 245 246 |
# 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) {|RubyExcel::Sheet| ... } ⇒ RubyExcel::Sheet, Enumerator
Select a Sheet or iterate through them
214 215 216 217 218 219 220 221 222 223 224 225 |
# 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) ⇒ Object
229 230 231 |
# File 'lib/rubyexcel.rb', line 229 def sort( &block ) dup.sort!( &block ) end |
#sort!(&block) ⇒ Object
Sort Sheets according to a block
237 238 239 |
# File 'lib/rubyexcel.rb', line 237 def sort!( &block ) @sheets = @sheets.sort( &block ) end |
#sort_by(&block) ⇒ Object
243 244 245 |
# File 'lib/rubyexcel.rb', line 243 def sort_by( &block ) dup.sort_by!( &block ) end |
#sort_by!(&block) ⇒ Object
Sort Sheets by an attribute given in a block
251 252 253 |
# File 'lib/rubyexcel.rb', line 251 def sort_by!( &block ) @sheets = @sheets.sort_by( &block ) end |
#to_excel(invisible = false) ⇒ WIN32OLE::Workbook
Output the RubyExcel::Workbook to Excel
255 256 257 258 259 260 261 262 263 264 265 266 267 268 |
# 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 ⇒ Object
The Workbook as a group of HTML Tables
259 260 261 |
# File 'lib/rubyexcel.rb', line 259 def to_html map(&:to_html).join('</br>') end |
#to_safe_format ⇒ Object
272 273 274 |
# File 'lib/rubyexcel/excel_tools.rb', line 272 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.
281 282 283 284 |
# File 'lib/rubyexcel/excel_tools.rb', line 281 def to_safe_format! sheets &:to_safe_format! self end |