Cách Chỉnh Sửa Bảng Tính Excel Bằng Python Và Openpyxl

Bảng tính Excel có thể mạnh mẽ, nhưng một số tác vụ có thể đòi hỏi nhiều sức mạnh lập trình hơn. Nhập Python và mô-đun 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 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 giống nhau trên nhiều tệp Excel. Đây có thể là trường hợp 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.

Mục Lục

• Cách cài đặt Mô-đun Openpyxl
• Tạo tệp Excel của bạn và dữ liệu thử nghiệm
• Cách tạo tập lệnh Python
• Cách đọc và ghi dữ liệu từ tệp Excel
• Cách thực hiện các hàm tích hợp trong Excel bằng tập lệnh Python
• Cách ghi vào tệp và đóng sổ làm việc
• Cách chạy tập lệnh Python
• Tự động hóa nhiều hơn với Python

Cách cài đặt Mô-đun Openpyxl

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

1. Mở dấu nhắc lệnh và chạy như sau: 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ở dấu nhắc lệnh. Điều hướng đế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

Tạo tệp Excel của bạn và dữ liệu thử nghiệm

Tạo nhiều tệp Excel và thêm một số dữ liệu thử nghiệm vào chúng. Nếu bạn chưa quen với Excel, có rất nhiều công cụ có thể giúp bạn tìm hiểu nó.

1. Tạo một số tệp Excel trong cùng một thư mục. Trong trường hợp này, có năm 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 kiểm tra. 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á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 tốt nào, như Visual Studio Code hoặc Atom.
2. Thêm ba lần nhập 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 của bạn 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 openpyxlimport osimport sys
3. Bên dưới phần nhập, 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 programif __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 để 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('.')

LÀM VIDEO TRONG NGÀY

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 từng 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 filefor 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 mã sau đọc một giá trị cụ thể từ một ô cụ thể: cellValue = sheet[f'B3'].value
3. Đoạn mã sau ghi dữ liệu vào ô “A10”: sheet[f'A10'].value = 56

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

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

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 “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.
• tờ giấy[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)'

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

Đoạn mã 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()

Mã hoàn toàn:

# Main, start of the programif __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 filefor 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 workbookwb.save(excelFiles[i])print(excelFiles[i] + ' completed.')

# When the code finishes, close the program.sys.exit()

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

1. Mở dấu nhắc lệnh. Điều hướng đế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 của mình trên Màn hình nền, lệnh sẽ trông giống như sau: cd C:UsersSharlDesktop
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ả cá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:UsersSharlDesktopCountryData
4. Mở một trong các tệp Excel trong thư mục để xem các thay đổi đã thực hiện.

Đảm bảo không có tệp Excel nào được mở khi bạn chạy tập lệnh.

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. Giờ đây, 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.

Cách tạo biểu mẫu nhập dữ liệu tự động trong Excel VBA

Đọc tiếp

Thông tin về các Tác giả

Sharlene Von Drehnen (4 bài báo đã xuất bản)

Sharlene là Nhà văn kỹ thuật tại MUO và cũng làm việc toàn thời gian trong lĩnh vực Phát triển phần mềm. Cô có bằng Cử nhân CNTT và đã từng có kinh nghiệm trong lĩnh vực Đảm bảo chất lượng và dạy kèm tại trường Đại học. Sharlene thích chơi game và chơi piano.

Xem thêm từ Sharlene Von Drehnen

Theo dõi bản tin của chúng tôi

Tham gia bản tin của chúng tôi để biết các mẹo công nghệ, đánh giá, sách điện tử miễn phí và các ưu đãi độc quyền!

Bấm vào đây để đăng ký