[Solved] Is this an OpenOffice / LibreOffice bug?

[dlochrin]

I wrote three macros known as "Add" "Delete" and "Sort" for some early version of OpenOffice and have been using them successfully for years. Given that most day-to-day spreadsheets have a header cell-range containing the column headers and an end row, with the data in the middle, I define a named range "Header" and a named cell "End" on the last row. Toolbar buttons then enable me to add & delete any number of rows beginning at the highlighted cell while preserving formats & formulae, or to sort the data, all with one popup enquiry box. It's extremely convenient.

When adding rows, the macro previously added the rows so the originally selected row was moved down. The contents were then copied from the selected row (now in its new position further down the spreadsheet) into the added rows, and the values deleted leaving formats & formulae. That worked fine until recently because, it turns out, named ranges of data were always defined globally.

However there's a problem if rows are added within a named range of data but only if the scope of the named range is confined to the sheet. Suppose we have a named range Fred = $A$2:$A$100 and we add ten rows somewhere in the middle of that range. Fred should be incremented to $A$2:$A$110 but instead it's decremented to $A$2:$A$90. If the scope is global everything is fine.

The problem lies in the code which copies the selected row to the added rows - oActSheet.copyRange (CellAddress, CellRangeAddress). The selected row must be copied forward to higher-numbered rows rather than backwards to lower-numbered rows. Is this a bug? Code with & without the bug is shown below.

Regards,
David L.

The macro code with the problem is as follows, where 'n' is the number of rows to be added.

Code: Select all

         CellRangeAddress.Sheet = SheetNbr
         CellRangeAddress.StartColumn = 0
         CellRangeAddress.EndColumn = EndColumn
         CellRangeAddress.StartRow = RefRow
         CellRangeAddress.EndRow = RefRow+n-1
         oActSheet.insertCells (CellRangeAddress, com.sun.star.sheet.CellInsertMode.ROWS)
'
'        Copy the the row originally selected from column-0 to 'EndColumn' to each of the inserted rows,
'        then delete all values, date-time values, and strings.
'
         CellRangeAddress.StartRow = RefRow+n
         CellRangeAddress.EndRow = RefRow+n
         CellAddress.Sheet = SheetNbr
         CellAddress.Column = 0
         For i = RefRow To RefRow+n-1
            CellAddress.Row = i
            oActSheet.copyRange (CellAddress, CellRangeAddress)
         Next i
         Flags = com.sun.star.sheet.CellFlags.VALUE + _
                 com.sun.star.sheet.CellFlags.DATETIME + _
                 com.sun.star.sheet.CellFlags.STRING + _
                 com.sun.star.sheet.CellFlags.ANNOTATION
         oActSheet.getCellRangeByPosition(0,RefRow,EndColumn,RefRow+n-1).clearContents(Flags)

This code works whether a named range is defined globally or confined to the sheet.

Code: Select all

oCellRangeAddress.Sheet = SheetNbr
oCellRangeAddress.StartColumn = 0
oCellRangeAddress.EndColumn = EndColumn
oCellRangeAddress.StartRow = RefRow+1
oCellRangeAddress.EndRow = RefRow+n
oActSheet.insertCells (oCellRangeAddress, com.sun.star.sheet.CellInsertMode.ROWS)
'
oCellRangeAddress.StartRow = RefRow
oCellRangeAddress.EndRow = RefRow
oCellAddress.Sheet = SheetNbr
oCellAddress.Column = 0
For i = RefRow+1 To RefRow+n
   oCellAddress.Row = i
   oActSheet.copyRange (oCellAddress, oCellRangeAddress)
   Next i
'
Flags = com.sun.star.sheet.CellFlags.VALUE + _
        com.sun.star.sheet.CellFlags.DATETIME + _
        com.sun.star.sheet.CellFlags.STRING + _
        com.sun.star.sheet.CellFlags.ANNOTATION
oActSheet.getCellRangeByPosition (0,RefRow,EndColumn,RefRow+n-1).clearContents(Flags)

[Villeroy]

This might be a LibreOffice bug. OpenOffice still has global names only.
I would always prefer database ranges over the named ranges because this is what database ranges are made for: database-like lists on a spreadsheet. They are always global names.

[dlochrin]

Thanks, Villeroy, I've lodged a bug report in the LibreOffice Bugzilla at https://bugs.documentfoundation.org/sho ... i?id=91744 and I'll mark this forum post as solved.

