inserting rows and columns at specific positions

[missuse]

Hi, and thanks for a great package!

Is there a way to insert rows and columns at a specific position on a worksheet? Possibly by copying the style and filling formulae.

[JanMarvin]

Hi @missuse, there are plenty of options documented for instance here:

https://janmarvin.github.io/ox2-book/chapters/openxlsx2_style_manual.html#the-quick-way-using-high-level-functions-1

Or here:

https://janmarvin.github.io/ox2-book/chapters/openxlsx2_style_manual.html#copy-cell-styles

As with many things in spreadsheets it depends on your specific question. But please be aware that we do not feature inserting columns or rows into spreadsheets. But you can insert data frames into specific columns and rows just fine.

[missuse]

Hi @JanMarvin ,

And thanks for prompt reply,

I was referring to an analogue of the excel “insert” functionality where a row of all blank cells would be inserted for example above the specified row, which would keep the style, the data validation rules and the formulas of the cells in the row bellow.

Per your answer it appears the only way this can be achieved using openxlsx2 is to copy the data (wb_copy_cells) and the styles (wb_set_cell_style) to the range of cells one row bellow the current, and then write blank values to the first originating row.

Could you confirm there is no way to programmatically extract the data validation rules using openxlsx2 for a cell range so they can be reapplied to another cell range?

[JanMarvin]

Hi @missuse , yes indeed. I replied here some time ago as to why I don't implement a function to insert/delete columns or rows into a spreadsheet: https://stackoverflow.com/a/75666670/12340029

Unfortunately, my reasoning has not changed since then. There are several things we would need to check and update, among the long list are:

• Formulas
• Named ranges
• References to cells in tables, charts, pivot tables
• Merged cells

Since we do not currently check these things, the output would always differ from the expectation. So my suggestion would always be: find another solution that doesn't require inserting rows or columns in a new format. I'm not saying it's impossible, in the example above you can see how it's only possible for raw data, but that's the maximum of my interest in supporting this feature.

[missuse]

Hi @JanMarvin, thank you for the explanation and especially for your efforts in creating and maintaining the package.