Tấm openpyxl trong python

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

1. Thông tin cơ bản về Excel
2. Openpyxl là gì và làm thế nào để cài đặt nó?
3. Đọc dữ liệu từ Excel bằng Python
4. Đọc nhiều ô từ Excel trong Python
5. Tìm số hàng và cột tối đa của một trang tính Excel trong Python
6. Làm cách nào để lặp qua các hàng và cột Excel trong Python?
7. Tạo một tệp Excel mới bằng Python
8. Ghi dữ liệu vào Excel bằng Python
9. Nối dữ liệu vào Excel trong Python
10. Thao tác với Bảng tính Excel trong Python
11. 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

1. Các tệp Excel được gọi là Sổ làm việc
2. Mỗi Workbook có thể chứa nhiều sheet
3. 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
4. Các hàng và cột cùng nhau tạo thành một ô
5. 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ư. -

1. Đọc dữ liệu
2. ghi dữ liệu
3. Chỉnh sửa tệp Excel
4. Vẽ đồ thị và biểu đồ
5. Làm việc với nhiều sheet
6. 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
Quantity

Bước 1 - Nhập phương thức

ProductId
ProductName
Cost per Unit
Quantity

from openpyxl import load_workbook

Bướ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
Quantity

   sheet = wb.active

Cá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
Quantity

Cá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
Quantity

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
Quantity

đầu ra

ProductId
ProductName
Cost per Unit
Quantity

Bạn có thể thấy rằng bằng cách cắt dữ liệu

ProductId
ProductName
Cost per Unit
Quantity

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)

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)

Mã số

from openpyxl import load_workbook

đầu ra

from openpyxl import load_workbook

Phươ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
Quantity

Mã số

from openpyxl import load_workbook

đầu ra

ProductId
ProductName
Cost per Unit
Quantity

Phươ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)

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)

Mã số

from openpyxl import load_workbook

đầu ra

from openpyxl import load_workbook

Phươ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

đầu ra

from openpyxl import load_workbook

Phươ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)

Mã số

from openpyxl import load_workbook

đầu ra

from openpyxl import load_workbook

Cá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
Quantity

Mã số

   wb = load_workbook('wb1.xlsx')

đầu ra

from openpyxl import load_workbook

Tì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)

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)

Mã số

   wb = load_workbook('wb1.xlsx')

đầu ra

   wb = load_workbook('wb1.xlsx')

Lư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)

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)

Mã số

   wb = load_workbook('wb1.xlsx')

đầu ra

   wb = load_workbook('wb1.xlsx')

Là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
Quantity

ProductId
ProductName
Cost per Unit
Quantity

1. ProductId
   ProductName
   Cost per Unit
   Quantity

   3 - Trả về một phần tử bộ trên mỗi hàng được chọn

2. ProductId
   ProductName
   Cost per Unit
   Quantity

   4 - 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')

đầu ra

   wb = load_workbook('wb1.xlsx')

Như 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
Quantity

Mã - Không chuyển min_col và max_col

   wb = load_workbook('wb1.xlsx')

đầu ra

   wb = load_workbook('wb1.xlsx')

Tấ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.active

đầu ra

   sheet = wb.active

Cá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
Quantity

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
Quantity

Mã - Không chuyển bất kỳ đối số nào

   sheet = wb.active

đầu ra

from openpyxl import load_workbook

Tạ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
Quantity

Mã số

   sheet = wb.active

Thao 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
Quantity

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.active

đầu ra

Cách 2 - Ghi dữ liệu vào Excel bằng phương thức cell()

Mã số

   sheet = wb.active

đầ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.active

đầu ra

Bạn cũng có thể sử dụng các phương thức như

ProductId
ProductName
Cost per Unit
Quantity

ProductId
ProductName
Cost per Unit
Quantity

Mã - Ví dụ 2 - sử dụng phương pháp

ProductId
ProductName
Cost per Unit
Quantity

   sheet = wb.active

đầu ra

Mã - Ví dụ 3 - sử dụng phương pháp

ProductId
ProductName
Cost per Unit
Quantity

   sheet = wb.active

đầ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
Quantity

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

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
Quantity

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

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)

Như bạn có thể thấy,

ProductId
ProductName
Cost per Unit
Quantity

Để 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
Quantity

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

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)

đầ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)

đầ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
Quantity

Khách quan

1. Thêm một cột mới hiển thị

   ProductId
   ProductName
   Cost per Unit
   Quantity

   29
2. Tính toán

   ProductId
   ProductName
   Cost per Unit
   Quantity

   30 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)

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)

Mã số

   wb = load_workbook('wb1.xlsx')

đầu ra

   wb = load_workbook('wb1.xlsx')

Bướ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
Quantity

ProductId
ProductName
Cost per Unit
Quantity

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

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)

Bây giờ bạn có thể tính toán

ProductId
ProductName
Cost per Unit
Quantity

ProductId
ProductName
Cost per Unit
Quantity

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
Quantity

Bướ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
Quantity

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)

from openpyxl import load_workbook

Openpyxl có hoạt động với Xlsx không?

Openpyxl có thể viết XLS không?

Pandas hay openpyxl nào tốt hơn?