If it's a conventional way of thinking , Open one first Excel form , Then add the corresponding table name in the last column of the table , If it's just a form , If the table contains only one line , This is how it works , Three times five divided by two to finish the work . But if you encounter many tables , If we deal with it one by one , Very tired .
But here's a way to use Python Automated office methods to help you solve problems , And promise not to make mistakes , The battle can be completed in about a few seconds .
The implementation method is to use Python Program , The efficiency is very different . Let's take a look at the code ！
One 、 Code 1
# coding: utf-8
# For each excel Medium sheet Add a row , The value is excel name .xlsx
from pathlib import Path
import pandas as pd
path = Path(r'E:\PythonCrawler\python_crawler-master\MergeExcelSheet\file\777')
excel_list = [(i.stem, pd.concat(pd.read_excel(i, sheet_name=None))) for i in path.glob("*.xls*")]
data_list = []
for name, data in excel_list:
print(name)
print(data)
data[' Table name '] = name
data_list.append(data)
result = pd.concat(data_list, ignore_index=True)
result.to_excel(path.joinpath(' For each excel Medium sheet Add a row , The value is excel name .xlsx'), index=False, encoding='utf-8')
print(' Add and merge complete ！i.stem Get the name of the file , Excluding parent nodes and suffixes , example D:/Desktop/test.txt,i.stem Namely test！
Two 、 Code 2
# coding: utf-8
# For each excel Medium sheet Add a row , The value is excel name -sheet name .xlsx
from pathlib import Path
import pandas as pd
path = Path(r'E:\PythonCrawler\python_crawler-master\MergeExcelSheet\file\777')
excel_list = [(i.stem, pd.concat(pd.read_excel(i, sheet_name=None))) for i in path.glob("*.xls*")]
data_list = []
for name, data in excel_list:
print(name)
print(data)
data[' Table name '] = name
data.reset_index(level=0, inplace=True) # Reset level_0 Indexes
data_list.append(data)
result = pd.concat(data_list, ignore_index=True)
result[' Table name '] = result[' Table name '] + '-' + result['level_0'] # Change column value
del result['level_0'] # Delete extra columns
result.to_excel(path.joinpath(' For each excel Medium sheet Add a row , The value is excel name -sheet name .xlsx'), index=False, encoding='utf-8')
print(' Add and merge complete ！')This code is actually similar to code 1 , The column name is slightly different , Because this example 3 individual excel Inside sheet Same name , Just prefix it with the table name , Table name -sheet name 了 .
After the code runs , In the code directory, the corresponding Excel file , As shown in the figure below .
After each Excel In the table , There are also corresponding table names and corresponding column names , It also realizes the merging function of all tables , As shown in the figure below .
If the Excel If you are interested in merging knowledge , You can learn ~
Here are just two methods , If you have a better solution, you can discuss it in the comment area ~
