使用openpyxl对Excel进行操作

1.使用openpyxl库

pip install openpyxl

2.创建workbook对象，写入内容并存储

#coding:utf-8
from openpyxl import Workbook
from datetime import datetime
#创建wb对象
wb = Workbook()
#激活
ws = wb.active
ws['A1'] = 'openpyxl'
ws['A2'] = 90
ws['B1'] = datetime.now()
#在下一行追加
ws.append(["smith","ted","jessica"])
#存储
wb.save("D:/ab/demo.xlsx")

3.设置sheet的标签颜色

wb.sheet_properties.tabcolor = "1072BA" 　

4.在第0位置，新建sheet

ws2 = wb.create_sheet("NewSheet", 1)　

5.获取所有的sheet名字

names = wb.sheetnames

6.根据sheet名称获取sheet对象

sheet = wb.get_sheet_by_name("Sheet")

7.加载xlsx文件

#coding:utf-8
from openpyxl import load_workbook
wb = load_workbook("D:/ab/demo.xlsx")
ws = wb.get_sheet_by_name(wb.sheetnames[0])

8.单元格读写

#读
ws['A1'].value
ws.cell(row=1, column=1).value
#写
ws['B1'] = 78

9.设置样式

#coding:utf-8
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment
wb = load_workbook("D:/ab/demo.xlsx")
ws = wb.get_sheet_by_name(wb.sheetnames[0])
#设置字体样式,样式一旦创建就不能更改，除非重新定义样式
font = Font(name='华文琥珀',size=15,bold=True,italic=False,vertAlign=None,underline='none',strike=False,color='FF000000')
fill = PatternFill(fill_type=None,start_color='FFFFFFFF',end_color='FF000000')
alignment = Alignment(horizontal='general',vertical='bottom',text_rotation=0,wrap_text=False,shrink_to_fit=False,indent=0)
ws['A1'].font = font
ws['A1'].fill = fill
ws['A1'].alignment = alignment
wb.save("D:/ab/demo.xlsx")

10.获取全部row，cloumn

total_row = ws.max_row
total_column = ws.max_column　

 11.获取所有row，转为list，显示所有cell内容。column同理

rows = ws.iter_rows()
all_rows = list(rows)
for row in all_rows:
    for cell in row:
        print(cell.value)

 

转载于:https://www.cnblogs.com/SneakingSled/p/9628142.html