python~openpyxl操作excel文件

目录

1.创建文件，写入数据并保存

2.创建sheet,设定sheet属性及遍历sheet

3.操作单元格

4.批量操作单元格

获取所有的行对象

获取所有的列对象

5.操作已存在的文件

6.单元格类型

7.使用公式

8.合并单元格

9.操作excel文件插入图片

10.隐藏单元格

11.设定单元格字体颜色

12.设定字体和大小

13.设定行和列的字体

14.设定单元格的边框、字体、颜色、大小和边框背景色

15.给某个范围设定样式

---

1.创建文件，写入数据并保存

思路：导入模块后，创建文件，获得sheet对象，然后在sheet对象里操作，最后保存文件

#encoding:utf-8


import time
from openpyxl import Workbook
     
wb=Workbook()      #创建文件对象
wa=wb.active       #获取第一个sheet

#写入各种类型的数据
wa["A1"]="姓名"
wa["B1"]=42
wa["C1"]="test"+"automation test"

#写入多个单元格
wa.append([1,2,3])

#写入时间

wa["A3"]=time.time()
wa["A4"]=time.strftime("%Y-%m-%d %H:%M:%S",time.localtime())

wb.save("tester.xlsx")   #保存文件对象

2.创建sheet,设定sheet属性及遍历sheet

#encoding:utf-8

from openpyxl import Workbook

wb=Workbook()

ws1=wb.create_sheet("mysheet")            #创建一个sheet
ws1.title="New Title"                     #更改sheet名字

ws2=wb.create_sheet("mysheet",0)          #创建sheet时，设定sheet的插入位置
ws2.title="excel操作"

ws1.sheet_properties.tabColor="102765"    #设定sheet标签的背景颜色

#获取某个sheet对象
print (wb.get_sheet_by_name("excel操作"))        
print (wb["New Title"])

#获取全部sheet的名字，并遍历
print (wb.sheetnames)
print (wb.get_sheet_names())    #二者等价

for sheet_name in wb.sheetnames:
    print (sheet_name)

print ("*"*50)
for sheet in wb:
    print (sheet.title)         #二者等价

#复制一个sheet
wb["New Title"]["A1"]="tester"
source=wb["New Title"]
target=wb.copy_worksheet(source)
target.title="target sheet"

wb.save("tester.xlsx")

3.操作单元格

from openpyxl import Workbook

wb=Workbook()
ws1=wb.create_sheet("Newsheet")

ws1["A1"]=123.0
ws1["B2"]="青藏高原"
d=ws1.cell(row=4,column=2,value="高兴")    #设置第4行，第2列的值为'高兴'

#打印出指定单元格位置的值
print (ws1["A1"].value)
print (ws1["B2"].value)

print (d)                  #输出为<Cell 'Newsheet'.B4>对象
print (dir(d))             #可对单元格进行多种操作
print (d.value)
print (ws1.cell(row=4,column=2).value)

wb.save("tester.xlsx")

4.批量操作单元格

from openpyxl import Workbook

wb=Workbook()

ws1=wb.create_sheet("mysheet")
#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

"操作单列"
print (ws1["A"])
for cell in ws1["A"]:
    print (cell.value)

"操作多列,获取每一个值"
print (ws1["A:C"])    #-->闭区间
for column in ws1["A:C"]:
    for cell in column:
        print (cell.value)

"操作多行"
row_range=ws1[1:3]      #闭区间
print (row_range)
for row in row_range:
    for cell in row:
        print (cell.value)

print ("*"*30)
for row in ws1.iter_rows(min_row=1,min_col=1,max_row=3,max_col=3):
    for cell in row:
        print (cell.value)

“获得所有行”
print (ws1.rows)
print (ws1.max_row)   #获得行数
for row in ws1.rows:
    for cell in row:
        print (cell.value)

“获得所有列”
print (ws1.columns)
print (ws1.max_column)
for col in ws1.columns:
    for cell in col:
        print (cell.value)

"单元格值保存为百分数"
from openpyxl import load_workbook

wb=load_workbook("sample.xlsx")
#wb.guess_types=True        #输出结果为小数0.12，格式类型
wb.guess_types=False        #输出结果为百分数

