Creating Reports on the Desktop

The first few lines of Listing 4-1 create a connection to your database and set up your models, similar to the code in the previous examples. Next, you start creating the Excel report using the spreadsheet-excel gem:

require 'spreadsheet/excel'
include Spreadsheet

workbook = Excel.new("spreadsheet_report.xls")
worksheet = workbook.add_worksheet

The first line loads the spreadsheet/excel library, and the second line mixes the spreadsheet/excel code into your current module so you can use it. Next, you create a new Excel document, which is called a worksheet, and then add a new worksheet to the workbook. The worksheet will contain your report.

Note  You can add more than one worksheet to a workbook if desired. Then your end users will be able to select between them using tabs at the bottom of their spreadsheet application.

Next, you set up some graphical formatting for your data:

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(:color=>'black', :bold=>false)

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)

This code creates a number of formats and adds them to the workbook. You need to add them before you can use them later, because formats are specific to the entire document and then referenced when used. Next, you proceed to format the first column using the format_column method, passing three parameters:

1. The first parameter specifies which column to format; in this case, column 0, or the first column.
   
2. The second parameter specifies the width of the column; in this case, 35, which is wide enough to display all of the data in that column. 
    
3. The third parameter is a display format for the column; in this case, data_format.

An astute observer may notice that the data_format format contains no specific formatting, so it is not any different from an unformatted cell. This is intentional;
the format_column method can, in theory, set just a column width without setting a format, but due to a bug, the library will crash if a format is not specified. (This will likely be fixed in a future release; as of this writing, the current version of the spreadsheet gem is 0.3.5.1.) However, by calling format_column with both a width and a dummy format, you can achieve the desired effect of widening the column without changing the display format.

Next, the script begins to write information to the spreadsheet. It begins with a header stating the title of the spreadsheet:

current_row= 0

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

The first line initializes a variable, current_row, which will store the current row in the spreadsheet. The second line uses the write method of the worksheet object to write "Player Win/Loss Report" using the page_header_format format, which has a large, bold font. The worksheet.write method takes a row number, a column number, a value, and an optional format. (Note that the code uses the current_row variable only for consistency with later code, since it will always equal zero at this point.)

Next, you start looping through all of the players, first outputting a brief name and header for each player:

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

The second line uses the format_row method, which is much like the format_column method, except that it formats rows rather than columns. You format the first row, giving it a special format defined earlier, player_name_format, and giving it a variable height, depending on whether it's the second row or another row. The second row is the row immediately following the page header (which is in a font twice the size of the data rows), and it looks better with less vertical space.

Next, you write the player name to the first column of your current row and increment your current_row counter.

Note  The need to increment the current_row counter gets somewhat tedious. If it really irritates you, you could create an object that simulates a stream object fairly easily, which would let the object keep track of this for you. Whether this is helpful depends on your application, and such an approach would not necessarily help applications with a complicated layout.

After that, you write a brief header describing your data for each player. This call is interesting, since it passes an array to the write method of your worksheet object. This array is expanded into the cells to the right of the cell, so these two blocks of code are the same:

# This:

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

# ... Is just like this:

worksheet.write(current_row, 0, 'Game', header_format)
worksheet.write(current_row, 1, 'Wins', header_format)
worksheet.write(current_row, 2, 'Losses', header_format)

Additionally, the write method can accept a format, just like the format_column and format_row methods. This can be used to highlight just one cell of a row or column. (In this particular case, you are formatting the entire row, so you could use the format_row method.)

Next, you loop through all of the games, tally the players' wins and losses for each game, and write them to the spreadsheet:

  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

The two calls to the Active Record count method (discussed in Chapter 2) count the wins and losses, respectively. You then use the worksheet object's write method to put the information into the spreadsheet, and continue on to the next game. After all of the games have been reported, you continue on to the next player. After all of the players have been reported, you close the workbook, and you're finished:

workbook.close

As you can see, the spreadsheet/Excel interface is reasonably clean and easy to use . But if you need a more powerful, desktop GUI solution, there's a graphical interface library that is just as clean and easy to use.

Please check back tomorrow for the conclusion to this article.