Python_excel基本操作一(Openpyxl)
excel操作
一、 安装Openpyxl包
pip install openpyxl==2.5.4
想要在文件中插入图片文件,需要安装pillow,安装文件:PIL-fork-1.1.7.win-amd64-py2.7.exe
二、 创建一个excel 文件,并写入不同类的内容
# -*- coding: utf-8 -*- import locale from openpyxl import Workbook wb = Workbook() #创建文件对象 # grab the active worksheet ws = wb.active #获取第一个sheet # Data can be assigned directly to cells ws['A1'] = 42 #写入数字 ws['B1'] = "光荣之路"+"automation test" #写入中文 # Rows can also be appended ws.append([1, 2, 3]) #写入多个单元格(在下一行写入) wb.save("e:\\a.xlsx")
运行结果:
三、单元格写入时间
import locale
from openpyxl import Workbook
import datetime import time import locale wb = Workbook() ws = wb.active ws['A2'] = datetime.datetime.now() #写入一个当前时间 #写入一个自定义的时间格式 locale.setlocale(locale.LC_CTYPE, 'chinese') ws['A3'] =time.strftime("%Y年%m月%d日 %H时%M分%S秒", time.localtime()) # Save the file wb.save("e:\\sample.xlsx")
运行结果:
四、多个sheet的操作
from openpyxl import Workbook wb = Workbook() ws = wb.create_sheet("Mysheet1") ws1 = wb.create_sheet("Mysheet") ws1.title = "New Title" ws2 = wb.create_sheet("Mysheet", 0) #设定sheet的插入位置,在最前面 ws2.title = u"光荣之路自动化测试培训"#修改sheet的名称 ws1.sheet_properties.tabColor = "1072BA" #设定颜色码 #获取某个sheet对象 print (wb["光荣之路自动化测试培训"]) print (wb["New Title" ])
运行结果:
#获取全部sheet的名字,遍历sheet名字
print (wb.sheetnames) for sheet_name in wb.sheetnames: print (sheet_name)
运行结果:
#遍历sheet对象,按照sheet顺序获取
for sheet in wb: print (sheet)
运行结果:
#复制一个sheet wb["New Title" ]["A1"]="gloryroad" source = wb["New Title" ] target = wb.copy_worksheet(source) # Save the file wb.save("e:\\sample.xlsx")
五、操作单元格
1、读取某个单元格的值
# -*- coding: utf-8 -*- from openpyxl import Workbook wb = Workbook() ws1 = wb.create_sheet("Mysheet") #创建一个sheet ws1["A1"]=123.11 ws1["B2"]="光荣之路" d = ws1.cell(row=4, column=2, value=10)#行号列号都从1开始的
print (ws1["A1"].value) print (ws1["B2"].value) print (d.value) print (ws1.cell(row=4,column=2).value) # Save the file wb.save("e:\\sample.xlsx")
2、批量操作单元格(列操作)
# -*- coding: utf-8 -*- from openpyxl import Workbook wb = Workbook() ws1 = wb.create_sheet("Mysheet") #创建一个sheet 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 #操作单列 print (ws1["A"]) for cell in ws1["A"]:#遍历A所在的整个列 print (cell.value) #打印最大行号,最大列号 print(ws1.max_row,ws1.max_column) print(ws1.min_row,ws1.min_column)
3、行操作
# -*- coding: utf-8 -*- from openpyxl import Workbook wb = Workbook() ws1 = wb.create_sheet("Mysheet") #创建一个sheet 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 #操作多行 row_range=ws1[1:3] print(row_range) for row in row_range:#遍历A所在的整个列 for cell in row: print (cell.value)
4、获取所有行列
#获取所有行
print (ws1.rows)
for row in ws1.rows:
print (row)
print ("*"*50)
#获取所有列
print (ws1.columns)
for col in ws1.columns:
print (col)
5、百分比
# -*- coding: utf-8 -*-
from openpyxl import Workbook
from openpyxl import load_workbook
wb = load_workbook('e:\\sample.xlsx')
wb.guess_types = False
ws=wb.active
ws["D1"]="12%"#false是百分比,true是小数
print (ws["D1"].value)
# Save the file
wb.save("e:\\sample.xlsx")