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

This article mainly explains "how to operate Excel with Python". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to operate Excel with Python".

Foreword:

Today is an information age, it is very important to master the processing of information. I have to say, Excel is really powerful in information processing, and we often use it to organize information and display it.

However, there are a variety of sources of information. For example, information may be stored in plain text in txt format. Suppose we want to import these types of files into Excel, I believe most people will do so.

Create a new Excel file, open the txt file, and copy the contents of the txt file directly into Excel.

In this way, the way the information is stored has been successfully changed. But have you ever thought that if there are dozens or even hundreds of txt files, would you still be willing to manually open and copy them one by one? I don't think most people want to. But what can I do if I don't want to? Don't worry, today I have brought you a tutorial on using the Python library to operate Excel, so that you can deal with data efficiently. If you are in a hurry, you can skip to the end of the article.

Preparatory work

Install Python's openpyxl library by opening the command prompt (cmd). If you don't know, hold down win+r and the following window pops up.

Then type cmd and click OK to enter the command prompt

After entering the command prompt, enter the following instructions and press enter

Pip install openpyxl

After a short wait, the word successfully indicates that the installation is successful.

Process analysis

Create an Ecxlel workbook

New Excel worksheet

Copy all the txt files to the specified folder

Get the full path of the txt file

Open all txt files and store their contents in different variables

Loop to open the txt file

Read the content of txt text and divide it into lists according to certain specifications

Use a loop to write the contents of the list to Excel cells one by one

Save the Excel workbook

Complete code

# Import openpyxl Library import openpyxl# Import os Library, which is used to get all the files import os in a directory

# create a workbook wb=openpyxl.Workbook () # create a worksheet, title is the name of the table, index is the serial number of the table, 0 represents the first table, # title can change ws=wb.create_sheet (title=' employee information', index=0) # get the file path path=input ('Please enter the full path of the folder where the txt file is located You can also drag it directly to the window:') # list all the files in the folder txt_file_list=os.listdir (path) # jroom1 represents the j + 1 file jroom0

# write the files in the folder to for txt in txt_file_list one by one: # Open txt this file r means you can only read this file. Encoding='utf-8' specifies the encoding format used to open the file #. If you open the txt file, you will see garbled codes in Chinese. You can add that encoding='utf-8' # path+'/'+txt represents the full path to the txt file with open (path+'/'+txt,'r',encoding='utf-8') as f: # txt_content represents all the contents of the txt file. F.read () is all the contents of the txt file txt_content=f.read () # txt_content_list represents a list divided by newline characters. If you use the delimited file # then\ nit should be replaced by txt_content_list=txt_content.split ('\ n')

# use len (txt_content_list) to get the list length of txt_content_list length=len (txt_content_list) # start length from 1, write each element in txt_content_list into # range function left close and right open for i in range (1): # ws.cell (1) represents the first row of 'employee information' in this table The second column, ws.cell (1 txt 2). Value represents the contents of this cell # add the elements in the txt_content_list list to the corresponding cell ws.cell (I, jade 1). Value=txt_content_ list [I-1] # each time you finish writing a txt file, j adds 1Magne jend1 to represent the sequence number of the column in Excel. It also represents the j + 1 txt file j=j+1# keeps as the file wb.save ('test.xlsx') input (' file saved successfully, press any key to exit:') under the path of the current code file.

Thank you for your reading, the above is the content of "how to operate Excel with Python". After the study of this article, I believe you have a deeper understanding of how to use Python to operate Excel, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.