EXCEL利器 —— openpyxl_openpyxl教程

一瓦清雪此觉冬，半株残香待明夏。燃尽长夜心亦然，轻羽飘落封我情。（没有水平，见谅）

软件：Jupyter NoteBook3
语言：Python
作者：落寞红颜玉玫瑰
时间：霜降第二候：蛰虫咸俯

1. 认识Excel

2. 关于openpyxl

3. openpyxl常用操作

3.1 创建新表

3.2 操作已经存在的文件

1. 认识Excel

         Excel是Microsoft微软公司推出的办公软件Office中的一个重要组成部分,也是目前最流行的关于电子表格处理的软件之一。它具有强大的计算、分析和图表等功能，是公司目前最常用的办公数据表格软件。Excel中内置的公式和函数,可能帮忙用户进行复杂的计算；由于Excel在数据运算方面有强大的功能,使它成为用户办公必不可少的一个常用办公软件。

Excel常用的文件类型

• xls文件： 主要是2007年以前的文件格式；
• xlsx文件： 2007年以后的文件格式；
• xlsm文件: 工作区文件（宏文件），用来保存当前的工作状态；
• xlsb文件: 二进制文件，用储存文件，其体积小，易读写（对于电脑）；
• csv文件: 逗号分隔数据保存的文件。

         随着社会发展，我们的工作量也越来越多，工作的复杂度也在逐步提高，每天做着重复且繁琐的事，这样不仅是对工作信心的一种打击；长期以往，也是在消耗对生活的热情。如何改变这种现状，首先就是有个良好的心态，然后呢，把自己从繁杂琐碎且重复的工作中脱离出来，借助一个优秀的工具，去帮助自己实现这一切，进而实现自我价值的提升。

2. 关于openpyxl

具体请参阅官方文档： https://pypi.org/project/openpyxl/

"""
Introduction
openpyxl is a Python library to read/write Excel 2010 xlsx/xlsm/xltx/xltm files.

It was born from lack of existing library to read/write natively from Python the Office Open XML format.

All kudos to the PHPExcel team as openpyxl was initially based on PHPExcel.
"""
1
2
3
4
5
6
7
8

         以上是官方文档对于openpyxl的介绍，其作为流行的办公自动化模块之一，其优点也是很明显的，无论是在文件的读取亦或是写入。若是不对当前EXCEL中的数据进行分析（pivot，map，group，merge，esc）时，单独的操作数据表来说，openpyxl比pandas更加适合。

注1：openpyxl无法对 .xls 类型文件进行操作，如需操作此类型文件，需提前转为 .xlsx 类型文件。当然，也可以通过python自带模块来转化，本质也是调动EXCEL底层来操作，下面是其代码。

def trans(oldFilePath,newFilePath):
	"""
	params: oldFilePath,旧文件路径 .xls 文件格式;
			newFilePath,新文件路径 .xlsx 文件格式。
	"""
	#导入所需模块
    import win32com.client,os
    #调动EXCEL
    if os.path.exists(oldFilePath):
    	excel = win32com.client.gencache.EnsureDispatch('Excel.Application')
    	#打开文件
    	wb = excel.Workbooks.Open(oldFilePath)
    	#检查新文件路径，若存在，则删除
    	if os.path.exists(newFilePath):
        	os.remove(newFilePath)
		#保存为 .xlsx 类型的文件。
    	wb.SaveAs(newFilePath, FileFormat=51)# 51 表示的是xlsx格式
    	wb.Close()
    	#记得退出EXCEL,不要空载线程，容易死。
    	excel.Application.Quit()
    	#返回新的文件路径
    	return newFilePath
    else:
 		print("没有{}此文件，请确认文件是否存在后重试。".format(oldFilePath))
 		return False
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

3. openpyxl常用操作

不知道怎么开始，从创建新表开始吧。

3.1 创建新表

#os：文件路径
import os
#操作EXCEL
import openpyxl
#复制文件的模块
import shutil
1
2
3
4
5
6

path = r"C:\Users\weixi\Desktop\new_workbook.xlsx"
1

