OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[Calc] Insert Row In Current Region And Fill Down Formulae

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Code Snippets
View previous topic :: View next topic  
Author Message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Aug 30, 2007 1:41 pm    Post subject: [Calc] Insert Row In Current Region And Fill Down Formulae Reply with quote

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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun Sep 02, 2007 10:20 am    Post subject: Reply with quote

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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Jan 09, 2008 1:37 am    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Code Snippets All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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