CODEWITHSUNDEEP
rubyexcelaxlsx
facetoe
facetoe

Reputation: 729

Insert multi-line text into spreadsheet with axlsx [Ruby]

I'm trying to create some multi-line cells with the axlsx gem in Ruby. I found this issue which suggested a fix using to_xml_string - but no matter what I try I can't get those multi-line cells!

Here is my current code:

def saveAsXlsx
    Axlsx::Package.new do |p|
        p.workbook.add_worksheet(:name => "Basic Worksheet") do |sheet|
            sheet.add_row @headers
            @headers.each_with_index do |line, i|
                @headerValues[i].unshift @noteValues[i]
                sheet.add_row  @headerValues[i]
                sheet.to_xml_string
            end
        end
        p.serialize('simple.xlsx')
    end
end

If anyone can help me out here I would be most appreciative...

Upvotes: 1

Views: 2243

Answers (2)

randym
randym

Reputation: 2460

Facetoe -

I've a feeling that if you take your code out the block against the package, :preserve value for xml_space is properly initialized within the workbook.

You have highlighted a hidden assumption in the library that assumes you will never be building directly off the yielded package.

Obviously I will make efforts to support this use case.

In the mean time, you are going to save your processor a whole lot of work by doing the following:

p = Axlsx::package.new
p.workbook.add_worksheet do |sheet|
    # - your code - styles is available via sheet 
    # !! you do _not_ need to call to_xml_string
end
p.serialize('foo')

To be honest I never envisioned that someone would do all their report processing in a single method, so this has been most enlightening!

Best,

randym

Upvotes: 1

facetoe
facetoe

Reputation: 729

In case anyone else has this problem, I was using the to_xml_string in the wrong place...

Here is the updated code:

#Save to excel spreadsheet found at file
def saveAsXlsx(file)
    Axlsx::Package.new do |p|
        p.workbook.styles do |s|

            #Column header format
            black_cell = s.add_style :bg_color => "00", :fg_color => "FF", :sz => 14, :alignment => { :horizontal=> :center }
            headerFormat = []
            @headers.size.times {headerFormat << black_cell}

            p.workbook.add_worksheet(:name => "Basic Worksheet") do |sheet|
                sheet.add_row @headers, :style => headerFormat
                numEntries = @headerValues.size
                #Add the values to the spreadsheet
                0.upto(numEntries-1) do |i|
                    sheet.add_row  @headerValues[i], :height => 60, :widths=>[50, :auto, :auto, :auto, :auto]
                end
                #This is necessary to preserve newlines
                sheet.to_xml_string
            end
            p.serialize(file)
        end
    end
end

Upvotes: 0

Related Questions