使用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)