Class: SpreadsheetX::Worksheet

Inherits:
Object
  • Object
show all
Defined in:
lib/spreadsheetx/worksheet.rb

Overview

Workbooks are made up of N Worksheets, this class represents a specific Worksheet.

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(archive, sheet_id, r_id, name) ⇒ Worksheet

return a Worksheet object which relates to a specific Worksheet



11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
# 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

Instance Attribute Details

#name ⇒ Object (readonly)

Returns the value of attribute name.



8
9
10
 
# File 'lib/spreadsheetx/worksheet.rb', line 8

def name
  @name
end

#r_id ⇒ Object (readonly)

Returns the value of attribute r_id.



7
8
9
 
# File 'lib/spreadsheetx/worksheet.rb', line 7

def r_id
  @r_id
end

#sheet_id ⇒ Object (readonly)

Returns the value of attribute sheet_id.



6
7
8
 
# File 'lib/spreadsheetx/worksheet.rb', line 6

def sheet_id
  @sheet_id
end

Class Method Details

.cell_id(col_number, row_number) ⇒ Object

turns a cell address into its excel name, 1,1 = A1 2,3 = C2 etc.



131
132
133
134
135
136
137
138
 
# 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

Instance Method Details

#row_count ⇒ Object

the number of rows containing data this sheet has NOTE: this is the count of those rows, not the length of the document



119
120
121
122
123
 
# 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 ⇒ Object

returns the xml representation of this worksheet



126
127
128
 
# 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) ⇒ Object

update the value of a particular cell, if the row or cell doesnt exist in the XML, then it will be created



31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
 
# 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