Difference between revisions of "Python/WaysToTimeStamps"
m |
m |
||
Line 3: | Line 3: | ||
This script was published by Villeroy at the [https://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=12575 Code Snippets Forum]. | 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. <br> |
− | Using a shortcut to NOW() (a tiny little bit shorter): | + | Hit {{key|F9}} to calculate the formula right on the input line. <br> |
− | Call {{menu|Insert|Names|Define…}} ({{key|Ctrl|F3}}) | + | Then hit {{key|Enter}} twice (confirm and enter). |
− | Name: n | + | |
− | Refers to: NOW() | + | Using a shortcut to NOW() (a tiny little bit shorter):<br> |
− | Hit {{button|Add}}, then {{button|OK}} | + | Call {{menu|Insert|Names|Define…}} ({{key|Ctrl|F3}}) <br> |
+ | Name: n <br> | ||
+ | Refers to: NOW() <br> | ||
+ | Hit {{button|Add}}, then {{button|OK}}<br> | ||
Type into a cell: =n F9,Enter,Enter | Type into a cell: =n F9,Enter,Enter | ||
Latest revision as of 14:15, 15 May 2021
This script was published by Villeroy at the Code Snippets Forum.
Type =NOW() without entering the formula.
Hit {{#switch:f9
| shift = ⇧ Shift
| up = ↑
| down = ↓
| > = →
| < = ←
| enter = ↵ Enter
| cmd = ⌘ Cmd
| command = ⌘ Cmd
| opt = ⌥ Opt
| option = ⌥ Opt
| tab = Tab ⇆
| backspace = ← Backspace
| << = ← Backspace
| bs = ← Backspace
| * = *
| # = #
| ; = ;
| : = :
| #default = F9
}} {{#if:| + {{#switch:{{{2}}}
| shift = ⇧ Shift
| up = ↑
| down = ↓
| > = →
| < = ←
| enter = ↵ Enter
| cmd = ⌘ Cmd
| command = ⌘ Cmd
| opt = ⌥ Opt
| option = ⌥ Opt
| tab = Tab ⇆
| backspace = ← Backspace
| << = ← Backspace
| bs = ← Backspace
| * = *
| # = #
| ; = ;
| : = :
| #default = {{{2}}}
}} }}{{#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}}}
}} }} to calculate the formula right on the input line.
Then hit {{#switch:enter
| shift = ⇧ Shift
| up = ↑
| down = ↓
| > = →
| < = ←
| enter = ↵ Enter
| cmd = ⌘ Cmd
| command = ⌘ Cmd
| opt = ⌥ Opt
| option = ⌥ Opt
| tab = Tab ⇆
| backspace = ← Backspace
| << = ← Backspace
| bs = ← Backspace
| * = *
| # = #
| ; = ;
| : = :
| #default = Enter
}} {{#if:| + {{#switch:{{{2}}}
| shift = ⇧ Shift
| up = ↑
| down = ↓
| > = →
| < = ←
| enter = ↵ Enter
| cmd = ⌘ Cmd
| command = ⌘ Cmd
| opt = ⌥ Opt
| option = ⌥ Opt
| tab = Tab ⇆
| backspace = ← Backspace
| << = ← Backspace
| bs = ← Backspace
| * = *
| # = #
| ; = ;
| : = :
| #default = {{{2}}}
}} }}{{#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}}}
}} }} 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 Add , 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,