python操作Excel模块openpyxl
安装：pip install openpyxl
pip install openpyxl -i https://pypi.douban.com/simple 豆瓣镜像
想要在文件中插入图片文件，需要安装pillow : pip3 install pillow
pip3 install pillow -i https://pypi.douban.com/simple 豆瓣镜像
【openpyxl只能操作xlsx文件而不能操作xls文件】
创建文件：
from openpyxl import Workbook wb = Workbook() #创建文件对象
#如果设置参数writw_only=True只写模式，可以提高速度，但是该参数使得刚创建的工作簿中不含任何工作表，如果没有这个参数会包含一个空的工作表
ws = wb.active #获取活动工作表 ws['A1'] = 42 #写入数值 ws['B1'] = "你好"+"automation test" #写入字符串 ws.append([1, 2, 3]) #写入多个单元格--[写在新的一行] ws.append([10, 20, 30]) ws['A4'] = 42 wb.save("d:\\sample.xlsx") #保存文件
工作表操作：
from openpyxl import Workbook wb = Workbook() ws = wb.active ws['A1'] = 42 ws1 = wb.create_sheet("Mysheet") #创建一个工作表 # 参数：新工作表名称 ws2 = wb.create_sheet("Mysheet1", 0) #创建一个工作表 #参数2：插入的位置序号；默认最后位置； -1 倒数第二个 ws1.title = "物理" #修改工作表名字 ws2.title = u"化学" #修改工作表名字；必须是Unicode ws1.sheet_properties.tabColor = "FF0000" #设定工作表的标签的背景颜色
ws=wb.worksheets[0] #获取指定工作表对象
#参数：工作表序号 ws3=wb[u"化学"] #获取指定工作表对象---<Worksheet "化学"> #参数：工作表名称 ws3['A1'] = 420 ws3['B1'] = 520 print(ws3.title) #返回工作表名称 sheets=wb.sheetnames #获取所有工作表名称----['Sheet', '物理'] for sheet in sheets: print(sheet) for sheet in wb: #遍历文件的工作表 print(sheet.title) ws4 = wb.copy_worksheet(ws) #把ws工作表的内容复制到一张新工作表 #新工作表的名称为：Sheet Copy print(ws4.title) wb.save("d:\\sample.xlsx")
单元格操作：
from openpyxl import Workbook wb = Workbook() ws = wb.active ws['A1'] = 42 #写入数值--如果使用writw_only=True只写模式，就不能使用这种模式
d = ws.cell(row=4, column=2, value=10) #给指定行指定列赋值
#d 单元格对象---<Cell 'Sheet'.B4> #row=4 第4行---从1开始 #column=2 第2列---从1开始 #value=10 赋值10
#writw_only=True只写模式,不能使用cell方法
a=self.ws.cell(row=4, column=3) #返回指定行列的对象
#<Cell '合计'.C4>
b=a.value #返回值
ws.cell(row=4, column=3, value="及格")
#赋的值也可以是字符串
aa=ws["C4"].value #返回单元格的数据
bb=d.value #返回单元格的数据
print(aa,bb) wb.save("d:\\sample.xlsx")
ws['a1'].font=Font(name='黑体',size=36,bold=True,italic=False,underline='none',strike=True,color='FF0000FF') #单元格字体 #需要 from openpyxl.styles import Font #参数2：字号 #bold 是否加粗;italic 是否斜体 #underline='none' 不加下划线 #strike 是否有删除线 #color 文本颜色
col.font = Font(bold=True) #将A列设定为粗体
row.font = Font(underline="single") #将第一行设定为下划线格式
#边框可以选择的值为：'hair', 'medium', 'dashDot', 'dotted', 'mediumDashDot', 'dashed', 'mediumDashed', 'mediumDashDotDot', 'dashDotDot', 'slantDashDot', 'double', 'thick', 'thin'] #diagonal 表示对角线
ws['a1'].alignment=Alignment(horizontal='center',vertical='bottom',text_rotation=30) #文本对齐方式 #需要 from openpyxl.styles import Alignment #horizontal 水平对齐方式 #vertical 垂直对齐方式 #text_rotation 文本旋转角度
ws['a1'].fill=GradientFill(type='linear',stop=('FF0000','0000FF')) #使用渐变色填充单元格 #需要 from openpyxl.styles import GradientFill #stop=('FF0000','0000FF') 起始颜色 结束颜色
ws['a1'].fill=GradientFill(type='linear',stop=('FF0000','FF0000')) #使用固定颜色填充单元格背景 #需要 from openpyxl.styles import GradientFill
ws['a1'].border=Border(left=Side(style='medium',color='FF000000'),right=Side(style='double',color='00FF0000'),top=Side(style='thick',color='0000FF00'),bottom=Side(style='thin',color='FF00FF00')) #设置单元格边框 #需要 from openpyxl.styles import Border,Side
ws['a1'].comment=Comment(text='注释内容',author='李明') #给单元格设置注释 #需要 from openpyxl.comments import Comment #参数2：作者 ws['a1'].comment.width=units.points_to_pixels(50) #设置注释的宽度 ws['a1'].comment.height=units.points_to_pixels(20) #设置注释的高度--没有变化？？？ #需要 from openpyxl.utils import units
ws.merge_cells('A2:d4') #合并单元格--方式一
ws.merge_cells(start_row=6,start_column=1,end_row=8,end_column=3) #合并单元格--方式二
ws.unmerge_cells('A2:D4') #拆分单元格 #A2是合并后的单元格
ws.unmerge_cells(start_row=2,start_column=1,end_row=4,end_column=4) #拆分单元格
行和列操作：
from openpyxl import Workbook wb = Workbook() ws1 = wb.active ws1["A1"]=1 ws1["A2"]=2 ws1["A3"]=3 ws1["B1"]=4 ws1["B2"]=5 ws1["B3"]=6 ws1["C1"]=7 ws1["C2"]=8 ws1["C3"]=9 cell=ws1["A"] #返回指定列元组 #(<Cell 'Sheet'.A1>, <Cell 'Sheet'.A2>, <Cell 'Sheet'.A3>)---元组 for c in cell: print(c.value) cells=ws1["A:C"] #返回多列元组--返回A列到C列的元组---闭区间 #((<Cell 'Sheet'.A1>, <Cell 'Sheet'.A2>, <Cell 'Sheet'.A3>), (<Cell 'Sheet'.B1>, <Cell 'Sheet'.B2>, <Cell 'Sheet'.B3>), (<Cell 'Sheet'.C1>, <Cell 'Sheet'.C2>, <Cell 'Sheet'.C3>)) #返回值：元组；每一列又是一个元组 row=ws1[2] #返回指定行元组 #(<Cell 'Sheet'.A2>, <Cell 'Sheet'.B2>, <Cell 'Sheet'.C2>) for r in row: print(r.value) rows=ws1[1:3] #返回1到3行的元组 #((<Cell 'Sheet'.A1>, <Cell 'Sheet'.B1>, <Cell 'Sheet'.C1>), (<Cell 'Sheet'.A2>, <Cell 'Sheet'.B2>, <Cell 'Sheet'.C2>), (<Cell 'Sheet'.A3>, <Cell 'Sheet'.B3>, <Cell 'Sheet'.C3>)) #返回值：元组；每一行又是一个元组 rows=ws1.rows #获取所有行对象 # <generator object Worksheet._cells_by_row at 0x000000000333A048> cells=ws1.columns #获取所有列对象 #<generator object Worksheet._cells_by_col at 0x000000000333A8C8> wb.save("d:\\sample.xlsx")
ws.append(range(10)) #在最后追加一行
ws.append([10,20,30]) #在最后追加一行
ws.insert_cols(2) #在第二列位置插入一个空白列
ws.insert_cols(2,3) #在第二列位置开始插入3个空白列
#ws.insert_rows(2) #在第二行位置插入一个空白行 ws.insert_rows(2,3) #在第二行位置开始插入3个空白行
#ws.column_dimensions.group('B', 'D', hidden=True) #隐藏b到d列范围内的列 ws.column_dimensions.group('B', 'D', hidden=False) #不隐藏b到d列范围内的列
from openpyxl import Workbook wb = Workbook() ws1 = wb.active ws1["A1"]=1 ws1["A2"]=2 ws1["A3"]=3 ws1["B1"]=4 ws1["B2"]=5 ws1["B3"]=6 ws1["C1"]=7 ws1["C2"]=8 ws1["C3"]=9 rs=ws1.rows #获取所有行对象 i=0 rsl=[] for row in rs: i=i+1 #计算行数 rsl.append(row) #获取所有行列表 #[(<Cell 'Sheet'.A1>, <Cell 'Sheet'.B1>, <Cell 'Sheet'.C1>), (<Cell 'Sheet'.A2>, <Cell 'Sheet'.B2>, <Cell 'Sheet'.C2>), (<Cell 'Sheet'.A3>, <Cell 'Sheet'.B3>, <Cell 'Sheet'.C3>)] #每一行是一个元组 r=rsl[0] #获取第一行 a=rsl[0][0] #获取第一行第一列的单元格对象 i=rsl[0][0].value #获取第一行第一列的单元格的值 r=rsl[-1] #获取最后行 cs=ws1.columns #获取所有列对象 csl=[] for cell in cs: csl.append(cell) #获取所有列列表 i=len(csl) #列数 c=csl[0] #获取第一列 a=csl[0][0] #获取第一列第一行的单元格对象 a=csl[0][0].value #获取第一列第一行的单元格数据 c=csl[-1] #获取最后一列 print(i) wb.save("d:\\sample.xlsx")
打开已经存在的文件：
from openpyxl import Workbook from openpyxl import load_workbook wb = load_workbook('d:\\sample.xlsx') #读入文件
#需要from openpyxl import load_workbook
#没有data_only=True 参数，遇到公式时读出的公式。有data_only=True 参数，读出的是公式计算后的值
ws=wb.active aa=ws["A1"].value i=ws.max_row #返回总行数 ii=ws.max_column #返回总列数 print(aa,i,ii) wb.save("d:\\sample.xlsx")
公式：
如上图：D1单元格用了公式
from openpyxl import Workbook from openpyxl import load_workbook wb = load_workbook('d:\\sample.xlsx') #读入文件 ws=wb.active aa=ws["D1"].value print(aa,type(aa))
D1单元格返回的数据和类型是：=SUM(A1:C1) <class 'str'>
返回的是公式还是公式计算后的值，就得看带不带data_only=True 参数
from openpyxl import Workbook from openpyxl import load_workbook wb = load_workbook('d:\\sample.xlsx') #读入文件 ws=wb.active ws["A1"] = "=SUM(1, 1)" #使用公式 wb.save("d:\\sample.xlsx")
单元格类型：
ty=ws["A1"].number_format #返回单元格类型
如果是常规[数值、公式、字符串]，返回General
如果是百分数，返回0.00%
图片操作：
需要先安装Pilow
需要 from openpyxl.drawing.image import Image
img = Image('d:\\ss\\1.jpg') #读入图片 img.height //=5 #高缩小为五分之一 img.width //=5 #宽缩小为五分之一 ws.add_image(img, 'g1') #g1单元格插入图片
画饼状图、柱状图：
资料：https://www.cnblogs.com/zeke-python-road/p/8986318.html
