Java转python学习笔记-自动化办公

import os

#打印当前程序的路径
print(os.getcwd())
#
# # 遍历指定文件夹所有文件
print(os.listdir('./'))#输出列表
#
# # 遍历所有文件
for item in os.listdir('./'):
    print(item,os.path.isdir(item)) #输出文件名+是否文件夹

for file in os.scandir('./'):
    print(file.name,file.path,file.is_dir(),file.stat().st_size/1024)#输出文件名+相对路径+是否文件夹+文件大小以kb为单位
    print(file.stat().st_atime)#输出文件创建的时间st_等等

for dirpath,direnames,filenames in os.walk('./'):
    print(f'发现文件夹{dirpath}')
    print(filenames)
#
# # 判断是否以XX为开头活着结尾
print('makerbean'.startswith('.txt'))
print('makerbean'.endswith('.txt'))
#
# # 找特定名字的文件
import glob
print(glob.glob('*.py'))#找当前目录下以.py为结尾的
print(glob.glob('m*.py'))#找当前目录下以.py为结尾以m为开头的文件
print(glob.glob('mai?.py'))#找当前目录下mai(任意字符).py的文件
print(glob.glob('**/*.py',recursive=True))#深层遍历所有.py文件

# # 打开文件读取和写
stream = open(r'C:\Users\Admin\Desktop\aa.txt')
print(stream.readline())
print(stream.readlines())#读取全部放到列表
container = stream.read()#读取一行
print(container)#读取全部
print(stream.readable())#判断是否可以读取

stream = open(r'C:\Users\Admin\Desktop\aa.txt','w')#如果是'a'则追加写入 'w'覆盖写入
stream.write('111')#每次写(知道close)会把原文件覆盖
stream.writelines(['111\n','222\n'])#换行需要自己打回车
stream.close()#用完关闭通道
#
# # 图片的复制 用rb读取二进制  用wb写入二进制 用with能自动管理流通道
with open(r'C:\Users\Admin\Desktop\aa.jpg','rb') as rstream:
    container = rstream.read()#读取文件
    with open(r'C:\Users\Admin\Desktop\新建文件夹\aa.jpg','wb') as wstream:
        wstream.write(container)
#
# # 图片地址拼接 和 OS模块
import os
with open(r'C:\Users\Admin\Desktop\aa.jpg','rb') as rstream:
    container = rstream.read()#读取文件
    path = os.path.dirname(__file__)#获取当前文件的目录地址
    print(os.path.split(path))#('C:/Users/Admin/PycharmProjects', 'pythonProject')
    print(os.path.splitext(path))#判断文件类型
    print(os.path.getsize(path))#获取当前文件字节数
    print(os.listdir(r'C:\Users'))#当前目录所有文件夹的名称，保存到列表

if not os.path.exists(r'C:\Users\Admin\Desktop\p1'):#判断文件夹是否存在
    os.mkdir(r'C:\Users\Admin\Desktop\p1')#最后创建文件夹

os.makedirs('111/222/333')#创建多层结构文件夹们

path = r'C:\Users\Admin\Desktop\p1'
filelist = os.listdir(path)
for file in filelist:
    path1 = os.path.join(path,file)
    os.remove(path1)
else:
    os.rmdir(r'C:\Users\Admin\Desktop\p1')#删除文件夹，文件夹中不能有文件

os.chdir(r'C:\Users\Admin\Desktop')#切换目录

path1 = os.path.join(path,'bb.jpg')#在目录地址下创建bb.jpg
print(os.path.join(os.getcwd(),'file','a','123.jpg'))#在原有地址之间加入file/a/再拼接
with open(path1,'wb') as wstream:
    wstream.write(container)
#
# # 绝对和相对路径
print((os).path.isabs(r'C:\Users\Admin\Desktop\aa.jpg'))#判读是否绝对路径
# ../表示退一个目录

print(os.path.abspath('bb.jpg'))#用相对路径找绝对路径
#
# # 复制文件夹
def copy(src,target):
    if os.path.isdir(src) and os.path.isdir(target):
        filelist = os.listdir(src)
        for file in filelist:
            path = os.path.join(src,file)
            with open(path,'rb') as rstream:
                container = rstream.read()
                path1 = os.path.join(target,file)
                with open(path1,'wb') as wstream:
                    wstream.write(container)
        else:
            print('复制完毕')

src = r'C:\Users\Admin\Desktop\p1'
target = r'C:\Users\Admin\Desktop\p2'
copy(src,target)

