insert_row: bad value for cell#column and cell#worksheet

[lionelperrin]

After inserting a new row in a worksheet, it appears that the newly created cells are missing some attributes:

the following code can be used as a workaround:

r = sheet.insert_row( pos )
r.size.times do |i| # workaround rubyxl
  c = r[i]
  c.worksheet = sheet
  c.column = i
end

[weshatheleopard]

Please provide the exact use case you have problem with. Some attributes are not intended to be filled during the normal operation and should not be relied upon. For example, Cell#column is provided for reference only, if it is present in the original file, and is rewritten upon save anyway (see https://github.com/weshatheleopard/rubyXL/blob/master/lib/rubyXL/objects/worksheet.rb#L699). The only reliable way to obtain a cell column number is to determine its position in the Row array.

[lionelperrin]

something like

p = RubyXL::Parser.parse('mydoc.xlsx') # parse a document with a first row
sheet = p[0]
sheet.insert_row( 1 )
sheet[1][0].is_underlined # or any access to the style of the cell

produces the following exception: RuntimeError: Cell #<RubyXL::Cell:0x0000000425e370> is not in worksheet

[FreeNewMan]

I am styling xlsx file by MS Excel 2007. RubyXL was upgraded to 3.3.7 version. Wrote this code, but railse error like this ((: Cell #<RubyXL::Cell:0xccaa420> is not in worksheet #<RubyXL::Worksheet:0xc346064> Code:

  def cell_write(obj, scell, svalue, haligm = nil, valign = nil)
    nROW = RubyXL::Reference.ref2ind(scell.to_s)[0]
    nCOL = RubyXL::Reference.ref2ind(scell.to_s)[1]
    #obj.add_cell(nROW, nCOL, svalue)
    obj[nROW][nCOL].change_contents(svalue, formula = nil)
    obj.sheet_data[nROW][nCOL].change_horizontal_alignment(haligm) unless haligm == nil
    obj.sheet_data[nROW][nCOL].change_vertical_alignment(valign) unless valign == nil
    #obj[nROW][nCOL].change_font_bold(bolded = true)
  end

def pricelistreport_rpt
   @pricelistspecs = Pricelistspec.where(:pricelist_id => @pricelist.id )
   book = RubyXL::Parser.parse("#{Rails.root}/reports/price_report.xlsx")
   @pricelistspecs.each do |spec|
     fst = 5  # first row
     dlt = 0  # counter for adding rows
     worksheet.insert_row(tmc)
     cell_write(worksheet, ['A',tmc].join('') , dlt+1, 'right')
     cell_write(worksheet, ['B',tmc].join('') , dlt+1, 'right')
     cell_write(worksheet, ['B',tmc].join('') , spec.servtype.name, 'left')
     cell_write(worksheet, ['C',tmc].join('') , spec.sumprice, 'right')
     dlt = dlt + 1
   end
end

[weshatheleopard]

@LutovPavel: your contribution is actually helping a lot, since the code you are touching comes all the way from the very very initial version of the gem (i.e. very untested), and hasn't been used much. I shall take a look at the exact reasons for this crash tomorrow.

[nilesh]

I have a related error -- I have a blank but formatted row of cells. I then insert a row below this row. RubyXL copies the formatting from the previous row and things look fine till this point. Now in the newly inserted row, I cannot use the #change_contents method for any cell in this row (probably because the cell is new). I get the same error:

Cell #<RubyXL::Cell:0xccaa420> is not in worksheet #<RubyXL::Worksheet:0xc346064>

However, when I use #add_cell method to write contents of that same cell in the newly created row, it loses all formatting that was copied from the previous row.

Has this issue already been fixed?

[weshatheleopard]

@nilesh: can you provide the minimal code sample? I'm not able to reproduce, it doesn't crash.

s = RubyXL::Parser.parse("test.xlsx")
s[0].insert_row(1)  # Inserting a row below the very first row on the first sheet
s[0][1][0].change_contents "changed_value"  # Changing contents of a cell in the freshly inserted row
s[0][1][0]
=> #<RubyXL::Cell(1,0): "changed_value", datatype="str", style_index=5>

[nilesh]

@weshatheleopard, I tried with a clean XLSX file, added some formatting and used your code. It worked fine! However, I have a heavily formatted sheet, for which the same code does not work. Let me attach it here for you to check out. export_template.xlsx

[belovamarina]

@nilesh, I have the same problem. But if I save the file (write 'file.xlsx') after insert_row, and only after that change cell by change_contents in new row, this error disappears.

[weshatheleopard]

@belovamarina , @nilesh : Can someone give me EXACT steps to reproduce already? I tried the attached file, everything works just fine.

[belovamarina]

I'm using @nilesh 's file as example.

require 'rubyXL'

workbook = RubyXL::Parser.parse("export_template.xlsx")
worksheet = workbook.worksheets[0]

worksheet.each do |row|
   row && row.cells.each do |cell|
    if cell && cell.value == 'Field 1'
      puts "Find Cell (Row: #{cell.row} Col: #{cell.column})"

      puts "Insert row #{cell.row + 1}..."
      worksheet.insert_row(cell.row + 1)

      puts "Change content of cells.."
      worksheet[cell.row + 1][cell.column].change_contents('Changed content')

    end
   end
end

workbook.write("export_template.xlsx")

Error:

.rvm/gems/ruby-2.1.7/gems/rubyXL-3.3.16/lib/rubyXL/cell.rb:38:in `validate_worksheet': Cell #<RubyXL::Cell:0x00000001d92b18> is not in worksheet #<RubyXL::Worksheet:0x000000023725b0> (RuntimeError)

@weshatheleopard , thank you!

[tasdendu]

Is there any workout for copying and pasting the specific row in specific position using RubyXL gem? Your help will be highly appreciated.

[Saganesque]

I have the same (or similar) question. I'm trying to find the correct analog for hand copying a tr class row from a web page and pasting it into a spreadsheet already parsed by RubyXL. If I were doing this by hand, the values would paste at intervals and replace anything that might happen to be there.

[msa7]

Hi.

I have same issue after insert_column(3).

Whole example:

      sheet.insert_column(3)
      sheet[3][3].change_contents('session')

it produce

Cell #<RubyXL::Cell:0x0000000114293230> is not in worksheet.

Any workaround?

[dmolesUC]

@msa7 I ran into this as well, see https://github.com/weshatheleopard/rubyXL/issues/441