DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

Generate Excel File With Stand-alone Script

10.06.2006
| 24770 views |
  • submit to reddit
        I needed to grab a days records from MySQL, generate an Excel spreadsheet and then email it as an attachment. The sales_lead script is to be run by a cron. This requires the 'spreadsheet-excel' gem. (gem install spreadsheet-excel)

OrderMailer model

class OrderMailer < ActionMailer::Base
  def sales_leads(to, cc, site, city)
    @recipients      = to
    @cc              = cc
    @subject         = "#{site} (#{city}) sales leads for #{1.day.ago.to_date} to #{Date.today}" 
    @from            = "sales@#{site}.com" 
    @body            = {}

    attachment "application/vnd-ms-excel" do |a|
      a.filename = "#{site}_#{city}_sales_leads.xls" 
      a.body = File.read(RAILS_ROOT + "/public/reports/#{site}_#{city}_sales_leads.xls")
    end
  end
end

script/sales_leads
#!/usr/bin/env ruby

RAILS_ENV = 'production'

require File.dirname(__FILE__) + '/../config/environment'
require "spreadsheet/excel" 

@operators = Operator.find(:all)
@operators.each do |operator|
  @orders = Order.find(:all, 
    :conditions => [ "operator_id = ? AND created_at #{(1.day.ago.to_date..Date.today).to_s(:db)}", operator.id ])

  file = "#{operator.site}_#{operator.city}_sales_leads.xls" 
  workbook = Spreadsheet::Excel.new("#{RAILS_ROOT}/public/reports/#{file}")

  worksheet = workbook.add_worksheet("Sales Leads")
  worksheet.write(0, 0, "Customer name")
  worksheet.write(0, 1, "Email")
  worksheet.write(0, 2, "Service street address")
  worksheet.write(0, 3, "City")
  worksheet.write(0, 4, "State")
  worksheet.write(0, 5, "Zip")
  worksheet.write(0, 6, "Home phone")
  worksheet.write(0, 7, "Daytime phone")
  worksheet.write(0, 8, "Cable Package")
  worksheet.write(0, 9, "Num. of TV's")
  worksheet.write(0, 10, "Num. dig A/O")
  worksheet.write(0, 11, "Num. HD/DVR")
  worksheet.write(0, 12, "HD only")
  worksheet.write(0, 13, "Installation date")
  worksheet.write(0, 14, "Installation time")
  worksheet.write(0, 15, "Alternate installation date")
  worksheet.write(0, 16, "Alternate installation time")
  worksheet.write(0, 17, "Order num")
  worksheet.write(0, 18, "Comments")

  row = 1
  @orders.each do |order|
    worksheet.write(row, 0, "#{order.customer.first_name} #{order.customer.last_name}")
    worksheet.write(row, 1, "#{order.customer.email}")
    worksheet.write(row, 2, "#{order.customer.addresses.first.address}")
    worksheet.write(row, 3, "#{order.customer.addresses.first.city}")
    worksheet.write(row, 4, "#{order.customer.addresses.first.state}")
    worksheet.write(row, 5, "#{order.customer.addresses.first.zip}")
    worksheet.write(row, 6, "#{order.customer.home_phone}")
    worksheet.write(row, 7, "#{order.customer.daytime_phone}")
    worksheet.write(row, 8, "#{order.package.name}")
    worksheet.write(row, 9, "#{order.tv_sets}")
    worksheet.write(row, 10, "#{order.digital_boxes}")
    worksheet.write(row, 11, "#{order.dvr_boxes}")
    worksheet.write(row, 12, "#{order.own_hdtv_set}")
    worksheet.write(row, 13, "#{order.operator_order.installation_date}")
    worksheet.write(row, 14, "#{order.operator_order.installation_time}")
    worksheet.write(row, 15, "#{order.operator_order.alternate_installation_date}")
    worksheet.write(row, 16, "#{order.operator_order.alternate_installation_time}")
    worksheet.write(row, 17, "#{order.id}")
    worksheet.write(row, 18, "#{order.operator_order.special_instructions}")
    row += 1
  end

  workbook.close

  OrderMailer.deliver_sales_leads(operator.report_email_address_to, operator.report_email_address_cc, operator.site, operator.city)
end
    

Comments

Gabriel Falkenberg replied on Wed, 2008/12/10 - 6:56pm

The above code is a tad old. Requiring spreadsheet/excel will yield a warning. This is how the spreadsheet API looks now: require 'spreadsheet' workbook = Spreadsheet::Workbook.new() worksheet = workbook.create_worksheet() worksheet[0, 0] = "Hello, World!" workbook.write("test.xls")