• Import data từ file .csv, .xls, .... và import vào database có vẻ là 1 chức năng đã khá quen thuộc, và cũng vì vậy mà mỗi web framework đều có 1 vài thư viện hỗ trợ việc đọc các file spreadsheet rất tốt. Nhưng các thư viện này sẽ cung cấp các chức năng cơ bản, và để chúng hoạt động phù hợp với yêu cầu của dự án cụ thể, chúng ta thường xây dựng lại các lớp cơ sở riêng.
• Bài viết này mình sẽ giới thiệu đến các bạn 1 gem hỗ trợ rất tốt cho việc đọc data từ file spreadsheet cho Ruby là roo
• Và cũng để ghi lại cách mà chính mình đã sử dụng nó cho dự hiện tại để ruốt lại kinh nghiệm lần sau. Mong nhận được sự góp ý.

1. Installation

$ gem install roo

Hoặc

  gem "roo"
  gem "roo-xls"

• Ở đây mình có 2 gem là roo và roo-xls.
• roo-xls được tách ra từ roo để riêng biệt cho việc đọc file .xls. Vì dự án hiện tại của mình cần đọc cả file .xls và .xlsx
• Một vài function được hỗ tự trừ roo mình sẽ liệt kê dưới đây:

2. Usage

Opening a spreadsheet

require 'roo'

xlsx = Roo::Spreadsheet.open('./new_prices.xlsx')
xlsx = Roo::Excelx.new("./new_prices.xlsx")

# Use the extension option if the extension is ambiguous.
xlsx = Roo::Spreadsheet.open('./rails_temp_upload', extension: :xlsx)

xlsx.info
# => Returns basic info about the spreadsheet file

Roo::Spreadsheet.open can accept both paths and File instances.

Working with sheets

ods.sheets
# => ['Info', 'Sheet 2', 'Sheet 3']   # an Array of sheet names in the workbook

ods.sheet('Info').row(1)
ods.sheet(0).row(1)

# Set the last sheet as the default sheet.
ods.default_sheet = ods.sheets.last
ods.default_sheet = ods.sheets[2]
ods.default_sheet = 'Sheet 3'

# Iterate through each sheet
ods.each_with_pagename do |name, sheet|
  p sheet.row(1)
end

Accessing rows and columns

Roo uses Excel's numbering for rows, columns and cells, so 1 is the first index, not 0 as it is in an Array

sheet.row(1)
# returns the first row of the spreadsheet.

sheet.column(1)
# returns the first column of the spreadsheet.

Almost all methods have an optional argument sheet. If this parameter is omitted, the default_sheet will be used.

sheet.first_row(sheet.sheets[0])
# => 1             # the number of the first row
sheet.last_row
# => 42            # the number of the last row
sheet.first_column
# => 1             # the number of the first column
sheet.last_column
# => 10            # the number of the last column

Accessing cells

You can access the top-left cell in the following ways

sheet.cell(1,1)
sheet.cell('A',1)
sheet.cell(1,'A')
sheet.a1

# Access the second sheet's top-left cell.
sheet.cell(1,'A',sheet.sheets[1])

Querying a spreadsheet

Use each to iterate over each row.

If each is given a hash with the names of some columns, then each will generate a hash with the columns supplied for each row.

sheet.each(id: 'ID', name: 'FULL_NAME') do |hash|
  puts hash.inspect
  # => { id: 1, name: 'John Smith' }
end

Use sheet.parse to return an array of rows. Column names can be a String or a Regexp.

sheet.parse(id: /UPC|SKU/, qty: /ATS*\sATP\s*QTY\z/)
# => [{:id => 727880013358, :qty => 12}, ...]

Use the :header_search option to locate the header row and assign the header names.

sheet.parse(header_search: [/UPC*SKU/,/ATS*\sATP\s*QTY\z/])

Use the :clean option to strip out control characters and surrounding white space.

sheet.parse(clean: true)

Exporting spreadsheets

Roo has the ability to export sheets using the following formats. It will only export the default_sheet.

sheet.to_csv
sheet.to_matrix
sheet.to_xml
sheet.to_yaml

Excel (xlsx and xlsm) Support

Stream rows from an Excelx spreadsheet.

xlsx = Roo::Excelx.new("./test_data/test_small.xlsx")
xlsx.each_row_streaming do |row|
  puts row.inspect # Array of Excelx::Cell objects
end

By default blank cells will be excluded from the array. To keep them, use the option pad_cells = true. (They will be set to nil in the array)

xlsx.each_row_streaming(pad_cells: true) do |row|
  puts row.inspect # Array of Excelx::Cell objects
end

To stream only some of the rows, you can use the max_rows and offsetoptions.

xlsx.each_row_streaming(offset: 1) do |row| # Will exclude first (inevitably header) row
  puts row.inspect # Array of Excelx::Cell objects
end

