python3编程基础：操作excel(

目录

前言

安装模块

例1：创建一个excel 文件，并写入不同类的内容

例2：写入时间

例3：创建sheet

例4 ：操作单元格

例5 ：操作行/列/指定区域

例6：显示小数

例7：获取所有的行对象

例8：获取所有的列对象

例9：单元格类型

例10：公式

例11：合并单元格/取消合并单元格

例12：插入图片

---

python中操作excel的模块有很多，比如xlrd,xlwt,openpyxl,xlutils等。前两个是一套，一个读一个写。注意：xlwt模块，只能支持到excel2003，也就是扩展名为.xls的excel；xlrd模块可以支持读取07版本，也就是.xlsx扩展名的excel。每个模块都有一些优缺点，本文以openpyxl模块为例来进行介绍。

安装openpyxl和pillow(在文件中插入图片时使用)

py -3 -m pip install openpyxl==2.5.4（指定版本安装）

py -3 -m pip install pillow

# -*- coding: utf-8 -*-
import locale
from openpyxl import Workbook
wb = Workbook()    #创建文件对象


#获取第一个sheet
ws = wb.active     
# 将数据写入到指定的单元格
ws['A1'] = 42      #写入数字
ws['B1'] = "自动化"+"automation test" #写入中文


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

#保存为a.xlsx
wb.save("a.xlsx")

 

from openpyxl import Workbook
import datetime
import time
import locale
wb = Workbook()    #创建文件对象


ws = wb.active     #获取第一个sheet

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("sample.xlsx")

前面的例子都是操作默认创建的sheet,也可以用create_sheet()方法生成指定名称的sheet进行操作。

from openpyxl import Workbook 
wb = Workbook()

ws = wb.create_sheet("kongsh") 
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名字
print (wb.sheetnames)
for sheet_name in wb.sheetnames:
    print (sheet_name)
    print(wb[sheet_name])

#遍历获取sheet对象，按照sheet顺序获取
for sheet in wb:
    print (sheet)
    print(sheet.title)#获取sheet名称

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

target.title="New copy Title"

#保存文件
wb.save("a.xlsx")

# -*- coding: utf-8 -*-
from openpyxl import Workbook
wb = Workbook()
ws1 = wb.create_sheet("Mysheet")           #创建一个sheet
ws1["A1"]=123.11
ws1["B2"]="测试用例"
#行号和列号必须从1开始，获取指定单元格的内容
d = ws1.cell(row=1, column=1, value=10)
#指定行列坐标组成的字符串，value属性就是单元格的值
print (ws1["A1"].value)
print (ws1["B2"].value)
print (d.value)
print (ws1.cell(row=4,column=2).value)

# 保存文件
wb.save("a.xlsx")

# -*- coding: utf-8 -*-
from openpyxl import Workbook, load_workbook
wb = Workbook()
ws1 = wb.create_sheet("Mysheet")           #创建一个sheet

ws1["A1"]=1
ws1["A2"]=2
ws1["A3"]=3
##ws1["A10"]=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.max_row,ws1.max_column)

#最小有效行和列
print(ws1.min_row,ws1.min_column)

print(ws1.columns)
print(ws1.rows)


#操作单列
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 ("*"*50)
for row in ws1.iter_rows(min_row=1, min_col=1, 
max_col=3, max_row=3):
    for cell in row:
        print (cell.value)

#获取所有行
print (ws1.rows)
for row in ws1.rows:
    print (row)

print ("*"*50)
#获取所有列
print (ws1.columns)
for col in ws1.columns:
    print (col)

# 保存文件
wb.save("a.xlsx")

 

# -*- coding: utf-8 -*-

from openpyxl import Workbook
from openpyxl import load_workbook
wb = load_workbook('a.xlsx')
wb.guess_types = True#将单元格的百分数显示为小数
#wb.guess_types = False#将单元格的百分数显示为百分数
ws=wb.active
ws["D1"]="12%"
print (ws["D1"].value)

# 保存
wb.save("a.xlsx")

#coding=utf-8
from openpyxl import Workbook
from openpyxl import load_workbook
#加载一个已经存在的excel文件
wb = load_workbook('a.xlsx')
ws=wb.active
rows=[]
for row in ws.iter_rows():
            rows.append(row)

print (rows)                 #所有行
print (rows[0])              #获取第一行
print (rows[0][0])            #获取第一行第一列的单元格对象
print (rows[0][0].value)          #获取第一行第一列的单元格对象的值


print (rows[len(rows)-1])             #获取最后行
print (rows[len(rows)-1][len(rows[0])-1])       #获取第后一行和最后一列的单元格对象
print (rows[len(rows)-1][len(rows[0])-1].value)  #获取第后一行和最后一列的单元格对象的值

 

#coding=utf-8
from openpyxl import load_workbook
wb = load_workbook('sample.xlsx')
ws=wb.active
cols=[]
cols = []
for col in ws.iter_cols():
    cols.append(col)

print (cols)               #所有列
print (cols[0])                #获取第一列
print (cols[0][0])              #获取第一列的第一行的单元格对象
print (cols[0][0].value)         #获取第一列的第一行的值

print ("*"*30)
print (cols[len(cols)-1])            #获取最后一列
print (cols[len(cols)-1][len(cols[0])-1])      #获取最后一列的最后一行的单元格对象
print (cols[len(cols)-1][len(cols[0])-1].value)    #获取最后一列的最后一行的单元格对象的值

# -*- coding: utf-8 -*-
from openpyxl import Workbook
from openpyxl import load_workbook
import datetime
wb = load_workbook('a.xlsx')

ws=wb.active
wb.guess_types = True

ws["A1"]=datetime.datetime(2010, 7, 21)
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("a.xlsx")

说明：如果是常规，显示general,如果是数字，显示'0.00_ '，如果是百分数显示0%

 

from openpyxl import Workbook
from openpyxl import load_workbook

wb = load_workbook('a.xlsx')

ws1=wb.active

ws1["A1"]=1
ws1["A2"]=2
ws1["A3"]=3

ws1["A4"] = "=SUM(1, 1)"
ws1["A5"] = "=SUM(A1:A3)"

print (ws1["A4"].value)  #打印的是公式，不是公式计算后的值,程序无法取到计算后的值
print (ws1["A5"].value)  #打印的是公式，不是公式计算后的值,程序无法取到计算后的值

# 保存文件
wb.save("a.xlsx")

# -*- coding: utf-8 -*-
from openpyxl import Workbook
from openpyxl import load_workbook


wb = Workbook()
ws = wb.active

ws.merge_cells('A2:D2')
ws.unmerge_cells('A2:D2')#取消合并

# 或者
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("a.xlsx")

 

# -*- coding: utf-8 -*-
from openpyxl import load_workbook
from openpyxl.drawing.image import Image

wb = load_workbook('a.xlsx')
ws1=wb.active
img = Image(r'F:\important icon_xx.png')
ws1.add_image(img, 'A1')

# 保存文件
wb.save("a.xlsx")