Gem axlsx how to set font font_name?

Question

I use axlsx gem to work with xlsx file. Please help me to set font in cells of sheet.

item_style = s.add_style :b => false, :sz => 9,  :font_name => 'courier',
      :alignment => { :horizontal => :left, :vertical => :center, :wrap_text => true}
row = sheet.add_row [item.name, item.price], :style => item_style

But font in cells still 'Arial'. I need any 'mono width' font. I know that 'courier' is not mono width font, use it just for example.

Because I have fixed column width. And I want to know when text in cell takes 2 lines. To set appropriate row height.

Thanks.

Deprivation

Answer 1

Looking at your style declaration, it seems appropriate to me. At the risk of sounding pedantic, you should capitalized the font name.

Combining your bits and the nice example from acsmith, the following code should work fine for you in excel. What software are you using to view the Axlsx file? Not all spreadsheet software fully/implements the OOXML spec.

require 'axlsx'
p = Axlsx::Package.new
wb = p.workbook
item_style = wb.styles.add_style :b => false, :sz => 9,  :font_name => 'Courier',
  :alignment => { :horizontal => :left, :vertical => :center, :wrap_text => true}
wb.add_worksheet(:title => "Worksheet 1") do |sheet|
  sheet.add_row(["text in Courier"], :style => item_style)
end
p.serialize("courier.xlsx")

best

randym

Peterkin

SOLVED. Thank You. And yes you are right. I used libreoffice 3.6.4.3, when I tried open files in the Microsoft Excel 2007 everything, in your and in my files, looks fine. After it I tried to install libreoffice 4.0.0.2. But it's not helped, so libreoffice do not supports this feature. Fortunately my client uses Excel. – Deprivation

Answer 2

I would suggest trying the following mini-example and making sure it works. You need to have the whole thing within a styles block.

p = Axlsx::Package.new
  wb = p.workbook
  wb.styles do |s|
    courier = s.add_style :font_name => "Courier"
    wb.add_worksheet(:title => "Worksheet 1") do |sheet|
      sheet.add_row(["text in Courier"], :style => courier)
    end
  end
p.serialize("Courier.xlsx")

I haven't used axlsx all that much, but I believe that any styles used must be declared in a styles block, and used within that block.

Kyleekylen

I copied and pasted your code, and executed it. The font is still "Arial". But thanks for trying. – Deprivation

Great example acsmith! FYI - Block or inline declarations are fine. – Peterkin

thinking about the comment below from @Peterkin -- Google Drive renders the file properly when I generate it with only the above code. You may want to try checking it in Google Drive, because it'll resolve any possible font name issues you might have. – Kyleekylen

Answer 3

by us Axlsx gem ,font_name can be set like these many ways ,this was work for me:

sheet.add_row ["some data", "","","", "", "","",""], :sz => 9,:height => 16,:font_name => "Arial"   -------------> first way

sheet.rows.last.cells[0].font_name = "Arial"  ----------> second way

sheet["A10"].font_name = "Arial"  -----------> third way

for multiple rows:

sheet["A1:E1"].each { |c| c.font_name = "Arial" } -------> fourth way

@arial_fontfamily = s.add_style :b => 'true', :sz => 10,  :font_name =>   'Arial'  --> small css definition  -------> fifth way

sheet["A1:E1"].each { |c| c.style = @arial_fontfamily  } -------> sixth way

sheet.add_row ["some data", "","","", "", "","",""], :sz => 9,:height => 16,:style => @arial_fontfamily   -------------> seventh way

Grapnel