Class: Axlsx::Worksheet
- Inherits:
-
Object
- Object
- Axlsx::Worksheet
- Includes:
- OptionsParser
- Defined in:
- lib/axlsx/workbook/worksheet/worksheet.rb
Overview
The Worksheet class represents a worksheet in the workbook.
Instance Attribute Summary collapse
-
#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.
-
#add_chart(chart_type, options = {}) {|chart| ... } ⇒ Object
Adds a chart to this worksheets drawing.
-
#add_comment(options = {}) ⇒ Object
Shortcut to worsksheet_comments#add_comment.
-
#add_conditional_formatting(cells, rules) ⇒ Object
Add conditional formatting to this worksheet.
-
#add_data_validation(cells, data_validation) ⇒ Object
Add data validation to this worksheet.
-
#add_hyperlink(options = {}) ⇒ WorksheetHyperlink
Adds a new hyperlink to the 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 = {}) {|tables.last| ... } ⇒ Object
needs documentation.
-
#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_filter=(v) ⇒ Object
The auto filter range for the worksheet.
-
#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_info ⇒ SimpleTypedList
Column info for the sheet.
-
#column_widths(*widths) ⇒ Object
This is a helper method that Lets you specify a fixed width for multiple columns in a worksheet in one go.
-
#comments ⇒ Array|SimpleTypedList
The a shortcut to the worksheet_comments list of comments.
-
#dimension ⇒ Dimension
The dimensions of a worksheet.
-
#drawing ⇒ Drawing
The drawing associated with this worksheet.
- #fit_to_page=(v) ⇒ Boolean
-
#fit_to_page? ⇒ Boolean
Indicates if the worksheet will be fit by witdh or height to a specific number of pages.
-
#header_footer {|@header_footer| ... } ⇒ HeaderFooter
Options for headers and footers.
-
#hyperlinks ⇒ WorksheetHyperlinks
A typed collection of hyperlinks 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.
-
#initialize_page_options(options) ⇒ Object
Initalizes page margin, setup and print options.
-
#merge_cells(cells) ⇒ Object
Creates merge information for this worksheet.
-
#name ⇒ String
The name of the worksheet.
-
#name=(name) ⇒ Object
The name of the worksheet The name of a worksheet must be unique in the workbook, and must not exceed 31 characters.
-
#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.
-
#page_setup {|@page_setup| ... } ⇒ PageSetup
Page setup settings for printing the worksheet.
-
#pn ⇒ String
The part name of this worksheet.
-
#print_options {|@print_options| ... } ⇒ PrintOptions
Options for printing the worksheet.
-
#protect_range(cells) ⇒ ProtectedRange
Adds a new protected cell range to the worksheet.
-
#relationships ⇒ Relationships
The worksheet relationships.
-
#relationships_index_of(object) ⇒ Integer
identifies the index of an object withing the collections used in generating relationships for the worksheet.
-
#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.
-
#rows ⇒ SimpleTypedList
The rows in this worksheet.
-
#sanitize(str) ⇒ String
returns the provided string with all invalid control charaters removed.
-
#selected ⇒ Object
deprecated
Deprecated.
Use SheetView#tab_selected instead.
-
#selected=(v) ⇒ Boolean
deprecated
Deprecated.
Use SheetView#tab_selected= instead.
-
#sheet_calc_pr ⇒ SheetCalcPr
The sheet calculation properties.
-
#sheet_pr ⇒ SheetPr
The sheet properties for this workbook.
-
#sheet_protection {|@sheet_protection| ... } ⇒ SheetProtection
The sheet protection object for this workbook.
-
#sheet_view {|@sheet_view| ... } ⇒ SheetView
The sheet view object for this worksheet.
-
#show_gridlines ⇒ Object
deprecated
Deprecated.
Use SheetView#show_grid_lines instead.
-
#show_gridlines=(v) ⇒ Boolean
deprecated
Deprecated.
Use SheetView#show_grid_lines= instead.
-
#styles ⇒ Object
shortcut method to access styles direclty from the worksheet This lets us do stuff like:.
-
#tables ⇒ Array
The tables in this worksheet.
-
#to_xml_string ⇒ String
Serializes the worksheet object to an xml string This intentionally does not use nokogiri for performance reasons.
Methods included from OptionsParser
Constructor Details
#initialize(wb, options = {}) ⇒ Worksheet
the recommended way to manage worksheets is Workbook#add_worksheet
Creates a new worksheet.
24 25 26 27 28 29 30 31 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 24 def initialize(wb, ={}) self.workbook = wb @workbook.worksheets << self @sheet_protection = nil () end |
Instance Attribute Details
#workbook ⇒ Workbook
The workbook that owns this worksheet
74 75 76 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 74 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
11 12 13 14 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 11 def self.thin_chars # removed 'e' and 'y' from this list - as a GUESS @thin_chars ||= "^.acfijklrstxzFIJL()-" end |
Instance Method Details
#[](cell_def) ⇒ Cell, Array
Returns the cell or cells defined using excel style A1:B3 references.
557 558 559 560 561 562 563 564 565 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 557 def [] (cell_def) return rows[cell_def] if cell_def.is_a?(Integer) parts = cell_def.split(':').map{ |part| name_to_cell part } if parts.size == 1 parts.first else range(*parts) end 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.
444 445 446 447 448 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 444 def add_chart(chart_type, ={}) chart = worksheet_drawing.add_chart(chart_type, ) yield chart if block_given? chart end |
#add_comment(options = {}) ⇒ Object
Shortcut to worsksheet_comments#add_comment
458 459 460 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 458 def add_comment(={}) worksheet_comments.add_comment() end |
#add_conditional_formatting(cells, rules) ⇒ Object
Add conditional formatting to this worksheet.
405 406 407 408 409 410 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 405 def add_conditional_formatting(cells, rules) cf = ConditionalFormatting.new( :sqref => cells ) cf.add_rules rules conditional_formattings << cf conditional_formattings end |
#add_data_validation(cells, data_validation) ⇒ Object
Add data validation to this worksheet.
417 418 419 420 421 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 417 def add_data_validation(cells, data_validation) dv = DataValidation.new(data_validation) dv.sqref = cells data_validations << dv end |
#add_hyperlink(options = {}) ⇒ WorksheetHyperlink
Adds a new hyperlink to the worksheet
427 428 429 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 427 def add_hyperlink(={}) hyperlinks.add() end |
#add_image(options = {}) {|image| ... } ⇒ Object
Adds a media item to the worksheets drawing
464 465 466 467 468 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 464 def add_image(={}) image = worksheet_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.
386 387 388 389 390 391 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 386 def add_row(values=[], ={}) Row.new(self, values, ) update_column_info @rows.last.cells, .delete(:widths) || [] yield @rows.last if block_given? @rows.last end |
#add_table(ref, options = {}) {|tables.last| ... } ⇒ Object
needs documentation
451 452 453 454 455 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 451 def add_table(ref, ={}) tables << Table.new(ref, self, ) yield tables.last if block_given? tables.last end |
#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.
111 112 113 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 111 def auto_filter @auto_filter ||= AutoFilter.new self end |
#auto_filter=(v) ⇒ Object
The auto filter range for the worksheet
310 311 312 313 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 310 def auto_filter=(v) DataTypeValidator.validate "Worksheet.auto_filter", String, v auto_filter.range = v end |
#cells ⇒ Array
convinience method to access all cells in this worksheet
216 217 218 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 216 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
493 494 495 496 497 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 493 def col_style(index, style, ={}) offset = .delete(:row_offset) || 0 cells = @rows[(offset..-1)].map { |row| row.cells[index] }.flatten.compact cells.each { |cell| cell.style = style } end |
#cols ⇒ Object
returns the sheet data as columnw
103 104 105 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 103 def cols @rows.transpose end |
#column_info ⇒ SimpleTypedList
Column info for the sheet
128 129 130 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 128 def column_info @column_info ||= Cols.new self end |
#column_widths(*widths) ⇒ Object
For updating only a single column it is probably easier to just set the width of the ws.column_info[col_index].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 on the next call to add_row.
477 478 479 480 481 482 483 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 477 def column_widths(*widths) widths.each_with_index do |value, index| next if value == nil Axlsx::validate_unsigned_numeric(value) unless value == nil find_or_create_column_info(index).width = value end end |
#comments ⇒ Array|SimpleTypedList
The a shortcut to the worksheet_comments list of comments
90 91 92 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 90 def comments worksheet_comments.comments if worksheet_comments.has_comments? end |
#dimension ⇒ Dimension
The dimensions 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
244 245 246 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 244 def dimension @dimension ||= Dimension.new self end |
#drawing ⇒ Drawing
the recommended way to work with drawings and charts is Worksheet#add_chart
The drawing associated with this worksheet.
343 344 345 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 343 def drawing worksheet_drawing.drawing end |
#fit_to_page=(v) ⇒ Boolean
294 295 296 297 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 294 def fit_to_page=(v) warn('axlsx::DEPRECIATED: Worksheet#fit_to_page has been depreciated. This value will automatically be set for you when you use PageSetup#fit_to.') fit_to_page? end |
#fit_to_page? ⇒ Boolean
Indicates if the worksheet will be fit by witdh or height to a specific number of pages. To alter the width or height for page fitting, please use page_setup.fit_to_widht or page_setup.fit_to_height. If you want the worksheet to fit on more pages (e.g. 2x2), set PageSetup#fit_to_width and PageSetup#fit_to_height accordingly.
120 121 122 123 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 120 def fit_to_page? return false unless self.instance_values.keys.include?('page_setup') page_setup.fit_to_page? end |
#header_footer {|@header_footer| ... } ⇒ HeaderFooter
Options for headers and footers.
208 209 210 211 212 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 208 def @header_footer ||= HeaderFooter.new yield @header_footer if block_given? @header_footer end |
#hyperlinks ⇒ WorksheetHyperlinks
A typed collection of hyperlinks associated with this worksheet
84 85 86 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 84 def hyperlinks @hyperlinks ||= WorksheetHyperlinks.new self end |
#index ⇒ Integer
The index of this worksheet in the owning Workbook's worksheets list.
335 336 337 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 335 def index @workbook.worksheets.index(self) end |
#initialize_page_options(options) ⇒ Object
Initalizes page margin, setup and print options
35 36 37 38 39 40 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 35 def () @page_margins = PageMargins.new [:page_margins] if [:page_margins] @page_setup = PageSetup.new [:page_setup] if [:page_setup] @print_options = PrintOptions.new [:print_options] if [:print_options] @header_footer = HeaderFooter.new [:header_footer] if [:header_footer] end |
#merge_cells(cells) ⇒ Object
Creates merge information for this worksheet. Cells can be merged by calling the merge_cells method on a worksheet.
229 230 231 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 229 def merge_cells(cells) merged_cells.add cells end |
#name ⇒ String
The name of the worksheet
44 45 46 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 44 def name @name ||= "Sheet" + (index+1).to_s end |
#name=(name) ⇒ Object
The name of the worksheet The name of a worksheet must be unique in the workbook, and must not exceed 31 characters
302 303 304 305 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 302 def name=(name) validate_sheet_name name @name=Axlsx::coder.encode(name) end |
#name_to_cell(name) ⇒ Cell
returns the column and row index for a named based cell
570 571 572 573 574 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 570 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.
148 149 150 151 152 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 148 def page_margins @page_margins ||= PageMargins.new yield @page_margins if block_given? @page_margins end |
#page_setup {|@page_setup| ... } ⇒ PageSetup
Page setup settings for printing the worksheet.
171 172 173 174 175 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 171 def page_setup @page_setup ||= PageSetup.new yield @page_setup if block_given? @page_setup end |
#pn ⇒ String
The part name of this worksheet
317 318 319 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 317 def pn "#{WORKSHEET_PN % (index+1)}" end |
#print_options {|@print_options| ... } ⇒ PrintOptions
Options for printing the worksheet.
193 194 195 196 197 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 193 def @print_options ||= PrintOptions.new yield @print_options if block_given? @print_options end |
#protect_range(cells) ⇒ ProtectedRange
When using an array of cells, a contiguous range is created from the minimum top left to the maximum top bottom of the cells provided.
Adds a new protected cell range to the worksheet. Note that protected ranges are only in effect when sheet protection is enabled.
237 238 239 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 237 def protect_range(cells) protected_ranges.add_range(cells) end |
#relationships ⇒ Relationships
The worksheet relationships. This is managed automatically by the worksheet
537 538 539 540 541 542 543 544 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 537 def relationships r = Relationships.new r + [tables.relationships, worksheet_comments.relationships, hyperlinks.relationships, worksheet_drawing.relationship].flatten.compact || [] r end |
#relationships_index_of(object) ⇒ Integer
identifies the index of an object withing the collections used in generating relationships for the worksheet
549 550 551 552 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 549 def relationships_index_of(object) objects = [tables.to_a, worksheet_comments.comments.to_a, hyperlinks.to_a, worksheet_drawing.drawing].flatten.compact || [] objects.index(object) end |
#rels_pn ⇒ String
The relationship part name of this worksheet
323 324 325 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 323 def rels_pn "#{WORKSHEET_RELS_PN % (index+1)}" end |
#rId ⇒ String
The relationship Id of thiw worksheet
329 330 331 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 329 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
507 508 509 510 511 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 507 def row_style(index, style, ={}) offset = .delete(:col_offset) || 0 cells = cols[(offset..-1)].map { |column| column[index] }.flatten.compact cells.each { |cell| cell.style = style } end |
#rows ⇒ SimpleTypedList
The recommended way to manage rows is Worksheet#add_row
The rows in this worksheet
98 99 100 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 98 def rows @rows ||= SimpleTypedList.new Row end |
#sanitize(str) ⇒ String
returns the provided string with all invalid control charaters removed.
531 532 533 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 531 def sanitize(str) str.gsub(CONTROL_CHAR_REGEX, '') end |
#selected ⇒ Object
Use SheetView#tab_selected instead.
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.
287 288 289 290 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 287 def selected warn('axlsx::DEPRECIATED: Worksheet#selected has been depreciated. This value can get over SheetView#tab_selected.') sheet_view.tab_selected end |
#selected=(v) ⇒ Boolean
Use SheetView#tab_selected= instead.
268 269 270 271 272 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 268 def selected=(v) warn('axlsx::DEPRECIATED: Worksheet#selected= has been depreciated. This value can be set over SheetView#tab_selected=.') Axlsx::validate_boolean v sheet_view.tab_selected = v end |
#sheet_calc_pr ⇒ SheetCalcPr
The sheet calculation properties
50 51 52 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 50 def sheet_calc_pr @sheet_calc_pr ||= SheetCalcPr.new end |
#sheet_pr ⇒ SheetPr
The sheet properties for this workbook. Currently only pageSetUpPr -> fitToPage is implemented
251 252 253 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 251 def sheet_pr @sheet_pr ||= SheetPr.new self end |
#sheet_protection {|@sheet_protection| ... } ⇒ SheetProtection
The sheet protection object for this workbook
57 58 59 60 61 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 57 def sheet_protection @sheet_protection ||= SheetProtection.new yield @sheet_protection if block_given? @sheet_protection end |
#sheet_view {|@sheet_view| ... } ⇒ SheetView
The sheet view object for this worksheet
66 67 68 69 70 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 66 def sheet_view @sheet_view ||= SheetView.new yield @sheet_view if block_given? @sheet_view end |
#show_gridlines ⇒ Object
Use SheetView#show_grid_lines instead.
Indicates if the worksheet should show gridlines or not
277 278 279 280 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 277 def show_gridlines warn('axlsx::DEPRECIATED: Worksheet#show_gridlines has been depreciated. This value can get over SheetView#show_grid_lines.') sheet_view.show_grid_lines end |
#show_gridlines=(v) ⇒ Boolean
Use SheetView#show_grid_lines= instead.
Indicates if gridlines should be shown in the sheet. This is true by default.
259 260 261 262 263 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 259 def show_gridlines=(v) warn('axlsx::DEPRECIATED: Worksheet#show_gridlines= has been depreciated. This value can be set over SheetView#show_grid_lines=.') Axlsx::validate_boolean v sheet_view.show_grid_lines = v end |
#styles ⇒ Object
shortcut method to access styles direclty from the worksheet This lets us do stuff like:
585 586 587 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 585 def styles @styles ||= self.workbook.styles end |
#tables ⇒ Array
The tables in this worksheet
78 79 80 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 78 def tables @tables ||= Tables.new self end |
#to_xml_string ⇒ String
Serializes the worksheet object to an xml string This intentionally does not use nokogiri for performance reasons
516 517 518 519 520 521 522 523 524 525 |
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 516 def to_xml_string auto_filter.apply if auto_filter.range str = '<?xml version="1.0" encoding="UTF-8"?>' str << worksheet_node serializable_parts.each do |item| item.to_xml_string(str) if item end str << '</worksheet>' sanitize(str) end |