Class Spreadsheet::Worksheet
In: lib/spreadsheet/worksheet.rb
lib/spreadsheet/excel.rb
Parent: Object
Column Format Row Font Enumerable Worksheet\n[lib/spreadsheet/excel.rb\nlib/spreadsheet/worksheet.rb] Link Workbook Workbook\n[lib/spreadsheet/excel.rb\nlib/spreadsheet/excel/workbook.rb] SstEntry String Row Array Worksheet ExcelCompatibleWorkbook Format Worksheet Workbook DelegateClassSpreadsheet::Format Writer Compatibility Formula Reader\n[lib/parseexcel/parseexcel.rb\nlib/spreadsheet/excel/reader.rb\nlib/spreadsheet/excel/reader/biff5.rb\nlib/spreadsheet/excel/reader/biff8.rb] Error lib/spreadsheet/excel.rb lib/spreadsheet/link.rb lib/spreadsheet/formula.rb lib/spreadsheet/workbook.rb lib/spreadsheet/font.rb lib/spreadsheet/column.rb lib/spreadsheet/format.rb lib/spreadsheet/writer.rb lib/spreadsheet/row.rb Encodings lib/spreadsheet/excel.rb lib/spreadsheet/excel/row.rb lib/spreadsheet/excel/reader/biff5.rb lib/spreadsheet/excel/worksheet.rb lib/spreadsheet/excel/sst_entry.rb lib/spreadsheet/excel/error.rb lib/spreadsheet/excel/workbook.rb lib/spreadsheet/excel/writer/format.rb lib/spreadsheet/excel/writer/workbook.rb lib/spreadsheet/excel/writer/worksheet.rb Biff8 Writer Biff5 Biff8 Internals Offset Excel Datatypes ParseExcel Compatibility Spreadsheet dot/m_34_0.png

The Worksheet class. Contains most of the Spreadsheet data in Rows.

Interesting Attributes

name :The Name of this Worksheet.
default_format:The default format used for all cells in this Workhseet that have no format set explicitly or in Row#default_format.
rows :The Rows in this Worksheet. It is not recommended to Manipulate this Array directly. If you do, call updated_from with the smallest modified index.
columns :The Column formatting in this Worksheet. Column instances may appear at more than one position in columns. If you modify a Column directly, your changes will be reflected in all those positions.
selected :When a user chooses to print a Workbook, Excel will include all selected Worksheets. If no Worksheet is selected at Workbook#write, then the first Worksheet is selected by default.

Methods

Included Modules

Spreadsheet::Encodings Enumerable

Attributes

columns  [R] 
name  [RW] 
rows  [R] 
selected  [RW] 
workbook  [RW] 

Public Class methods

[Source]

    # File lib/spreadsheet/worksheet.rb, line 31
31:     def initialize opts={}
32:       @default_format = nil
33:       @selected = opts[:selected]
34:       @dimensions = [0,0,0,0]
35:       @name = opts[:name] || 'Worksheet'
36:       @workbook = opts[:workbook]
37:       @rows = []
38:       @columns = []
39:       @links = {}
40:     end

Public Instance methods

Get the enriched value of the Cell at row, column. See also Worksheet#cell, Row#[].

[Source]

     # File lib/spreadsheet/worksheet.rb, line 245
245:     def [] row, column
246:       row(row)[column]
247:     end

Set the value of the Cell at row, column to value. See also Row#[]=.

[Source]

     # File lib/spreadsheet/worksheet.rb, line 251
251:     def []= row, column, value
252:       row(row)[column] = value
253:     end

Add a Format to the Workbook. If you use Row#set_format, you should not need to use this Method.

[Source]

    # File lib/spreadsheet/worksheet.rb, line 52
52:     def add_format fmt
53:       @workbook.add_format fmt if fmt
54:     end

Get the enriched value of the Cell at row, column. See also Worksheet#[], Row#[].

[Source]

    # File lib/spreadsheet/worksheet.rb, line 58
58:     def cell row, column
59:       row(row)[column]
60:     end

Returns the Column at idx.

[Source]

    # File lib/spreadsheet/worksheet.rb, line 63
