[Calc,Python,Basic,Base] Several ways to time stamps

Creating Extension - Shared Libraries
Forum rules
For sharing working examples of macros / scripts. These can be in any script language supported by OpenOffice.org [Basic, Python, Netbean] or as source code files in Java or C# even - but requires the actual source code listing. This forum is not for asking questions about writing your own macros.

[Calc,Python,Basic,Base] Several ways to time stamps

Postby Villeroy » Wed Nov 26, 2008 8:39 pm

 Edit: 2009-03-28: How to insert a time stamp into Calc without any macro 


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.

 Edit: 2010-12-29: How to insert a time stamp depending on another cell value into Calc without any macro 

menu:Tools>Options...OOoCalc>Calculate: Enable iteration with 1 step
Code: Select all   Expand viewCollapse view
Cell B1: =IF(A1="";"";IF(B1="";NOW();B1))

returns the current time when A1 is not empty nor empty string.
Thanks to this forum's most valued member Robert Tucker pointing us to http://www.mcgimpsey.com/excel/timestamp.html

Several macros to write time stamps into spreadsheet cells. It writes naked numeric values, leaving all formatting up to you. Straight ahead, no gimmicks, no error-handling. Just tested against the assumed scenarios.

1. NowToActiveCell
Writes the time stamp to the currently active input cell. Best used with a shortcut.

2a. NowToTimeStampCell
Writes the time stamp to a named cell "TimeStamp" anywhere in the current document.
2b. NowToEmptyTimeStampCell
Same as above, leaving alone an already filled stamp-cell.

3.a NowToColumnAByValidation
Triggered by a failed validation. Writes the time stamp to the same row's column "A" when a validated cell has been modified. Set validation to "Text Length = 0" with action set to "Macro". The validation fails, when you put something into the cell (length becomes >0), which triggers the macro.
3b. NowToEmptyColumnAByValidation
Same as above, leaving alone an already filled stamp-cell.

Python Install
Download, extract and copy to one of the suggested directories.
Single user:
Does not work with version 3.0. Use the application wide directory until the release of 3.0.1
<user profile>/user/Scripts/python/

Application wide:
v2.x <install dir>/share/Scripts/python/
v3.0 <install dir>/basis3.0/share/Scripts/python/
Symlinks do work under Linux, so you can create links to files in any directory.

Basic Install
Re: [Tutorial] How to install a code snippet

 Edit: 2008-12-21 Replaced Python attachment. It can be called from a menu or button now. 

 Edit: 2010-09-24: Simplified the Python module, added NowToRightNeigbour 


Basic version:
Code: Select all   Expand viewCollapse view
REM  *****  BASIC  *****
REM I won't update this Basic code. You may refer to the latest Python
Sub test_splitStringAddress()
REM instead of splitting the string address you may simply use the active cell,
REM ignoring the strings from the validation trigger. See updated Python code
Dim s$,sSh$,sRg$
   a=Array("'my sheet'.A1", _
   "$'my sheet'.A", _
   "'file:///doc.ods'#'my sheet'.A1", _
   "$'file:///doc.ods'#$'my sheet'.A")
   for i= 0 to uBound(a())
      s = a(i)
      splitStringAddress(s,sSh,sRg)
      print sSh,sRg
   next
End Sub
sub splitStringAddress(s$, sSheet$, sRange$)
dim sURL$, linkPos%, sheetPos%
   ' remove leading $ in any case:
   if left(s, 1) = "$" then s = mid(s, 2)
   linkPos = instr(s, "'#")
   if linkPos > 0 then
      sheetPos = instr(linkPos, s, ".")
      sURL = left(s, linkPos +1)
      sSheet = mid(s, linkPos +2, sheetPos -linkPos -2)
      sRange = mid (s, sheetPos +1)
   else
      sheetPos = instr(s, ".")
      if sheetPos > 0 then
         sSheet = left(s, sheetPos -1)
         sRange = mid (s, sheetPos +1)
      else
      sRange = s
      endif
   endif
   if left(sSheet, 1) = "$" then sSheet = mid(sSheet, 2)
   if (left(sSheet, 1) = "'") and (right(sSheet, 1) = "'") then
      sSheet = mid(sSheet, 2, len(sSheet) -2)
   endif
   sSheet = sURL & sSheet
