Rails. Exporting Model to Xlsx with checkbox params. Link_to, button_to, sumbit_tag?

Question

I need to export my Model to .xlsx and to submit some parameters (checkbox) for axlsx template.

I have a controller "leads" and custom post action "export", which should invoke my export.xlsx.axlsx template:

def export
  respond_to do |format|
      format.xlsx ## here is invoking my export.xlsx.axlsx
  end
end

In my routes.rb I have the following:

resources :leads do
  collection { post :ipmort }
end

I am trying to implement the following view:

<%= form_tag export_leads_path do %> 
  <%= button_to "Export to Excel", {controller: 'leads', action: "export", 
      remote: true, form: { "data-type" => "xlsx" }} %>
<% end %>

But when I click this button I have got html data format, but not xlsx

Is there any possibility to specify respond_to format in submit_tag, button_to?

I have tried to use <%= link_to %> but then I cannot get my form params..

<%= form_tag export_leads_path do %> 
  <%= link_to "Excel", export_leads_path(format: "xlsx", commit: "Excel"), method: :post %>
<% end %>

Here i haven't lead_ids params:

I also have tried <% submit_tag %>, but result was the same as <%= button_to %>

Please help me, I have spent all the day struggling with this issue and have no thoughts what can I do else.. Let me know if you need any additional information and I will provide it as soon as possible!

Thanks in advance!

Answer 1

I have found solution and here are my tips. Hope it can be helpfull for someone faced with the same issue.

1) Route:

resources :leads do
  collection { post :export }
end

2) Controller action:

 def export
    case params[:commit]
      when "To Excel" then render xlsx: 'export'
      when "To CSV" then send_data @leads.to_csv_modified(@lead_ids), filename: 'export.csv'
      else render action: "index"
    end
  end

3) View:

  <%= form_tag export_leads_path, method: :post do %>
    <%= text_field_tag :created_at_first %>
    <%= text_field_tag :created_at_last%>

    <% Lead.new.attributes.keys[0...Lead.new.attributes.keys.length-1].each_with_index do |field, index| %>
      <%= check_box_tag "lead_ids[]", field, checked = true %>
      <%= field %>
    <% end %>

    <%= submit_tag 'To Excel', controller: 'leads', action: "export" %>
    <%= submit_tag 'To CSV', controller: 'leads', action: "export" %>
  <% end %>

4) Axlsx template (invoking in controller with render xlsx: 'export'):

$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib"

require 'axlsx'

p = Axlsx::Package.new
wb = xlsx_package.workbook
 style_shout = wb.styles.add_style sz: 16, b: true, alignment: { horizontal: :center }
 wb.add_worksheet(name: "#{Time.now.to_date}") do |sheet|
   sheet.add_row params[:lead_ids]
   @leads.each_with_index do |lead, index|
     sheet.add_row params[:lead_ids].map {|e| lead.send(e) }
   end
 end

5) Method to_csv_modified (invoking in controller with send_data @leads.to_csv_modified(@lead_ids), filename: 'import.csv'):

def self.to_csv_modified(lead_ids, options = {})
    CSV.generate(options) do |csv|
      csv << lead_ids
      all.each do |lead|
    csv << lead.attributes.values_at(*lead_ids)
      end
    end
end

If you would like to see how it is working, visit my github conditional_exporting_to_XLSX_with_params

Another useful links: Axlsx_documentation, Railscast #396 - importing csv-and-excel, Railscast #362 - exporting csv-and-excel

Answer 2

use format: "xls"

Also, you can create export.xls.erb. Example is as follows:

<% @headers = ["Id","Name","Email","Grade","Groups","% Total Playlist Videos Watched"] %>
<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
          xmlns:o="urn:schemas-microsoft-com:office:office"
          xmlns:x="urn:schemas-microsoft-com:office:excel"
          xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
          xmlns:html="http://www.w3.org/TR/REC-html40">
  <Worksheet ss:Name="Users">
    <Table>
      <Row>
        <% @headers.each do |column| %>
          <Cell><Data ss:Type="String"><%=column%></Data></Cell>
        <% end %>
      </Row>
      <% @users.each do |user| %>
        <Row>
            <Cell><Data ss:Type="Number"><%= user.id %></Data></Cell>
            <Cell><Data ss:Type="String"><%= user.name %></Data></Cell>
            <Cell><Data ss:Type="String"><%= user.email %></Data></Cell>
            <Cell><Data ss:Type="Number"><%= user.grade %></Data></Cell>
            <Cell><Data ss:Type="String"><%= user.group_name %></Data></Cell>
            <Cell><Data ss:Type="Number"><%= user.videos_seen %></Data></Cell>
        </Row>
      <% end %>
    </Table>
  </Worksheet>
</Workbook>

I hope it helps.