63:     def column idx
64:       @columns[idx] || Column.new(idx, default_format, :worksheet => self)
65:     end

The number of columns in this Worksheet which contain data.

[Source]

    # File lib/spreadsheet/worksheet.rb, line 68
68:     def column_count
69:       dimensions[3] - dimensions[2]
70:     end

[Source]

    # File lib/spreadsheet/worksheet.rb, line 71
71:     def column_updated idx, column
72:       @columns[idx] = column
73:     end

The default Format of this Worksheet, if you have set one. Returns the Workbook‘s default Format otherwise.

[Source]

    # File lib/spreadsheet/worksheet.rb, line 84
84:     def default_format
85:       @default_format || @workbook.default_format
86:     end

Set the default Format of this Worksheet.

[Source]

    # File lib/spreadsheet/worksheet.rb, line 89
89:     def default_format= format
90:       @default_format = format
91:       add_format format
92:       format
93:     end

Delete the Row at idx (0-based) from this Worksheet.

[Source]

    # File lib/spreadsheet/worksheet.rb, line 76
76:     def delete_row idx
77:       res = @rows.delete_at idx
78:       updated_from idx
79:       res
80:     end
Dimensions:[ first used row, first unused row, first used column, first unused column ] ( First used means that all rows or columns before that are empty. First unused means that this and all following rows or columns are empty. )

[Source]

     # File lib/spreadsheet/worksheet.rb, line 100
100:     def dimensions
101:       @dimensions || recalculate_dimensions
102:     end

If no argument is given, each iterates over all used Rows (from the first used Row until but omitting the first unused Row, see also dimensions).

If the argument skip is given, each iterates from that row until but omitting the first unused Row, effectively skipping the first skip Rows from the top of the Worksheet.

[Source]

     # File lib/spreadsheet/worksheet.rb, line 110
110:     def each skip=dimensions[0], &block
111:       skip.upto(dimensions[1] - 1) do |idx|
112:         block.call row(idx)
113:       end
114:     end

Sets the default Format of the column at idx.

idx may be an Integer, or an Enumerable that iterates over a number of Integers.

format is a Format, or nil if you want to remove the Formatting at idx

Returns an instance of Column if idx is an Integer, an Array of Columns otherwise.

[Source]

     # File lib/spreadsheet/worksheet.rb, line 128
128:     def format_column idx, format=nil, opts={}
129:       opts[:worksheet] = self
130:       res = case idx
131:             when Integer
132:               column = nil
133:               if format
134:                 column = Column.new(idx, format, opts)
135:               end
136:               @columns[idx] = column
137:             else
138:               idx.collect do |col| format_column col, format, opts end
139:             end
140:       shorten @columns
141:       res
142:     end

[Source]

    # File lib/spreadsheet/excel.rb, line 40
40:       def format_column column, width=nil, format=nil
41:         if width.is_a? Format
42:           new_format_column column, width, format
43:         else
44:           new_format_column column, format, :width => width
45:         end
46:       end

Formats all Date, DateTime and Time cells with format or the default formats:

  • ‘DD.MM.YYYY’ for Date
  • ‘DD.MM.YYYY hh:mm:ss’ for DateTime and Time

[Source]

     # File lib/spreadsheet/worksheet.rb, line 148
148:     def format_dates! format=nil
149:       each do |row|
150:         row.each_with_index do |value, idx|
151:           unless row.formats[idx] || row.format(idx).date_or_time?
152:             numfmt = case value
153:                      when DateTime, Time
154:                        format || client('DD.MM.YYYY hh:mm:ss', 'UTF-8')
155:                      when Date
156:                        format || client('DD.MM.YYYY', 'UTF-8')
157:                      end
158:             case numfmt
159:             when Format
160:               row.set_format idx, numfmt
161:             when String
162:               fmt = row.format(idx).dup
163:               fmt.number_format = numfmt
164:               row.set_format idx, fmt
165:             end
166:           end
167:         end
168:       end
169:     end

Insert a Row at idx (0-based) containing cells

[Source]

     # File lib/spreadsheet/worksheet.rb, line 172
