办公笔记
Excel学习
初步使用
安装模块
pip install xlwt
import xlwt
#创建一个excel
wb=xlwt.Workbook()
#选择工作薄
sh=wb.add_sheet('电影')
#写入数据到单元格 第一个数据为行,第二个数据为列,从0开始计算
sh.write(0,0,'机器人')
#保存excel
wb.save('电影数据.xls')
读取数据
安装模块
pip install xlrt
import xlrd
#打开excel
wb=xlrd.open_workbook('./电影数据.xls')
# print(wb)
print(f'excel中有{wb.nsheets}个工作薄')
print(f'excel中sheets的名字:{wb.sheet_names()}')
#选择工作薄
index=wb.sheet_by_index(0)
name=wb.sheet_by_name('电影')
print(index)
print(name)
print(f'sheet里面一共有{index.nrows}行{name.ncols}列的数据')
#获取单元格的值
print(f'第一行第一列的值:{index.cell_value(0,0)}')
print(f'第一行第一列的值:{index.cell(0,0).value}')
print(f'第一行第一列的值:{index.row(0)[0].value}')
#获取整行或整列数据
print(index.row_values(0))
print(index.col_values(0))
#遍历所有数据
for r in range(index.nrows):
for c in range(index.ncols):
print(f'第{r}行,第{c}列的数据是{index.cell_value(r,c)}')
修改数据
安装模块
pip install xlutils
import xlrd
from xlutils.copy import copy
#打开excel
read_book=xlrd.open_workbook('./电影数据.xls')
#复制数据
wb=copy(read_book)
#选择工作薄
sh=wb.get_sheet(0)
sh.write(1,1,'海绵宝宝')
#增加工作薄
sh2=wb.add_sheet('new_电影')
count=0
rs=read_book.sheet_by_index(0)
print(rs.nrows)
for i in range(0,rs.nrows):
num=rs.cell_value(i,0)
print(num)
count=i+1
sh2.write(0,0,'总计')
sh2.write(0,1,count)
wb.save('./new_电影数据.xls')
设置样式
import xlwt
wb=xlwt.Workbook()
sh=wb.add_sheet('数据')
ft=xlwt.Font()
ft.name='宋体'#设置字体
ft.colour_index=2#设置颜色
ft.height=11*20#字体大小
ft.bold=True
ft.underline=True
ft.italic=True
style=xlwt.XFStyle()
style.font=ft
#设置单元格高度
sh.row(2).height_mismatch=True
sh.row(2).height=5*256
#设置单元格宽度
sh.col(3).width=5*256
#对齐方式
alg=xlwt.Alignment()
alg.horz=2#1 左 2 中 3右
alg.vert=1#0 上 1 中 2下
style2=xlwt.XFStyle()
style2.alignment=alg
#设置边框
border=xlwt.Borders()
#细实线:1,小粗实线:2,细虚线;3,中细虚线:4,大粗实线:5,双线:6,细点虚线:7
#大粗虚线:8,细点划线:9,粗点划线:10,细双点划线:11,粗双点划线:12,斜点划线:13
border.left=4
border.right=4
border.top=4
border.bottom=4
border.left_colour=1
border.right_colour=2
border.top_colour=3
border.bottom_colour=4
style3=xlwt.XFStyle()
style3.borders=border
#设置背景颜色
pattern=xlwt.Pattern()
pattern.pattern=xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour=5
style4=xlwt.XFStyle()
style4.pattern=pattern
style5=xlwt.easyxf('font: bold on,color_index 6;align:vert center,horiz center')
sh.write(0,0,'吕布')
sh.write(1,1,'吕布',style)
sh.write(2,2,'刘备')
sh.write(2,3,'刘备',style2)
sh.write(3,3,'张飞',style3)
sh.write(3,4,'关羽',style4)
sh.write(4,4,'诸葛亮',style5)
wb.save('样式练习.xls')
汇总数据练习
import xlrd
from xlutils.copy import copy
def read_data():
'''
:return: 各分类的总价值,每个单品的总价值
'''
wb=xlrd.open_workbook('./data.xls')
sh=wb.sheet_by_index(0)
fen_type={}#{A:110,B:12}
count_price=[]#[1,2,3]
for r in range(sh.nrows):
count=sh.cell_value(r,3)*sh.cell_value(r,4)
count_price.append(count)
key=sh.cell_value(r,0)
if fen_type.get(key):
fen_type[key]+=count
else:
fen_type[key]=count
return fen_type,count_price
def save(fen,count):
wb=xlrd.open_workbook('./data.xls')
sh_t=wb.sheet_by_index(0)
wb2=copy(wb)
sh=wb2.get_sheet(0)
for r in range(sh_t.nrows):
sh.write(r,sh_t.ncols,count[r])
sh2=wb2.add_sheet('汇总的数据')
for i,key in enumerate(fen.keys()):
sh2.write(i,0,key)
sh2.write(i,1,fen.get(key))
wb2.save('./new_data.xls')
if __name__ == '__main__':
f,c=read_data()
save(f,c)
表格拆分
import xlrd
from xlutils.copy import copy
def get_data():
wb = xlrd.open_workbook('./data.xls')
sh = wb.sheet_by_index(0)
'''
all_data={
a:[{},{},{}]
}
'''
all_data = {}
for r in range(sh.nrows):
d = {'type': sh.cell_value(r, 1), 'name': sh.cell_value(r, 2), 'count': sh.cell_value(r, 3),
'price': sh.cell_value(r, 4)}
key = sh.cell_value(r, 0)
if all_data.get(key):
all_data[key].append(d)
else:
all_data[key] = [d]
return all_data
def save(data):
wb=xlrd.open_workbook('./data.xls')
wb2=copy(wb)
for key in data.keys():
temp_sheet=wb2.add_sheet(key)
for i,d in enumerate(data.get(key)):
temp_sheet.write(i,0,d.get('key'))
temp_sheet.write(i,1,d.get('name'))
temp_sheet.write(i,2,d.get('count'))
temp_sheet.write(i,3,d.get('price'))
wb2.save('./表格拆分.xls')
if __name__ == '__main__':
data=get_data()
save(data)
openpyxl的使用
安装模块
pip install openpyxl
import os
from openpyxl import load_workbook, Workbook
from openpyxl.styles import Font, Alignment, colors,PatternFill
from openpyxl.chart import LineChart, Reference
from datetime import date,datetime
class Openpyxl_Study:
def __init__(self):
self.wb = load_workbook('./data.xlsx')
self.wb = Workbook()
def open(self):
# 获取工作薄
sh1 = self.wb.active
sh2 = self.wb['Sheet1']
sh3 = self.wb.get_sheet_by_name('Sheet1')
print(sh1 is sh2 is sh3)
def show_sheets(self):
"""
显示所有的工作薄
:return:
"""
print(self.wb.sheetnames)
for sh in self.wb:
print(sh.title)
def get_one_value(self):
"""
获取单个值
:return:
"""
sh1 = self.wb.active
value1 = sh1.cell(1, 2).value
value2 = sh1['c2'].value
print(value1)
print(value2)
def get_many_value(self):
"""
获取多个值
:return:
"""
sh = self.wb.active
# 切片
cells1 = sh['a1':'c1']
# print(cells1)
# 整行,整列
cell_row3 = sh[2]
cell_col3 = sh['b']
# print(cell_row3)
# print(cell_col3)
# 通过迭代获取数据
for row in sh.iter_rows(min_row=2, max_row=5, max_col=3):
for cell in row:
print(cell.value)
def get_all_data(self):
"""
获取所有数据
:return:
"""
sh = self.wb.active
for row in sh.rows:
for cell in row:
print(cell.value)
def get_num(self):
"""
获取行,列总数
:return:
"""
sh = self.wb.active
print(sh.max_row)
print(sh.max_column)
def new_excel(self):
"""
创建excel
:return:
"""
sh1 = self.wb.active
sh2 = self.wb.create_sheet('数据')
sh3 = self.wb.create_sheet('人员', 0)
self.wb.save('openpyxl创建.xlsx')
def set_value(self):
"""
添加数据,设置样式
:return:
"""
bold_italic_30_font = Font(name='宋体', size=30, italic=True, bold=True, color=colors.BLUE)
sh1 = self.wb.active
sh1['A1'] = '你好'
sh1['B2'] = 'Excel'
sh1['B2'].font = bold_italic_30_font
self.wb.save('openpyxl创建.xlsx')
def set_value2(self):
sh1 = self.wb.active
data = ['pthon', 'java', 'c', 'php']
for i, d in enumerate(data):
sh1.cell(i + 1, 1).value = d
self.wb.save('添加数据.xlsx')
def set_style(self):
"""
行,列设置
:return:
"""
sh1 = self.wb.active
sh1.row_dimensions[1].height = 30
sh1.column_dimensions['A'].width = 30
data = ['pthon', 'java', 'c', 'php']
for i, d in enumerate(data):
sh1.cell(i + 1, 1).value = d
sh1.cell(i + 1, 1).alignment = Alignment(horizontal='center', vertical='center')
self.wb.save('添加数据样式.xlsx')
def set_merge(self):
"""
合并单元格
:return:
"""
sh1 = self.wb.active
sh1.merge_cells('A1:c1')
sh1.merge_cells('d2:e5')
sh1['a1'] = '横向合并'
sh1['d2'] = '多合并'
self.wb.save('添加合并数据.xlsx')
def set_imge(self):
"""
设置图表
:return:
"""
ws = self.wb.active
rows = [
['Date', 'Batch 1', 'Batch 2', 'Batch 3'],
[date(2020, 12, 1), 40, 30, 25],
[date(2020, 12, 2), 40, 30, 25],
[date(2020, 12, 3), 50, 30, 75],
[date(2020, 12, 4), 50, 30, 25],
[date(2020, 12, 5), 20, 30, 25],
[date(2020, 12, 6), 40, 30, 25],
]
for row in rows:
ws.append(row)
c1 = LineChart()
c1.title = 'Line Chart'
c1.x_axis.title = 'Test_number'
c1.y_axis.title = 'size'
# min_col从第几列开始是数据,min_row从第几行开始读数据(第一行是title)max_row,max_col数据取第几行和列
data = Reference(ws, min_col=2, min_row=1, max_row=4, max_col=7)
c1.add_data(data, titles_from_data=True)
ws.add_chart(c1, 'A9')
self.wb.save('设置图表.xlsx')
def set_many_merge(self):
"""
多表合并
:return:
"""
sh = self.wb.active
all_data = []
for name in os.listdir('./销售表'):
path = f'./销售表/{name}'
tmp_wb = load_workbook(path)
tmp_sh = tmp_wb.active
for r in range(1, tmp_sh.max_row + 1):
# 获取整行数据
row_value = []
for c in range(1, tmp_sh.max_column + 1):
value = tmp_sh.cell(r, c).value
row_value.append(value)
# 获取整行数据 加到全局数据里
if row_value not in all_data: # 去重操作
all_data.append(row_value)
for data in all_data:
sh.append(data)
self.wb.save('./销售表/合并的数据.xlsx')
def set_many_to_sheet(self):
"""
合并excel到多个sheet中
:return:
"""
for name in os.listdir('./销售表'):
path = f'./销售表/{name}'
tmp_wb = load_workbook(path)
tmp_sh = tmp_wb.active
sh = self.wb.create_sheet(name[:-5])
for r in range(1, tmp_sh.max_row + 1):
# 获取整行数据
row_value = []
for c in range(1, tmp_sh.max_column + 1):
value = tmp_sh.cell(r, c).value
row_value.append(value)
sh.append(row_value)
del self.wb['Sheet']
self.wb.save('./销售表/合并的数据2.xlsx')
def salary_sheet(self):
"""
生成工资条
:return:
"""
wb = load_workbook('./工资数据.xlsx',data_only=True)
sh = wb.active
# 按照行数
title = ['工号', '姓名', '部门', '基本工资', '提成', '加班工资', '社保扣除', '考勤扣除', '应发工资', '邮箱' ]
for i,row in enumerate(sh.rows):
if i==0:
continue
else:
temp_wbook=Workbook()
temp_sh=temp_wbook.active
temp_sh.append(title)
row_data=[cell.value for cell in row]
temp_sh.append(row_data)
# print(row_data)
temp_wbook.save(f'./工资条/{row_data[1]}.xlsx')
def interlacing_Change_color(self):
"""
隔行变色
:return:
"""
sh=self.wb.active
rows = [
['Date', 'Batch 1', 'Batch 2', 'Batch 3'],
[date(2020, 12, 1), 40, 30, 25],
[date(2020, 12, 2), 40, 30, 25],
[date(2020, 12, 3), 50, 30, 75],
[date(2020, 12, 4), 50, 30, 25],
[date(2020, 12, 5), 20, 30, 25],
[date(2020, 12, 6), 40, 30, 25],
]
for row in rows:
sh.append(row)
#修改样式-填充背景色
bak_color=PatternFill('solid',fgColor='AEEEEE')
for i in range(1,sh.max_row+1):
if i%2==0:
for cell in range(1,sh.max_column+1):
sh.cell(i,cell).fill=bak_color
self.wb.save('隔行变色.xlsx')
def quickly_count_overtime_hours(self):
"""
快速统计加班时间
:return:
"""
sh=self.wb.active
rows = [
['Date', '姓名', '打卡时间'],
[date(2020, 12, 1), '刘备', '13:10'],
[date(2020, 12, 2), '刘小备', '13:30'],
[date(2020, 12, 3), '刘大备', '13:20'],
[date(2020, 12, 4), '刘备', '13:10'],
[date(2020, 12, 5),'刘备', '13:10'],
[date(2020, 12, 6),'刘备', '13:10'],
]
for row in rows:
sh.append(row)
self.wb.save('快速统计加班时间.xlsx')
def statistice():
#读取数据
wb=load_workbook('快速统计加班时间.xlsx')
sh=wb.active
data=[]
for i in range(2,sh.max_row+1):
t_data=[]
for j in range(1,sh.max_column+1):
t_data.append(sh.cell(i,j).value)
#统计
h,m=t_data[2].split(":")
full=int(h)*60+int(m)
tmp=full-13*60
t_data.append(tmp)
#处理时间问题
t_data[0]=t_data[0].date()
data.append(t_data)
#保存
n_web=Workbook()
n_sh=n_web.active
for d in data:
n_sh.append(d)
n_web.save('统计加班时间.xlsx')
statistice()
def quickly_check_duplicate_data(self):
"""
快速查重复数据
:return:
"""
wb=load_workbook('./快速统计加班时间.xlsx')
sh=wb.active
#存储哪一行是重复数据
index=[]
tmp=[]#没有重复数据
for i,c in enumerate(sh["B"]):
if c.value not in tmp:
tmp.append(c.value)
else:
index.append(i)
fill=PatternFill('solid',fgColor='AEEEEE')
for i,r in enumerate(sh.rows):
if i in index:
for c in r:
c.fill=fill
print(f'第{i+1}条数据是重复数据')
wb.save('./查找重复数据.xlsx')
def extract_ID_card_number_information(self):
"""
提取身份证号码信息
:return:
"""
now_year=datetime.now().year
wb=load_workbook('./身份证号码.xlsx')
sh=wb.active
max_column=sh.max_column
for i,cell in enumerate(sh["B"]):
pno=cell.value
if pno:
year=pno[6:10]
mouth=pno[10:12]
day=pno[12:14]
# print(f'year:{year},mouth:{mouth},day:{day}')
age=now_year-int(year)
sh.cell(i+1,max_column+1).value=year
sh.cell(i+1,max_column+2).value=mouth
sh.cell(i+1,max_column+3).value=day
sh.cell(i+1,max_column+4).value=age
else:
break
wb.save('./身份证号码.xlsx')
def main(self):
# self.show_sheets()
# self.get_one_value()
# self.get_many_value()
# self.get_num()
# self.new_excel()
# self.set_value()
# self.set_value2()
# self.set_style()
# self.set_merge()
# self.set_imge()
# self.set_many_merge()
# self.set_many_to_sheet()
# self.salary_sheet()
# self.interlacing_Change_color()
# self.quickly_count_overtime_hours()
# self.quickly_check_duplicate_data()
self.extract_ID_card_number_information()
if __name__ == '__main__':
o = Openpyxl_Study()
o.main()
word学习
基本操作
安装模块
pip install python-docx
from docx import Document
doc1=Document()
doc1.add_heading('如何使用python创建和操作word',0)
doc1.save('./word的创建.docx')
内容填写
from docx import Document
doc1=Document()
doc1.add_heading('如何使用python创建和操作word',0)
doc1.add_heading('python操作word 1级标题',1)
doc1.add_heading('python操作word 2级标题',2)
doc1.add_heading('python操作word 3级标题',3)
doc1.add_paragraph('这是段落内容')
doc1.add_paragraph('这是段落内容','Title')
doc1.save('./word的填写.docx')
增加段落
from docx import Document
doc1=Document()
paragraph=doc1.add_paragraph('Python,是一种广泛使用的解释型、高级和通用的编程语言。Python支持多种编程范型,包括结构化、过程式、反射式、面向对象和函数式编程。它拥有动态类型系统和垃圾回收功能,能够自动管理内存使用,并且其本身拥有一个巨大而广泛的标准库。')
paragraph.add_run('这是添加的内容')
doc1.add_paragraph('Python,是一种广泛使用的解释型、高级和通用的编程语言。Python支持多种编程范型,包括结构化、过程式、反射式、面向对象和函数式编程。它拥有动态类型系统和垃圾回收功能,能够自动管理内存使用,并且其本身拥有一个巨大而广泛的标准库。')
doc1.save('./word的填写.docx')
增加列表
from docx import Document
doc1=Document()
doc1.add_paragraph('刘备',style='List Bullet')
doc1.add_paragraph('关羽',style='List Bullet')
doc1.add_paragraph('张飞',style='List Number')
doc1.add_paragraph('孙尚香',style='List Number')
doc1.save('./word的填写.docx')
增加引用
from docx import Document
doc1=Document()
doc1.add_paragraph('这是一个引用',style='Intense Quote')
doc1.save('./word的填写.docx')
增加图片
from docx import Document
from docx.shared import Inches#英寸
doc1=Document()
# doc1.add_picture('./3.jpeg',width=Inches(5))
pic=doc1.add_picture('./3.jpeg')
height=pic.height
width=pic.width
p_width=doc1.sections[0].page_width
sc=(p_width/10-doc1.sections[0].left_margin/10*2)/(width/10)
pic.width=int(width*sc)
pic.height=int(height*sc)
doc1.save('./word的填写.docx')
添加表格
from docx import Document
from docx.shared import Inches#英寸
doc1=Document()
table=doc1.add_table(rows=1,cols=3)
cells=table.rows[0].cells
cells[0].text='编号'
cells[1].text='姓名'
cells[2].text='职业'
data=[
[1,'吕布','将军'],
[2,'诸葛亮','军师'],
[3,'刘备','主公'],
]
for i,n,w in data:
cells=table.add_row().cells
cells[0].text=str(i)
cells[1].text=n
cells[2].text=w
doc1.save('./word的填写.docx')
添加样式
字体样式
from docx import Document
from docx.shared import Inches,Pt,RGBColor
from docx.oxml.ns import qn
doc1=Document()
p1=doc1.add_paragraph('这是一个段落\n')
p1.add_run('是否加粗\n').bold=True
p1.add_run('是否斜体\n').italic=True
p1.add_run('设置字体大小\n').font.size=Pt(26)
p1.add_run('是否设置下划线\n').font.strike=True
p1.add_run('是否设置阴影\n').font.shadow=True
p1.add_run('设置字体颜色\n').font.color.rgb=RGBColor(255,13,71)
p1.add_run('Test font style \n').font.name='微软雅黑'
run=p1.add_run('设置字体\n')
run.font.name=''
run._element.rPr.rFonts.set(qn('w:eastAsia'),'微软雅黑')
doc1.save('./word的样式.docx')
from docx import Document
from docx.enum.text import WD_ALIGN_PARAGRAPH
from docx.shared import Inches,Pt
doc1=Document()
p0=doc1.add_paragraph('Python')
p1=doc1.add_paragraph('Python,是一种广泛使用的解释型、高级和通用的编程语言。Python支持多种编程范型,包括结构化、过程式、反射式、面向对象和函数式编程。它拥有动态类型系统和垃圾回收功能,能够自动管理内存使用,并且其本身拥有一个巨大而广泛的标准库。')
p2=doc1.add_paragraph('Python,是一种广泛使用的解释型、高级和通用的编程语言。Python支持多种编程范型,包括结构化、过程式、反射式、面向对象和函数式编程。它拥有动态类型系统和垃圾回收功能,能够自动管理内存使用,并且其本身拥有一个巨大而广泛的标准库。')
p3=doc1.add_paragraph('Python,是一种广泛使用的解释型、高级和通用的编程语言。Python支持多种编程范型,包括结构化、过程式、反射式、面向对象和函数式编程。它拥有动态类型系统和垃圾回收功能,能够自动管理内存使用,并且其本身拥有一个巨大而广泛的标准库。')
p0.paragraph_format.alignment=WD_ALIGN_PARAGRAPH.CENTER
p1.paragraph_format.left_indent=Inches(0.5)#整体缩进
p2.paragraph_format.first_line_indent=Inches(0.5)#首字缩进
doc1.add_paragraph('段落前缩进').paragraph_format.space_before=Pt(30)
doc1.add_paragraph('段落后缩进').paragraph_format.space_after=Pt(30)
doc1.add_paragraph('测试缩进')
p3.paragraph_format.line_spacing=3#行缩进
doc1.save('./word的样式.docx')
生成通知书
from docx import Document
from docx.enum.text import WD_ALIGN_PARAGRAPH
from docx.shared import Inches,Pt,RGBColor
from docx.oxml.ns import qn
doc1=Document()
title=doc1.add_paragraph()
p=title.add_run('车辆违章处罚通知书')
p.font.size=Pt(30)
p.font.color.rgb=RGBColor(255,0,0)
p.font.name=''
p._element.rPr.rFonts.set(qn('w:eastAsia'),'宋体')
title.paragraph_format.alignment=WD_ALIGN_PARAGRAPH.CENTER
info='''
尊敬的车主:
您好!我们是【城市名称】交通管理局,根据交通法规的相关规定,对您的车辆进行了查处。
经现场勘验及系统查询,您的车辆存在以下违法行为:
【违法时间】在【违法地点】违反了【违法行为内容】的规定(违法代码:【违法代码】),共计【罚款金额】元。
请您于收到本通知书之日起15日内,将罚款缴至【银行名称】【账号】,并携带本通知书及车辆行驶证、车辆登记证等相关证件前往【交通管理部门名称】接受处理。
逾期未缴纳罚款的,我们将依法采取强制措施,同时可能会影响您的个人信用记录。
驾驶员签字: 年 月 日
'''
content=doc1.add_paragraph()
p2=content.add_run(info)
content.paragraph_format.first_line_indent=Inches(0.25)
doc1.save('./word的通知书.docx')
读取word文档
from docx import Document
doc1=Document('./word的填写')
#读取文档内容
for p in doc1.paragraphs:
print(p)
#读取表格内容
for t in doc1.tables:
for row in t.rows:
for cell in row.cells:
print(cell.text)
word转成pdf
安装模块
pip install pywin32
from win32com.client import gencache
from win32com.client import constants,gencache
def createPdf(wordPath,pdfPath):
"""
word转pdf
:param wordPath: word文件路径
:param pdfPath: 生成pdf文件路径
:return:
"""
word=gencache.EnsureDispatch('Word.Application')
doc=word.Documents.Open(wordPath,ReadOnly=1)
doc.ExportAsFixedFormat(pdfPath,
constants.wdExportFormatPDF,
Item=constants.wdExportDocumentWithMarkup,
CreateBookmarks=constants.wdExportCreateHeadingBookmarks)
word.Quit(constants.wdDoNotSaveChanges)
if __name__ == '__main__':
createPdf('C:/Users/ytmjcmn/Desktop/python练习/办公学习/word的通知书.docx',
'C:/Users/ytmjcmn/Desktop/python练习/办公学习/word的通知书.pdf')
pdf学习
读取pdf
模块安装
pip install pypdf2或pypdf3
pip install pdfplumber
import pdfplumber
from PyPDF2 import PdfReader
def read_pdf():
with open('./word的通知书.pdf','rb')as f:
pdf=PdfReader(f)
info=pdf.metadata
number=len(pdf.pages)
for i in range(number):
print(pdf.pages[i].extract_text())
print(info)
print(number)
def read_pdf2():
with pdfplumber.open('./word的通知书.pdf')as pdf:
for i in range(len(pdf.pages)):
page=pdf.pages[i]
print(page.extract_text())
if __name__ == '__main__':
# read_pdf()
read_pdf2()
合并pdf
import pdfplumber
from PyPDF2 import PdfReader,PdfWriter
def merger_pdf(path1,path2):
write=PdfWriter()
for path in [path1,path2]:
tmp_pdf=PdfReader(open(path,'rb'))
for page in tmp_pdf.pages:
write.add_page(page)
with open('./合并.pdf','wb')as f:
write.write(f)
if __name__ == '__main__':
merger_pdf('./word的通知书.pdf','./word的通知书2.pdf')
拆分pdf
import pdfplumber
from PyPDF2 import PdfReader,PdfWriter
def split_pdf(path):
pdf=PdfReader(open(path,'rb'))
for i, page in enumerate(pdf.pages):
write=PdfWriter()
write.add_page(page)
with open(f'./拆分{i}.pdf','wb')as out:
write.write(out)
if __name__ == '__main__':
split_pdf('./合并.pdf')
加密pdf
import pdfplumber
from PyPDF2 import PdfReader,PdfWriter
def encrypt_pdf(path):
pdf=PdfReader(open(path,'rb'))
# pdf.decrypt('123')#当读取pdf有密码时,填写相应的密码
write=PdfWriter()
write.encrypt('123')
for page in pdf.pages:
write.add_page(page)
with open('./加密.pdf','wb')as target:
write.write(target)
if __name__ == '__main__':
encrypt_pdf('./合并.pdf')
ppt学习
基本操作
模块安装
pip install python-pptx
创建ppt
from pptx import Presentation
def create_ppt():
ppt=Presentation()
slide=ppt.slides.add_slide(ppt.slide_layouts[0])
slide=ppt.slides.add_slide(ppt.slide_layouts[1])
slide=ppt.slides.add_slide(ppt.slide_layouts[2])
slide=ppt.slides.add_slide(ppt.slide_layouts[3])
ppt.save('./创建.pptx')
if __name__ == '__main__':
create_ppt()
添加内容
from pptx import Presentation
def add_content():
#创建ppt
ppt=Presentation()
slide=ppt.slides.add_slide(ppt.slide_layouts[1])
#获取内容框
shape=slide.shapes
title=shape.title
title.text='This is Python Title !!!'
cotent=shape.placeholders[1]
cotent.text='Content Info'
ppt.save('./添加内容.pptx')
if __name__ == '__main__':
add_content()
添加段落
from pptx import Presentation
def add_content():
#创建ppt
ppt=Presentation()
slide=ppt.slides.add_slide(ppt.slide_layouts[1])
#获取内容框
slide2=ppt.slides.add_slide(ppt.slide_layouts[1])
shapes=slide2.shapes
content=shapes.placeholders[1]
tf=content.text_frame
p1=tf.add_paragraph()
p1.text='这个是内容1'
p1.level=1
p2 = tf.add_paragraph()
p2.text = '这个是内容2'
p2.level = 2
p3 = tf.add_paragraph()
p3.text = '这个是内容3'
p3.level = 3
ppt.save('./添加内容.pptx')
if __name__ == '__main__':
add_content()
from pptx import Presentation
from pptx.util import Pt
def add_content():
#创建ppt
ppt=Presentation()
slide=ppt.slides.add_slide(ppt.slide_layouts[1])
#获取内容框
slide2=ppt.slides.add_slide(ppt.slide_layouts[1])
shapes=slide2.shapes
content=shapes.placeholders[1]
tf=content.text_frame
p1=tf.add_paragraph()
p1.text='这个是内容1'
p2 = tf.add_paragraph()
p2.text = '这个是内容1'
p2.font.bold=True
p2.font.size=Pt(30)
ppt.save('./添加内容.pptx')
if __name__ == '__main__':
add_content()
填写内容位置
from pptx import Presentation
from pptx.util import Pt
def add_content():
#创建ppt
ppt=Presentation()
slide=ppt.slides.add_slide(ppt.slide_layouts[6])
left=top=width=height=Pt(300)
text_box=slide.shapes.add_textbox(left,top,width,height)
tf=text_box.text_frame
tf.text='这是文本框内容!'
p = tf.add_paragraph()
p.text = '这个是内容1'
ppt.save('./添加内容.pptx')
if __name__ == '__main__':
add_content()
增加图片
from pptx import Presentation
from pptx.util import Pt
def add_pic():
#创建ppt
ppt=Presentation()
slide=ppt.slides.add_slide(ppt.slide_layouts[1])
#获取内容框
shapes=slide.shapes
left=top=Pt(30)
shapes.add_picture('./3.jpeg',left,top)
slide2 = ppt.slides.add_slide(ppt.slide_layouts[1])
# 获取内容框
shapes = slide2.shapes
left = top = Pt(0)
height=width=Pt(300)
shapes.add_picture('./3.jpeg', left, top,height,width)
ppt.save('./添加图片.pptx')
if __name__ == '__main__':
add_pic()
增加流程图
from pptx import Presentation
from pptx.util import Pt,Inches
from pptx.enum.shapes import MSO_AUTO_SHAPE_TYPE
def flow_chart():
ppt=Presentation()
slide=ppt.slides.add_slide(ppt.slide_layouts[5])
shapes=slide.shapes
shapes.title.text='流程图'
top=Inches(3)
left=Inches(1)
width=Inches(2)
height=Inches(1)
t_sh=shapes.add_shape(MSO_AUTO_SHAPE_TYPE.PENTAGON,left,top,width,height)
t_sh.text='第1步'
for i in range(2,6):
left=left+width-Inches(0.3)
temp=shapes.add_shape(MSO_AUTO_SHAPE_TYPE.CHEVRON,left,top,width,height)
# temp.text=f'第{}步'
p=temp.text_frame
p.text = f'第{i}步'
p.fit_text(max_size=10,bold=True,italic=True)
ppt.save('./流程图.pptx')
if __name__ == '__main__':
flow_chart()
绘制条图
from pptx import Presentation
from pptx.util import Pt,Inches
from pptx.chart.data import CategoryChartData
from pptx.enum.chart import XL_CHART_TYPE
from pptx.enum.shapes import MSO_AUTO_SHAPE_TYPE
def bar_plot():
ppt=Presentation()
slide=ppt.slides.add_slide(ppt.slide_layouts[6])
#封装图表数据
chart_data=CategoryChartData()
#分组数据
chart_data.categories=['第一季度','第二季度','第三季度','第四季度']
#具体数据
chart_data.add_series('series',[19,21,16])
# # 多个数据
# chart_data.add_series('series', [19, 21, 16])
# chart_data.add_series('series', [22, 21, 16])
# chart_data.add_series('series', [19, 36, 16])
x=y=Inches(2)
width=Inches(6)
height=Inches(4.5)
#绘制图表
slide.shapes.add_chart(XL_CHART_TYPE.COLUMN_CLUSTERED,x,y,width,height,chart_data)
ppt.save('./绘制图表_条图.pptx')
if __name__ == '__main__':
bar_plot()
绘制图表样式
from pptx import Presentation
from pptx.util import Pt,Inches
from pptx.chart.data import CategoryChartData
from pptx.enum.chart import XL_CHART_TYPE,XL_DATA_LABEL_POSITION
from pptx.enum.shapes import MSO_AUTO_SHAPE_TYPE
def bar_plot():
ppt=Presentation()
slide=ppt.slides.add_slide(ppt.slide_layouts[6])
#封装图表数据
chart_data=CategoryChartData()
#分组数据
chart_data.categories=['第一季度','第二季度','第三季度','第四季度']
# 多个数据
chart_data.add_series('series1', [19, 21, 16])
chart_data.add_series('series2', [22, 21, 16])
chart_data.add_series('series3', [19, 36, 16])
x=y=Inches(2)
width=Inches(6)
height=Inches(4.5)
#绘制图表
chart=slide.shapes.add_chart(XL_CHART_TYPE.COLUMN_CLUSTERED,x,y,width,height,chart_data).chart
chart.chart_style=13#绘制主题颜色
chart.font.size=Pt(10)#设置字体大小
category_axis=chart.category_axis
category_axis.tick_labels.font.size=Pt(15)#设置axis的字体大小
category_axis.has_major_gridlines=True#是否显为表格
#设置标签
plot=chart.plots[0]
plot.has_data_labels=True
data_labels=plot.data_labels
data_labels.position=XL_DATA_LABEL_POSITION.INSIDE_END
#显示图例
chart.has_legend=True
chart.legend.position=XL_DATA_LABEL_POSITION.LEFT#显示位置
chart.legend.font.size=Pt(15)
chart.legend.include_in_layout=False
ppt.save('./绘制图表_样式.pptx')
if __name__ == '__main__':
bar_plot()
绘制拆线图和饼图
from pptx import Presentation
from pptx.util import Pt,Inches
from pptx.chart.data import CategoryChartData
from pptx.enum.chart import XL_CHART_TYPE,XL_DATA_LABEL_POSITION,XL_LEGEND_POSITION
from pptx.enum.shapes import MSO_AUTO_SHAPE_TYPE
def disassembly_diagram_plot():
ppt=Presentation()
slide=ppt.slides.add_slide(ppt.slide_layouts[6])
#封装图表数据
chart_data=CategoryChartData()
#分组数据
chart_data.categories=['第一季度','第二季度','第三季度','第四季度']
# 多个数据
chart_data.add_series('series1', [19, 21, 16])
chart_data.add_series('series2', [22, 21, 16])
chart_data.add_series('series3', [19, 36, 16])
x=y=Inches(2)
width=Inches(6)
height=Inches(4.5)
#绘制图表
chart=slide.shapes.add_chart(XL_CHART_TYPE.LINE,x,y,width,height,chart_data).chart
chart.chart_style=10#绘制主题颜色
chart.font.size=Pt(10)#设置字体大小
category_axis=chart.category_axis
category_axis.tick_labels.font.size=Pt(15)#设置axis的字体大小
category_axis.has_major_gridlines=True#是否显为表格
# #设置标签
plot=chart.plots[0]
plot.has_data_labels=True
#显示图例
chart.has_legend=True
chart.legend.position=XL_LEGEND_POSITION.TOP#显示位置
chart.legend.font.size=Pt(15)
chart.legend.include_in_layout=False
ppt.save('./绘制图表_样式_折线图.pptx')
def pie_plot():
ppt=Presentation()
slide=ppt.slides.add_slide(ppt.slide_layouts[6])
#封装图表数据
chart_data=CategoryChartData()
chart_data.categories = ['第一季度', '第二季度', '第三季度', '第四季度']
chart_data.add_series('季度销售比例',(0.27,0.23,0.31,0.19))
x = y = Inches(2)
width = Inches(6)
height = Inches(4.5)
chart=slide.shapes.add_chart(XL_CHART_TYPE.PIE,x,y,width,height,chart_data).chart
chart.has_legend=True#显示图例
chart.plots[0].has_data_labels=True
data_labels=chart.plots[0].data_labels
data_labels.position=XL_DATA_LABEL_POSITION.OUTSIDE_END
data_labels.number_format='0%'
ppt.save('./绘制图表_样式_饼图.pptx')
if __name__ == '__main__':
# disassembly_diagram_plot()
pie_plot()
邮件学习
发送普通邮箱
import smtplib
from email.mime.text import MIMEText
from email.header import Header
#登录邮箱
smtp_obj=smtplib.SMTP('smtp.qq.com')
smtp_obj.login('xxx@qq.com','你的密钥')
#编辑内容
mail_text='This is Email~ 你要的邮件来啦'
msg_body=MIMEText(mail_text,'plain','utf-8')
msg_body['From']='xxx@qq.com'
msg_body['Subject']=Header('测试邮件','utf-8')
#发邮件
try:
smtp_obj.sendmail('xxx@qq.com',['收件人账号@qq.com'],msg_body.as_string())
smtp_obj.quit()
print('邮件发送成功')
except smtplib.SMTPException as e:
print('邮件发送失败',str(e))
发送HTML邮箱
import smtplib
from email.mime.text import MIMEText
from email.header import Header
#登录邮箱
smtp_obj=smtplib.SMTP('smtp.qq.com')
smtp_obj.login('xxx@qq.com','你的密钥')
#编辑内容
mail_text='''
<h1 style='color:red'>这个是一个HTML邮件</h1>
<p>这是邮件的主题内容</p>
<p><a href='http://www.baidu.com'>这是一个链接</a></p>
'''
msg_body=MIMEText(mail_text,'html','utf-8')
msg_body['From']='xxx@qq.com'
msg_body['Subject']=Header('测试邮件','utf-8')
#发邮件
try:
smtp_obj.sendmail('xxx@qq.com',['收件人账号@qq.com'],msg_body.as_string())
smtp_obj.quit()
print('邮件发送成功')
except smtplib.SMTPException as e:
print('邮件发送失败',str(e))
发送附件邮件
import smtplib
from email.mime.text import MIMEText
from email.header import Header
from email.mime.application import MIMEApplication
from email.mime.multipart import MIMEMultipart
#登录邮箱
smtp_obj=smtplib.SMTP('smtp.qq.com')
smtp_obj.login('xxx@qq.com','你的密钥')
#编辑内容
mail_text='''
<h1 style='color:red'>这个是一个HTML邮件</h1>
<p>这是邮件的主题内容</p>
<p><a href='http://www.baidu.com'>这是一个链接</a></p>
'''
#编写文本内容
msg_body=MIMEText(mail_text,'html','utf-8')
#增加附件
file=MIMEApplication(open('./添加图片.pptx','rb').read())
file.add_header('Content-Disposition','attachment',filename='wp.pptx')
#封装邮件主体
multi_part=MIMEMultipart()
multi_part.attach(msg_body)
multi_part['From']='xxx@qq.com'
multi_part['Subject']=Header('测试附件邮件','utf-8')
multi_part.attach(file)
#发邮件
try:
smtp_obj.sendmail('xxx@qq.com',['收件人账号@qq.com'],msg_body.as_string())
smtp_obj.quit()
print('邮件发送成功')
except smtplib.SMTPException as e:
print('邮件发送失败',str(e))
zmail邮箱的使用
安装模块
pip install zmail
import zmail
def base_use():
server=zmail.server('xxx@qq.com','你的密钥')
info={
'subject':'这个是主题',
'from':'xxx@qq.com',
'content_text':'这个Email的内容!!!!'
}
try:
server.send_mail('xxx@qq.com',info)
print('发送成功')
except Exception as e:
print(e)
print('发送失败')
def base_file():
server=zmail.server('xxx@qq.com','你的密钥')
info={
'subject':'这个是主题',
'from':'xxx@qq.com',
'content_text':'这个是改善Email附件',
'attachments':['./添加图片.pptx']
}
try:
server.send_mail('xxx@qq.com',info)
print('发送成功')
except Exception as e:
print(e)
print('发送失败')
def base_html():
server=zmail.server('xxx@qq.com','你的密钥')
info={
'subject':'这个是主题',
'from':'xxx@qq.com',
'content_html':'<h1>这个是改善Email附件</h1>',
'attachments':['./添加图片.pptx']
}
try:
server.send_mail('xxx@qq.com',info)
print('发送成功')
except Exception as e:
print(e)
print('发送失败')
def get_mail():
server=zmail.server('xxx@qq.com','你的收密钥')
last_mail=server.get_latest()
print(last_mail.get('subject'))
print(last_mail.get('from'))
print(last_mail.get('to'))
print(last_mail.get('content_text'))
print(last_mail.get('content_html'))
print(last_mail.get('date'))
print(last_mail)
if __name__ == '__main__':
# base_use()
# base_file()
base_html()
get_mail()
window定时任务
钉钉学习
发送钉钉普通消息
模块安装
pip install dingtalkchatbot
from dingtalkchatbot.chatbot import DingtalkChatbot,CardItem,ActionCard
class DingDingStudy:
def __init__(self):
self.url = '群链接'
self.secret = '群加密码'
self.bot=DingtalkChatbot(self.url,secret=self.secret)
def send_text(self):
"""
发送普通消息
:return:
"""
# self.bot.send_text('测试一下消息')
# self.bot.send_text('你该交作业了!!!',at_mobiles=['jkljlkjlkj'])#提醒某人
self.bot.send_text('时间到了,所有人交作业了!!!',is_at_all=True)#提醒所有人
def send_img(self):
"""
发送图片
:return:
"""
self.bot.send_image(pic_url='https://pic.netbian.com/uploads/allimg/240129/164203-1706517723a4bc.jpg')
def send_link(self):
"""
发送链接
:return:
"""
self.bot.send_link(title='python',text='学习',message_url='https://www.baidu.com',pic_url='https://pic.netbian.com/uploads/allimg/240129/164203-1706517723a4bc.jpg')
def send_md(self):
"""
发送markdown文件内容
:return:
"""
self.bot.send_markdown(title='python',
text='#一级标题\n'
)
def send_card(self):
"""
发送卡片
:return:
"""
card1=CardItem(title='python',url='https://www.baidu.com',pic_url='https://pic.netbian.com/uploads/allimg/240129/164203-1706517723a4bc.jpg')
card2=CardItem(title='java',url='https://www.baidu.com',pic_url='https://pic.netbian.com/uploads/allimg/240129/164203-1706517723a4bc.jpg')
card3=CardItem(title='php',url='https://www.baidu.com',pic_url='https://pic.netbian.com/uploads/allimg/240129/164203-1706517723a4bc.jpg')
cards=[card1,card2,card3]
self.bot.send_feed_card(cards)
def send_card2(self):
"""
改善卡片第二种
:return:
"""
#Link消息
#ActionCard整体跳转消息类型
btns1=[CardItem(title='python',url='https://www.baidu.com')]
actioncard1=ActionCard(
title='万万没有想到,竟然。。。',
text='![选择](https://pic.netbian.com/uploads/allimg/240129/164203-1706517723a4bc.jpg)',
btns=btns1,
btn_orientation=1,
hide_avatar=1
)
self.bot.send_action_card(actioncard1)
def send_card3(self):
"""
发送卡片第三种
:return:
"""
# Link消息
# ActionCard独立跳转消息类型(双选项)
btns1 = [CardItem(title='支持', url='https://www.baidu.com'),CardItem(title='反对', url='https://www.ping.com')]
actioncard2 = ActionCard(
title='万万没有想到,竟然。。。',
text='![选择](https://pic.netbian.com/uploads/allimg/240129/164203-1706517723a4bc.jpg)',
btns=btns1,
btn_orientation=1,
hide_avatar=1
)
self.bot.send_action_card(actioncard2)
def send_card4(self):
"""
发送卡片第四种
:return:
"""
# Link消息
# ActionCard独立跳转消息类型(三选项)
btns1 = [CardItem(title='支持', url='https://www.baidu.com'),CardItem(title='中立', url='https://www.jd.com'), CardItem(title='反对', url='https://www.ping.com')]
actioncard3 = ActionCard(
title='万万没有想到,竟然。。。',
text='![选择](https://pic.netbian.com/uploads/allimg/240129/164203-1706517723a4bc.jpg)',
btns=btns1,
btn_orientation=1,
hide_avatar=1
)
self.bot.send_action_card(actioncard3)
def main(self):
# self.send_text()
# self.send_img()
# self.send_link()
# self.send_md()
# self.send_card()
# self.send_card2()
# self.send_card3()
self.send_card4()
if __name__ == '__main__':
d=DingDingStudy()
d.main()
压缩文件学习
基本操作
import os.path, tarfile
from zipfile import ZipFile
def zip_in():
with ZipFile('./zip文件.zip', 'w') as zip:
zip.write('绘制图表_条图.pptx')
zip.write('绘制图表_样式.pptx')
zip.write('绘制图表_样式_折线图.pptx')
def zip_out():
with ZipFile('./zip文件.zip', 'r') as zip:
print(zip.namelist())
# zip.extract('绘制图表_条图.pptx','./解压文件')#解压单个
zip.extractall('./解压文件') # 解压多个
def compree_zip(zip_name, dir_name):
"""
:param zip_name: 压缩名字
:param dir_name: 文件或文件夹名字
:return:
"""
with ZipFile(zip_name, 'w') as zip:
if os.path.isfile(dir_name): # 判断是否是文件
zip.write(dir_name)
else:
for root, dirs, files in os.walk(dir_name):
# root所指的当前正在遍历的这个文件夹的本身地址
# dirs list,是该文件夹中所有的目录的名字(不包含子目录)
# files list,是该文件夹中所有的文件名字
for f in files:
if f != zip_name:
path = os.path.join(root, f)
zip.write(path)
print(f'正在压缩{f}')
def add_file(zip_name, dir_name):
with ZipFile(zip_name, 'a') as zip:
if os.path.isfile(dir_name): # 判断是否是文件
zip.write(dir_name)
else:
for root, dirs, files in os.walk(dir_name):
# root所指的当前正在遍历的这个文件夹的本身地址
# dirs list,是该文件夹中所有的目录的名字(不包含子目录)
# files list,是该文件夹中所有的文件名字
for f in files:
if f != zip_name:
path = os.path.join(root, f)
zip.write(path)
print(f'正在压缩{f}')
def tar_in():
with tarfile.open('./tar_in.tar', 'w') as tar:
tar.add('流程图.pptx', arcname='pdf.py')
tar.add('添加内容.pptx', arcname='pdf.py')
def tar_out():
with tarfile.open('./tar_in.tar', 'r') as tar:
print(tar.getmembers())
if __name__ == '__main__':
# zip_in()
# zip_out()
# compree_zip('./zip压缩文件.zip','./解压文件')
# tar_in()
tar_out()
加解密码
import pyzipper
def create_encrypted_zip(file_to_zip, output_zip, password):
with pyzipper.AESZipFile(output_zip, 'w', compression=pyzipper.ZIP_LZMA, encryption=pyzipper.WZ_AES) as zf:
zf.setpassword(password.encode())
zf.write(file_to_zip, arcname=file_to_zip)
zf.setencryption(pyzipper.WZ_AES, pwd=password.encode())
def extract_encrypted_zip(zip_file, password, output_path):
with pyzipper.AESZipFile(zip_file) as zf:
zf.setpassword(password.encode())
zf.extractall(path=output_path)
压缩密码破解
import os.path
from zipfile import ZipFile
import itertools as its
import pyzipper
def password(path,pwd):
type_=os.path.splitext(path)[-1][1:]
if type_=='zip':
with pyzipper.AESZipFile(path,'r')as zip:
print(f'正在尝试密码:{pwd}')
try:
zip.setpassword(pwd.encode('utf-8'))
zip.extractall('./解压')
# zip.extractall('./解压',pwd=str(pwd).encode('utf-8'))
print(f'解压成功,密码是:{pwd}')
return True
except Exception as e:
# print(e)
pass
def create_pwd(length):
words='0123456789r'
for i in range(1,length):
base=its.product(words,repeat=i)
for j in base:
yield ''.join(j)
if __name__ == '__main__':
for p in create_pwd(5):
flag=password('./新建文件夹/加密.zip',p)
if flag:
break
# password('./新建文件夹/加密.zip','123r')