end sub
'-------usefull helper-function, returning focussed cell
'by UROS > http://www.oooforum.org/forum/viewtopic.phtml?t=19348
REM 2006-08-09: fixed error when row > 8191
'       ;sh;                     ;lSheet +3
'100/60/0;1;tw:309;2/2/0/0/0/0/2/0/0/0/0;253/8191/0/0/0/0/2/246/0/0/8158;0/0/0/0/0/0/2/0/0/0/0
'100/60/0;1;tw:309;2/2/0/0/0/0/2/0/0/0/0;253+8192+0+0+0+0+2+246+0+0+8158;0/0/0/0/0/0/2/0/0/0/0
Function getActiveCell(oView)
Dim as1(), lSheet&,lCol&,lRow$, sDum as String,bErr as Boolean
   as1()  = Split(oView.ViewData, ";")
   lSheet = CLng(as1(1))
   sDum = as1(lSheet +3)
   as1() = Split(sDum, "/")
   on error goto errSlash
      lCol = CLng(as1(0))
      lRow = CLng(as1(1))
   on error goto 0
   getActiveCell = oView.Model.getSheets.getByIndex(lSheet).getcellByPosition(lCol,lRow)
exit Function
errSlash:
   if NOT(bErr) then
      bErr = True
      as1() = Split(sDum, "+")
      resume
   endif
End Function
Sub 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())
End Sub

Sub _NowToColumnAByValidation(sAddr, bOverwrite)
Dim s$,r$
    iCol = 0
    doc = thisComponent
    splitStringAddress(sAddr,s,r)
    sh = doc.Sheets.getByName(s)
    rg = sh.getCellRangeByName(r)
    iRow = rg.RangeAddress.StartRow
    c = sh.getCellByPosition(iCol, iRow)
    if bOverwrite or (len(c.getFormula())=0)then printStamp(c)
End Sub

Sub _NowToTimeStampCell(bOverwrite)
    oNames = thisComponent.NamedRanges
    oName = oNames.getByName("TimeStamp")
    oRg = oName.getReferredCells()
    c = oRg.getCellByPosition(0,0)
    if bOverwrite or (len(c.getFormula())=0)then printStamp(c)
End Sub

Sub NowToEmptyColumnAByValidation(sFormula, sAddr):
    '''Current time to empty column A, triggered by failing validation'''
    _NowToColumnAByValidation(sAddr, False)
End Sub

Sub NowToColumnAByValidation(sFormula, sAddr):
    '''Current time to column A, triggered by failing validation'''
    _NowToColumnAByValidation(sAddr, True)
End Sub

Sub NowToTimeStampCell():
    '''Put current time into a cell named "TimeStamp"'''
    _NowToTimeStampCell(True)
End Sub

Sub NowToEmptyTimeStampCell():
    '''Put current time into an empty cell named "TimeStamp"'''
    _NowToTimeStampCell(False)
End Sub

Sub NowToActiveCell():
    '''Put current time into the currently active input cell'''
    oDoc = thisComponent
    oView = oDoc.getCurrentController()
    oCell = getActiveCell(oView)
    printStamp(oCell)
End Sub


 Edit: 2009-03-28: Adding a link to another approach involving c.s.s.util.XModifyListener. Not as easy to implement since the listener needs to be attached to the respective range(s). Trouble with race conditions when used with volatile functions such as NOW(). This is why I suggest the NowToColumnAByValidation with the validation-trick: Re: Run a python macro when cell contents change 

 Edit: 2016-11-04: Made the code Python3 compatible. Works with recent versions of LibreOffice 
Attachments
TimeStamp2Calc.py.zip
Latest version of the Python macros
(1009 Bytes) Downloaded 209 times
Last edited by Villeroy on Fri Nov 04, 2016 8:07 pm, edited 20 times in total.
User avatar
Villeroy
Volunteer
 
