Sử dụng gem RubyXL để export file excel trong Rails

Nguồn : https://github.com/weshatheleopard/rubyXL Phiên bản mới nhất hiện tại : 3.3.10 RubyXL hỗ trợ hoạt động trên định dạng file .xlsx (mở định dạng XML) Gem này phù hợp để sử dụng trong 2 trường hợp sau

• #####Nạp vào một file excel có sẵn, thao tác chỉnh sửa, thay đổi trên file đó và xuất ra một file excel mới
• #####Xuất dữ liệu từ database của hệ thống ra file excel

Cùng với đó, bạn có thể có những tùy chỉnh cụ thể như thay đổi font, align, color... của các hàng, cột hay từng ô cụ thể của file excel đó mà vẫn dữ được cấu trúc của file Lưu ý : Định dạng file excel .xls không được hỗ trợ

1. Cài đặt.

• Bằng dòng lệnh.

gem install rubyXL

• #####Qua Gemfile.

gem "rubyXL"

2. Sử dụng.

Để sử dụng thì ta cần thêm require "rubyXL" vào ứng dụng

• #####Nạp vào biến workbook một file excel có sẵn.

workbook = RubyXL::Parser.parse("path/file.xlsx")

• #####Tạo mới 1 workbook.

workbook = RubyXL::Workbook.new

3. Truy cập

• #####Truy cập đến 1 worksheet.

workbook.worksheets[0] # Returns first worksheet
workbook[0]            # Returns first worksheet
workbook["Sheet1"]     # Finds and returns worksheet titled "Sheet1"

• #####Chỉ truy cập đến giá trị.

worksheet = workbook[0]
worksheet.extract_data  # Produces a simple rectangular array that consists only of cell values (rather than the Cell objects)

• #####Truy cập đến 1 hàng.

worksheet = workbook[0]
worksheet.sheet_data[0] # Returns first row of the worksheet
worksheet[0]            # Returns first row of the worksheet

• #####Truy cập đến 1 đối tượng cụ thể.

worksheet = workbook[0]
worksheet.sheet_data[0][0] # Returns cell A1 in the worksheet
worksheet[0][0]            # Returns cell A1 in the worksheet

Ngoài ra, còn có thể truy cập đến thuộc tính của cột, wrappers để kết nối đến thuộc tính của hàng, cell

4. Thao tác

RubyXL hỗ trợ người dùng khá nhiều thao tác làm việc với 1 file excel

• #####Thêm mới hoặc thay đổi tên 1 worksheet.

worksheet = workbook.add_worksheet("Sheet2")
worksheet.sheet_name = "Cool New Name"

• #####Thêm mới hoặc thay đổi giá trị tại một ô cụ thể trong file excel.

worksheet.add_cell(0, 0, "A1")      # Sets cell A1 to string "A1"
worksheet.add_cell(0, 1, "", "A1")  # Sets formula in the cell B1 to "=A1"
worksheet[0][0].change_contents("", worksheet[0][0].formula) # Sets value of cell A1 to empty string, preserves formula

• #####Thêm mới hàng/cột/ô.

worksheet.insert_row(1)
worksheet.insert_column(1)
worksheet.insert_cell(0, 0, "blah", formula = nil, :right)  # Inserts cell at A1, shifts cells in first row right

• #####Xóa hàng/cột/ô.

worksheet.delete_row(1)
worksheet.delete_column(1)
worksheet.delete_cell(0, 0, :left)  # Deletes A1, shifts contents of first row left

• #####Nối các ô lại bới nhau.

worksheet.merge_cells(0, 0, 1, 1)  # Merges A1:B2

Ngoài ra, ta cũng có thể thay đổi những thuộc tính cụ thể của file excel xuất ra như : font, border, alignment, column awidth, row height,

5. I/O

• #####Writing.

workbook.write("path/to/desired/Excel/file.xlsx")

• #####Streaming.

workbook.stream

6. Ví dụ xuất dữ liệu từ DB ra file excel.

Có 2 bảng có các trường và giá trị cụ thể như sau

• #####companies(name, address) || id| name |address | -------- ------ | 1 |Samsung|Bac Ninh | | 2 | LG |Hai Phong|

• #####products(name, price) ||id| name |price | -------- ------ | 1 |Galaxy S6|14000000| | 2 | LG G4 |12000000| | 3 |Galaxy S5|9000000 |

• #####Config app/config/application.rb

require "rubyXL"

• #####Config app/config/initializers/mime_types.rb

Mime::Type.register "application/xlsx", :xlsx

• #####Router app/config/routes.rb

root "export_rubyxl#index"
get "export_file" => "export_rubyxl#new"

• #####Model app/models/export_rubyxl.rb

	class ExportRubyxl < ActiveRecord::Base
		def self.export_file products, companies
    		workbook = RubyXL::Workbook.new # create new workbook

    		sheet1 = workbook[0] # access to first work book
            sheet1.sheet_name = "Product" # return sheet name "Product"
            sheet1.add_cell(0, 0, "ID")
            sheet1.add_cell(0, 1, "Name")
            sheet1.add_cell(0, 2, "Price")

            # pass value from table "products" into cell of sheet "Product"
            products.each_with_index do |product, num|
              sheet1.add_cell(num + 1, 0, product.id)
              sheet1.add_cell(num + 1, 1, product.name)
              sheet1.add_cell(num + 1, 2, product.price)
            end

            last = products.count + 1
            sheet1.add_cell(last, 2, "Total")
            sheet1.sheet_data[last][2].change_font_bold(true)
            sheet1.add_cell(last + 1, 2, "", "SUM(C2:C#{last})")

            # add new sheet with name is "Company"
            sheet2 = workbook.add_worksheet("Company")
            sheet2.add_cell(0, 0, "ID")
            sheet2.add_cell(0, 1, "Name")
            sheet2.add_cell(0, 2, "Address")

            # pass value from table "companies" into cell of sheet "Company"
            companies.each_with_index do |company, num|
              sheet2.add_cell(num + 1, 0, company.id)
              sheet2.add_cell(num + 1, 1, company.name)
              sheet2.add_cell(num + 1, 2, company.address)
            end

            # set bold for the first row
            sheet1.change_row_bold(0, true)
            sheet2.change_row_bold(0, true)

            # streaming
            workbook.stream.string
  		end
	end

• #####Controller app/controllers/export_rubyxl_controller.rb

    class ExportRubyxlController < ApplicationController
      def index

      end

      def new
        @products = Product.all
        @companies = Company.all

        respond_to do |format|
          format.xlsx do
            send_data ExportRubyxl.export_file @products, @companies
          end
        end
      end
    end

• #####View app/views/export_rubyxl/index.html.erb

<%= link_to "Export", export_file_path(format: :xlsx ) %>

Sau khi chạy server hoàn chỉnh, mở page lên và click vào link Export để tiến hành export dữ liệu trong DB ra file excel Hệ thống sẽ download về máy tính cá nhân 1 file excel chứa nội dung của company và product (mặc định file được lưu trữ ở thư mục ~/Downloads) File excel sau khi download về sẽ có 2 sheet với nội dung cụ thể như sau

• Sheet "Company"

• Sheet "Product"