If we can achieve report automation, we will save a lot of time and complete the work more efficiently. So, how to use python to realize report automation?

This article will introduce the common functions of xlwt, xlrd and xlutils, the application of formulas when xlwt writes Excel, and the application of xlwt to write specific directories to help you realize report automation.

Python writing Excel

**1. Prepare to install xlwt**

Enter pip install xlwt or easy in the terminal_ Install xlwt introduces xlwt package:

import xlwt  # write

2. Basic tutorial new Workbook & add sheet

Create a new workbook and add sheet s to it

f = xlwt.Workbook()  # Create Workbook
sheet1 = f.add_sheet(u'sheet1', cell_overwrite_ok=True)

Multiple sheets can be added to an excel table to write content to the sheet:

sheet.write The function can pass three parameters i (parameter 1) j (parameter 2) to store the contents (parameter 3)

sheet1.write(i, j, 'The first i Line No j Column to store this content', style)

The function of this statement is to save the contents of the third parameter to the i-th row and the j-th column. The fourth parameter is the style (such as font, background). The fourth parameter can not be passed.

Merge cells and write:

sheet1.write_merge(x, x + m, y, y + n, 'content', style)

This y statement means to merge the matrix of [x:x+m] row [y:y+n] column into a cell. Save the contents of the fifth parameter. In the same way, the style parameter can be saved to excel without transferring parameters. Finally, use f.save('demo ') to save f to excel

3. Actual combat

We can first create a new workbook, then add two sheet s to it, and then check the effect

#coding=utf-8
import xlwt
f = xlwt.Workbook()  # Create Workbook
​
 sheet1 = f.add_sheet(u'Table 1', cell_overwrite_ok=True)
​
 
​
sheet2 = f.add_sheet(u'Table 2', cell_overwrite_ok=True)
​
 
​
save('xlwt_tutorial')

We start to write to the sheet, only use the write function without passing in the style parameter

import xlwt
f = xlwt.Workbook()  # Create Workbook
sheet1 = f.add_sheet(u'Table 1', cell_overwrite_ok=True)
sheet2 = f.add_sheet(u'Table 2', cell_overwrite_ok=True)
row = 0
temp = [u'full name',u'Age',u'school',u'major']
for pos,v in enumerate(temp):
sheet1.write(row,pos,v)
row += 1
sheet1.write(row,0,u'Zhang San')
sheet1.write(row,1,18)
sheet1.write(row,2,u'Tsinghua University')
row += 1
sheet1.write(row,0,u'Li Si')
sheet1.write(row,1,20)
sheet1.write(row,2,u'Peking University')
f.save('xlwt_tutorial')

So we create a table with three rows and four columns.

(both the row and column values of the write function start from 0.) let's use write_merge function to merge cells and write a line of code before f.save:

sheet1.write_merge(1,2,3,3,u'Chinese language and Literature')

Merge lines 2-3, column 4.

Python XD reading excel

**1. Prepare to install xlrd**

Enter pip install xlrd or easy in the terminal_ Install xlrd introduces xlrd package:

import xlrd  # read

2. Basic course & actual combat

Open an Excel and output the names of all sheet s

#coding=utf-8
import xlrd
import uniout
f = xlrd.open_workbook(r'xlwt_tutorial')
print f.sheet_names()
//Output: [u 'Table 1', u 'Table 2']

Get all the sheet s in the table

for i in range(len(f.sheet_names())):
sheet1 = workbook.sheet_by_index(i)

Get the contents of the sheet

#coding=utf-8
f = xlrd.open_workbook(r'xlwt_tutorial')
sheet1 = f.sheet_by_index(0)  #Open the first sheet
sheet2 = f.sheet_by_name(u'Table 2')  #Open the sheet named little grape
​
#Name of output sheet, number of rows, number of columns
print sheet1.name,sheet1.nrows,sheet1.ncols
print sheet2.name,sheet2.nrows,sheet2.ncols
//Output: Table 1, 3, 4, table 2, 0
print sheet1.row_values(1)  #Get the second line
print sheet1.col_values(2)  #Get the third column
//The output is: [u 'Zhang San', 18.0, u 'Tsinghua University', u 'Chinese language and literature'] [u 'school', u 'Tsinghua University', u 'Peking University']
​
# Get cell contents
print sheet1.cell(1,0).value
​
# Get the data type of cell content
print sheet1.cell(1,1).ctype
​
#ctype : 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
//Output: Zhang san2

