Follow me on Twitter!

Dont Trust This Guy why not take my word for it? A blog by Jim Jeffers

Creating Excel Documents with Ruby On Rails

Generating an excel document is not so difficult to achieve but limited unfortunately. You cannot write formulas or generate charts via the existing available tools but you can achieve usable results overall. Here is a snippet I used to generate sales forecasts involving data pulled together from two active record objects. Here is the gem you’ll need.

  • Update: the link I was posting to prior points to a new Ruby project. The correct link can be found here.
  •   # Builds an excel report.
      def report
        # Grab time span for report
        get_span
    
        # Define stats levels to include.
        status = %w(high medium low lost won)
    
        # Create workbook.
        file = "#{session[:user_id]}_#{Time.now.strftime("%m%d%G%s")}_forecast.xls"
        workbook = Excel.new("#{RAILS_ROOT}/reports/#{file}")
    
        heading = Format.new(
           :color     => "green",
           :bold      => true,
           :underline => true
        )
    
        data = Format.new(
           :color     => "black",
           :bold      => false,
           :underline => false
        )
    
        workbook.add_format(heading)
        workbook.add_format(data)
    
        # Cycle through each status level
        status.each do |status|
          start_column, start_row = 2, 3
          worksheet = workbook.add_worksheet(status)
          opportunities = get_opportunities_that_are(status)
    
          #Cycle through the opportunities
          row = start_row
          totals, dates = [], []
    
          for opp in opportunities
            worksheet.write(row,start_column,opp.client,heading)
    
            column = start_column + 1
            opp.find_forecasts_within(@span[0],@span[-1]).each do |i|
              worksheet.write(row,column,i.volume,data)
              totals[column] = i.volume + totals[column].to_i
              dates[column] = i.date.strftime("%b '%y")
              column += 1
            end    
    
            row += 1
          end
    
          # Generate the totals row and monthly headings
          column = start_column+1
          @span.length.times do
            worksheet.write(row,column,totals[column],heading)
            worksheet.write(start_row-1,column,dates[column],heading)
            column += 1
          end
    
        end
    
        workbook.close
        redirect_to :action => 'show'
      end
    

6 Responses to This Article.

  1. CantReadGood Says:

    what a messy code

  2. Jim Says:

    Sorry the formatting engine I used to use stopped working when I upgraded to the latest version of wordpress :(

  3. Neha Says:

    Hey, can u tell me what all dependencies to include as i am into a similar situation where i have to generate an excel file and save it somewhere and i dont want to use views for doing that. I tried your example and have also installed the gem parseexcel 0.5.2 but my application is failing at Excel.new(’C:\example.xls’) . Please help.

  4. Jim Says:

    It’s difficult to diagnose your problem without more information. What does the system tell you in your logs when it fails?

  5. engtech Says:

    @neha: this code uses Spreadsheet::Excel, not Parseexcel

    @jim: The link in your example goes to two different excel gems (I think it changed on you)

    gem install spreadsheet-excel

    require ‘rubygems’
    require ’spreadsheet/excel’
    include Spreadsheet

  6. Amit Says:

    Hi can u tell me how to write a code to clean the square images which appears in the excel sheet.
    @bill = @workbook.Worksheet.Clean(@bill)
    The above code it shows an error that Clean is not a valid method.
    Can u help it out

Leave a Reply

Meta Information

This post was filed under code and tagged with: , , , .

This Post as a Feed

The content of this post and it's comments can be subscribed to as an RSS feed.

DontTrustThisGuy.com and all contents copyright 2003-2007 by Jim Jeffers, unless otherwise noted.Written in valid XHTML and a participant of XFN while being powered by WordPress
Contents under Creative Commons License. Visual design, layout and Cascading Style Sheets may not be reused without permission.
Entries (RSS) and Comments (RSS)