Bạn có thể tự động nhập dữ liệu bằng Python không?

Bước đầu tiên trong Tự động hóa Python Excel là phân tích Bộ dữ liệu. Bộ dữ liệu được sử dụng trong hướng dẫn này là Bộ dữ liệu bán hàng. Bộ dữ liệu này cũng có sẵn trên Kaggle. Vì Bộ dữ liệu này nằm trong. csv, bạn cần thay đổi nó thành. định dạng xlsx. Dữ liệu sẽ được sử dụng để tạo báo cáo bên dưới 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 Tự động hóa Python Excel là thiết kế Bảng Pivot. 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 dùng để đọc file Excel, tạo Pivot table và xuất ra Excel. Sau đó, bạn có thể sử dụng thư viện Openpyxl trong Python để viết công thức Excel, tạo biểu đồ và bảng tính trong Python

Để đọc tệp Excel của bạn, hãy đảm bảo rằng tệp ở cùng vị trí với tập lệnh Python của bạn và chạy đoạn 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ể 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ã bên dưới

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 ta sẽ sử dụng “. phương thức to_excel[]” như hình bên dưới

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 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 Tự động hóa Python Excel 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 cách sử dụng “. phương thức lưu[]”. Điều này được hiển thị dưới đây

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

Tự động hóa Python Excel cho phép bạn tạo biểu đồ Excel bằng Bảng Pivot. Để 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 thứ 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. Một ví dụ về điều này được hiển thị dưới đây

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

Bước 4. Tự động báo cáo với Python

Bước tiếp theo trong Tự động hóa Python Excel 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 chức năng để 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ên lịch cho Python Script

Bước cuối cùng trong Tự động hóa Python Excel là chạy tập lệnh Python theo 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

Đó là nó. Bạn đã thiết lập thành công Tự động hóa Python Excel trong 5 bước đơn giản

Sự kết luận

Bài viết này cung cấp cho bạn hướng dẫn từng bước về cách 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ề cả Python và MS Excel cùng với các tính năng của chúng. Nhìn chung, Tự động hóa Python Excel là một quy trình sáng tạo mà bạn có thể sử dụng để tạo báo cáo trực quan trên Python giống như cách bạn làm trên Excel một cách liền mạch. Các tổ chức có thể tận dụng Tự động hóa Python Excel để tận dụng các nhiệm vụ kinh doanh của họ theo nhu cầu của họ

Trong trường hợp bạn muốn tích hợp dữ liệu từ các nguồn dữ liệu như Python và MS Excel vào Cơ sở dữ liệu/đích mong muốn của mình và trực quan hóa nó một cách liền mạch trong công cụ BI mà bạn chọn, thì Hevo Data là lựa chọn phù hợp cho bạn. Nó sẽ giúp đơn giản hóa quy trình quản lý và ETL của cả nguồn và đích dữ liệu

Bạn muốn thử Hevo?

Có thể tự động nhập dữ liệu?

Tôi có thể sử dụng Python để tự động hóa Excel không?

Bạn có thể tự động hóa tập lệnh Python không?