Shulou(Shulou.com)06/02 Report--

This article is about how to use Python to find data from hundreds of Excel. I think it is very practical, so I share it with you. I hope you can get something after reading this article.

Hello, everyone, it's time for Python office automation series again.

Today to share a real office automation requirements, we must read the requirements carefully, after understanding the requirements, you can experience the power of Python!

I. description of requirements

First of all, let's take a look at today's requirements. There is an Excel workbook with a summary of file records. Every day, a new table is created based on the date of the day. Each day's table contains all the file information, and new file names may also be added. The summary table of the same year may have two or three hundred worksheets at the end of the year, and there may be more than a thousand archival information in each table. The form of the table is as follows (for visual presentation, this example is presented in the form of 7 worksheets and more than ten files)

What needs to be done: in order to facilitate the review of specific file information, you need to generate a new table after giving the file name, which contains the records of the specified file on all dates (that is, all worksheets). The final result is as follows (take file x003 as an example):

That is, the boss said: give me all the relevant data including file x003 in these hundreds of tables and sort out a new form to me!

Second, step analysis

You can analyze the requirements clearly and simplify complex problems before you formally write the code.

To put it bluntly, this requirement requires that specific rows in all date worksheets be extracted and consolidated into a new table. So we can traverse each table, and then iterate through the first column (name column, which can also be seen as column A) every cell with data. If the text in the cell is the file name we need, extract this row and put it in a new table. Further sorting steps are as follows

Create a new EXCEL workbook

The header of the new table is the same as that in the archive record Excel, which is also the name, configuration, submission date, etc.

Traverse each worksheet sheet of the file record Excel, and then traverse each cell with data in the first column to judge the content

After finding the qualified cell, get the row number, extract the specific row in the current table according to the row number, and append the row to the newly created table

After a clear analysis, we can start to write the code.

Third, Python implementation

First import the required library this example involves opening the old table and creating the new table, so you need to import load_workbook and Workbook from openpyxl (if the modules used by ppt and word are smarter, one method can do it)

From openpyxl import load_workbook, Workbook

Then import the old table and create a new table

# get the summary table from the desktop filepath = ritual C:\ Users\ chenx\ Desktop\ account .xlsm'# modify according to the actual situation workbook = load_workbook (filepath) # create a new Excel workbook get the worksheet new_workbook = Workbook () new_sheet = new_workbook.active # write the new table header new_headers = ['name', 'configuration', 'submission date', 'restricted operation', 'operation time' 'status', 'storage location'] new_sheet.append (new_headers)

Now is the core step: multiple iterations, you can use workbook.sheetnames to get a list of all worksheet names in the workbook, and then traverse

For i in workbook.sheetnames: sheet = workbook [I] # get the column names = sheet where the file name belongs ['A']

According to the previous analysis, you need to traverse the name column to determine whether the value of each cell is the desired file name. It should be noted here that if you have looped to the desired cell, you can stop the loop, but be sure to pass the line number of the cell to a variable for recording, otherwise you will have no memory once the break is out of the loop.

Flag = 0 for cell in names: if cell.value = = keyword: # the keyword here is the file name. You can take file x003 as an example, flag = cell.row break

Get the eligible line number and use sheet [flag] to get the matching line. Openpyxl does not support writing a whole row of the old table to the new table, so the strategy is to assemble all the specific cell values of this row into a list and write it to the new table with the method of sheet.append (list). The complete code for traversing is as follows:

For i in workbook.sheetnames: sheet = workbook [I] names = sheet ['A'] flag = 0 for cell in names: if cell.value = = keyword: flag = cell.row break if flag: # if the flag has not been modified, the following code data_lst = [] for cell in sheet [flag] is not required: # add a judgment to the content here Instead of writing a none if cell.value: data_lst.append (str (cell.value)) else: data_lst.append ('') new_sheet.append (data_lst), let the empty lines be emptied directly.

Finally, remember to save.

New_workbook.save (ritual C:\ Users\ chenx\ Desktop\ account query .xlsx')

This is a real case after a certain adaptation, it can be seen that Python automation office can indeed help us liberate our hands, but we should split the task before writing automation scripts, and then proceed with a clear train of thought. If you are interested in the code and data of this article, you can reply to get it automatically in the background. Finally, I hope you can understand that one of the core of Python office automation is batch operation-liberating hands and automating complex work!

The above is how to use Python to find data from hundreds of Excel. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.