I would always prefer database ranges over the named ranges because this is what database ranges are made for: database-like lists on a spreadsheet. They are always global names.

Hmmm.... I'm inclined to favour horses for courses! I'd argue that a database and a spreadsheet are essentially quite different tools, and it's best to recognise the differences and design them accordingly despite their functional overlap for some small-scale applications.

Named ranges are a case in point.

A spreadsheet commonly has several sheets where each one deals with an independent, but related, part of some overall job. For example, an accounting spreadsheet might have one sheet for each bank account, mortgage, etc., and one containing an overall summary. If named ranges can have either global or "sheet" scope then such a situation is easier to handle.

The macro which is the subject of this topic was designed to cater for such situations. Each sheet has a header and an end row, but they're usually different. As originally written for OpenOffice, the globally defined values for the header cellRange and a cell in the last row had to be specified in the form "SheetName_Header" and "SheetName_End" and then parsed to extract the three variables "SheetName", "Header" and "End". In my view, that's less elegant.

I think a basic issue here is that data storage and data presentation are quite separate in a database application, but integrated in a spreadsheet.

Regards,
David L.

[Villeroy]

Database ranges are part of the ODF standard and therefore an integral part of Calc (menu items Data>Define, Data>Select, Data>Refresh). Database ranges are just like named ranges but unlike names they always refer absolutely to one rectangle of cells which is assumed to be organized as fields and records. They may be linked to row sets of real databases. They store currently applied filter settings and sort orders. Multiple database ranges can have independent filters and sort orders, even when they reside on the same sheet.
The vast majority of spreadsheet users use spreadsheets as poor man's database anyway. Named ranges that refer to database-like lists should be database ranges.
http://www.openoffice.org/api/docs/comm ... Range.html
Unfortunately, LibreOffice developers ignore database ranges entirely.

[dlochrin]

Database ranges are part of the ODF standard and therefore an integral part of Calc (menu items Data>Define, Data>Select, Data>Refresh).

Thanks, that's interesting. I wasn't aware of it, and will have to read further to understand just what can be done and how far Calc will support an SQL-type external database.

The vast majority of spreadsheet users use spreadsheets as poor man's database anyway. Named ranges that refer to database-like lists should be database ranges.

Years ago (before the Internet) somebody opined that the spreadsheet was the only completely new tool to emerge from the development of computers, and no doubt it's still evolving. But while it's true that spreadsheets are often used as a poor-man's database they are not the same thing, and I think there's a risk that trying to bridge the gap will eventually result in falling into it. Perhaps that part of the architecture should be more clearly defined.

So I can see an argument that spreadsheet ranges and "database" ranges should be functionally separated, with two quite different definitions. There does need to be good database support, but I'd suggest there be more emphasis on the interface together with an external (but associated) lightweight database package. That would provide a really useful migration path, especially with a good UML database modelling and code-generating package which could be used by programmers who are not database specialists.

Regards,
David L.

[Villeroy]

So I can see an argument that spreadsheet ranges and "database" ranges should be functionally separated, with two quite different definitions. There does need to be good database support, but I'd suggest there be more emphasis on the interface together with an external (but associated) lightweight database package.

That's what the Base component is about. Base and Calc interface in various ways while Base can connect with anything you have a JDBC or ODBC driver for. Hardly anybody uses Base because databases are too techie stuff. People love to get quick results even if they tend to be wrong. Others notice that Base is really, really lightweight (simple, basic, not very powerful) and reject it right from the start.

Calc's database ranges can be linked to record sets from arbitrary databases and they can be used as "database" tables when you connect a Base document to a spreadsheet which somewhat handles the sheet data as if they where stored in a regular database. When using a database range as a pseudo table in Base, you can hide away all the other unstructured stuff that may be on the same sheet by hiding away the "table" that represents the used area of the entire sheet. This way you can even define simple SQL queries on single sheet areas (no cross-table relations).
When not connected to any Base document, the database ranges are just a more simple way to name a database-like list (records in rows, fields in columns with one label row on top). I have one flat spreadsheet list where I use to manually edit a filtered database range and then call menu:Data>Refresh from time to time until there is no visible row left which means that my work is done. Data>Refresh updates the currently set filtering and sorting and database data if the range is linked to some database.

[dlochrin]

I have used Base to browse databases which I've created externally (using a UML modelling package known as ArgoUML), but I have to say I've never explored the integrated functionality of the LibreOffice suite. It has some interesting potential...

Regards,
David L