#创建临时文件写入+读取，之后会自动删除
from tempfile import TemporaryFile
f = TemporaryFile('w+')#w+ 写+读
f.write('Hello')
f.seek(0)#游标回到开头
data = f.readlines()
print(data)
f.close()

# 创建临时文件夹操作后自动删除
from tempfile import TemporaryDirectory
with TemporaryDirectory() as  tem_folder:
    print(f"临时文件夹已经创建{tem_folder}")

# 文件复制
import shutil
shutil.copy('main.py','./aaa.txt')#把main.py复制到上层目录下创建aaa.txt把main.py内容写入
if not os.path.exists(r'C:\Users\Admin\Desktop\p1'):#判断文件夹是否存在
    os.mkdir(r'C:\Users\Admin\Desktop\p1')#最后创建文件夹
shutil.copytree(r'C:\Users\Admin\Desktop\p1','./学习/p1')#把C:\Users\Admin\Desktop\p1 文件夹 复制到 当前目录/学习文件夹下

# 重命名
os.rename('111','222')#把111重命名222

# 删除文件
os.remove('111')

# 删除文件夹
import shutil
shutil.rmtree('123')

# 读取压缩包文件
import zipfile
with zipfile.ZipFile('main.zip','r') as zipobj:
    print(zipobj.namelist())#['main.py']  中文可能会乱码

with zipfile.ZipFile('main.zip','r') as zipobj:
    for filename in zipobj.namelist():
        info = zipobj.getinfo(filename)
        file_name = filename.encode('cp437').decode('gbk')
        print(file_name,info.file_size,info.compress_size)#输出名字，大小，压缩后大小

# 解压全部
import zipfile
with zipfile.ZipFile('main.zip','r') as zipobj:
    zipobj.extractall(path='C:/Users/Admin/Desktop/',pwd=b'123')#解压位置,解压密码

创建压缩包
file_list = ['main.py']
with zipfile.ZipFile('我想创建的压缩包.zip','w' )as zipobj:
    for file in file_list:
        zipobj.write(file)

# 往压缩包里追加压缩文件
with zipfile.ZipFile('程序创建的压缩包.zip','a') as zipobj:
    zipobj.write('file')

from openpyxl import load_workbook
workbook = load_workbook(filename='1.xlsx')
sheet = workbook.active

print(sheet.dimensions)#表格的尺寸 A1:A30

cell = sheet['A1']
print(cell.value,cell.column,cell.coordinate)#值+列+行 1 1 A1

cell = sheet.cell(row=1,column=1)#获取指定位置的数据
print(cell.value)

cells = sheet['A1:A5']#获取一条或者一列
print(cells)

cells = sheet['A']#获取一列
print(cells)

cells = sheet['A:C']#获取A-C列
print(cells)

cells = sheet['5']#获取第5行
print(cells)

cells = sheet['5:6']#获取5到6行
print(cells)

for row in sheet.iter_rows(min_row=1,max_row=1,min_col=1,max_col=10):#指定从某行某列到某行某列  一行一行打印  row 换 col按列打印
    print(row)

cell = sheet['A1']#控制写入的位置
cell.value = '你好'#写入数据
workbook.save(filename='1.xlsx')#修改完要保存

data = [
    ['a',1],#追加插入 每一个列表就是一行
    ['b',2],
    ['c',3]
]
for row in data:
    sheet.append(row)
workbook.save(filename='1.xlsx')#修改完要保存

sheet['A2'] = '=AVERAGE(A3:A5)'#用公式
sheet.insert_cols(idx=2)#在第二列左边插入一列
sheet.delete_cols(idx=2)#在第二列左边删除一列
sheet.move_range('A2:A5',rows=2,cols=2)#将A2:A5向下向左移动两格
workbook.create_sheet('表格2')#创建一个新的表格
sheet = workbook['表格2']
workbook.copy_worksheet(sheet)#复制一个表格
sheet.title = '表格三'#表格改名
workbook.remove(sheet)#删除表格2
sheet.freeze_pans = 'B2'#冻结窗格，让某一行即使下拉也能看的见
sheet.auto_filter.ref = sheet.dimensions#筛选

from openpyxl.styles import Font
cell = sheet['A10']
font = Font(name='思源黑体 Regular',size=12,bold=True,italic=True,color='FF0000')#加粗，斜体
cell.font = font

