I've talked in the past about automating iTunes, and about automating Excel. Let's now look at how to use Ruby to produce an iTunes report in Excel. Our finished product will be a sorted worksheet containing Artist, Year, Album, and Song Name.
As usual, we'll be working with the win32ole library, so include the following at the top of your code:
require 'win32ole'
Next, we want to launch the iTunes application using the
WIN32OLE.new() method:
itunes = WIN32OLE.new('iTunes.Application')
We'll create an array to hold the iTunes Library data:
data = []
The following code iterates over the iTunes
LibraryPlaylist.Tracks collection. For each track in the library that is not a podcast, our code adds a row to the
data array containing Artist, Year, Album, and Year, all of which are properties of the Track object.
itunes.LibraryPlaylist.Tracks.each do |track|
if not track.Podcast
data << [track.Artist, track.Year, track.Album, track.Name]
end
end
To include podcasts in your report, simply remove the
Podcast conditional:
itunes.LibraryPlaylist.Tracks.each do |track|
data << [track.Artist, track.Year, track.Album, track.Name]
end
Now that we have our data array, let's sort it...
data.sort!
...and then insert a row of field names as the first row:
data.insert(0, ['ARTIST', 'YEAR', 'ALBUM', 'NAME'])
Next, we'll launch a new instance of Excel, assigning it to the
xl variable...
xl = WIN32OLE.new('Excel.Application')
...and make the application window visible:
xl.Visible = true
We create a new workbook by calling the Application object's
Workbooks.Add() method...
wb = xl.Workbooks.Add()
...and we get a reference to the first worksheet in the Workbook object's
Worksheets collection:
ws = wb.Worksheets(1)
Now we're ready to
insert our data into a range of cells. We'll define a Range of cells that begins at cell A1. We'll call the
Range.Resize() method to resize the Range to fit the number of rows (data.size) and the number of columns (data.first.size) in our data array.
rng = ws.Range('A1').Resize(data.size, data.first.size)
Then we insert our data:
rng.Value = data
Finally, we'll do a wee bit of formatting, making the first row bold...
ws.Rows(1).Font.Bold = true
...and adding AutoFilter drop-down lists to the column headers...
ws.Rows(1).AutoFilter()
There's a variety of other formatting you could apply, as discussed
here.
That's all for now, but feel free to post a comment here or send me email with questions, comments, or suggested topics.
Thanks for stopping by!
