Python语法进阶（4）- Python办公自动化

2022/4/21 20:14:37

编程Tag： import 进阶 python PATH doc __ UTF 办公自动化 Accent

本文主要是介绍Python语法进阶（4）- Python办公自动化，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

1.Python文件自动化管理

shutil文件操作模块；glob文件与文件夹获取模块

1.1.文件操作利器-shutil

1.1.1.文件的复制

 1 # coding:utf-8
 2 import os
 3 from shutil import copy
 4 
 5 #相对路径
 6 copy('test.txt','test')
 7 
 8 #绝对路径
 9 path=os.path.join(os.getcwd(),'test1.txt')
10 target = os.path.join(os.getcwd(), 'test')
11 copy(path,target)
12 
13 #通过copy实现文件内容覆盖
14 copy('test.txt','test2.txt')

1.1.2.文件内容的覆盖

1 # coding:utf-8
2 import os
3 from shutil import copy,copyfile
4 
5 #相对路径
6 copyfile('test.txt','test1.txt')

1.1.3.文件裁剪（移动，重命名）

1 # coding:utf-8
2 
3 from shutil import move
4 
5 move('test2.txt','test/test22.txt')

1.1.4.文件的删除

1 # coding:utf-8
2 
3 import os
4 
5 os.remove('test1.txt')

1.1.5.文件/文件夹的压缩

1 # coding:utf-8
2 import os
3 from shutil import make_archive
4 
5 make_archive('test','zip',os.getcwd())

1.1.6.文件/文件夹的解压缩

1 # coding:utf-8
2 import os
3 from shutil import make_archive,unpack_archive
4 
5 unpack_archive('test.zip',os.path.join(os.getcwd(),'test2'))

1.2.文件夹操作利器-shutil

1.2.1.文件夹的复制

1 # coding:utf-8
2 
3 from shutil import copytree
4 
5 copytree('test','test2') #copytree目标目录不能存在，不然报错

1.2.2.文件夹的删除

1 # coding:utf-8
2 
3 from shutil import rmtree
4 
5 rmtree('test2')#删除的文件夹必须要存在，不然会报错，文件夹中不管有没有文件都可以直接删除

1.2.3.文件夹的裁剪(移动，重命名)

1 # coding:utf-8
2 
3 from shutil import move
4 
5 move('test1','test')#当目标路径不存在，并且和来源目录属于相同路径下，相当于重命名操作

1.3.文件内容的查找:glob

1.3.1.glob的介绍

1.3.2.glob的基本使用

 1 # coding:utf-8
 2 import os
 3 from glob import glob
 4 
 5 print(glob(os.getcwd()))    #['D:\\WorkSpace\\Python_Study\\filetest'] 只返回了当前路径，因为没有给范围
 6 
 7 result=glob(os.getcwd()+'/*')   #通过正则表达式来匹配
 8 #也可以模糊匹配 result=glob(os.getcwd()+'/*.txt')
 9 print(result)
10 '''
11 [
12 'D:\\WorkSpace\\Python_Study\\filetest\\file_test.py', 
13 'D:\\WorkSpace\\Python_Study\\filetest\\filrtree_test.py', 
14 'D:\\WorkSpace\\Python_Study\\filetest\\test', 
15 'D:\\WorkSpace\\Python_Study\\filetest\\test.txt', 
16 'D:\\WorkSpace\\Python_Study\\filetest\\test1', 
17 'D:\\WorkSpace\\Python_Study\\filetest\\__init_
18 '''

1.3.3.练习1：查找指定的文件

 1 # coding:utf-8
 2 
 3 import glob
 4 
 5 # 获取当前路径下所有内容
 6 # 判断每个内容的类型（文件夹还是文件）
 7 # 递归
 8 
 9 path= glob.os.path.join(glob.os.getcwd(),'*')
10 
11 def search(path,target):
12     result = glob.glob(path)
13     final_result=[]
14 
15     for data in result:
16         if glob.os.path.isdir(data):
17             _path=glob.os.path.join(data,"*")
18             search(_path,target)
19         else:
20             if target in data:
21                 final_result.append(data)
22     return final_result
23 
24 if __name__=='__main__':
25     result=search(path,target='.py')
26     print(result)
27 
28 '''
29 ['D:\\WorkSpace\\Python_Study\\filetest\\file_test.py', 
30 'D:\\WorkSpace\\Python_Study\\filetest\\filrtree_test.py', 
31 'D:\\WorkSpace\\Python_Study\\filetest\\__init__.py']
32 '''

1.3.4.练习2：查找含有指定内容的文件

 1 # coding:utf-8
 2 
 3 import glob
 4 
 5 path= glob.os.path.join(glob.os.getcwd(),'*')
 6 final_result=[]
 7 
 8 def search(path,target):
 9     result = glob.glob(path)