Posts: 27111
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc,Python,Basic] Several ways to time stamps

Postby Villeroy » Sat Mar 28, 2009 5:37 pm

The time-stamp feature is typical for a database such as counter.odb
Databases of all kinds and derived record sets integrate perfectly in Calc, even better than values directly stored in the spreadsheet.

Testing the counter.odb with a spreadsheet:
Open the downloaded document.
Open the form therein.
Enter a few integer numbers (other values will be rejected).
There is no need to store anything. Databases write records to disk.
Select "Tables" in the main database window.
Open "Stamps" and see the time stamps and auto-IDs (to be used in further, interrelated tables).

Call menu:Tools>Options...OOo Base>Databases and register the database document.
Open a spreadsheet.
Call the beamer [key F4]
Browse the database>Tables>Stamps
Drag Stamps from the left pane into the spreadsheet.
menu:Data>Define...
Select "Import1"
and check options
[X] Insert/delete cells
[X] Keep formatting

Enter some formula in D2 (directly beneath the first "Value"), say =D2*2 and copy down the formula.
Apply any number formats you like to see in the import range. All values are guaranteed to be integers and date-times without gaps.

Edit some values using input form, the table or the beamer (invalid values and duplicate IDs will be rejected, the time stamps can be overwritten).
Spreadsheet-menu:Data>Refresh will refresh the import range.

Add some new integers into field "Value" using input form, the table or the beamer.
Spreadsheet-menu:Data>Refresh will refresh and resize the import range and the adjacent formula range as well.

Delete some records.
Spreadsheet-menu:Data>Refresh will refresh and resize the import range and the adjacent formula range as well.

Auto-resizing lists, including chart ranges and adjacent formulas, together with enforced consistancy, the ability to get arbitrary filtered and recombined data sets in arbitrary order of rows and columns, the fact that all data are stored in one place but usable in all Writer and Calc documents, including data pilots in Calc, without a single line of macro-code. All this can't be done using spreadsheets as the only tool.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27111
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc,Python,Basic] Several ways to time stamps

Postby Villeroy » Sat Mar 28, 2009 6:14 pm

Starting with the Calc-problem and a simplistic macro-solution, turning the view to a completely different approach (separation of storage and calculation model), now for one of the most frustrating shortcomings in OOo Base. The table designer in Base can not add time stamps as default values even though this is supported by the underlying HSQLDB.
Design your table without the stamp.
Call db-menu:Tools>SQL... and run
Code: Select all   Expand viewCollapse view
ALTER TABLE "TableX" ADD COLUMN "Stamp" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL BEFORE "X";

For an already existing field that is:
Code: Select all   Expand viewCollapse view
ALTER TABLE "TableX" ALTER COLUMN "Stamp" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;

I used the double-quoted object names as follows:
"TableX" is the table in question.
"Stamp" the name of the stamp field to be added.
"X" is the name of another field. Leave out the clause BEFORE "X" in order to append the column.
NOT NULL makes the field mandatory so you can not delete an existing stamp (it can be edited though).
Use NOT NULL if and only if the table has no data yet, otherwise the insertion of this column will fail.
Using a form bound to a table with a time stamp, such as the above linked counter.odb, requires that you unset the following option:
db-menu:Edit>Database>AdvancedSettings... "Form input checks for required fields". Otherwise the form will reject to store the record with the missing date-value. With this option unset, the underlying HSQLDB takes care of the integrity, adding the field's default value.

Currently, I don't see any option to implement time stamps that update with every change of the record. Within Base this requires a simple macro to be installed. Any macro solution applies to input forms only without affecting the underlying database. You could edit a record in the table without triggering any macro.

The stand-alone version of HSQLDB supports triggers which can do all kinds of actions for update, delete and insert events. In the current Base implementation it is impossible to define triggers for the embedded HSQLDB.

But all this is still better than using spreadsheets as storage. Once you've got everything set up and linked, the combination of Base and Calc can solve many of the most frequent list keeping problems in Calc without further intervention.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27111
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Code Snippets

Who is online

Users browsing this forum: No registered users and 1 guest