xlutils common features

**1. Prepare to install xlutils**

Enter pip install xlutils or easy in the terminal_ Install xlutils to import the xlutils package:

import xlutils

2. copy function in xlutils

We may have a problem editing a stored Excel.

But xlrd is read-only and cannot be written.

However, xlwt is write only mode and cannot be read into Excel file for editing. We can use xlrd to open a document, then use copy function in xlutils to copy * the document, and then edit it.

import xlrd
from xlutils.copy import copy
f = xlrd.open_workbook(r'xlwt_tutorial')
wb = copy(f) # Copy f to wb
sheet1 = wb.get_sheet(0) # Open sheet
print sheet1.name
sheet1.write(3,0,'change')
wb.save('xlwt_tutorial')
//Output as: Table 1 the output table has changed.

3. The application of formula when xlwt writes Excel we write a table with xlwt

coding=utf-8
import xlwt
f = xlwt.Workbook()  # Create Workbook
sheet1 = f.add_sheet(u'Score statistics', cell_overwrite_ok=True)
mdict = {"monkey":{"writing":80,"reading":60,"speaking":70,"listening":60},
"grape":{"writing":100,"reading":80,"speaking":70,"listening":60}}
sheet1.write(0,0,u'Score statistics')
sheet1.write(1,0,u'Calligraphy score')
sheet1.write(2,0,u'Reading score')
sheet1.write(3,0,u'Speech score')
sheet1.write(4,0,u'Listening score')
temp = ['writing','reading','speaking','listening']
for pos,name in enumerate(mdict):
sheet1.write(0,pos+1,name)
for p,v in enumerate(temp): 
sheet1.write(p+1,pos+1,mdict[name][v])
f.save('Score statistics')

Count the total score of grape and monkey: add code before f.save:

sheet1.write(5,0,u'Total score statistics')
for i in range(len(mdict)):
forstr = chr(65+i+1)+'2+'+chr(65+i+1)+'3+'+chr(65+i+1)+'4+'+chr(65+i+1)+'5'
print forstr
 sheet1.write(5,i+1,xlwt.Formula(forstr))
 //The output is:
B2+B3+B4+B5
C2+C3+C4+C5

4. xlwt writes to a specific directory

Because of code layering, the whole framework of code is beautiful. We need to write files to a specific directory. But because there are no functions written directly to a specific directory in xlwt.

So use shutil.move Function to move the file MOV to a specific directory:

##coding=utf-8
import xlwt
import os
import shutil
path = '../sheet/'
isExists = os.path.exists(path) # Determine whether the directory exists
if not isExists:   # If the directory does not exist, create a new directory
os.makedirs(path)
f = xlwt.Workbook()  # Create Workbook
sheet1 = f.add_sheet(u'Score statistics', cell_overwrite_ok=True)
mdict = {"monkey":{"writing":80,"reading":60,"speaking":70,"listening":60},
"grape":{"writing":100,"reading":80,"speaking":70,"listening":60}}
sheet1.write(0,0,u'Score statistics')
sheet1.write(1,0,u'Calligraphy score')
sheet1.write(2,0,u'Reading score')
sheet1.write(3,0,u'Speech score')
sheet1.write(4,0,u'Listening score')
temp = ['writing','reading','speaking','listening']
for pos,name in enumerate(mdict):
sheet1.write(0,pos+1,name)
for p,v in enumerate(temp):
sheet1.write(p+1,pos+1,mdict[name][v])
sheet1.write(5,0,u'Total score statistics')
for i in range(len(mdict)):
forstr = chr(65+i+1)+'2+'+chr(65+i+1)+'3+'+chr(65+i+1)+'4+'+chr(65+i+1)+'5'
print forstr
sheet1.write(5,i+1,xlwt.Formula(forstr))
f.save('Score statistics')
shutil.move(u'Score statistics', path)