Difference between revisions of "Python/WaysToTimeStamps"
Marcoagpinto (talk | contribs) ([Calc, Python] Several ways to time stamps) |
m |
||
Line 1: | Line 1: | ||
{{DISPLAYTITLE:Several ways to time stamps}} | {{DISPLAYTITLE:Several ways to time stamps}} | ||
− | This script was published by Villeroy at the [ | + | This script was published by Villeroy at the [https://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=12575 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). | 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): | Using a shortcut to NOW() (a tiny little bit shorter): | ||
− | Call menu | + | Call {{menu|Insert|Names|Define…}} ({{key|Ctrl|F3}}) |
Name: n | Name: n | ||
Refers to: NOW() | Refers to: NOW() | ||
− | Hit buttons | + | Hit {{buttons|Add}}, then {{button|OK}} |
Type into a cell: =n F9,Enter,Enter | Type into a cell: =n F9,Enter,Enter | ||
Line 14: | Line 14: | ||
== Original code == | == Original code == | ||
− | < | + | <syntaxhighlight lang="Python"> |
import uno | import uno | ||
Line 90: | Line 90: | ||
g_exportedScripts = NowToActiveCell,NowToTimeStampCell,NowToEmptyTimeStampCell,NowToColumnAByValidation,NowToEmptyColumnAByValidation,NowToRightNeigbour, | g_exportedScripts = NowToActiveCell,NowToTimeStampCell,NowToEmptyTimeStampCell,NowToColumnAByValidation,NowToEmptyColumnAByValidation,NowToRightNeigbour, | ||
− | </ | + | </syntaxhighlight> |
== Process of the script == | == Process of the script == | ||
[[Category:Python]] | [[Category:Python]] |
Revision as of 14:11, 15 May 2021
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 {{#if: Insert |Insert}}{{#if: Names | → Names}}{{#if: Define… | → Define…}}{{#if: | → {{{4}}}}}{{#if: | → {{{5}}}}}{{#if: | → {{{6}}}}}{{#if: | → {{{7}}}}}{{#if: | → {{{8}}}}}{{#if: | → {{{9}}}}}{{#if: | → {{{10}}}}} ( {{#switch:ctrl | shift = ⇧ Shift | up = ↑ | down = ↓ | > = → | < = ← | enter = ↵ Enter | cmd = ⌘ Cmd | command = ⌘ Cmd | opt = ⌥ Opt | option = ⌥ Opt | tab = Tab ⇆ | backspace = ← Backspace | << = ← Backspace | bs = ← Backspace | * = * | # = # | ; = ; | : = : | #default = Ctrl }} {{#if:F3| + {{#switch:f3 | shift = ⇧ Shift | up = ↑ | down = ↓ | > = → | < = ← | enter = ↵ Enter | cmd = ⌘ Cmd | command = ⌘ Cmd | opt = ⌥ Opt | option = ⌥ Opt | tab = Tab ⇆ | backspace = ← Backspace | << = ← Backspace | bs = ← Backspace | * = * | # = # | ; = ; | : = : | #default = F3 }} }}{{#if:| + {{#switch:{{{3}}} | shift = ⇧ Shift | up = ↑ | down = ↓ | > = → | < = ← | enter = ↵ Enter | cmd = ⌘ Cmd | command = ⌘ Cmd | opt = ⌥ Opt | option = ⌥ Opt | tab = Tab ⇆ | backspace = ← Backspace | << = ← Backspace | bs = ← Backspace | * = * | # = # | ; = ; | : = : | #default = {{{3}}} }} }}{{#if:| + {{#switch:{{{4}}} | shift = ⇧ Shift | up = ↑ | down = ↓ | > = → | < = ← | enter = ↵ Enter | cmd = ⌘ Cmd | command = ⌘ Cmd | opt = ⌥ Opt | option = ⌥ Opt | tab = Tab ⇆ | backspace = ← Backspace | << = ← Backspace | bs = ← Backspace | * = * | # = # | ; = ; | : = : | #default = {{{4}}} }} }}{{#if:| + {{#switch:{{{5}}} | shift = ⇧ Shift | up = ↑ | down = ↓ | > = → | < = ← | enter = ↵ Enter | cmd = ⌘ Cmd | command = ⌘ Cmd | opt = ⌥ Opt | option = ⌥ Opt | tab = Tab ⇆ | backspace = ← Backspace | << = ← Backspace | bs = ← Backspace | * = * | # = # | ; = ; | : = : | #default = {{{5}}} }} }}{{#if:| + {{#switch:{{{6}}} | shift = ⇧ Shift | up = ↑ | down = ↓ | > = → | < = ← | enter = ↵ Enter | cmd = ⌘ Cmd | command = ⌘ Cmd | opt = ⌥ Opt | option = ⌥ Opt | tab = Tab ⇆ | backspace = ← Backspace | << = ← Backspace | bs = ← Backspace | * = * | # = # | ; = ; | : = : | #default = {{{6}}} }} }}) Name: n Refers to: NOW() Hit Template:Buttons, then 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,