MS Excel: cannot copy/paste into new sheet

[jungle-boogie]

Hello,

This is a continuation of https://github.com/saulpw/visidata/issues/1348

I can now copy/paste within the same sheet, but if I create a new sheet and attempt to paste into it, I get the io.bufferedReader error to occur.

1. Open MS Excel file
2. Press y to copy
3. Shift + A for new sheet
4. p to paste

Perhaps I'm pasting wrong, but the error is generated.

Looks like I'm running commit 88d868cae488180606ea86198efbecc964dac3f2

[anjakefala]

Ah! The fix for 4f9a1d5e74f6801e3f17bd8c53c734cef4a24150 is only working because it implemented a shallow copy for rows pasted onto XlsxSheets. You found the loophole, which is pasting those rows on non-XlsxSheets. Those do the standard deepcopy.

[jungle-boogie]

Does this mean it's more of a complex fix for this issue, or that it's unsolvable?

[anjakefala]

Unsure, but at the moment I am not sure how I would address it!

[saulpw]

I'm thinking we should make a distinction between "Standard Sheet Types" and "Special Sheet Types". Standard sheets would all have a rowdef of dict and standard (source/editable) columns would all be ItemColumn, so standard rows could be pasted between Standard sheets with no problem.

Special Sheet Types are everything else: all derived sheets, meta sheets, and many of the more exotic and/or high-performance loaders (including pandas). At first most loaders would be Special, but we could convert many loaders into Standard Sheets pretty easily.

Then, a Special Sheet can override its addRow() method to check for standard rows and convert them into its own format. And you as a user can convert a Special Sheet to a Standard Sheet with the existing g' (freeze-sheet).

This would clarify several things and improve sheet interoperability. Using Standard Sheets will probably take more time and memory, but it would solve this entire class of bugs.

[geekscrapy]

@saulpw given these thoughts, might it be a good idea to be able to convert between these two sheet types as well?A Special Sheet could have a method which converts it's rows to dicts and casts onto a Standard Sheet. It would also expose a method which would convert dicts to its own format

[saulpw]

Yup! As mentioned in the above comment, g' would convert from Special to Standard, and pasting from Standard to a Special Sheet would go through addRow, converting to its own format.

[saulpw]

Okay, so I implemented a more universal paste mechanism, which creates new rows on the target sheet and then fills them with values from the copied rows from the previous sheet. This value-filling happens positionally, so if columns are missing or in a different order, the values will be in different columns, which is different from how it would work previously. From a user perspective, this means you can no longer copy/paste rows onto the same sheet unless the column and their ordering haven't changed (this is more like Excel).

This is somewhat disappointing from a VisiData-architecture standpoint, but I think less surprising and more useful in the general case. I did special-case cut/paste of Columns on the ColumnsSheet to work as previously, since 2 of our tests rely on this feature. If we need paste with better column-orientation we can offer an option to paste based on column name, and/or rework the architecture as I mentioned above, to differentiate between Standard Sheets and Special Sheets.