Openpyxl hợp nhất hai tệp excel

Lúc đầu, hãy đặt đường dẫn chứa tất cả các tệp excel bạn muốn hợp nhất. Lấy các tệp excel và đọc chúng bằng glob −

path = "C:\Users\amit_\Desktop\"

filenames = glob.glob(path + "\*.xlsx")
print('File names:', filenames)

Tiếp theo, tạo một khung dữ liệu trống cho tệp excel đầu ra đã hợp nhất sẽ lấy dữ liệu từ hai tệp excel trên -

outputxlsx = pd.DataFrame()

Bây giờ, quá trình thực tế có thể được nhìn thấy tôi. e. lúc đầu, lặp lại các tệp excel bằng vòng lặp for. Đọc các tệp excel, nối chúng và nối thêm dữ liệu -

for file in filenames:
   df = pd.concat(pd.read_excel(file, sheet_name=None), ignore_index=True, sort=False)
   outputxlsx = outputxlsx.append(df, ignore_index=True)

Thí dụ

Sau đây là mã -

import pandas as pd
import glob

# getting excel files to be merged from the Desktop 
path = "C:\Users\amit_\Desktop\"

# read all the files with extension .xlsx i.e. excel 
filenames = glob.glob(path + "\*.xlsx")
print('File names:', filenames)

# empty data frame for the new output excel file with the merged excel files
outputxlsx = pd.DataFrame()

# for loop to iterate all excel files
for file in filenames:
   # using concat for excel files
   # after reading them with read_excel()
   df = pd.concat(pd.read_excel( file, sheet_name=None), ignore_index=True, sort=False)

   # appending data of excel files
   outputxlsx = outputxlsx.append( df, ignore_index=True)

print('Final Excel sheet now generated at the same location:')
outputxlsx.to_excel("C:/Users/amit_/Desktop/Output.xlsx", index=False)

đầu ra

Điều này sẽ tạo ra đầu ra sau i. e. , tệp excel đã hợp nhất sẽ được tạo tại cùng một vị trí -

Trong hướng dẫn ngắn này, tôi sẽ chỉ cho bạn cách sử dụng Python để kết hợp nhiều tệp Excel thành một bảng tính chính. Hãy tưởng tượng rằng bạn có hàng chục tệp Excel với các trường dữ liệu giống nhau và công việc của bạn là tổng hợp các trang tính từ các tệp đó. Thực hiện công việc này theo cách thủ công là cực kỳ kém hiệu quả và Python sẽ giúp bạn tiết kiệm rất nhiều thời gian trong thời gian dài, vì vậy hãy để tất cả làm việc thông minh hơn

Lưu ý rằng bài viết này nói về việc nối thêm các tệp Excel có cùng định dạng/trường dữ liệu. Hợp nhất nhiều tập dữ liệu là một nhiệm vụ khác

Nếu bạn chưa quen với Python, loạt bài này Tích hợp Python với Excel sẽ cung cấp một số mẹo về cách sử dụng Python để nâng cấp bảng tính Excel của bạn

quy trình làm việc

Để giải quyết vấn đề, chúng ta cần thực hiện theo quy trình công việc bên dưới

1. Xác định các tệp chúng ta cần kết hợp
2. Lấy dữ liệu từ tệp
3. Di chuyển dữ liệu từ bước 2) sang tập dữ liệu chính (chúng tôi sẽ gọi nó là “khung dữ liệu”)
4. Báo cáo 2-3 cho số lượng tệp
5. Lưu tập dữ liệu chính vào bảng tính Excel

Nhập thư viện

Được rồi, hãy xem cách viết mã luồng công việc trên bằng Python. Đối với bài tập này, chúng ta sẽ cần sử dụng hai thư viện Python. os và pandas. Nếu bạn muốn làm theo, vui lòng lấy mã nguồn và các tệp được sử dụng trong hướng dẫn này từ đây. Mặc dù bạn có thể kết hợp bao nhiêu tệp Excel tùy thích, nhưng chúng tôi sẽ sử dụng ba tệp để minh họa quy trình

Nếu bạn cần trợ giúp cài đặt Python hoặc thư viện, đây là hướng dẫn về cách thực hiện

oslibrary đưa ra cách sử dụng các chức năng phụ thuộc vào hệ điều hành. Chẳng hạn như thao tác với thư mục và đường dẫn tệp. Chúng tôi sử dụng thư viện này để lấy tất cả các tên tệp Excel, bao gồm cả đường dẫn của chúng

Thư viện pandas là tiêu chuẩn vàng để phân tích và thao tác dữ liệu. Nó nhanh, mạnh mẽ và linh hoạt. Chúng tôi sử dụng thư viện này để tải dữ liệu Excel vào Python, thao tác dữ liệu và tạo lại bảng tính chính

Chúng tôi sẽ bắt đầu bằng cách nhập hai thư viện này. Sau đó tìm thư mục làm việc hiện tại, cũng như tất cả các tên tệp trong thư mục

import os
import pandas as pd
cwd = os.path.abspath('') 
files = os.listdir(cwd) 

Nhận thư mục làm việc hiện tại và các tệp trong đó

Biến cwd hiển thị đường dẫn đến thư mục làm việc hiện tại và biến files là danh sách tất cả các tên tệp trong thư mục làm việc hiện tại. Lưu ý rằng có những tệp không phải Excel và chúng tôi không muốn mở những tệp đó, vì vậy chúng tôi sẽ xử lý sớm

Kết hợp nhiều tệp Excel vào một bảng tính

