Using Python for report automation ~

Using Python for report automation ~

This article will cover:

• XLWT Common functions
• XLRD Common functions
• Xlutils Common functions
• XLWT application of formulas when writing Excel
• XLWT writing to a specific directory (path setting)

In the XLWT Python language, an extension tool that writes to an Excel file. You can implement a specified form, a specified cell write. Supports version excel03 to version excel2013. Make sure you have the Python environment installed when you use it. Baidu Encyclopedia

In the Xlrd Python language, read the extension tool for Excel. You can implement a read of a specified form, a specified cell. Make sure you have the Python environment installed when you use it. Baidu Encyclopedia

NOTICE:

• XLWT only write-only operations on Excel
• xrrd only read-only operations on Excel

In addition, there are xlutils.copy to enable the replication and re-editing of Excel.

1.python write EXCEL-XLWT Common functions a. Preparatory work

• Install XLWT : Enter pip install XLWT or Easy_install XLWT in the terminal
• Introducing the XLWT package :

import xlwt  # 写

B. Basic Tutorials

• Create a new workbook & Add sheet: Create a new workbook, and sheet

f = xlwt.Workbook()  # 创建工作簿sheet1 = f.add_sheet(u‘sheet1‘, cell_overwrite_ok=True)#一个excel表格中可以添加多个sheet

• Write content to sheet: The sheet.write function can pass three parameters
  Section I (parameter 1) j (parameter 2) column deposit contents (parameter 3)

‘第i行第j列存放此内容‘, style)# 这条语句实现的功能就是往第i行第j列存第三个参数的内容，第四个参数是样式(如字体，背景)，可以不传第四个参数。

• Merge cells and write content:

‘内容‘, style)# 这条y语句表示将[x:x+m]行[y:y+n]列的矩阵合并成一个单元格。存放第五个参数的内容，同理，style参数可以不传参

• Finally use f.save (' demo ')
  You can save F to Excel.

C. Actual combat

• We can create a new workbook, Add two sheetto it, and see the effect

#coding=utf-8import xlwtf = xlwt.Workbook()  # 创建工作簿sheet1 = f.add_sheet(u‘葡小萄‘, cell_overwrite_ok=True)sheet2 = f.add_sheet(u‘小葡萄‘, cell_overwrite_ok=True)f.save(‘xlwt_tutorial‘)

• The results are as follows, found in table xlwt_tutorial with two sheet.

• We start writing to sheet without passing in the style parameter
  Use only the write function first

#coding =utf-8ImportXLWTF = XLWT. Workbook ()# Create workbooksSheet1 = F.add_sheet (u ' Portuguese little grapes ', cell_overwrite_ok=True) Sheet2 = F.add_sheet (u ' Little grapes ', cell_overwrite_ok=True) row =0temp = [u ' name ',u ' age ',u ' School ',u ' professional '] forPos,vinchEnumerate (temp): Sheet1.write (row,pos,v) Row + =1Sheet1.write (Row,0,u ' grapes ') Sheet1.write (Row,1, -) Sheet1.write (Row,2,u ' Beijing Film Academy ') Row + =1Sheet1.write (Row,0,u ' Coconut ') Sheet1.write (Row,1, -) Sheet1.write (Row,2,u ' Imperial King University of Technology ') F.save (' xlwt_tutorial ')

• The results are as follows, and we have created a table with 3 rows and 4 columns. (The Write function row and column values are all starting from 0)

• Below we use the write_merge function to merge cells and write
  Add a line of code before F.save

sheet1.write_merge(1,2,3,3,u‘汉语言文学‘)

• The effect is as follows, merging the 4th column of the 第2-3 row
  

2.PYTHONXD read EXCEL-XLRD Common functions a. Preparatory work

• Install xlrd : Enter pip install XLRD or Easy_install xlrd in the terminal
• Introducing the XLRD package :

import xlrd  # 读

B. Basic Tutorials & Actual combat

• Open an Excel, and then output all sheet names

#coding=utf-8import xlrdimport unioutf = xlrd.open_workbook(r‘xlwt_tutorial‘)print f.sheet_names()

Output: [u ' Portuguese grape ', U ' Little Grapes ']

• Get all the sheet in the form.

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

• Get the content in sheet

f = xlrd.open_workbook(r‘xlwt_tutorial‘)sheet1 = f.sheet_by_index(0)  #打开第一个sheetsheet2 = f.sheet_by_name(u‘小葡萄‘)  #打开名字为小葡萄的sheet#输出sheet的名称,行数,列数print sheet1.name,sheet1.nrows,sheet1.ncolsprint sheet2.name,sheet2.nrows,sheet2.ncols

