preface

A few days ago, in the platinum exchange group, a fan named [🇼🇺🇱🇦] asked a question about Python automated office in the python exchange group. At first glance, he thought it was very simple, but it was actually difficult. The question is shown in the figure below.

2, Solution ideas

If you follow the conventional idea, first open an Excel table, and then add the corresponding table name in the last column of the table. If there is only one table and the table content has only one row, you can divide three by five by two. However, if you encounter many tables, you will be very tired if you deal with them one by one.

However, here is a way to use Python to automate office work to help you solve problems and ensure that there will be no mistakes. You can complete the battle in about a few seconds.

In fact [🇼🇺🇱🇦] I also tried to use Python to solve it, but I encountered some problems. Although the Excel file was created, the subsequent column names failed to be written, and he finally needs to merge the Excel table. In fact, there are two requirements here.

3, Solution

Given here[ 🌑 (this is the code provided by the [big man] on the back of the moon. The general idea is actually the same, but the implementation method is implemented by Python program, and the efficiency is very different. Let's look at the code directly below!

1, Code one

# coding: utf-8
# Add a column to each sheet in excel with the value of 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 column with the value excel name.xlsx'), index=False, encoding='utf-8')
print('Add and merge complete!')

i. Stem means to get the name of the file, excluding the parent node and suffix. For example, D: / desktop / test Txt, i.stem is test!

2, Code two

# coding: utf-8
# Add a column to each sheet in excel, with the value of 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 index
    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 column with the value excel name-sheet name.xlsx'), index=False, encoding='utf-8')
print('Add and merge complete!')

In fact, this code is similar to code 1, but the column name is slightly different. Because the sheet names in the three excel files in the example are the same, the table name is prefixed with the table name - sheet name.

After the code is run, the corresponding Excel file will be automatically generated in the code directory, as shown in the following figure.

After that, each Excel table also has the corresponding table name and the corresponding column name, and also realizes the merging function of all tables, as shown in the following figure.

If you are interested in Excel merging knowledge, you can learn from this article: Four methods of counting use Python to batch merge all Sheet data in Excel files under all subfolders in the same folder , it's full of dry goods!

4, Summary

I'm an advanced python. Based on the questions of fans about Python automated office, this paper gives a solution using Python foundation + pandas file processing, which fully meets the requirements of fans and saves fans a lot of time.

Finally, thank you[ 🌑 (this is the back of the moon] the boss put forward the code and suggestions. Thank [🇼🇺🇱🇦] for asking questions and [Ma Chencheng] for participating in discussion and learning. The article gives two solutions. If you have other methods, you can share them with me at any time. Life is short, I use python!

More Python office automation related codes have been uploaded to git. Welcome to download and star support.

https://github.com/cassieeric/Python-office-automation