When you drag a database query or table into a spreadsheet, Calc generates a database range "Import1" with both options "Keep formatting" and "Insert/Remove Cells" unchecked. "Keep formatting" takes care that your spreadsheet formatting survives the next refresh. "Insert/Remove Cells" prevents that adjacent data are overwritten on refresh when the database row set has increased. And it prevents that adjacent data are left in empty sheet space when the row set has decreased. You always want these two options being checked. I can not imagine any scenario where it would make sense to uncheck any of them. These options are checked by default when you create a database range manually. How does this make any sense with an unlinked (not refreshable) db range? Answer: When you copy the result of a sort or filter operation to another sheet, option "Insert/Remove Cells" makes a difference. Again, you never want to turn this off. I can't find any effect of "Keep Formatting" with unlinked db ranges. Please, check these options for linked import ranges per default. IMHO, they should not exist anyway.
How to reproduce: Drag the biblio table from the data source window into a Calc sheet. Call Data>Define... "Options". Only the header option is set because database data always import with column headers. Select a cell or range, call Data>Define... enter a name, click [Add] and look at the options. Options "Insert/Remove" and "Keep Formatting" are set.
It has always worked like that, inherited from OOo. I agree, but I'm not sure if there are no scenarios for these unselected options. Could the change be a break for users? In any case, it seems more a request for ‘enhancement’ than a ‘bug’.
Many years ago, I filed an enhancement request to set these options by default and someone implemented the new defaults. However, the defaults do not apply to linked import ranges, where they would be even more useful than with manually created ranges.
See also: https://bz.apache.org/ooo/show_bug.cgi?id=123725 This incomplete enhancement inherits from AOO.
Created attachment 199889 [details] Screenshot of the 'Define Database Range' dialog On defining a range items 3 and 4 (insert/delete cells, keep formatting) are enabled. Seems to make sense to have the same settings when importing a database table.
Might be tricky to find but with a code pointer easy to solve.
(In reply to Heiko Tietze from comment #6) > Might be tricky to find but with a code pointer easy to solve. Well, the only way to trigger the creation of a linked database range from the UI is a drag&drop operation of a table/query icon on the data source window's left pane to a spreadsheet cell. Is this possibly Dispatch command Resource ID symbol Resource ID value .uno:SbaImport SID_SBA_IMPORT 26349
"Keep formatting" belongs to member bKeepFmt, "Insert or Delete Cells" belongs to member bDoSize of class ScDBData. They are set in its ctor in #71 and #72 in https://opengrok.libreoffice.org/xref/core/sc/source/core/tool/dbdata.cxx It's easy to change the initial values from `false` to `true` and the dialog will have this two options selected. But I do not know whether that has any bad consequences.
Let me put it this way: In no practical use case, there is any reason to not check these options. IMHO, these 2 options should not even exist. When "Keep formatting" is off, every refresh of the database range removes the spreadsheet formatting. Adding some formatting to linked database data (including csv) is one reason why anybody drags a record set into Calc. When "Insert/Remove" is off, every refresh of the database range will not resize any references to the database range, possibly overwriting adjacent data and formulas. The automatic adjustment of import ranges makes this way of linking superior to all the other ways of linking. With this option turned on, your spreadsheet remains fully intact when the size of your refreshed record set has changed. No "totals row" will be overwritten, making that option obsolete as well.