Excel: Conditional Formatting of Formulae

Yesterday, I was working with an Excel sheet and wanted to calculate a table of values, but be able to override some of the values. To make it obvious which values were calculated and which typed in, I decided to make the calculated values grey and leave the overridden values as the default black text.

Excel is massively flexible, so I didn't think I'd have much trouble – something in the Conditional Formatting arena would do what I needed. To my surprise, although I could do it, the only technique I found (on the j-walk website) relies on an obscure part of Excel that has almost been lost to memory: the XLM macro language, which was superseded by VBA in 1993.

I'm not entirely sure which version of Excel is in the picture on that page (97?), but it's old enough so that some of the technique has changed. For Excel 2007 (and probably later versions):

1. On the "Formulas" ribbon, click "Define Name"
2. Name your name, "CellHasFormula"
3. In the "Refers to:" box, type, "=GET.CELL(48,INDIRECT("rc",FALSE))"
4. Click "OK"
5. Select the cells for which you want the conditional formatting rule to apply
6. On the "Home" ribbon, click "Conditional Formatting" and select "New Rule…" from the menu.
7. Select rule type "Use a formula…"
8. In the rule description formula box, type, "=CellHasFormula"
9. Change your formatting to the desired style using the "Format…" button
10. Press "OK"

In all cases, inverted commas are not included in the values to be used. Be careful of typos: if you make a mistake you probably won't get any error messages (it just won't work).

For the curious, there is a description of the mechanics on the source site. The important point is that "48" is a magic number which instructs the GET.CELL() function to identify cells which contain formulae. Unfortunately, "=GET.CELL(…)" is not a valid argument to the Conditional Formatting rules engine but is to the Define Name engine. It's worth noting that Conditional Formatting rules also don't allow searching for '=' at the start of a cell (anywhere but the start is fine), which would have made this a lot easier.

A complication with using Excel 2007 upwards is that the standard file type (with the .xlsx extension) does not support macros because of potential security issues with passing around files in which they're used. You'll need to use the macro-enabled .xslm format instead. Template files in the .xltm format are fine. When reloading, you may also be told that macros have been disabled. If so, click "Options" on the message to remedy the situation and hit F9 to refresh the formatting once you've done so.

Does anyone know of a better way? Please let me know!

Squeezebox: Longer Timeout for Alarms

At home, we use Logitech Squeezeboxes for all our listening pleasures, including our bedroom where we use the alarm function to wake us up in the morning. The alarm has never been outstanding (a few years ago, a separate plugin was necessary to get reasonable functionality) and although it’s improved in fits and starts over the years, it’s still not a paragon of outstanding design.

For us, the worst problem is that the length of time the squeezebox plays for after the alarm has been triggered is restricted to 90 minutes or less (you can have it play indefinitely, but if you want it to switch itself off at all, 90 minutes is the cut-off). As the Today programme is on for two hours after we wish to be woken up, two hours is the time I want the alarm to last for.

It turns out the restriction is purely down to the user interface and not a limitation of the system itself. I seem to recall posting a feature request for the slider to become logarithmic, which would allow fine control at the 5 / 10 minute end of the scale and longer periods at the other, but it’s never been implemented. I can’t really complain: it’s open source so I should have done it myself. One reason I haven’t is that it’s relatively easy to get around just by editing the relevant prefs file.

