LibreOffice BASIC Easy GetCell

Improving on GetCell from an earlier post I have added a Column2Index function to allow you to pass the names of the column and row rather then the index.

If the column is a number then it will use the values as index values, otherwise it will convert both the column and the row.

These two calls are the same
GetCell("Sheet1", "D", 3) <- using the names of the columns and rows
GetCell("Sheet1", 3, 2) <- using the index of the columns and rows


For cell that contain numbers, ether fixed or formula generated you can simply read from them or write to them like so.

If GetCell("Sheet1", "D", 3).Value > 100 Then
   GetCell("Sheet1", "D", 3).Value = 0
End If

For cells that contain text you use GetCell("Sheet1", "D", 3).String the same way you would any normal string variable.

Function Column2Index(ColNameX As String)
 ReturnInt = 0
 ColNameU = Ucase(ColNameX)
 While ColNameU <> ""
  LastChar = Left(ColNameU, 1)
  ColNameU = Right(ColNameU, Len(ColNameU) - 1)
  ReturnInt = ReturnInt * 26
  ReturnInt = ReturnInt + Asc(LastChar) - 64
 Wend
 Column2Index = ReturnInt - 1
End Function

' Now accepts column and row as they are named or their index value
' These two calls are the same
' GetCell("Sheet1", "D", 3) <- using the names of the columns and rows
' GetCell("Sheet1", 3, 2) <- using the index of the columns and rows


Function GetCell(SheetName As String, Column, Row) As com.sun.star.table.XCell
 ColumnNumber = 0
 RowNumber = 0
 If ISNUMERIC(Column) Then  
  ColumnNumber = Column
  RowNumber = Row
 Else
  ColumnNumber = Column2Index(Column)
  RowNumber = Row - 1 
 End If
 AllSheets = ThisComponent.Sheets()
 FindSheet = AllSheets.GetByName(SheetName)
 TheCell = FindSheet.GetCellByPosition(ColumnNumber,RowNumber)
 GetCell = TheCell
End Function

Simple Process Locking In Perl

My co-work Rick asked for a simple process lock control in Perl that would work over a server load balanced pool with a common NFS share. The easiest way is a loop that tries to create a directory, if the command fails then it waits and tries again, if the command succeeds then it has the lock and can do it's job. When done it removes the directory and another process gets to continue.

We started to build in an extra step so that if the script dies during the "meat and potatoes" section anther process can check if the PID from the previous process is still running and if not remove it. We did not get that far, I will let you figure the rest out.

The first usleep command is just to prevent process hammering, you should set it to match your expected time for a process to run. The second usleep is to simulate process congestion and is only for testing, you should remove it from your project.

#!/usr/bin/perl -w

use Time::HiRes qw(usleep nanosleep);

my $COUNTER=0;
my $dir = "/mnt/share/templock.dir";

while (! mkdir($dir) )
{
 $COUNTER++; 
 usleep(100);

 printf ("$$ waiting: $COUNTER\n");
 if ($COUNTER > 1000)
 {
  #open(my $fh, "<", $dir/PID");
  #my $row = <$fh>;
  
  die("squak\n");
 }
}

open(my $fh, ">", "$dir/PID");
printf $fh "$$\n";
close($fh);

#meat and potatoes
usleep(10000);


unlink("$dir/PID");
rmdir($dir);

Some tail or some head

Often times I find that head and tail don't put out enough. Some times I just want more. I don't want to create aliases for them because there are times that I just need it to work normally and I am too lazy to absolute path to remove the alias.

This is how I am able to me some tail and some head with more.

In my home I have a bin directory that is just for my bin scripts, but you could put this in /usr/local/bin/ if you like.

I wanted one script that does both, so I use basename and the built in BASH test replacement operation to strip the command down to either head or tail. I created the script as somehead and then made a symlink that points to it called sometail.

$(basename ${0//some/}) -n $(( $(tput lines) - 3 ))

Notice this uses tput to get the number of lines in the terminal and then subtracts three, just so it does not overflow the screen and you get an idea of where the output started.

Remember to

chmod 755 somehead
ln -s somehead sometail

Running shell commands from awk

There are a few ways to run shell commands from inside an awk command. One is system(...) but it is better for handing values off to a program to do something, not the best way to get some data back from the shell command.

The best way to get data back from a command is to define the command as a variable then execute it piping the output to a new variable using the built in getline function.

The input of this example is a log file were the first field is epoch time and we need to see the time in human readable time format.

tail /var/log/my.log | awk ' {
   DC="date -d@"$1; 
   DC | getline T; 
   printf "%s\t", T; 
   for(i=2;i<NF;i++) {
     printf $i"\t"
   }; 
   printf "\n";
}'

In this example DC becomes the date command that is given the epoch value in variable $1.  T is the variable for the time as a string that we display using printf. Then a loop prints the rest of the data from each line of the log from the second filed to the NF, end number of fields.

Truncate a file with sed

Log files grow over time but you don't want to fill your disk. Often a log file is just for simple debug and does not need to be rotated or kept in /var/log/, sometimes a simple debug log just goes well in /dev/shm/. In this case it is important to keep it short and trim off the top of the file from time to time.

I don't know why but this problem seems very difficult for a lot of people and they end up writing long complex multi line blocks of code to truncate a file and just keep the end of it. It is super simple with sed.

# only keep the last 100 lines of the log file
sed -i /dev/shm/my_debug.log -e :a -e '$q;N;100,$D;ba'

Alpha sequence

When doing a quick little shell script the seq command is very handy to generate a list of numbers but all too often I find that I need a sequence of letters and there is no alphabetical version of seq. There are hundreds of examples on the Internet of how to do this in almost every script language you can think of, this is my version in bash.

This version has the added feature of being able to run up a sequence of upper or lower case letter. The script only accepts one argument of the last letter, but could easily be expanded to do all sorts of extra tricks.

#!/bin/bash

OUT=""
TARGET=$(echo ${1:0:1} | grep -i "[a-z]")
UPPER=$(echo $TARGET | tr "[a-z]" "[A-Z]")
START=97 # a
[ "$TARGET" = "$UPPER" ] && {
 START=65 # A
}
while [ "$OUT" != "$1" ]; do
 OUT=$(awk 'BEGIN{printf "%c",'${START}'}')
 echo -n "$OUT "
 let "START=$START+1"
done

Examples:

$ alphaseq J
A B C D E F G H I J

$ alphaseq m
a b c d e f g h i j k l m

wget script hacking the Cisco DPC3825

My ISP at home has some of the best prices on high speed Internet around. They can do this because they only use the most budget of all the junky equipment small amounts of money can buy. Ever since I first subscribed my home router (CATV modem) will progressively get slower and slow with decreasing Wi-Fi range until I go through the steps to login to the web interface and click "Reset" or pull the power and plug it back in. After that it is good for a few days. The problem with that is it dies at the worst time. I have complained for many months but the only thing that happens is they replace the junk modem with another junk modem.


The best way to solve this is to create a wget script that cron will run around 4:00 AM when no one is using the Internet. The script will reboot the modem and have it ready for me to use problem free all day.

wget is a very powerful command line web client. Reading the source file of the page you want to script you can automate almost any action.

#!/bin/bash

wget \
  --save-cookies=cookies \
  --post-data="username_login=cusadmin&password_login=yourpassword" \
  http://192.168.0.1/goform/Docsis_system -O /dev/null 2>&1

wget \
  --load-cookies=cookies \
  --post-data="devicerestart=1" \
  http://192.168.0.1/goform/Devicerestart -O /dev/null 2>&1