python-閫氳繃openpy鎿嶄綔excel，,鏍囩锛?a hre
python-閫氳繃openpy鎿嶄綔excel，,鏍囩锛?a hre
鏍囩锛?a href='http://www.byrx.net/so/1/orange' title='orange'>orange
1.瀹夎 openpyxl
pip install openpyxl == 2.3.5 瀹夎鎸囧畾鐗堟湰
閬囧埌闂锛?/p>
鏌ヨ缁撴灉锛氳繖鏄洜涓虹數鑴戜笂鏈夊叾浠栬蒋浠朵篃鏈塸ip鍛戒护锛屾垜鐨勭數鑴戜笂鏄洜涓鸿浜唋oadrunner
瑙ｅ喅鍔炴硶锛?/p>
https://stackoverflow.com/questions/7469361/pip-on-windows-giving-the-error-unknown-or-unsupported-command-install/8634923
鎴戠敤鐨勬湁鏁堝懡浠わ細
python -m pip install openpyxl 瀹夎鎴愬姛
2.瀹夎pillow
鐩存帴鐐瑰嚮瀹夎鍗冲彲锛?4浣嶉渶瑕佸拰python鐗堟湰涓€鑷达紱32涔熸槸
3.show openpyxl
c:\Python27>python -m pip show openpyxl
4.鍒涘缓excel鏂囦欢瀵硅薄
>>> from openpyxl import *
>>> wb = Workbook() #鍒涘缓excel鏂囦欢瀵硅薄锛屽啓鍦ㄥ唴瀛橀噷鐨勶紝涓嶄繚瀛樼殑璇濆湪鍏抽棴鍚庡氨娌′簡
>>> ws = wb.active #鑾峰彇excel鏂囦欢鐨勪竴涓猻heet
>>> ws[鈥楢1鈥榏=12 #鍐欏叆鍐呭 鍗曟牸鍐?/p>
>>> ws[鈥楢2鈥榏=12.333
>>> ws[鈥楢3鈥榏=u"灏忎竷"
>>> wb.save("e:\\test1.xlsx") #淇濆瓨鏂囦欢
>>> ws["B1"]=u"鍝ュ摜"
>>> ws["B2"]=u"鎴戞兂鎴戝摜鍝ヤ簡"
>>> wb.save("e:\\test1.xlsx")
>>> import time #鍐欏叆鏃堕棿
>>> ws["c1"]= time.strftime(u"%Y骞?m鏈?d鏃?%H鏃?M鍒?S绉?.encode("utf-8"),time.lo
caltime())
>>> wb.save("e:\\test1.xlsx")
importdatetime
importtime
ws[鈥楢2鈥榏=datetime.datetime.now()
5.鎵撳嵃鏈夋晥琛屽拰鍒楋細
>>> ws.columns
<generator object _cells_by_col at0x0000000002E117E0>
>>> for col in ws.columns:
...print col
...
(<Cell u鈥楽heet鈥?A1>, <Cellu鈥楽heet鈥?A2>, <Cell u鈥楽heet鈥?A3>)
(<Cell u鈥楽heet鈥?B1>, <Cellu鈥楽heet鈥?B2>, <Cell u鈥楽heet鈥?B3>)
(<Cell u鈥楽heet鈥?C1>, <Cellu鈥楽heet鈥?C2>, <Cell u鈥楽heet鈥?C3>)
>>> for col in ws.rows:
...print col
...
(<Cell u鈥楽heet鈥?A1>, <Cellu鈥楽heet鈥?B1>, <Cell u鈥楽heet鈥?C1>)
(<Cell u鈥楽heet鈥?A2>, <Cellu鈥楽heet鈥?B2>, <Cell u鈥楽heet鈥?C2>)
(<Cell u鈥楽heet鈥?A3>, <Cellu鈥楽heet鈥?B3>, <Cell u鈥楽heet鈥?C3>)
浠€涔堟槸鏈夋晥琛屽拰鍒楋紵
鏁版嵁鍐欏叆鐨勫彲鍖呭惈鏁翠綋鏁版嵁鐨勬渶澶ц寖鍥达紝杩欎釜鑼冨洿鍐呯殑閮芥槸鏈夋晥琛屽拰鍒?/p>
6.鍒涘缓sheet
>>> ws = wb.create_sheet("gloryroad")
>>> ws = wb.create_sheet("Ilove")
>>> ws = wb.create_sheet(u"鍝ュ摜")
>>>wb.save("e:\\test.xlsx")
7.鑾峰彇sheet鍚嶇О
>>> wb.get_sheet_names() #鑾峰彇鎵€鏈夊悕绉?/p>
[u鈥楽heet鈥? u鈥榞loryroad鈥? u鈥業 love鈥?u鈥榎u54e5\u54e5鈥榏
>>> ws =wb.get_sheet_by_name(u"鍝ュ摜") #鑾峰彇鎸囧畾鍚嶇О
>>> ws
<Worksheet "\u54e5\u54e5">
>>> printws.encode("gbk")
>>> ws =wb.get_sheet_by_name(wb.get_sheet_names()[-1])#鑾峰彇鎸囧畾浣嶇疆鐨剆heet鍚嶇О
>>> ws
<Worksheet "\u54e5\u54e5">
8.淇敼sheet鍚嶇О
>>> ws =wb.get_sheet_by_name(wb.get_sheet_names()[-1])#淇敼鍓嶉渶瑕佸厛鑾峰彇
>>> ws
<Worksheet "\u54e5\u54e5">
>>> ws.title = "lin"
>>> wb.get_sheet_names()
[u鈥楽heet鈥? u鈥榞loryroad鈥? u鈥業 love鈥? u鈥榣in鈥榏
>>> ws.title
u鈥榣in鈥?/p>
9.鑾峰彇sheet鍚嶇О
>>> ws = wb["I love"]
>>> ws
<Worksheet "I love">
>>> wb.sheetnames
[u鈥楽heet鈥? u鈥榞loryroad鈥? u鈥業 love鈥? u鈥榣in鈥榏
10. 閫氳繃琛屽拰鍒椾慨鏀硅〃鏍煎唴鐨勫唴瀹?/strong>
>>>ws.cell(row=1,column=2,value=123456)
<Cell u鈥業 love鈥?B1>
>>> ws["B1"].value
123456
>>>ws.cell(row=1,column=2,value="I love")
<Cell u鈥業 love鈥?B1>
>>> ws["B1"].value
u鈥業 love鈥?/p>
灏忕粌涔狅細
浠嶢1鍒癉4鍖哄煙鐨勬墍鏈夊崟鍏冩牸閮借鍐欏唴瀹癸紝鍐呭鏄鍙锋槸绗竴浣嶏紝鍒楀彿鏄浜屼綅
>>> for row in range(1,5):
...for col in range(1,5):
...ws.cell(row=row,column=col,value=str(row)+str(col))
...
<Cell u鈥業 love鈥?A1>
<Cell u鈥業 love鈥?B1>
<Cell u鈥業 love鈥?C1>
<Cell u鈥業 love鈥?D1>
<Cell u鈥業 love鈥?A2>
<Cell u鈥業 love鈥?B2>
<Cell u鈥業 love鈥?C2>
<Cell u鈥業 love鈥?D2>
<Cell u鈥業 love鈥?A3>
<Cell u鈥業 love鈥?B3>
<Cell u鈥業 love鈥?C3>
<Cell u鈥業 love鈥?D3>
<Cell u鈥業 love鈥?A4>
<Cell u鈥業 love鈥?B4>
<Cell u鈥業 love鈥?C4>
<Cell u鈥業 love鈥?D4>
>>>wb.save("e:\\test.xlsx")
>>>
11. 鎿嶄綔鏌愬垪涓墍鏈夋湁鏁堟暟鎹?/strong>
>>> print ws["A"]
(<Cell u鈥業 love鈥?A1>, <Cell u鈥業love鈥?A2>, <Cell u鈥業 love鈥?A3>, <Cell u鈥業 love鈥?
A4>)
12. 鎿嶄綔鏌愪袱鍒椾箣闂寸殑鎵€鏈夋湁鏁堝€?/strong>
>>> print ws["A:D"]
((<Cell u鈥業 love鈥?A1>, <Cell u鈥業love鈥?A2>, <Cell u鈥業 love鈥?A3>, <Cell u鈥業 love鈥?/p>
.A4>), (<Cell u鈥業 love鈥?B1>,<Cell u鈥業 love鈥?B2>, <Cell u鈥業 love鈥?B3>, <Cell u鈥業
love鈥?B4>), (<Cell u鈥業 love鈥?C1>,<Cell u鈥業 love鈥?C2>, <Cell u鈥業 love鈥?C3>, <Ce
ll u鈥業 love鈥?C4>), (<Cell u鈥業love鈥?D1>, <Cell u鈥業 love鈥?D2>, <Cell u鈥業 love鈥?D3
>, <Cell u鈥業 love鈥?D4>))
13. 鎵撳嵃鍑鸿幏鍙栧埌鐨勬寚瀹氫綅缃殑鍊?/strong>
>>> printws["A:D"][0][0].value
11
>>>ws["A:D"][0][0].value=鈥?2鈥?nbsp; 绗竴鍒楃殑绗竴琛岋細鍒楀湪鍓嶏紝琛屽湪鍚?/p>
14. 鍙栧嚭鍒楃殑鍊硷紱鍙栧嚭琛屽拰鍒楃殑鍊?/p>
>>> ws[1] 鍙栧嚭1琛?/p>
(<Cell u鈥業 love鈥?A1>, <Cell u鈥業love鈥?B1>, <Cell u鈥業 love鈥?C1>, <Cell u鈥業 love鈥?
D1>)
>>> ws[1:2] 鍙栧嚭1琛?鍒楃殑鍊?/p>
((<Cell u鈥業 love鈥?A1>, <Cell u鈥業love鈥?B1>, <Cell u鈥業 love鈥?C1>, <Cell u鈥業 love鈥?/p>
.D1>), (<Cell u鈥業 love鈥?A2>,<Cell u鈥業 love鈥?B2>, <Cell u鈥業 love鈥?C2>, <Cell u鈥業
love鈥?D2>))
灏忕粌涔狅細鍙栧嚭1鍒?琛岀殑鍐呭
鑷繁鐨勫仛娉曪紝鍙栦簡3鍒?/p>
>>> for i in range(1,3):
...print ws[i:3]
...
((<Cell u鈥業 love鈥?A1>, <Cell u鈥業love鈥?B1>, <Cell u鈥業 love鈥?C1>, <Cell u鈥業 love鈥?/p>
.D1>), (<Cell u鈥業 love鈥?A2>,<Cell u鈥業 love鈥?B2>, <Cell u鈥業 love鈥?C2>, <Cell u鈥業
love鈥?D2>), (<Cell u鈥業 love鈥?A3>,<Cell u鈥業 love鈥?B3>, <Cell u鈥業 love鈥?C3>, <Ce
ll u鈥業 love鈥?D3>))
((<Cell u鈥業 love鈥?A2>, <Cell u鈥業love鈥?B2>, <Cell u鈥業 love鈥?C2>, <Cell u鈥業 love鈥?/p>
.D2>), (<Cell u鈥業 love鈥?A3>,<Cell u鈥業 love鈥?B3>, <Cell u鈥業 love鈥?C3>, <Cell u鈥業
love鈥?D3>))
鑰佸笀鐨勬柟娉曪細
>>>forrowinws[1:3]:
...forjinrange(len(row)): 鍒╃敤姣忚鍏冪礌鐨勪釜鏁版潵纭畾鏈夊灏戝垪
...printrow[j].value
鍚屽鐨勬柟娉曪細
forrowsinws[1:3]:
forrowinrows:
printrow.value,
print
15. 鎸囧畾涓€涓寖鍥达紝閫氳繃闄愬埗鏈€澶ц鍙峰拰鍒楀彿锛屾渶灏忚鍙峰拰鍒楀彿鏉ュ疄鐜?/strong>
>>> for row in ws.iter_rows(min_row=1,max_col=3,max_row=3):
...for cell in row:
...print cell.value
...
22
12
13
21
22
23
31
32
33
>>> for row in ws.iter_rows(min_row=1,min_col=1,max_col=3,max_row=3):
...for cell in row:
...print cell.value
...
16. 鎵撳嵃鎵€鏈夌殑琛屽拰鍒?/strong>
>>> for row in ws.rows: 鎵撳嵃鎵€鏈夌殑琛?/p>
...print row
...
>>> for col in ws.columns: 鎵撳嵃鎵€鏈夌殑鍒?/p>
...print col
...
17. 鍐欏叆鐧惧垎鏁?/strong>
>>> ws["Z100"]="66%"
>>> printws["Z100"].value
66%
>>> wb.guess_type = True 涓篢rue鏃秂xcel閲岄潰灏辨槸甯歌绫诲瀷锛屼负False鏃舵槸鐧惧垎鏁帮紵
>>> ws["Z101"].value
>>>wb.save("e:\\test.xlsx")
18. 淇敼excel閲岄潰鐨勫€?/strong>
>>> wb =load_workbook("e:\\test.xlsx")#璇诲彇涓€涓幇鏈夌殑鏂囦欢杩涜鎿嶄綔
>>> ws = wb.active #鑾峰彇褰撳墠sheet
>>> ws[鈥楢1鈥榏.vlue
>>> ws[鈥楢1鈥榏.value
12L
>>> ws[鈥楢1鈥榏.value=12 #淇敼鍊?/p>
>>> ws[鈥楢1鈥榏.value
12
>>> ws[鈥楢2鈥榏.value
12.333
>>>ws[鈥楢2鈥榏.value=u"gege" #淇敼鍊?/p>
>>> ws[鈥楢2鈥榏.value
u鈥榞ege鈥?/p>
19. 鍒ゆ柇excel鍐呭瓨鍌ㄧ殑鏁板€兼牸寮忕被鍨?/strong>
>>> ws[鈥楢3鈥榏.value ="12%"
>>> ws[鈥楢3鈥榏.number_format
鈥楪eneral鈥?/p>
>>> ws[鈥楢20鈥榏.number_format
鈥楪eneral鈥?/p>
>>> wb.guess_type = True
>>> ws[鈥楢10鈥榏="12%"
>>> ws[鈥楢10鈥榏.number_format
鈥楪eneral鈥?/p>
>>> ws[鈥楢10鈥榏="12%"
>>>wb.save("e:\\test.xslx")
>>> ws[鈥楢10鈥榏.number_format
鈥楪eneral鈥?/p>
>>> import datetime
>>>ws["A11"]=datetime.datetime(1017,1,1)
>>> ws[鈥楢11鈥榏.number_format
鈥榶yyy-mm-dd h:mm:ss鈥?/p>
>>>
20. 鍐欏叆涓€涓猻um鍑芥暟
>>>ws["A11"]="=sum(1,1)"
>>>printws["A11"]
<Cellu鈥楽heet鈥?A11>
>>>printws["A11"].value
=sum(1,1)
>>>wb.save("e:\\sample.xlsx")
21. 鍚堝苟鍗曞厓鏍煎拰鍙栨秷鍚堝苟
ws.merge_cells("A1:C3")
ws.umerge_cells("A1:C3")
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)
22. 鎻掑叆鍥剧墖
from openpyxl import load_workbook
from openpyxl.drawing.image import Image
wb = load_workbook(鈥榚:\\test.xlsx鈥?
ws1=wb.active
img = Image(鈥榚:\\1.png鈥?
ws1.add_image(img, 鈥楢1鈥?
# Save the file
wb.save("e:\\test.xlsx")
E:\>python a.py
Traceback (most recent call last):
File "a.py", line 13,in <module>
wb.save("e:\\test.xlsx")
File "C:\Python27\lib\site-packages\openpyxl\workbook\workbook.py",line 349,
in save
save_workbook(self, filename)
File"C:\Python27\lib\site-packages\openpyxl\writer\excel.py", line 267,in sa
ve_workbook
archive = ZipFile(filename,鈥榳鈥? ZIP_DEFLATED, allowZip64=True)
File"C:\Python27\lib\zipfile.py", line 756, in __init__
self.fp = open(file,modeDict[mode])
IOError: [Errno 13] Permission denied: 鈥榚:\\test.xlsx鈥?/p>
Excel娌℃湁鍏抽棴锛屾墍浠ユ姤閿欙紒锛侊紒
E:\>python a.py
23. 闅愯棌鍒?/strong>
ws1.column_dimensions.group(鈥楢鈥? 鈥楧鈥?hidden=True) 闅愯棌鍒?/p>
24. 鐢熸垚鏌卞舰鍥?/strong>
from openpyxl import load_workbook
from openpyxl import Workbook
from openpyxl.chart import BarChart,Reference, Series
wb = load_workbook(鈥榚:\\test.xlsx鈥?
ws1=wb.active
wb = Workbook()
ws = wb.active
for i in range(10): #鐢熸垚鏁版嵁
ws.append([i])
values = Reference(ws, min_col=1,min_row=1, max_col=1, max_row=10) #鏁版嵁鑼冨洿
chart = BarChart() #鐢熸垚鏌辩姸鍥惧璞?/p>
chart.add_data(values) #鏌辩姸鍥惧璞＄敤values瀛樺偍鏁版嵁
ws.add_chart(chart, "E15")
# Save the file
wb.save("e:\\test.xlsx")
25. 鐢熸垚鍗曞厓鏍硷紝鏈夋牱寮?/strong>
# -*- coding: utf-8 -*-
from openpyxl import load_workbook
from openpyxl import Workbook
from openpyxl.worksheet.table import Table,TableStyleInfo
wb = Workbook()
ws = wb.active
data = [
[鈥楢pples鈥? 10000, 5000, 8000, 6000],
[鈥楶ears鈥? 2000, 3000, 4000,5000],
[鈥楤ananas鈥? 6000, 6000, 6500, 6000],
[鈥極ranges鈥? 500, 300,200, 700],
]
# add column headings. NB. these must bestrings
ws.append(["Fruit","2011", "2012", "2013", "2014"])
for row in data:
ws.append(row)
tab = Table(displayName="Table1",ref="A1:E5") #table鎸囩殑鏄浣跨敤鏍峰紡鐨勫尯鍩?/p>
# Add a default style with striped rows andbanded 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("e:\\test.xlsx")
26. 璁剧疆鍗曞厓鏍间腑鐨勫瓧浣?/strong>
# -*- coding: utf-8 -*-
from openpyxl import Workbook
from openpyxl.styles import colors
from openpyxl.styles import Font
wb = Workbook()
ws = wb.active
a1 = ws[鈥楢1鈥榏
d4 = ws[鈥楧4鈥榏
ft = Font(color=colors.RED) # color="FFBB00"锛岄鑹茬紪鐮佷篃鍙互璁惧畾棰滆壊
a1.font = ft
d4.font = ft
# If you want to change the color of aFont, you need to reassign it::
a1.font = Font(color=colors.RED,italic=True) # the change only affects A1
a1.value = "abc"
# Save the file
wb.save("e:\\test.xlsx")
16杩涘埗鐨勯鑹诧細
鍙互鍦ㄧ綉涓婃煡
27. 璁剧疆瀛椾綋鍜屽ぇ鍐?/strong>
# -*- coding: utf-8 -*-
from openpyxl import Workbook
from openpyxl.styles import colors
from openpyxl.styles import Font
wb = Workbook()
ws = wb.active
a1 = ws[鈥楢1鈥榏
d4 = ws[鈥楧4鈥榏
ft = Font(color="FFBB00") # color="FFBB00"锛岄鑹茬紪鐮佷篃鍙互璁惧畾棰滆壊
a1.font = ft
d4.font = ft
# If you want to change the color of aFont, you need to reassign it::
a1.font = Font(name=u鈥樺畫浣撯€?size=28,color=colors.RED, italic=True) # the change only
affects A1
a1.value = "abc"
# Save the file
wb.save("e:\\test.xlsx")
28. 璁剧疆涓虹矖浣?/strong>
# -*- coding: utf-8 -*-
from openpyxl import Workbook
from openpyxl.styles import colors
from openpyxl.styles import Font
wb = Workbook()
ws = wb.active
a1 = ws[鈥楢1鈥榏
d4 = ws[鈥楧4鈥榏
ft = Font(color="FFBB00") # color="FFBB00"锛岄鑹茬紪鐮佷篃鍙互璁惧畾棰滆壊
a1.font = ft
d4.font = ft
# If you want to change the color of aFont, you need to reassign it::
a1.font = Font(name=u鈥樺畫浣撯€?size=28,bold=True,color=colors.RED,italic=True) # the
change only affects A1
a1.value = "abc"
# Save the file
wb.save("e:\\test.xlsx")
29. 璁剧疆鎴愭牱寮忔ā鏉垮啀鍘荤粰鍗曞厓鏍煎簲鐢紝浣嗘槸涓嶆敮鎸佸涓悓鏃惰缃紝闇€瑕佺殑璇濆彲浠ラ€氳繃寰幆
# -*- coding: utf-8 -*-
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=鈥榯hick鈥? color="000000") #杈规棰滆壊鍙婄矖缁?/p>
highlight.border = Border(left=bd, top=bd,right=bd, bottom=bd) #杈规涓婁笅宸﹀彸
print dir(ws["A1"])
ws["A1"].style =highlight #璁剧疆鍗曞厓鏍兼牱寮?/p>
# Save the file
wb.save("e:\\test.xlsx")
30. 甯哥敤鐨勬牱寮忓拰灞炴€ц缃?/strong>
#-*-coding:utf-8-*-
fromopenpyxlimportWorkbook
fromopenpyxl.stylesimportFont
fromopenpyxl.stylesimportNamedStyle,Font,Border,Side,PatternFill
fromopenpyxl.stylesimportPatternFill,Border,Side,Alignment,Protection,Font
wb=Workbook()
ws=wb.active
ft=Font(name=u鈥樺井杞泤榛戔€?
size=11,
bold=False,
italic=False,
vertAlign=None,
underline=鈥榥one鈥?
strike=False,
color=鈥楩F000000鈥?
fill=PatternFill(fill_type="solid",
start_color=鈥楩FEEFFFF鈥?
end_color=鈥楩F001100鈥?
#杈规鍙互閫夋嫨鐨勫€间负锛氣€榟air鈥?鈥榤edium鈥?鈥榙ashDot鈥?鈥榙otted鈥?鈥榤ediumDashDot鈥?鈥榙ashed鈥?鈥榤ediumDashed鈥?鈥榤ediumDashDotDot鈥?鈥榙ashDotDot鈥?鈥榮lantDashDot鈥?鈥榙ouble鈥?鈥榯hick鈥?鈥榯hin鈥榏
#diagonal琛ㄧず瀵硅绾?br>bd=Border(left=Side(border_style="thin",
color=鈥楩F001000鈥?,
right=Side(border_style="thin",
color=鈥楩F110000鈥?,
top=Side(border_style="thin",
color=鈥楩F110000鈥?,
bottom=Side(border_style="thin",
color=鈥楩F110000鈥?,
diagonal=Side(border_style=None,
color=鈥楩F000000鈥?,
diagonal_direction=0,
outline=Side(border_style=None,
color=鈥楩F000000鈥?,
vertical=Side(border_style=None,
color=鈥楩F000000鈥?,
horizontal=Side(border_style=None,
color=鈥楩F110000鈥?
)
alignment=Alignment(horizontal=鈥榞eneral鈥?
vertical=鈥榖ottom鈥?
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
number_format=鈥楪eneral鈥?br>
protection=Protection(locked=True,
hidden=False)
ws["B5"].font=ft
ws["B5"].fill=fill
ws["B5"].border=bd
ws["B5"].alignment=alignment
ws["B5"].number_format=number_format
ws["B5"].value="gloryroad"
#Savethefile
wb.save("e:\\sample.xlsx")
python-閫氳繃openpy鎿嶄綔excel
鏍囩锛?a href='http://www.byrx.net/so/1/orange' title='orange'>orange
鍘熸枃鍦板潃锛歨ttps://www.cnblogs.com/qingqing-919/p/8337865.html
评论关闭