Thiết lập tự động hóa Python Excel: 5 bước đơn giản

Python là ngôn ngữ lập trình được sử dụng phổ biến nhất hiện nay và có vô số ứng dụng trên nhiều lĩnh vực. Khả năng linh hoạt và năng động của nó khiến nó trở thành lựa chọn lý tưởng khi triển khai và bảo trì. MS Excel là một trong những ứng dụng phổ biến của Microsoft hoạt động dưới dạng bảng tính và được sử dụng để lưu và phân tích dữ liệu số.

Đây là một trong những công cụ đầu tiên được các công ty trên toàn thế giới sử dụng để phân tích dữ liệu nhằm có được thông tin chi tiết. Nó còn được gọi là một công cụ BI (Business Intelligence) truyền thống. Bằng cách thiết lập Tự động hóa Python Excel, bạn có thể chuyển dữ liệu tự động từ nhiều nguồn khác nhau đến nhiều đích một cách liền mạch.

Bài viết này cung cấp hướng dẫn từng bước để giúp bạn thiết lập Tự động hóa Python Excel. Nó cũng cung cấp cho bạn cái nhìn tổng quan về Python và Excel để hiểu rõ hơn về các công nghệ này riêng lẻ. Đọc cùng để tìm hiểu cách bạn có thể thiết lập Tự động hóa Python Excel cho tổ chức của mình.

Điều kiện tiên quyết

• Kiến thức làm việc của MS Excel.
• Nền tảng kỹ thuật mạnh mẽ bằng Python.
• Kiến thức về các Thư viện Python phổ biến.

Giới thiệu về Python

Python là một ngôn ngữ lập trình cấp cao sử dụng trình thông dịch để xử lý mã. Nó được phát triển bởi Guido Von Rossum và được phát hành vào năm 1991. Khả năng đọc mã liền mạch và tính chất năng động của nó khiến nó trở thành một lựa chọn tuyệt vời khi viết mã. Nó được sử dụng trong nhiều lĩnh vực bao gồm Học máy, Trí tuệ nhân tạo, Kịch bản phía máy chủ, Phân tích dữ liệu và Học sâu. Nó được phát triển bởi Python Software Foundation và bản phát hành ổn định của nó là vào ngày 3 tháng 5 năm 2021.

Python là ngôn ngữ lập trình tương tác, được thông dịch, hướng đối tượng kết hợp nhiều thuộc tính như Ngoại lệ, Mô-đun, Nhập động, Liên kết động, Lớp, Loại dữ liệu động cấp cao, v.v. Nó cũng có thể được sử dụng để thực hiện lệnh gọi hệ thống cho hầu hết tất cả Hệ điều hành nổi tiếng.

Các tính năng chính của Python

Python có một loạt các tính năng giúp phân biệt nó với các ngôn ngữ lập trình khác. Một số tính năng đó được đưa ra dưới đây:

• Nó là miễn phí và mã nguồn mở. Bạn có thể tải xuống Python từ trang web chính thức của họ . Nó có thể được tải xuống và cài đặt dễ dàng. Vì Python là mã nguồn mở, người dùng có nền tảng kỹ thuật tốt có thể thực hiện các thay đổi đối với mã theo các trường hợp sử dụng kinh doanh và yêu cầu sản phẩm.
• Python là một ngôn ngữ thân thiện với người mới bắt đầu và do đó, hầu hết những người có kiến ​​thức lập trình cơ bản đều có thể dễ dàng học cú pháp Python và bắt đầu viết mã trên đó.
• Trong khi sử dụng Python, các nhà phát triển không cần phải có bất kỳ thông tin nào về Kiến trúc hệ thống hoặc quản lý việc sử dụng bộ nhớ theo cách thủ công. Trình thông dịch Python tự động xử lý điều này.
• Python rất mạnh mẽ và di động. Điều này có nghĩa là mã Python được viết trên một máy tính có thể dễ dàng được chuyển sang hệ thống khác và có thể chạy mà không gặp bất kỳ sự cố nào.
• Python cũng hỗ trợ Lập trình hướng đối tượng cho phép người dùng viết mã có thể đọc và sử dụng lại.

Với Tự động hóa Python Excel, bạn có thể sử dụng tính di động và mạnh mẽ của Python song song với Excel để cải thiện Phân tích dữ liệu của mình một cách hiệu quả.

