A simple way to export data to excel format using python script

During the internship, a senior fellow on the server asked me to help sort out the server’s log data. Finally, I used Python to extract the data and export it in Excel format. The following is the source code of my Python implementation, which can automatically traverse all text files in a certain file directory and export the total data to an Excel file. Exporting it to Excel format is more convenient for statistics.

//Achieve traversal and statistics of all files in the directory in the .txt format. If it is in another format, just change the following .txt to the format suffix you need, which is more convenient.

//The process is to first extract the contents of all files and write them into a new file, then extract the data from the new file, and finally write the data into the Excel file

from pyExcelerator import *
import os
currentpath = os.getcwd() 
testlog = open('test.mak','w') 
os.mkdir(r'Excel') 
print "currentpath: ",currentpath 
for file in os.listdir(currentpath):
if os.path.isfile(os.path.join(currentpath,file))==True:
if file.find('.txt')>0:  //如果是别的格式直接将下面的.txt改为你所需要的格式后缀就可以了
file_ = open(file,'r')
content = file_.read()  
file_.close()  
testlog.write( content ) 
print 1
os.popen('log_parse.exe test.mak >> shuju.log')
print 2
for _file in os.listdir(currentpath):
if os.path.isfile(os.path.join(currentpath,_file))==True:
if _file.find('.log')>0:
work = Workbook() 
works = work.add_sheet('Sheet1') 
print 3
file_object = open(_file)
for i in range(0,2):
works.col(i).width = 10000
i = 0
for line in file_object:
line = line.rstrip('\n')
print 4
if not line.split():
i = i + 1
if line.strip():
array = line.split(':')
lineleft = array[0]
lineright = array[1]
works.write(i,0,lineleft)
works.write(i,1,lineright)
i = i + 1
_file = _file.rstrip('.log')
_file = 'Excel\%s.xls' % _file
work.save(_file)

//The print 1 2 3 4 is the log I made. If you don’t want it, you can delete it directly. When using this Python implementation, just save the above code directly to the test.py file.

In addition, a c++ extraction executable file log_parse.exe is used in the middle, which is placed below. When using it, just put it in the same directory as test.py.

If you want to be more convenient, you can create a .bat file and write it in the form of a command line. You can automatically complete all the work with just one click, as follows:

echo
python test.py

My own implementation is to run about 150M files and get the result in one and a half minutes, which I think is quite ideal.

The simple method (recommended) of the above python script to export data to excel format is all the content shared by the editor. I hope it can give you a reference, and I hope you will support the PHP Chinese website.

For more python scripts to implement simple methods for exporting data to excel format, please pay attention to the PHP Chinese website for related articles!