10 
11     for data in result:
12         if glob.os.path.isdir(data):
13             _path=glob.os.path.join(data,"*")
14             search(_path,target)
15         else:
16             try:
17                 with open(data,'r',encoding='utf-8') as f:
18                     content=f.read()
19                 if target in content:
20                     final_result.append(data)
21             except:
22                 print('data read failed:%s'% data)
23 
24     return final_result
25 
26 if __name__=='__main__':
27     result=search(path,target='你好')
28     print(result)
29 
30 '''
31 ['D:\\WorkSpace\\Python_Study\\filetest\\filrtree_test.py',
32  'D:\\WorkSpace\\Python_Study\\filetest\\test\\test22.txt', 
33  'D:\\WorkSpace\\Python_Study\\filetest\\test.txt']
34 '''

1.3.5.练习3：清理重复文件

 1 # coding:utf-8
 2 
 3 import glob
 4 import hashlib
 5 
 6 data={}
 7 def clear(path):
 8     result=glob.glob(path)
 9 
10     for _data in result:
11         if glob.os.path.isdir(_data):
12             _path=glob.os.path.join(_data,'*')
13             clear(_path)
14         else:
15             name=glob.os.path.split(_data)[-1]
16             if 'zip' in name:
17                 open_type='rb'
18                 is_byte=True
19             else:
20                 open_type='r'
21                 is_byte = False
22             with open(_data,open_type) as f:
23                 content = f.read()
24                 if is_byte:
25                     hash_content_obj = hashlib.md5(content)
26                 else:
27                     hash_content_obj = hashlib.md5(content.encode('utf-8'))
28                 hash_content = hash_content_obj.hexdigest()
29             if name in data:
30                 sub_data=data[name]
31                 is_delete=False
32                 for k,v in sub_data:
33                     if v == hash_content:
34                         glob.os.remove(_data)
35                         print('%s will delete' % _data)
36                         is_delete=True
37                 if not is_delete:
38                     data[name][_data]=hash_content
39             else:
40                 data[name]= {
41                     _data:hash_content
42                 }
43 
44 if __name__=='__main__':
45     path = glob.os.path.join(glob.os.getcwd(),'*')
46     clear(path)
47     print(data)

1.3.6.练习4：批量修改目录中的文件名称

 1 # coding:utf-8
 2 
 3 import glob
 4 import shutil
 5 
 6 
 7 def update_name(path):
 8     result = glob.glob(path)
 9 
10     for index,data in enumerate(result):
11         if glob.os.path.isdir(data):
12             _path = glob.os.path.join(data,'*')
13             update_name(_path)
14         else:
15             path_list=glob.os.path.split(data)
16             name=path_list[-1]
17             new_name='%s_%s' % (index,name)
18             new_data=glob.os.path.join(path_list[0],new_name)
19             shutil.move(data,new_data)
20 
21 if __name__=='__main__':
22     path = glob.os.path.join(glob.os.getcwd(),'*')
23     update_name(path)

2.Python Word自动化

2.1.Word操作利器之python-docx

2.1.1.python-docx的源头Document

2.1.2.段落的读取

 1 # coding:utf-8
 2 
 3 from docx import Document
 4 
 5 doc=Document('文本.docx')    #如果不是docx文件用不了，需要转格式即doc->docx
 6 print(doc.paragraphs)   #[<docx.text.paragraph.Paragraph object at 0x0000019CB5E75A48>, <docx.text.paragraph.Paragraph object at 0x0000019CB5E75F08>, <docx.text.paragraph.Paragraph object at 0x0000019CB5E75948>, <docx.text.paragraph.Paragraph object at 0x0000019CB5E75EC8>, <docx.text.paragraph.Paragraph object at 0x0000019CB5E75B48
 7 
 8 for p in doc.paragraphs:
 9     print(p.text)
