Microsoft Excel có lẽ là một trong những ứng dụng lưu trữ dữ liệu được sử dụng nhiều. Một tỷ lệ lớn các doanh nghiệp vừa và nhỏ đáp ứng yêu cầu phân tích của họ bằng Excel
Tuy nhiên, việc phân tích lượng dữ liệu khổng lồ trong Excel có thể trở nên rất tẻ nhạt và tốn thời gian. Bạn có thể xây dựng ứng dụng phân tích và xử lý dữ liệu tùy chỉnh bằng Visual Basic (VBA), ngôn ngữ hỗ trợ các trang tính Excel. Tuy nhiên, học VBA có thể khó và có lẽ không đáng
Tuy nhiên, nếu bạn có một chút kiến thức về Python, bạn có thể xây dựng Business Intelligence chuyên nghiệp cao bằng cách sử dụng dữ liệu Excel mà không cần cơ sở dữ liệu. Sử dụng Python với Excel có thể thay đổi cuộc chơi cho doanh nghiệp của bạn
Phần được bảo hiểm
- Thông tin cơ bản về Excel
- Openpyxl là gì và làm thế nào để cài đặt nó?
- Đọc dữ liệu từ Excel bằng Python
- Đọc nhiều ô từ Excel trong Python
- Tìm số hàng và cột tối đa của một trang tính Excel trong Python
- Làm cách nào để lặp qua các hàng và cột Excel trong Python?
- Tạo một tệp Excel mới bằng Python
- Ghi dữ liệu vào Excel bằng Python
- Nối dữ liệu vào Excel trong Python
- Thao tác với Bảng tính Excel trong Python
- Ví dụ sử dụng thực tế về phân tích dữ liệu của trang tính Excel trong Python
Thông tin cơ bản về Excel
Trước khi bắt đầu hướng dẫn Openpyxl này, bạn cần ghi nhớ các chi tiết sau
- Các tệp Excel được gọi là Sổ làm việc
- Mỗi Workbook có thể chứa nhiều sheet
- Mỗi trang tính bao gồm các hàng bắt đầu từ 1 và các cột bắt đầu từ A
- Các hàng và cột cùng nhau tạo thành một ô
- Bất kỳ loại dữ liệu có thể được lưu trữ
Openpyxl là gì và làm thế nào để cài đặt nó?
Mô-đun Openpyxl trong Python được sử dụng để xử lý các tệp Excel mà không liên quan đến phần mềm ứng dụng Microsoft của bên thứ ba. Có thể nói, thư viện excel python tốt nhất cho phép bạn thực hiện các thao tác Excel khác nhau và tự động hóa các báo cáo excel bằng Python. Bạn có thể thực hiện tất cả các loại tác vụ bằng Openpyxl như. -
- Đọc dữ liệu
- ghi dữ liệu
- Chỉnh sửa tệp Excel
- Vẽ đồ thị và biểu đồ
- Làm việc với nhiều sheet
- Tạo kiểu trang tính, v.v.
Bạn có thể cài đặt mô-đun Openpyxl bằng cách gõ pip install openpyxl trong dòng lệnh của mình
pip install openpyxlĐọc dữ liệu từ Excel bằng Python
Để nhập tệp excel bằng Python, hãy sử dụng phương thức load_workbook từ thư viện Openpyxl
Hãy nhập tệp Excel có tên ProductId ProductName Cost per Unit Quantity5 bằng Python bằng mô-đun Openpyxl. Nó có các dữ liệu sau như trong hình bên dưới
Bước 1 - Nhập phương thức ProductId ProductName Cost per Unit Quantity6 từ Openpyxl
from openpyxl import load_workbookBước 2 - Cung cấp vị trí tệp cho tệp Excel bạn muốn mở bằng Python
wb = load_workbook('wb1.xlsx')Nếu tệp Excel của bạn nằm trong cùng thư mục với tệp python, bạn không cần cung cấp cho toàn bộ vị trí tệp
Bước 3 - Chọn trang tính hoạt động đầu tiên có trong sổ làm việc bằng thuộc tính ProductId ProductName Cost per Unit Quantity7
sheet = wb.activeCác điểm trên là một cách tiêu chuẩn để truy cập trang tính Excel bằng Python. Bạn sẽ thấy chúng được sử dụng nhiều lần trong suốt bài viết này
Hãy đọc tất cả dữ liệu có trong Hàng 1 (hàng tiêu đề)
Cách 1 - Đọc dữ liệu qua tên ô Excel trong Python
Mã số
from openpyxl import load_workbook wb = load_workbook('wb1.xlsx') sheet = wb.active print(sheet["A1"].value) print(sheet["B1"].value) print(sheet["C1"].value) print(sheet["D1"].value)đầu ra
ProductId ProductName Cost per Unit QuantityCách 2 - Đọc dữ liệu từ Excel bằng phương thức cell() trong Python
Mã số
from openpyxl import load_workbook wb = load_workbook('wb1.xlsx') sheet = wb.active print(sheet.cell(row=1, column=1).value) print(sheet.cell(row=1, column=2).value) print(sheet.cell(row=1, column=3).value) print(sheet.cell(row=1, column=4).value)đầu ra
ProductId ProductName Cost per Unit QuantityĐọc nhiều ô từ Excel trong Python
Bạn cũng có thể đọc nhiều ô từ sổ làm việc Excel. Hãy hiểu điều này thông qua các ví dụ khác nhau. Tham khảo hình ảnh của tệp ProductId ProductName Cost per Unit Quantity5 ở trên để rõ
Phương pháp 1 - Đọc một dãy ô trong Excel bằng cách sử dụng tên ô
Để đọc dữ liệu từ một dải ô cụ thể trong trang tính Excel của bạn, bạn cần cắt đối tượng trang tính của mình qua cả hai ô
Mã số
ProductId ProductName Cost per Unit Quantity2đầu ra
ProductId ProductName Cost per Unit Quantity3Bạn có thể thấy rằng bằng cách cắt dữ liệu ProductId ProductName Cost per Unit Quantity9 từ from openpyxl import load_workbook wb = load_workbook('wb1.xlsx') sheet = wb.active print(sheet.cell(row=1, column=1).value) print(sheet.cell(row=1, column=2).value) print(sheet.cell(row=1, column=3).value) print(sheet.cell(row=1, column=4).value)0, nó trả về cho chúng tôi bộ dữ liệu hàng bên trong một bộ. Để đọc giá trị của mọi ô được trả về, bạn có thể lặp qua từng hàng và sử dụng from openpyxl import load_workbook wb = load_workbook('wb1.xlsx') sheet = wb.active print(sheet.cell(row=1, column=1).value) print(sheet.cell(row=1, column=2).value) print(sheet.cell(row=1, column=3).value) print(sheet.cell(row=1, column=4).value)1
Mã số
from openpyxl import load_workbook 0đầu ra
from openpyxl import load_workbook 1Phương pháp 2 - Đọc một hàng trong Excel bằng tên ô
Để đọc một hàng trong trang tính Excel của bạn, chỉ cần truy cập vào số hàng đơn từ đối tượng ProductId ProductName Cost per Unit Quantity9 của bạn
Mã số
from openpyxl import load_workbook 2đầu ra
ProductId ProductName Cost per Unit QuantityPhương pháp 3 - Đọc tất cả các hàng trong Excel bằng thuộc tính rows
Để đọc tất cả các hàng, hãy sử dụng from openpyxl import load_workbook wb = load_workbook('wb1.xlsx') sheet = wb.active print(sheet.cell(row=1, column=1).value) print(sheet.cell(row=1, column=2).value) print(sheet.cell(row=1, column=3).value) print(sheet.cell(row=1, column=4).value)3 để lặp lại các hàng bằng Openpyxl. Bạn nhận được một phần tử bộ trên mỗi hàng bằng cách sử dụng thuộc tính from openpyxl import load_workbook wb = load_workbook('wb1.xlsx') sheet = wb.active print(sheet.cell(row=1, column=1).value) print(sheet.cell(row=1, column=2).value) print(sheet.cell(row=1, column=3).value) print(sheet.cell(row=1, column=4).value)3
Mã số
from openpyxl import load_workbook 4đầu ra
from openpyxl import load_workbook 1Phương pháp 4 - Đọc một cột trong Excel bằng tên ô
Tương tự như đọc một hàng, bạn có thể đọc dữ liệu trong một cột của trang tính Excel theo bảng chữ cái của nó
Mã số
from openpyxl import load_workbook 6đầu ra
from openpyxl import load_workbook 7Phương pháp 5 - Đọc tất cả các cột trong Excel bằng thuộc tính cột
Để đọc tất cả dữ liệu dưới dạng một bộ cột trong trang tính Excel của bạn, hãy sử dụng thuộc tính from openpyxl import load_workbook wb = load_workbook('wb1.xlsx') sheet = wb.active print(sheet.cell(row=1, column=1).value) print(sheet.cell(row=1, column=2).value) print(sheet.cell(row=1, column=3).value) print(sheet.cell(row=1, column=4).value)5 để lặp qua tất cả các cột bằng Openpyxl
Mã số
from openpyxl import load_workbook 8đầu ra
from openpyxl import load_workbook 9Cách 6 - Đọc toàn bộ dữ liệu trong Excel
Để đọc tất cả dữ liệu có trong trang tính Excel của bạn, bạn không cần lập chỉ mục cho đối tượng ProductId ProductName Cost per Unit Quantity9. Bạn chỉ có thể lặp đi lặp lại nó
Mã số
wb = load_workbook('wb1.xlsx')0đầu ra
from openpyxl import load_workbook 1Tìm số hàng và cột tối đa của Trang tính Excel trong Python
Để tìm số hàng và cột tối đa từ trang tính Excel của bạn bằng Python, hãy sử dụng các thuộc tính from openpyxl import load_workbook wb = load_workbook('wb1.xlsx') sheet = wb.active print(sheet.cell(row=1, column=1).value) print(sheet.cell(row=1, column=2).value) print(sheet.cell(row=1, column=3).value) print(sheet.cell(row=1, column=4).value)7 và from openpyxl import load_workbook wb = load_workbook('wb1.xlsx') sheet = wb.active print(sheet.cell(row=1, column=1).value) print(sheet.cell(row=1, column=2).value) print(sheet.cell(row=1, column=3).value) print(sheet.cell(row=1, column=4).value)8 trong Openpyxl
Mã số
wb = load_workbook('wb1.xlsx')2đầu ra
wb = load_workbook('wb1.xlsx')3Lưu ý - Nếu bạn cập nhật một ô có giá trị, các giá trị from openpyxl import load_workbook wb = load_workbook('wb1.xlsx') sheet = wb.active print(sheet.cell(row=1, column=1).value) print(sheet.cell(row=1, column=2).value) print(sheet.cell(row=1, column=3).value) print(sheet.cell(row=1, column=4).value)7 và from openpyxl import load_workbook wb = load_workbook('wb1.xlsx') sheet = wb.active print(sheet.cell(row=1, column=1).value) print(sheet.cell(row=1, column=2).value) print(sheet.cell(row=1, column=3).value) print(sheet.cell(row=1, column=4).value)8 cũng thay đổi, ngay cả khi bạn chưa lưu các thay đổi của mình
Mã số
wb = load_workbook('wb1.xlsx')4đầu ra
wb = load_workbook('wb1.xlsx')5Làm cách nào để lặp qua các hàng và cột Excel trong Python?
Openpyxl cung cấp hai phương thức thường được sử dụng có tên là ProductId ProductName Cost per Unit Quantity1 và ProductId ProductName Cost per Unit Quantity2 để lặp qua các hàng và cột Excel trong Python
- ProductId ProductName Cost per Unit Quantity3 - Trả về một phần tử bộ trên mỗi hàng được chọn
- ProductId ProductName Cost per Unit Quantity4 - Trả về một phần tử bộ trên mỗi cột được chọn
Cả hai phương thức được đề cập ở trên đều có thể nhận các đối số sau để đặt ranh giới cho phép lặp
- min_row
- max_row
- min_col
- max_col
Ví dụ 1 - iter_rows()
Mã số
wb = load_workbook('wb1.xlsx')6đầu ra
wb = load_workbook('wb1.xlsx')7Như bạn có thể thấy, chỉ 3 cột đầu tiên của 2 hàng đầu tiên được trả về. Các bộ dữ liệu dựa trên hàng
Bạn cũng có thể chọn không chuyển vào một số hoặc bất kỳ đối số nào trong phương thức ProductId ProductName Cost per Unit Quantity1
Mã - Không chuyển min_col và max_col
wb = load_workbook('wb1.xlsx')8đầu ra
wb = load_workbook('wb1.xlsx')9Tất cả các cột từ 2 hàng đầu tiên đang được in
Ví dụ 2 - iter_cols()
Mã số
sheet = wb.active0đầu ra
sheet = wb.active1Các bộ dữ liệu được trả về là cột dựa trên việc sử dụng phương thức ProductId ProductName Cost per Unit Quantity4
Bạn cũng có thể chọn không chuyển vào một số hoặc bất kỳ đối số nào trong phương thức ProductId ProductName Cost per Unit Quantity4
Mã - Không chuyển bất kỳ đối số nào
sheet = wb.active2đầu ra
from openpyxl import load_workbook 9Tạo một tệp Excel mới bằng Python
Để tạo một tệp Excel mới bằng Python, bạn cần nhập lớp ProductId ProductName Cost per Unit Quantity8 từ thư viện Openpyxl
Mã số
sheet = wb.active4Thao tác này sẽ tạo một sổ làm việc Excel mới có tên là ProductId ProductName Cost per Unit Quantity9 với dữ liệu được cung cấp
Ghi dữ liệu vào Excel bằng Python
Có nhiều cách để ghi dữ liệu vào tệp Excel trong Python
Phương pháp 1 - Ghi dữ liệu vào Excel bằng cách sử dụng tên ô
Mã số
sheet = wb.active4đầu ra
Cách 2 - Ghi dữ liệu vào Excel bằng phương thức cell()
Mã số
sheet = wb.active6đầu ra
Phương pháp 3 - Ghi dữ liệu vào Excel bằng cách lặp qua hàng
Mã - Ví dụ 1
sheet = wb.active7đầu ra
Bạn cũng có thể sử dụng các phương thức như ProductId ProductName Cost per Unit Quantity3 và ProductId ProductName Cost per Unit Quantity4 để ghi dữ liệu vào Excel
Mã - Ví dụ 2 - sử dụng phương pháp ProductId ProductName Cost per Unit Quantity3
sheet = wb.active8đầu ra
Mã - Ví dụ 3 - sử dụng phương pháp ProductId ProductName Cost per Unit Quantity4
sheet = wb.active9đầu ra
Nối dữ liệu vào Excel trong Python
Openpyxl cung cấp phương thức ProductId ProductName Cost per Unit Quantity24, được sử dụng để nối các giá trị vào trang tính Excel hiện có trong Python
Mã số
from openpyxl import load_workbook wb = load_workbook('wb1.xlsx') sheet = wb.active print(sheet["A1"].value) print(sheet["B1"].value) print(sheet["C1"].value) print(sheet["D1"].value)0đầu ra
Thao tác với Bảng tính Excel trong Python
Mỗi sổ làm việc Excel có thể chứa nhiều trang tính. Để có danh sách tất cả các tên trang tính trong sổ làm việc Excel, bạn có thể sử dụng hàm ProductId ProductName Cost per Unit Quantity25
Mã số
from openpyxl import load_workbook wb = load_workbook('wb1.xlsx') sheet = wb.active print(sheet["A1"].value) print(sheet["B1"].value) print(sheet["C1"].value) print(sheet["D1"].value)1đầu ra
from openpyxl import load_workbook wb = load_workbook('wb1.xlsx') sheet = wb.active print(sheet["A1"].value) print(sheet["B1"].value) print(sheet["C1"].value) print(sheet["D1"].value)2Như bạn có thể thấy, ProductId ProductName Cost per Unit Quantity9 chỉ có một trang tính
Để tạo một trang tính mới trong Python, hãy sử dụng phương thức ProductId ProductName Cost per Unit Quantity27 từ thư viện Openpyxl
Mã số
from openpyxl import load_workbook wb = load_workbook('wb1.xlsx') sheet = wb.active print(sheet["A1"].value) print(sheet["B1"].value) print(sheet["C1"].value) print(sheet["D1"].value)3đầu ra
Bạn cũng có thể tạo các sheet ở các vị trí khác nhau trong Excel Workbook
Mã số
from openpyxl import load_workbook wb = load_workbook('wb1.xlsx') sheet = wb.active print(sheet["A1"].value) print(sheet["B1"].value) print(sheet["C1"].value) print(sheet["D1"].value)4đầu ra
Nếu sổ làm việc Excel của bạn chứa nhiều trang tính và bạn muốn làm việc với một trang tính cụ thể, bạn có thể tham khảo tiêu đề của trang tính đó trong đối tượng sổ làm việc của mình
Mã số
from openpyxl import load_workbook wb = load_workbook('wb1.xlsx') sheet = wb.active print(sheet["A1"].value) print(sheet["B1"].value) print(sheet["C1"].value) print(sheet["D1"].value)5đầu ra
Ví dụ sử dụng thực tế về phân tích dữ liệu của trang tính Excel trong Python
Hãy thực hiện một số phân tích dữ liệu với tệp ProductId ProductName Cost per Unit Quantity5 như trong hình đầu tiên
Khách quan
- Thêm một cột mới hiển thị ProductId ProductName Cost per Unit Quantity29
- Tính toán ProductId ProductName Cost per Unit Quantity30 của tất cả các mặt hàng đã mua
Bảng Excel kết quả sẽ giống như hình dưới đây
Bước 1 - Tìm dòng tối đa và cột tối đa của trang tính Excel
Như đã đề cập trước đây, bạn có thể sử dụng các thuộc tính from openpyxl import load_workbook wb = load_workbook('wb1.xlsx') sheet = wb.active print(sheet.cell(row=1, column=1).value) print(sheet.cell(row=1, column=2).value) print(sheet.cell(row=1, column=3).value) print(sheet.cell(row=1, column=4).value)7 và from openpyxl import load_workbook wb = load_workbook('wb1.xlsx') sheet = wb.active print(sheet.cell(row=1, column=1).value) print(sheet.cell(row=1, column=2).value) print(sheet.cell(row=1, column=3).value) print(sheet.cell(row=1, column=4).value)8 để tìm hàng tối đa và cột tối đa cho bất kỳ trang tính Excel nào với Openpyxl
Mã số
wb = load_workbook('wb1.xlsx')2đầu ra
wb = load_workbook('wb1.xlsx')3Bước 2 - Thêm một cột bổ sung trong Excel bằng Python
Để thêm một cột bổ sung trong trang tính Excel đang hoạt động, với các phép tính, trước tiên bạn cần tạo một tiêu đề cột mới trong ô trống đầu tiên, sau đó lặp lại trên tất cả các hàng để nhân ProductId ProductName Cost per Unit Quantity33 với ProductId ProductName Cost per Unit Quantity34
Mã số
from openpyxl import load_workbook wb = load_workbook('wb1.xlsx') sheet = wb.active print(sheet["A1"].value) print(sheet["B1"].value) print(sheet["C1"].value) print(sheet["D1"].value)8đầu ra
Bây giờ một tiêu đề cột bổ sung đã được tạo, giá trị from openpyxl import load_workbook wb = load_workbook('wb1.xlsx') sheet = wb.active print(sheet.cell(row=1, column=1).value) print(sheet.cell(row=1, column=2).value) print(sheet.cell(row=1, column=3).value) print(sheet.cell(row=1, column=4).value)8 sẽ thay đổi thành 5
Bây giờ bạn có thể tính toán ProductId ProductName Cost per Unit Quantity29 bằng phương pháp ProductId ProductName Cost per Unit Quantity3
Mã số
from openpyxl import load_workbook wb = load_workbook('wb1.xlsx') sheet = wb.active print(sheet["A1"].value) print(sheet["B1"].value) print(sheet["C1"].value) print(sheet["D1"].value)9đầu ra
ProductId ProductName Cost per Unit Quantity0Bước 3 - Tính tổng của một cột trong Excel bằng Python
Bước cuối cùng là tính toán ProductId ProductName Cost per Unit Quantity30 của cột cuối cùng trong tệp Excel
Truy cập cột cuối cùng và cộng tất cả chi phí
Bạn có thể đọc cột cuối cùng bằng cách truy cập thuộc tính from openpyxl import load_workbook wb = load_workbook('wb1.xlsx') sheet = wb.active print(sheet.cell(row=1, column=1).value) print(sheet.cell(row=1, column=2).value) print(sheet.cell(row=1, column=3).value) print(sheet.cell(row=1, column=4).value)5. Vì nó trả về một trình tạo, trước tiên bạn chuyển đổi nó thành một from openpyxl import load_workbook 00 và truy cập vào cột cuối cùng