Using Gem 'wicked_pdf' to Generate PDF FIle in Rails
Ok, it ‘s turn for PDF file in Rails.
The wicked_pdf is the best gem I have seen for generating pdf file. It is powerful, versatile and flexible. It also provides the capability of generate PDF from HTML file. This is just what I want.
Thewicked_pdf utilizing the shell utility wkhtmltopdf to serve a PDF file to a user from HTML.
There are also many options which provides huge flexibility to users to customize the output PDF file.
Installation
put it in Gemfile
gem 'wicked_pdf'
Likewise, bundle install. Then, execute the initializer.
$rails generate wicked_pdf
It will create app/config/initializers/wicked_pdf.rb. Normally, nothing has to do with it if higher version of Rails was used. (My Rails version is 5)
Because wicked_pdf is a wrapper for wkhtmltopdf, you’ll need to install that in Gemfile, too.
gem 'wkhtmltopdf-binary'
Of course, then bundle install.
Usage
1.Put the code in the response_tosection of the controller. Like:
def show
@orderdetails = @order.orderdetails.includes(:model, :size, :color)
respond_to do |format|
fn = "order_#{@order.po_number}_#{Time.now.strftime("%Y-%m-%d%H:%M:%S")}"format.pdf { render pdf: fn, disposition: 'attachment'}
end
end
2.Copy the show.html.erb to a new file and rename it as show.pdf.erb.
3. Remove all the link_to tags in the file to prevent them from showing in the pdf file.
It’s done!
Further reading: How To Create PDFs in Rails
Using Gem 'to_spreadsheet' to Generate XLSX FIle in Rails
Now, let’s find out how to generate Excel file in Rails.
After searching many gems, I found that axlsx is a very well-designed for my purpose. However, it generates xlsx file by building up the components piece by piece. I need a quick solution which can generates a xlsx file from html file. Then I found a gem called to_spreadsheet that is totally full-filled my needs!
Actually, to_spreadsheet is a wrapper of axlsx. It simplifies the usage and allows us using the existing view template to generate xlsx file.
Installation
Put it in Gemfile.
gem 'to_spreadsheet'
Likewise, bundle install.
Usage
1.Put the code in the response_tosection of the controller. Like:
def show
@orderdetails = @order.orderdetails.includes(:model, :size, :color)
respond_to do |format|
fn = "order_#{@order.po_number}_#{Time.now.strftime("%Y-%m-%d%H:%M:%S")}"format.xlsx { render xlsx: :show, filename: fn }
end
end
2.Copy the show.html.erb to a new file and rename it as show.xlsx.erb.
3. Remove all the link_to tags in the file to prevent them from showing in the xlsx file.
4. Try to put the data you need to show in <table></table> block.
First, let’s create a new directory ‘services’ under ‘app/’.
$cd app
$mkdir services
$cd services
Create a New File ‘app/services/document_service.rb’
Second, add a new file called ‘document_service.rb’.
Create a New Service Object called ‘DocumentService’
Third, move the code in private action query_to_csv of the orders_controller to the new service object. Now, we call this service object as DocumentService.
classDocumentServicedefinitialize(order_id)
@order_id = order_id
end# Use ActiveRecord::Base.connection.exec_query to query db directly only once.defto_csv
query_string = 'select a.po_number as "PO Number", c.name as "Model Name", d.name as Color,
e.name as Size, b.price as Price, b.quantity as Quantity,
(b.price*b.quantity) as "Total Amount"
from orders a, orderdetails b, models c, colors d, sizes e
where a.id = b.order_id and b.model_id=c.id and b.color_id=d.id and b.size_id=e.id and b.order_id=' + @order_id.to_s
results = ActiveRecord::Base.connection.exec_query(query_string)
CSV.generate(headers:true) do |csv|
csv << results.columns
results.rows.each do |row|
csv << row
endendendend
Modify The Show Action
Four, modify the show action and delete private action query_to_csv.
If there are many associated models needed to be retrieved during a query, it’s time to use ‘includes’ instead of nothing. It will increase your apps performance by reducing the number of queries to database.
Here is the scenario:
Order has_many Orderdetails
Without ‘includes’
The code:
@orderdetails = @order.orderdetails
It generates the log below.
Started GET “/orders/1” for ::1 at 2016-10-10 04:09:07 +0800
Processing by OrdersController#show as HTML
Parameters: {“id”=>”1”}
Order Load (0.3ms) SELECT “orders”.* FROM “orders” WHERE “orders”.”id” = ? LIMIT ? [[“id”, 1], [“LIMIT”, 1]]
Rendering orders/show.html.erb within layouts/application
Orderdetail Load (0.6ms) SELECT “orderdetails”.* FROM “orderdetails” WHERE “orderdetails”.”order_id” = ? ORDER BY model_id, size_id ASC [[“order_id”, 1]]
Model Load (0.4ms) SELECT “models”.* FROM “models” WHERE “models”.”id” = ? LIMIT ? [[“id”, 1], [“LIMIT”, 1]]
Size Load (0.4ms) SELECT “sizes”.* FROM “sizes” WHERE “sizes”.”id” = ? ORDER BY name ASC LIMIT ? [[“id”, 1], [“LIMIT”, 1]]
Color Load (0.6ms) SELECT “colors”.* FROM “colors” WHERE “colors”.”id” = ? ORDER BY name ASC LIMIT ? [[“id”, 4], [“LIMIT”, 1]]
CACHE (0.0ms) SELECT “models”.* FROM “models” WHERE “models”.”id” = ? LIMIT ? [[“id”, 1], [“LIMIT”, 1]]
CACHE (0.0ms) SELECT “sizes”.* FROM “sizes” WHERE “sizes”.”id” = ? ORDER BY name ASC LIMIT ? [[“id”, 1], [“LIMIT”, 1]]
Color Load (0.2ms) SELECT “colors”.* FROM “colors” WHERE “colors”.”id” = ? ORDER BY name ASC LIMIT ? [[“id”, 2], [“LIMIT”, 1]]
CACHE (0.0ms) SELECT “models”.* FROM “models” WHERE “models”.”id” = ? LIMIT ? [[“id”, 1], [“LIMIT”, 1]]
Size Load (0.1ms) SELECT “sizes”.* FROM “sizes” WHERE “sizes”.”id” = ? ORDER BY name ASC LIMIT ? [[“id”, 2], [“LIMIT”, 1]]
CACHE (0.0ms) SELECT “colors”.* FROM “colors” WHERE “colors”.”id” = ? ORDER BY name ASC LIMIT ? [[“id”, 4], [“LIMIT”, 1]]
CACHE (0.0ms) SELECT “models”.* FROM “models” WHERE “models”.”id” = ? LIMIT ? [[“id”, 1], [“LIMIT”, 1]]
CACHE (0.0ms) SELECT “sizes”.* FROM “sizes” WHERE “sizes”.”id” = ? ORDER BY name ASC LIMIT ? [[“id”, 2], [“LIMIT”, 1]]
CACHE (0.0ms) SELECT “colors”.* FROM “colors” WHERE “colors”.”id” = ? ORDER BY name ASC LIMIT ? [[“id”, 2], [“LIMIT”, 1]]
Rendered orders/show.html.erb within layouts/application (105.4ms)
Rendered common/_navbar.html.erb (1.1ms)
Completed 200 OK in 275ms (Views: 236.7ms | ActiveRecord: 5.1ms)
Started GET “/orders/1” for ::1 at 2016-10-10 14:26:43 +0800
ActiveRecord::SchemaMigration Load (0.2ms) SELECT “schema_migrations”.* FROM “schema_migrations”
Processing by OrdersController#show as HTML
Parameters: {“id”=>”1”}
Order Load (0.2ms) SELECT “orders”.* FROM “orders” WHERE “orders”.”id” = ? LIMIT ? [[“id”, 1], [“LIMIT”, 1]]
Rendering orders/show.html.erb within layouts/application
Orderdetail Load (0.2ms) SELECT “orderdetails”.* FROM “orderdetails” WHERE “orderdetails”.”order_id” = ? ORDER BY model_id, size_id ASC [[“order_id”, 1]]
Model Load (0.1ms) SELECT “models”.* FROM “models” WHERE “models”.”id” = 1
Size Load (0.2ms) SELECT “sizes”.* FROM “sizes” WHERE “sizes”.”id” IN (1, 2) ORDER BY name ASC
Color Load (0.2ms) SELECT “colors”.* FROM “colors” WHERE “colors”.”id” IN (4, 2) ORDER BY name ASC
Orderdetail Load (0.2ms) SELECT “orderdetails”.* FROM “orderdetails” WHERE “orderdetails”.”order_id” = ? ORDER BY model_id, size_id ASC [[“order_id”, 1]]
Rendered orders/show.html.erb within layouts/application (84.2ms)
Rendered common/_navbar.html.erb (1.9ms)
Completed 200 OK in 617ms (Views: 560.0ms | ActiveRecord: 3.4ms)
It takes 6 queries and 3.4ms.
The includes method can minimize the queries to the database. Rails generates a single query each time it need the associated model by default. The ‘includes’ method, however, aggregates the queries to the same associated model .
For example, If there are four order details belong to order id ‘1’, Rails generates 12 ( 4 records x 3 associated models ) queries by default. On the other hand, it only takes 3 queries by using the ‘includes’ method.
Conclusion
Add ‘includes’ method for the query with many associated models.
Using Ruby CSV Library to Export CSV File in Rails Apps - send_data method and 'exec_query' Approach
N+1 Queries Problem
Since there are 4 relations (order, model, color, size) for model Orderdetail, it causes ‘N+1 Query’ problems for this scenario. Let’s take a look at the console log:
Started GET “/orders/1.csv” for ::1 at 2016-10-08 12:54:14 +0800
ActiveRecord::SchemaMigration Load (0.2ms) SELECT “schema_migrations”.* FROM “schema_migrations”
Processing by OrdersController#show as CSV
Parameters: {“id”=>”1”}
Order Load (0.2ms) SELECT “orders”.* FROM “orders” WHERE “orders”.”id” = ? LIMIT ? [[“id”, 1], [“LIMIT”, 1]]
Orderdetail Load (0.2ms) SELECT “orderdetails”.* FROM “orderdetails” WHERE “orderdetails”.”order_id” = ? ORDER BY model_id, size_id ASC [[“order_id”, 1]]
Order Load (0.1ms) SELECT “orders”.* FROM “orders” WHERE “orders”.”id” = ? LIMIT ? [[“id”, 1], [“LIMIT”, 1]]
Model Load (0.1ms) SELECT “models”.* FROM “models” WHERE “models”.”id” = ? LIMIT ? [[“id”, 1], [“LIMIT”, 1]]
Size Load (0.1ms) SELECT “sizes”.* FROM “sizes” WHERE “sizes”.”id” = ? ORDER BY name ASC LIMIT ? [[“id”, 1], [“LIMIT”, 1]]
Color Load (0.3ms) SELECT “colors”.* FROM “colors” WHERE “colors”.”id” = ? ORDER BY name ASC LIMIT ? [[“id”, 4], [“LIMIT”, 1]]
Order Load (0.1ms) SELECT “orders”.* FROM “orders” WHERE “orders”.”id” = ? LIMIT ? [[“id”, 1], [“LIMIT”, 1]]
Model Load (0.1ms) SELECT “models”.* FROM “models” WHERE “models”.”id” = ? LIMIT ? [[“id”, 1], [“LIMIT”, 1]]
Size Load (0.1ms) SELECT “sizes”.* FROM “sizes” WHERE “sizes”.”id” = ? ORDER BY name ASC LIMIT ? [[“id”, 1], [“LIMIT”, 1]]
Color Load (0.1ms) SELECT “colors”.* FROM “colors” WHERE “colors”.”id” = ? ORDER BY name ASC LIMIT ? [[“id”, 2], [“LIMIT”, 1]]
Order Load (0.1ms) SELECT “orders”.* FROM “orders” WHERE “orders”.”id” = ? LIMIT ? [[“id”, 1], [“LIMIT”, 1]]
Model Load (0.1ms) SELECT “models”.* FROM “models” WHERE “models”.”id” = ? LIMIT ? [[“id”, 1], [“LIMIT”, 1]]
Size Load (0.1ms) SELECT “sizes”.* FROM “sizes” WHERE “sizes”.”id” = ? ORDER BY name ASC LIMIT ? [[“id”, 2], [“LIMIT”, 1]]
Color Load (0.2ms) SELECT “colors”.* FROM “colors” WHERE “colors”.”id” = ? ORDER BY name ASC LIMIT ? [[“id”, 4], [“LIMIT”, 1]]
Order Load (0.1ms) SELECT “orders”.* FROM “orders” WHERE “orders”.”id” = ? LIMIT ? [[“id”, 1], [“LIMIT”, 1]]
Model Load (0.1ms) SELECT “models”.* FROM “models” WHERE “models”.”id” = ? LIMIT ? [[“id”, 1], [“LIMIT”, 1]]
Size Load (0.1ms) SELECT “sizes”.* FROM “sizes” WHERE “sizes”.”id” = ? ORDER BY name ASC LIMIT ? [[“id”, 2], [“LIMIT”, 1]]
Color Load (0.1ms) SELECT “colors”.* FROM “colors” WHERE “colors”.”id” = ? ORDER BY name ASC LIMIT ? [[“id”, 2], [“LIMIT”, 1]]
Rendering text template
Rendered text template (0.0ms)
Sent data order_1234_2016-10-08 12:54:14.csv (14.0ms)
Completed 200 OK in 149ms (Views: 13.8ms | ActiveRecord: 4.7ms)
There are four order details for order 1, it takes 19 queries for 4.7ms. We can conclude that:
query number = ( number of order details X 4 ) + 3
Thus, the more order details contained in one order, the more query time used.
Direct SQL Query
Is there a way to solve this problem? What if we just use one single query to get all the data we need? Here is my plan. I wrote a single SQL statement which can pull out all the data from the database:
select a.po_number as ‘PO Number’, c.name as ‘Model Name’,
d.name as ‘Color’, e.name as ‘Size’, b.price as ‘Price’, b.quantity as Quantity (b.price*b.quantity) as ‘Total Amount’
from orders a, orderdetails b, models c, colors d, sizes e
where a.id = b.order_id and b.model_id=c.id and b.color_id=d.id and b.size_id=e.id and b.order_id=a.id
I tested it through a standalone database client and it returned the correct result. Then, I called ‘“exec_query” method to do the one-query action in the orders controller. The class used is:
The result set is an ActiveRecord::Result object and very easy to handle with. The header and values of the result set can be obtained by using the following methods:
# Get the column names of the result:
result.columns
# => ["id", "model", "color"]# Get the record values of the result:
result.rows
# => [[1, "model_1", "color_1"],
[2, "model_2", "color_2"],
...
]
All we need to do is to save the header and the values to the csv string. All these code was wrapped into a private action query_to_csv(). In action show, the csv string is passed into send_data which outputs the result to the file in csv format.
It’s quite simple. Here is the code in the controller.
app/controllers/orders_controller.rb
classOrdersController< ApplicationController::defshow
@orderdetails = @order.orderdetails
add_breadcrumb @order.id, order_path
respond_to do |format|
format.html
format.json
fn = "order_#{@order.po_number}_#{Time.now.strftime("%Y-%m-%d %H:%M:%S")}.csv"# Send_data method and exec_query approach
format.csv { send_data query_to_csv(@order.id), filename: fn }
endend
private
# Use ActiveRecord::Base.connection.exec_query to # query db directly only once.defquery_to_csv(order_id)
query_string = 'select a.po_number as "PO Number",
c.name as "Model Name", d.name as Color,
e.name as Size, b.price as Price,
b.quantity as Quantity,
(b.price*b.quantity) as "Total Amount"
from orders a, orderdetails b, models c,
colors d, sizes e
where a.id = b.order_id and b.model_id=c.id and
b.color_id=d.id and b.size_id=e.id and
b.order_id=' + order_id.to_s
results = ActiveRecord::Base.connection.exec_query(query_string)
CSV.generate(headers:true) do |csv|
csv << results.columns
results.rows.each do |row|
csv << row
endendendend
Note that PostgreSQL Server only allow double quote for the sql statement. So I use single quote for quoting the whole query string and double quote for the item name with space in it. For example:
‘select a.po_number as “PO Number”…………….. ‘
That’s it!
Performance Improvement
Let’s take a look at the console log.
Started GET “/orders/1.csv” for ::1 at 2016-10-08 12:58:50 +0800
ActiveRecord::SchemaMigration Load (0.2ms) SELECT “schema_migrations”.* FROM “schema_migrations”
Processing by OrdersController#show as CSV
Parameters: {“id”=>”1”}
Order Load (0.2ms) SELECT “orders”.* FROM “orders” WHERE “orders”.”id” = ? LIMIT ? [[“id”, 1], [“LIMIT”, 1]]
(0.2ms) select a.po_number as ‘PO Number’, c.name as ‘Model Name’, d.name as ‘Color’, e.name as ‘Size’, b.price as ‘Price’, b.quantity as Quantity, (b.price*b.quantity) as ‘Total Amount’ from orders a, orderdetails b, models c, colors d, sizes ewhere a.id = b.order_id and b.model_id=c.id and b.color_id=d.id and b.size_id=e.id and b.order_id=1
Rendering text template
Rendered text template (0.0ms)
Sent data order_1234_2016-10-08 12:58:50.csv (4.5ms)
Completed 200 OK in 56ms (Views: 4.3ms | ActiveRecord: 1.3ms)
It takes 3 queries for 1.3ms! More than three times faster!
Note: the private action can be extracted as a Service Object. Please refer to this article.
CHEF is a platform of deployment automation. It is one of the important tools for DevOps engineers.
This is the first time I install Chef in my Mac, trying to understand how it works. Here are the installation processes I documented according to this tutorial.
Perequisites
First, make sure all listed below have been installed in my Mac.
Official documentation Extra reading
This step is to setup the host name of the virtual machine. It writes the host data into the ssh configuration file in the local machine (not the virtual machine).
vagrant ssh-config —host hogege >> ~/.ssh/config
Now we can use ssh hogege to connect to the vagrant virtual machine.
ssh hogege
Chef site-cookbooks creation
knife solo init chef-repo
cd chef-repo
knife solo prepare hogege
$ bootstrapping Chef...
Create cookbook
knife cookbook create hello -o site-cookbooks/
WARN: This command is being deprecated in favor of `chef generate cookbook` and will soon return an error.
Please use `chef generate cookbook` instead of this command.
at /Users/chaoyee/.rvm/gems/ruby-2.3.1@global/gems/chef-12.14.89/lib/chef/knife.rb:430:in `block in run_with_pretty_exceptions'
** Creating cookbook hello in /Users/chaoyee/chef/chef-repo/sitecookbooks
** Creating README for cookbook: hello
** Creating CHANGELOG for cookbook: hello
** Creating metadata for cookbook: hello
Add the line below to /site-cookbooks/hello/recipts/default.rb file
Using Ruby CSV Library to Export CSV File in Rails Apps - send_data method Approach
‘send_data’ method approach
As we mentioned in the previous article, there are two approaches to generate CSV file. Here is the other approach: ‘send_data’ method approach.
The ‘send_data’ method approach use ‘send_data’ method to export data directly to the CSV file in the controller. It does not use the template file, instead, the ‘to_csv’ method is created in the object model. The ‘to_csv’ method utilizes CSV commands to generate data. Then ‘send_data’ method use object.to_csvto export data to the CSV file.
Again, we need to require CSV library in application.rb
app/config/application.rb
require'csv' <== add this line
require'rails/all':
For example, I need a download button on the show page of the model Order. First, I have to add code to the show action of the order controller. ( Actually I manipulate @orderdetails is orders controller )
app/controllers/orders_controller.rb
Note that %w(aaa bbb) is a shortcut for [“aaa”, “bbb”]. Meaning it’s a notation to write an array of strings separated by spaces instead of commas and without quotes around them.
The CSV library generates data according to the attributesarray to the csvobject. Then pass it to the function who calls it ( here is the send_datamethod).
While typing the url below in the browser, it downloads the CSV file instantly.
http://localhost:3000/orders/1.csv
Here is the exported CSV file.
File name: order_1234_2016-10-05 23-38-27.csv
PO Number,Model Name,Size,Color,Price (USD),Quantity,Total
1234,PINEROLO AL SE 0.2 TIG welded Sora 18 Carbon/Alloy Fork,700C X 48CM,Gloss Red,365.42,106,38734.52
1234,PINEROLO AL SE 0.2 TIG welded Sora 18 Carbon/Alloy Fork,700C X 48CM,Gloss Black,365.42,2,730.84
1234,PINEROLO AL SE 0.2 TIG welded Sora 18 Carbon/Alloy Fork,700C X 50CM,Gloss Red,365.42,2,730.84
1234,PINEROLO AL SE 0.2 TIG welded Sora 18 Carbon/Alloy Fork,700C X 50CM,Gloss Black,365.42,2,730.84
The last step is to make a link somewhere in the order show page.
app/views/orders/show.html.erb
<%= link_to 'Export to CSV', order_path(@order, format::csv) %>
Using Ruby CSV Library to Export CSV File in Rails Apps - Template Approach
When we need to export data to a file in CSV format, we don’t need an extra gem to do so. The standard ruby library has included this functionality. All we need is to require it in application.rb
app/config/application.rb
require'csv' <== add this line
require'rails/all':
Now, I need to have a download button on the show page of the model Order. First, I have to add code to the show action of the order controller.
At this point, there are two ways to do the job. One is template approach, the other is send_data method approach.
(send_data method approach is in another article: here )
Template Approach
Template approach using XXX.csv.erbas a template to generate data to CSV file. Utilization of CSV commands exists in the template file.
app/controllers/orders_controller.rb
Note that <%- -%>prevents the app from generating a line break in the CSV file.
While typing the url below in the browser, it downloads the CSV file instantly.
http://localhost:3000/orders/1.csv
Here is the exported CSV file.
File name: order_1234_2016-10-05 23-38-27.csv
PO Number,Model Name,Size,Color,Price (USD),Quantity,Total
1234,PINEROLO AL SE 0.2 TIG welded Sora 18 Carbon/Alloy Fork,700C X 48CM,Gloss Red,365.42,106,38734.52
1234,PINEROLO AL SE 0.2 TIG welded Sora 18 Carbon/Alloy Fork,700C X 48CM,Gloss Black,365.42,2,730.84
1234,PINEROLO AL SE 0.2 TIG welded Sora 18 Carbon/Alloy Fork,700C X 50CM,Gloss Red,365.42,2,730.84
1234,PINEROLO AL SE 0.2 TIG welded Sora 18 Carbon/Alloy Fork,700C X 50CM,Gloss Black,365.42,2,730.84
The last step is to make a link somewhere in the order show page.
app/views/orders/show.html.erb
<%= link_to 'Export to CSV', order_path(@order, format::csv) %>