Multiline text formatting

[PriyankaRPathak]

When cell contains multi line text along with different partial format (bold, strike through or italic) than it doesn't work.

eg.

I have cell A1 with content as :

This is \b Bold the text \n This is the \strike Strike through the text.

\n, \t works fine but \b or \strike doesn't work. Can you guide me how to add partial style in multi line text cell?

As per this post, that 's avoiding those characters? #127.

[randym]

Hi @Priyanka
\b and \strike are not valid OOXML data structures. This kind of thing is possible if we deeply implement multiple rich text runs for cells, however at the current time Axlsx only supports a single run that defines the bold/underline/strike etc for the text data in the entire cell.

§18.4.4 text runs

If you want to have a go at this, fork the repo and have a look at lib/axlsx/workbook/worksheet/cell.rb

[oneiros]

I might need this feature in the near future and from a quick glance I gather the xml needed to achieve this is straight-forward.

I wonder, though, what the API in axlsx should look like. Should axlsx parse a string with \b and \strike as shown above. Or HTML-like tags, like prawn does?

Or would you prefer a more object-oriented approach? Maybe something like this pseudo-code:

text = Axlsx::Text.new
text.append "This is "
text.append "bold", :style => :bold
sheet.add_row [text]

[randym]

@oneiros

I think that as a user of the API, I would prefer to do the least amount of work.
So some kind of macro-language like you defined above is probably easier for the consumer.
However I expect it will be fairly difficult to get right when you think about overlaps, and termination and combination of text run properties.

Can you start with an object oriented approach that follows the specification, and then we can work out some kind of macro language for driving that?

The basic model is:

Cell
  is (Rich Text Inline) §18.3.1.53
     r (Rich Text Run) §18.4.4
       rPr (Run Properties) §18.4.7
       t (Text) §18.4.12

We need to implement r(Rich Text Run) of which a cell can have many. That is serialized directly to the worksheet when shared_string is off inside of an elelement, and serialized into a element inside of the shared strings table.

I image something like this, but the real work will happen in the RichTextRun object, parsing out the options to create a rPr object, a text object and manage the serialization of those parts, and Cell will need a fairly large re-write to handle existing 'inline styles' and proper serialization.

# 
class Axlsx::RichText

  def initialize(text, options={})
     @runs = SimpleTypedList.new(RichTextRun)
     add_run(text, options)
     yield self if block_given?
  end

  attr_reader :runs

  def add_run(text, options={})
    @runs << RichTextRun.new(text, options)
  end

  def to_xml_string(str='', tag='si')
    str << "<#{tag}>"
    runs.each{ |run| run.to_xml_string(str) }
    str << "</#{tag}>"
  end
end

That should give us usage like this

text_run = Axlsx::RichText.new('this is') do |rich_text|
  rich_text.add_run('bold', :b => true)
  rich_text.add_run(', but this is ')
  rich_text.add_run('underlined and red.', :u => :double, :color => 'FF0000')
  rich_text.add_run('in the end - this is just naked text')
end
sheet.add_row [text_run, 'simple inline string']

One caveat is that we need to maintain backwards compatibility for Cell and the inline style attributes so when someone sets.

  rows.first.cells.first.b = true

We need to be sure to create a new run for all of the text or update all of the runs in that cell.

Number of other small things that come to mind, like what do we return for 'value' when the consumer has created this kind of rich text, but those details will most likely sort themselves out over time.

[kbaum]

I am also in need of this unfortunately. Has anyone made any progress on this?

thx!

[randym]

not a stitch, as far as I know @kbaum
care to send in an awesome pull request?

[kbaum]

We are currently manually generating the excel with erb but if we change over to axlsx, I may take a stab at it. Thx!

Sent from my iPhone

On Aug 29, 2013, at 9:47 AM, "Randy Morgan (@morgan_randy)" notifications@github.com wrote:

not a stitch, as far as I know @kbaum
care to send in an awesome pull request?

—
Reply to this email directly or view it on GitHub.

[randym]

@jurriaan are we implementing these deep text runs?

looks like we had a bump from @kbaum - just following up on ship/close intention on this issue but...
Requires a pretty big refactoring of Cell, but I would be massively stoked to see the text run tree fully flushed out according to the specs!

[jurriaan]

Yeah, I'm going to look at it in a moment.
First I'm going to create a basic working version based on comment #134 (comment). Maybe I'll just create a separate version of Cell for text runs, until it's al working properly. I think I can create a basic version fairly easily.

[jurriaan]

I've merged a basic working version (043dc9a) including some tests.

[kbaum]

Wow! Merged in already? That was fast!

On Sat, Mar 1, 2014 at 4:09 PM, Jurriaan Pruis notifications@github.comwrote:

I've merged a basic working version (043dc9ahttps://github.com/randym/axlsx/commit/043dc9ad)
including some tests.

Reply to this email directly or view it on GitHubhttps://github.com//issues/134#issuecomment-36436702
.

[songjiayang]

i find rich_text.rb had been required in workbook.rb, but when i run myself test, i meet in <main>: uninitialized constant Axlsx::RichText (NameError)

my code:

require 'axlsx'

p = Axlsx::Package.new
p.use_shared_strings = true
wb = p.workbook

rt = Axlsx::RichText.new
rt.add_run("B+H Architects \n", :b => true)
rt.add_run("Project: Project Name \n", :b => true)
rt.add_run("Date: 2014-12-12")
wb.styles do |s|
  header_cell = s.add_style :bg_color => "ff", :fg_color => "00", :sz => 14, :b => true, :border => { :style => :thin, :color => "00" }, :alignment => { :horizontal=> :center, :vertical => :center }
  center_cell = s.add_style :alignment => { :horizontal=> :center, :vertical => :center }
  wb.add_worksheet(:name => "project materials") do |sheet|
    column_names = %w(symbol description area application brand_name name dimension remark other_brand)
    column_headers = ["Symbol", "Description", "Area", "Application", "Manufacturer", "Name",  "Dimension",  "Remark", "Acceptable Brand", "Image"]
    sheet.column_widths 10, 20, 20, 20, 20, 30, 20, 20, 20, 20
    sheet.add_row column_headers, :height => 30, :style => Array.new( column_headers.size, header_cell)
    sheet.add_row [rt], :style => Array.new(0, (s.add_style :alignment => { :horizontal=> :center, :wrap_text => true }))
    sheet.merge_cells('A2:G2')
  end
end

p.serialize "test1.xlsx"

someone meet the same problem ???

[jurriaan]

Which version are you using?

[songjiayang]

@jurriaan axlsx-2.0.1 thanks .. i install axlsx in rails's gemfile.

[jurriaan]

RichText isn't released yet. You have to use the master branch to be able to use it..

[songjiayang]

@jurriaan thanks ...

[kenliu]

nice work on this! I added a RichText field and found it to be pretty easy to work with.

[jurriaan]

@kenliu Thanks, I think this issue can be closed for now :)

[rsl]

can this get put in a gem version? two years [with no activity] seems a long long time to... wait.