python办公自动化笔记(三)
python操作 openpyxl
即用openpyxl读取和写入数据
引用语法
from openpyxl import Workbook,load_workbook
创建工作薄
w1=Workbook()#创建工作簿
ws=w1.active #取第一个活动工作簿
通过工作簿创建工作表以及复制工作表
w1.create_sheet("s1") #创建一个工作表s1
ws=w1.create_sheet("s3",0) #插在第一个工作表的前面
ws.title="s0" #改工具表的名字
ws.sheet_properties.tabColor="1072BA"#设置工作表标签颜色为蓝色
wb.sheetnames #查看所有工作表
wb.save("E://x3.xlsx")#保存到该路径
sw=w1.active
sw1=w1.copy_worksheet(sw)#w1工作薄中复制了第一个工作表。
**#工作表中的图片或者图表不可复制,只读或者只写模式下也不可复制**
具体效果图:
获取单元格值
ws["A1"]#访问w1的A1值
ws["A1"]="name"#直接赋值为name
ws.cell(row=15,column=1,value=5)#第15行,第一列输入5
w1.save("E://x3.xlsx")
ws["A1":"E1"]#多单元格赋值
#iter_rows读取,在只读模式下不可读取
ws.iter_rows(min_row=1,max_row=2,max_col=3)#通过iter_rows方法获取值。按行读取
ws.iter_cols(min_row=1,max_row=2,max_col=3)#读取方式不同,按列读取
for i in ws.values:
for r in i:
print(r)
for j in ws.iter_cols(min_row=1,max_row=2,max_col=3,values_only=True):
print(j)#values_only=True,只返回单元格的值
日期格式以及插入公式
#日期格式
import datetime
ws['A2']=datetime.datetime(2020,2,19)
print(ws['A2'].value)
ws['A2'].number_format="yyyy/mm/dd h:mm:ss"#日期格式转化
print(ws['A2'].value)
#插入公式
ws['A3']="=SUM(2,2)"#对2和2求值
具体效果图
插入图片
from openpyxl.drawing.image import Image
#ws['A4']="See i"
img=Image("E://f2.jpg")#定位到图片路径
ws.add_image(img,'A1')#锚点,即左上方的点在excel中A1单元格上
w1.save("E://xx.xlsx")
合并单元格以及折叠
ws.merge_cells('A1:C1')#合并单元格(A1-C1)
#合并后为一格ws['A1'],若写ws['A1':'C1']会报错
ws.merge_cells(start_row=1,end_row=1,start_column=1,end_column=3)
#合并单元格(A1-C1) start_row开始行,end_row结束行,start_column开始列,end_column结束列。
ws['A1']=1
w1.save("E://xx.xlsx")
ws.unmerge_cells('A1:C1')#取消合并单元格
插入与删除行列
liv=[['s','s2'],['s','s1']]
for i in liv:
ws.append(i)
ws.insert_cols(1)#在第一列之前插入一列
ws.insert_rows(1)#在第一行之前插入一行
ws.delete_cols(1)#删除第一列
ws.delete_rows(1)#删除第一行
ws.move_range("A1:F10",rows=1,cols=3)#A1-F10范围的内容移动一行三列
w1.save("E://xx4.xlsx")
折叠
#使用折叠
ws.column_dimensions.group("A","D",hidden=True)#使用列折叠,把A-D列折叠起来
ws.row_dimensions.group(1,4,hidden=True)#使用行折叠,把1-4行折叠起来
具体效果图
插入数据
person=[('Number','Batch1','Batch2'),(2,10,30),(3,40,60),(4,50,70),(5,20,10),(6,10,40),(7,50,30)]
for i in person:
ws.append(i)
绘制图表
1、条形图
#竖状条形图
from openpyxl.chart import BarChart,Series,Reference
rows=[('Number','Batch1','Batch2'),(2,10,30),(3,40,60),(4,50,70),(5,20,10),(6,10,40),(7,50,30)]
for row in rows:
ws_1.append(row)
chart1=BarChart()#条形图实例化
chart1.type='col'#设置类型为竖状条形图
chart1.style=14#设置图形样式为14
chart1.title="Bar Chart"#设置图表名称
chart1.x_axis.title="Text number"#设置x轴坐标标题
chart1.y_axis.title="Sample Length(mm)"#设置y轴坐标标题
data=Reference(ws_1,min_col=2,max_col=3,min_row=1,max_row=7)#数据选取范围
cat=Reference(ws_1,min_col=1,min_row=2,max_row=7)#种类选取范围
chart1.add_data(data,titles_from_data=True)#将数据添加为作图数据
chart1.set_categories(cat)#作为x轴
chart1.shape=3
ws_1.add_chart(chart1,"A10")#在A10处加入chart1
#横状条形图
#深复制chart1数据
from copy import deepcopy#引入deepcoy库来复制数据
chart2=deepcopy(chart1)#复制chart1的图数据
chart2.type='bar'#设置类型为横状条形图
chart2.style=11#设置chart2的样式
chart2.title="HBar Chart"#设置title
ws_1.add_chart(chart2,"G10")#在G10处加入图chart2
w1.save("E://xx5.xlsx")
具体效果图:
2、折线图
from datetime import date
from openpyxl.chart import LineChart
#线形图
rows=[['Date','Batch1','Batch2','Batch3'],[date(2015,9,1),40,30,25],[date(2015,9,2),40,25,30],[date(2015,9,3),50,30,45],[date(2015,9,4),35,25,40],[date(2015,9,5),25,35,30],[date(2015,9,6),20,40,35]]
for row in rows:
sw1.append(row)
chart3=LineChart()#折线图实例化
chart3.x_axis.title="Text number"#设置x轴标题
chart3.y_axis.title="Size"#设置y轴标题
chart3.title="Line Chart"#设置chart3的图表标题
chart3.style=13#设置样式为13
data3=Reference(sw1,min_row=1,max_row=7,min_col=2,max_col=4)#设置数据选取。选取sw1中数据
chart3.add_data(data3,titles_from_data=True)#将数据添加为作图数据
s1=chart3.series[0]#设置第一条线的数据
#chart3.series为有几种数据列
s1.marker.symbol="triangle"#设置点的形状
s1.marker.graphicalProperties.solidFill="FF0000"#设置实体填充色(红色)
s1.marker.graphicalProperties.line.solidFill="FF0000"#设置边框色(红色)
s1.graphicalProperties.line.noFill=True#无连接线
s2=chart3.series[1]#设置第二条线的数据
s2.graphicalProperties.line.solidFill="00AAAA"#设置实体填充色(蓝色)
s2.graphicalProperties.line.dashStyle="sysDot"#线的样式为虚线,并且为点
s2.graphicalProperties.line.width=100050#设置宽度为(单位为EMUS)
s3=chart3.series[2]#设置第三条线的数据
s3.smooth=True #设置为光滑曲线
sw1.add_chart(chart3,"A10")
w1.save("E://line2.xlsx")
具体效果图
3、饼图
#加数据语法
data4=[['Page','views'],['Search',95],['Products',4],['Offers',0.5],['Sales',0.5]]
for row in data4:
ws_1.append(row)
from openpyxl.chart import PieChart,Reference
from openpyxl.chart.series import DataPoint
pie=PieChart()#读取饼图实例
labels=Reference(ws_1,min_col=1,min_row=2,max_row=5)#设置标志
data_4=Reference(ws_1,min_col=2,min_row=1,max_row=5)#设置作图数据
pie.add_data(data_4,titles_from_data=True)
pie.set_categories(labels)
pie.title="Pie chart"
slice=DataPoint(idx=0,explosion=20)
pie.series[0].data_point=[slice]#重点突出数据为Slice
ws_1.add_chart(pie,"D10")
w1.save("E://sss.xlsx")
具体效果图: