1.修改字体样式
# Font(name=字体名称, size=字体大小, bold=是否加粗, italic=是否斜体,color=字体颜色)
from openpyxl.styles import Font
cell = sheet['A1']
font = Font(name = "宋体 Regular",zize=12, bold=True, italic=True,color='FF000000')
cell.font = font
work_book.save(filename=filename)2.获取表格中字体的样式
#cell.font.属性
workbook = load_workbook( filename '这是一个表格.xlsx ')sheet = workbook.active
cell = sheet[ 'A2']
font = cell.font
print(font.name，font.size，font.bold, font.italic)3.设置对齐样式
# Alignment(horizontal=水平对齐模式, vertical=垂直对齐模式, text_rotation=旋转角度, wrap_text=是否自动换行)
# 水平对齐样式 distributed justify center left fill centerContinuous right general
# 垂直对齐样式 bottom distributed justify center top
cell.alignment = alignment
from openpyxl.styles import Alignment
cell = sheet['A1']
alignment = Alignment(horizontal='center',vertical='center',text_rotation=0,warp_text=True)
work_book.save(filename=filename)4.设置边框样式
from openpyxl.styles import Side,Border
cell = sheet['A1']
side1 = Side(style='thin',color='FF000000')
side2 = Side(style='dotted',color='FFFF0000')
border = Border(left=side1,right=side1,top=side2,bottom=side2)
cell.border = border
work_book.save(filename=filename)5.设置填充样式
from openpyxl.styles import PatternFill，GradientFill
from openpyxl import load_workbook
workbook = load_workbook(filename='这是一个表格.xlsx ')
sheet = workbook.active
cell_a3 = sheet[ 'A3']
pattern_fill = PatternFill( fill_type='solid', fgColor='99ccff')
cell_a3.fill = pattern_fil1
cell_a4 = sheet[ 'A4']
gradient_fill = GradientFill(stop=( 'FFFFFF' , '99ccff' , '000000 ')) #渐变色
cell_a4.fill = gradient_fill
workbook.save( filename='测试.xlsx ')6.合并单元格
# .merge_cells(待合并的格子编号)
# .merge_cells(start_row=起始行号, start_column=起始列号, end_row=结束行号，end_column=结束列号)
workbook = load_workbook( filename='测试.xlsx')
sheet = workbook.active
sheet.merge_cells('C1:D2')
sheet.merge_cells(start_row=7,start_column=1,end_row=8,end_column=4)
workbook.save( filename='测试.xlsx ')7.取消合并单元格
# .unmerge_cells(待合并的格子编号)
# .unmerge_cells(start_row=起始行号, start_column=起始列号, end_row=结束行号, end_column=结束列号)
workbook = load_workbook( filename='测试.xlsx')
sheet = workbook.active
sheet.unmerge_cells ( 'C1:D2')
sheet.unmerge_cells (start_row=7, start_column=1, end_row=8,end_column=4)
workbook.save( filename='测试.xlsx ')8.插入图片
from openpyxl import load_workbook
from openpyxl.drawing.image import Image
workbook = load_workbook(filename="测试.xlsx")
sheet = workbook.active
logo = Image("刘亦菲.jpg") # 图片路径
logo.height = 100
logo.width = 100
sheet.add_image( logo,"C1") # 插入位置
workbook.save( filename="测试.xlsx") 9.插入柱状图
from openpyxl import load_workbook
from openpyxl.chart import BarChart, Reference
workbook = load_workbook(filename="测试.xlsx")
sheet = workbook.active
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_row=7) # 分类名称
chart.add_data(data,titles_from_data=True) #titles_from_data 使用数据源中的标题，如果存在的话
chart.set_categories(categories)
sheet.add_chart(chart,"E2")
workbook.save("测试.xlsx")10.插入折线图
from openpyxl import load_workbook
from openpyxl.chart import LineChart,Reference
workbook = load_workbook(filename="测试.xlsx" )
sheet = workbook.active
chart = LineChart()
data = Reference(worksheet=sheet, min_row=20,max_row=21,min_col=1,max_col=13)
categories = Reference(sheet,min_row=19,min_col=2, max_col=13)
chart.add_data(data,from_rows=True,titles_from_data=True) # from_rows=True表示数据是横向的
chart.set_categories(categories)
sheet.add_chart(chart,"B23")
workbook.save("测试.xlsx")11,设置超链接,本地文件超链接
filename = path_excel
work_book = load_workbook(filename=filename)
sheet = work_book.active#获取当前打开的sheet
i=2
sheet.cell(row=i + 2, column=4).hyperlink = target_path # 设置本地文件超链接;
work_book.save(filename=filename) # 也可以是其他表格12、自动设置列宽行高
这个方法只能针对单行的单元格文本，大家可以根据自己的实际情况调整一下取值系数或最后的调整参数
大致思路就是找出每一列的字符串最大长度，直接设置列宽值为最大长度+2，由于python在识别字符串长度的时候是不会区分中文字符和英文字符的，而在EXCEL中中文字符的宽度大约是英文字符的两倍，所以要找出中文字符的数量
import openpyxl
from openpyxl.utils import get_column_letter
import re
# 修改下述参数即可使用，Excel名称及Sheet名称即可
outputXlName = '
'
inputSheetName = 'Sheet1'
wb = openpyxl.load_workbook(outputXlName)
ws = wb[inputSheetName]
# 设置一个字典用于保存列宽数据
dims = {}
# 遍历表格数据，获取自适应列宽数据
for row in ws.rows:
for cell in row:
if cell.value:
# 遍历整个表格，把该列所有的单元格文本进行长度对比，找出最长的单元格
# 在对比单元格文本时需要将中文字符识别为1.7个长度，英文字符识别为1个，这里只需要将文本长度直接加上中文字符数量即可
# re.findall('([\u4e00-\u9fa5])', cell.value)能够识别大部分中文字符
cell_len = 0.7*len(re.findall('([\u4e00-\u9fa5])', str(cell.value))) + len(str(cell.value))
dims[cell.column] = max((dims.get(cell.column, 0), cell_len))
for col, value in dims.items():
# 设置列宽，get_column_letter用于获取数字列号对应的字母列号，最后值+2是用来调整最终效果的
ws.column_dimensions[get_column_letter(col)].width = value + 2
wb.save(outputXlName)
13、小案例，设置公式，列宽，格式，自动换行并左对齐
def setEXcelStyle(sPath):
'''
sPath:表格绝对路径 xlsx格式
'''
import openpyxl
from openpyxl import load_workbook # load_workbook 只能打开已经存在的表格，不能创建新表格
filename = sPath
work_book = load_workbook(filename=filename)
sheet = work_book.active
from openpyxl.utils import FORMULAE
# print(FORMULAE) # 打印所有支持直接赋值的公式
rows = sheet.max_row # 3346 获取最大行数
# print(rows)
sheet.column_dimensions['a'].width = 45
sheet.column_dimensions['b'].width = 45
sheet.column_dimensions['c'].width = 45
sheet.column_dimensions['d'].width = 20
sheet.column_dimensions['e'].width = 15
sheet.column_dimensions['f'].width = 12
sheet.column_dimensions['g'].width = 20
sheet.column_dimensions['h'].width = 16
sheet.column_dimensions['i'].width = 20
sheet.column_dimensions['j'].width = 20
sheet.column_dimensions['k'].width = 10
sheet.column_dimensions['l'].width = 15
sheet.column_dimensions['m'].width = 50
sheet.column_dimensions['n'].width = 10
sheet.column_dimensions['o'].width = 15
sheet.column_dimensions['p'].width = 15
sheet.column_dimensions['q'].width = 20
sheet.column_dimensions['r'].width = 15
sheet.column_dimensions['s'].width = 15
sheet.column_dimensions['t'].width = 50
sheet.column_dimensions['u'].width = 50
sheet.column_dimensions['v'].width = 16
sheet.column_dimensions['w'].width = 16
for rownum in range(2, rows):
# print(rownum)
rownum = str(rownum)
sheet['S' + str(rownum)] = '=sum(k%s-R%s)' % (rownum, rownum) # 对F列进行评价计算
sheet['B' + str(rownum)] = '=HYPERLINK(A%s)' % rownum
sheet['I' + str(rownum)] = '=sum(G%s*0.35)' % rownum # 对I列进行评价计算G列*0.35
sheet['J' + str(rownum)] = '=sum(G%s*0.3)' % rownum # 对F列进行评价计算G列*0.3
cell = sheet['C' + str(rownum)] # 获取C列的值
sheet.cell(row=int(rownum), column=22).number_format = '#,##0' # 设置货币格式;
sheet.cell(row=int(rownum), column=23).number_format = '#,##0' # 设置货币格式;
sheet.cell(row=int(rownum), column=11).number_format = '0' #
# if cell.value!=None:
# print('C' + str(rownum)+"超链接公式设置完成")
# sheet['C' + str(rownum)] = '=HYPERLINK(C%s)' % rownum#对C列本身进行设置超链接
#设置水平左对齐,垂直居中;自动换行 wrapText=True为自动换行;
nrows = sheet.max_row # 获得行数
ncols = sheet.max_column
for i in range(1,nrows):
for j in range(ncols):
sheet.cell(row=i + 1, column=j + 1).alignment = Alignment(horizontal='left', vertical='center',wrapText=True,wrap_text=True)
work_book.save(filename=filename)
print("数据整理完成")14、创建居中
ws6.append(
['统计日期', "公司名称", "法人代表", "董事长", "总经理", "注册资本", "经营范围", "取得会员资格交易所名称", "公司住所", "公司办公地址", "首席风险官"]) # 股东人员信息
from openpyxl.styles import Alignment
# 创建居中对齐的样式
align = Alignment(horizontal='center', vertical='center')
# 定义要设置的列
columns = ['A', 'B', 'C', 'D', 'E', 'F', 'K']
# 循环设置列宽和单元格对齐方式
for col in columns:
# 设置列宽
ws6.column_dimensions[col].width = 20
# 循环该列的所有单元格，并设置对齐方式
for cell in ws6[col]:
cell.alignment = align
work_book.save(filename=filename)15、自动创建表格自动创建sheet
import openpyxl
from openpyxl import Workbook
import datetime
import os
folder_path = r"D:\RPA\xxxx"
# 检查文件夹是否存在
if not os.path.exists(folder_path):
# 不存在则创建文件夹
os.makedirs(folder_path)
print("文件夹已创建")
else:
print("文件夹已存在")
# 获取当前日期和月份
sheet_name =str(datetime.date.today())#当天日期
print(sheet_name)
now = datetime.datetime.now()
current_year = now.year
current_month = now.month
print(current_year,current_month)
excel_name =f"邮箱查询结果{current_year}年{current_month}月"
print(excel_name)
file_name = excel_name+".xlsx"
file_path = os.path.join(folder_path, file_name)
if not os.path.exists(file_path):
workbook = Workbook()
workbook.save(file_path)
# 打开Excel文件
wb = openpyxl.load_workbook(file_path)
# 检查工作表是否存在
if sheet_name not in wb.sheetnames:
# 创建工作表
wb.create_sheet(sheet_name)
print(f"工作表 {sheet_name} 已创建")
else:
print(f"工作表 {sheet_name} 已存在")
# 检查工作表是否存在
if "Sheet" in wb.sheetnames:
# 删除工作表
sheet = wb["Sheet"]
wb.remove(sheet)
print(f"工作表 Sheet已删除")
else:
print(f"工作表Sheet不存在")
# 保存Excel文件
wb.save(file_path)
16、创建并追加写入字典数据
'''
判断指定的EXcel是否存在，如果不存在则创建，判断指定的sheet是否存在，如果不存在则创建；
判断指定的sheet中是否存在指定的列，如果不存在则创建；
将字典中的数据追加到指定的sheet中指定的列中。
'''
from openpyxl import load_workbook
from openpyxl import Workbook
def append_dict_to_excel(dictionary, filename,sheetName):
try:
# 尝试加载现有的Excel文件
wb = load_workbook(filename)
except FileNotFoundError:
# 若文件不存在，则创建一个新的Excel文件
wb = Workbook()
# 检查是否存在 "Kk系统统计结果" 的工作表
if sheetName not in wb.sheetnames:
# 创建一个新的工作表
sheet = wb.create_sheet(sheetName)
else:
# 选择要追加写入数据的工作表
sheet = wb[sheetName]
# 写入数据行
val_row = sheet.max_row + 1 # 最大行
for key, value in dictionary.items():
existing_columns = [sheet.cell(row=1, column=col_num).value for col_num in range(1, sheet.max_column + 1) if
sheet.cell(row=1, column=col_num).value is not None]
print("111",existing_columns)
col_num = sheet.max_column # 最大列数
# 如果列标已经存在，找到对应的列标索引
if key not in existing_columns:
# 判断是否为空 为空从第一列开始添加
if existing_columns==[]:
col_num = 1 # 最大列数
print("col_num45行",col_num)
sheet.cell(row=1, column=col_num).value = key
else:
# 不为空 获取最大列数，从最后一列添加
col_num = sheet.max_column+1 # 最大列数
sheet.cell(row=1, column=col_num).value = key
existing_columns = [sheet.cell(row=1, column=col_num).value for col_num in range(1, sheet.max_column + 1) if
sheet.cell(row=1, column=col_num).value is not None]
col_num = existing_columns.index(key) + 1
print("key对应的列数", key, col_num)
sheet.cell(row=val_row, column=col_num).value = value
if "Sheet" in wb.sheetnames:
sheet = wb["Sheet"]
wb.remove(sheet)
# 保存修改后的Excel文件
wb.save(filename)
# # 示例字典数据
# dic_arr = [
# {
# "成绩": 90,
# "姓名": "张三",
# "其他": "其他内容"
# },
# {
# "成绩": 92,
# "年龄": 25,
# "其他": "其他内容"
# },
# {
# "成绩": 95,
# "邮箱": "example@example.com",
# "其他": "其他内容"
# }
# ]
# filename = "test.xlsx"
# sheetname = "KK"
# for data in dic_arr:
# append_dict_to_excel(data, filename, sheetname)
17、字母与索引的互相转换
在openpyxl中可以通过get_column_letter()和column_index_from_string()函数进行列字母和索引之间的转换。下面是一个示例代码，展示如何在openpyxl中进行列字母和索引之间的转换：
from openpyxl.utils import get_column_letter, column_index_from_string
# 列字母到索引的转换
column_letter = 'A'
column_index = column_index_from_string(column_letter)
print(f"列字母 {column_letter} 对应列索引 {column_index}")
# 索引到列字母的转换
column_index = 1
column_letter = get_column_letter(column_index)
print(f"列索引 {column_index} 对应列字母 {column_letter}")
18、对表格自动换行，指定列宽
读取表格的列标，对指定列标进行转换为字母； 转换后 对执行列进行自动换行处理；以及设置列宽
'''
对文件名称列，文件份数列 进行自动换行处理
1、对表格中 标题为“文件名称”的和“文件分数” 进行自动换行处理，并设置列宽；
'''
from openpyxl.utils import get_column_letter, column_index_from_string
from openpyxl import load_workbook
from openpyxl.styles import Alignment
filename=r'C:\Users\Administrator\Desktop\用章统计流程2022-11-22---2023-11-23.xlsx'
sheet = "KK系统统计结果"
# 加载现有的工作簿
workbook = load_workbook(filename)
# 选择特定的工作表
sheet = workbook["KK系统统计结果"]
# 获取列标
existing_columns = [sheet.cell(row=1, column=col_num).value for col_num in range(1, sheet.max_column + 1) if
sheet.cell(row=1, column=col_num).value is not None]
print(existing_columns)
for key in existing_columns:
if key == "文件名称" or key == "文件份数":
col_num = existing_columns.index(key) + 1
# 序列转换 字母列标
column_letter = get_column_letter(col_num)
if key == "文件名称":
# 设置宽度
sheet.column_dimensions[column_letter].width = 40
if key == "文件份数":
# 设置宽度
sheet.column_dimensions[column_letter].width = 10
# 循环遍历A列中的单元格，设置自动换行和对齐
for cell in sheet[column_letter]:
cell.alignment = Alignment(wrap_text=True)
# 保存工作簿
workbook.save(filename)对每个表里面设置千位符，设置后为字符串
```python
从第五行开始 遍历设置千位符
for row_number, row in enumerate(worksheet.iter_rows(min_row=5, values_only=True), start=5):
for i, cell_value in enumerate(row, start=1):
if isinstance(cell_value, (int, float)):
cell = worksheet.cell(row=row_number, column=i) # 使用row_number作为行号
formatted_value = “{:,.2f}”.format(cell_value) # 将数字转换为字符串，并添加千位分隔符和保留两位小数的格式
cell.value = formatted_value
对指定列数据中的指定行进行标红
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill
def setred(red_rows,filepath):
wb = load_workbook(filename=filepath)
# 选择要操作的工作表
ws = wb.active
# 设置要标红的行号
# red_rows = [2, 3, 4]
# 遍历标红的行号，设置字体样式和填充样式
for row_num in red_rows:
print(row_num)
cell = ws.cell(row=row_num, column=2)# A列为1 B列为2
# 设置字体样式为红色
font = Font(color=”FF0000”)
cell.font = font
# 设置填充样式为红色背景
fill = PatternFill(fgColor=”FF0000”)
cell.fill = fill
# 保存修改后的Excel文件
wb.save(filename=filepath)
# 打开现有的Excel文件
#