#创建一个新的工作表,其包含一个活动表，分别是：Sheet1。
wb = openpyxl.Workbook()
#获得活动表
ws = wb.active
#sheet表有很多属性(属性不加括号，方法需要加括号调动），我们这儿用了sheet.title来获得sheet表的名字。
print(ws.title) #output：Sheet
#将新工作表保存在前面写的路径下
wb.save(path)
1
2
3
4
5
6
7
8

3.2 操作已经存在的文件

#文件路径
exist_path = r"C:\Users\weixi\Desktop\data.xlsx"
#打开工作表，其可以传很多参数，但我常用的只有以下几个:
#read_only：默认False，为True时表示只读，不可更改数据；
#data_only：默认False，为True时，表示以纯数据载入，有公式的单元格将被转化为计算后数值载入
wb = openpyxl.load_workbook(exist_path)
1
2
3
4
5
6

wb.active
#output：<Worksheet "入厂煤化验日报(批次+采样)">
#查看工作表的所有sheet表
print(wb.sheetnames)
#output：['入厂煤化验日报(批次+采样)']
#打开此sheet表
ws = wb[wb.sheetnames[0]]
1
2
3
4
5
6
7

其在EXCEL中的所有属性，在此模块中都可以查看和修改，如：边框，填充（包括渐变填充，图案填充，纯色填充等），字体，对齐，数字格式等等，只要EXCEL可以实现，那么openpyxl也可以，只有少数的不可以做，但基本用不到（我没遇到过，应该是有的）。

