Python操作Excel （openpyxl）

读取excel数据

import  openpyxl

import faker
f = faker.Faker(locale='zh_CN')
book = openpyxl.Workbook()

#读

book = openpyxl.load_workbook("number.xls")
sheet = book.active#获取默认的sheet页

sheet = book["number.xls"]

sheet.max_row#获取excel多少行
sheet.max_column#获取excel多少列

for row in sheet.rows:#获取所有行
     row = [r.value for r in row]
     print(row)
print(sheet.cell(1, 1).value)#获取指定单元格内容

for col in sheet.columns:#获取所有列
    col = [r.value for r in col]
    print(col)

for v in sheet.values:   #获取所有行
    print(v)

print(list(sheet.values))#获取所有行

print(list(sheet.title))#获取所有表头

写入Excel

import  openpyxl

import faker
f = faker.Faker(locale='zh_CN')
#写
book = openpyxl.Workbook()
# sheet1 = book.create_sheet()#新建sheet页
sheet= book.active#获取默认sheet页
# sheet.append([1,1,12])
sheet.cell(1,1,"编号")#指定写入第几行第几列
sheet.cell(1,2,"身份证")