用 Ruby on Rails 幫 Excel 檔案加密、解密和欄位加上超連結、修改字形色彩

被端走的小菜

9 min readJan 31, 2022

前言

延續 2021 鐵人賽這篇文章 (Day16 — 匯出 excel-應用篇)，進階介紹如行幫 Excel 檔案加密/解密，還有幫欄位加上超連結、修改字形色彩、增加篩選、凍結第一列…等。

說明

標題已經夠淺白，應該不用多說明吧 (笑

note: 延續 2021 鐵人賽的 repo 中的範例

demo 用的假資料 Excel

# 產一份 demo 用的假資料xlsx = ShopsExcel::Generator.new.execute
xlsx.use_shared_strings = true
xlsx_file = Rails.root.join('data/river_demo.xlsx')
xlsx.serialize(xlsx_file) # 裡面已經有 4 筆假資料

Excel 加密

使用 secure-spreadsheet 來加密

secure-spreadsheet 官方文件很好上手，文字不多，很快就能看完
先安裝 npm install -g secure-spreadsheet
若要針對 XLSX 加密(保護) 的話，CLI example

secure-spreadsheet --password secret --input-format xlsx < input.xlsx > output.xlsx# rails console
# 使用 secure-spreadsheet 來加密input_xlsx_file = Rails.root.join('data/river_demo.xlsx')
password = '我是密碼-River'
result, status = Open3.capture2('secure-spreadsheet', '--password', password, '--input-format', 'xlsx', stdin_data: File.open(input_xlsx_file))
encrypted_xlsx_file_path = input_xlsx_file.dirname.join('river_demo_encrypted.xlsx') # 加密後的檔案
File.write(encrypted_xlsx_file_path, result)

看完上面後，有沒有很簡單，可惜這套僅支援加密
若同時有加密/解密需求的話，可參考以下另一個方法

Excel 加密/解密 (另一個方法)

使用 msoffice 進行加密/解密 Excel 檔案

msoffice 官方文件雖然寫是在 64-bit Windows 執行，但也有支援 Linux 喔!!

note: 安裝方法請參照官方文件說明

在 macOS 上實測加密/解密，是沒問題的!!

以下示範如何解密 Excel 檔案，就不再贅述如何加密

# rails console
# 假設 msoffice path 與 repo path 是同目錄名 (dirname)msoffice = Pathname.new(Dir.pwd).dirname.join('msoffice', 'bin', 'msoffice-crypt.exe')
# or
msoffice = Pathname.new(Dir.pwd).dirname.join('msoffice/bin/msoffice-crypt.exe')input_xlsx_file = Rails.root.join('data/river_demo.xlsx')
encrypted_xlsx_file_path = input_xlsx_file.dirname.join('river_demo_encrypted.xlsx')
password = '我是密碼-River'
decrypted_xlsx_file_path = input_xlsx_file.dirname.join('river_demo_decrypted.xlsx') # 解密後的檔案# 解密 xlsx 檔案
`#{msoffice} -d -p #{password} #{encrypted_xlsx_file_path} #{decrypted_xlsx_file_path}`

補充: 隨機產密碼

若密碼要改成隨機產生 (包含數字、英文大小寫) 的話，以下幾種方法皆可產密碼

隨機產的密碼，務必要存起來，不然加密後的 Excel 會不知道開啟的密碼 XD

$ openssl rand -base64 32# macOS 需安裝 brew install pwgen
$ pwgen 14 1 --symbols# ruby
# need require 'securerandom'
require 'securerandom'SecureRandom.hex
SecureRandom.base64
SecureRandom.alphanumeric(20) # 20 為密碼長度

幫 Excel 欄位加超連結、修改字形色彩…等

仍以上面 demo 資料為主(未加密的)
若需要幫已加上超連結的欄位資料，修改字形色彩、加粗體底線的話，這部分的範例，可參考此 pr

增加超連結 (hyperlink)

commit 寫比較完整，可參考此 commit

# app/services/shops_excel/generator.rbLINK_PATTERN = /\A\[(.*)\]\((http.*)\)\z/ # 針對資料 "[data](https://riverye.com/shops/id)"，過濾成 data 和 https://riverye.com/shops/iddef check_link(row_data)
  row_data.each_with_index.map do |data, index|
    if data.is_a?(String) && matched = data.match(LINK_PATTERN)
      row_data[index] = matched[1]
      [index, matched[2]]
    end
  end.compact
enddef add_link(sheet, links, cur_row)
  links.map do |index, link|
    sheet.add_hyperlink(location: link, ref: sheet.rows[cur_row.row_index].cells[index])
  end
end

增加篩選、凍結第一列

commit 寫比較完整，可參考此 commit

# app/services/shops_excel/generator.rb# 增加篩選
def add_auto_filter(sheet)
  number = TITLES.size - 1
  name = ((number % 26) + 'A'.ord).chr
  excel_column_name =
    if number < 26
      name
    else
      (((number / 26) - 1) + 'A'.ord).chr + name
    end
  sheet.auto_filter = "A1:#{excel_column_name}1"
end# 凍結第一列
def freeze_header(sheet)
  sheet.sheet_view.pane do |pane|
    pane.top_left_cell = 'B2'
    pane.state = :frozen_split
    pane.y_split = 1
    pane.x_split = 0
    pane.active_pane = :bottom_right
  end
end

針對有超連結欄位，改成藍色字形色彩、加底線

commit 寫比較完整，可參考此 commit

# app/services/shops_excel/generator.rbdef add_link(sheet, links, cur_row)
  links.map do |index, link|
    ref = sheet.rows[cur_row.row_index].cells[index]
    # ref.r # A3
    ref.color = '0000FF' # 藍色
    ref.u = true # 底線
    sheet.add_hyperlink(location: link, ref: ref)
  end
end

補測試

commit 寫比較完整，可參考此 commit

# spec/services/shops_excel/generator_spec.rb# 驗證: 超連結
expect_hyperlink = [first_sheet.hyperlinks.first.location, first_sheet.hyperlinks.first.ref]
expect(expect_hyperlink).to match_array(
  ["https://riverye.com/shops/#{@shop.id}", 'A2']
)
# 驗證: 顏色
expect(first_sheet.rows[1].cells.map(&:color).map { |d| d&.rgb }).to match_array(
  ['FF0000FF', nil, nil]
)
# 驗證: 底線
expect(first_sheet.rows[1].cells.map(&:u)).to match_array(
  [:single, nil, nil]
)