weshatheleopard
insert_row doesn't move merged cells
While all the styles and content move down after insert_row(0), all the merged cells continue to stay on their places. It makes impossible to insert data into a prepared file.
Before:
After:
Well, I made a rough experiment and it looks like the behavior can be easily patched.
3.times { worksheet.insert_row(15) }
worksheet.merged_cells.each do |cell|
next unless cell.ref.row_range.min >= 15
cell.ref.instance_variable_set(:"@row_range", Range.new(cell.ref.row_range.min + 3, cell.ref.row_range.max + 3))
end
The code above turned this:
Into this:
That's what I've been expecting from the gem to do from the beginning. We can make a special RubyXL::MergedCells interface for shifting rows starting from some index and call it within insert_row. What we need to take in count is the fact that when we insert a row, there can be a multirow MergedCell that starts before the insert and ends after it. If happens so I suggest to increase only the max end of the range, leaving the min on its place.
Also I think we should copy merge info from the shifted row. That's exactly what Mac Numbers app does (can't check it in real Excel).
And in the end the behavior needs to be mirrored within insert_column.
Found extra bug related to insert:
worksheet.insert_row(row_index)
# Here is everything ok:
worksheet[row_index][0].change_contents("some string")
# Here I get validate_worksheet exception:
worksheet[row_index][2].change_contents("some_other_string")
Can't understand what is happening here exactly. Maybe sheet_data index does not get proper updates somewhere during insert_row?
To make my production code work (I need it just for one file), I removed the validation. Everything works 100% ok.
There are many issues stemming from the fact that Excel emulation is far from perfect. So far, the gem is best used for reading from and minor modifications of XLSX files. References throughout the file are not recalculated upon row/column insertion; in particular, formulas certainly do get broken. Proper handling of that stuff requires quite a bit of planning...
@weshatheleopard well, let's think what we can do to update references?
+1 with the issue related to insert and the validate_worksheet exception.
I'm using @killthekitten's fork which addresses my issue. thanks!
This is like plugging a single hole in a colander.
For starters, we need to complete handling the Reference class. Which is a huge task — see:
- https://msdn.microsoft.com/en-us/library/ff533995%28v=office.12%29.aspx
- http://ewbi.blogs.com/develops/2004/12/excel_formula_p.html
- https://office.microsoft.com/en-us/excel-help/overview-of-formulas-HP010081865.aspx#BMusing_references_in_formulas
@weshatheleopard totally fair, thanks for your work here, it's much appreciated.
I certainly don't need an incredibly robust library, and while I can appreciate your dedication to it, I just need to export some data into an xlsx template, which involves adding some rows, but nothing with formulas.
Is that issue pertaining to the Reference class also why the add_hyperlink convenience method is commented out? I forked and played with a bit and can't get it to work (Excel throws an invalid reference upon clicking into the cell).
You see, that's the problem: nobody "needs an incredibly robust library", but everybody needs some particular hole plugged. If I do hole-plugging, then it will eventually become nothing but a loosely holding together bunch of plugs. My personal policy is to fix the root causes, not the particular manifestations.
Yes, that's the reason. You see, References may be:
- Absolute or relative (currently supported);
- Referencing a single cell (currently supported);
- Referencing a range of cells (currently supported);
- Referencing cells on a different worksheet(s) within the same workbook file;
- Referencing cells in a different workbook file;
- Have a relative (R1C1) format
So I need to be able to support everything, even such complex things as [C:\My Documents\Budget.xlsx]'John''s Expenses'!A1:$D$99
Similar problem with hyperlinks. They can refer to files on the Web, but also to files on the local system...
Yeah, I understand, it's just one of those thankless parts of being a maintainer. Again, I appreciate all of your work were, and I think thats a fine policy. Thanks again!
So for example, the few things breaking when you delete row (or column) are:
- Merged cells
- Selections
- Named ranges
- Conditional formatting
- Formulae
- (and other stuff that I don't remember off the top of my head)
If I properly implement References, that will automatically fix all these breakages. But doing so is not an easy task, given how much stuff Reference needs to support (see comment above). And I have a primary job which doesn't allow me to just sit down and concentrate on it.
Is there any workout for copying and pasting the specific row maintaining its formats and formulas in specific position using RubyXL gem? Your help will be highly appreciated.
