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

Question

I have been using the Spreadsheet gem to write xls files. With the Spreadsheet gem, merging the cells can be done dynamically based on the row and column number like this

  merge_cells(start_row, start_col, end_row, end_col)

My Code snippet

  excel_file = Spreadsheet::Workbook.new     
  sheet1 = excel_file.create_worksheet :name => 'Example'
  .
  .#code blocks
  .
  .
  start_col = 4
  end_col = 0

  wk_array_size.each_with_index do |v,i|
    end_col = start_col+((v.to_i*2)-1)
    sheet1.merge_cells(0, start_col, 0, end_col)
    start_col = (end_col+3)
  end
  .
  .#code blocks
  .
  sheet1.insert_row(0,week_names)

Where week_array_size is an array which holds the size of an hash based on which the cells are merged.

  [11, 10, 3]

The merging would be done dynamically as iterated over the array week_array_size

  start_col = 4
  end_col = (4+(11*2)-1) = 25

  (0, 4, 0, 25)
  sheet1.merge_cells(0, 4, 0, 25)
  .
  .
  (0, 28, 0, 47)
  sheet1.merge_cells(0, 28, 0, 47)
  .
  .
  (0, 50, 0, 55)
  sheet1.merge_cells(0, 50, 0, 55)

Once the cells have been merged, the data is written to the merged cells

  sheet1.insert_row(0,week_names)
  sheet1.row(0).height = 30

But the same approach can't seem to be applied with the AXLSX gem for xlsx files, since it uses the alpha-numeric row-column names like this "A1:A2". And data has to be written with empty string values before merging like the below example

  p = Axlsx::Package.new


  p.workbook.add_worksheet(:name => 'Example') do |sheet|
  sheet.add_row ["Class Name", "", "", "Time"]
  merge_cells "A1:A2"
  merge_cells "A3:A4"
  end

Is there a way to merge cells in Axlsx based on row and column numbers like how it is done in Spreadsheet gem? Also can the data be written after merging the cells?

Yellowweed

Answer 1

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.

Martingale

The first approach worked right away and I had to construct an array of initial and final column cell values like this sheet.merge_cells sheet.rows[0].cells[(4..(v-1))] and sheet.merge_cells sheet.rows[0].cells[(((tmp_arry4[i-1] + 3)-1)..(v-1))] . Will try the second approach too. – Yellowweed

Regarding initialize strings, I am unable to merge empty cells unless I fill it with an empty string value. I had to build an array whose rows constitute the rows of an axlsx sheet and these rows have empty string values. – Yellowweed