from openpyxl.styles import Alignment
cell = sheet['A10']
alignment = Alignment(horizontal='center',vertical='center',text_rotation=45)#水平对齐，垂直对齐，旋转角度
cell.alignment = alignment

from openpyxl.styles import Border,Side#设置边框
cell = sheet['A10']
side1 = Side(style='thin',color='FF000000')#细线
side2 = Side(style='dotted',color='FFFF0000')
border = Border(left=side1,right=side1,top=side2,bottom=side1)
cell.border = border

from openpyxl.styles import PatternFill,GradientFill
cell = sheet['A10']
pattern_fill = PatternFill(fill_type='solid',fgColor='99ccff')#填充样式
cell.fill = pattern_fill
gradient_fill = GradientFill(stop=('FFFFFF','99ccff','000000'))#设置渐变
cell.fill = gradient_fill
sheet.row_dimensions[1].height = 50  #第一行行高50
sheet.column_dimensions['A'].width = 20 #第一列列宽20
sheet.merge_cells('B1:B2')#合并单元格
sheet.unmerge_cells('B1:B2')#取消合并单元格
sheet.merge_cells(start_row=3,start_column=5,end_column=6,end_row=10)

from openpyxl.drawing.image import Image#插入图片
logo = Image('timg.jpg')
logo.height = 100
logo.width = 150
sheet.add_image(logo,'C1')

from openpyxl.chart import BarChart,Reference #BarChart是柱状图  LineChart是条形图
chart = BarChart()#柱状图
data = Reference(worksheet=sheet,min_row=1,max_row=7,min_col=2,max_col=3)#数据区域
categories = Reference(sheet,min_col=1,min_row=2,max_col=7)#标题行
chart.add_data(data,titles_from_data=True)#告知数据中带有标题
chart.set_categories(categories)#设置X轴类目
sheet.add_chart(chart,'E2')
workbook.save(filename='1.xlsx')#修改完要保存

import pdfplumber #pdf操作
from openpyxl import Workbook
with pdfplumber.open('Netease Q2 2019 Earnings Release-Final.pdf') as pdf:#自动图文转换
    table_page = pdf.pages[9]#第9页开始
    table = table_page.extract_table(
        table_settings={#设置表格格式
            "vertical_strategy":'text',
            "horizontal_strategy":'text'
        }
    )
    workbook = Workbook()
    sheet = workbook.active
    for row in table:
        if not ''.join([str(item) for item in row]) == '':#去空格
            new_row = []
            new_row.append(''.join([str(item) if item else '' for item in row[:3]]))#把前三行分隔的字母拼接在一格内
            new_row += row[3:]
            sheet.append(new_row)
    workbook.save(filename='网易财报.xlsx')

from PyPDF2 import PdfFileReader,PdfFileWriter
# # 分隔每页输出
pdf_reader = PdfFileReader('Netease Q2 2019 Earnings Release-Final.pdf')
for page in range(pdf_reader.getNumPages()):
    pdf_writer = PdfFileWriter()
    pdf_writer.addPage(pdf_reader.getPage(page))
    with open(f'./Netease Q2 2019 Earnings {page}.pdf','wb') as out:
        pdf_writer.write(out)

# 合并
pdf_writer = PdfFileWriter()
for page in range(16):
    pdf_reader = PdfFileReader(f'./Netease Q2 2019 Earnings {page}.pdf')
    for page in range(pdf_reader.getNumPages()):
        pdf_writer.addPage(pdf_reader.getPage(page))
with open('merged.pdf','wb') as out:
    pdf_writer.write(out)

# 加密
from PyPDF2 import PdfFileReader,PdfFileWriter
pdf_reader = PdfFileReader('Netease Q2 2019 Earnings Release-Final.pdf')
pdf_reader.decrypt('thisiscode')#解密时候需要
pdf_writer = PdfFileWriter()
for page in range(pdf_reader.getNumPages()):
    pdf_writer.addPage(pdf_reader.getPage(page))

pdf_writer.encrypt('thisiscode')#解密时不需要此行
with open('encrypted.pdf','wb') as out:
    pdf_writer.write(out)

# word操作
from docx import Document
doc = Document('111.docx')

for paragraph in doc.paragraphs:
    print(paragraph.text)#输出每段文字

paragraph = doc.paragraphs[1]
runs = paragraph.runs
for run in runs:
    print(run.text)#输出每个文字块

doc = Document()
doc.add_heading('一级标题',level=1)#标题名，标题等级

paragraph1 = doc.add_paragraph('第一个段落')#添加段落