你必須很努力

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

2022/01/31
字數統計: 1.2k閱讀時間: 5 min

前言

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

說明

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

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

demo 用的假資料 Excel

1
2
3
4
5
6
# 產一份 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 官方文件很好上手,文字不多,很快就能看完
先安裝 npm install -g secure-spreadsheet
若要針對 XLSX 加密(保護) 的話,CLI example

1
secure-spreadsheet --password secret --input-format xlsx < input.xlsx > output.xlsx

1
2
3
4
5
6
7
8
# 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 加密/解密 (另一個方法)

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

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

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

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 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

1
2
3
4
$ openssl rand -base64 32

# macOS 需安裝 brew install pwgen
$ pwgen 14 1 --symbols

1
2
3
4
5
6
7
# ruby
# need require 'securerandom'
require 'securerandom'

SecureRandom.hex
SecureRandom.base64
SecureRandom.alphanumeric(20) # 20 為密碼長度


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

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

增加超連結 (hyperlink)

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# app/services/shops_excel/generator.rb

LINK_PATTERN = /\A\[(.*)\]\((http.*)\)\z/ # 針對資料 "[data](https://riverye.com/shops/id)",過濾成 data 和 https://riverye.com/shops/id

def 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
end

def 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
# 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

1
2
3
4
5
6
7
8
9
10
11
# app/services/shops_excel/generator.rb

def 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 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]
)

實際結果

小結

原本是只打算寫如何幫 Excel 的 xlsx 檔案加密,想說都寫了,順便查下如何解密,若有更好的方法,歡迎留言和我說,謝謝!!

寫加入超連結那段時,邊寫邊補資料 (原本就只是單純要寫如何加超連結,改字形色彩、下底線..等,是後來補的),同時參考官方範例,網路搜尋下,也能查到許多可以參考的~

參考文件

  1. Opening password-protected excel files? #399
  2. axlsx 官方文件

medium 文章連結:https://link.medium.com/EAV6xgl0fnb
本文同步發布於 小菜的 Blog https://riverye.com/

備註:之後文章修改更新,以個人部落格為主

原文連結:https://riverye.com/2022/01/31/excel-advanced-skills/

發表日期:2022-01-31

更新日期:2022-01-31

CATALOG
  1. 1. 前言
  2. 2. 說明
    1. 2.0.1. demo 用的假資料 Excel
  • Excel 加密
    1. 1. 使用 來加密
  • Excel 加密/解密 (另一個方法)
    1. 1. 使用 進行加密/解密 Excel 檔案
      1. 1.1. 補充: 隨機產密碼
  • 幫 Excel 欄位加超連結、修改字形色彩...等
    1. 1. 增加超連結 (hyperlink)
    2. 2. 增加篩選、凍結第一列
    3. 3. 針對有超連結欄位,改成藍色字形色彩、加底線
    4. 4. 補測試
      1. 4.1. 實際結果
    5. 5. 小結
    6. 6. 參考文件