class RobustExcelOle::Worksheet
worksheet: see github.com/Thomas008/robust_excel_ole/blob/master/lib/robust_excel_ole/worksheet.rb
Constants
- ColorIndex
- Name
- Saved
- Value
Attributes
Public Class Methods
@private
# File lib/robust_excel_ole/worksheet.rb, line 399 def self.listobject_class @listobject_class ||= begin module_name = self.parent_name "#{module_name}::ListObject".constantize rescue NameError => e ListObject end end
# File lib/robust_excel_ole/worksheet.rb, line 22 def initialize(win32_worksheet) @ole_worksheet = win32_worksheet if @ole_worksheet.ProtectContents @ole_worksheet.Unprotect @end_row = last_row @end_column = last_column @ole_worksheet.Protect else @end_row = last_row @end_column = last_column end end
@private
# File lib/robust_excel_ole/worksheet.rb, line 384 def self.workbook_class @workbook_class ||= begin module_name = self.parent_name "#{module_name}::Workbook".constantize rescue NameError => e Workbook end end
Public Instance Methods
# File lib/robust_excel_ole/worksheet.rb, line 332 def == other_worksheet other_worksheet.is_a?(Worksheet) && self.workbook == other_worksheet.workbook && self.Name == other_worksheet.Name end
value of a range given its defined name or address @params [Variant] defined name or address @returns [Variant] value (contents) of the range
# File lib/robust_excel_ole/worksheet.rb, line 73 def [](name_or_address, address2 = :__not_provided) range(name_or_address, address2).value end
sets the value of a range given its defined name or address, and the value @params [Variant] defined name or address of the range @params [Variant] value (contents) of the range @returns [Variant] value (contents) of the range
# File lib/robust_excel_ole/worksheet.rb, line 81 def []=(name_or_address, value_or_address2, remaining_arg = :__not_provided) if remaining_arg != :__not_provided name_or_address, value = [name_or_address, value_or_address2], remaining_arg else value = value_or_address2 end begin range(name_or_address).value = value rescue #WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException raise RangeNotEvaluatable, "cannot assign value to range with name or address #{name_or_address.inspect}\n#{$!.message}" end end
@private returns true, if the worksheet object responds to VBA methods, false otherwise
# File lib/robust_excel_ole/worksheet.rb, line 349 def alive? @ole_worksheet.UsedRange true rescue # trace $!.message false end
value of a cell, if row and column are given @params row and column @returns value of the cell
# File lib/robust_excel_ole/worksheet.rb, line 211 def cellval(x,y) # :deprecated :# @ole_worksheet.Cells.Item(x, y).Value rescue raise RangeNotEvaluatable, "cannot read cell (#{x.inspect},#{y.inspect})\n#{$!.message}" end
# File lib/robust_excel_ole/worksheet.rb, line 327 def col_range(col, integer_range = nil) integer_range ||= 1..@end_row RobustExcelOle::Range.new(@ole_worksheet.Range(@ole_worksheet.Cells(integer_range.min, col), @ole_worksheet.Cells(integer_range.max, col)), self) end
@return [Enumerator] traversing the rows values
# File lib/robust_excel_ole/worksheet.rb, line 234 def each if block_given? @ole_worksheet.UsedRange.Rows.lazy.each do |ole_row| row_value = ole_row.Value yield (row_value.nil? ? [] : row_value.first) end else to_enum(:each).lazy end end
@return [Enumerator] traversing the cells
# File lib/robust_excel_ole/worksheet.rb, line 284 def each_cell if block_given? each_row do |row_range| row_range.lazy.each do |cell| yield cell end end else to_enum(:each_cell).lazy end end
@return [Enumerator] traversing the columns
# File lib/robust_excel_ole/worksheet.rb, line 265 def each_column(offset = 0) if block_given? offset += 1 1.upto(@end_column) do |column| next if column < offset yield RobustExcelOle::Range.new(@ole_worksheet.Range(@ole_worksheet.Cells(1, column), @ole_worksheet.Cells(@end_row, column)), self) end else to_enum(:each_column).lazy end end
@return [Enumerator] traversing the rows
# File lib/robust_excel_ole/worksheet.rb, line 246 def each_row(offset = 0) if block_given? offset += 1 1.upto(@end_row) do |row| next if row < offset yield RobustExcelOle::Range.new(@ole_worksheet.Range(@ole_worksheet.Cells(row, 1), @ole_worksheet.Cells(row, @end_column)), self) end else to_enum(:each_row).lazy end end
# File lib/robust_excel_ole/worksheet.rb, line 306 def each_rowvalue # :deprecated: # values.each do |row_values| yield row_values end end
# File lib/robust_excel_ole/worksheet.rb, line 312 def each_rowvalue_with_index(offset = 0) # :deprecated: # i = offset values.each do |row_values| yield row_values, i i += 1 end end
# File lib/robust_excel_ole/worksheet.rb, line 46 def excel workbook.excel end
@private
# File lib/robust_excel_ole/worksheet.rb, line 419 def inspect to_s end
@private
# File lib/robust_excel_ole/worksheet.rb, line 374 def last_column special_last_column = @ole_worksheet.UsedRange.SpecialCells(RobustExcelOle::XlLastCell).Column used_last_column = @ole_worksheet.UsedRange.Columns.Count [special_last_column, used_last_column].max end
@private
# File lib/robust_excel_ole/worksheet.rb, line 367 def last_row special_last_row = @ole_worksheet.UsedRange.SpecialCells(RobustExcelOle::XlLastCell).Row used_last_row = @ole_worksheet.UsedRange.Rows.Count [special_last_row, used_last_row].max end
@private
# File lib/robust_excel_ole/worksheet.rb, line 409 def listobject_class self.class.listobject_class end
sheet name @returns name of the sheet
# File lib/robust_excel_ole/worksheet.rb, line 52 def name @ole_worksheet.Name.encode('utf-8') rescue raise WorksheetREOError, "name could not be determined\n#{$!.message}" end
sets sheet name @param [String] new_name the new name of the sheet
# File lib/robust_excel_ole/worksheet.rb, line 60 def name= (new_name) @ole_worksheet.Name = new_name rescue WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException => msg if msg.message =~ /800A03EC/ || msg.message =~ /Visual Basic/ raise NameAlreadyExists, "sheet name #{new_name.inspect} already exists" else raise UnexpectedREOError, "unexpected WIN32OLERuntimeError: #{msg.message}" end end
returns the contents of a range with a locally defined name evaluates the formula if the contents is a formula if the name could not be found or the range or value could not be determined, then return default value, if provided, raise error otherwise @param [String] name the name of a range @param [Hash] opts the options @option opts [Symbol] :default the default value that is provided if no contents could be returned @return [Variant] the contents of a range with given name
# File lib/robust_excel_ole/worksheet.rb, line 151 def namevalue(name, opts = { default: :__not_provided }) begin ole_range = self.Range(name) rescue # WIN32OLERuntimeError, VBAMethodMissingError, Java::OrgRacobCom::ComFailException return opts[:default] unless opts[:default] == :__not_provided raise NameNotFound, "name #{name.inspect} not in #{self.inspect}" end begin worksheet = self if self.is_a?(Worksheet) #value = ole_range.Value value = if !::RANGES_JRUBY_BUG ole_range.Value else values = RobustExcelOle::Range.new(ole_range, worksheet).v (values.size==1 && values.first.size==1) ? values.first.first : values end rescue WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException return opts[:default] unless opts[:default] == :__not_provided raise RangeNotEvaluatable, "cannot determine value of range named #{name.inspect} in #{self.inspect}\n#{$!.message}" end if value == -2146828288 + RobustExcelOle::XlErrName return opts[:default] unless opts[:default] == __not_provided raise RangeNotEvaluatable, "cannot evaluate range named #{name.inspect} in #{File.basename(workbook.stored_filename).inspect rescue nil}\n#{$!.message}" end return opts[:default] unless (opts[:default] == :__not_provided) || value.nil? value end
a range given a defined name or address @params [Variant] defined name or address @return [Range] a range
# File lib/robust_excel_ole/worksheet.rb, line 97 def range(name_or_address, address2 = :__not_provided) if name_or_address.respond_to?(:gsub) && address2 == :__not_provided name = name_or_address range = get_name_object(name).RefersToRange rescue nil end unless range address = normalize_address(name_or_address, address2) workbook.retain_saved do begin a1_address = address_tool.as_a1(address) rescue nil if a1_address range = self.Range(a1_address) else saved = self.Parent.Saved begin self.Names.Add('__dummy_name_object_001__',nil,true,nil,nil,nil,nil,nil,nil,'=' + address_tool.as_r1c1(address)) range = get_name_object('__dummy_name_object_001__').RefersToRange ensure self.Names.Item('__dummy_name_object_001__').Delete self.Parent.Saved = saved end end rescue address2_string = (address2.nil? || address2 == :__not_provided) ? "" : ", #{address2.inspect}" raise RangeNotCreated, "cannot find name or address #{name_or_address.inspect}#{address2_string})" end end end range.to_reo end
# File lib/robust_excel_ole/worksheet.rb, line 322 def row_range(row, integer_range = nil) integer_range ||= 1..@end_column RobustExcelOle::Range.new(@ole_worksheet.Range(@ole_worksheet.Cells(row, integer_range.min), @ole_worksheet.Cells(row, integer_range.max)), self) end
sets the value of a cell, if row, column and color of the cell are given @params [Integer] x,y row and column @option opts [Symbol] :color the color of the cell when set
# File lib/robust_excel_ole/worksheet.rb, line 220 def set_cellval(x,y,value, opts = { }) # option opts is deprecated cell = @ole_worksheet.Cells.Item(x, y) cell.Interior.ColorIndex = opts[:color] unless opts[:color].nil? cell.Value = value rescue # WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException raise RangeNotEvaluatable, "cannot assign value #{value.inspect} to cell (#{y.inspect},#{x.inspect})\n#{$!.message}" end
assigns a value to a range given a locally defined name @param [String] name the name of a range @param [Variant] value the assigned value
@option opts [Symbol] :color the color of the cell when set
# File lib/robust_excel_ole/worksheet.rb, line 183 def set_namevalue(name, value, opts = { }) begin ole_range = self.Range(name) rescue WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException, VBAMethodMissingError raise NameNotFound, "name #{name.inspect} not in #{self.inspect}" end begin ole_range.Interior.ColorIndex = opts[:color] unless opts[:color].nil? if !::RANGES_JRUBY_BUG ole_range.Value = value else address_r1c1 = ole_range.AddressLocal(true,true,XlR1C1) row, col = address_tool.as_integer_ranges(address_r1c1) row.each_with_index do |r,i| col.each_with_index do |c,j| ole_range.Cells(i+1,j+1).Value = (value.respond_to?(:pop) ? value[i][j] : value) end end end value rescue WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException raise RangeNotEvaluatable, "cannot assign value to range named #{name.inspect} in #{self.inspect}\n#{$!.message}" end end
@params [Variant] table (listobject) name or number @return [ListObject] a table (listobject)
# File lib/robust_excel_ole/worksheet.rb, line 341 def table(number_or_name) listobject_class.new(@ole_worksheet.ListObjects.Item(number_or_name)) rescue raise WorksheetREOError, "table #{number_or_name} not found" end
@private
# File lib/robust_excel_ole/worksheet.rb, line 414 def to_s "#<Worksheet: #{(workbook.nil? ? "not alive " : (name + ' ' + File.basename(workbook.stored_filename)))}>" end
@return [Array] a 2-dimensional array that contains the values in each row of the used range
# File lib/robust_excel_ole/worksheet.rb, line 229 def values @ole_worksheet.UsedRange.Value end
@private
# File lib/robust_excel_ole/worksheet.rb, line 394 def workbook_class self.class.workbook_class end
Private Instance Methods
# File lib/robust_excel_ole/worksheet.rb, line 427 def method_missing(name, *args) super unless name.to_s[0,1] =~ /[A-Z]/ if ::ERRORMESSAGE_JRUBY_BUG begin @ole_worksheet.send(name, *args) rescue Java::OrgRacobCom::ComFailException raise VBAMethodMissingError, "unknown VBA property or method #{name.inspect}" end else begin @ole_worksheet.send(name, *args) rescue NoMethodError raise VBAMethodMissingError, "unknown VBA property or method #{name.inspect}" end end end
# File lib/robust_excel_ole/worksheet.rb, line 130 def normalize_address(address, address2) address = [address,address2] unless address2 == :__not_provided address = if address.is_a?(Integer) || address.is_a?(::Range) [address, nil] elsif address.is_a?(Array) && address.size == 1 && (address.first.is_a?(Integer) || address.first.is_a?(::Range)) [address.first, nil] else address end end