python對excel表格的操作

標籤：none   分數   code   launch   5.x   for   from   nat   styles   

# 活頁簿， 工作表，儲存格#workbook ,sheet ,cell# 靈活擦歐總各種對象，進行修改‘# 編輯樣式%cd D:\python全站\office

D:\python全站\office

# pip install openpyxlimport openpyxlwb = openpyxl.load_workbook(‘coop.xlsx‘)  # 載入建立的表格coop.xlsx

wb.get_active_sheet()

c:\users\coop\miniconda3\envs\coop\lib\site-packages\ipykernel_launcher.py:1: DeprecationWarning: Call to deprecated function get_active_sheet (Use the .active property).  """Entry point for launching an IPython kernel.<Worksheet "Sheet2">

sh1 = wb.active

cell1 = sh1[‘A1‘]

print(cell1)cell1.value

<Cell ‘Sheet2‘.A1>‘學生‘

sh1[‘A1‘].value

‘學生‘

sh1[‘A2‘].coordinate

‘A2‘

sh1[‘A2‘] = ‘zhao‘  # another sh1[‘A2‘].value = ‘zhao‘

sh1[‘A2‘].value

‘zhao‘

###########

sh1.title

‘Sheet2‘

sh1.title = ‘成績單‘

sh1.title

‘成績單‘

wb.save(‘coop-1.xlsx‘)  # 另存新檔

##########

# 取sheet1的資料，放入sheet2，成績> 65# 開啟活頁簿%cd D:\python全站\officeimport openpyxlwb = openpyxl.load_workbook(‘coop.xlsx‘)# 開啟sheet1，開啟sheet2#操作sheet1，存入sheet2# 另存新檔新的檔案

D:\python全站\office

# sh1 = wb.get_sheet_by_name(‘Sheet1‘)  # 首寫大寫

sh1 = wb[‘Sheet1‘]  #對Sheet1頁面操作sh2 = wb[‘Sheet2‘]  #對Sheet2頁面操作

for row in sh1.rows:  # 迴圈每一行 sh1.row()    print(row)    print(row[0].value, row[1].value)   # 列印的是元祖

(<Cell ‘Sheet1‘.A1>, <Cell ‘Sheet1‘.B1>)學生 成績(<Cell ‘Sheet1‘.A2>, <Cell ‘Sheet1‘.B2>)coop 60(<Cell ‘Sheet1‘.A3>, <Cell ‘Sheet1‘.B3>)murphy 61(<Cell ‘Sheet1‘.A4>, <Cell ‘Sheet1‘.B4>)lisi 62(<Cell ‘Sheet1‘.A5>, <Cell ‘Sheet1‘.B5>)zhangsan 63(<Cell ‘Sheet1‘.A6>, <Cell ‘Sheet1‘.B6>)lilei 64(<Cell ‘Sheet1‘.A7>, <Cell ‘Sheet1‘.B7>)××× 65(<Cell ‘Sheet1‘.A8>, <Cell ‘Sheet1‘.B8>)hao 66

for rows in sh1.rows:    if rows[0].coordinate != ‘A1‘:   #元祖用法        #rows[0].coordinate去座標，不等於A1        print(rows[0].value, rows[1].value)

coop 60murphy 61lisi 62zhangsan 63lilei 64××× 65hao 66

for rows in sh1.rows:    if rows[0].coordinate != ‘A1‘ and rows[1].value >63:         #rows[0].coordinate去座標，不等於A1        print(rows[0].value, rows[1].value)

lilei 64××× 65hao 66

index = 2for rows in sh1.rows:    if rows[0].coordinate != ‘A1‘ and rows[1].value >63:         #rows[0].coordinate去座標，不等於A1        print(rows[0].value, rows[1].value)        sh2[‘A‘ + str(index)] = rows[0].value        sh2[‘B‘ + str(index)] = rows[1].value        print(‘in sh2:‘, sh2[‘A‘+str(index)].value,sh2[‘B‘+ str(index)].value)        index += 1wb.save(‘coop-2.xlsx‘)

lilei 64in sh2: lilei 64××× 65in sh2: ××× 65hao 66in sh2: hao 66

# 第二種寫法，根據範圍取值#A2 B2# A3, B3# index = 2sh1 = wb[‘Sheet1‘]sh2 = wb[‘Sheet3‘]for rows in range(2, sh1.max_row +1):    grade = sh1.cell(row = rows, column = 2).value#     print(grade)#     print(type(grade))    if grade > 63:        sh2[‘A‘ + str(rows)] = sh1.cell(row = rows, column = 1).value        sh2[‘B‘ + str(rows)] = grade        print(‘in sh2:‘, sh2[‘A‘+str(index)].value,sh2[‘B‘+ str(index)].value)wb.save(‘coop-3.xlsx‘)

in sh2: None Nonein sh2: None Nonein sh2: None None

print(sh1.max_row)

8

# 最後一行添加平均分數sh1.cell(row=9, column=2).value = ‘=average(B2:B8)‘sh1.cell(row=9, column=1).value = ‘平均分‘print(sh1[‘B10‘].value)wb.save(‘coop-4.xlsx‘)

None

print(sh1[‘B9‘].value)

=average(B2:B8)

from openpyxl.styles import Font# Font?font = Font(bold =True, size = 20) # name, size, bold, italic...sh1[‘B9‘].font = fontwb.save(‘coop-5.xlsx‘)

python對excel表格的操作