pip3 install openpyxl

from openpyxl import Workbook

#创建Workbook，并默认会创建一个空表,名称为：Sheet

wb = Workbook()

#获取默认的sheet

ws1 = wb.active

#设置Sheet名称

ws1.title = 'Sheet1'

#写入单个单元格

ws1['A1'] = '标题列1'

ws1['B1'] = '标题列2'

#写入多个单元格(从有数据的行的下一行写入)

ws1.append(['张三', 80])

ws1.append(['李四', 90])

#创建一个新sheet，可以指定名称

ws2 = wb.create_sheet('Sheet2')

#复制Sheet1，新sheet名称为Sheet1 Copy

ws3 = wb.copy_worksheet(wb['Sheet1'])

#打印所有表名

print(wb.sheetnames)

#保存

wb.save('1.xlsx')

from openpyxl import Workbook

from openpyxl import load_workbook

wb = load_workbook('1.xlsx')

#猜测格式类型

wb.guess_types = True

ws1 = wb.active

print('总行数', ws1.max_row)

print('总列数', ws1.max_column)

print('---获取单个单元格的值---')

print(ws1['A1'].value)

print(ws1[1][0].value) #这里也是A1值,行索引从1、列索引从0算起

print('---获取单列的所有值---')

for cell in ws1['A']:

    print(cell.value)

print('---获取多列的值(通过切片)---')

#如果范围比实际大，如实际只有AB两列，指定A:B，则获取结果返回None，并且C列会被后面.columns和.rows获取到

for column in ws1['A:B']:

    for cell in column:

        print(cell.value)

print('---获取所有列的值---')

for column in ws1.columns: #也可用ws1.iter_cols()

    for cell in column:

        print(cell.value)

print('---获取某行的值---')

for cell in ws1[1]:

    print(cell.value)

print('---获取多行的值(通过切片)---')

for row in ws1[1:2]:

    for cell in row:

        print(cell.value)

print('---获取所有行的值---')

for row in ws1.rows: #也可用ws1.iter_rows()

    for cell in row:

        print(cell.value)