#插入行，两个参数，第一个表示需要插入的行位置，第二个表示要插入的行数，默认插入一行
ws.insert_rows(ws.max_row）
#插入列，两个参数，第一个表示需要插入的列位置，第二个表示要插入的列数，默认插入一列
ws.insert_cols(4)
1
2
3
4

#删除行，两个参数，第一个表示需要删除的行位置，第二个表示要删除的行数，默认删除一行
ws.delete_rows(ws.max_row)

#删除列，两个参数，第一个表示需要删除的列位置，第二个表示要删除的列数，默认删除一列
ws.delete_cols(4)
#合并的单元格，有两种语法，谨记，openpyxl合并的单元格只有只读属性，如需改变值，需要先取消合并，改变值后，再合并单元格
ws.merge_cells("A1:R1")
ws.merge_cells(start_row=1,start_column=17,end_row=2,end_column=17)
#取消合并单元格，与合并单元格一样
ws.unmerge_cells("A1:R1")
ws.unmerge_cells(start_row=1,start_column=17,end_row=2,end_column=17)
1
2
3
4
5
6
7
8
9
10
11

#获得当前单元格对应列的字母
print(ws.cell(1,1).column_letter #output：A
#两个属性，查看其最大行数和列数（写了数据或者带格式的单元格）
print(ws.max_row,ws.max_column)
#output：118，30
#查看列宽，行高
print(ws.column_dimensions["A"].width,ws.row_dimensions[12].height）
#output：8.0，20.25

#查看其边框信息（单个单元格）
print(ws.cell(1,1).border)
"""
<openpyxl.styles.borders.Border object>
Parameters:
outline=True, diagonalUp=False, diagonalDown=False, start=None, end=None, left=<openpyxl.styles.borders.Side object>
Parameters:
style='thin', color=<openpyxl.styles.colors.Color object>
Parameters:
rgb=None, indexed=None, auto=True, theme=None, tint=0.0, type='auto', right=<openpyxl.styles.borders.Side object>
Parameters:
style='thin', color=<openpyxl.styles.colors.Color object>
Parameters:
rgb=None, indexed=None, auto=True, theme=None, tint=0.0, type='auto', top=<openpyxl.styles.borders.Side object>
Parameters:
style='thin', color=<openpyxl.styles.colors.Color object>
Parameters:
rgb=None, indexed=None, auto=True, theme=None, tint=0.0, type='auto', bottom=<openpyxl.styles.borders.Side object>
Parameters:
style='thin', color=<openpyxl.styles.colors.Color object>
Parameters:
rgb=None, indexed=None, auto=True, theme=None, tint=0.0, type='auto', diagonal=<openpyxl.styles.borders.Side object>
Parameters:
style=None, color=None, vertical=None, horizontal=None
"""
#查看单元格字体
print(ws.cell(1,1).font)
"""
<openpyxl.styles.fonts.Font object>
Parameters:
name='微软雅黑', charset=134, family=2.0, b=False, i=False, strike=None, outline=None, shadow=None, condense=None, color=None, extend=None, sz=12.0, u=None, vertAlign=None, scheme=None
"""
#查看单元格对齐格式
print(ws.cell(1,1).alignment)
"""
<openpyxl.styles.alignment.Alignment object>
Parameters:
horizontal='center', vertical='center', textRotation=0, wrapText=None, shrinkToFit=None, indent=0.0, relativeIndent=0.0, justifyLastLine=None, readingOrder=0.0
"""
print(ws.cell(1,1).fill)
"""
<openpyxl.styles.fills.PatternFill object>
Parameters:
patternType=None, fgColor=<openpyxl.styles.colors.Color object>
Parameters:
rgb='00000000', indexed=None, auto=None, theme=None, tint=0.0, type='rgb', bgColor=<openpyxl.styles.colors.Color object>
Parameters:
rgb='00000000', indexed=None, auto=None, theme=None, tint=0.0, type='rgb'
"""
#查看数字格式
print(ws.cell(1,1).number_format) #output：0
#判断是否存在格式
ws.cell(1,1).has_style #output：True
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

注：在EXCEL中，单元格的确定一般用行和列来确定，行为数字，列为字母，比如“A1”表示表格左上角第一个单元格。

在openpyxl所打开的表中，其有两种表示：

1）、ws.cell(1,1)表示“A1”，为左上角第一个单元格，其起始列和起始行均为1，行在前，列在后。
2）、ws[“A”][0]表示“A1”，为第一个单元格，列用字母表示，行用数字表示，起始行为0（偏向列表，按列表理解）。

#输出单元格的值
print(ws["A"][0].value）#output："入厂煤化验日报(2023-10-21至2023-10-25)"
print(ws.cell(1,1).value) #output："入厂煤化验日报(2023-10-21至2023-10-25)"
1
2
3

上面便是我日常工作中所需要使用到的一些属性，下面介绍一下如何更改属性及单元格的值。

#单元格的值的改变通过赋值来直接改变，只有保存后改变数据才生，关闭则数据不发生变化。
ws["A"][2].value = "gaibian"   #改变第3行第一列单元格的值。
ws.cell(3,1).value = "cuoguo" #改变第3行第一列单元格的值。
1
2
3

关于单元格格式的改变一般都是一个整体，既需要边框，也需要字体，对齐方式等，下面是自己写的一个片段，用到了openpyxl的styles模块，具体见下：

def sheetBeautiful(sheet_name):
    
 #对齐方式：水平居中，垂直居中   align=openpyxl.styles.Alignment(horizontal="center",vertical="center")
    #边框线条：细，颜色为黑色
    side=openpyxl.styles.Side(style="thin",color="000000")
    
 #填充：纯色填充-》背景色为红色的一种  
    fill=openpyxl.styles.PatternFill(patternType="solid",bgColor="FABF8F",fgColor="FABF8F")
  #边框：用前面设置好
    border=openpyxl.styles.Border(top=side,left=side,bottom=side,right=side)
    
    font = openpyxl.styles.Font(name=u"楷体",size=12)
    
    if sheet_name.max_row == 5 and sheet_name.cell(4,1).value == "合计":
        
        end_row = 5
        
    else:
        
        end_row = sheet_name.max_row+1
    
    for i in range(1,end_row):
        
        for j in range(1,17):
            
            if j in range(8,17):
                
                sheet_name.cell(i,j).number_format="0.00"
                
            sheet_name.cell(i,j).alignment=align
            
            sheet_name.cell(i,j).border=border
        #改变数字格式   
        sheet_name.cell(i,15).number_format="0.000"
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

wb.close()#改变的数据，格式等不会保存
wb.save(new_path) #保存改变之后的数据，new_path为想要保存的路径，不加路径则保存到原来的路径。
1
2

更多高级的操作，无非就是配合其他模块（numpy,os,shutil,copy）以及基础的循环，判断等，可以进行整理数据，比较数据，分表等操作。

以上便是本期所有内容，openpyxl模块还有更多好用的方法与属性，想要深入了解的可查看官方文档。
----九月授衣，十月获稻，葭月潜龙，腊月嘉岁。注意保暖，各位，改天见。----