Several ways to time stamps

From Apache OpenOffice Wiki
Jump to: navigation, search


This script was published by Villeroy at the Code Snippets Forum.

Type =NOW() without entering the formula. Hit F9 to calculate the formula right on the input line. Then hit Enter twice (confirm and enter). Using a shortcut to NOW() (a tiny little bit shorter): Call menu:Insert>Names>Define...[Ctrl+F3] Name: n Refers to: NOW() Hit buttons [Add], [OK] Type into a cell: =n F9,Enter,Enter

NOW() returns the full date and time info as a formatted number. Having such a full stamp in a A1, =INT(A1) returns the day-portion, MOD(A1;1) returns the time-fraction only. You can use number formats to display only the date or the time without changing the value.

Original code

import uno
 
def getActiveCell(oView):
    '''Desparately missing in API. We extract from view data.'''
    sData = oView.getViewData()
    oSheet = oView.getActiveSheet()
    as1  = sData.split(";")
    lSheet = long(as1[1])
    sDum = as1[lSheet +3]
    delim = '/' in sDum and '/' or '+'
    as2 = sDum.split(delim)
    lCol = (as2[0])
    lRow = (as2[1])
    return oSheet.getCellByPosition(lCol,lRow)
 
def printStamp(oCell):
    '''Put the current time into the active cell.
    No formatting intended. Apply any date/time formatting you like.'''
    oCell.setFormula("=NOW()")
    oCell.setValue(oCell.getValue())
 
def _NowToColumnAByValidation(bOverwrite):
    iCol = 0
    doc = XSCRIPTCONTEXT.getDocument()
    c = getActiveCell(doc.getCurrentController())
    sh = c.getSpreadsheet()
    iRow = c.CellAddress.Row
    c = sh.getCellByPosition(iCol, iRow)
    if bOverwrite or not c.getFormula():
        printStamp(c)
 
def _NowToTimeStampCell(bOverwrite):
    thisComponent =  XSCRIPTCONTEXT.getDocument()
    oNames = thisComponent.NamedRanges
    oName = oNames.getByName("TimeStamp")
    oRg = oName.getReferredCells()
    oCell = oRg.getCellByPosition(0,0)
    if bOverwrite or not oCell.getFormula():
        printStamp(oCell)
 
def NowToEmptyColumnAByValidation(*a):
    '''Current time to empty column A, triggered by failing validation'''
    _NowToColumnAByValidation(False)
 
def NowToColumnAByValidation(*a):
    '''Current time to column A, triggered by failing validation'''
    _NowToColumnAByValidation(True)
 
def NowToTimeStampCell(*a):
    '''Put current time into a cell named "TimeStamp"'''
    _NowToTimeStampCell(True)
 
def NowToEmptyTimeStampCell(*a):
    '''Put current time into an empty cell named "TimeStamp"'''
    _NowToTimeStampCell(False)
 
def NowToActiveCell(*a):
    '''Put current time into the currently active input cell'''
    oDoc = XSCRIPTCONTEXT.getDocument()
    oView = oDoc.getCurrentController()
    oCell = getActiveCell(oView)
    printStamp(oCell)
 
def NowToRightNeigbour(*a):
    '''Put current time into the currently active input cell'''
    oDoc = XSCRIPTCONTEXT.getDocument()
    oView = oDoc.getCurrentController()
    oA = getActiveCell(oView)
    a = oA.getCellAddress()
    sh = oDoc.Sheets.getByIndex(a.Sheet)
    oCell = sh.getCellByPosition(a.Column +1, a.Row)
    printStamp(oCell)
 
 
g_exportedScripts = NowToActiveCell,NowToTimeStampCell,NowToEmptyTimeStampCell,NowToColumnAByValidation,NowToEmptyColumnAByValidation,NowToRightNeigbour,

Process of the script

Personal tools