Class: Axlsx::Worksheet
- Inherits:
-
Object
- Object
- Axlsx::Worksheet
- Defined in:
- lib/axlsx/workbook/worksheet/worksheet.rb
Overview
The Worksheet class represents a worksheet in the workbook.
Instance Attribute Summary collapse
-
#auto_filter ⇒ Object
An range that excel will apply an autfilter to “A1:B3” This will turn filtering on for the cells in the range.
-
#auto_fit_data ⇒ Array
readonly
An array of content based calculated column widths.
-
#column_info ⇒ SimpleTypedList
readonly
Column info for the sheet.
-
#fit_to_page ⇒ Object
Indicates if the worksheet should print in a single page.
-
#merged_cells ⇒ Object
readonly
An array of merged cell ranges e.d “A1:B3” Content and formatting is read from the first cell.
-
#name ⇒ String
The name of the worksheet.
-
#rows ⇒ SimpleTypedList
readonly
The rows in this worksheet.
-
#selected ⇒ Object
Indicates if the worksheet is selected in the workbook It is possible to have more than one worksheet selected, however it might cause issues in some older versions of excel when using copy and paste.
-
#show_gridlines ⇒ Object
Indicates if the worksheet should show gridlines or not.
-
#tables ⇒ Array
readonly
The tables in this worksheet.
-
#workbook ⇒ Workbook
readonly
The workbook that owns this worksheet.
Class Method Summary collapse
-
.thin_chars ⇒ String
definition of characters which are less than the maximum width of 0-9 in the default font for use in String#count.
Instance Method Summary collapse
-
#[](cell_def) ⇒ Cell, Array
Returns the cell or cells defined using excel style A1:B3 references.
-
#abs_auto_filter ⇒ Object
The absolute auto filter range.
-
#add_chart(chart_type, options = {}) {|chart| ... } ⇒ Object
Adds a chart to this worksheets drawing.
-
#add_conditional_formatting(cells, rules) ⇒ Object
Add conditional formatting to this worksheet.
-
#add_image(options = {}) {|image| ... } ⇒ Object
Adds a media item to the worksheets drawing.
-
#add_row(values = [], options = {}) {|@rows.last| ... } ⇒ Row
(also: #<<)
Adds a row to the worksheet and updates auto fit data.
-
#add_table(ref, options = {}) {|table| ... } ⇒ Object
needs documentation.
-
#cells ⇒ Array
convinience method to access all cells in this worksheet.
-
#col_style(index, style, options = {}) ⇒ Object
Set the style for cells in a specific column.
-
#cols ⇒ Object
returns the sheet data as columnw.
-
#column_widths(*args) ⇒ Object
This is a helper method that Lets you specify a fixed width for multiple columns in a worksheet in one go.
-
#dimension ⇒ String
The demensions of a worksheet.
-
#drawing ⇒ Drawing
The drawing associated with this worksheet.
-
#index ⇒ Integer
The index of this worksheet in the owning Workbook’s worksheets list.
-
#initialize(wb, options = {}) ⇒ Worksheet
constructor
Creates a new worksheet.
-
#merge_cells(cells) ⇒ Object
Creates merge information for this worksheet.
-
#name_to_cell(name) ⇒ Cell
returns the column and row index for a named based cell.
-
#page_margins {|@page_margins| ... } ⇒ PageMargins
Page margins for printing the worksheet.
-
#pn ⇒ String
The part name of this worksheet.
-
#relationships ⇒ Relationships
The worksheet relationships.
-
#rels_pn ⇒ String
The relationship part name of this worksheet.
-
#rId ⇒ String
The relationship Id of thiw worksheet.
-
#row_style(index, style, options = {}) ⇒ Object
Set the style for cells in a specific row.
-
#to_xml_string ⇒ String
Serializes the object.
Constructor Details
#initialize(wb, options = {}) ⇒ Worksheet
the recommended way to manage worksheets is Workbook#add_worksheet
Creates a new worksheet.
97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 97 def initialize(wb, ={}) self.workbook = wb @workbook.worksheets << self @drawing = @page_margins = @auto_filter = nil @merged_cells = [] @auto_fit_data = [] @conditional_formattings = [] @selected = false @show_gridlines = true self.name = "Sheet" + (index+1).to_s @page_margins = PageMargins.new [:page_margins] if [:page_margins] @rows = SimpleTypedList.new Row @column_info = SimpleTypedList.new Col # @cols = SimpleTypedList.new Cell @tables = SimpleTypedList.new Table .each do |o| self.send("#{o[0]}=", o[1]) if self.respond_to? "#{o[0]}=" end end |
Instance Attribute Details
#auto_filter ⇒ Object
An range that excel will apply an autfilter to “A1:B3” This will turn filtering on for the cells in the range. The first row is considered the header, while subsequent rows are considerd to be data.
39 40 41 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 39 def auto_filter @auto_filter end |
#auto_fit_data ⇒ Array (readonly)
a single auto fit data item is a hash with :longest => [String] and :sz=> [Integer] members.
An array of content based calculated column widths.
28 29 30 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 28 def auto_fit_data @auto_fit_data end |
#column_info ⇒ SimpleTypedList (readonly)
Column info for the sheet
59 60 61 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 59 def column_info @column_info end |
#fit_to_page ⇒ Object
Indicates if the worksheet should print in a single page
54 55 56 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 54 def fit_to_page @fit_to_page end |
#merged_cells ⇒ Object (readonly)
An array of merged cell ranges e.d “A1:B3” Content and formatting is read from the first cell.
33 34 35 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 33 def merged_cells @merged_cells end |
#name ⇒ String
The name of the worksheet
9 10 11 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 9 def name @name end |
#rows ⇒ SimpleTypedList (readonly)
The recommended way to manage rows is Worksheet#add_row
The rows in this worksheet
23 24 25 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 23 def rows @rows end |
#selected ⇒ Object
Indicates if the worksheet is selected in the workbook It is possible to have more than one worksheet selected, however it might cause issues in some older versions of excel when using copy and paste.
50 51 52 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 50 def selected @selected end |
#show_gridlines ⇒ Object
Indicates if the worksheet should show gridlines or not
43 44 45 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 43 def show_gridlines @show_gridlines end |
#tables ⇒ Array (readonly)
The tables in this worksheet
17 18 19 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 17 def tables @tables end |
#workbook ⇒ Workbook
The workbook that owns this worksheet
13 14 15 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 13 def workbook @workbook end |
Class Method Details
.thin_chars ⇒ String
definition of characters which are less than the maximum width of 0-9 in the default font for use in String#count. This is used for autowidth calculations
87 88 89 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 87 def self.thin_chars @thin_chars ||= "^.acefijklrstxyzFIJL()-" end |
Instance Method Details
#[](cell_def) ⇒ Cell, Array
Returns the cell or cells defined using excel style A1:B3 references.
451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 451 def [] (cell_def) return rows[cell_def] if cell_def.is_a?(Integer) parts = cell_def.split(':') first = name_to_cell parts[0] if parts.size == 1 first else cells = [] last = name_to_cell(parts[1]) rows[(first.row.index..last.row.index)].each do |r| r.cells[(first.index..last.index)].each do |c| cells << c end end cells end end |
#abs_auto_filter ⇒ Object
The absolute auto filter range
218 219 220 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 218 def abs_auto_filter Axlsx.cell_range(@auto_filter.split(':').collect { |name| name_to_cell(name)}) if @auto_filter end |
#add_chart(chart_type, options = {}) {|chart| ... } ⇒ Object
each chart type also specifies additional options
Adds a chart to this worksheets drawing. This is the recommended way to create charts for your worksheet. This method wraps the complexity of dealing with ooxml drawing, anchors, markers graphic frames chart objects and all the other dirty details.
383 384 385 386 387 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 383 def add_chart(chart_type, ={}) chart = drawing.add_chart(chart_type, ) yield chart if block_given? chart end |
#add_conditional_formatting(cells, rules) ⇒ Object
Add conditional formatting to this worksheet.
137 138 139 140 141 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 137 def add_conditional_formatting(cells, rules) cf = ConditionalFormatting.new( :sqref => cells ) cf.add_rules rules @conditional_formattings << cf end |
#add_image(options = {}) {|image| ... } ⇒ Object
Adds a media item to the worksheets drawing
400 401 402 403 404 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 400 def add_image(={}) image = drawing.add_image() yield image if block_given? image end |
#add_row(values = [], options = {}) {|@rows.last| ... } ⇒ Row Also known as: <<
Adds a row to the worksheet and updates auto fit data.
301 302 303 304 305 306 307 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 301 def add_row(values=[], ={}) Row.new(self, values, ) update_column_info @rows.last.cells, .delete(:widths) ||[], .delete(:style) || [] # update_auto_fit_data @rows.last.cells, options.delete(:widths) || [] yield @rows.last if block_given? @rows.last end |
#add_table(ref, options = {}) {|table| ... } ⇒ Object
needs documentation
390 391 392 393 394 395 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 390 def add_table(ref, ={}) table = Table.new(ref, self, ) @tables << table yield table if block_given? table end |
#cells ⇒ Array
convinience method to access all cells in this worksheet
123 124 125 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 123 def cells rows.flatten end |
#col_style(index, style, options = {}) ⇒ Object
You can also specify the style for specific columns in the call to add_row by using an array for the :styles option
Set the style for cells in a specific column
341 342 343 344 345 346 347 348 349 350 351 352 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 341 def col_style(index, style, ={}) offset = .delete(:row_offset) || 0 @rows[(offset..-1)].each do |r| cells = r.cells[index] next unless cells if cells.is_a?(Array) cells.each { |c| c.style = style } else cells.style = style end end end |
#cols ⇒ Object
returns the sheet data as columnw
329 330 331 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 329 def cols @rows.transpose end |
#column_widths(*args) ⇒ Object
For updating only a single column it is probably easier to just set the width of the ws.column_info.width directly
This is a helper method that Lets you specify a fixed width for multiple columns in a worksheet in one go. Axlsx is sparse, so if you have not set data for a column, you cannot set the width. Setting a fixed column width to nil will revert the behaviour back to calculating the width for you.
361 362 363 364 365 366 367 368 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 361 def column_widths(*args) args.each_with_index do |value, index| next if value == nil Axlsx::validate_unsigned_numeric(value) unless value == nil @column_info[index] ||= Col.new index+1, index+1 @column_info[index].width = value end end |
#dimension ⇒ String
The demensions of a worksheet. This is not actually a required element by the spec, but at least a few other document readers expect this for conversion
165 166 167 168 169 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 165 def dimension dim_start = rows.first.cells.first == nil ? 'A1' : rows.first.cells.first.r dim_end = rows.last.cells.last == nil ? 'AA:200' : rows.last.cells.last.r "#{dim_start}:#{dim_end}" end |
#drawing ⇒ Drawing
the recommended way to work with drawings and charts is Worksheet#add_chart
The drawing associated with this worksheet.
258 259 260 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 258 def drawing @drawing || @drawing = Axlsx::Drawing.new(self) end |
#index ⇒ Integer
The index of this worksheet in the owning Workbook’s worksheets list.
250 251 252 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 250 def index @workbook.worksheets.index(self) end |
#merge_cells(cells) ⇒ Object
Creates merge information for this worksheet. Cells can be merged by calling the merge_cells method on a worksheet.
152 153 154 155 156 157 158 159 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 152 def merge_cells(cells) @merged_cells << if cells.is_a?(String) cells elsif cells.is_a?(Array) cells = cells.sort { |x, y| x.r <=> y.r } "#{cells.first.r}:#{cells.last.r}" end end |
#name_to_cell(name) ⇒ Cell
returns the column and row index for a named based cell
199 200 201 202 203 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 199 def name_to_cell(name) col_index, row_index = *Axlsx::name_to_indices(name) r = rows[row_index] r.cells[col_index] if r end |
#page_margins {|@page_margins| ... } ⇒ PageMargins
Page margins for printing the worksheet.
77 78 79 80 81 82 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 77 def page_margins @page_margins ||= PageMargins.new yield @page_margins if block_given? @page_margins end |
#pn ⇒ String
The part name of this worksheet
232 233 234 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 232 def pn "#{WORKSHEET_PN % (index+1)}" end |
#relationships ⇒ Relationships
The worksheet relationships. This is managed automatically by the worksheet
439 440 441 442 443 444 445 446 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 439 def relationships r = Relationships.new @tables.each do |table| r << Relationship.new(TABLE_R, "../#{table.pn}") end r << Relationship.new(DRAWING_R, "../#{@drawing.pn}") if @drawing r end |
#rels_pn ⇒ String
The relationship part name of this worksheet
238 239 240 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 238 def rels_pn "#{WORKSHEET_RELS_PN % (index+1)}" end |
#rId ⇒ String
The relationship Id of thiw worksheet
244 245 246 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 244 def rId "rId#{index+1}" end |
#row_style(index, style, options = {}) ⇒ Object
You can also specify the style in the add_row call
Set the style for cells in a specific row
318 319 320 321 322 323 324 325 326 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 318 def row_style(index, style, ={}) offset = .delete(:col_offset) || 0 rs = @rows[index] if rs.is_a?(Array) rs.each { |r| r.cells[(offset..-1)].each { |c| c.style = style } } else rs.cells[(offset..-1)].each { |c| c.style = style } end end |
#to_xml_string ⇒ String
Serializes the object
409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 409 def to_xml_string str = '<?xml version="1.0" encoding="UTF-8"?>' str.concat "<worksheet xmlns=\"%s\" xmlns:r=\"%s\">" % [XML_NS, XML_NS_R] str.concat "<sheetPr><pageSetUpPr fitToPage=\"%s\"></pageSetUpPr></sheetPr>" % fit_to_page if fit_to_page str.concat "<dimension ref=\"%s\"></dimension>" % dimension unless rows.size == 0 str.concat "<sheetViews><sheetView tabSelected='%s' workbookViewId='0' showGridLines='%s'><selection activeCell=\"A1\" sqref=\"A1\"/></sheetView></sheetViews>" % [@selected, show_gridlines] if @column_info.size > 0 str << "<cols>" @column_info.each { |col| col.to_xml_string(str) } str.concat '</cols>' end str.concat '<sheetData>' @rows.each_with_index { |row, index| row.to_xml_string(index, str) } str.concat '</sheetData>' str.concat "<autoFilter ref='%s'></autoFilter>" % @auto_filter if @auto_filter str.concat "<mergeCells count='%s'>%s</mergeCells>" % [@merged_cells.size, @merged_cells.reduce('') { |memo, obj| memo += "<mergeCell ref='%s'></mergeCell>" % obj } ] unless @merged_cells.empty? page_margins.to_xml_string(str) if @page_margins str.concat "<drawing r:id='rId1'></drawing>" if @drawing unless @tables.empty? str.concat "<tableParts count='%s'>%s</tableParts>" % [@tables.size, @tables.reduce('') { |memo, obj| memo += "<tablePart r:id='%s'/>" % obj.rId }] end @conditional_formattings.each do |cf| str.concat cf.to_xml_string end str + '</worksheet>' end |