Đặt mật khẩu excel Python

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

Làm cách nào để nhập Excel được bảo vệ bằng mật khẩu vào Python?

Bạn có thể điều khiển Excel bằng Python không?