Class: Axlsx::Worksheet

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

Overview

The Worksheet class represents a worksheet in the workbook.

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(wb, options = {}) ⇒ Worksheet

Note:

the recommended way to manage worksheets is Workbook#add_worksheet

Creates a new worksheet.

Parameters:

  • options (Hash) (defaults to: {})

    a customizable set of options

Options Hash (options):

  • name (String)

    The name of this worksheet.

  • page_margins (Hash)

    A hash containing page margins for this worksheet. @see PageMargins

  • print_options (Hash)

    A hash containing print options for this worksheet. @see PrintOptions

  • show_gridlines (Boolean)

    indicates if gridlines should be shown for this sheet.

See Also:



21
22
23
24
25
26
27
28
29
30
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 21

def initialize(wb, options={})
  self.workbook = wb
  @workbook.worksheets << self
  @sheet_protection = nil

  initialize_page_options(options)
  options.each do |o|
    self.send("#{o[0]}=", o[1]) if self.respond_to? "#{o[0]}="
  end
end

Instance Attribute Details

#workbookWorkbook

The workbook that owns this worksheet

Returns:



66
67
68
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 66

def workbook
  @workbook
end

Class Method Details

.thin_charsString

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

Returns:

  • (String)


10
11
12
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 10

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.

Parameters:

  • cell_def (String|Integer)

    the string defining the cell or range of cells, or the rownumber

Returns:



525
526
527
528
529
530
531
532
533
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 525

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

Note:

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.

Parameters:

  • chart_type (Class)
  • options (Hash) (defaults to: {})

    a customizable set of options

Options Hash (options):

  • start_at (Array)
  • end_at (Array)
  • title (Cell, String)
  • show_legend (Boolean)
  • style (Integer)

Yields:

  • (chart)

See Also:



421
422
423
424
425
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 421

def add_chart(chart_type, options={})
  chart = worksheet_drawing.add_chart(chart_type, options)
  yield chart if block_given?
  chart
end

#add_comment(options = {}) ⇒ Object

Shortcut to worsksheet_comments#add_comment



435
436
437
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 435

def add_comment(options={})
  worksheet_comments.add_comment(options)
end

#add_conditional_formatting(cells, rules) ⇒ Object

Add conditional formatting to this worksheet.

Examples:

This would format column A whenever it is FALSE.

