1.安装
pip install openpyxl
2.使用方法
2.1 给单元格和行赋值
from openpyxl importWorkbook
wb=Workbook()#获取第一个活动的sheet,默认创建excel会有一个sheet
ws =wb.active#单元格直接赋值
ws['A1'] = 42
#按行赋值
ws.append([1,2,3])importdatetime
ws['A2'] =datetime.datetime.now()
wb.save("1.xlsx")
2.2 创建sheet
from openpyxl importWorkbook
wb=Workbook()#在末尾插入
ws1 = wb.create_sheet("Mysheet")
ws1.title= "New Title"
#在开始插入
ws2 = wb.create_sheet("Mysheet", 0)
ws2.title="你好"
#在倒数第二个位置插入
ws3= wb.create_sheet("Mysheet", -1)
ws3.title="third"ws1.sheet_properties.tabColor= "1072BA"
for sheet inwb:print(sheet.title)
wb.save("create_sheet.xlsx")
2.3 操作单元格
from openpyxl importWorkbook
wb=Workbook()
ws1=wb.create_sheet("Mysheet")
ws1["A1"]=124.45ws1["B2"]="您好"
#为第四行第二列也就是B4赋值10
d=ws1.cell(row=4, column=2, value=10)
wb.save("cell.xlsx")
2.4 批量操作单元格
from openpyxl importWorkbook
wb=Workbook()
ws=wb.activefor row in ws.iter_rows(min_row=1, max_col=3, max_row=2):for cell inrow:
cell.value="test"
print(cell)for col in ws.iter_rows(min_row=3, max_col=3, max_row=4):for cell incol:
cell.value="hello"
print(cell)
ws['c9']='hello world'tuple(ws.rows)
wb.save("batchcell.xlsx")
执行完之后，会在A1,B1,C1,A2,B2,C2赋值test，在A3,B3,C3,A4,B4,C4赋值hello
2.5 操作已经存在的Excel
#-*- coding: utf-8 -*-
from openpyxl importWorkbookfrom openpyxl importload_workbook
wb= load_workbook('sample.xlsx')
wb.guess_types= True #猜测格式类型
ws=wb.active
ws["D1"]="12%"
#Save the file
wb.save("sample.xlsx")
2.6 使用公式
from openpyxl importWorkbookfrom openpyxl importload_workbook
wb= load_workbook("sample.xlsx")
ws1=wb.active
ws1["A1"] = 1ws1["A2"] = 2ws1["A3"] = 3ws1["A4"] = "=SUM(1,1)"ws1["A5"] = "=SUM(A1:A3)"wb.save("sample.xlsx")
2.7 合并单元格
from openpyxl importWorkbookfrom openpyxl importload_workbook
wb= load_workbook("sample.xlsx")
ws1=wb.active
ws1.merge_cells("A2:D2")#ws1.unmerge_cells("A2:D2")
ws1.merge_cells(start_row=2,start_column=1,end_row=2,end_column=4)#ws1.unmerge_cells(start_row=2,start_column=1,end_row=2,end_column=4)
wb.save("sample.xlsx")
2.8 插入图片
from openpyxl importload_workbookfrom openpyxl.drawing.image importImage
wb= load_workbook("sample.xlsx")
ws1=wb.active
img= Image("jielun.png")
ws1.add_image(img,'A1')
wb.save("sample.xlsx")
2.9 隐藏单元格
from openpyxl importload_workbookfrom openpyxl.drawing.image importImage
wb= load_workbook("sample.xlsx")
ws1=wb.active
ws1.column_dimensions.group('A', 'D', hidden=True)
wb.save("sample.xlsx")
2.10 画柱状图
from openpyxl importload_workbookfrom openpyxl importWorkbookfrom openpyxl.chart importBarChart,Reference,Series
wb=Workbook()
ws=wb.activefor i in range(10):
ws.append([i])
values= Reference(ws, min_col=1, min_row=1, max_col=1, max_row=10)
chart=BarChart()
chart.add_data(values)
ws.add_chart(chart,"E15")
wb.save("sample.xlsx")
2.11 画饼状图
#-*- coding: utf-8 -*-
from openpyxl importload_workbookfrom openpyxl importWorkbookfrom openpyxl.chart import(PieChart , ProjectedPieChart, Reference)from openpyxl.chart.series importDataPoint
data=[
['Pie', 'Sold'],
['Apple', 50],
['Cherry', 30],
['Pumpkin', 10],
['Chocolate', 40],
]
wb=Workbook()
ws=wb.activefor row indata:
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)
pie.set_categories(labels)
pie.title= "Pies sold by category"
#Cut the first slice out of the pie
slice = DataPoint(idx=0, explosion=20)
pie.series[0].data_points=[slice]
ws.add_chart(pie,"D1")
ws= wb.create_sheet(title="Projection")
data=[
['Page', 'Views'],
['Search', 95],
['Products', 4],
['Offers', 0.5],
['Sales', 0.5],
]for row indata:
ws.append(row)
projected_pie=ProjectedPieChart()
projected_pie.type= "pie"projected_pie.splitType= "val" #split by value
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
data= Reference(ws, min_col=2, min_row=1, max_row=5)
projected_pie.add_data(data, titles_from_data=True)
projected_pie.set_categories(labels)
ws.add_chart(projected_pie,"A10")from copy importdeepcopy
projected_bar=deepcopy(projected_pie)
projected_bar.type= "bar"projected_bar.splitType= 'pos' #split by position
ws.add_chart(projected_bar,"A27")#Save the file
wb.save("sample.xlsx")
2.12 表格
from openpyxl.worksheet.table importTable, 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],
]#add column headings. NB. these must be strings
ws.append(["Fruit", "", "", "", ""])for row indata:
ws.append(row)
tab= Table(displayName="Table1", ref="A1:E5")#Add a default style with striped rows and banded columns
style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=True,
showLastColumn=True, showRowStripes=True, showColumnStripes=True)#第一列是否和样式第一行颜色一行，第二列是否···#是否隔行换色，是否隔列换色
tab.tableStyleInfo =style
ws.add_table(tab)#Save the file
wb.save("sample.xlsx")
2.13 字体
#-*- coding: utf-8 -*-
from openpyxl importWorkbookfrom openpyxl.styles importFont
wb=Workbook()
ws=wb.active
ws["A1"] = "测试"ws["A2"] = "字体"col= ws.column_dimensions['A']
col.font= Font(bold=True) #将A列设定为粗体
row = ws.row_dimensions[1]
row.font= Font(underline="single") #将第一行设定为下划线格式
#Save the file
wb.save("sample.xlsx")
3.使用爬虫爬取贝壳网二手房的信息并存入到excel
importsysfrom os importpathimporttimeimporturllib3importrequestsimportnumpy as npfrom bs4 importBeautifulSoupfrom urllib importparsefrom openpyxl importWorkbookfrom openpyxl importload_workbook
headers=[{'User-Agent':'Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.1.6) Gecko/1201 Firefox/3.5.6'},\
{'User-Agent':'Mozilla/5.0 (Windows NT 6.2) AppleWebKit/535.11 (KHTML, like Gecko) Chrome/17.0.963.12 Safari/535.11'},\
{'User-Agent': 'Mozilla/5.0 (compatible; MSIE 10.0; Windows NT 6.2; Trident/6.0)'}]defget_house():
page_num=0;
total_num=0;
wb=Workbook()
ws= wb.create_sheet("housesheet")
ws.append(["title","tall","year","house","area","direction", "tag","totalPrice", "unitPrice"])while(1):
page_num+=1url= "/ershoufang/pg"+str(page_num)+"/"
print(url)
http=urllib3.PoolManager()
time.sleep(np.random.rand()*5)try:
r= http.request("GET", url, headers=headers[page_num%len(headers)])
plain_text=r.data.decode()print(plain_text)exceptException as e:print(e)continuesoup= BeautifulSoup(plain_text, features="lxml")
ligroup= soup.find_all("li", class_="clear")for item inligroup:try:
title= item.find("div", class_="title").a.get("title")
content= item.find("div", class_="houseInfo").get_text()
contents= content.strip().split("|")
tall= contents[0].strip().replace("\n","")
year= contents[1].strip()
house= contents[2].strip().replace("\n", "")
area= contents[3].strip().replace("\n", "")
direction= contents[4].strip()
tag= item.find("div", class_="tag").find("span").get_text()
totalPrice= item.find("div", class_="totalPrice").get_text()
unitPrice= item.find("div", class_="unitPrice").get_text().strip().replace("\n", "")
ws.append([title, tall,year,house,area,direction,tag,totalPrice,unitPrice])exceptException as e:print(e)continue
if page_num > 20:breakwb.save("hosuebeike.xlsx")if __name__=='__main__':
get_house()
跑完之后，在当前目录下会有一个excel文件，打开看一下，部分截图如下：
