Method: Writexlsx::Worksheet#write_rich_string

Defined in:

lib/write_xlsx/worksheet.rb

#write_rich_string(*args) ⇒ Object

:call-seq:

write_rich_string(row, column, (string | format, string)+,  [,cell_format] )

The write_rich_string() method is used to write strings with multiple formats. The method receives string fragments prefixed by format objects. The final format object is used as the cell format.

write_rich_string methods return:

For example to write the string “This is bold and this is italic” you would use the following:

bold   = workbook.add_format(:bold   => 1)
italic = workbook.add_format(:italic => 1)

worksheet.write_rich_string('A1',
    'This is ', bold, 'bold', ' and this is ', italic, 'italic')

The basic rule is to break the string into fragments and put a format object before the fragment that you want to format. For example:

# Unformatted string.
  'This is an example string'

# Break it into fragments.
  'This is an ', 'example', ' string'

# Add formatting before the fragments you want formatted.
  'This is an ', format, 'example', ' string'

# In WriteXLSX.
worksheet.write_rich_string('A1',
    'This is an ', format, 'example', ' string')

String fragments that don’t have a format are given a default format. So for example when writing the string “Some bold text” you would use the first example below but it would be equivalent to the second:

# With default formatting:
bold    = workbook.add_format(:bold => 1)

worksheet.write_rich_string('A1',
    'Some ', bold, 'bold', ' text')

# Or more explicitly:
bold    = workbook.add_format(:bold => 1)
default = workbook.add_format

worksheet.write_rich_string('A1',
    default, 'Some ', bold, 'bold', default, ' text')

As with Excel, only the font properties of the format such as font name, style, size, underline, color and effects are applied to the string fragments. Other features such as border, background and alignment must be applied to the cell.

The write_rich_string() method allows you to do this by using the last argument as a cell format (if it is a format object). The following example centers a rich string in the cell:

bold   = workbook.add_format(:bold  => 1)
center = workbook.add_format(:align => 'center')

worksheet.write_rich_string('A5',
    'Some ', bold, 'bold text', ' centered', center)

See the rich_strings.rb example in the distro for more examples.

bold   = workbook.add_format(:bold        => 1)
italic = workbook.add_format(:italic      => 1)
red    = workbook.add_format(:color       => 'red')
blue   = workbook.add_format(:color       => 'blue')
center = workbook.add_format(:align       => 'center')
super  = workbook.add_format(:font_script => 1)

# Write some strings with multiple formats.
worksheet.write_rich_string('A1',
    'This is ', bold, 'bold', ' and this is ', italic, 'italic')

worksheet.write_rich_string('A3',
    'This is ', red, 'red', ' and this is ', blue, 'blue')

worksheet.write_rich_string('A5',
    'Some ', bold, 'bold text', ' centered', center)

worksheet.write_rich_string('A7',
    italic, 'j = k', super, '(n-1)', center)

As with write_sting() the maximum string size is 32767 characters. See also the note about “Cell notation”.

Raises:

• (WriteXLSXInsufficientArgumentError)

# File 'lib/write_xlsx/worksheet.rb', line 2272

def write_rich_string(*args)
  # Check for a cell reference in A1 notation and substitute row and column
  row, col, *rich_strings = row_col_notation(args)
  raise WriteXLSXInsufficientArgumentError if [row, col, rich_strings[0]].include?(nil)

  # If the last arg is a format we use it as the cell format.
  if rich_strings[-1].respond_to?(:xf_index)
    xf = rich_strings.pop
  else
    xf = nil
  end

  # Check that row and col are valid and store max and min values
  check_dimensions(row, col)
  store_row_col_max_min_values(row, col)

  # Create a temp XML::Writer object and use it to write the rich string
  # XML to a string.
  writer = Package::XMLWriterSimple.new

  fragments, length = rich_strings_fragments(rich_strings)
  # can't allow 2 formats in a row
  return -4 unless fragments

  # If the first token is a string start the <r> element.
  writer.start_tag('r') if !fragments[0].respond_to?(:xf_index)

  # Write the XML elements for the format string fragments.
  fragments.each do |token|
    if token.respond_to?(:xf_index)
      # Write the font run.
      writer.start_tag('r')
      write_font(writer, token)
    else
      # Write the string fragment part, with whitespace handling.
      attributes = []

      attributes << 'xml:space' << 'preserve' if token =~ /^\s/ || token =~ /\s$/
      writer.data_element('t', token, attributes)
      writer.end_tag('r')
    end
  end

  # Add the XML string to the shared string table.
  index = shared_string_index(writer.string)

  store_data_to_table(StringCellData.new(self, row, col, index, xf))
end