# for a longer example, see examples/example_conditional_formatting.rb (link below)
worksheet.add_conditional_formatting( "A1:A1048576", { :type => :cellIs, :operator => :equal, :formula => "FALSE", :dxfId => 1, :priority => 1 }

Parameters:

  • cells (String)

    The range to apply the formatting to

  • rules (Array|Hash)

    An array of hashes (or just one) to create Conditional formatting rules from.

See Also:



382
383
384
385
386
387
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 382

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.

Parameters:

  • cells (String)

    The cells the validation will apply to.

  • data_validation (hash)

    options defining the validation to apply.

See Also:

  • for an example


394
395
396
397
398
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 394

def add_data_validation(cells, data_validation)
  dv = DataValidation.new(data_validation)
  dv.sqref = cells
  data_validations << dv
end

Adds a new hyperlink to the worksheet

Parameters:

  • options (Hash) (defaults to: {})

    for the hyperlink

Returns:

See Also:



404
405
406
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 404

def add_hyperlink(options={})
  hyperlinks.add(options)
end

#add_image(options = {}) {|image| ... } ⇒ Object

Adds a media item to the worksheets drawing

Parameters:

  • [Hash] (Hash)

    a customizable set of options

Yields:

  • (image)


441
442
443
444
445
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 441

def add_image(options={})
  image = worksheet_drawing.add_image(options)
  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.

Examples:

  • put a vanilla row in your spreadsheet

ws.add_row [1, 'fish on my pl', '8']

  • specify a fixed width for a column in your spreadsheet

# The first column will ignore the content of this cell when calculating column autowidth.
# The second column will include this text in calculating the columns autowidth
# The third cell will set a fixed with of 80 for the column.
# If you need to un-fix a column width, use :auto. That will recalculate the column width based on all content in the column

ws.add_row ['I wish', 'for a fish', 'on my fish wish dish'], :widths=>[:ignore, :auto, 80]

  • specify a fixed height for a row

ws.add_row ['I wish', 'for a fish', 'on my fish wish dish'], :height => 40

  • create and use a style for all cells in the row

blue = ws.styles.add_style :color => "#00FF00"
ws.add_row [1, 2, 3], :style=>blue

  • only style some cells

blue = ws.styles.add_style :color => "#00FF00"
red = ws.styles.add_style :color => "#FF0000"
big = ws.styles.add_style :sz => 40
ws.add_row ["red fish", "blue fish", "one fish", "two fish"], :style=>[red, blue, nil, big] # the last nil is optional

  • force the second cell to be a float value

ws.add_row [3, 4, 5], :types => [nil, :float]

  • use << alias

ws << [3, 4, 5], :types => [nil, :float]

Parameters:

  • options (Hash) (defaults to: {})

    a customizable set of options

Options Hash (options):

  • values (Array)
  • types (Array, Symbol)
  • style (Array, Integer)
  • widths (Array)

    each member of the widths array will affect how auto_fit behavies.

  • height (Float)

    the row's height (in points)

Yields:

Returns:

See Also:



363
364
365
366
367
368
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 363

def add_row(values=[], options={})
  Row.new(self, values, options)
  update_column_info @rows.last.cells, options.delete(:widths) || []
  yield @rows.last if block_given?
  @rows.last
end

#add_table(ref, options = {}) {|tables.last| ... } ⇒ Object

needs documentation

Yields:



428
429
430
431
432
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 428

def add_table(ref, options={})
  tables << Table.new(ref, self, options)
  yield tables.last if block_given?
  tables.last
end

#auto_filterObject

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.

Returns:

  • String



103
104
105
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 103

def auto_filter
  @auto_filter ||= AutoFilter.new self
end

#auto_filter=(v) ⇒ Object

The auto filter range for the worksheet

Parameters:

  • v (String)

See Also:



287
288
289
290
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 287

def auto_filter=(v)
  DataTypeValidator.validate "Worksheet.auto_filter", String, v
  auto_filter.range = v
end

#cellsArray

convinience method to access all cells in this worksheet

Returns:

  • (Array)

    cells



193
194
195
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 193

def cells
  rows.flatten
end

#col_style(index, style, options = {}) ⇒ Object

Note:

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

Parameters:

  • index (Integer)

    the index of the column

  • style (Integer)

    the cellXfs index

  • options (Hash) (defaults to: {})
  • [Integer] (Hash)

    a customizable set of options

See Also:



470
471
472
473
474
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 470

def col_style(index, style, options={})
  offset = options.delete(:row_offset) || 0
  cells = @rows[(offset..-1)].map { |row| row.cells[index] }.flatten.compact
  cells.each { |cell| cell.style = style }
end

#colsObject

returns the sheet data as columnw



95
96
97
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 95

def cols
  @rows.transpose
end

#column_infoSimpleTypedList

Column info for the sheet

Returns:

  • (SimpleTypedList)


120
121
122
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 120

def column_info
  @column_info ||= Cols.new self
end

#column_widths(*widths) ⇒ Object

Note:

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.

Examples:

This would set the first and third column widhts but leave the second column in autofit state.

ws.column_widths 7.2, nil, 3

Parameters:

  • widths (Integer|Float|Fixnum|nil)


454
455
456
457
458
459
460
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 454

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

#commentsArray|SimpleTypedList

The a shortcut to the worksheet_comments list of comments

Returns:

  • (Array|SimpleTypedList)


82
83
84
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 82

def comments
  worksheet_comments.comments if worksheet_comments.has_comments?
end

#dimensionDimension

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

Returns:



221
222
223
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 221

def dimension
  @dimension ||= Dimension.new self
end

#drawingDrawing

Note:

the recommended way to work with drawings and charts is Worksheet#add_chart

The drawing associated with this worksheet.

Returns:

See Also:



320
321
322
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 320

def drawing
  worksheet_drawing.drawing
end

#fit_to_page=(v) ⇒ Boolean

Returns:

  • (Boolean)


271
272
273
274
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 271

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.

Returns:

  • (Boolean)

    Boolean

See Also:



112
113
114
115
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 112

def fit_to_page?
  return false unless self.instance_values.keys.include?('page_setup')
  page_setup.fit_to_page?
end

A typed collection of hyperlinks associated with this worksheet

Returns:



76
77
78
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 76

def hyperlinks
  @hyperlinks ||= WorksheetHyperlinks.new self
end

#indexInteger

The index of this worksheet in the owning Workbook's worksheets list.

Returns:

  • (Integer)


312
313
314
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 312

def index
  @workbook.worksheets.index(self)
end

#initialize_page_options(options) ⇒ Object

Initalizes page margin, setup and print options

Parameters:

  • options (Hash)

    Options passed in from the initializer



34
35
36
37
38
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 34

def initialize_page_options(options)
  @page_margins = PageMargins.new options[:page_margins] if options[:page_margins]
  @page_setup = PageSetup.new options[:page_setup]  if options[:page_setup]
  @print_options = PrintOptions.new options[:print_options] if options[:print_options]
end

#merge_cells(cells) ⇒ Object

Creates merge information for this worksheet. Cells can be merged by calling the merge_cells method on a worksheet.

Examples:

This would merge the three cells C1..E1 #

worksheet.merge_cells "C1:E1"
# you can also provide an array of cells to be merged
worksheet.merge_cells worksheet.rows.first.cells[(2..4)]
#alternatively you can do it from a single cell
worksheet["C1"].merge worksheet["E1"]

Parameters:

  • cells (Array, string)


206
207
208
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 206

def merge_cells(cells)
  merged_cells.add cells
end

#nameString

The name of the worksheet

Returns:

  • (String)


42
43
44
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 42

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

Parameters:

  • name (String)


279
280
281
282
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 279

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

Parameters:

  • name (String)

    The cell or cell range to return. "A1" will return the first cell of the first row.

Returns:



538
539
540
541
542
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 538

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.

Examples:

wb = Axlsx::Package.new.workbook
# using options when creating the worksheet.
ws = wb.add_worksheet :page_margins => {:left => 1.9, :header => 0.1}

# use the set method of the page_margins object
ws.page_margins.set(:bottom => 3, :footer => 0.7)

# set page margins in a block
ws.page_margins do |margins|
  margins.right = 6
  margins.top = 0.2
end

Yields:

Returns:

See Also:



140
141
142
143
144
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 140

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.

Examples:

wb = Axlsx::Package.new.workbook

# using options when creating the worksheet.
ws = wb.add_worksheet :page_setup => {:fit_to_width => 2, :orientation => :landscape}

# use the set method of the page_setup object
ws.page_setup.set(:paper_width => "297mm", :paper_height => "210mm")

# setup page in a block
ws.page_setup do |page|
  page.scale = 80
  page.orientation = :portrait
end

Yields:

Returns:

See Also:



163
164
165
166
167
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 163

def page_setup
  @page_setup ||= PageSetup.new
  yield @page_setup if block_given?
  @page_setup
end

#pnString

The part name of this worksheet

Returns:

  • (String)


294
295
296
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 294

def pn
  "#{WORKSHEET_PN % (index+1)}"
end

Options for printing the worksheet.

Examples:

wb = Axlsx::Package.new.workbook
# using options when creating the worksheet.
ws = wb.add_worksheet :print_options => {:grid_lines => true, :horizontal_centered => true}

# use the set method of the page_margins object
ws.print_options.set(:headings => true)

# set page margins in a block
ws.print_options do |options|
  options.horizontal_centered = true
  options.vertical_centered = true
end

Yields:

Returns:

See Also:



185
186
187
188
189
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 185

def print_options
  @print_options ||= PrintOptions.new
  yield @print_options if block_given?
  @print_options
end

#protect_range(cells) ⇒ ProtectedRange

Note:

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.

Parameters:

  • cells (String|Array)

    The string reference for the cells to protect or an array of cells.

Returns:



214
215
216
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 214

def protect_range(cells)
  protected_ranges.add_range(cells)
end

#relationshipsRelationships

The worksheet relationships. This is managed automatically by the worksheet

Returns:



505
506
507
508
509
510
511
512
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 505

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

Parameters:

  • object (Any)

    the object to search for

Returns:

  • (Integer)

    The index of the object



517
518
519
520
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 517

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_pnString

The relationship part name of this worksheet

Returns:

  • (String)


300
301
302
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 300

def rels_pn
  "#{WORKSHEET_RELS_PN % (index+1)}"
end

#rIdString

The relationship Id of thiw worksheet

Returns:

  • (String)


306
307
308
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 306

def rId
  "rId#{index+1}"
end

#row_style(index, style, options = {}) ⇒ Object

Note:

You can also specify the style in the add_row call

Set the style for cells in a specific row

Parameters:

  • index (Integer)

    or range of indexes in the table

  • style (Integer)

    the cellXfs index

  • options (Hash) (defaults to: {})

    the options used when applying the style

  • [Integer] (Hash)

    a customizable set of options

See Also:



484
485
486
487
488
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 484

def row_style(index, style, options={})
  offset = options.delete(:col_offset) || 0
  cells = cols[(offset..-1)].map { |column| column[index] }.flatten.compact
  cells.each { |cell| cell.style = style }
end

#rowsSimpleTypedList

Note:

The recommended way to manage rows is Worksheet#add_row

The rows in this worksheet

Returns:

  • (SimpleTypedList)

See Also:



90
91
92
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 90

def rows
  @rows ||=  SimpleTypedList.new Row
end

#selectedObject

Deprecated.

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.

Returns:

  • Boolean



264
265
266
267
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 264

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

Deprecated.

Returns:

  • (Boolean)

See Also:



245
246
247
248
249
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 245

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_prSheetPr

The sheet properties for this workbook. Currently only pageSetUpPr -> fitToPage is implemented

Returns:



228
229
230
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 228

def sheet_pr
  @sheet_pr ||= SheetPr.new self
end

#sheet_protection {|@sheet_protection| ... } ⇒ SheetProtection

The sheet protection object for this workbook

Yields:

Returns:

See Also:



49
50
51
52
53
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 49

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

Yields:

Returns:

See Also:

  • [SheetView]


58
59
60
61
62
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 58

def sheet_view
  @sheet_view ||= SheetView.new
  yield @sheet_view if block_given?
  @sheet_view
end

#show_gridlinesObject

Deprecated.

Indicates if the worksheet should show gridlines or not

Returns:

  • Boolean



254
255
256
257
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 254

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

Deprecated.

Indicates if gridlines should be shown in the sheet. This is true by default.

Returns:

  • (Boolean)


236
237
238
239
240
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 236

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

#stylesObject

shortcut method to access styles direclty from the worksheet This lets us do stuff like:

Examples:

p = Axlsx::Package.new
p.workbook.add_worksheet(:name => 'foo') do |sheet|
  my_style = sheet.styles.add_style { :bg_color => "FF0000" }
  sheet.add_row ['Oh No!'], :styles => my_style
end
p.serialize 'foo.xlsx'


553
554
555
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 553

def styles
  @styles ||= self.workbook.styles
end

#tablesArray

The tables in this worksheet

Returns:

  • (Array)

    of Table



70
71
72
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 70

def tables
  @tables ||=  Tables.new self
end

#to_xml_stringString

Serializes the worksheet object to an xml string This intentionally does not use nokogiri for performance reasons

Returns:

  • (String)


493
494
495
496
497
498
499
500
501
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 493

def to_xml_string
  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>'
  str.gsub(/[[:cntrl:]]/,'')
end