python操作EXCEL的相关笔记

python操作EXCEL（.xlsx）的相关笔记

下面代码是python 操作Excel (.xlsx) 的常用功能

---

#encoding = utf-8

# 一、安装
'''
pip install openpyxl==2.5.4
想要在文件中插入图片文件，需要安装pillow
'''

# 二，创建一个excel文件，并写入不同内容，创建的文件不保存只是在内存，看不见。

from  openpyxl import Workbook
import locale
import time
import datetime
from  openpyxl import load_workbook
import os


wb = Workbook()   #创建一个workbook对象,w大写
ws = wb.active    #获取默认sheet
print('默认sheet的名字为：',ws.title)   #获取sheet名字

ws['C5'] = '这是一个sheet'     #在C5单元格写入内容
ws.append([1,2,3,4,5])        #在有效区域后边（C5）写入多个单元格
wb.save('e:\\b.xlsx')       #保存到硬盘

locale.setlocale(locale.LC_CTYPE,'chinese')  #设置中文时间格式
now = time.time()        #获取当前时间戳
arraryTime = time.localtime(now)   #获取时间元组
currentTime = time.strftime('%Y年%m月%d日 %H时%M分%S秒',arraryTime)  #自定义时间格式
print(currentTime)
nowtime = datetime.datetime.now()  #获取当前时间

ws['B2'] = now
ws['C2'] = currentTime
ws['B3'] = nowtime
wb.save('e:\\b.xlsx')

# 三，创建sheet

ws1 = wb.create_sheet('newsheet')  #创建一个新的sheet
ws2 = wb.create_sheet('newsheet2',1)  #在指定位置插入一个sheet
ws1.title = '新sheet'                   #设置sheet 名字
wb.save('e:\\b.xlsx')                 #保存到硬盘

ws1.sheet_properties.tabColor = 'FFF00F'  #设置sheet标签的背景颜色
print('all sheet name: ',wb.sheetnames)    #获取所有sheet 名字
print('通过sheet名下标获取sheet: ',wb[wb.sheetnames[2]])
wslist = wb.worksheets   #获取全部sheet 对象

for i in wslist:
    print(i.title)      #遍历sheet 对象获取sheet 名字
print('通过sheet名字获取sheet ',wb['新sheet'])     #根据sheet 名字获取sheet对象
print('通过sheet对象下标获取sheet ',wslist[0])         #根据sheet 对象的下标获取sheet 对象

target = wb.copy_worksheet(ws)   #copy sheet所有内容
target.title = 'copy'
del wb['copy']        #根据sheet 名删除sheet
wb.save('e:\\b.xlsx')

# 四，操作单元格

print(ws['B2'].value)     #根据定位读单元格值
print(ws.cell(row=3,column=2).value)    #根据坐标读取单元格值，行号和列号都是从1开始
for col in list(ws.columns)[2]:    #操作单列第3列，列和行都是从1开始
    print(col.value)

for row in list(ws.rows)[1]:  #操作单行第2行
    print(row.value)

for col in list(ws.columns)[0:2]:    #操作多列第1,2列，列和行都是从1开始
    for cell in col:
        print(cell.value)

for row in list(ws.rows)[1:4]:  #操作多行第2，3,4行
    for cell in row:
        print(cell.value)

for i in range(1,5):       #通过坐标获取区域内的单元格值,列和行都是从1开始
    for j in range(1,6):
        cell = ws.cell(row=i,column=j)
        print(cell,cell.value)

for row in ws.iter_rows(min_row=1,min_col=1,max_row=3,max_col=5): #按行和列取范围内值
    for cell in row:
        print(cell,cell.value)

for cols in ws.iter_cols(min_row=1,min_col=1,max_row=3,max_col=5): #按列和行取范围内值
    for cell in cols:
        print(cell,cell.value)
#获取所有的行对象
rows=[]   #储存行对象
for row in ws.iter_rows():
    rows.append(row)
print(rows[-1])     #获取最后一行，= rows[len(rows)-1]
print(rows[len(rows)-1][len(rows[0])-1].value)  #获取最后一行最后一个单元格对象的值

#获取所有的列对象
cols = []     #存储列对象
for col in ws.iter_cols():
    cols.append(col)