Tiếp theo, chúng tôi tạo một khung dữ liệu trống

df = pd.DataFrame()
for file in files:
     if file.endswith('.xlsx'):
         df = df.append(pd.read_excel(file), ignore_index=True) 
df.head()

0 để lưu trữ dữ liệu cho bảng tính chính. Chúng tôi lặp qua tất cả các tệp trong thư mục làm việc hiện tại, nhưng chỉ xử lý các tệp Excel có tên kết thúc bằng “. xlsx”. Điều này được thực hiện bởi dòng mã này

df = pd.DataFrame()
for file in files:
     if file.endswith('.xlsx'):
         df = df.append(pd.read_excel(file), ignore_index=True) 
df.head()

1

df = pd.DataFrame()
for file in files:
     if file.endswith('.xlsx'):
         df = df.append(pd.read_excel(file), ignore_index=True) 
df.head()

2 sẽ đọc dữ liệu Excel vào Python và lưu trữ dưới dạng đối tượng DataFrame của gấu trúc. Xin lưu ý rằng phương pháp này chỉ đọc tab/trang tính đầu tiên của tệp Excel theo mặc định. Nếu file Excel của bạn chứa nhiều hơn 1 sheet, hãy tiếp tục đọc phần tiếp theo

df = pd.DataFrame()
for file in files:
     if file.endswith('.xlsx'):
         df = df.append(pd.read_excel(file), ignore_index=True) 
df.head()

3 sẽ nối/kết hợp dữ liệu từ tệp này sang tệp khác. Hãy nghĩ về việc sao chép một khối dữ liệu từ một tệp Excel và dán nó vào một tệp khác. Thay vì mở Excel, dữ liệu được lưu trữ bên trong bộ nhớ máy tính của bạn

df = pd.DataFrame()
for file in files:
     if file.endswith('.xlsx'):
         df = df.append(pd.read_excel(file), ignore_index=True) 
df.head()

Đoạn mã trên thực hiện như sau

1. Lặp lại tất cả các tệp trong thư mục làm việc hiện tại, xác định xem tệp có phải là Excel hay không bằng cách kiểm tra tên tệp kết thúc bằng “. xlsx”
2. Nếu có, hãy đọc nội dung tệp (dữ liệu) và nối/thêm nó vào biến khung dữ liệu chính có tên là

   df = pd.DataFrame()
   for file in files:
        if file.endswith('.xlsx'):
            df = df.append(pd.read_excel(file), ignore_index=True) 
   df.head()

   4
3. Lưu khung dữ liệu chính vào bảng tính Excel

Chúng tôi có thể kiểm tra khung dữ liệu chính bằng cách kiểm tra df. head() , hiển thị 5 hàng đầu tiên của dữ liệu.

Kiểm tra 5 hàng dữ liệu đầu tiên trong khung dữ liệu

Nhìn có vẻ tốt. Chỉ cần kiểm tra nhanh khác để đảm bảo chúng tôi đã tải mọi thứ trong DataFrame.

df = pd.DataFrame()
for file in files:
     if file.endswith('.xlsx'):
         df = df.append(pd.read_excel(file), ignore_index=True) 
df.head()

5 sẽ cho chúng ta thấy kích thước (36 hàng, 5 cột) của dữ liệu

Mọi thứ đều ổn, vì vậy hãy xuất dữ liệu trở lại Excel. Dòng cuối cùng

df = pd.DataFrame()
for file in files:
     if file.endswith('.xlsx'):
         df = df.append(pd.read_excel(file), ignore_index=True) 
df.head()

6 sẽ làm điều đó

Kết hợp nhiều trang tính từ cùng một tệp Excel

Mình đã nói về 2 kỹ thuật đọc nhiều sheet trên cùng 1 file Excel rồi nên mình sẽ không nhắc lại. Tuy nhiên, tôi sẽ xem qua một ví dụ ở đây với cài đặt hơi khác

Chúng tôi có 2 tệp, mỗi tệp chứa một số trang tính. Chúng tôi không biết có bao nhiêu trang tính trong mỗi tệp, nhưng chúng tôi biết định dạng giống nhau cho tất cả các trang tính. Mục tiêu của chúng tôi là tổng hợp tất cả các trang tính vào một bảng tính (và một tệp)

Quy trình làm việc cũng tương tự

1. Nhận tất cả các tệp Excel
2. Lặp qua các tệp Excel
3. Đối với mỗi tệp, lặp qua tất cả các trang tính
4. Đọc từng trang tính vào một khung dữ liệu, sau đó kết hợp tất cả các khung dữ liệu lại với nhau

df_total = pd.DataFrame()
for file in files:  # loop through Excel files
    if file.endswith('.xlsx'):
        excel_file = pd.ExcelFile(file)
        sheets = excel_file.sheet_names
        for sheet in sheets: # loop through sheets inside an Excel file
            df = excel_file.parse(sheet_name = sheet)
            df_total = df_total.append(df)
df_total.to_excel('combined_file.xlsx')

Để tất cả chúng cùng nhau

Dưới đây là mã đầy đủ được kết hợp với nhau. 10 dòng mã sẽ giúp bạn kết hợp tất cả các tệp hoặc trang tính Excel của mình thành một bảng tính chính. Vui thích

Làm cách nào để kết hợp hai tệp Excel vào một sổ làm việc trong Python?

Làm cách nào để hợp nhất hai tệp Excel?

Làm cách nào để kết hợp nhiều tệp Excel vào một VBA sổ làm việc Excel?