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

This article introduces the knowledge of "how to obtain the file name and maximum number of lines of Excel in batches by Python+os+openpyxl". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Put forward a demand

I don't know how many questions my fans have asked me about office automation, and these questions are the real problem scenarios that people have encountered in their study and work.

two。 Problem solving idea 1) Import the related library import pandas as pd

From openpyxl import load_workbook

From openpyxl import Workbook

Import os 2) get the path of the file path = os.getcwd ()

Print (path)

The results are as follows:

3) iterate through the folder to get the files (including folders and files) for path,dirs,files in os.walk (path) under the folder:

Print (files)

The results are as follows:

4) filter out the Excel table ending in .xlsx tables = []

Path = os.getcwd ()

For path,dirs,files in os.walk (path):

For i in files:

If i.split (".") [1] = "xlsx":

Tables.append (I)

Tables

The results are as follows:

5) organize the data to facilitate subsequent writing to Excel

In particular, the organized data should be a list nested, and each list in the inner layer is each row in the Excel table.

Final_data = []

For table in tables:

Lis = []

Wb = load_workbook (table)

Sheet = wb [wb.sheetnames [0]]

Max_row = sheet.max_row

Lis.append (table)

Lis.append (max_row)

Final_data.append (lis)

Final_data

The results are as follows:

6) create a new Excel table and loop into the data new_wb = Workbook ()

Sheet = new_wb.active

Sheet.title = "final data"

Sheet.append (["File name", "Line"])

For row in final_data:

Sheet.append (row)

New_wb.save (filename= "result .xlsx")

The results are as follows:

3. Complete code

For the sake of the integrity of the article, I put my code at the end of the article. But limited to the length of the article, finally, I only paste a picture, detailed code, you can go to the end of the article to get.

This is the end of the content of "how to get the file name and maximum number of lines of Excel in batch by Python+os+openpyxl". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.