Star, Open, Libre. Automating your spreadsheets

I started using StarOffice when it first came out and it was good. OpenOffice was great and did everything I ever needed. When Oracle bought Sun Microsystems the developers left to create LibreOffice. OpenOffice was released from Oracle and now lives in the Apache Software Foundation. Some day the two may get unified but for now LibreOffice is the popular choice, and it is great.

I love to create spreadsheets and often offer to help my co-workers with automating their scalc files for them. I have a lot of fun writing StarBasic macros. With a little work the StarBasic macros can be modified to work in other office programs.

Two functions that I add to almost every spreadsheet are GetCell and GetDataCell to make it easy and fast to read and change the cell contents.

Function GetDataCell(SheetName As String, DataName As String, Row) As com.sun.star.table.XCell
 AllSheets = ThisComponent.Sheets()
 FindSheet = AllSheets.GetByName(SheetName)
 For ALoopCounter = 0 to 1000 Step 1
  TheCell = FindSheet.GetCellByPosition(ALoopCounter,0)
  If TheCell.String = DataName then
   TheCell = FindSheet.GetCellByPosition(ALoopCounter,Row)
   Exit for
  End if
  If TheCell.String = "" then
   MsgBox("Could not find DataField = "+DataName)
   Stop    
  End if
 Next ALoopCounter 
 GetDataCell = TheCell
End Function


Function GetCell(SheetName As String, Column, Row) As com.sun.star.table.XCell
 AllSheets = ThisComponent.Sheets()
 FindSheet = AllSheets.GetByName(SheetName)
 TheCell = FindSheet.GetCellByPosition(Column,Row)
 GetCell = TheCell
End Function

Bash me with a pipe

I love to use the pipe. It is a simple tool that brings so much power. So this is how you can take things to the next level.

The snippet below shows how to redirect STDOUT to the STDIN of one script and the STDERR to the STDIN of another.

You cannot use a pipe for both because it will only redirect the file descriptor connected at fd1, normally STDOUT unless you change it. When you use a pipe everything to the right becomes a different program, so STDERR needs a different solution.

The solution is to redirect fd2 before you use the pipe on fd1.

$ crontab -l
# This cron entry sends STDERR to MyErrorLogger.sh and STDOUT to MyLogger.sh
* *  *  *  * ./MyScript.sh 2> >(MyErrorLogger.sh) | MyLogger.sh



$ cat MyLogger.sh 
#!/bin/bash
# This takes STDIN and sends it out to a file that has the date in its name
cat - >> mylog_$(date +%F).log 



$ cat MyErrorLogger.sh 
#!/bin/bash
# This takes STDIN and sends it out to a file that has the date in its name
cat - >> myerrors_$(date +%F).log



$ cat MyScript.sh
#!/bin/bash
# This sends sample text to both STDOUT and STDERR
echo "Hello world!" | tee /dev/fd/2

Perl standard deviation

First of all, yes there is a module for this, but what fun is that. I needed to do a standard deviation calculation in a production system where we have a custom stripped down Perl and I did not have time to get change control for installing a new RPM. I don't need change control to have my own Perl script in my home directory and it is so simple. Also I love writing math code, when ever possible I try to use a bit mask test rather then big lists of if conditions.

Perl is one language that it bittersweet to me. It is very powerful, has endless ability, millions of modules that add every possible feature you could want. My only complaint is that it is so flexible that it creates the perfect environment for bad code habits. Most of the worst code I have ever seen was done in Perl, and some of it was my code.

Coding in Perl is fun and it is a very powerful tool. If you have not done any Perl coding you should give it a try. For now here is my simple code to calculate standard deviation.

#!/usr/bin/perl
use strict;

#Prevent division by 0 error in case you get junk data
exit undef unless(scalar(@ARGV));

# Step 1, find the mean of the numbers
my $total1 = 0;
foreach my $num (@ARGV)
{
        $total1 += $num;
}

my $mean1 = $total1 / (scalar @ARGV);

# Step 2, find the mean of the squares of the differences
# between each number and the mean
my $total2 = 0;
foreach my $num (@ARGV)
{
        $total2 += ($mean1-$num)**2;
}
my $mean2 = $total2 / (scalar @ARGV);

# Step 3, standard deviation is the square root of the
# above mean
my $std_dev = sqrt($mean2);
printf "%0.2f", $std_dev;