print(cols[-1])      #获取最后一列 = cols[len(cols)-1]
print(cols[len(cols)-1][len(cols[0])-1].value)  #获取最后一列最后一个单元格对象值

#五，对已存在的文件进行操作     from  openpyxl import load_workbook

path = 'e:\\b.xlsx'
if not os.path.exists(path):   #判断路径是否存在，不存在创建一个
    os.mkdir(path)

wb = load_workbook(path)         #打开文件
ws = wb['新sheet']               #根据sheet 名字获取sheet 对象
wb.guess_types = True            #猜测格式类型

ws["A1"]=datetime.datetime(2010, 7, 21)
print (ws["A1"].number_format)    #获取单元格类型
ws["A5"] = "=SUM(A1:A3)"         #使用公式计算

ws.merge_cells(start_row=2,start_column=1,end_row=2,end_column=4)   #合并单元格
ws.merge_cells('A6:D9')        #合并单元格
ws.unmerge_cells('A6:D9')      #解除合并

ws.insert_cols(1)              #在指定下标处新增一列
ws.insert_rows(2)              #在指定下标处新增一行
ws.column_dimensions.group('A', 'D', hidden=True)  #隐藏单元格
wb.save(path)

#六，插入图片     pip install pillow

from openpyxl.drawing.image import Image

img = Image('e:\\1.jpg')        #获取图片地址
ws.add_image(img,'H5')    #插入图片
wb.save(path)

#七，插入柱状图和饼图

from openpyxl.chart import BarChart,Reference,series
from openpyxl.chart import PieChart,ProjectedPieChart,Reference
from openpyxl.chart.series import DataPoint


wb = Workbook()
ws = wb.active
for i in range(10):
    ws.append([i])

#设定了柱状图的数据区域，从第一列的第一行到第10行
values = Reference(ws, min_col=1, min_row=1, max_col=1, max_row=10)
chart = BarChart()#生成一个柱状图对象
chart.title = "Bar Chart"#给柱状图起个名字
chart.y_axis.title = 'SN' #横坐标名字
chart.x_axis.title = 'Sample value' #纵坐标名字
chart.add_data(values) #把数据关联到柱状图对象中
ws.add_chart(chart, "E15") #定义一下柱状图显示的位置E15

wb.save("e:\\b.xlsx")

#饼图数据源
data = [
    ['Pie', 'Sold'],
    ['Apple', 50],
    ['Cherry', 30],
    ['Pumpkin', 10],
    ['Chocolate', 40],
]
wb = Workbook()
ws = wb.active
for row in data:
    ws.append(row)

pie = PieChart()   #生成了饼图对象
labels = Reference(ws, min_col=1, min_row=2, max_row=5) #设定了一下标签的数据区域
data = Reference(ws, min_col=2, min_row=1, max_row=5)  #设定的数据的区域
pie.add_data(data, titles_from_data=True)  #加载数据到饼图的对象，名字从数据上来，设定为True

pie.set_categories(labels)               #添加了饼图的标签
pie.title = "Pies sold by category"      #饼图的名字
slice = DataPoint(idx=3, explosion=40)   #把哪一块切出来，突出20单位显示
pie.series[0].data_points = [slice]      #把这个区域添加到饼图中
ws.add_chart(pie, "D5")                  #把饼图显示在D1区域

wb.save("e:\\c.xlsx")

#投射饼图
projected_pie = ProjectedPieChart()  #生成一个投射饼图的对象
projected_pie.type = "pie"  #设定类型，还是饼图
projected_pie.splitType = "val" #且饼图的时候类型用的是值,value值做饼图的切分

labels = Reference(ws, min_col=1, min_row=2, max_row=5) #设定了饼图的标签区域范围，第一列的第二行到第5行
data = Reference(ws, min_col=2, min_row=1, max_row=5) #设定了饼图的数据区域范围，第一列的第1行到第5行
projected_pie.add_data(data, titles_from_data=True) #把数据区域添加到投射饼图中

projected_pie.set_categories(labels) #设定好投射饼图的标签
ws.add_chart(projected_pie, "A10") #设定一下投射饼图的位置
from copy import deepcopy

