Reporting in Excel with Rails

At my startup, we recently have run into a problem. The deal is, our customers have large amounts of data stored on our servers, and they want a large array of reports to access it. That's no big deal in and of itself, but the problem is that in some cases they want a report that spans a years worth of data (around 1,000,000 records of data), and they want to be able to sort, arrange, perform calculations on, and graph all of these data points. So my first thought was that we were going to be in for a lot of really unpleasent javascripting, but it turns out there is a better way. What could we leverage that already can take in data and do all those things that I mentioned above? Spreadsheet programs, of course, Microsoft Excel being the most common (I use OpenOffice myself, but it can open .xls files too). So I went hunting for a library to help me produce Excel files. As it so happens, there's already a great library in the works called (unsuprisingly) spreadsheet. It's available on rubyforge as a Rails plugin, and for me it's getting the job done. [NOTE: Requires "ruby-ole" gem]

So here's what I started with, after reading the docs for the spreadsheet library. Basically this is the creation of one report:

require 'spreadsheet'

class Reporter
  
public

  class << self

    def report_all_transactions(district)
      book = Spreadsheet::Workbook.new
      sheet = book.create_worksheet
      sheet.row(1).concat ["ALL TRANSACTIONS BY THERAPIST"]
      index = 2
      district.therapists.each do |t|
        sheet.row(index).concat [t.full_name]
        index += 1
        sheet.row(index).concat ["ID","Date","Start",
                           "Stop","Status","Location"]
        index += 1
        district.transactions.each do |trans|
            sheet.row(index).concat [trans.id,trans.date,
                              trans.start,trans.stop,
                              trans.status,trans.school.name]
            index += 1
        end
        index += 2
      end
      book.write "#{RAILS_ROOT}/public/data/all_transactions_report.xls"
    end

  end
end

It's not bad on the whole, but it's a little wordy, and the one thing that REALLY bothered me about it was that index tracking I was having to do and the repetitive nature of the whole "add some data, increment the index" thing.

So, in order to make this spreadsheet library more pleasant to work with, I created the class below:

class SheetWrapper
  
  attr_accessor :index
  
  def initialize(worksheet)
    @sheet = worksheet
    @index = 0
  end

  def add_row(array)
    @sheet.row(@index).concat array
    @index += 1
  end 
  
  def add_lines(count)
    @index += count
  end 
end

It's pretty simple, just a couple methods, but look what it does to that first code example I showed:

require 'spreadsheet'

class Reporter
  
public

  class << self

    def report_all_transactions_by_date(district)
      book = Spreadsheet::Workbook.new
      sheet = SheetWrapper.new(book.create_worksheet)
      sheet.add_row ["ALL TRANSACTIONS BY THERAPIST"]
      sheet.add_lines(1)
      district.therapists.each do |t|
        sheet.add_row [t.full_name]
        sheet.add_row ["ID","Date","Start",
                       "Stop","Status","Location"]
        district.transactions.each do |trans|
            sheet.add_row [trans.id,trans.date,
                      trans.start,trans.stop,
                      trans.status,trans.school.name]
        end
        sheet.add_lines(2)
      end
      book.write "#{RAILS_ROOT}/public/data/all_transactions_report.xls"
    end

  end
end

Better already. One more problem that I had, though, was that all my report methods were kind of following the same pattern of opening a work book, adding some data, and then writing it out to the data directory. In order to reduce that pattern down to one chunk of reusable code, I extracted the "run_report" method as follows:

require 'spreadsheet'

class Reporter
  
public

  class << self

    def report_all_transactions_by_date(district)
      filename = "all_transactions"
      self.run_report(filename,"ALL TRANSACTIONS") do |sheet|
        district.therapists.each do |t|
          sheet.add_row [t.full_name]
          sheet.add_row ["ID","Date","Start","Stop","Status","Location"]
          district.transactions.each do |trans|
            sheet.add_row [trans.id,trans.date,
                              trans.start,trans.stop,
                              trans.status,trans.school.name]
          end
          sheet.add_lines 2
        end
      end
    end

  end

protected
  class << self

    def run_report(file_name,title,header_row = nil)
      name = "#{file_name}.xls"
      book = Spreadsheet::Workbook.new
      sheet = SheetWrapper.new(book.create_worksheet)
      sheet.add_row [title]
      sheet.index = 2
      sheet.add_row header_row if header_row
      yield(sheet)
      book.write "#{RAILS_ROOT}/public/data/#{name}"
    end

  end
end

And that's pretty much it. Now all the large reports can be generated as Excel files, and the users are happy because they can play with that data anyway they need to in order to extract the information they're looking for (and we didn't have to spend a month writing features that spreadsheet programs already have into our web app).