Ubuntu 9.04 delayed (for me)

I received the Ubuntu 9.04 CD through shipit a couple of weeks ago, which was great. So, I sat down to install it in place of 8.10 last weekend, popped the disc into the drive, and then…nada. No acknowledgement of the CD at all. Turns out the drive was faulty – my laptop must have taken a knock at some point as BIOS didn’t even recognise that there was a CD-ROM drive at all, even after unseating and reseating it.

So, I called up Dell tech support, got a replacement drive sent out, which is now safely installed and humming away. Nice. Only problem is, I had taken the CD round to install Ubuntu on my brother’s laptop (which is now running well, except for a lack of wireless connection). I’d left the CD with him, so now I need to get it back before I can install 9.04 on mine…I’m starting to wonder whether I will get 9.04 installed before 9.10 is released. <Sigh>

Advertisements

Why I’d like Python at work, part 2

Automating Excel with Python

As well as using Python to interact with the filesystem, it’s possible to use COM automation to open Excel and access its functionality. I decided to take a look at Mark Hammond’s PythonWin IDE, which contains the win32com.client package to provide a wrapper for the Excel automation object. For example, the function newest from part 1 could be implemented as follows:

# OpenNewest.py - opens latest Excel file
import os, os.path, win32com.client

def newest(ext, path=os.getcwd()):
    '''pre: path a valid absolute filepath (optional), ext a file
    extension (e.g. '.xls')
    returns: name of the newest (last modified on *nix systems,
    last created on Windows) file with extension ext from path
    '''
    file_dict = dict([(fname, \
      os.path.getctime(os.path.join(path, fname))) \
      for fname in os.listdir(path) \
      if os.path.splitext(fname)[1] == ext])
    return os.path.join(path, \
                        max(file_dict, key=lambda x: file_dict[x]))

if __name__ == __main__:
  xlApp = win32com.client.Dispatch("Excel.Application")
  lastFileName = newest('.xls')
  lastFile = xlApp.Workbooks.Open(lastFileName)

  # Do some stuff with lastFile...

  lastFile.Close()
  xlApp.Quit()
  xlApp = None

So, what stuff can you do? One thing that would be nice is to be able to open an Excel file and run a macro in it. I decided to test this out by saving a file named ‘PythonTest.xls’, containing the macro ‘helloFromPython’:

Public Sub helloFromPython()
Sheet1.Range("B1:B100").Value = "Hello from Python!"
End Sub

The script RunInExcel.py then creates an Excel automation object, which opens the file and calls the macro in it:

# RunInExcel.py - opens named Excel file fName, runs named macro
# macName
import os, os.path, win32com.client

def run_macro(fName, macName, path=os.getcwd()):
  """
  pre: fName is the name of a valid Excel file with macro macName
  post: fName!macName is run, fName saved and closed
  """
  fName = os.path.join(path, fName)
  xlApp = win32com.client.Dispatch("Excel.Application")
  fTest = xlApp.Workbooks.Open(fName)
  macName = fTest.Name + '!' + macName
  xlApp.Run(macName)
  fTest.Close(1)
  xlApp.Quit()
  xlApp = None

if __name__ == "__main__":
    run_macro("PythonTest.xls", "helloFromPython")

Run the script, open the file and voila!

python_test

What I’m thinking would be really nice is to be able to automate a lot of our regular reporting by doing this – a single script could be run to kick off a batch of reports. I wonder, though, about how efficient using Python to do this would be as opposed to using a VBA/VBScript routine to do the same thing…does anyone else use Python for this sort of task?

Why I’d like Python at work

One of the main reasons I like Python as a scripting language is its brevity – in particular I enjoy using things like list comprehensions, which enable you to escape the use of for loops when dealing with lists. Lists in Python are powerful and when used in combination with map and filter, allow you to do a lot more with a lot less code. This is one of the things I really miss when I write code in VBA/VBScript, where you’re often stuck dealing with either arrays or collections from the object model. Dive Into Python offers an interesting perspective on this.

Dick Kusleika posted a VBA sub to open the newest file in a folder over at Daily Dose of Excel today, which made use of the Scripting library to iterate over .csv files in a folder, find the newest and open it. Dick mentioned wanting an easier way to find the newest than looping over all of them, which got me thinking. If the Scripting.Folder object had a .NewestFile property, then that would obviously do it, but failing that, I could not see any other way than to loop through all files to examine the create dates.

In Python, however, without looping, the exercise becomes a two-line function:

import os, os.path, sys

def newest(ext, path=os.getcwd()):
  """
  pre: path a valid absolute filepath or None, ext a file 
  extension (e.g. '.py')
  returns: newest (last modified on *nix system, last created 
  on Windows) file from path
  """
  file_dict = dict([(fname, \
    os.path.getctime(os.path.join(path, fname))) \
    for fname in os.listdir(path) \
    if os.path.splitext(fname)[1] == ext])
  return max(file_dict, key=lambda x: file_dict[x])

I’m not sure at all whether there would be any performance benefit from doing it this way as opposed to looping over the files, but the real benefit of this is that it frees you from the worry of how to construct a for loop over the list of filenames returned from os.listdir(). All you need to worry about is which one of them you want.