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
CantReadGood Says:
2:45 pmSep 20what a messy code
Jim Says:
2:57 pmSep 20Sorry the formatting engine I used to use stopped working when I upgraded to the latest version of wordpress
Neha Says:
11:37 pmSep 24Hey, 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.
Jim Says:
12:19 amSep 25It’s difficult to diagnose your problem without more information. What does the system tell you in your logs when it fails?
engtech Says:
2:16 pmJan 09@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
stj Says:
6:25 amDec 16I was still not able to open a regular XLS spreadsheet. Still learning the ropes with Ruby and I did try and Google for the error, but no success. I am able to successfully create spreadsheets and write to them, I just cannot read from them. Any help would be appreciated.
require ‘rubygems’
require ’spreadsheet/excel’
include Spreadsheet
data_root=”c:/projects/”
data_file=’report.xls’
file=data_root+data_file
book = Spreadsheet.open file
My error is:
private method `open’ called for Spreadsheet:Module (NoMethodError)
Fernando Says:
8:47 amJan 19Hi,I want to know how can I write a date in date format not as String.
Banthi Says:
4:24 amFeb 02this is not the best method I am unable to format the excel sheet in way I want through this method,the best method is to use WIN32OLE gem to create the excel sheet.
nemeral Says:
8:46 pmJun 24just use this pluginhttp://github.com/xinuc/ekuseru/tree/masterit’s much simple and pretty good.
nemeral Says:
8:47 pmJun 24ups.. sorry, this is the urlhttp://github.com/xinuc/ekuseru/tree/master