On my Ubuntu system, this is /var/lib/squeezeboxserver/prefs/server.prefs; Windows users will probably find it in C:\Documents and Settings\All Users\Application Data\Squeezebox\prefs\server.prefs (according to Logitech's documentation, although newer Windows versions may have a slightly different path). It seems wise to stop the server while you make your edits: I found that changes can occasionally be overwritten.

The section for each player is headed by its MAC address (look in the "Player" tab of the settings pages of the web interface: it's listed in the "Basic Settings" section). Incidentally, the all-zeros MAC address at the start of the file is used as a template when new players are added. Skip through all the _ts stuff and you’ll find several entries starting “alarm”. As you might guess, alarmTimeoutSeconds is the value of interest and this needs to be set accordingly. There are 3600 seconds in an hour, so I have 7200 set. Save, restart the server and you’re done.

A couple of notes. Firstly, this is obviously only applicable to those running Logitech Media Server* at home: if your alarm is set using Logitech’s online system (MySqueezebox.com*) you’re out of luck. I have no idea how the online system works and it may not be subject to the same restrictions anyway. Secondly, be careful about changing the settings using the web interface afterwards – the UI pages will obviously overwrite what’s in the prefs file and you may need to reset the timeout value if you change anything else on the alarm page (other settings changes are fine). As a result, if you edit your alarm times frequently, you may find this process is not worth the trouble.

You can set the alarm time here too (“alarms: <number>: _time”), which makes things easier if you don’t want to disturb the timeout you’ve set, but you will need to calculate the time you’re after in seconds form midnight if you do.

* Correct this week, but nomenclature changes frequently.

Apache mod_rewrite & CodeIgniter

This article isn’t really about CodeIgniter. I’m getting to grips with that at the moment, so I might write some more about it in the future. It is about Apache’s mod_rewrite module and trying to get it to work in a way that’s useful on a dev server for the way CodeIgniter (and other PHP) projects are set out.

What I wanted was to have a single server (i.e. one virtual host) with space for several different projects, or branches of a project. In my opinion, the easiest way to access each project is just to use http://server/project/ in the browser (there are other ways – notably virtual hosts – but they usually require configuration for each new project and / or each new dev machine). With simple websites, it’s fine to put each project in a sub-folder and access them as suggested. However, that does ignores a recommendation for CI projects and one that I think should be followed on any web project and that is to move code that does not need to be publicly accessible outside of the browsable section of your file system (in this case, CI’s “system” and “application” folders should be outside “webroot”, or whatever you want to call it).

My goal was to have the dev server set up so projects could be moved between it and a production server without modification and to have each project wholly contained in its own folder, which means that each project needs its own “webroot” and its own space outside “webroot”. I therefore want every request to //server/project/index.php to be rewritten to //server/project/webroot/index.php (and of course similar for other files in other folders below webroot): in essence, “webroot” needs to be injected after every project name. This means that files and folders other than "webroot" in the project folder become inaccessible to the browser, which isn’t just a matter of convenience for the developer, it means that no resources can be accidentally accessed outside the correct area of the web server’s file system and all relative links (stylesheets, images, etc.) must be properly located.

The first thing I learned is to put the rules directly in the <virtual host> section and not in a <directory> tag wherever possible. There are two reasons for this. Firstly, it’s more efficient – Apache deals with rewriting much faster if it’s not done on a per-folder basis which is because of (at least in part) the second reason, which is that <directory> entries (and .htaccess files in particular directories, which are equivalent) can be parsed multiple times as the request is processed. This can cause major headaches for the unwary because there’s nothing to stop Apache deciding it needs to run through the rules again (in fact, it always seems to do so if the URL has been rewritten) and rather than starting with the original URL, you get the modified one. This means that you can get into an infinite loop if you, say, simply add something on to the end of whatever URL comes in.

The difference between behaviour for rules located in different sections of the config file is not limited to multiple passes, unfortunately. The other thing that changes is the content of some of the variables that you can make use of in the rules. For this reason it is important to check (and potentially modify) any rules you see suggested unless you’re sure that the rules were designed to go in the same place that you want to put them.

I ended up with the following, the second part of which adds index.php after project names (if not present), whilst retaining the rest of the URL as parameters. It’s based on examples in the CodeIgniter documentation:

# Inject 'webroot/' if request starts with a valid folder
# and '/webroot' is not already 2nd folder
RewriteCond %{DOCUMENT_ROOT}$1 -d
RewriteCond $2 !/webroot
RewriteRule ^(/[^/]+)(/?[^/]*)(.*) $1/webroot$2$3

# Rewrite any */webroot/* file request to index.php
# Don't rewrite if file exists OR it's already
# index.php (even if 404)
RewriteCond %{DOCUMENT_ROOT}%{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !/index\.php$
RewriteRule ^(/[^/]+/webroot)/?(.*)$ $1/index.php/$2

I've used %{REQUEST_FILENAME} in conditions for the second rule. Although there are several other variables with similar content, be careful which you choose to use in situations like that above: not only do the values of some of them change depending on the location of the rules within the Apache config files, but I found that some of them had their contents rewritten by earlier rules and some did not (and I found no reference to this in the mod_rewrite documentation).

Linux Fileserver and ClamFS

I recently needed to provide a file server for a client that would work with Windows and OS X clients. For reasons of cost and maintenance we decided to use Ubuntu LTS Server. We also wanted anti-virus scanning as customer files are introduced to this server regularly. I decided to use the popular, open source ClamAV engine, with ClamFS providing the on-access scanning. I want to talk briefly about ClamFS in general, because there isn't much comment on it that I can find and then about a specific problem I had, because the solution is not necessarily obvious and uses an interesting feature of samba.

ClamFS seems to be most straightforward way to provide on-access scanning with ClamAV. It's a FUSE based daemon that mirrors one part of the file system to a mount point elsewhere, providing on-access protection for reads and writes to the mirrored version. I discovered the following about it:

1. The version I installed from the Ubuntu repository doesn't include an init.d script – adding a line to rc.local seems to be the preferred method of boot time initiation. You can, of course, write your own init.d script
   
2. The config file is written in XML, rather than the more readable and more easily editable (certainly on a GUIless server) familiar format that pretty much every other Unix-based config file uses. You need to include the config filename when starting ClamFS
   
3. There is apparently no way to stop the process other than using kill and then manually umounting the FUSE mount associated with it
   
4. Lack of permissions caused a bit of difficulty – the ClamAV user might need some additional permissions before your users can read and write protected files
   
5. There is little documentation; a tutorial taking new users through the steps of installation and configuration would make its use clearer
   
6. Once set up, it seems to work fine: I've had no problems with it.
   

My configuration is as follows: Truecrypt volumes (which are normal files, stored at a point we'll call location A) are mounted at another point in the filesystem (location B) and ClamFS mounts a copy of B to a third point (location C). Location C is then used for the samba share path.

I wondered if having ClamFS start at boot time and mounting a copy of B elsewhere would prevent TC (which doesn't start at boot time) mounting a volume to B later on, but it turns out mounting volumes "underneath" an existing ClamFS mount works fine.

I had another problem though. Because I have more than one share and more than one encrypted volume, I configured ClamFS to protect the directory above the one in which all the TC drives were mounted. Because of this (or maybe because of some other aspect of the redirection), the free space reported by samba was not that of the individual drives mounted within the ClamFS protected directory, but the space on the drive that contained those mount points (or the point which the ClamFS was mounting to, I'm not sure which as they are on the same partition).

This can be more than an annoyance because Windows systems from Vista onwards actually check this free space before attempting to write a file. If there isn't room, you can't write. In my case, reported size was on a partition that was almost full of TC volumes, so the reported free space (and therefore the maximum file size that could be written by Windows 7 clients) was severely curtailed.

There are two possible ways round this. The most obvious is to only allow ClamFS to mount to and from points inside any TC volumes you want to share. This will cause you headaches if either you have many shares and only want to have ClamFS configured to protect one directory or ClamFS needs to be started before TC mounts its volumes (common, because manual intervention is usually needed on TC mounts for security reasons).

The second solution is to use a feature of samba which allows you to override the internal free space code with a method of your design. The smb.conf man page explains the details – essentially you need to provide a command (writing a script seems to be the most common solution) that will return two numbers. These give the total number of 1K blocks in the filesystem and the number that are free, respectively. The man page makes a suggestion which I tailored slightly:

#!/bin/sh
df -P $1 | tail -1 | awk '{print $2,$4}'

The "-P" switch (added to the df command) forces the results for each drive onto a single line. If you don't do this and the path reported for the partition is longer than 20 characters, a line break is inserted and the positional parameters to awk will be incorrect.

You then need to make sure the definition in smb.conf for each affected share contains the following:

[Sharename]
   …
   path = /path/to/share  # loc C
   dfree command = /path/to/script.sh /path/to/TC/mount  # loc B

A quick side note: samba calls the script with the location it is trying to ascertain the size of as a first parameter. We've included a first parameter here, which simply pushes the samba-appended one into second position (which is then ignored). I have read that samba may call the script with the parameter "/", having chrooted to the share point before executing the script. I haven't investigated exactly what is happening in my test or production installations, but both work with the procedure I have outlined and this would not be the case if any chrooting were going on. I can only conclude that this is not the behaviour of current versions of samba (I'm using 3.4.7, courtesy of Ubuntu 10.04 LTS) or something else about my environments is altering that behaviour. I'd be interested to hear about different experiences.