10 '''
11 paragraphs是读取不到图片以及表格等信息的，因为读取的是段落
12 文本
13 文本
14 文本
15 文本
16 文本
17 ''

1 # coding:utf-8
2 
3 from docx import Document
4 #获取word总行数，并输出每一行内容
5 doc =Document('文本.docx')
6 leng_doc=len(doc.paragraphs)
7 print('段落数：%s' % leng_doc)
8 for i in range(leng_doc):
9     print('第{}段的内容是：{}'.format(i+1,doc.paragraphs[i].text))

2.1.3.表格的读取

 1 # coding:utf-8
 2 
 3 from docx import Document
 4 
 5 doc=Document('文本.docx')    #如果不是docx文件用不了，需要转格式即doc->docx
 6 
 7 #按行去取数据
 8 for t in doc.tables:  #拿到表格
 9     for row in t.rows:  #拿到表格的行
10         _row_str=''     #定义一个变量将一行的数据拼接在一起
11         for cell in row.cells:  #遍历一行中的小表格即列
12             _row_str+=cell.text+'\t'    #数据拼接
13         print(_row_str)
14 
15 '''
16 1-1    1-2    
17 2-1    2-2    
18 3-1    3-2    
19 '''
20 
21 # 按列去取数据
22 for t in doc.tables:  #拿到表格
23     for row in t.columns:  #拿到表格的行
24         _row_str=''     #定义一个变量将一行的数据拼接在一起
25         for cell in row.cells:  #遍历一行中的小表格即行
26             _row_str+=cell.text+'\t'    #数据拼接
27         print(_row_str)
28 '''
29 1-1    2-1    3-1    
30 1-2    2-2    3-2    
31 '''

2.1.4.练习：获取出有效简历-简历筛选

 1 # coding:utf-8
 2 
 3 import glob
 4 
 5 from docx import Document
 6 
 7 class ReadDoc(object):
 8     def __init__(self, path):
 9         self.doc = Document(path)
10         self.p_text = ''
11         self.table_text = ''
12 
13         self.get_para()
14         self.get_table()
15 
16     def get_para(self):
17         for p in self.doc.paragraphs:
18             self.p_text += p.text + '\n'
19 
20     def get_table(self):
21         for table in self.doc.tables:
22             for row in table.rows:
23                 _cell_str = ''
24                 for cell in row.cells:
25                     _cell_str += cell.text + ','
26                 self.table_text += _cell_str + '\n'
27 
28 
29 def search_word(path, targets):
30     result = glob.glob(path)
31     final_result = []
32 
33     for i in result:
34         isuse = True
35         if glob.os.path.isfile(i):
36             if i.endswith('.docx'):
37                 doc = ReadDoc(i)
38                 p_text = doc.p_text
39                 t_text = doc.table_text
40                 all_text = p_text + t_text
41 
42                 for target in targets:
43                     if target not in all_text:
44                         isuse = False
45                         break
46 
47                 if not isuse:
48                     continue
49                 final_result.append(i)
50     return final_result
51 
52 
53 if __name__ == '__main__':
54     path = glob.os.path.join(glob.os.getcwd(), '*')
55     res = search_word(path, ['简历', '1'])
56     print(res)

2.2.生成word文档

2.2.1.生成标题

 1 # coding:utf-8
 2 
 3 from docx import Document
 4 
 5 doc = Document()
 6 
 7 #定义标题内容
 8 title = doc.add_heading('My title',0)
 9 
10 #标题追加
11 title.add_run('\n123')
12 
13 #保存doc对象 word文档
14 doc.save('test.docx')

2.2.2.添加段落

 1 # coding:utf-8
 2 
 3 from docx import Document
 4 
 5 doc = Document()
 6 
 7 #定义标题内容
 8 title = doc.add_heading('My title',0)
 9 
10 #标题追加
11 title.add_run('\n123')
12 
13 #添加段落
14 p = doc.add_paragraph('这是段落内容')
15 #段落内容追加
16 p.add_run('\n这是追加的段落内容')
17 #保存doc对象 word文档
18 doc.save('test.docx')

2.2.3.添加图片

 1 # coding:utf-8
 2 
 3 from docx import Document
 4 from docx.shared import Inches
 5 
 6 doc = Document()
 7 
 8 #添加图片
 9 image=doc.add_picture('logo2020.png',width=Inches(3),height=Inches(2))
10 
11 #保存doc对象 word文档
12 doc.save('test.docx')

2.2.4.添加表格

 1 # coding:utf-8
 2 
 3 from docx import Document
 4 
 5 doc = Document()
 6 
 7 title = ['name','age','sex']
 8 
 9 table = doc.add_table(rows=1,cols=3)
10 title_cells = table.rows[0].cells
11 for i in range(len(title)):
12     title_cells[i].text=title[i]
13 
14 data = [
15     ('zhangsan','10','man'),
16     ('lisi','20','man'),
17     ('xiaofang','18','women')
18 ]
19 
20 for d in data:
21     row_cells = table.add_row().cells
22     row_cells[0].text = d[0]
23     row_cells[1].text = d[1]
24     row_cells[2].text = d[2]
25 
26 #保存doc对象 word文档
27 doc.save('test.docx')

2.2.5.分页

 1 # coding:utf-8
 2 
 3 from docx import Document
 4 
 5 doc = Document()
 6 
 7 doc.add_heading('第一页',0)
 8 
 9 #分页
10 doc.add_page_break()
11 
12 doc.add_heading('第二页',0)
13 #保存doc对象 word文档
14 doc.save('test.docx')

2.2.6.保存生成word

1 #保存doc对象 word文档
2 doc.save('test.docx')

2.3.word全局样式和文本样式

2.3.1.全局样式定义

 1 # coding:utf-8
 2 
 3 from docx import Document
 4 from docx.shared import Inches,RGBColor,Pt
 5 
 6 doc = Document()
 7 
 8 #style的这些操作不会对标题产生影响
 9 style = doc.styles['Normal']
10 style.font.name = '仿宋'  #字体类型
11 style.font.color.rgb = RGBColor(255,0,0)    #字体颜色：红色
12 style.font.size = Pt(30)    #字体大小：30
13 
14 #标题
15 doc.add_heading('第一页',0)
16 #段落
17 doc.add_paragraph('这是段落内容')
18 
19 #保存doc对象 word文档
20 doc.save('test.docx')

2.3.2.文本样式定义-标题与段落

 1 # coding:utf-8
 2 
 3 from docx import Document
 4 from docx.shared import Inches,RGBColor,Pt
 5 from docx.enum.text import WD_PARAGRAPH_ALIGNMENT
 6 
 7 doc = Document()
 8 
 9 #标题
10 title = doc.add_heading('第一页',0)
11 title.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER     #标题居中
12 title.style.font.size=Pt(30)    #标题字体大小
13 title.italic = True             #一开始的标题内容无法使其斜体格式生效，如果想要实现，那么我们在add_heading的时候传入一个空内容
14 _t = title.add_run('\n123')
15 _t.italic = True                #标题追加的内容，可以使其变成斜体
16 _t.bold = True                  #标题加粗，同样只能对追加的标题内容生效
17 _t.underline = True             #标题加下划线，同样只能对追加的标题内容生效
18 #段落
19 paragraph = doc.add_paragraph('这是段落内容')
20 paragraph.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER     #段落居中
21 paragraph.style.font.size=Pt(19)    #段落字体大小
22 paragraph.italic = True             #一开始的段落内容无法使其斜体格式生效，如果想要实现，那么我们在add_paragraph的时候传入一个空内容
23 _p = paragraph.add_run('\n456')
24 _p.italic = True                    #段落追加的内容，可以使其变成斜体
25 _p.bold = True                      #段落加粗，同样只能对追加的段落内容生效
26 _p.underline = True                 #段落加下划线，同样只能对追加的段落内容生效
27 
28 #通过dir(段落)和dir(标题)可以看到更多的样式方法
29 
30 #保存doc对象 word文档
31 doc.save('test.docx')

2.3.3.图片的居中

 1 # coding:utf-8
 2 
 3 from docx import Document
 4 from docx.shared import Inches
 5 from docx.enum.text import WD_ALIGN_PARAGRAPH
 6 
 7 doc = Document()
 8 
 9 #图片
10 p1 = doc.add_paragraph()
11 p1.alignment = WD_ALIGN_PARAGRAPH.RIGHT
12 _p1 = p1.add_run()
13 image_obj=_p1.add_picture('logo2020.png',width=Inches(3),height=Inches(2))
14 
15 #保存doc对象 word文档
16 doc.save('test.docx')
17 
18 print(dir(WD_ALIGN_PARAGRAPH))
19 '''
20 位置有哪些格式：
21 ['CENTER', 'DISTRIBUTE', 'JUSTIFY', 'JUSTIFY_HI', 'JUSTIFY_LOW', 'JUSTIFY_MED', 'LEFT', 'RIGHT', 
22 'THAI_JUSTIFY', '__class__', '__delattr__', '__dict__', '__dir__', '__doc__', '__docs_rst__', 
23 '__eq__', '__format__', '__ge__', '__getattribute__', '__gt__', '__hash__', '__init__', '__init_subclass__', '__l'
24 '''

2.3.4.表格的样式定义

  1 # coding:utf-8
  2 
  3 from docx import Document
  4 from docx.enum.style import WD_STYLE_TYPE
  5 
  6 doc = Document()
  7 
  8 for i in doc.styles:
  9     if i.type == WD_STYLE_TYPE.TABLE:
 10         print(i.name)       #打印所有的表格样式
 11 '''
 12 Normal Table
 13 Table Grid
 14 Light Shading
 15 Light Shading Accent 1
 16 Light Shading Accent 2
 17 Light Shading Accent 3
 18 Light Shading Accent 4
 19 Light Shading Accent 5
 20 Light Shading Accent 6
 21 Light List
 22 Light List Accent 1
 23 Light List Accent 2
 24 Light List Accent 3
 25 Light List Accent 4
 26 Light List Accent 5
 27 Light List Accent 6
 28 Light Grid
 29 Light Grid Accent 1
 30 Light Grid Accent 2
 31 Light Grid Accent 3
 32 Light Grid Accent 4
 33 Light Grid Accent 5
 34 Light Grid Accent 6
 35 Medium Shading 1
 36 Medium Shading 1 Accent 1
 37 Medium Shading 1 Accent 2
 38 Medium Shading 1 Accent 3
 39 Medium Shading 1 Accent 4
 40 Medium Shading 1 Accent 5
 41 Medium Shading 1 Accent 6
 42 Medium Shading 2
 43 Medium Shading 2 Accent 1
 44 Medium Shading 2 Accent 2
 45 Medium Shading 2 Accent 3
 46 Medium Shading 2 Accent 4
 47 Medium Shading 2 Accent 5
 48 Medium Shading 2 Accent 6
 49 Medium List 1
 50 Medium List 1 Accent 1
 51 Medium List 1 Accent 2
 52 Medium List 1 Accent 3
 53 Medium List 1 Accent 4
 54 Medium List 1 Accent 5
 55 Medium List 1 Accent 6
 56 Medium List 2
 57 Medium List 2 Accent 1
 58 Medium List 2 Accent 2
 59 Medium List 2 Accent 3
 60 Medium List 2 Accent 4
 61 Medium List 2 Accent 5
 62 Medium List 2 Accent 6
 63 Medium Grid 1
 64 Medium Grid 1 Accent 1
 65 Medium Grid 1 Accent 2
 66 Medium Grid 1 Accent 3
 67 Medium Grid 1 Accent 4
 68 Medium Grid 1 Accent 5
 69 Medium Grid 1 Accent 6
 70 Medium Grid 2
 71 Medium Grid 2 Accent 1
 72 Medium Grid 2 Accent 2
 73 Medium Grid 2 Accent 3
 74 Medium Grid 2 Accent 4
 75 Medium Grid 2 Accent 5
 76 Medium Grid 2 Accent 6
 77 Medium Grid 3
 78 Medium Grid 3 Accent 1
 79 Medium Grid 3 Accent 2
 80 Medium Grid 3 Accent 3
 81 Medium Grid 3 Accent 4
 82 Medium Grid 3 Accent 5
 83 Medium Grid 3 Accent 6
 84 Dark List
 85 Dark List Accent 1
 86 Dark List Accent 2
 87 Dark List Accent 3
 88 Dark List Accent 4
 89 Dark List Accent 5
 90 Dark List Accent 6
 91 Colorful Shading
 92 Colorful Shading Accent 1
 93 Colorful Shading Accent 2
 94 Colorful Shading Accent 3
 95 Colorful Shading Accent 4
 96 Colorful Shading Accent 5
 97 Colorful Shading Accent 6
 98 Colorful List
 99 Colorful List Accent 1
100 Colorful List Accent 2
101 Colorful List Accent 3
102 Colorful List Accent 4
103 Colorful List Accent 5
104 Colorful List Accent 6
105 Colorful Grid
106 Colorful Grid Accent 1
107 Colorful Grid Accent 2
108 Colorful Grid Accent 3
109 Colorful Grid Accent 4
110 Colorful Grid Accent 5
111 Colorful Grid Accent 6
112 '''
113 title = ['name','age','sex']
114 table = doc.add_table(rows=1,cols=3,style='Colorful List Accent 3')
115 
116 title_cells = table.rows[0].cells
117 for i in range(len(title)):
118     title_cells[i].text=title[i]
119 
120 data=[
121     ('zhangsan','10','man'),
122     ('lisi','20','man'),
123     ('xiaofang','18','women')
124 ]
125 
126 for d in data:
127     row_cells = table.add_row().cells
128     for i in range(len(data)):
129         row_cells[i].text = d[i]
130 #保存doc对象 word文档
131 doc.save('test.docx')

2.3.生成pdf文档

2.3.1.pdf工具包

下载wkhtmltopdf安装包，并且安装到电脑上
- 下载地址：https://wkhtmltopdf.org/downloads.html
在pycharm中安装wkhtmltopdf库：
- pip install wkhtmltopdf
在代码中指定configuration参数
- configuration=pdfkit.configuration(wkhtmltopdf=r'C:\Program Files\wkhtmltopdf\bin\wkhtmltopdf.exe' 或者设置环境变量：为系统变量path加入wkhtmltopdf安装目录的bin即可

2.3.2.html转pdf

2.3.3.网址转pdf

2.3.4.字符串生成pdf

 1 # coding:utf-8
 2 
 3 import pdfkit
 4 
 5 pdfkit.from_url('https://www.baidu.com','test1.pdf',configuration=pdfkit.configuration(wkhtmltopdf=r'C:\Program Files\wkhtmltopdf\bin\wkhtmltopdf.exe'))
 6 
 7 html='''
 8 <html>
 9 <head>
10 <meta charset="utf-8" />
11 </head>
12 <body>
13     <p>你好</p>
14 </body>
15 </html>
16 '''
17 pdfkit.from_string(html,'test2.pdf',configuration=pdfkit.configuration(wkhtmltopdf=r'C:\Program Files\wkhtmltopdf\bin\wkhtmltopdf.exe'))

2.3.5.word转html之pydocx

# coding:utf-8

import pdfkit
from pydocx import PyDocX

html = PyDocX.to_html('简历1.docx')
f = open('html1.html','w',encoding='utf-8')
f.write(html)
f.close()

#方法一
pdfkit.from_file('html1.html','test3.pdf',configuration=pdfkit.configuration(wkhtmltopdf=r'C:\Program Files\wkhtmltopdf\bin\wkhtmltopdf.exe'))
#方法二
pdfkit.from_string(html,'test4.pdf',configuration=pdfkit.configuration(wkhtmltopdf=r'C:\Program Files\wkhtmltopdf\bin\wkhtmltopdf.exe'))

3.Python Excel自动化

3.1.Excel读取之xlrd

3.1.1.xlrd的安装

3.1.2.常用函数介绍

 1 # coding:utf-8
 2 
 3 import xlrd
 4 
 5 excel = xlrd.open_workbook('study.xlsx')
 6 
 7 book = excel.sheet_by_name('学生手册')
 8 print(book)     #<xlrd.sheet.Sheet object at 0x000001F48391D708>
 9 
10 book = excel.sheet_by_index(0)
11 print(book.name)    #打印sheet的名字，学生手册
12 
13 for i in excel.sheets():    #打印excel所有的sheet
14     print(i.name)           #学生手册

3.1.3.读取工作薄内容

 1 # coding:utf-8
 2 
 3 import xlrd
 4 
 5 excel = xlrd.open_workbook('study.xlsx')
 6 
 7 book = excel.sheet_by_name('学生手册')
 8 print(book)     #<xlrd.sheet.Sheet object at 0x000001F48391D708>
 9 
10 book = excel.sheet_by_index(0)
11 print(book.name)    #打印sheet的名字，学生手册
12 
13 for i in excel.sheets():    #打印excel所有的sheet
14     print(i.name)           #学生手册
15 
16 print(book.nrows)   #行数：6行
17 print(book.ncols)   #列数：5列
18 
19 for i in book.get_rows():
20     print(i)    #里面每一个键值对都是一个对象，[text:'姓名', text:'性别', text:'年龄', text:'成绩', text:'等级']
21     content = []
22     for j in i:
23         content.append(j.value) 
24     print(content)  #['姓名', '性别', '年龄', '成绩', '等级']

3.2.Excel写之xlsxwriter

xlsxwriter 只能创建新的文件(覆盖)，不能对文件进行数据的操作，往后的随笔到数据分析可以使用pandas很好的完成追加

3.2.1.xlsxwriter的安装

3.2.2.常用函数介绍

 1 # coding:utf-8
 2 
 3 import xlsxwriter  # pip install xlsxwriter
 4 
 5 excel = xlsxwriter.Workbook('write1.xlsx')
 6 book = excel.add_worksheet('study')
 7 
 8 title = ['姓名', '性别', '年龄', '成绩', '等级']
 9 
10 for index,data in enumerate(title):
11     book.write(0,index,data)
12 excel.close()

 1 # coding:utf-8
 2 
 3 import xlsxwriter  # pip install xlsxwriter
 4 import xlrd
 5 
 6 #excel 文件之间相互复制
 7 def read():
 8     result = []
 9     excel = xlrd.open_workbook('study.xlsx')
10     book = excel.sheet_by_name('学生手册')
11     for i in book.get_rows():
12         content=[]
13         for j in i:
14             content.append(j.value)
15         result.append(content)
16     return result
17 
18 
19 def write(content):
20     excel = xlsxwriter.Workbook('write1.xlsx')
21     book = excel.add_worksheet('study')
22 
23     for index,data in enumerate(content):
24         for sub_index,sub_data in enumerate(data):
25             book.write(index,sub_index,sub_data)
26     excel.close()
27 
28 if __name__=='__main__':
29     result = read()
30     write(result)

3.2.3.生成图表

 1 # coding:utf-8
 2 
 3 import xlsxwriter  # pip install xlsxwriter
 4 import xlrd
 5 
 6 #excel 文件之间相互复制
 7 def read():
 8     result = []
 9     excel = xlrd.open_workbook('study.xlsx')
10     book = excel.sheet_by_name('学生手册')
11     for i in book.get_rows():
12         content=[]
13         for j in i:
14             content.append(j.value)
15         result.append(content)
16     return result
17 
18 
19 def write(content):
20     excel = xlsxwriter.Workbook('write1.xlsx')
21     book = excel.add_worksheet('study')
22 
23     for index,data in enumerate(content):
24         for sub_index,sub_data in enumerate(data):
25             book.write(index,sub_index,sub_data)
26 
27     book1 = excel.add_worksheet('学生等级')
28     #生成图表
29     data = [
30         ['优秀', '良好', '中', '差'],
31         [1100, 2000, 1000, 900]
32     ]
33     book1.write_column('A1',data[0])
34     book1.write_column('B1',data[1])
35 
36     chart = excel.add_chart({'type':'column'})
37     chart.add_series({
38         'categories': '=学生等级!$A1:$A4',
39         'values': '=学生等级!$B1:$B4',
40         'name': '成绩占比'
41     })
42     chart.set_title({'name': '成绩占比图表'})
43     #将图表插入到A10位置
44     book1.insert_chart('A10', chart)
45 
46     excel.close()
47 
48 if __name__=='__main__':
49     result = read()
50     write(result)

4.PPT自动化

4.1.PPT的创建

4.1.1.python-pptx的安装

4.1.2.创建空ppt对象

 1 # coding:utf-8
 2 
 3 import pptx  # pip install python-pptx
 4 
 5 p = pptx.Presentation()  # 生成ppt对象
 6 
 7 layout = p.slide_layouts[1]  # 选择布局
 8 
 9 # 0 title
10 # 1 title content
11 # 7
12 slide = p.slides.add_slide(layout)
13 
14 p.save('test1.ppt')

4.2.PPT段落的使用

4.2.1.获取段落

4.2.2.段落添加内容

 1 # coding:utf-8
 2 
 3 import pptx
 4 
 5 p = pptx.Presentation()
 6 layout = p.slide_layouts[1]  # title content
 7 slide = p.slides.add_slide(layout)
 8 
 9 placeholder = slide.placeholders[1] # 0 title 1 content
10 placeholder.text = '欢迎学习ppt制作\n欢迎学习python'
11 
12 
13 
14 title = slide.placeholders[0]
15 title.text = '题目'
16 p.save('test2.ppt')

4.2.3.段落中定义多个段落

 1 # coding:utf-8
 2 
 3 import pptx
 4 from pptx.util import Pt
 5 from pptx.enum.text import PP_PARAGRAPH_ALIGNMENT
 6 
 7 p = pptx.Presentation()
 8 layout = p.slide_layouts[1]  # title content
 9 slide = p.slides.add_slide(layout)
10 
11 placeholder = slide.placeholders[1] # 0 title 1 content
12 
13 
14 title = slide.placeholders[0]
15 title.text = '题目'
16 
17 paragraph1 = placeholder.text_frame.add_paragraph()
18 paragraph1.text = '欢迎学习ppt制作'
19 paragraph1.bold = True
20 paragraph1.font.italic = True
21 paragraph1.font.size = Pt(16)
22 paragraph1.font.underline = True
23 paragraph1.alignment = PP_PARAGRAPH_ALIGNMENT.CENTER
24 
25 
26 paragraph2 = placeholder.text_frame.add_paragraph()
27 paragraph2.text = '欢迎学习python'
28 paragraph2.font.size = Pt(32)
29 paragraph2.alignment = PP_PARAGRAPH_ALIGNMENT.RIGHT
30 
31 p.save('test2.ppt')

4.2.4.自定义段落

 1 # coding:utf-8
 2 
 3 import pptx
 4 from pptx.util import Pt, Inches
 5 from pptx.enum.text import PP_PARAGRAPH_ALIGNMENT
 6 from pptx.dml.color import RGBColor
 7 
 8 p = pptx.Presentation()
 9 layout = p.slide_layouts[6]  # 只有一个title，没有段落
10 slide = p.slides.add_slide(layout)
11 left = top = width = height = Inches(5)
12 box = slide.shapes.add_textbox(left, top, width, height)
13 para = box.text_frame.add_paragraph()
14 
15 para.text = 'this is a para test'
16 para.alignment = PP_PARAGRAPH_ALIGNMENT.CENTER
17 para.font.size = Pt(32)
18 para.font.color.rgb = RGBColor(255, 255, 0)
19 para.font.name = '微软雅黑'
20 
21 p.save('test2.ppt')

4.3.PPT插入表格

 1 # coding:utf-8
 2 
 3 import pptx
 4 from pptx.util import Inches
 5 
 6 p = pptx.Presentation()
 7 layout = p.slide_layouts[1]
 8 slide = p.slides.add_slide(layout)
 9 
10 rows = 10
11 cols = 2
12 
13 left = top = Inches(2)
14 width = Inches(6.0)
15 height = Inches(1.0)
16 
17 table = slide.shapes.add_table(rows, cols, left, top, width, height).table
18 
19 for index, _ in enumerate(range(rows)):
20     for sub_index in range(cols):
21         table.cell(index, sub_index).text = '%s:%s' % (index, sub_index)
22 
23 p.save('test2.ppt')

4.4.PPT插入图片

 1 # coding:utf-8
 2 
 3 import pptx
 4 from pptx.util import Inches
 5 
 6 p = pptx.Presentation()
 7 layout = p.slide_layouts[6]
 8 slide = p.slides.add_slide(layout)
 9 
10 image = slide.shapes.add_picture(
11     image_file='logo2020.png',
12     left=Inches(1),
13     top=Inches(1),
14     width=Inches(6),
15     height=Inches(4)
16 )
17 
18 p.save('test2.ppt')

4.5.读取PPT

 1 # coding:utf-8
 2 
 3 import pptx
 4 
 5 p = pptx.Presentation('test2.ppt')
 6 for slide in p.slides:
 7     for shape in slide.shapes:
 8         if shape.has_text_frame:
 9             print(shape.text_frame.text)
10         if shape.has_table:
11             for cell in shape.table.iter_cells():
12                 print(cell.text)

5.Python邮件自动化

5.1.发送邮件

 1 # coding:utf-8
 2 
 3 #发送qq邮件
 4 import smtplib
 5 
 6 from email.mime.text import MIMEText
 7 from email.header import Header
 8 
 9 
10 #第三方的smtp
11 mail_host = 'smtp.qq.com'       #smtp服务器地址
12 mail_user = '34503'         #用户名
13 mail_pass = 'cqsahmvsf假的看自己的'  #smtp 开通， 授权码
14 
15 sender = '34503@qq.com'     #发送人
16 receivers = ['gei@are.cc'] #接收者邮箱
17 
18 mesage = MIMEText('这是一个测试','plain','utf-8')     #定义邮件发送内容的对象，编码格式为utf-8，邮件类型为文本类型的plain普通邮件，邮件内容为：这是一个测试
19 
20 mesage['Form'] = Header(sender)     #邮件发送内容对象Form属性：是谁发送的
21 mesage['Subject'] = Header('python脚本测试','utf-8')    #邮件发送内容对象Subject属性：邮件标题
22 
23 smtpobj = smtplib.SMTP()    #创建smtp协议对象
24 smtpobj.connect(mail_host,25)   #协议链接到服务地址，服务地址端口
25 smtpobj.login(mail_user,mail_pass)  #登录
26 smtpobj.sendmail(sender,receivers,mesage.as_string())   #发送，发送者，接收者，邮件对象通过as_string进行加密

5.2.发送html邮件

 1 # coding:utf-8
 2 
 3 import smtplib
 4 
 5 from email.mime.text import MIMEText
 6 from email.header import Header
 7 
 8 
 9 #第三方的smtp
10 mail_host = 'smtp.qq.com'       #smtp服务器地址
11 mail_user = '34503'         #用户名
12 mail_pass = 'cqsahmvsfl'  #smtp 开通， 授权码
13 
14 sender = '34503@qq.com'     #发送人
15 receivers = ['gei@are.cc'] #接收者邮箱
16 
17 mesage = MIMEText('<p style="color:red;">这是二个测试</p>', 'html', 'utf-8')     #定义邮件发送内容的对象，编码格式为utf-8，邮件类型为html类型，邮件内容为：这是二个测试
18 
19 mesage['Form'] = Header(sender)     #邮件发送内容对象Form属性：是谁发送的
20 mesage['Subject'] = Header('python脚本测试','utf-8')    #邮件发送内容对象Subject属性：邮件标题
21 
22 smtpobj = smtplib.SMTP()    #创建smtp协议对象
23 smtpobj.connect(mail_host,25)   #协议链接到服务地址，服务地址端口
24 smtpobj.login(mail_user,mail_pass)  #登录
25 smtpobj.sendmail(sender,receivers,mesage.as_string())   #发送，发送者，接收者，邮件对象通过as_string进行加密

5.3.发送带附件的邮件

 1 # coding:utf-8
 2 
 3 import smtplib
 4 
 5 from email.mime.text import MIMEText
 6 from email.header import Header
 7 from email.mime.multipart import MIMEMultipart
 8 
 9 #第三方的smtp
10 mail_host = 'smtp.qq.com'       #smtp服务器地址
11 mail_user = '34503'         #用户名
12 mail_pass = 'cqsahmvsfl'  #smtp 开通， 授权码
13 
14 sender = '34503@qq.com'     #发送人
15 receivers = ['gei@are.cc'] #接收者邮箱
16 
17 message = MIMEMultipart()
18 message['Form'] = Header(sender)     #邮件发送内容对象Form属性：是谁发送的
19 message['Subject'] = Header('python脚本测试','utf-8')    #邮件发送内容对象Subject属性：邮件标题
20 
21 attr = MIMEText(open('send.py','rb').read(),'base64','utf-8')
22 attr['Content-Type'] = 'application/octet-stream'
23 attr['Content-Disposition'] = 'attachment;filename="send.py"'
24 
25 message.attach(attr)
26 message.attach(MIMEText('这是一个带附件的邮件', 'plain', 'utf-8'))
27 
28 smtpobj = smtplib.SMTP()    #创建smtp协议对象
29 smtpobj.connect(mail_host,25)   #协议链接到服务地址，服务地址端口
30 smtpobj.login(mail_user,mail_pass)  #登录
31 smtpobj.sendmail(sender,receivers,message.as_string())   #发送，发送者，接收者，邮件对象通过as_string进行加密

5.4.定时发送邮件

 1 # coding:utf-8
 2 
 3 import smtplib
 4 import schedule
 5 
 6 from email.mime.text import MIMEText
 7 from email.header import Header
 8 from email.mime.multipart import MIMEMultipart
 9 from time import sleep
10 #第三方的smtp
11 mail_host = 'smtp.qq.com'       #smtp服务器地址
12 mail_user = '34503'         #用户名
13 mail_pass = 'cqsahmvsfl'  #smtp 开通， 授权码
14 
15 sender = '34503@qq.com'     #发送人
16 receivers = ['gei@are.cc'] #接收者邮箱
17 
18 message = MIMEMultipart()
19 message['Form'] = Header(sender)     #邮件发送内容对象Form属性：是谁发送的
20 message['Subject'] = Header('python脚本测试','utf-8')    #邮件发送内容对象Subject属性：邮件标题
21 
22 attr = MIMEText(open('send.py','rb').read(),'base64','utf-8')
23 attr['Content-Type'] = 'application/octet-stream'
24 attr['Content-Disposition'] = 'attachment;filename="send.py"'
25 
26 message.attach(attr)
27 message.attach(MIMEText('这是一个带附件的邮件', 'plain', 'utf-8'))
28 
29 def send():
30     try:
31         smtpobj = smtplib.SMTP()    #创建smtp协议对象
32         smtpobj.connect(mail_host,25)   #协议链接到服务地址，服务地址端口
33         smtpobj.login(mail_user,mail_pass)  #登录
34         smtpobj.sendmail(sender,receivers,message.as_string())   #发送，发送者，接收者，邮件对象通过as_string进行加密
35     except smtplib.SMTPException as e:
36         print('error: %s' %e)
37 
38 
39 if __name__=='__main__':
40     schedule.every(10).seconds.do(send)
41 
42     while 1:
43         schedule.run_pending()
44         sleep(1)

这篇关于Python语法进阶（4）- Python办公自动化的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！