class SpreadsheetX::Worksheet
Workbooks are made up of N Worksheets, this class represents a specific Worksheet
.
Attributes
name[R]
r_id[R]
sheet_id[R]
Public Class Methods
cell_id(col_number, row_number)
click to toggle source
turns a cell address into its excel name, 1,1 = A1 2,3 = C2 etc.
# File lib/spreadsheetx/worksheet.rb, line 131 def self.cell_id(col_number, row_number) raise 'There is no row 0 in an excel sheet, start at 1 instead' if row_number < 1 raise 'There is no column 0 in an excel sheet, start at 1 instead' if col_number < 1 letter = 'A' # some day, speed this up (col_number.to_i-1).times{letter = letter.succ} "#{letter}#{row_number}" end
new(archive, sheet_id, r_id, name)
click to toggle source
return a Worksheet
object which relates to a specific Worksheet
# File lib/spreadsheetx/worksheet.rb, line 11 def initialize(archive, sheet_id, r_id, name) @sheet_id = sheet_id @r_id = r_id @name = name # open the workbook archive.fopen("xl/worksheets/sheet#{@r_id}.xml") do |f| # read contents of this file file_contents = f.read # parse the XML and hold the doc @xml_doc = XML::Document.string(file_contents) # set the default namespace @xml_doc.root.namespaces.default_prefix = 'spreadsheetml' end end
Public Instance Methods
row_count()
click to toggle source
the number of rows containing data this sheet has NOTE: this is the count of those rows, not the length of the document
# File lib/spreadsheetx/worksheet.rb, line 119 def row_count count = 0 # target the sheetData rows @xml_doc.find('spreadsheetml:sheetData/spreadsheetml:row').count end
to_s()
click to toggle source
returns the xml representation of this worksheet
# File lib/spreadsheetx/worksheet.rb, line 126 def to_s @xml_doc.to_s(:indent => false).gsub(/\n/,"\r\n") end
update_cell(col_number, row_number, val, format=nil)
click to toggle source
update the value of a particular cell, if the row or cell doesnt exist in the XML, then it will be created
# File lib/spreadsheetx/worksheet.rb, line 31 def update_cell(col_number, row_number, val, format=nil) cell_id = SpreadsheetX::Worksheet.cell_id(col_number, row_number) val_is_a_date = (val.kind_of?(Date) || val.kind_of?(Time) || val.kind_of?(DateTime)) # if the val is nil or an empty string, then just delete the cell if val.nil? || val == '' if cell = @xml_doc.find_first("spreadsheetml:sheetData/spreadsheetml:row[@r=#{row_number}]/spreadsheetml:c[@r='#{cell_id}']") cell.remove! end return end row = @xml_doc.find_first("spreadsheetml:sheetData/spreadsheetml:row[@r=#{row_number}]") # was this row found unless row # build a new row row = XML::Node.new('row') row['r'] = row_number.to_s # if there are no rows higher than this one, then add this row to the end of the sheetData next_largest = @xml_doc.find_first("spreadsheetml:sheetData/spreadsheetml:row[@r>#{row_number}]") if next_largest next_largest.prev = row else # there are no rows higher than this one # add this row to the end of the sheetData @xml_doc.find_first('spreadsheetml:sheetData') << row end end cell = row.find_first("spreadsheetml:c[@r='#{cell_id}']") # was this row found unless cell # build a new cell cell = XML::Node.new('c') cell['r'] = cell_id # add it to the other cells in this row row << cell end # are we setting a format cell['s'] = format.to_s # reset this attribute cell['t'] = '' # create the node which represents the value in the cell # numeric types if val.kind_of?(Integer) || val.kind_of?(Float) || val.kind_of?(Fixnum) cell_value = XML::Node.new('v') cell_value.content = val.to_s # if we are using a format, then dates are stored as floats, otherwise they get caught by string use a string elsif format && val_is_a_date cell_value = XML::Node.new('v') # dates are stored as flaots, otherwise use a string cell_value.content = (val.to_time.to_f / (60*60*24)).to_s else # assume its a string # put the strings inline to make life easier cell['t'] = 'inlineStr' # the string node looks like <is><t>string</t></is> is = XML::Node.new('is') t = XML::Node.new('t') t.content = val_is_a_date ? val.to_time.strftime('%Y-%m-%d %H:%M:%S') : val.to_s cell_value = ( is << t ) end # first clear out any existing values (nodes) cell.find('*').each{|n| n.remove! } # now we put the value in the cell cell << cell_value end