前言
延續 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 檔案加密，想說都寫了，順便查下如何解密，若有更好的方法，歡迎留言和我說，謝謝!!
寫加入超連結那段時，邊寫邊補資料 (原本就只是單純要寫如何加超連結，改字形色彩、下底線..等，是後來補的)，同時參考官方範例，網路搜尋下，也能查到許多可以參考的~
參考文件
medium 文章連結：https://link.medium.com/EAV6xgl0fnb
本文同步發布於 小菜的 Blog https://riverye.com/
備註：之後文章修改更新，以個人部落格為主