Giới thiệu về MS Excel

Microsoft Excel là một chương trình bảng tính được phát triển bởi Microsoft cho Windows, macOS, Android và iOS. Nó được phát hành vào năm 1987 và đã được sử dụng bởi nhiều phép tính, công cụ đồ họa, bảng tổng hợp và một ngôn ngữ lập trình macro như Visual Basic for Applications. MS Excel đã trở nên phổ biến trong những năm qua vì dữ liệu dễ lưu và dữ liệu có thể được thêm vào hoặc xóa bỏ mà không gây bất kỳ khó chịu nào.

Các tính năng chính của MS Excel

MS Excel có nhiều tính năng độc đáo khiến nó trở thành một lựa chọn tốt so với các phần mềm bảng tính khác. Một số tính năng đó được đưa ra dưới đây:

• MS Excel cho phép bạn tạo đầu trang và chân trang trong tài liệu bảng tính và cho phép người dùng bảo vệ dữ liệu của họ bằng cách cung cấp mật khẩu cho dữ liệu của họ.
• Nó hỗ trợ lọc nơi bạn có thể tìm thấy dữ liệu cần thiết trong sổ làm việc của mình và thay thế nó bằng giá trị bạn cần.
• MS Excel hỗ trợ Sắp xếp dữ liệu, do đó cho phép bạn sắp xếp dữ liệu của mình theo thứ tự tăng dần hoặc giảm dần.
• Nó hỗ trợ nhiều biến thể của công thức, vì vậy bạn có thể thực hiện tất cả các loại tính toán một cách dễ dàng. Nó cũng hỗ trợ nhiều kiểu Trực quan hóa Dữ liệu và biểu đồ.
• Bạn có thể thực hiện Kiểm tra Công thức để tạo mối quan hệ giữa các ô và bảng.

Các bước thiết lập tự động hóa Excel trong Python

Bây giờ bạn đã hiểu rõ về Python và MS Excel, đã đến lúc hiểu các bước để thiết lập Python Excel Automation. Bạn có thể làm theo các bước dưới đây để thiết lập Tự động hóa Python Excel

Bước 1: Phân tích Tập dữ liệu Excel

Bước đầu tiên trong Python Excel Automation là phân tích Dataset. Tập dữ liệu được sử dụng trong hướng dẫn này là Tập dữ liệu bán hàng. Tập dữ liệu này cũng có sẵn trên Kaggle . Vì Tập dữ liệu này ở định dạng .csv , bạn cần thay đổi nó thành định dạng .xslx . Dữ liệu sẽ được sử dụng để tạo báo cáo dưới đây sau khi thiết lập Tự động hóa Python Excel của chúng tôi.

Bước 2: Tạo Pivot Table bằng Pandas

Bước tiếp theo trong Python Excel Automation là thiết kế Pivot Table. Trước khi làm điều đó, bạn cần nhập các thư viện sau:

import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.chart import BarChart, Reference
import string

Pandas được sử dụng để đọc tệp Excel, tạo bảng Pivot và xuất nó sang Excel. Sau đó, bạn có thể sử dụng thư viện Openpyxl bằng Python để viết công thức Excel, tạo biểu đồ và bảng tính bằng Python.

Để đọc tệp Excel của bạn, hãy đảm bảo rằng tệp nằm ở cùng nơi đặt tập lệnh Python của bạn và chạy mã sau trong Excel:

excel_file = pd.read_excel('supermarket_sales.xlsx')
excel_file[['Gender', 'Product line', 'Total']]

Để tạo bảng tổng hợp, bạn cần truy cập vào khung dữ liệu excel_file mà bạn đã tạo trước đó. Bạn có thể sử dụng “ .pivot_table () ” để tạo bảng. Nếu bạn muốn tạo một bảng tổng hợp để hiển thị tổng số tiền được chia cho nam và nữ, bạn có thể chạy mã dưới đây:

report_table = excel_file.pivot_table(index='Gender',columns='Product line',values='Total',aggfunc='sum').round(0)

Cuối cùng, để xuất Pivot Table, chúng tôi sẽ sử dụng phương thức “ .to_excel () ” như hình dưới đây:

report_table.to_excel('report_2021.xlsx',sheet_name='Report',startrow=4)

Thư mục Excel hiện được xuất ở cùng vị trí với các tập lệnh Python của bạn.

