【工作必备技能整理】openpyxl全套教程，提升工作效率【接力pandas】

大家早上好，本人姓吴，如果觉得文章写得还行的话也可以叫我吴老师。欢迎大家跟我一起走进数据分析的世界，一起学习！

感兴趣的朋友可以关注我的数据分析专栏，里面有许多优质的文章跟大家分享哦。

---

openpyxl在我看来，就是用代码代替我们的双手，在EXCEL上操作。在EXCEL上能做到的操作，openpyxl基本都能实现。

我们在上班的时候经常需要处理数据，运算啥的自然是交给pandas，但是输出的时候呢？这样子输出的格式是我们满意的吗？如果说输出后再到excel上运算，会不会总感觉还不够那么的自动化？

pandas现在有了df.style，可以帮助我们处理输出的表格格式，但是说实话，操作起来还真的很不方便，这也是为什么上次我写关于pandas的这篇博客的时候，在最后介绍处理表格格式的时候，挖了一个坑，说有机会再介绍，但是大概率是不会再去介绍df.style了，因为真的不好用，或者说没有openpyxl好用。

所以我现在处理数据并且输出到表格的时候，都是pandas(处理数据)+openpyxl（输出格式）

那么之前已经整理了好几篇超详细的pandas博文，今天给大家介绍一下openpyxl的各种实用功能，绝对干货满满，欢迎收藏哦！

导入所需模块

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, colors

0 xls转xlsx文件

首先openpyxl里只能操作xlsx文件，如果是xls文件，需要进行转换。

import win32com.client as win32def 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.sheetnames

3.2 读取指定的sheet

# Sheet1是sheet名
worksheet= workbook['Sheet1']

3.3 获取sheet中的最大行数列数

# 最大行数
max_row = worksheet.max_row
# 最大列数
max_col = worksheet.max_column

3.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 = 58

4.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_fill

4.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 = font 

4.3 修改单元格居中&自动换行

• 垂直（horizontal）、居中（vertical）、自动换行（wrapText）

alignment_center = Alignment(horizontal='center', vertical='center', wrapText=True)
worksheet.cell(5, 8).alignment = alignment_center

4.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 = border

4.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_centerj.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_rangeworksheet.cell(row=i, column=j).alignment = alignment_centerworksheet.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 = Nonefor cells in sheet[range_string]:for cell in cells:if cell.value is not None:v = cell.valuebreakif v is not None:breaksheet.merge_cells(range_string=range_string)sheet['{0}{1}'.format(get_column_letter(start_column), str(start_row))] = vmerge_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.sheetnamesfor sheetname in sheetnames:print(sheetname)sheet = wb[sheetname]sheet2 = wb2.create_sheet(sheetname)# tab颜色sheet2.sheet_properties.tabColor = sheet.sheet_properties.tabColorwm = list(sheet.merged_cells)if len(wm) > 0:for i in range(0, len(wm)):cell2 = str(wm[i]).replace('(<CellRange ', '').replace('>,)', '')sheet2.merge_cells(cell2)for i, row in enumerate(sheet.iter_rows()):sheet2.row_dimensions[i+1].height = sheet.row_dimensions[i+1].heightfor j, cell in enumerate(row):sheet2.column_dimensions[get_column_letter(j+1)].width = sheet.column_dimensions[get_column_letter(j+1)].widthsheet2.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@报告，今天也有好好学习