3

According to example below: Value is stored only in A1, other cells return nil. How is possible to get the A1'a value from the others merged cells, or simply check range of the A1 cell?

example

Community
  • 1
  • 1
zfChaos
  • 415
  • 4
  • 13

2 Answers2

0

here is my take, if all merged fields are same as prev - then non-merged fields should become array

  xlsx = Roo::Excelx.new(__dir__ + "/output.xlsx", { expand_merged_ranges: true })

  parsed = xlsx.sheet(0).parse(headers: true).drop(1)

  parsed_merged = []
    .tap do |parsed_merged|
      parsed.each do |x|
        if parsed_merged.empty?
          parsed_merged << {
            "field_non_merged1" => x["field_non_merged1"],
            "field_merged1"     => [x["field_merged1"]],
            "field_merged2"     => [x["field_merged2"]],
            "field_merged3"     => [x["field_merged3"]],
            "field_merged4"     => [x["field_merged4"]],
            "field_non_merged2" => x["field_non_merged2"],
            "field_non_merged3" => x["field_non_merged3"],
          }
        else
          field_merged1_is_same_as_prev = x["field_non_merged1"] == parsed_merged.last["field_non_merged1"]
          field_merged2_is_same_as_prev = x["field_non_merged2"] == parsed_merged.last["field_non_merged2"]
          field_merged3_is_same_as_prev = x["field_non_merged3"] == parsed_merged.last["field_non_merged3"]

          merged_rows_are_all_same_as_prev = field_non_merged1_is_same_as_prev && field_merged2_is_same_as_prev && field_merged3_is_same_as_prev

          if merged_rows_are_all_same_as_prev
            parsed_merged.last["field_merged1"].push x["field_merged1"]
            parsed_merged.last["field_merged2"].push x["field_merged2"]
            parsed_merged.last["field_merged3"].push x["field_merged3"]
            parsed_merged.last["field_merged4"].push x["field_merged4"]
          else
            parsed_merged << {
              "field_non_merged1" => x["field_non_merged1"],
              "field_merged1"     => [x["field_merged1"]],
              "field_merged2"     => [x["field_merged2"]],
              "field_merged3"     => [x["field_merged3"]],
              "field_merged4"     => [x["field_merged4"]],
              "field_non_merged2" => x["field_non_merged2"],
              "field_non_merged3" => x["field_non_merged3"],
            }
          end
        end
      end
    end
    .map do |x|
      {
        "field_non_merged1" => x["field_non_merged1"],
        "field_merged1"     => x["field_merged1"].compact.uniq,
        "field_merged2"     => x["field_merged2"].compact.uniq,
        "field_merged3"     => x["field_merged3"].compact.uniq,
        "field_merged4"     => x["field_merged4"].compact.uniq,
        "field_non_merged2" => x["field_non_merged2"],
        "field_non_merged3" => x["field_non_merged3"],
      }
    end
srghma
  • 4,770
  • 2
  • 38
  • 54
-1

This is not possible without first assigning the value to all the cells of the range, even in Excel VBA this is the case.

See this sample

require 'axlsx'

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

wb.add_worksheet(:name => "Basic Worksheet") do |sheet|
  sheet.add_row ["Val", nil]
  sheet.add_row [nil, nil]
  merged = sheet.merge_cells('A1:B2')
  p sheet.rows[0].cells[0].value # "Val"
  p sheet.rows[0].cells[1].value # nil
  sheet[*merged].each{|cell|cell.value = sheet[*merged].first.value}
  p sheet.rows[0].cells[0].value # "Val"
  p sheet.rows[0].cells[1].value # "Val"
end

p.serialize('./simple.xlsx')

Please add a sample yourself next time so that we see which gem you used, which code, error etc.

peter
  • 41,770
  • 5
  • 64
  • 108
  • I think the question was referring to reading an XLSX file with roo, not writing to an xlsx with axlsx – Dan Corneanu Oct 11 '16 at 03:14
  • @DanCorneanu In my answer I make it clear that this is a general issue with most frameworks/programs and since the OP did not provide a code sample for Roo I couldn't adapt that, I could have answered this also without a sample – peter Oct 11 '16 at 07:44