ws=wb.active
ws["A1"]="12%"
print (ws["A1"].value)

wb.save("sample.xlsx")

获取所有的行对象

from openpyxl import load_workbook

wb=load_workbook("sample.xlsx")
ws=wb.active
#ws=wb.get_active_sheet()  功能同上

for row in ws.rows:
    for i in range(len(row)):
        print (row[i].value)

rows=[]
for row in ws.iter_rows():
    print (row)      #打印每一行
    rows.append(row)

print ("打印所有行")       
print (rows)
print ("打印第一行")
print (rows[0])
print ("打印第一行第一列的单元格对象")
print (rows[0][0])
print ("打印第一行第一列的单元格对象的值")
print (rows[0][0].value)
print ("打印最后一行")
#print (rows[len(rows)-1])
print (rows[-1])
print ("打印最后一行最后一列的单元格对象")
print (rows[-1][-1])
print ("打印最后一行最后一列的单元格对象的值")
print (rows[-1][-1].value)

获取所有的列对象

from openpyxl import load_workbook

wb=load_workbook("sample.xlsx")
#ws=wb.active
ws=wb.get_active_sheet()

cols=[]
for col in ws.iter_cols():
    #print (col)   #打印每一列
    cols.append(col)

print ("打印所有列")
print (cols)
print ("打印第一列")
print (cols[0])
print ("打印第一列第一行的单元格对象")
print (cols[0][0])
print ("打印第一列第一行的单元格对象的值")
print (cols[0][0].value)
print ("*"*50)
print ("打印最后一列")
print (cols[-1])
print ("打印最后一列最后一行的单元格对象")
print (cols[-1][-1])
print ("打印最后一列最后一行的单元格对象的值")
print (cols[-1][-1].value)

5.操作已存在的文件

from openpyxl import load_workbook

wb=load_workbook("sample.xlsx")
wb.guess_types=True
#wb.guess_types=False
ws=wb.active

ws["D1"]="12%"
print (ws["D1"].value)

wb.save("sample.xlsx")

6.单元格类型

from openpyxl import load_workbook
import datetime

wb=load_workbook("sample.xlsx")
wb.guess_type=True
ws=wb.active

ws["A1"]=datetime.datetime(2018,11,23)
print (ws["A1"].value)
print (ws["A1"].number_format)

ws["A2"]="12%"
print (ws["A2"].number_format)

ws["A3"]=1.1
print (ws["A3"].number_format)

ws["A4"]="中国"
print (ws["A4"].number_format)

wb.save("sample.xlsx")

7.使用公式

from openpyxl import load_workbook

wb=load_workbook("sample.xlsx")
ws=wb.active

ws["A1"]=11
ws["A2"]=12
ws["A3"]=13
ws["A4"]="=SUM(1,1)"
ws["A5"]="=SUM(A1:A3)"

print (ws["A4"].value)  #打印的是公式内容,不是计算结果；表格中实际保存的公式计算结果
print (ws["A5"].value)

wb.save("sample.xlsx")

8.合并单元格

from openpyxl import Workbook
#from openpyxl import load_workbook

wb=Workbook()
#wb=load_workbook("test.xlsx")
ws=wb.active

ws.merge_cells("A1:C1")          #合并单元格
#ws.unmerge_cells("A1:C1")       #取消合并单元格

#ws.merge_cells(start_row=2,start_column=1,end_row=2,end_column=4)      #合并单元格
#ws.unmerge_cells(start_row=2,start_column=1,end_row=2,end_column=4)    #取消合并单元格

wb.save("test.xlsx")

9.操作excel文件插入图片

#pip install openpyxl==2.5.4     #安装指定版本
#pip install pillow              #安装pillow


from openpyxl import Workbook
from openpyxl.drawing.image import Image

wb=Workbook()
sheet=wb.active
img=Image(r"C:\Users\zhigang\Desktop\test.png")
sheet.add_image(img,"A1")

wb.save("sample.xlsx")
--------------------------------------------------
或者换一种解决办法
import xlsxwriter