Bước 3: Thiết kế Báo cáo bằng Openpyxl

Bước tiếp theo trong Python Excel Automation là thiết kế các báo cáo. Để tạo báo cáo, bạn cần sử dụng phương thức “ load_workbook” được nhập từ Openpyxl và lưu nó bằng phương thức “ .save ()” . Điều này được hiển thị bên dưới:

wb = load_workbook('report_2021.xlsx')
sheet = wb['Report']
# cell references (original spreadsheet)
min_column = wb.active.min_column
max_column = wb.active.max_column
min_row = wb.active.min_row
max_row = wb.active.max_row

Python Excel Automation cho phép bạn tạo biểu đồ Excel bằng Pivot Tables. Để tạo biểu đồ Excel bằng Pivot Table, bạn cần sử dụng Mô-đun Barchart và để xác định vị trí của dữ liệu và giá trị danh mục, bạn có thể sử dụng Mô-đun Tham chiếu. Cả hai công thức này đã được nhập trước đó trong Bước 1. Bạn có thể viết các công thức dựa trên Excel bằng Python, giống như cách bạn viết chúng trong Excel. Ví dụ về điều này được hiển thị bên dưới:

sheet['B7'] = '=SUM(B5:B6)'
sheet['B7'].style = 'Currency

Bước 4: Tự động hóa Báo cáo bằng Python

Bước tiếp theo trong Python Excel Automation là tự động hóa báo cáo của bạn. Bạn có thể viết tất cả mã vào một hàm để dễ dàng tự động hóa báo cáo. Mã đó được hiển thị bên dưới:

import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.chart import BarChart, Reference
import string

def automate_excel(file_name):
   """The file name should have the following structure: sales_month.xlsx"""
   # read excel file
   excel_file = pd.read_excel(file_name)
   # make pivot table
   report_table = excel_file.pivot_table(index='Gender', columns='Product line', values='Total', aggfunc='sum').round(0)
   # splitting the month and extension from the file name
   month_and_extension = file_name.split('_')[1]
   # send the report table to excel file
   report_table.to_excel(f'report_{month_and_extension}', sheet_name='Report', startrow=4)
   # loading workbook and selecting sheet
   wb = load_workbook(f'report_{month_and_extension}')
   sheet = wb['Report']
   # cell references (original spreadsheet)
   min_column = wb.active.min_column
   max_column = wb.active.max_column
   min_row = wb.active.min_row
   max_row = wb.active.max_row
   # adding a chart
   barchart = BarChart()
   data = Reference(sheet, min_col=min_column+1, max_col=max_column, min_row=min_row, max_row=max_row) #including headers
   categories = Reference(sheet, min_col=min_column, max_col=min_column, min_row=min_row+1, max_row=max_row) #not including headers
   barchart.add_data(data, titles_from_data=True)
   barchart.set_categories(categories)
   sheet.add_chart(barchart, "B12") #location chart
   barchart.title = 'Sales by Product line'
   barchart.style = 2 #choose the chart style
   # applying formulas
   # first create alphabet list as references for cells
   alphabet = list(string.ascii_uppercase)
   excel_alphabet = alphabet[0:max_column] #note: Python lists start on 0 -> A=0, B=1, C=2. #note2 the [a:b] takes b-a elements
   # sum in columns B-G
   for i in excel_alphabet:
       if i!='A':
           sheet[f'{i}{max_row+1}'] = f'=SUM({i}{min_row+1}:{i}{max_row})'
           sheet[f'{i}{max_row+1}'].style = 'Currency'
   sheet[f'{excel_alphabet[0]}{max_row+1}'] = 'Total'
   # getting month name
   month_name = month_and_extension.split('.')[0]
   # formatting the report
   sheet['A1'] = 'Sales Report'
   sheet['A2'] = month_name.title()
   sheet['A1'].font = Font('Arial', bold=True, size=20)
   sheet['A2'].font = Font('Arial', bold=True, size=10)
   wb.save(f'report_{month_and_extension}')
   return

Bước 5: Lập lịch tập lệnh Python

Bước cuối cùng trong Python Excel Automation là chạy tập lệnh Python ở các lịch trình khác nhau theo yêu cầu dữ liệu. Bạn chỉ cần sử dụng bộ lập lịch tác vụ hoặc cron tương ứng trên Windows và Mac.