Axlsx Merge Cells Inside a Style

AXLSX merge cells inside a style

Yes give this a try (*Disclaimer I did not actually test these methods but I have used similar functionality in the past)

def merge_last_row(sheet,options ={})
  last_row = sheet.rows.last.index + 1
  first_col,last_col = options[:columns]
  if first_col && last_col
    sheet.merge_cells "#{first_col}#{last_row}:#{last_col}#{last_row}"
  else
    sheet.merge_cells sheet.rows.last
  end
  sheet.rows.last.style = style if options[:style]
end

so to do what you want it would be

merge_last_row sheet, columns:["A","E"]
sheet.add_row [I18n.t('foo.some_label').upcase]
merge_last_row sheet, columns:["B","E"], style:title

If the last row contains data in A-E then the columns can be left empty and it will merge the whole row. If it does not you could add an option for filling the columns like so

def fill_columns(sheet,column_count,options={})
  row = options[:row_data] || []
  (column_count - row.count).times do 
    row << nil
  end
  sheet.add_row row
end

calls as

my_row = ["Hello","World"]
fill_columns sheet, 5,row_data: my_row
# this will add a row like["Hello","World",nil,nil,nil]
# so that it will merge properly across the columns A-E
merge_last_row sheet

If you are going to use these consistently then patching these functions into Worksheet might make more sense so you don't have to pass the sheet object.

module Axlsx
  class Worksheet
    def merge_last_row(options={})
       last_row = rows.last.index + 1
       first_col,last_col = options[:columns]
       if first_col && last_col
         merge_cells "#{first_col}#{last_row}:#{last_col}#{last_row}"
       else
         merge_cells rows.last
       end
       rows.last.style = style if options[:style]
    end
    def fill_columns(column_count,options={})
      row_data = options[:row_data] || []
      (column_count - row.count).times do 
        row_data << nil
      end
      add_row row_data
    end
  end
end

Call

sheet.merge_last_row columns:["A","E"]
sheet.add_row [I18n.t('foo.some_label').upcase]
sheet.merge_last_row columns:["B","E"], style:title

Axlsx merge cells based on row-column number like in Spreadsheet gem

Probably the easiest way is to pass in the actual cells (untested):

merge_cells sheet.rows.last.cells[(1..2)]
merge_cells sheet.rows.last.cells[(3..4)]

But you can also use the Axlsx::col_ref or Axlsx::cell_r method to convert row, column to the spreadsheet notation:

merge_cells Axlsx::cell_r(0,0) + ':' + Axlsx::cell_r(1,0)
merge_cells Axlsx::cell_r(2,0) + ':' + Axlsx::cell_r(3,0)

Edit

Regarding your issue of having to initialize strings, I am able to merge uninitialized cells, and specify merge before cells are specified. I am using Axlsx 2.0.1.

Applying several styles to a cell in Excel spreadsheet with AXLSX gem

I've managed to overlay cell styles by monkey patching Axlsx classes. The idea is to first apply raw styles to cell in the form of Ruby hashes. When that is finished one can generate Axlsx styles for the workbook and apply them. I can now separate the markup from style, having the styles applied as

sheet["B2:D2"].add_style(b: true)
sheet["B2:D5"].add_style(bg_color: "E2D3EB")
workbook.apply_styles

Below is the full listing of my hacky solution. This doesn't include identifying unique styles among other things that should be done in professional code. Looking forward to any feedback.

require 'axlsx'

class Array
  def add_style(style)
    return unless map{ |e| e.kind_of? Axlsx::Cell }.uniq.first
    each { |cell| cell.add_style(style) }
  end
end

class Axlsx::Workbook
  attr_accessor :styled_cells

  def add_styled_cell(cell)
    self.styled_cells ||= []
    self.styled_cells << cell
  end

  def apply_styles
    return unless styled_cells
    styled_cells.each do |cell|
      cell.style = styles.add_style(cell.raw_style)
    end
  end
end

class Axlsx::Cell
  attr_accessor :raw_style

  def workbook
    row.worksheet.workbook
  end

  def add_style(style)
    self.raw_style ||= {}
    self.raw_style = raw_style.merge(style)
    workbook.add_styled_cell(self)
  end
end

axlsx = Axlsx::Package.new
workbook = axlsx.workbook

workbook.add_worksheet do |sheet|
  sheet.add_row
  sheet.add_row ["", "Product", "Category", "Price"]
  sheet.add_row ["", "Butter", "Dairy", 4.99]
  sheet.add_row ["", "Bread", "Baked Goods", 3.45]
  sheet.add_row ["", "Broccoli", "Produce", 2.99]

  sheet["B2:D2"].add_style(b: true)
  sheet["B2:D5"].add_style(bg_color: "E2D3EB")
end

workbook.apply_styles

axlsx.serialize "grocery.xlsx"

Edit: I've leaned up my solution and extracted it into a gem https://github.com/sakovias/axlsx_styler

Ruby Axlsx Gem Unlocking style on whole column

To do this you can use Worksheet#col_style signature is col_style(index,style,options={})

index

The index of the row you want to set the style on (indexing starts at 0). Also index can be a range so if you want to unlock columns A though C then sheet.col_style((0..2),unlocked) will still work appropriately based on the way the code functions.

style

must be a predefined style as it is in the rest of the gem

options ={}

options allows you to set a row offset so if you didn't want to unlock the top n rows you could pass row_offset: n and all the cells in the column after n rows would be unlocked.
Example:

#unlock cells in column A starting at A3
sheet.col_style(0,unlocked,row_offset: 2)

Full Example

require 'axlsx'
p = Axlsx::Package.new
wb = p.workbook
unlocked = wb.styles.add_style(locked: false)
wb.add_worksheet do |sheet|
  5.times { sheet.add_row [1,2,3,4,5,6]}
  sheet.col_style(3,unlocked)
end
p.serialize('/simple_test.xlsx')

This will create a spreadsheet with 6 columns and the column with 4's (indexing starts at 0) will be unlocked while the rest of the columns remain locked (default).

Additional Info

The return value from this method will be a flat Array of the cells affected.

Note: the code does pretty much exactly what you are doing in your loop. It collects all the cells and then applies the style to each cell in a loop.

---

Related Topics