Cách chỉnh sửa bảng tính Excel bằng Python và Openpyxl

Openpyxl cung cấp các hàm cho phép bạn làm việc với tệp Excel từ Python. Bạn có thể sử dụng nó để xử lý dữ liệu, tạo bảng tính mới và thậm chí là thao tác với công thức.

Thư viện này rất hữu ích nếu bạn cần tự động hóa các phép tính lặp lại trên nhiều tệp Excel. Điều này đặc biệt đúng nếu bạn cần xử lý dữ liệu lớn hoặc thực hiện phân tích dữ liệu.

1. Cách cài đặt mô-đun Openpyxl

Đầu tiên, cài đặt Openpyxl vào máy tính theo các bước sau:

1. Mở Command Prompt và chạy:

   pip install openpyxl

2. Nếu cách trên không hoạt động, bạn có thể tải xuống mô-đun từ trang Tải xuống Tệp của Openpyxl:

    

   1. Tải xuống tệp openpyxl-version.tar.gz.
   2. Giải nén nội dung của tệp.
   3. Mở Command Prompt. Đi đến thư mục của các tệp được giải nén và chạy lệnh sau:

      py setup.py install

2. Tạo tệp Excel và dữ liệu thử nghiệm

Tạo một vài tệp Excel và thêm dữ liệu thử nghiệm vào chúng theo các bước sau:

1. Trong cùng một thư mục, tạo một số tệp Excel. Trong ví dụ này, có 5 tệp Excel. Mỗi tệp lưu trữ dữ liệu dân số cho ba quốc gia khác nhau.

    

2. Trong mỗi tệp Excel, hãy thêm một số dữ liệu thử nghiệm. Ví dụ:

    

Ví dụ này bao gồm dữ liệu liên quan đến dân số của nhiều quốc gia.

>>> Đọc ngay: Học lập trình game với Python cho người mới bắt đầu dễ nhất

3. Cách tạo tập lệnh Python

Tạo tập lệnh Python như sau:

1. Tạo một tệp mới có tên dataAnalysisScript.py. Mở nó bằng bất kỳ trình soạn thảo văn bản nào, như Visual Studio Code hoặc Atom .
2. Thêm ba import sau vào đầu tệp. “os” và “sys” liên quan đến việc truy cập các tệp trên máy tính hoặc đóng chương trình. “openpyxl” là mô-đun được tải xuống ở bước trước cho phép bạn đọc và ghi các tệp Excel.

   import openpyxl
   import os
   import sys
   

3. Bên dưới import, hãy thêm cấu trúc cơ bản của tập lệnh Python bên dưới. Hãy nhớ rằng cấu trúc mã trong Python phụ thuộc vào thụt lề chính xác.

   # Main, start of the program
   if __name__ == "__main__":
    while True:
    # Write code here
    # When the code finishes, close the program.
    sys.exit()
   

4. Bên trong vòng lặp while, thêm lời nhắc (prompt) để người dùng nhập đường dẫn đến thư mục chứa tệp Excel.

   if __name__ == "__main__":
    while True:
    # Asks the user to enter the filepath of the excel file. 
    filePath = input('Please enter the path of the folder where the excel files are stored: ')
    # Goes inside that folder. 
    os.chdir(filePath)
    # Gets the list of excel files inside the folder. 
    excelFiles = os.listdir('.')
   

4. Cách đọc và ghi dữ liệu từ tệp Excel

Sử dụng vòng lặp for để mở, đọc, thao tác và đóng mỗi tệp Excel.

1. Thêm vòng lặp for cho mỗi tệp Excel. Trong vòng lặp, hãy mở từng tệp.

   # For each Excel file
   for i in range(0, len(excelFiles)):
    # This is using the openpyxl module to open the Excel file.
    wb = openpyxl.load_workbook(excelFiles[i])
    sheet = wb.active

2. Đoạn code sau đọc một giá trị cụ thể từ một ô cụ thể:

   cellValue = sheet[f'B3'].value
   

3. Đoạn code sau ghi dữ liệu vào ô “A10”:

   sheet<strong>[strong>f'A10'<strong>].strong>value <strong>=strong> 56

   Bạn cũng có thể ghi vào ô bằng cách chỉ định số hàng và cột. Đoạn code sau đây thêm số “2” vào ô “F1”.

   sheet.cell(row=1, column=6).value = 2
   

>>> Đọc ngay: Python là gì? Những ứng dụng của lập trình Python trong thực tế

5. Cách thực hiện các hàm tích hợp trong Excel bằng tập lệnh Python

