Event Markers in Excel

For the last several weeks I have been creating a tool that accepts a set of configuration files and builds a number of charts based on information in those configuration files. For a few reasons I chose to use Ruby and the WIN32OLE support in Excel for staging the data and creating the charts. There may be better Windows solutions to creating charts, but Excel isn’t bad at it and the OLE automation in Ruby makes it fairly easy to drive Excel.

Things moved along nicely and the project was nearing completion when a new feature request came in. The client wanted to add vertical bars to a chart to indicate events that could occur on specified dates. For instance, for a graph that was charting a project’s burndown rate, they wanted to add events that were of importance to the project. The specification looked like something like this:

This presented a bit of a problem as there is no Excel built-in feature that matches this specification. The first idea that came to mind was to use the Excel shape drawing tools to add a line at the appropriate place. This seemed the most straight forward, but meant that I would have to find some way in Excel to convert the X-Axis data values into pixel locations. Looking into the Excel Ole library I found some classes and methods that might make this work, but it didn’t look promising and I searched for a better solution.

Some Googling brought to my attention an Excel charting feature that I hadn’t heard about – customized markers. I was able to create a single sparse series that contained one marker for each event. Each marker was then customized with one-pixel-wide image of the correct height. By setting the marker label and value appropriately in the script I was able to achieve the look the client was looking for. Here is an image of a completed chart:

I visited many web sites and blogs that helped a lot in figuring out the Excel OLE automation methods and strategies, most notably Ruby On Windows. In return I offer up this ruby method that takes the config hash (I load it from a YAML file in the tool) and the Excel OLE objects representing the chart and worksheet and produces the vertical bars at the right places:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
def insert_events(config, chart, sheet, picture_file)
  yAxis = chart.axes(ExcelConst::XlValue)
  marker_value = (yAxis.maximumScale - yAxis.minimumScale) / 2.0
  
  active_cells = sheet.usedRange
  data_column = sheet.columns(active_cells.columns.count + 1)
  date_column = sheet.columns(1)
  row = 1
  first_date = Date.parse(date_column.cells(1).value)
  config.each do |event|
    event_date = Date.parse(event['date'])
    if event_date >= first_date
      while (!date_column.cells(row).value.nil? && Date.parse(date_column.cells(row).value) < event_date)
        row += 1
      end
      
      if !date_column.cells(row).value.nil?
        data_column.cells(row).value = marker_value
        event[:marker_index] = row
      end
    end
  end
  
  series = chart.seriesCollection.add(data_column)
  series.name = 'events'
  series.applyDataLabels
  series.datalabels.position = ExcelConst::XlLabelPositionAbove
  series.markerStyle = ExcelConst::XlMarkerStylePicture
  format = series.format 
  format.line.dashStyle = MsoConst::MsoLineSolid
  format.fill.userPicture(picture_file)
  markers = series.points
  current_color = nil
  config.each do |event|
    if event[:marker_index]
      marker = markers.item(event[:marker_index])
      current_color = event[:color] if event[:color]
      marker.dataLabel.text = event['name'] if event['name']
      marker.markerForegroundColor = color if current_color
    end
  end
end