大家早上好，本人姓吴，如果觉得文章写得还行的话也可以叫我吴老师。欢迎大家跟我一起走进数据分析的世界，一起学习！
感兴趣的朋友可以关注我的数据分析专栏，里面有许多优质的文章跟大家分享哦。
openpyxl在我看来，就是用代码代替我们的双手，在EXCEL上 *** 作。在EXCEL上能做到的 *** 作，openpyxl基本都能实现。
我们在上班的时候经常需要处理数据，运算啥的自然是交给pandas，但是输出的时候呢？这样子输出的格式是我们满意的吗？如果说输出后再到excel上运算，会不会总感觉还不够那么的自动化？
pandas现在有了pandas.style，可以帮助我们处理输出的表格格式，但是说实话， *** 作起来还真的很不方便，这也是为什么上次我写关于pandas的这篇博客的时候，在最后介绍处理表格格式的时候，挖了一个坑，说有机会再介绍，但是大概率是不会再去介绍pandas.style了，因为真的不好用，或者说没有openpyxl好用。
所以我现在处理数据并且输出到表格的时候，都是pandas(处理数据)+openpyxl（输出格式）
那么之前已经整理了好几篇超详细的pandas博文，今天给大家介绍一下openpyxl的各种实用功能，绝对干货满满，欢迎收藏哦！
目录导入所需模块0 xls转xlsx文件1 新建、保存excel
1.1 新建Workbook1.2 新建sheet1.3 删除sheet1.4 保存表格 2 读取excel文件3 读取、修改sheet各种设置
3.1 获取全部sheet名3.2 读取指定的sheet3.3 获取sheet中的最大行数列数3.4 修改sheet的名字3.5 修改sheet标签页的填充颜色3.6 追加行3.7 插入行3.8 删除行 4 读取、修改单元格各种设置
4.1 读取、修改单元格内容4.2 修改单元格填充颜色4.3 修改单元格字体格式4.3 修改单元格居中&自动换行4.4 修改数值显示格式4.5 设置单元格的边框4.6 修改单元格的其他格式 5 调整列宽、行高6 修改多个单元格
6.1 第一种方法6.2 第二种方法 7 合并单元格8 冻结指定的行和列9 复制表格中的sheet到另一个sheet中10 设置表格缩放比例结束语
导入所需模块import copy import openpyxl from openpyxl import load_workbook from openpyxl.utils import get_column_letter from openpyxl.styles import Alignment, Font, PatternFill, Border, Side, colors0 xls转xlsx文件
首先openpyxl里只能 *** 作xlsx文件，如果是xls文件，需要进行转换。
import win32com.client as win32
def xls2xlsx(filePath):
excelobj = win32.gencache.EnsureDispatch('Excel.Application')
wb = excelobj.Workbooks.Open(filePath)
wb.SaveAs(filePath + "x", FileFormat=51)
wb.Close()
excelobj.Application.Quit()
1 新建、保存excel
1.1 新建Workbook
workbook = openpyxl.Workbook()1.2 新建sheet
worksheet = wb2.create_sheet('这是一个sheet') # 取sheet名的时候注意不要太多字符哦，会有长度限制。
worksheet = wb2.create_sheet('Sheet2')
1.3 删除sheet
if 'Sheet2' in workbook.sheetnames:
del workbook['Sheet2']
1.4 保存表格
path = './test.xlsx' workbook.save(path)2 读取excel文件
excel_name = 'test.xlsx' workbook = load_workbook(excel_name)3 读取、修改sheet各种设置 3.1 获取全部sheet名
获取全部sheet名，得到的是一个list，按sheet显示的顺序存放。
sheet_names = wotkbook.sheetnames3.2 读取指定的sheet
# Sheet1是sheet名 worksheet= workbook['Sheet1']3.3 获取sheet中的最大行数列数
# 最大行数 max_row = worksheet.max_row # 最大列数 max_col = worksheet.max_column3.4 修改sheet的名字
worksheet.title = 'openpyxl_sheet'3.5 修改sheet标签页的填充颜色
worksheet.sheet_properties.tabColor = "d9e1f2"3.6 追加行
# 插入list
list_row = [1, 3, 7]
worksheet.append(list_row)
# 插入dict
dict_row = {'A': 1, 'B':3, 'C':7}
worksheet.append(dict_row)
3.7 插入行
在第一行插入空行
worksheet.insert_rows(1)
在第2行往下数3行插入空行（2、3、4行）
worksheet.insert_rows(2, 3)3.8 删除行
删除行后，后面的行会上移。
worksheet.delete_rows(1) # 删除第一行 worksheet.delete_rows(3， 5) # 删除第第3行到第5行4 读取、修改单元格各种设置
这里我先说明一下，指定特定的单元格主要有两种方式：worksheet['A2'] 和 worksheet.cell(2, 1)，指的都是第2行第1列的单元格，大家喜欢哪种就用哪种。不过因为我喜欢用第二种，所以下面我也多会用第二种来讲解。 4.1 读取、修改单元格内容
# 读取 value = worksheet.cell(5, 8).value # 第三行第二列的值 # 修改 worksheet.cell(5, 8).value = '单元格内容' # worksheet.cell(5, 8).value = 584.2 修改单元格填充颜色
这里只需要修改start_color和end_color就可以了，注意要保持统一。
# 读取 color_fill = worksheet.cell(row=5, column=8).fill # 修改 yellow_fill = PatternFill(start_color="ffff00", end_color="ffff00", fill_type="solid") worksheet.cell(row=5, column=8).fill = yellow_fill4.3 修改单元格字体格式
这里name是指字体类型，基本上excel上显示的字体都可以选。bold指的是是否加粗。size指的是字体大小。color指的是字体颜色。其他参数还有charset、outline等，不过不常用。
font = Font(name="Arial", bold=True, size = "11", color=colors.WHITE) worksheet.cell(row=5, column=8).font = font4.3 修改单元格居中&自动换行
垂直（horizontal）、居中（vertical）、自动换行（wrapText）
alignment_center = Alignment(horizontal='center', vertical='center', wrapText=True) worksheet.cell(5, 8).alignment = alignment_center4.4 修改数值显示格式
上图中的格式我们在openpyxl中都能设置。值得注意的是，有些小朋友明明设置了格式，但是输出时发现并没有应用到，这里很有可能是因为你单元格的数值是文本类型，需要先用float()转换成数值类型，再进行设置。
# try: # worksheet.cell(row=5, column=8).value = # float(worksheet.cell(row=5, column=8).value) # except: # pass worksheet.cell(row=5, column=8).number_format = r'_(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)' # 设置为千分位显示格式
当然最常见的还是日期格式设置的时候，需要我们先转换成日期类型。
# try: # worksheet.cell(row=5, column=8).value = # datetime.datetime.strptime(worksheet.cell(row=5, column=8).value, "%Y-%m-%d").date() # except: # pass worksheet.cell(row=5, column=8).number_format = 'mmm-yy'
其他数值显示格式
0: 'General',
1: '0',
2: '0.00',
3: '#,##0',
4: '#,##0.00',
5: '"$"#,##0_);("$"#,##0)',
6: '"$"#,##0_);[Red]("$"#,##0)',
7: '"$"#,##0.00_);("$"#,##0.00)',
8: '"$"#,##0.00_);[Red]("$"#,##0.00)',
9: '0%',
10: '0.00%',
11: '0.00E+00',
12: '# ?/?',
13: '# ??/??',
14: 'mm-dd-yy',
15: 'd-mmm-yy',
16: 'd-mmm',
17: 'mmm-yy',
18: 'h:mm AM/PM',
19: 'h:mm:ss AM/PM',
20: 'h:mm',
21: 'h:mm:ss',
22: 'm/d/yy h:mm',
37: '#,##0_);(#,##0)',
38: '#,##0_);[Red](#,##0)',
39: '#,##0.00_);(#,##0.00)',
40: '#,##0.00_);[Red](#,##0.00)',
41: r'_(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)',
42: r'_("$"* #,##0_);_("$"* (#,##0);_("$"* "-"_);_(@_)',
43: r'_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)',
44: r'_("$"* #,##0.00_)_("$"* (#,##0.00)_("$"* "-"??_)_(@_)',
45: 'mm:ss',
46: '[h]:mm:ss',
47: 'mmss.0',
48: '##0.0E+0',
49: '@'
4.5 设置单元格的边框
border_style是图中边框的样式。color自然指的是颜色。left、right、top、bottom自然指的是上下左右的边框。
border = Border(left=Side(border_style='thin',color='000000'), right=Side(border_style='thin',color='000000'), top=Side(border_style='thin',color='000000'), bottom=Side(border_style='thin',color='000000')) # border_bottom = Border(bottom=Side(border_style='thin',color='000000')) # border_top = Border(top=Side(border_style='thin',color='000000')) worksheet.cell(row=5, column=8).border = border4.6 修改单元格的其他格式
其实除了边框、填充、字体格式、显示格式、居中换行等等，还有其他的一些设置，比如_style和protection，这里就不一一介绍了，毕竟也基本不会用到。
5 调整列宽、行高sheet.row_dimensions[2].height = 15 sheet.column_dimensions['C'].width = 15
如果你不知道你应该设置多大的话，除了手动输出多次尝试之外，还可以先遍历该行（列），获取其中最大的字符长度，将行高（列宽）设置为最大的字符长度即可。 6 修改多个单元格 6.1 第一种方法
sheet_area = worksheet["A1:N2"]
font2 = Font(size = "9")
alignment_center = Alignment(horizontal='center', vertical='center', wrapText=True)
for i in sheet_area:
for j in i:
j.alignment = alignment_center
j.font = font2
6.2 第二种方法
row_range= range(1, 5)
col_range= range(5, 8)
font2 = Font(size = "9")
alignment_center = Alignment(horizontal='center', vertical='center', wrapText=True)
for i in row_range:
for j in icol_range
worksheet.cell(row=i, column=j).alignment = alignment_center
worksheet.cell(row=i, column=j8).font = font2
7 合并单元格
def merge_cells_value(sheet=None, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None):
if start_row > end_row:
fmt = "{end_row} must be greater than {start_row}"
raise ValueError(fmt.format(start_row=start_row, end_row=end_row))
if start_column > end_column:
fmt = "{end_column} must be greater than {start_column}"
raise ValueError(fmt.format(start_column=start_column, end_column=end_column))
if range_string is None:
fmt = '{start_column}{start_row}:{end_column}{end_row}'
range_string = fmt.format(start_row=start_row,
start_column=get_column_letter(start_column),
end_row=end_row,
end_column=get_column_letter(end_column))
v = None
for cells in sheet[range_string]:
for cell in cells:
if cell.value is not None:
v = cell.value
break
if v is not None:
break
sheet.merge_cells(range_string=range_string)
sheet['{0}{1}'.format(get_column_letter(start_column), str(start_row))] = v
merge_cells_value(sheet=sheet, start_row=1, start_column=1, end_row=2, end_column=1)
8 冻结指定的行和列
# c = worksheet['A2'] # 冻结首行 c = worksheet['C1'] # 冻结左边两列 worksheet.freeze_panes = c
freeze_panes可以冻结输入单元格坐标上面和左面的部分，比如如果需要冻结首行，输入的就是A2。
但是下面神坑来了：冻结首行没问题，但是必须在你打开Excel时看到的是表单第一行第一列是才生效，如果默认 打开时不是显示第一样第一列，Excel会出现让人费解的“吃行吃列不显示”的bug。
所以，如若要实现冻结首行，一定一定要先把默认打开显示设置为第一行第一列，也就是这句：
worksheet.topLeftCell = 'A2'9 复制表格中的sheet到另一个sheet中
下面的copy_sheet函数会将wb的所有sheet加到wb2中。
def copy_sheet(wb, wb2):
sheetnames = wb.sheetnames
for sheetname in sheetnames:
print(sheetname)
sheet = wb[sheetname]
sheet2 = wb2.create_sheet(sheetname)
# tab颜色
sheet2.sheet_properties.tabColor = sheet.sheet_properties.tabColor
wm = list(sheet.merged_cells)
if len(wm) > 0:
for i in range(0, len(wm)):
cell2 = str(wm[i]).replace('(,)', '')
sheet2.merge_cells(cell2)
for i, row in enumerate(sheet.iter_rows()):
sheet2.row_dimensions[i+1].height = sheet.row_dimensions[i+1].height
for j, cell in enumerate(row):
sheet2.column_dimensions[get_column_letter(j+1)].width = sheet.column_dimensions[get_column_letter(j+1)].width
sheet2.cell(row=i + 1, column=j + 1, value=cell.value)
# 设置单元格格式
source_cell = sheet.cell(i+1, j+1)
target_cell = sheet2.cell(i+1, j+1)
target_cell.fill = copy.copy(source_cell.fill)
if source_cell.has_style:
target_cell._style = copy.copy(source_cell._style)
target_cell.font = copy.copy(source_cell.font)
target_cell.border = copy.copy(source_cell.border)
target_cell.fill = copy.copy(source_cell.fill)
target_cell.number_format = copy.copy(source_cell.number_format)
target_cell.protection = copy.copy(source_cell.protection)
target_cell.alignment = copy.copy(source_cell.alignment)
10 设置表格缩放比例
workbook = load_workbook('test.xlsx')
for i in workbook.sheetnames:
workbook[i].views.sheetView[0].zoomScale = 60 # 设置缩放比例
workbook.save('test_result.xlsx')
结束语
看完这篇，还有更多知识点分享给你哦，自己慢慢找哈，就在下面链接。
推荐关注的专栏
机器学习：分享机器学习实战项目和常用模型讲解
数据分析：分享数据分析实战项目和常用技能整理
往期内容回顾
学习Python全套代码【超详细】Python入门、核心语法、数据结构、Python进阶【致那个想学好Python的你】
❤️ 学习pandas全套代码【超详细】数据查看、输入输出、选取、集成、清洗、转换、重塑、数学和统计方法、排序
学习pandas全套代码【超详细】分箱 *** 作、分组聚合、时间序列、数据可视化
学习NumPy全套代码【超详细】基本 *** 作、数据类型、数组运算、复制和试图、索引、切片和迭代、形状 *** 作、通用函数、线性代数
关注我，了解更多相关知识！
CSDN@报告，今天也有好好学习
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）