rails导出excel表单

spreadsheet官网
本地gem下载
本地plugin下载

关于，ｅｘｃｅｌ的ｒｕｂｙ处理，

如果，你想看更复杂的例子， 请点击这里
如果，你想看更简单的解析excel文件的例子，请点击这里
如果，你想了解windows下操作excel的特有方法，请点击这里
如果，你想考虑另外的插件roo来操作excel，请点击这里
如果，你还想考虑rails的一个插件railsxls，请点击这里

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

# 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  

另一个例子：

def export_excel
	if @request.env['HTTP_USER_AGENT'] =~ /msie/i
		@headers['Pragma'] = ''
		@headers['Cache-Control'] = ''
	else
		@headers['Pragma'] = 'no-cache'
		@headers['Cache-Control'] = 'no-cache, must-revalidate'
	end

	@employee_id = @params["employee_id"]
	@geotag_id = @params["geotag_id"]
	firm_id = @session[:user].id
	corrected_server_date = (Time.now).strftime('%Y-%m-%d 00:00:00')
	@time_entries = TimeEntry.find(:all, :conditions =>["time_entries.firm_id = 
? AND employee_id like ? and geotag_id like ?", firm_id, 
"%#{@employee_id}%", "%#{@geotag_id}%"], :order => "time_entries.start_time 
DESC", :include=>[:employee,:geotag])


	wb = Excel.new("test/timesheets.xls")
	version = Excel::VERSION

	# Preferred way to add a format
	f1 = wb.add_format(:color=>"black",:bold=>1,:italic=>true)

	f4 = Format.new(:num_format => "d mmm yyyy")
	f5 = Format.new(:num_format => 0x0f)
	wb.add_format(f4)
	wb.add_format(f5)

	ws1 = wb.add_worksheet("timesheets")
	#headers
	@header = ['Employee','Address','Zip','City','Duration','Start','Stop']
	#headers afprinten op de 1ste rij
	0.upto(@header.length - 1) do |i|
		ws1.write(0,i, at header[i], f1)
	end

	rij = 1 #de gegevens worden getoond vanaf de 2de rij
	#time entries afprinten
	 for time_entry in @time_entries
		ws1.write(rij,0,time_entry.employee.last_name + " " + 
time_entry.employee.first_name)
		ws1.write(rij,1,time_entry.geotag.address1)
		ws1.write(rij,2,time_entry.geotag.zip)
		ws1.write(rij,3,time_entry.geotag.city)

		if time_entry.stop_time.nil?
			ws1.write(rij,4,"")
			ws1.write(rij,5,time_entry.start_time.strftime("%d/%m/%Y %H:%M"))
			ws1.write(rij,6,"")
		else
			ws1.write(rij,4,time_entry.duration)
			ws1.write(rij,5,time_entry.start_time.strftime("%d/%m/%Y %H:%M"))
			ws1.write(rij,6,time_entry.stop_time.strftime("%d/%m/%Y %H:%M"))
		end

		rij = rij + 1
	 end
	ws1.format_column(0..1,20,f1)
	ws1.format_column(2,5,f1)
	ws1.format_column(3..4,10,f1)
	ws1.format_column(5..6,15,f1)
	wb.close

	redirect_to :action=>"list_times"
end

评论

1 楼 乌龙饭 2012-12-03  

请教个问题，这个format能否设置单元格的背景色？