Creating Reports on the Desktop

Let's say that your manager at Transmegtech Studios, the fictional game development company we've used for the examples in previous chapters, wants a report on the game players' win/loss records per game in the form of a formatted spreadsheet. Listing 4-1 shows the script to create a simple Excel report.

Listing 4-1. Player Win/Loss Spreadsheet (spreadsheet_team_performance.rb)

require 'active_record'
require 'optparse'
require 'rubygems'
require 'active_record'

ActiveRecord::Base.establish_connection(
  :adapter  => 'mysql',
  :host     => 'localhost',
  :username => 'insert_your_mysql_username_here',
  :password => 'insert_your_mysql_password_here',
  :database => 'players_4')

class Player < ActiveRecord::Base
  has_many :plays
end
class Game < ActiveRecord::Base
  has_many :plays
end
class Play < ActiveRecord::Base
  belongs_to :game
  belongs_to :player
end

require 'spreadsheet/excel'
include Spreadsheet
spreadsheet_file = "spreadsheet_report.xls"
workbook = Excel.new(spreadsheet_file) worksheet = workbook.add_worksheet

page_header_format = Format.new(:color=>'black', :bold=>true, :size=>30)
player_name_format = Format.new(:color=>'black', :bold=>true)
header_format      = Format.new(:color=>'gray', :bold=>true)
data_format        = Format.new()

workbook.add_format(page_header_format) workbook.add_format(player_name_format) workbook.add_format(header_format) workbook.add_format(data_format)

worksheet.format_column(0, 35, data_format)

current_row=0

worksheet.write(current_row, 0, 'Player Win/Loss Report', page_header_format)

current_row=current_row+1

Player.find(:all).each do |player|

  worksheet.format_row(current_row, current_row==1 ? 20 : 33, player_name_format)
  worksheet.write(current_row, 0, player.name)
  current_row=current_row+1

  worksheet.write(current_row, 0, ['Game', 'Wins', 'Losses'], header_format)
  current_row=current_row+1

  Game.find(:all).each do |game|

    win_count = Play.count(:conditions=>[
                 "player_id = ? AND
                  game_id= ? AND
                  won=true",

                  player.id,
                  game.id])

    loss_count = Play.count(:conditions=>[
                 "player_id = ? AND
                  game_id= ? AND
                  won=false",

                  player.id,
                  game.id])

    worksheet.write(current_row, 0, [game.name, win_count, loss_count])
    current_row=current_row+1
  end

end

workbook.close

Save this code as spreadsheet_team_performance.rb. You can run it by issuing the following command:

ruby spreadsheet_team_perfomance.rb

The script creates a file called spreadsheet_report.xls. Open the file with Microsoft Excel or OpenOffice.org, as shown in Figure 4-1.

Figure 4-1.  Player win/loss spreadsheet in OpenOffice.org

Tip  You can use the spreadsheet/excel library to create spreadsheets dynamically in a Rails application, and then send them to the user. This lets you have a "Download this as Excel" link in your views, for example. You can see a scheme for quickly sending binary files in Rails at http://wiki.rubyonrails. org/rails/pages/HowtoSendFilesFast.