Bảng tính là một công cụ phổ biến trong nhiều tổ chức để nhập, tính toán, xử lý và trình bày dữ liệu. Microsoft Excel, là một biến thể của bảng tính, đặc biệt phổ biến vì tính dễ sử dụng và ít nỗ lực hơn để tìm hiểu và sử dụng nó cho các tác vụ có ý nghĩa
Trong thế giới phân tích, các tệp Excel, như tệp CSV rất phổ biến. Thông thường, các nguồn dữ liệu được yêu cầu để xử lý hoặc chuyển đổi phân tích xuôi dòng thường ở định dạng excel hoặc csv. Do đó, hầu hết tất cả các công cụ, khung hoặc thư viện xử lý dữ liệu đều có tính năng tải, đọc hoặc phân tích tệp excel và csv
Khi xử lý các tệp excel, người ta thường bắt gặp các tệp được bảo vệ bằng mật khẩu do nội dung được bảo mật. Những tệp như vậy rất dễ mở và sử dụng miễn là người dùng biết mật khẩu được sử dụng để khóa chúng. Tuy nhiên, tác vụ thủ công đơn giản này có thể trở nên tẻ nhạt khi có vô số tệp được bảo vệ như vậy cần được mở khóa hoặc giải mã trước khi chúng có thể được sử dụng để xử lý thêm. Trong tình huống như vậy, ngay cả khi biết mật khẩu, việc nhập mật khẩu thủ công trên giao diện Excel để mở từng tệp là không thực tế
Do đó, mong muốn thiết kế một hàm Python có thể tái sử dụng để có thể đọc tệp excel được bảo vệ bằng mật khẩu theo chương trình mà không cần phải nhập mật khẩu theo cách thủ công trong Excel. Chức năng này sẽ tự động hóa quá trình giải mã/mở khóa tệp excel được bảo vệ bằng mật khẩu, đọc dữ liệu và hiển thị kết quả dưới dạng khung dữ liệu có thể được sử dụng bởi các công cụ xử lý xuôi dòng. Đây là mục tiêu của bài viết này
Trong quá trình tìm kiếm một gói Python tiềm năng có thể xử lý tình huống được mô tả ở trên, một số thư viện đã xuất hiện nhưng hầu hết chúng đều có một hoặc hai nhược điểm không hữu ích trong việc đạt được mục đích của bài đăng này. Ví dụ: thư viện pywin32 có thể chấp nhận nhập mật khẩu theo chương trình khi đọc tệp Excel được bảo vệ bằng mật khẩu. Tuy nhiên nó có một hạn chế là chỉ có thể hoạt động trong môi trường Windows
Cuối cùng tôi đã giải quyết được một thư viện có tên là msoffcrypto-tool. Theo nhà phát triển gói, msoffcrypto-tool là một "công cụ và thư viện Python để giải mã các tệp MS Office được mã hóa bằng mật khẩu, khóa trung gian hoặc khóa riêng đã tạo khóa ký quỹ của nó". Xem trang dự án tại đây
Charles Heckroth cung cấp hướng dẫn chi tiết về cách sử dụng công cụ msoffcrypto trên trang blog của anh ấy. Xem nó ở đây
Các bước giải pháp
- Xác định đường dẫn của tệp Excel sẽ được đọc hoặc mở. Với mục đích của bài đăng này, tệp Excel được bảo vệ bằng mật khẩu được cho là nằm trong nhóm S3
- Tạo một đối tượng ByteIO trong bộ nhớ nơi các luồng byte từ tệp Excel sẽ được ghi vào
- Mở tệp được bảo vệ ở chế độ nhị phân. Chế độ này cho phép dữ liệu được đọc và ghi dưới dạng đối tượng byte
- Cung cấp mật khẩu tệp cho phương thức load_key của công cụ msoffcrypto để giải mã tệp. Để đảm bảo tính bảo mật, mật khẩu có thể được lưu trữ trong Kho tham số của Trình quản lý hệ thống AWS và được tham chiếu trong mã
- Sử dụng thư viện Pandas để đọc và tải tệp đã giải mã dưới dạng khung dữ liệu
- Tùy chọn chuyển đổi khung dữ liệu Pandas sang khung dữ liệu Spark cho các trường hợp sử dụng xử lý dữ liệu phân tán
thiết kế mã
from pyspark.sql import DataFrame
import io
import msoffcrypto as mso
import pandas as pd
import pyspark.pandas as ps
import s3fs
import openpyxl
def readPasswordProtectedXLFileFromS3[objectPath, password, **kwargs]:
""" A function to read/load a password-protected Excel file without having to manually type it in Excel interface
Args:
objectPath [str]: The path to the object in the S3 bucket.
password [str]: The password to the object.
**kwargs: Any additional arguments to pass to the Pandas's read_excel method. E.g. header=1, sheet_name='testSheet'
Returns:
DataFrame: Pandas and Spark DataFrames.
"""
# mount s3 like local fs
s3 = s3fs.S3FileSystem [anon=False]
# create an in-memory ByteIO object
decrypted_wb = io.BytesIO[]
with s3.open[objectPath, 'rb'] as xlsfile:
# open the protected file
office_file = mso.OfficeFile[xlsfile]
# provide the password
office_file.load_key[password=password]
# decrypt and write to output file
office_file.decrypt[decrypted_wb]
# read the output file with pandas
pandasDf = pd.read_excel[decrypted_wb, engine="openpyxl", **kwargs]
# extract spark dataframe from pandas dataframe
sparkDf = ps.from_pandas[pandasDf].to_spark[]
return [pandasDf, sparkDf]
Kiểm tra chức năng
Chúng tôi sẽ kiểm tra chức năng bằng tệp excel mẫu được bảo vệ bằng mật khẩu [LMIA2021p. xlsx] nằm trong nhóm S3. Để cho phép đọc tệp excel bằng phương thức mở của Python, chúng tôi sẽ gắn S3 giống như hệ thống tệp cục bộ bằng thư viện s3fs Python như hình bên dưới
Lưu ý rằng bạn phải cung cấp thông tin đăng nhập AWS [khóa và id bí mật] khi sử dụng phương pháp s3fs để mở tệp trong bộ chứa S3 từ bên ngoài tài khoản AWS của bạn. Nếu không, điều này sẽ dẫn đến lỗi cấp phép
s3 = s3fs.S3FileSystem [anon=False, key="XXXXXXXXXXXXXXXXXXXX", secret="YYYYYYYYYYYYYYYYYY"]
Mật khẩu để mở khóa tệp sẽ được tìm nạp dưới dạng chuỗi bảo mật từ kho lưu trữ Tham số của Trình quản lý hệ thống AWS bằng mã bên dưới