xlsx.each_row_streaming(max_rows: 3) do |row| # Will yield 4 rows (it's automatically incremented by 1) after the supplied offset.
  puts row.inspect # Array of Excelx::Cell objects
end

Iterate over each row

xlsx.each_row do |row|
  ...
end

Roo::Excelx also provides these helpful methods.

xlsx.excelx_type(3, 'C')
# => :numeric_or_formula

xlsx.cell(3, 'C')
# => 600000383.0

xlsx.excelx_value(row,col)
# => '600000383'

xlsx.formatted_value(row,col)
# => '0600000383'

Roo::Excelx can access celltype, comments, font information, formulas, hyperlinks and labels.

xlsx.comment(1,1, ods.sheets[-1])
xlsx.font(1,1).bold?
xlsx.formula('A', 2)

OpenOffice / LibreOffice Support

Roo::OpenOffice has support for encrypted OpenOffice spreadsheets.

# Load an encrypted OpenOffice Spreadsheet
ods = Roo::OpenOffice.new("myspreadsheet.ods", password: "password")

Roo::OpenOffice can access celltype, comments, font information, formulas and labels.

ods.celltype
# => :percentage

ods.comment(1,1, ods.sheets[-1])

ods.font(1,1).italic?
# => false

ods.formula('A', 2)

CSV Support

# Load a CSV file
csv = Roo::CSV.new("mycsv.csv")

Because Roo uses the standard CSV library, you can use options available to that library to parse csv files. You can pass options using the csv_options key.

For instance, you can load tab-delimited files (.tsv), and you can use a particular encoding when opening the file.

# Load a tab-delimited csv
csv = Roo::CSV.new("mytsv.tsv", csv_options: {col_sep: "\t"})

# Load a csv with an explicit encoding
csv = Roo::CSV.new("mycsv.csv", csv_options: {encoding: Encoding::ISO_8859_1})

source: README.md

3. Apply

Vấn đề:

• Có 6 template file spreadsheet bao gồm 2 extendtion: .xls và .xlsx
• Mỗi 1 file excel sẽ có nhiều sheet và mỗi sheet có Mình sẽ có 3 loại data:
  • List users
  • List managers
  • 1 block data group
• Yêu cầu:
  • Tìm group trong database với group_code của group.
  • Với mỗi record manager: tạo hoặc update manager và add manager vào danh sách quản lý của group. Nếu có chứa thông tin class_name thì tạo thêm 1 user với data của manager và add vào danh sách students của class và là 1 students thuộc group
  • Với mỗi record users: thì tạo 1 user thuộc class và group.
  • Verify: Nếu data chỉ định user sẽ bắt đầu học 1 class với cùng 1 ngày start_date thì báo errors.
  • List manager sẽ có tọa độ sẽ bắt đầu từ cell đầu tiên nằm phí dưới, cùng column với cell có content là MANAGERS có content là 1 number và kết thúc khi gặp cell có content là STUDENTS
  • List user sẽ có tọa độ sẽ bắt đầu từ cell đầu tiên nằm phí dưới, cùng column với cell có content là STUDENTS có content là 1 number và kết thúc ở row cuối cùng.
  • block group sẽ là nằm ở 1 column có các cell liên tiếp là ["Class code", "Class name", "Class info", "Teacher name"]

ví dụ:

						Class code	CODE_CLASS_1
						Class name	HÓA
						Class info	DẠY TIÊU HÓA
						Teacher name	BERBERIN
	MANAGERS
		name	email	class name
	1
	2
	3
	STUDENTS
	1
	2
	3

Thực hiện:

• Mỗi 1 file excel tất nhiên sẽ có cách detect riêng, mình sẽ không nói về phần này ở đây vì nó khá đơn giản và cũng như không liên quan đến roo mình đang muốn giới thiệu.
• Vì nhận thấy việc xử lý data của mình đều dự trên từng sheet
• Mình sẽ xây dựng 1 thư viện tại lib/spreadsheet_reader
• Mình nhận thấy mình sẽ có 2 cách đọc data:
  • 1. là theo table.
  • 2. Theo block.

=> Mình sẽ xây dựng 2 class cho mỗi cách đọc data này

• Mình xây dựng 1 super class như sau:

# lib/spreadsheet_reader/sheet_reader.rb
class SpreadsheetReader::SheetReader
  include SpreadsheetReader::Finder
  include SpreadsheetReader::Validator

  attr_reader :sheet

  def initialize sheet, args = {}
    validate sheet, args[:validate] if args[:validate].present?
    @sheet = sheet
  end

  def read!
    raise NotImplementedError
  end

  def cell *args
    @sheet.cell(*args)
  end

  def column *args
    @sheet.column(*args)
  end

  def row *args
    @sheet.row(*args)
  end

  private
  def validate sheet, expected_format
    expected_format.each do |key, value|
      raise SpreadsheetReader::Errors::InvalidFormat unless public_send "#{key}?", value, sheet
    end
  end