projected_bar = deepcopy(projected_pie) #深拷贝一个投射饼图
projected_bar.type = "bar" #类型设定为bar,柱状图
projected_bar.splitType ='pos'#基于位置做切割
ws.add_chart(projected_bar, "A27") #设定显示的位置

wb.save("e:\\e.xlsx")

#八，设定一个表格区域，并设定表格的格式

from openpyxl.worksheet.table import Table,TableStyleInfo

wb = Workbook()
ws = wb.active
data = [
    ['Apples', 10000, 5000, 8000, 6000],
    ['Pears',   2000, 3000, 4000, 5000],
    ['Bananas', 6000, 6000, 6500, 6000],
    ['Oranges',  500,  300,  200,  700],
]

ws.append(['Fruit','2011','2012','2013','2014'])  #添加一个表头
for row in data:
    ws.append(row)
tab = Table(displayName="Table1", ref="A1:E5") #设定了表格范围，显示的名字Table1

'''
#name：样式的名字
#showFirstColumn是否第一列颜色加深
#showLastColumn:是否最后一列颜色加深
#showRowStripes是否显示间隔行效果
#showColumnStripes：是否显示间隔列的效果
'''
style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False,
                       showLastColumn=False, showRowStripes=True, showColumnStripes=True)
tab.tableStyleInfo = style  #把样式和表格进行绑定
ws.add_table(tab)           #把带有样式的表格对象添加到sheet中
wb.save("e:\\f.xlsx")

#九，给单元格设置背景颜色和字体的大小，颜色

from openpyxl.styles import  Font,NamedStyle,PatternFill,Border,Side,Alignment
from copy import copy

wb = Workbook()
ws = wb.active

ws['A1'] = 'WFWERFF'
ft = Font(name="楷体",  #字体类型
color='FFF00F',         #字体颜色
size=50,                #字体大小
bold=True,              #是否为粗体
underline="single",     #下划线类型,single 单线类型
italic=False,           #是否斜体
vertAlign=None,         #是否垂直对齐
strike=False,           #中位线
)  #字体对象实例化 color = 'FFF000'

ws['A1'].font = ft           #设定单元格字体样式
for i in  range(1,5):
    for j in range(1,6):
        ws.cell(row=i,column=j).font = ft   #给某个区域单元格设定字体样式
wb.save("e:\\K.xlsx")

ws1 =wb.create_sheet('newsheet')

ft2 = copy(ft)  #拷贝字体样式
col = ws1.column_dimensions['F']  #选中某列
row = ws1.row_dimensions['11']      #选中某行
col.font = ft2                   #把某列设为特定样式
row.font = ft2                     #把某行设定为特定样式
col.width = 10                     #列宽
wb.save("e:\\g.xlsx")


wb = Workbook()
ws = wb.active
all_style = NamedStyle(name='hight')  #设置了单元格的样式名称，生成了一个样式对象
all_style.fort = Font(size=14)                  #字体样式
all_style.fill = PatternFill(fill_type='solid',fgColor='FF00FF')   #背景样式

'''
边框可以选择的值为：'hair', 'medium', 'dashDot', 'dotted', 'mediumDashDot', 'dashed', 
'mediumDashed', 'mediumDashDotDot', 'dashDotDot', 'slantDashDot', 'double', 'thick', 
'thin']    diagonal 表示对角线  outline:轮廓,vertical 垂直，horizontal:水平
'''
bd = Side(style='thick',color='00ff00')  #生成了边框线对象，thick：粗边框，颜色
all_style.border = Border(top=bd,left=bd,bottom=bd,right=bd,
diagonal_direction = 0,outline = bd,vertical = bd,horizontal = bd,) #单元格上下左右边框线
ws['A1'].style = all_style

#对齐方式：horizontal：水平对齐，
#vertical：bottom底部对齐
#text_rotation:字体旋转
#wrap_text：绕图片的文字
alignment=Alignment(horizontal='general',vertical='bottom',text_rotation=0,
                     wrap_text=False,shrink_to_fit=False,indent=0)
protection = Protection(locked=True,hidden=False)   #保护
wb.save("e:\\K.xlsx")

总结

代码里面的有些功能在工作当中如果需要，可直接修改相关的字段使用。