Module: ControllerExtensions::ExportToExcel

Defined in:
lib/controller_extensions/export_to_excel.rb

Class Method Summary collapse

Class Method Details

.included(base) ⇒ Object 



3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
 
# File 'lib/controller_extensions/export_to_excel.rb', line 3

def self.included(base)
  base.class_eval do
    private
    require 'spreadsheet'
    #This is method to create excel file with data and send it to user.
    #
    # Example:
    #
    #   class PermissionSetsController < ApplicationController
    #     include ControllerExtensions::ExportToExcel
    #
    #     def export_to_excel
    #       records = User.where(:super_admin => true).all #find record to export
    #       export_to_excel_prototype(records, ["First Name", "Last Name"], %w(first_name last_name))
    #     end
    #   end
    #
    # You can specify file_name to provide name of excel file
    #
    #   export_to_excel_prototype(records, ["First Name", "Last Name"], %w(first_name last_name), :file_name => "Super Admin excel export.xls")
    #
    # You can specify worksheet_name to provide name of worksheet in excel file
    #
    #   export_to_excel_prototype(records, ["First Name", "Last Name"], %w(first_name last_name), :file_name => "Super Admin excel export.xls", :worksheet_name => "super admins")
    #
    def export_to_excel_prototype(records, column_names, model_column_names, options)
      spreadsheet = StringIO.new
      book = create_spreadsheet_for_export(records, column_names, model_column_names, options)
      book.write spreadsheet
      send_data spreadsheet.string, :filename => (options[:file_name].presence || "export_#{Time.now.to_s.underscore}.xls"), :type => "application/vnd.ms-excel"
    end


    #create workbook and write data to it
    def create_spreadsheet_for_export(records, column_names, model_column_names, options = {})
      book = ::Spreadsheet::Workbook.new #create new book for writing
      sheet = book.create_worksheet :name => (options[:worksheet_name].presence || options[:file_name].presence || 'Excel export') #create new sheet in book
      sheet.row(0).concat column_names #write first row(column names)
      records.each_with_index do |record, index|
        write_record_to_sheet(sheet, record, model_column_names, index + 1) #index+1 - begin from second line(first line is column names)
      end

      book
    end

    #write to worksheet one line of data
    def write_record_to_sheet(sheet, record, model_column_names, row_index)
      model_column_names.each_with_index do |name, column_index|
        sheet[row_index, column_index] = record.send(name.underscore.to_sym)
      end
    end
  end
end