Đối với mỗi tệp Excel, hãy tính tổng, trung bình và độ lệch chuẩn cho mỗi cột “country” (quốc gia). Đoạn mã sau đây tính tổng tất cả các số cho mỗi quốc gia:

sheet[f'B11'].value = '=SUM(B4:B9)' 
sheet[f'C11'].value = '=SUM(C4:C9)' 
sheet[f'D11'].value = '=SUM(D4:D9)'

• ‘= SUM (B4: B9)’ là phép tính Excel sẽ cộng tất cả các số trong cột B giữa các ô B4 và B9.
• sheet [f’B11 ‘]. value là giá trị trong ô B11. Chương trình sẽ lưu kết quả cuối cùng vào ô này.

Bạn cũng có thể tính toán các hàm Excel trung bình và độ lệch chuẩn theo cùng một cách được hiển thị ở trên.

sheet[f'B12'].value = '=AVERAGE(B4:B9)'
sheet[f'C12'].value = '=AVERAGE(C4:C9)'
sheet[f'D12'].value = '=AVERAGE(D4:D9)'

sheet[f'B13'].value = '=STDEV(B4:B9)' 
sheet[f'C13'].value = '=STDEV(C4:C9)' 
sheet[f'D13'].value = '=STDEV(D4:D9)'

6. Cách ghi vào tệp và đóng sổ làm việc

Đoạn code còn lại lưu và đóng từng sổ làm việc, sau đó đóng chương trình.

1. Bên trong vòng lặp for, sau khi thực hiện các chức năng Excel cần thiết, hãy lưu các thay đổi được thực hiện vào tệp Excel.

   wb.save(excelFiles[i])
   print(excelFiles[i] + ' completed.')

2. Bên ngoài vòng lặp for, hãy đóng Tập lệnh Python.

   sys.exit()

Toàn bộ mã:

# Main, start of the program
if __name__ == "__main__":
 while True:
 filePath = input('Please enter the path of the folder where the excel files are stored: ')
 os.chdir(filePath)
 excelFiles = os.listdir('.')
 
 # For each excel file
 for i in range(0, len(excelFiles)):
 
 wb = openpyxl.load_workbook(excelFiles[i])
 sheet = wb.active
 
 # Perform all required Excel functions as shown above here
 
 # Save and close the workbook
 wb.save(excelFiles[i])
 print(excelFiles[i] + ' completed.')
 
 # When the code finishes, close the program.
 sys.exit()

>>> Đọc ngay: Tự học lập trình Python căn bản tại FUNiX vô cùng đơn giản

7. Cách chạy tập lệnh Python

1. Mở Command prompt. Đi đến nơi bạn đã lưu trữ tập lệnh của mình. Nếu bạn đã lưu tập lệnh trên Desktop, lệnh sẽ trông giống như sau:

   cd C:\Users\Sharl\Desktop

2. Nhập thông tin sau để chạy tập lệnh:

   python dataAnalysisScript.py

3. Tập lệnh sẽ bắt đầu bằng cách yêu cầu bạn nhập thư mục lưu trữ tất cả tệp Excel của bạn. Ví dụ: nếu bạn lưu trữ thư mục này trên màn hình, đường dẫn tệp sẽ là:

   C:\Users\Sharl\Desktop\CountryData

4. Mở 1 trong các tệp Excel trong thư mục để xem các thay đổi đã thực hiện.

Tự động hóa nhiều hơn với Python

Bây giờ bạn đã có những kiến ​​thức cơ bản về cách thực hiện các hàm Excel bên trong Tập lệnh Python. Bây giờ bạn có thể tìm hiểu cách thực hiện nhiều hàm Excel khác, cũng như nhiều cách khác để tự động hóa tệp Excel. Điều này bao gồm việc sử dụng thư viện như Pandas để nhập dữ liệu hoặc thậm chí sử dụng Visual Basic để thực hiện các tác vụ lặp đi lặp lại trong Excel.

>>> Nếu bạn đang có nhu cầu tìm hiểu về khóa học lập trình đi làm ngay. Hãy liên hệ với FUNiX ngay tại đây:

>>> Xem thêm chuỗi bài viết liên quan:

FUNiX – Học lấy bằng đại học trực tuyến giá trị ngang bằng đại học chính quy

Lập trình game bằng Python đơn giản cho người mới bắt đầu

Lập trình game bằng Python là gì? 5 lợi thế Python trong lập trình game

Vai trò của Python trong phân tích dữ liệu

8 ý tưởng dự án Python phù hợp cho người mới bắt đầu

Vân Nguyễn

Dịch từ: https://www.makeuseof.com/python-openpyxl-excel-spreadsheet-edit/