wb = xlsxwriter.Workbook('pict.xlsx')
sheet = wb.add_worksheet('demo')
sheet.insert_image('D4','redis.png')
wb.close()

10.隐藏单元格

from openpyxl import Workbook,load_workbook

#wb=Workbook()
wb=load_workbook("sample.xlsx")
sheet=wb.active

sheet.column_dimensions.group("A","D",hidden=True)     #隐藏A到D范围内的列
#sheet.row_dimensions.group(1,4,hidden=True)           #隐藏1到4范围内的行

wb.save("sample.xlsx")

11.设定单元格字体颜色

from openpyxl import Workbook
from openpyxl.styles import colors
from openpyxl.styles import Font

wb=Workbook()
ws=wb.active

a1=ws["A1"]

"italic 斜体字"
a1.font=Font(color=colors.RED,italic=True)
a1.value="FAIL"

wb.save("sample1.xlsx")

12.设定字体和大小

from openpyxl import Workbook
from openpyxl.styles import Font
from copy import copy

wb=Workbook()
ws=wb.active

a1=ws["A1"]                   #<Cell 'Sheet'.A1> 单元格对象赋值给a1
d4=ws["D4"]
a1.value="abc"

ft1=Font(name="宋体",size=14)
ft2=copy(ft1)                 #复制字体对象
ft2.name="Tahoma"

print (ft1.name)       #宋体
print (ft2.name)       #Tahoma
print (ft2.size)
a1.font=ft1
#a1.font = Font(name="楷体",color=colors.RED, bold=True,underline="double",size=20)  推荐此写法

wb.save("sample.xlsx")

13.设定行和列的字体

from openpyxl import Workbook
from openpyxl.styles import Font

wb=Workbook()
ws=wb.active

row=ws.row_dimensions[1]
row.font=Font(underline="single")   #将第一行设定为下划线格式

col=ws.column_dimensions["A"]
#print (col)
col.font=Font(bold=True)            #将A列设定为bold黑体

wb.save("sample.xlsx")

14.设定单元格的边框、字体、颜色、大小和边框背景色

from openpyxl import Workbook
from openpyxl.styles import Font
from openpyxl.styles import NamedStyle,Font,Border,Side,PatternFill

wb=Workbook()
ws=wb.active

highlight=NamedStyle(name="highlight")
highlight.font=Font(bold=True,size=20,color="ff0100")        #字体
highlight.fill=PatternFill("solid",fgColor="DDDDDD")         #填充颜色
bd=Side(style="thick",color="000000")                        #设定边框颜色和大小
highlight.border=Border(left=bd,top=bd,right=bd,bottom=bd)   #设定单元格四周边框
print (dir(ws["A1"]))

ws["A1"].style=highlight       

wb.save("sample.xlsx")

15.给某个范围设定样式

from openpyxl.styles import Border,Side,PatternFill,Font,GradientFill,Alignment
from openpyxl.styles import Font, Border, Side,PatternFill
from openpyxl import Workbook

def style_range(ws,cell_range,border=Border(),fill=None,font=None,alignment=None):
    top = Border(top=border.top)
    left = Border(left=border.left)
    right = Border(right=border.right)
    bottom = Border(bottom=border.bottom)

    first_cell = ws[cell_range.split(":")[0]]  #B2:F4
    rows = ws[cell_range]
    if font:
        first_cell.font = font

    for row in rows:
        for cell in row:
            cell.border = cell.border + top + bottom + right + left
            cell.fill = fill
            cell.font = font
            cell.alignment = alignment


wb = Workbook()
ws = wb.active
my_cell = ws['B2']
my_cell.value = "My Cell"

thin = Side(border_style="thin",color = "000000")
double = Side(border_style="double",color="ff0000")

border = Border(top=double,left=thin,right=thin,bottom=double)
fill = PatternFill("solid",fgColor="DDDDDD")
#fill = GradientFill(stop=("000000","FFFFFF"))
font = Font(b=True,color="FF0000")
al = Alignment(horizontal="center",vertical="center")

style_range(ws,'B2:F4',border=border,fill=fill,font=font,alignment=al)

wb.save("a.xlsx")