end

Vì nhận thấy việc 2 việc cơ bản mình phải làm đó là:

• 1. Tìm kiếm địa chỉ của các cell có content, hay 1 block gì đó.
• 2. Đảm bảo sheet đang đọc đúng với format. => Mình xây dựng 2 modul Finder và validate để thực hiện 2 công việc này. Mình muốn việc handle error 1 các cụ thể sẽ do 1 service thuộc application đảm nhiệm nên ở đây mình sẽ raise error để tách biệt lib của mình với application. còn lib chỉ là đọc data theo yêu cầu của sẻvice

Class erros:

# lib/spreadsheet_reader/errors/base.rb
class SpreadsheetReader::Errors::Base < StandardError
  def initialize message = self.class.name.underscore.gsub(%r{\/}, " ")
    super message
  end
end
# lib/spreadsheet_reader/errors/invalid_format.rb
class SpreadsheetReader::Errors::InvalidFormat < SpreadsheetReader::Errors::Base
end

finder:

# lib/spreadsheet_reader/finder.rb
module SpreadsheetReader
  module Finder
    def coordinate_of_content content, sheet = @sheet
      (0...sheet.last_row).each do |row_index|
        column_index = sheet.row(row_index).index content
        return {row: row_index, column: column_index} if column_index
      end
      nil
    end

    def coordinate_of_column_block block, sheet = @sheet
      (0...sheet.last_column).each do |column_index|
        column = sheet.column column_index
        row_index = column.index block.first
        return {row: row_index, column: column_index} if array_contains_block? column, block, row_index
      end
      nil
    end

    def coordinate_of_row_block block, sheet = @sheet
      (0...sheet.last_column).each do |row_index|
        row = sheet.column column_index
        column_index = row.index block.first
        return {row: row_index, column: column_index} if array_contains_block? row, block, column_index
      end
      nil
    end

    def row_index_of content, column_index, sheet = @sheet
      sheet.column(column_index).index content
    end

    def column_index_of content, row_index, sheet = @sheet
      sheet.row(row_index).index content
    end

    private
    def array_contains_block? array, block, index = nil
      index ||= array.index block.first
      return false unless index
      block == array[index...(index + block.length)]
    end
  end
end

Validator:

#  lib/spreadsheet_reader/validator.rb
module SpreadsheetReader
  module Validator
    include SpreadsheetReader::Finder

    def has_cell_with_content? content, sheet = @sheet
      coordinate_of_content(content.to_s, sheet).present?
    end

    def has_cells_with_contents? contents, sheet = @sheet
      contents.is_a?(Array) && contents.all?{|content| coordinate_of_content content, sheet}
    end

    def has_column_with_block? block, sheet = @sheet
      coordinate_of_column_block(block, sheet).present?
    end

    def has_data_at? coordinate, sheet = @sheet
      sheet.cell(coordinate[:row], coordinate[:column]).present?
    end
  end
end

Hầu như mình đã hoàng thành những phần cơ bản mà mình cần, bây h sẽ là 2 class chính

# /lib/spreadsheet_reader/data_table_reader.rb
class SpreadsheetReader::DataTableReader < SpreadsheetReader::Base
  def read! formater
    start_at = table_start_at formater[:start_at]
    end_at = table_end_at formater[:end_at]
    raise SpreadsheetReader::Errors::ValidationError unless start_at && end_at
    map_data_table start_at[:row], end_at[:row], formater[:header], start_at[:column]
  end

  private
  def map_data_table start_at_row, end_at_row, header_formater, start_at_column = 0
    (start_at_row..end_at_row).reduce([]) do |data, row_index|
      row = @sheet.row row_index
      next data unless row[start_at_column].is_a? Numeric
      data.push header_formater.new(*row.slice!(start_at_column + 1, header_formater.new.length))
    end
  end

  def table_start_at start_with
    start_with[:index] || coordinate_of_content(start_with[:label])
  end

  def table_end_at end_with
    return {row: @sheet.last_row} unless end_with
    coordinate_of_content end_with[:label]
  end
end

# /lib/spreadsheet_reader/data_block_reader.rb
class SpreadsheetReader::DataBlockReader < SpreadsheetReader::Base
  def read! formater
    start_at = coordinate_of_column_block formater[:block_label]
    raise SpreadsheetReader::Errors::ValidationError unless start_at
    @sheet.column(start_at[:column] + 1)[start_at[:row]...(start_at[:row] + formater[:block_label].length)]
  end
end

Mình đã xây dựng được tần base cho việc đọc data từ từng sheet. Việc còn lại là nhận file và verify data. Mình sẽ nói cụ thể về cách mình xây dựng tiếp 1 service để phục vụ verify data cũng như import. Vì phần này cần model và logic hơi nhiều. nên mình đã tổ chức model tại repository . Các bạn có thể đọc qua. mình sẽ update các triển khai cụ thể sau.