3

I would like to be able to use the Axlsx gem to write out an Excel spreadsheet with different styles applied to different columns.

I am writing an Array of Hashes an entire row at a time and, as a result, I cannot seem to format the columns differently as needed.

I don't know how to say, "for columns A-D use default styling, however for column E and F, use horizontal center alignment".

require 'axlsx'

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

# Default Style for all cells
standard_text = wb.styles.add_style( :alignment => {:vertical=>:center, :wrap_text=>true} )

# Custom styling to be applied to cells in rows E and F only
custom_text = wb.styles.add_style( :alignment => {:horizontal=>:center} )

assessment_technology_hashes.each do |rows|
  sheet.add_row(rows.values, :height => 35, :style => standard_text)
end

Here is the structure of the Array of hashes for rows that is being written with add_row:

{:vendor_name=>"vendor", :link=>"Link\n", :importance=>"Low Priority", :score=>"5", :overall_score=>"4.5", :match=>"Yes", :access=>"Anywhere", :title=>"Full Title"}
{:vendor_name=>"vendor2", :link=>"Link2\n", :importance=>"Medium Priority", :score=>"7", :overall_score=>"8.5", :match=>"Yes", :access=>"Typical", :title=>"Full Title"}
...
...

In this scenario, is the correct approach to write all of the data with the primary formatting standard_text that is desired, and then apply the custom formatting AFTER all the data has been written?

I thought this might work in the loop to only write out rows when the condition is true but it always returns false:

sheet.add_row(rows.values, :height => 35, :style => custom_text) if rows.key?(:overall_score) || rows.key?(:match)

Can someone point me in the correct direction to do this?

the Tin Man
  • 158,662
  • 42
  • 215
  • 303
Kurt W
  • 321
  • 2
  • 15
  • To be clear, I read http://www.rubydoc.info/gems/axlsx/ and https://github.com/randym/axlsx/blob/master/examples/example.rb prior to posting. It seems that all the examples assume that you have greater control over the rows you write as you write them. I probably just haven't structured my data appropriately to have that level of granularity, but I'm also not sure how to get there. Any help is greatly appreciated, thanks ! – Kurt W Apr 12 '16 at 22:11
  • Have you solved your issue ? I wrote you an answer, was it somehow helpfull ? – Volodymyr Balytskyy May 23 '16 at 10:37

1 Answers1

1

Here is how I do it.

First, I generate my styles like this:

p  = Axlsx::Package.new
wb = p.workbook
  # styles
  style_1 = wb.styles.add_style bg_color: '3492ff', font_name: 'Calibri'
  style_2 = wb.styles.add_style bg_color: 'ff8800', font_name: 'Arial'

After my styles are read, I create a worksheet:

wb.add_worksheet(name: 'Example') do |sheet|

After worksheet has been created I am able to add data by calling add_row. And as a last argument, I pass array of styles. Example:

sheet.add_row ['Column 1', 'Column2'], style: [style_1, style_2]

In this way I am able to style individual column's cell. In case you don't want to apply style_2, just type [style_1, nil].

Here is the concatenated code

p  = Axlsx::Package.new
wb = p.workbook
  # styles
  style_1 = wb.styles.add_style bg_color: '3492ff', font_name: 'Calibri'
  style_2 = wb.styles.add_style bg_color: 'ff8800', font_name: 'Arial'
  # worksheet
  wb.add_worksheet(name: 'Example') do |sheet|
    sheet.add_row ['Column 1', 'Column2'], style: [style_1, style_2]
  end
end
Volodymyr Balytskyy
  • 577
  • 1
  • 7
  • 19
  • Thanks very much for this. I'm glad a reply finally cycled through. I found a way to do this a few days after posting but it was a post-write fix which was sloppy. I was using `sheet.rows[x].cells[1].style` to write styles after the fact. It works fine, but it's not ideal and your code works with less code and processing. Here's what I was doing for reference (since it seems .cells isn't super well documented but is handy): `sheet.rows[x].cells[1].style = link_text` & `sheet.rows[x].cells[2].style = custom_text`. – Kurt W May 23 '16 at 23:14
  • @KurtW I am glad my answer helped you. And unfortunately the true is that this gem isn’t well documented, very loose. I had to read source code to grasp some knowledge. And right now I learned form you, didn’t knew we could pass styles like that, because of that I up voted your question. Happy coding man and one line at the time! – Volodymyr Balytskyy May 23 '16 at 23:21
  • : - ) -- Thanks for the note. – Kurt W May 26 '16 at 20:43