class RubyExcel::Workbook
A Workbook
which can hold multiple Sheets
Constants
- ColumnWidth
- ExcelToolsMethods
Names of methods which require win32ole
Attributes
Get and set the Workbook
name
Set to true to cause the workbook to always create a new instance of Excel when exporting
Public Class Methods
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
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
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
Removes all Sheets from the Workbook
# File lib/rubyexcel.rb, line 111 def clear_all @sheets = []; self end
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
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
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
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
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
Yields each Sheet
.
# File lib/rubyexcel.rb, line 154 def each return to_enum( :each ) unless block_given? @sheets.each { |s| yield s } end
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
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
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
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 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
@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
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
Don’t require Windows-specific libraries unless the relevant methods are called
# 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
Allow for certain method_missing
calls
# File lib/rubyexcel.rb, line 196 def respond_to?( method, include_private = false ) if ExcelToolsMethods.include?( method ) true else super end end
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
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
{Workbook#sort!}
# File lib/rubyexcel.rb, line 229 def sort( &block ) dup.sort!( &block ) end
Sort Sheets according to a block
# File lib/rubyexcel.rb, line 237 def sort!( &block ) @sheets = @sheets.sort( &block ) end
{Workbook#sort_by!}
# File lib/rubyexcel.rb, line 243 def sort_by( &block ) dup.sort_by!( &block ) end
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
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
The Workbook
as a group of HTML Tables
# File lib/rubyexcel.rb, line 259 def to_html map(&:to_html).join('</br>') end
{Workbook#to_safe_format!}
# File lib/rubyexcel/excel_tools.rb, line 272 def to_safe_format dup.to_safe_format! end
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