Python处理Excel 文件小案例

1、新建表格

from openpyxl import Workbook 
# 实例化
wb = Workbook()
# 激活 worksheet
ws = wb.active
# 保存表格
wb.save("C:/TEMP/test1.xlsx")

2、读取已有表格

from openpyxl import load_workbook

wb2 = load_workbook("C:/TEMP/test2.xlsx")

print(wb2.sheetnames)

3、创建工作簿

from openpyxl import Workbook
wb = Workbook()
ws = wb.active

# 新建的工作簿插到末尾
ws1 = wb.create_sheet("Myshee1") 
print(wb.sheetnames)

# 新建的工作簿插到首部
ws2 = wb.create_sheet("Mysheet2", 0) 
print(wb.sheetnames)

# 新建的工作簿插到倒数第二个位置
ws3 = wb.create_sheet("Mysheet3", -1) 
print(wb.sheetnames)

wb.save("C:/TEMP/test3.xlsx")

4、更改工作簿名称

from openpyxl import Workbook

wb = Workbook()

ws = wb.active
print(wb.sheetnames)

ws.title = "New Title"
print(wb.sheetnames)

wb.save("C:/TEMP/test4.xlsx")

5、访问单元格

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws['A4'] = 'A4单元格'
c=ws['A4'].value
print(c)

d=ws.cell(4,2,'第四行第二列') #第4行第二列
print(d.value)
wb.save("C:/TEMP/test5.xlsx")

6、访问多个单元格

from openpyxl import Workbook
import random

wb = Workbook()
ws = wb.active

for i in range(1,40):
    for j in range(1,60):
        ws.cell(i,j,random.randint(0, 60))

# 使用切片访问
range = ws['A1':'D40']

# 使用列访问
colC=ws['C']
col_range=ws['C:D']

#使用行访问
row10=ws[10]
row_range=ws[5:10]

wb.save("C:/TEMP/test6.xlsx")

7、插入行和列

# openpyxl.worksheet.worksheet模块
# insert_cols（idx，amount = 1 ）
# 在col == idx之前插入一列或多列

# insert_rows（idx，amount = 1 ）
# 在row == idx之前插入一行或多行


from openpyxl import Workbook
import random

wb = Workbook()
ws = wb.active

for i in range(1,5):
    for j in range(1,5):
        ws.cell(i,j,random.randint(0, 60))

ws.insert_rows(1) #在第一行前插入1行
ws.insert_cols(3,3) #在第三列前面插入3列

wb.save("C:/TEMP/test7.xlsx")

8、删除行和列

# openpyxl.worksheet.worksheet模块
#delete_cols（idx，amount = 1 ）
#从col == idx删除一列或多列

#delete_rows（idx，amount = 1 ）
#从row == idx删除一行或多行


from openpyxl import Workbook
import random

wb = Workbook()
ws = wb.active

for i in range(1,9):
    for j in range(1,9):
#         ws.cell(i,j,random.randint(0, 60))
        ws.cell(i,j,'{},{}'.format(i,j))

# ws.delete_rows(1) # 删除第一行
ws.delete_cols(3,2) #删除第三列和第四列

wb.save("C:/TEMP/test8.xlsx")

9、合并单元格

from openpyxl import load_workbook

wb = load_workbook("C:/TEMP/test2.xlsx")
ws = wb.active
ws.merge_cells('B2:B5')
ws.merge_cells('C2:C5')
ws.merge_cells('D2:D5')

wb.save("C:/TEMP/test9.xlsx")

10、单元格格式

from openpyxl import load_workbook
from openpyxl.styles import Alignment

wb = load_workbook("C:/TEMP/test2.xlsx")
ws = wb.active
ws.merge_cells('a2:a18') #合并单元格

cell = ws.cell(row=2, column=1) #注意，在openpyxl的逻辑里面，编号是从1开始的，而非0；
cell.value = '医院名称'        # 对于合并的单元格而言，能被赋值等操作的，只有合并前最左上角的单元格
cell.alignment = Alignment(horizontal='center', vertical='center')

wb.save("C:/TEMP/test10.xlsx")

11、调整列宽

from openpyxl import load_workbook

wb = load_workbook("C:/TEMP/test2.xlsx")
ws = wb.active

ws.column_dimensions['E'].width=30
ws.column_dimensions['F'].width=50

wb.save("C:/TEMP/test11.xlsx")

12、设置自动换行

from openpyxl import load_workbook
from openpyxl.styles import Alignment

wb = load_workbook("C:/TEMP/test2.xlsx")
ws = wb.active

ws.column_dimensions['E'].width=30
ws.column_dimensions['F'].width=50
ws.column_dimensions['G'].width=36

ws.cell(row=1, column=1, value='调整自动换行').alignment = Alignment(horizontal='center',vertical='center',wrapText=True)

align = Alignment(horizontal='left',vertical='center',wrap_text=True)

for i in range(1,19):
    ws['E{}'.format(i)].alignment = align
    ws['F{}'.format(i)].alignment = align
    ws['G{}'.format(i)].alignment = align


wb.save("C:/TEMP/test12.xlsx")

13、函数

from openpyxl import Workbook
import random

wb = Workbook()
ws = wb.active

for i in range(1,8):
    for j in range(1,3):
        ws.cell(i,j,random.randint(0, 60))

ws['A8']="=SUM(A1:A7)"
ws['B8']="=AVERAGE(B1:B7)"

wb.save("C:/TEMP/test13.xlsx")

14、边框设置

from openpyxl import load_workbook
from openpyxl.styles import Alignment

wb = load_workbook("C:/TEMP/test2.xlsx")
ws = wb.active

ws.column_dimensions['E'].width=30
ws.column_dimensions['F'].width=50
ws.column_dimensions['G'].width=36

ws.cell(row=1, column=1, value='调整自动换行').alignment = Alignment(horizontal='center', vertical='center',wrapText=True)

align = Alignment(horizontal='left',vertical='center',wrap_text=True)

for i in range(1,19):
    ws['E{}'.format(i)].alignment = align
    ws['F{}'.format(i)].alignment = align
    ws['G{}'.format(i)].alignment = align
    
# 边框
from openpyxl.styles import Border,Side

border = Border(left=Side(border_style='thin',color='000000'),

                right=Side(border_style='thin',color='000000'),

                top=Side(border_style='thin',color='000000'),

                bottom=Side(border_style='thin',color='000000')
               )

ws['F3'].border = border



wb.save("C:/TEMP/test14.xlsx")

15、背景设置

from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
wb = load_workbook("C:/TEMP/征像模板01.xlsx")
ws = wb.active

for i in range(1,ws.max_row+1):
    ws["L{}".format(i)].fill = PatternFill("solid", fgColor="1874CD")
    ws["N{}".format(i)].fill = PatternFill("solid", fgColor="FFFF00")
    ws["O{}".format(i)].fill = PatternFill("solid", fgColor="FF7E00")
    
    
#     ws["L{}".format(i)].fill = PatternFill("lightGrid", fgColor="1874CD")
#     ws["N{}".format(i)].fill = PatternFill("lightVertical", fgColor="FFFF00")
#     ws["O{}".format(i)].fill = PatternFill("lightDown", fgColor="FF7E00")
    
wb.save("C:/TEMP/test15.xlsx")