Axlsx - Formatting text within a cell

Question

I can't seem to find any information on whether it's possible to fill a single cell with multiple formatting options.

For example, I want cell A1 to be filled with the text: "Hello world, this is excel"

Is this possible and if so what syntax do I use to do this?

Anselmo

Answer 1

For inline styling, use rich text. Here is an example from the axlsx page:

  p = Axlsx::Package.new
  p.use_shared_strings = true
  wb = p.workbook
  wrap_text = wb.styles.add_style({:alignment => {:horizontal => :center, :vertical => :center, :wrap_text => true}}  )
  rt = Axlsx::RichText.new
  rt.add_run('I\'m bold, ', :b => true)
  rt.add_run('I\'m italic, ', :i => true)
  rt.add_run('I\'m strike' + "\n", :strike => true)
  rt.add_run('I\'m bold, italic and strike' + "\n", :b => true, :i => true, :strike => true)
  rt.add_run('I\'m style-less :D')
  wb.add_worksheet(:name => "RichText") do | sheet |
    sheet.add_row [rt], :style => wrap_text
  end
  p.serialize 'rich_text.xlsx'

---

If you are just wanting to apply multiple styles to a cell, you need differential styling. There are two options:

First, do it manually. Basically you state your style type is :dxf. The default is :xf. Everything else is the same. From the docs on styles.rb:

p = Axlsx::Package.new
wb = p.workbook
ws = wb.add_worksheet

# define your styles
profitable = wb.styles.add_style(:bg_color => "FFFF0000",
                           :fg_color=>"#FF000000",
                           :type => :dxf)

ws.add_row ["Genreated At:", Time.now], :styles=>[nil, date_time]
ws.add_row ["Previous Year Quarterly Profits (JPY)"], :style=>title
ws.add_row ["Quarter", "Profit", "% of Total"], :style=>title
ws.add_row ["Q1", 4000, 40], :style=>[title, currency, percent]
ws.add_row ["Q2", 3000, 30], :style=>[title, currency, percent]
ws.add_row ["Q3", 1000, 10], :style=>[title, currency, percent]
ws.add_row ["Q4", 2000, 20], :style=>[title, currency, percent]

ws.add_conditional_formatting("A1:A7", { :type => :cellIs, :operator => :greaterThan, :formula => "2000", :dxfId => profitable, :priority => 1 })
f = File.open('example_differential_styling', 'w')
p.serialize(f)

Second, use the axlsx_styler gem. It makes it very easy to apply multiple styles.

Cede

Answer 2

I assume you're talking about this gem?

https://github.com/randym/axlsx

It looks like you can't:

https://github.com/randym/axlsx/issues/134

However this is an old thread and maybe it's been implemented since. Try the approach in the first post on that page: the poster is complaining that it doesn't work but it's worth a try.

Actually, reading further down it looks like someone has added a RichText format option which should allow this. I'm not sure how to do this though, grep the documentation for it.

Cadell