The output is:
Portuguese Small Grape 3 4
Small Grapes 0 0
.

print sheet1.row_values(1)  #获取第二行内容print sheet1.col_values(2)  #获取第三列内容

The output is:
[u ' Grape ', 18.0, U ' Beijing Film Academy ', U ' Chinese Language and literature ']
[u ' School ', U ' Beijing Film Academy ', U ' Imperial King Tech University ']
.

# 获取单元格内容print sheet1.cell(1,0).value# 获取单元格内容的数据类型print sheet1.cell(1,1).ctype#ctype : 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error

The output is:
Grape
2

3.xlutils common functions A. Preparatory work

• Install xlutils : Enter pip install Xlutils or Easy_install xlutils in the terminal
• Introducing the Xlutils package :

import xlutils  

copy function in B.xlutils

We may have a problem and want to edit a stored Excel .
However, XLRD is a read-only mode and cannot be written.
While XLWT is write-only mode, it cannot be read into an Excel file for editing.
We can use xlrd to open a document, then use the copy function in xlutils to copy the document and then edit it.

import xlrdfromimport copyf = xlrd.open_workbook(r‘xlwt_tutorial‘# 将f拷贝到wbsheet1 = wb.get_sheet(0# 打开sheetprint sheet1.namesheet1.write(3,0,‘change‘)wb.save(‘xlwt_tutorial‘)

The output is:
Portuguese small grapes
The output table has changed.

PS: You can see that the second row, the fourth column and the third row, the fourth column, have been destroyed at copy time.

Application of formulas in 4.XLWT writing Excel

We write a form with XLWT.

#coding =utf-8ImportXLWTF = XLWT. Workbook ()# Create workbooksSheet1 = F.add_sheet (u ' score Stats ', cell_overwrite_ok=True) mdict = {"Monkey":{"Writing": the,"reading": -,"Speaking": -,"Listening": -},"Grape":{"Writing": -,"reading": the,"Speaking": -,"Listening": -}}sheet1.write (0,0,u ' score Stats ') 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 ' hearing score ') TEMP = [' writing ',' reading ',' speaking ',' listening '] forPos,nameinchEnumerate (mdict): Sheet1.write (0, pos+1, name) forP,vinchEnumerate (temp): Sheet1.write (p+1, pos+1, Mdict[name][v]) F.save (' Scoring Stats ')

Open the table as:

What we want to do now is to count the total score of grape and the total score of monkey:
Add the code before F.save:

sheet1.write (5 , Span class= "Hljs-number" >0 , u ' total Statistics ' ) for  i in  range (len (mdict)): Forstr = Chr (65  +i +1 ) + ' '  +chr (65  +i+1 ) + ' all in all '  +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
Open the table as:

5.XLWT writing to a specific directory (path setting)

Because of the code layering, the overall framework of the code is beautiful.
We need to write the file to a specific directory.
However, because there are no functions directly written to a specific directory in XLWT.
So use the Shutil.move function to put the file mov into a specific directory:

#coding =utf-8ImportXlwtImportOsImportShutilpath ='. /sheet/'isexists = os.path.exists (path)# Determine if the directory existsif  notIsexists:# If the directory does not exist, create a new directoryOs.makedirs (path) f = xlwt. Workbook ()# Create workbooksSheet1 = F.add_sheet (u ' score Stats ', cell_overwrite_ok=True) mdict = {"Monkey":{"Writing": the,"reading": -,"Speaking": -,"Listening": -},"Grape":{"Writing": -,"reading": the,"Speaking": -,"Listening": -}}sheet1.write (0,0,u ' score Stats ') 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 ' hearing score ') TEMP = [' writing ',' reading ',' speaking ',' listening '] forPos,nameinchEnumerate (mdict): Sheet1.write (0, pos+1, name) forP,vinchEnumerate (temp): Sheet1.write (p+1, pos+1, Mdict[name][v]) Sheet1.write (5,0,u ' Total statistics ') forIinchRange (len (mdict)): Forstr = Chr ( $+i+1)+' the '+CHR ( $+i+1)+' in the '+CHR ( $+i+1)+' 4+ '+CHR ( $+i+1)+' 5 '    PrintForstr Sheet1.write (5, i+1, XLWT. Formula (FORSTR)) F.save (' Scoring Stats ') Shutil.move (u ' score Stats ', path)

：

Resources

• Use XLRD, XLWT to manipulate Excel tables in Python

• Python: Using third-party library XLWT to write Excel

• Python file, directory operation (new, move, delete, etc.)

• XLWT save file can be saved in the specified directory?

Using Python for report automation ~