Sử dụng gem Axlsx export excel file trong Rails

Sử dụng gem Axlsx export excel file trong Rails

1. Giới thiệu gem axlsx_rails

Trong mọi ứng dụng Rails đều luôn có yêu cầu trích xuất dữ liệu ra các file CSV, những file này sau đó được chuyển thành định dạng Excel để có thể tùy biến định dạng, thêm các style, biểu đồ, .... Tuy nhiên nếu có thể tự động thực hiện các tác vụ tùy biến đó vừa có thể sinh ra các file xlsx ngay lập tức thì thật tuyệt vời, gem axlsx với đa dạng các tính năng là câu trả lời tốt nhất cho vấn đề này.

Gem axlsx_rails cung cấp một danh sách lớn các tính năng được hỗ trợ, có thể kể đến đó là:

• Tạo ra các bản báo cáo xlsx chuyên nghiệp và có thể kiểm soát độ chính xác của dữ liệu

• Dễ dàng tạo nên các biểu đồ như hình tròn 3D, biểu đồ đường, cột dựa vào các dữ liệu trong worksheet của bạn hoặc từ database mà không cần chuyển dữ liệu vào worksheet, ngoài ra có thể tùy biến màu sắc, biểu tượng, …

• Tùy biến style, màu sắc, bố cụ, định dạng chữ, định dạng số, cho toàn bộ hàng hay chỉ một cell ở bất kỳ đầu trong workbook

• Tự động hỗ trợ nhiều kiểu, tự động xác định kiểu dữ liệu bạn dùng, có thể là Float, Integer, String, Date, Time và Boolean và chuyển vào spreadsheet của bạn.

• Gắn các ảnh jpg, gif và png với các thẻ link vào worksheet

• Dễ dàng tham chiếu tới các cell của worksheet bằng format “A1” và “A1:D4” hay từ workbook bằng cách sử dụng syntax “Sheet1!A3:B4” trong đó Sheet1 là worksheet đang trỏ tới và A3, B4 lần lượt là các cell đang tham chiếu đến.

• Hỗ trợ tính toán, gộp hàng, cột một cách dễ dàng.

• Tự động lọc các bảng với worksheet.auto_filter

• Xuất ra thành file hay một thiết bị StringIO, ví dụ như máy in, hỗ trợ căn lề và các tùy biến để in ấn, cài đặt mật khẩu cho sheet của bạn.

• Có thể tương thích 1 phần với GoogleDocs, LibreOffice và Numbers

• Tùy chỉnh tên header để in, tạo ra các header và footer cho sheet.

2. Cách sử dụng

Ta sẽ tìm hiểu nhanh các bước sử dụng cơ bản của gem axlsx

• Đầu tiên ta khởi tạo database, ví dụ tạo bảng Product có 2 cột sau:

rails g model Product title:string price:decimal

Tạo dữ liệu demo bằng gem faker và sử dụng file seeds.rb, sau đó xây dựng chức năng đơn giản:

class ProductsController < ApplicationController
  def index
    @products = Product.order('created_at DESC')
  end
end

ta muốn thực hiện thêm chức năng download file Excel khi click chọn link trong view.

Để thực hiện download, ta tiến hành theo các bước sau:

Thêm gem vào Gemfile và chạy bundle install

gem 'axlsx_rails'

khi đó, ta có một định dạng response mới trong products_controller.rb phục vụ cho chức năng export:

def index
  @products = Product.order('created_at DESC')
  respond_to do |format|
    format.html
    format.xlsx
  end
end

Thêm định dạng đó vào link download trong views/products/index.html.erb

<%= link_to 'Download as .xlsx', products_path(format: :xlsx) %>

Sau đó tạo một template mới cho response tương ứng là index.xlsx.axlsx, khi đó sẽ tự sinh ra một biến local là xlsx_package trong file index.xlsx.axlsx

Cấu trúc của 1 file Excel bao gồm 1 workbook, mỗi workbook sẽ bao gồm nhiều sheets. Từ đó ta tạo một sheet cho dữ liệu của product và sử dụng hàm add_row để tạo một hàng chứa dữ liệu ta cần export:

wb = xlsx_package.workbook
wb.add_worksheet(name: “Products”) do |sheet|
  @products.each do |product|
    sheet.add_row [product.title, product.price]
  end
end

3. Một vài config trong axlsx

Đổi tên file được tạo:

respond_to do |format|
  format.html
  format.xlsx {
    response.headers['Content-Disposition'] = 'attachment; filename="all_products.xlsx"'
  }
end

Hoặc sử dụng hàm render

render xlsx: 'products', template: 'my/template', filename: "my_products.xlsx", disposition: 'inline', xlsx_created_at: 3.days.ago, xlsx_author: "Elmer Fudd"

Bố cục:

ta có thể màu nền cho sheet bằng cách sử dụng hàm add_style

wb = xlsx_package.workbook
wb.styles do |style|
  highlight_cell = style.add_style(bg_color: “EFC376”)

  wb.add_worksheet(name: “Products”) do |sheet|
    @products.each do |product|
      sheet.add_row [product.title, product.price], style: [nil, hight_cell]
    end
  end
end

có rất nhiều style có thể dùng, ví dụ: bg_color, border, alignment

Định dạng thời gian:

Sẽ thật khó chịu khi hiển thị thời gian mặc định trong database, khi đó ta có thể hiển thị lại theo format ta đã định sẵn

Ví dụ, khi muốn hiển thị created_at của product: ta sử dụng style format_code

date_cell = style.add_style(format_code: “yyy-mm-dd”)

sheet.add_row [product.title, product.price, product.created_at], style: [nil, highlight_cell, date_cell]

Thêm thẻ link:

Với hàm add_hyperlink, ta có thể thêm link vào trong sheet

@products.each do |product|
  sheet.add_hyperlink location: url_for(action: “show”, id: product.id, only_path: false),
     ref: sheet.rows, last.cells.first
end

Thêm các công thức tính toán:

Ta muốn SUM tất cả các product hiện có

sheet.add_row ['Total', “=SUM(B1:B#{@products.length})”

4. Một vài vấn đề tương thích

Libre Office:

Bạn phải sử dụng dữ liệu ở trong sheet để tạo biểu đồ, không thể sử dụng dữ liệu tự nhập

Không thể render ra biểu đồ lưới và biểu đồ trục.

Google Docs:

Không hỗ trợ ảnh và màu viền

Numbers:

Không hỗ trợ biểu đồ

5. Kết luận

Trên đây là một vài hướng dẫn cơ bản để sử dụng gem axlsx cho việc xuất dữ liệu database ra file excel đồng thời giới thiệu qua các tính năng và các hạn chế của gem axlsx, mong rằng với các tính năng được hỗ trợ, gem axlsx sẽ là công cụ đắc lực cho quá trình export dữ liệu và bố cục dữ liệu trong file excel

Nguồn:

gem axlsx_rails

generate-excel-spreadsheets-rails-axlsx-gem