172:     def insert_row idx, cells=[]
173:       res = @rows.insert idx, Row.new(self, idx, cells)
174:       updated_from idx
175:       res
176:     end

[Source]

     # File lib/spreadsheet/worksheet.rb, line 177
177:     def inspect
178:       names = instance_variables
179:       names.delete '@rows'
180:       variables = names.collect do |name|
181:         "%s=%s" % [name, instance_variable_get(name)]
182:       end.join(' ')
183:       sprintf "#<%s:0x%014x %s @rows[%i]>", self.class, object_id,
184:                                             variables, row_count
185:     end

The last Row containing any data

[Source]

     # File lib/spreadsheet/worksheet.rb, line 187
187:     def last_row
188:       row(last_row_index)
189:     end

The index of the last Row containing any data

[Source]

     # File lib/spreadsheet/worksheet.rb, line 191
191:     def last_row_index
192:       [dimensions[1] - 1, 0].max
193:     end
new_format_column(idx, format=nil, opts={})

Alias for format_column

Replace the Row at idx with the following arguments. Like update_row, but truncates the Row if there are fewer arguments than Cells in the Row.

[Source]

     # File lib/spreadsheet/worksheet.rb, line 197
197:     def replace_row idx, *cells
198:       if(row = @rows[idx]) && cells.size < row.size
199:         cells.concat Array.new(row.size - cells.size)
200:       end
201:       update_row idx, *cells
202:     end

The Row at idx or a new Row.

[Source]

     # File lib/spreadsheet/worksheet.rb, line 205
205:     def row idx
206:       @rows[idx] || Row.new(self, idx)
207:     end

The number of Rows in this Worksheet which contain data.

[Source]

     # File lib/spreadsheet/worksheet.rb, line 210
210:     def row_count
211:       dimensions[1] - dimensions[0]
212:     end

Tell Worksheet that the Row at idx has been updated and the dimensions need to be recalculated. You should not need to call this directly.

[Source]

     # File lib/spreadsheet/worksheet.rb, line 216
216:     def row_updated idx, row
217:       @dimensions = nil
218:       @rows[idx] = row
219:     end

Updates the Row at idx with the following arguments.

[Source]

     # File lib/spreadsheet/worksheet.rb, line 222
222:     def update_row idx, *cells
223:       res = if row = @rows[idx]
224:               row[0, cells.size] = cells
225:               row
226:             else
227:               Row.new self, idx, cells
228:             end
229:       row_updated idx, res
230:       res
231:     end

Renumbers all Rows starting at idx and calls row_updated for each of them.

[Source]

     # File lib/spreadsheet/worksheet.rb, line 235
235:     def updated_from index
236:       index.upto(@rows.size - 1) do |idx|
237:         row = row(idx)
238:         row.idx = idx
239:         row_updated idx, row
240:       end
241:     end

[Source]

    # File lib/spreadsheet/excel.rb, line 48
48:     def write row, col, data=nil, format=nil
49:       if data.is_a? Array
50:         write_row row, col, data, format
51:       else
52:         row = row(row)
53:         row[col] = data
54:         row.set_format col, format
55:       end
56:     end

[Source]

    # File lib/spreadsheet/excel.rb, line 57
57:     def write_column row, col, data=nil, format=nil
58:       if data.is_a? Array
59:         data.each do |token|
60:           if token.is_a? Array
61:             write_row row, col, token, format
62:           else
63:             write row, col, token, format
64:           end
65:           row += 1
66:         end
67:       else
68:         write row, col, data, format
69:       end
70:     end

[Source]

    # File lib/spreadsheet/excel.rb, line 71
71:     def write_row row, col, data=nil, format=nil
72:       if data.is_a? Array
73:         data.each do |token|
74:           if token.is_a? Array
75:             write_column row, col, token, format
76:           else
77:             write row, col, token, format
78:           end
79:           col += 1
80:         end
81:       else
82:         write row, col, data, format
83:       end
84:     end

[Source]

    # File lib/spreadsheet/excel.rb, line 85
85:     def write_url row, col, url, string=url, format=nil
86:       row(row)[col] = Link.new url, string
87:     end

[Validate]