openpyxl

替换指定字符

s = ‘\tabc\t123titfistititittik’
print(s.replace(‘ti’, ‘’))

设置背景颜色

fille1 = PatternFill(patternType="solid", fgColor="E3EFD9")
ws.cell(k11, j).fill = fille1

字体修改

sheet.cell(1, 1).font=Font(bold=True,name=“黑体”,size=16,)

行高列宽

#设置连续行行高：
for r in range(1,17): # 注意，行和列的序数，都是从1开始
sheet.row_dimensions[r].height = 33 #
# 设置连续列列宽：
for c in range(1,24): # 注意，列序数从1开始，但必须转变为A\B等字母
w = get_column_letter© # 把列序数转变为字母
sheet.column_dimensions[w].width = 11.38
# 设置第2行行高：
sheet.row_dimensions[3].height = 66.75

居中

sheet.cell(r,c).alignment = Alignment(horizontal='center', vertical='center', wrapText=True)

设置边框

sheet.cell(1, 1).border=Border(bottom=Side(style='thin', color='000000'),
                         right=Side(style='thin', color='000000'),
                         left=Side(style='thin', color='000000'),
                         top=Side(style='thin', color='000000'))

获取指定路径下的文件目录

PaperFileSite=“.\file\”
fileList = os.listdir(PaperFileSite)

添加文件表数据

wb=load_workbook(kname)
ws=wb["表名"]

    for k in range(0,FileNameArraylen):
    kname=PaperFileSite+"/"+FileNameArray[k]#文件路径到底文件名
    wb.append(load_workbook(kname))
    ws.append(wb[k]["表名"])

文件名替换

  wb = []
ws = []
print("updatesurfacename")
for k in range(0,len(filename3)):
    kname=PaperFileSite+"/"+filename2[k]
# kname = PaperFileSite + "/" + filename2[0]
#     print(kname)
    wb.append(load_workbook(kname))
    # print(wb[k].sheetnames[0])
    surfacename=wb[k].sheetnames[0]
    # ws.append(wb[k].get_sheet_by_name(surfacename))
    ws.append(wb[k][surfacename])
    ws[k].title = “xxxx"
    wb[k].save(kname)

合并单元格

ws.merge_cells("B2:C2")

数字转英文

w = get_column_letter©

填写数据

ws.cell(2, 2).value = “xxxx”

创建多列表

temporaryspace1=[[“False” for i in range(0,len(projectname))] for j in range(0,len(proplename))]
#projectname行*proplename列
#append创建会导致2维行地址相同

删除行列

sheet.delete_cols(1)#删除1列
sheet.delete_rows(1)#删除1行