That's what I sed!

The power of the Linux CLI comes from the ability to connect thousands of programs together and make new tools. The most commonly used connector is the pipe. | It lets you send the output "STDOUT" from one program to the input "STDIN" of another. The sed program is great for taking input and changing it. The name sed is short for Stream EDitor, the stream is the data that flows in to and out of the program. sed can also edit files in place.

I use a command like this to edit the XML configuration files of Tomcat servers to point the Java program to new MySQL servers.

sed -i /usr/share/tomcat6/conf/server.xml.new 
-e '/Context docBase="someblock"/,/<\/Context>/{s/url="jdbc.*mydb/url="jdbc:mysql:\/\/'$DB_HOST':3306\/mydb/}'

CLI Tools: screen

One of my favourite CLI tools is screen. Basically it lets you detach from your shell session and it keeps running. You can reattach to it later from anywhere, start your shell from a local TTY then reattach via SSH. You can even share your session and have others join in so you can collaborate on shell.

The sample code for today is how to make an init script to run your program as a detached daemon using screen. The cool thing about this is it lets you connect to it at any time to see what it is doing. This is not recommend for normal use but it is a great way to debug that new program before you commit to making it a proper daemon.

#!/bin/bash

cd /etc/init.d

start() {
 echo "Starting Service: "
 APP_CMD="/usr/local/bin/MyProgram.pl"
 screen -dmS pmsgd $APP_CMD
 echo
}

stop() {
 echo "Cannot stop screen programs this way. Open the screen sessions."
 echo
}

restart() {
 stop
 start
}

# See how we were called.
case "$1" in
  start)
 start
 ;;
  stop)
 stop
 ;;
  restart)
 restart
 ;;
  *)
 printf "Usage: %s {start|stop|restart}\n" "$0"
 exit 1
esac

exit 0

Find zen for your users with Zenity

"That is a great program you made for us but we don't like that command line thing. Can you make it a GUI?" To which you reply, "Why yes I can. Just give me a few minutes."

Shell scripts are great. You have all the power from thousands of Linux commands that you can connect together to make the most impressive scripts since the ENIAC. No matter how great your scripts are there will be users that just don't like it because of the command line.

You have three options:  create a web server interface,  re-write it as a binary with graphical objects or use pre-existing dialog tools to add GUI features to your program. Zenity can do that for you.

There are a few dialog tools like zenity, kdialog, xdialog and may more. They are not very hard to use and can provide a lot of helpful iteration with things like information boxes, input boxes, selections and radio buttons.

zenity --entry --text "Please enter your name"

Daemonize your code

If you write a shell script or program and need to fork it to the background as a daemon there could be some issues. The normal standard daemon library may not be able to totally disconnect from any open file descriptors. This simple C++ program can do it all for you and it is so easy to use, just put this program in front of your normal command and it will turn any normal program in to a system level daemon.

#include <sys/types.h>
#include <sys/stat.h>
#include <stdio.h>
#include <stdlib.h>
#include <fcntl.h>
#include <errno.h>
#include <unistd.h>
#include <syslog.h>
#include <string.h>

int main(int argc, char *argv[]) {
 int i;
 // int reterr;
 pid_t pid, sid;
 
 //Fork the Parent Process
 pid = fork();
 
 if (pid < 0) { exit(EXIT_FAILURE); }
 
 //We got a good pid, Close the Parent Process
 if (pid > 0) { exit(EXIT_SUCCESS); }
 
 //Change File Mask
 umask(0);
 
 //Create a new Signature Id for our child
 sid = setsid();
 if (sid < 0) { exit(EXIT_FAILURE); }
 
 //Change Directory
 //If we cant find the directory we exit with failure.
 if ((chdir("/")) < 0) { exit(EXIT_FAILURE); }
 
 //Close Standard File Descriptors
 close(STDIN_FILENO);
 close(STDOUT_FILENO);
 close(STDERR_FILENO);
 
 //----------------
 //Main Process
 //----------------
 for(i=0; i < argc - 1; i++) {
  argv[i]=argv[i+1];
 }
 argv[argc-1] = '\0';
 execv(argv[0], argv);
 //reterr = execv(argv[0], argv);
 //printf("execv failed with '%s'\n", strerror(errno));

 //Close the log
 closelog ();
}