CODEWITHSUNDEEP
ruby-on-railsruby
Mahesh Mesta
Mahesh Mesta

Reputation: 783

How to display a column of a table associated to another table while exporting data to excel sheet?

I am trying to export data from documents table to spreadsheet using spreadsheet and to_xls gem. I am following this tutorial:

http://code.dblock.org/2011/09/01/exporting-data-to-excel-in-ruby-on-rails-w-spreadsheet-and-toxls.html

Now a service has_many: documents document belongs_to: service

The service table has a column called name. So instead of displaying service_id on my excel sheet , I want to display service.name

documents/views/index.html.erb

<table id='myDocumentsTable' class="table table-bordered">
<thead>
<tr>
  <th>Name</th>
  <th>Service</th>
  <th>Tat</th>
  <th>Automated</th>
  <th>Price</th>
  <th colspan="3"></th>
</tr>
</thead>

<tbody>
<% @documents.each do |document| %>
  <tr>
    <td><%= document.name %></td> 
    <td><%= document.service.name %></td>
    <td><%= document.tat %></td>
    <td><%= document.automated %></td>
    <td><%= document.default_price %></td>
    <td><%= link_to 'Show', document %></td>
    <td><%= link_to 'Edit', edit_document_path(document) %></td>
    <td><%= link_to 'Destroy', document, method: :delete, data: {    
    confirm: 'Are you sure?' } %></td>
  </tr>
 <% end %>
 </tbody>
 </table>

<br>
<%= will_paginate @documents %>
<%= link_to 'New Document', new_document_path, class: 'new_doc' %> 
<%= link_to 'Export', documents_path(request.parameters.merge({:format 
=>   :xls})), :class =>"btn btn-primary" %>

documents_controller.rb

def index
@documents = Document.all.paginate(:page => params[:page], :per_page => 
30).order('name ASC')
respond_to do |format|  
  format.html
  format.xls { send_data Document.all.select([:name, :service_id , :tat, 
 :automated, :default_price]).to_xls, content_type: 'application/vnd.ms-
  excel', filename: 'documents.xls' }
end
end

I want to display the exact contents of index.html.erb into an excel sheet where the service column should contain the service.name not the service_id. How can I achieve this? Please help!

Upvotes: 0

Views: 303

Answers (1)

Deepak Mahakale
Deepak Mahakale

Reputation: 23671

You can join the service and use select to get specific attributes from services table

You can use service.name as service_name which will return you service name 

def index
  @documents = 
    Document.all.paginate(page: params[:page], per_page: 30).order('name ASC')
  respond_to do |format|  
    format.html
    format.xls do
      send_data(
        Document.joins(:service)
          .select('documents.name, services.name as service_name,
                   documents.tat, documents.automated, 
                   documents.default_price'
                 ).to_xls, 
        content_type: 'application/vnd.ms-excel',
        filename: 'documents.xls' 
      )
    end
  end
end

Upvotes: 1

Related Questions