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, :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 = nil) ⇒ 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) ⇒ 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.
-
#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(m, *args, &block) ⇒ Object
Don’t require Windows-specific libraries unless the relevant methods are called.
-
#respond_to?(m) ⇒ 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.
45 46 47 48 |
# File 'lib/rubyexcel.rb', line 45 def initialize( name = 'Output' ) @name = name @sheets = [] end |
Dynamic Method Handling
This class handles dynamic methods through the method_missing method
#method_missing(m, *args, &block) ⇒ Object
Don’t require Windows-specific libraries unless the relevant methods are called
160 161 162 163 164 165 166 167 |
# File 'lib/rubyexcel.rb', line 160 def method_missing(m, *args, &block) if ExcelToolsMethods.include?( m ) require_relative 'rubyexcel/excel_tools.rb' send( m, *args, &block ) else super end end |
Instance Attribute Details
#name ⇒ Object
Get and set the Workbook name
39 40 41 |
# File 'lib/rubyexcel.rb', line 39 def name @name end |
Instance Method Details
#<<(other) ⇒ Object
Appends an object to the Workbook
56 57 58 59 60 61 62 63 64 |
# File 'lib/rubyexcel.rb', line 56 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
77 78 79 80 81 82 83 84 85 86 |
# File 'lib/rubyexcel.rb', line 77 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
93 94 95 |
# File 'lib/rubyexcel.rb', line 93 def clear_all @sheets = []; self end |
#delete(ref) ⇒ Object
Removes Sheet(s) from the Workbook
104 105 106 107 108 109 110 111 112 113 |
# File 'lib/rubyexcel.rb', line 104 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 |
#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
38 39 40 41 42 43 44 |
# File 'lib/rubyexcel/excel_tools.rb', line 38 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.
52 53 54 |
# File 'lib/rubyexcel/excel_tools.rb', line 52 def documents_path Win32::Registry::HKEY_CURRENT_USER.open( 'SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Explorer\\Shell Folders' )['Personal'] rescue Dir.pwd.gsub('/','\\') end |
#dump_to_sheet(data, sheet = nil) ⇒ WIN32OLE::Worksheet
Drop a multidimensional Array into an Excel Sheet
64 65 66 67 68 69 |
# File 'lib/rubyexcel/excel_tools.rb', line 64 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
121 122 123 124 125 |
# File 'lib/rubyexcel.rb', line 121 def dup wb = Workbook.new self.each { |s| wb.add s.dup } wb end |
#each ⇒ Object
Yields each Sheet.
131 132 133 134 |
# File 'lib/rubyexcel.rb', line 131 def each return to_enum( :each ) unless block_given? @sheets.each { |s| yield s } end |
#empty? ⇒ Boolean
Check whether the workbook has Sheets
142 143 144 |
# File 'lib/rubyexcel.rb', line 142 def empty? @sheets.empty? end |
#get_excel(invisible = false) ⇒ WIN32OLE::Excel
Open or connect to an Excel instance
78 79 80 81 82 |
# File 'lib/rubyexcel/excel_tools.rb', line 78 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
92 93 94 95 96 97 |
# File 'lib/rubyexcel/excel_tools.rb', line 92 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
152 153 154 |
# File 'lib/rubyexcel.rb', line 152 def load( *args ) add.load( *args ) end |
#make_sheet_pretty(sheet) ⇒ WIN32OLE::Worksheet
Take an Excel Sheet and standardise some of the formatting
106 107 108 109 110 111 112 113 114 115 |
# File 'lib/rubyexcel/excel_tools.rb', line 106 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?(m) ⇒ Boolean
Allow for certain method_missing calls
173 174 175 176 177 178 179 180 |
# File 'lib/rubyexcel.rb', line 173 def respond_to?( m ) if ExcelToolsMethods.include?( m ) true else super end end |
#save_excel(filename = nil, invisible = false) ⇒ WIN32OLE::Workbook
Save the RubyExcel::Workbook as an Excel Workbook
125 126 127 128 129 130 131 132 133 134 |
# File 'lib/rubyexcel/excel_tools.rb', line 125 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
191 192 193 194 195 196 197 198 199 200 201 202 |
# File 'lib/rubyexcel.rb', line 191 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
206 207 208 |
# File 'lib/rubyexcel.rb', line 206 def sort( &block ) dup.sort!( &block ) end |
#sort!(&block) ⇒ Object
Sort Sheets according to a block
214 215 216 |
# File 'lib/rubyexcel.rb', line 214 def sort!( &block ) @sheets = @sheets.sort( &block ) end |
#sort_by(&block) ⇒ Object
220 221 222 |
# File 'lib/rubyexcel.rb', line 220 def sort_by( &block ) dup.sort_by!( &block ) end |
#sort_by!(&block) ⇒ Object
Sort Sheets by an attribute given in a block
228 229 230 |
# File 'lib/rubyexcel.rb', line 228 def sort_by!( &block ) @sheets = @sheets.sort_by( &block ) end |
#to_excel(invisible = false) ⇒ WIN32OLE::Workbook
Output the RubyExcel::Workbook to Excel
143 144 145 146 147 148 149 150 151 152 153 154 155 156 |
# File 'lib/rubyexcel/excel_tools.rb', line 143 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 wb.application.visible = true unless invisible wb end |
#to_html ⇒ Object
The Workbook as a group of HTML Tables
236 237 238 |
# File 'lib/rubyexcel.rb', line 236 def to_html map(&:to_html).join('</br>') end |
#to_safe_format ⇒ Object
160 161 162 |
# File 'lib/rubyexcel/excel_tools.rb', line 160 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.
169 170 171 172 173 174 175 176 177 |
# File 'lib/rubyexcel/excel_tools.rb', line 169 def to_safe_format! sheets { |s| s.rows { |r| r.map! { |v| if v.is_a?( String ) v[0] == '=' ? v.sub( /\A=/,"'=" ) : v else v.to_s end } } }; self end |