https://bugs.documentfoundation.org/show_bug.cgi?id=91744
Bug ID: 91744
Summary: Failure adding rows with named range scope restricted
to the sheet
Product: LibreOffice
Version: unspecified
Hardware: x86 (IA32)
OS: Linux (All)
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: BASIC
Assignee: [email protected]
Reporter: [email protected]
This may be another manifestation of Bug ID 77459.
A BASIC macro is adding (n) rows to a spreadsheet beginning at the row
indicated by a selected cell (RefRow). The contents of the selected row are
copied to the 'n' new rows then the values deleted, leaving formats & formulae.
As previously written for OpenOffice V2, the code 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 a problem arises if rows are added within a named range of data AND 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 -
around "oActSheet.copyRange (CellAddress, CellRangeAddress)". The selected row
must be copied forward to higher-numbered rows rather than backwards to
lower-numbered rows. Code with & without the bug is shown below.
THIS DEMONSTRATES THE PROBLEM:
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)
BUT THIS WORKS FINE
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)
Thanks & Regards,
David Lochrin
[email protected]
--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Libreoffice-bugs mailing list
[email protected]
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
