文件操作files
excel
import win32com.client
from win32com.client import Dispatch, DispatchEx
import pythoncom
from PIL import ImageGrab, Image
import datetime
from docx import Document
from docx.shared import Cm
from openpyxl import Workbook
import re
from openpyxl.styles import Alignment, Font, Side, Border, PatternFill, GradientFill
from openpyxl.drawing.image import Image
from openpyxl.chart import BarChart, Reference, LineChart
# 新建工作簿
book = Workbook()
del book['Sheet']
sheet = book.create_sheet("新标签页")
sheet.cell(row=1, column=3).value = '日期'
print(book.sheetnames)
book.save("我的工作簿.xlsx")
# 打开工作簿
workbook = openpyxl.load_workbook('cs.xlsx')
sheet = workbook['Sheet1']
cour = sheet.max_row
couc = sheet.max_column
sheet.insert_cols(idx=1) # 插入列
sheet.insert_cols(idx=1, amount=3) # 插入多列
sheet.insert_rows(idx=1) # 插入行
sheet.insert_rows(idx=1, amount=3) # 插入多行
sheet.delete_cols(idx=1, amount=1) # 删除列
sheet.delete_rows(idx=1, amount=3) # 删除行
sheet.move_range('A3:B8', rows=2, cols=-2) # 移动格子，正整数为向下向右，负数为向左向上
workbook.create_sheet('mysheet') # 创建sheet
workbook.remove('mysheet') # 删除sheet
workbook.copy_worksheet('mysheet') # 复制sheet
sheet.title = 'mysheet' # 修改sheet名称
sheet.freeze_panes = 'A2' # 冻结窗格
sheet.auto_filter.ref = sheet.dimensions # 筛选
rows = sheet.rows # 获取所有行
lst = []
for row in rows:
if row[3].value == '41':
sub_list = []
for i in range(0, 4):
sub_list.append(row[i].value)
lst.append(sub_list)
print(lst)
new_workbook = openpyxl.Workbook()
new_sheet = new_workbook.active
for row in lst:
new_sheet.append(row)
new_workbook.save('目标文档.xlsx')
cell = sheet['A1']
font = openpyxl.styles.Font(name='微软雅黑', size=20, bold=True, italic=True,
color='ff0000') # 格式非常重要！！！！！！！！！！！！！Font是在styles里的
cell.font = font
print(cell.font.name)
algin = Alignment(horizontal='center', vertical='center')
cell.alignment = algin
side = Side(style='thin', color='ff0000') # 设置线条形状和颜色
border = Border(left=side, top=side, right=side, bottom=side)
cell.border = border
cell_c5 = sheet['C5']
pattern_fill = PatternFill(fill_type='solid', fgColor='ffff00')
cell_c5.fill = pattern_fill # 设置背景
gradien = GradientFill(stop=('ff00ff', '00ff00'))
cell_c5.fill = gradien # 设置渐变色
sheet.row_dimensions[2].height = 50 # 设置第二行的行高为50
sheet.column_dimensions['B'].width = 20 # 设置B列列宽为20
sheet.merge_cells('D1:G2') # 合并单元格
sheet.merge_cells(start_row=1, start_column=8, end_row=5, end_column=12) # 合并单元格从哪开始到哪结束
sheet.unmerge_cells('D1:G2') # 取消合并单元格
sheet.unmerge_cells(start_row=1, start_column=8, end_row=5, end_column=12) # 取消合并单元格从哪开始到哪结束
sheet = workbook['mysheet']
workbook.copy_worksheet(sheet)
sheet.title = '其他' # 这3句好像都是一起用，复制完改名
fill = PatternFill(fill_type='solid', fgColor='ffff00')
for row in workbook['好评数据'].row:
if re.search('.*好.*', row[0].value): # 正则表达式
for i in range(0, 4):
row[i].fill = fill
row[i].font = font
logo = Image('logo.png')
logo.width = 200
logo.height = 100 # 设置宽高
sheet.add_image(logo, 'A1') # 插入图片
chart = BarChart() # 创建柱状图的图表对象
# 数据的引用
data = Reference(worksheet=sheet, min_row=1, max_row=13, min_col=1, max_col=2)
# 类别的引用
categories = Reference(sheet, min_row=2, max_row=13, min_col=1)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
sheet.add_chart(chart, 'F12')
#条形图
chart=LineChart()
data=Reference(worksheet=sheet,min_row=2,max_row=3,min_col=1,max_col=13)
categories=Reference(sheet,min_row=1,min_col=2,max_col=13)
chart.add_data(data,from_rows=True,titles_from_data=True)
chart.set_categories(categories)
sheet.add_chart(chart,'K1')
alignment=Alignment(horizontal='center',vertical='center',wrap_text=True)
#获取单元格值
a1_value=sheet['A1'].value
#获取指定的行
rows=sheet[3]
for col in rows:
print(col.column_letter) #获取列的字母
i=1
sheet['B4'].value=sheet['A'+str(i)].value
#执行VBA
def useVBA(VBA):
xlApp = win32com.client.DispatchEx("Excel.Application")
xlApp.Visible = True
xlApp.DisplayAlerts = 0
xlBook = xlApp.Workbooks.Open(file_path, False)
xlBook.Application.Run(VBA) # 宏
xlBook.Close(True)
xlApp.quit()
file_path = r"D:\OneDrive\py\stydy\csw.xlsm"
useVBA('ceshi')
#EXCEL 存图片
area_list = ['A1:M11', 'A13:L23', 'A26:L36'] # 截图区域
picture_list = ['E:/pic1', 'E:/pic2', 'E:/pic3'] # 生成截图的文件名
# 函数功能：截取excel文件多个区域，生成对应多个.PNG图片
# 参数说明：
# filename：excel文件名（包括完整路径）
# sheetname：excel文件的sheet页名称
# screen_area：截图区域，以列表形式存储。取值例如：['A1:M11','A13:L23','A26:L36']，表示分别截取3个区域：A1到M11、A13到L23、A26到L36
# picture_name：生成截图的文件名（包括完整路径），以列表形式存储，程序会加上.PNG后缀。取值例如：['E:/pic1','E:/pic2','E:/pic3']
# flag：是否需要继续处理的标记，Y表示中间出现异常，没有生成全部截图，需要再调用此函数，处理未生成的截图；N表示全部处理完成
def excel_catch_screen(filename, sheetname, screen_area, picture_name, flag):
pythoncom.CoInitialize() # excel多线程相关
try:
excel = DispatchEx("Excel.Application") # 启动excel
excel.Visible = True # 可视化
excel.DisplayAlerts = False # 是否显示警告
wb = excel.Workbooks.Open(filename) # 打开excel
ws = wb.Sheets(sheetname) # 选择sheet
# 循环处理每个截图区域
for i in range(0, len(screen_area)):
ws.Range(screen_area[i]).CopyPicture() # 复制图片区域
ws.Paste() # 粘贴
excel.Selection.ShapeRange.Name = picture_name[i] # 将刚刚选择的Shape重命名，避免与已有图片混淆
ws.Shapes(picture_name[i]).Copy() # 选择图片
img = ImageGrab.grabclipboard() # 获取剪贴板的图片数据
img_name = picture_name[i] + ".PNG" # 生成图片的文件名
img.save(img_name) # 保存图片
# 记录已处理的截图区域
x = screen_area[i]
# 将已处理的截图区域从列表移除
area_list.remove(x)
# 记录已处理的截图名称
y = picture_name[i]
# 将已处理的截图名称从列表移除
picture_list.remove(y)
# 打印日志
print('移除区域：' + x + ' 移除图片：' + y)
print('剩余计数：' + str(len(screen_area)) + ' ' + str(len(picture_name)))
flag = 'N' # 如果程序执行到这里，说明所有截图都正常处理完成，将flag置为N
except Exception as e:
flag = 'Y' # 只要有任一截图异常，退出当前程序，将flag置为Y，等待再次调用此函数
print('error is:', e) # 打印异常日志
finally:
wb.Close(SaveChanges=0) # 关闭工作薄，不保存
excel.Quit() # 退出excel
pythoncom.CoUninitialize()
return flag # 返回flag
# 调用截图函数excel_catch_screen的入口
def gen():
print(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') + ' : 截图开始')
try:
filename = 'E:/1.xlsx' # excel文件名
sheetname = '截图专用' # excel文件的sheet页名称
flag = 'Y'
while flag == 'Y': # 循环调用截图函数
flag = excel_catch_screen(filename, sheetname, area_list, picture_list, flag)
except Exception as e:
print('main error is:', e)
print(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') + ' : 截图结束')
if __name__ == "__main__":
gen()
word
doc = Document('csw.docx')
paras = doc.paragraphs
for item in paras:
print(item.text)
# 获取文字块
runs = item.runs
for run in runs:
print(run.text)
doc.add_picture('logo.png')
doc.add_picture('logo.png', width=Cm(5))
doc.save('csw.docx')
lst = [
['xuehao', 'xingming', 'chengji'],
[101, 'zhangsan', 97],
[102, 'zhangsan', 97],
[103, 'zhangsan', 97]
]
table = doc.add_table(rows=4, cols=4)
for index in range(4):
cells=table.rows[index].cells
for idx in range(3):
cells[idx].text=str(lst[index][idx])
doc.add_heading('请假条',level=1)
保存文件
with open('linshi.html', 'w') as f:
f.write(self.html)
# 下载文件wb
with open(filepath + mydate[0:4] + '\\' + mydate[4:6] + '\\' + mydate[6:8] + '\\XXX\\' + i + '_1.csv',
"wb") as code:
code.write(r.content)
读取文件
dataline = []
with open(filepath + mydate[0:4] + '\\' + mydate[4:6] + '\\' + mydate[6:8] + '\\XXXX\\' + j + '_1.csv') as file:
for line in file:
dataline.append(line)
import pandas as pd
dataline = pd.read_csv(
filepath + mydate[0:4] + '\\' + mydate[4:6] + '\\' + mydate[6:8] + '\\XXX\\FutureDataHolding.txt', sep='\t',
skipinitialspace=True, skip_blank_lines=True).values.tolist()
解压文件
# 解压文件
import zipfile
import shutil
# 判断路径是否存在
isex = os.path.exists(filepath + mydate[0:4] + '\\' + mydate[4:6] + '\\' + mydate[6:8] + '\\XXX\\' + mydate + '_DCE_DPL')
# 如果存在递归删除文件夹及内容
if isex:
shutil.rmtree(filepath + mydate[0:4] + '\\' + mydate[4:6] + '\\' + mydate[6:8] + '\\XXX\\' + mydate + '_DCE_DPL')
os.chdir(filepath + mydate[0:4] + '\\' + mydate[4:6] + '\\' + mydate[6:8] + '\\XXX\\')
extracting = zipfile.ZipFile(mydate + '_DCE_DPL.zip')
extracting.extractall(
filepath + mydate[0:4] + '\\' + mydate[4:6] + '\\' + mydate[6:8] + '\\XXX\\' + mydate + '_DCE_DPL')
extracting.close()
遍历文件
for root, dirs, files in os.walk(
filepath + mydate[0:4] + '\\' + mydate[4:6] + '\\' + mydate[6:8] + '\\XXX\\' + mydate + '_DCE_DPL'):
for file in files:
# sioprint.cmdprint(file)
大商所 = pd.read_csv(os.path.join(root, file), sep='\t', skipinitialspace=True,
skip_blank_lines=True).values.tolist()