| View previous topic :: View next topic |
| Author |
Message |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
 Posted: Thu Aug 30, 2007 1:41 pm Post subject: [Calc] Insert Row In Current Region And Fill Down Formulae |
 |
|
Just another quick solution for a common task, best used with keyboard shortcuts (e.g. Ctrl+Ins, Ctrl+Del).
Most recently http://www.oooforum.org/forum/viewtopic.phtml?t=61432
It performs the following actions on a single range selection:
Get the range, specified by height of current selection and width of current region around selection (current region is the range of adjacent non-empty cells).
Insert cells, shift down.
Drag down all formula cells from the row above.
Fails silently (does nothing without error-message):
- if there is no single range or cell selected.
- does not fill down if selection started at row #1 or if there are no formulae in the row above previous selection.
If there are no used cells around the selection, it simply inserts cells.
Insertion of cells below the bottom of a range will adjust dependent references automatically if the following option is set: Tools>Options>Calc>General>"Expand references when new columns/rows are inserted"
EDIT 2007-09-02: Added two lines to select the blank cells of the newly created records. Added a Python version.
| Code: |
REM ***** BASIC *****
Sub insertRowInCurrentRegionAndFillDownFormulae()
'calls: getCurrentRegion, getRangeByAddress
on error goto exitErr
oSel = thisComponent.getCurrentSelection()
REM error if oSel is not a single range
oAddr = oSel.getRangeAddress()
oSheet = oSel.getSpreadsheet()
REM intersect with current region's columns around selected cells and insert:
oCurrent = getCurrentRegion(oSel)
oCurrAddr = oCurrent.getRangeAddress()
oAddr.StartColumn = oCurrAddr.StartColumn
oAddr.EndColumn = oCurrAddr.EndColumn
oSheet.insertCells(oAddr, com.sun.star.sheet.CellInsertMode.DOWN)
REM error if oAddr.StartRow = 0
oTopRow = oSheet.getCellRangeByPosition(oAddr.StartColumn, oAddr.StartRow -1, oAddr.EndColumn, oAddr.StartRow -1)
oFmlRanges = oTopRow.queryContentCells(com.sun.star.sheet.CellFlags.FORMULA)
a() = oFmlRanges.getRangeAddresses()
for i = 0 to uBound(a())
aI = a(i)
aI.EndRow = oAddr.EndRow
oDrag = getRangeByAddress(thisComponent, aI)
oDrag.fillSeries(com.sun.star.sheet.FillDirection.TO_BOTTOM, com.sun.star.sheet.FillMode.SIMPLE,0,0,0)
next
REM select blank cells of inserted records:
oSel = getRangeByAddress(oSheet, oAddr)
doc.CurrentController.select(oSel.queryEmptyCells())
exitErr:
REM fail silently if no single range selected or if TopRow <0
End Sub
Function getCurrentRegion(oRange)
Dim oCursor
oCursor = oRange.getSpreadSheet.createCursorByRange(oRange)
oCursor.collapseToCurrentRegion
getCurrentRegion = oCursor
End Function
'pass a spreadsheet-document, or a sheet. the latter ignores the sheet-part of the address
'return empty if oAddr out of bounds or wrong obj
Function getRangeByAddress(obj, oAddr as com.sun.star.table.CellRangeAddress)
on error goto nullErr:
Dim oSheet
If obj.supportsService("com.sun.star.sheet.SpreadsheetDocument") then
REM use the sheet specified by given address
oSheet = obj.getSheets.getByIndex(oAddr.Sheet)
else
REM use given object (range/sheet) as parent range
oSheet = obj
endif
getRangeByAddress = oSheet.getCellRangeByPosition(oAddr.StartColumn,oAddr.StartRow,oAddr.EndColumn,oAddr.EndRow)
exit function
nullErr:
getRangeByAddress = Null
End Function
|
Same stuff as Python macro:
| Code: |
def getRangeByAddress(obj, a,):
'''Retrieve a range by range address within doc, sheet or parent range.
Return None on ivalid address (negative or out of bound indices)'''
oReturn = None
if obj.supportsService("com.sun.star.sheet.SpreadsheetDocument"):
# use the sheet specified by given address
oSheet = obj.Sheets.getByIndex(a.Sheet)
else:
# use address relatively to given parent object (range or sheet)
oSheet = obj
try:
oReturn = oSheet.getCellRangeByPosition(
a.StartColumn, a.StartRow, a.EndColumn, a.EndRow
)
except:
pass
return oReturn
def getCurrentRegion(oRange):
"""Get current region around given range."""
oCursor = oRange.getSpreadsheet().createCursorByRange(oRange)
oCursor.collapseToCurrentRegion()
return oCursor
def insertRowInCurrentRegionAndFillDownFormulae(*args):
'''Insert new row(s) into the current region of a list,
drag down formula cells and select blank cells of new row(s)'''
from com.sun.star.sheet.CellFlags import FORMULA
from com.sun.star.sheet.FillDirection import TO_BOTTOM
from com.sun.star.sheet.FillMode import SIMPLE
from com.sun.star.sheet.CellInsertMode import DOWN
doc = XSCRIPTCONTEXT.getDocument()
oSel = doc.getCurrentSelection()
try:
# error if oSel is not a single range
oAddr = oSel.getRangeAddress()
oSheet = oSel.getSpreadsheet()
# intersect with current region's columns around selected cells and insert:
oCurrent = getCurrentRegion(oSel)
oCurrAddr = oCurrent.getRangeAddress()
oAddr.StartColumn = oCurrAddr.StartColumn
oAddr.EndColumn = oCurrAddr.EndColumn
oSheet.insertCells(oAddr, DOWN)
# error if oAddr.StartRow = 0
oTopRow = oSheet.getCellRangeByPosition(oAddr.StartColumn,
oAddr.StartRow -1,
oAddr.EndColumn,
oAddr.StartRow -1)
oFmlRanges = oTopRow.queryContentCells(FORMULA)
a = oFmlRanges.getRangeAddresses()
for aI in a:
aI.EndRow = oAddr.EndRow
oDrag = getRangeByAddress(doc, aI)
oDrag.fillSeries(TO_BOTTOM, SIMPLE,0,0,0)
oSel = getRangeByAddress(oSheet, oAddr)
doc.CurrentController.select(oSel.queryEmptyCells())
except:
# fail silently
pass
g_exportedScripts = insertRowInCurrentRegionAndFillDownFormulae,
|
Python macros need to be saved with UNIX line feeds below <OOo_user_dir>/user/Scripts/python/SomeModuleName.py
You may append this to an already existing module and extend global variable g_exportedScripts.
Last edited by Villeroy on Wed Jan 09, 2008 1:52 am; edited 3 times in total |
|
| Back to top |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Sun Sep 02, 2007 10:20 am Post subject: |
|
|
Adding the obviously missing.
Basic:
| Code: |
Sub removeRowInCurrentRegion()
'calls: getCurrentRegion
on error goto exitErr
oSel = thisComponent.getCurrentSelection()
REM error if oSel is not a single range
oAddr = oSel.getRangeAddress()
oSheet = oSel.getSpreadsheet()
REM intersect with current region's columns around selected cells:
oCurrent = getCurrentRegion(oSel)
oCurrAddr = oCurrent.getRangeAddress()
oAddr.StartColumn = oCurrAddr.StartColumn
oAddr.EndColumn = oCurrAddr.EndColumn
oSheet.removeRange(oAddr, com.sun.star.sheet.CellDeleteMode.UP)
|
|
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Wed Jan 09, 2008 1:37 am Post subject: |
|
|
Removed the Python versions from the two initial postings.
Here is the slightly more advanced version in Python only. I do not use Basic macros any more.
This is what insertListRows does when called from a single range selection:
- Fail silently if no single range selected.
- Set Tools>Options>Calc>General>"Expand references when new columns/rows are inserted", so all references will be updated even if we insert cells below a reference.
- Expand the width of selection to the width of the supposed list by means of the current region of adjacent values. Selection's height determines the count of rows to be inserted.
- Insert cells with option "move down"
- Reset ExpandReferences to previous state.
- If we did not insert at the first row: Get formula ranges from the row above and drag down all the formulas into the newly created blank cells.
- Select the blank cells of insertion for edit.
This is what removeListRows does when called from a single range selection:
- Fail silently if no single range selected.
- Expand the width of selection to the width of the supposed list by means of the current region of adjacent values. Selection's height determines the count of rows to be removed.
- Remove cells with option "shift up"
- Select the address of the previously removed range
Install
Create a directory <ooo user profile>/user/Scripts/python/ if not already there. Copy the script content into a text editor and save with Unix line feeds in that directory.
You may choose <ooo install directory>/share/Scripts/python/ for all users of this installation.
Proposal for shortcuts: Tools>Customize, Tab:Keyboard
Ctrl+Ins, category:OOo Macros>user or share>filename, function:insertListRows
Ctrl+Del, category:OOo Macros>user or share>filename, function:insertListRows
| Code: |
import uno
from com.sun.star.sheet.FillDirection import TO_BOTTOM
from com.sun.star.sheet.CellFlags import FORMULA
from com.sun.star.sheet.FillMode import SIMPLE
from com.sun.star.sheet.CellInsertMode import DOWN
from com.sun.star.sheet.CellDeleteMode import UP
def getRangeByAddress(obj, a):
'''Retrieve a range by range address within doc, sheet or parent range.'''
if obj.supportsService("com.sun.star.sheet.SpreadsheetDocument"):
# use the sheet specified by given address
oSheet = obj.Sheets.getByIndex(a.Sheet)
else:
# use address relatively to given parent object (range or sheet)
oSheet = obj
return oSheet.getCellRangeByPosition(
a.StartColumn,
a.StartRow,
a.EndColumn,
a.EndRow
)
def getCurrentRegion(oRange):
"""Get current region around given range."""
oCursor = oRange.getSpreadsheet().createCursorByRange(oRange)
oCursor.collapseToCurrentRegion()
return oCursor
def getCurrentColumnsAddress(oRange):
"""Get address of intersection between range and current region's columns"""
oCurrent = getCurrentRegion(oRange)
oAddr = oRange.getRangeAddress()
oCurrAddr = oCurrent.getRangeAddress()
oAddr.StartColumn = oCurrAddr.StartColumn
oAddr.EndColumn = oCurrAddr.EndColumn
return oAddr
def _getListRowArgs():
'''Get current spreadsheet controller, sheet and address of current range selection'''
doc = XSCRIPTCONTEXT.getDocument()
oView = doc.getCurrentController()
oSel = oView.getSelection()
# Exception if oSel is not a single range on a sheet
oSheet = oSel.getSpreadsheet()
oAddr = getCurrentColumnsAddress(oSel)
return oView, oSheet, oAddr
def _insertListRows(oSheet, oAddr):
'''Let references expand automagically on insertion directly below the list'''
ctx = uno.getComponentContext()
smgr = ctx.ServiceManager
oGlobalSettings = smgr.createInstance('com.sun.star.sheet.GlobalSheetSettings')
bExpand = oGlobalSettings.ExpandReferences
oGlobalSettings.ExpandReferences = True
oSheet.insertCells(oAddr, DOWN)
oGlobalSettings.ExpandReferences = bExpand
def _dragDownFormulas(oSheet, oAddr):
'''drag down formula ranges from row above oAddr'''
# exception if oAddr.StartRow <= 0
oTopRow = oSheet.getCellRangeByPosition(
oAddr.StartColumn,
oAddr.StartRow -1,
oAddr.EndColumn,
oAddr.StartRow -1
)
oFmlRanges = oTopRow.queryContentCells(FORMULA)
a = oFmlRanges.getRangeAddresses()
for dragAddr in a:
oDrag = oSheet.getCellRangeByPosition(
dragAddr.StartColumn,
dragAddr.StartRow,
dragAddr.EndColumn,
oAddr.EndRow
)
oDrag.fillSeries(TO_BOTTOM, SIMPLE,0,0,0)
def insertListRows(*args):
'''Insert new row(s) into current region, drag down formula cells,
select blank cells of new row(s)'''
try:
oView, oSheet, oAddr = _getListRowArgs()
except:
# fail silently
return
_insertListRows(oSheet, oAddr)
if oAddr.StartRow > 0:
_dragDownFormulas(oSheet, oAddr)
# select remaining empty cells
oCurrent = getRangeByAddress(oSheet, oAddr)
# quirk: queryEmptyCells() returns an empty collection for an entirely blank range
oSel = oCurrent.queryEmptyCells()
if oSel.getCount() == 0: #if all or none are blank
oSel = oCurrent
oView.select(oSel)
def removeListRows(*args):
'''Remove selected rows within current region, select address of removed cells'''
try:
oView, oSheet, oAddr = _getListRowArgs()
except:
# fail silently
return
oSheet.removeRange(oAddr, UP)
oSel = getRangeByAddress(oSheet, oAddr)
oView.select(oSel)
g_exportedScripts = insertListRows, removeListRows
|
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
