一、创建一个EXCEL

from openpyxl import Workbook

# 实例化
wb = Workbook() # create an excel file in RAM

# 获取当前active的sheet
print(wb.active)

sheet1 = wb.active 

print(sheet1.title)

sheet1.title = "创建一个sheet 名"
print(sheet1.title)

wb.save("用openpyxl创建的excel.xlsx")

 

二、写入数据

# 写数据
sheet1["B9"]='black girl'
sheet1["c9"]='171,48,90' # 赋值给指定单元格
sheet1.append(['rachel','190','89']) # 从下放空白处，最左开始追加行
sheet1["A3"] = datetime.datetime.now().strftime("%Y-%m-%d") # 输入时间类型
wb.save("用openpyxl创建的excel.xlsx")

三、打开已有文件

# 打开已有文件
from openpyxl import Workbook,load_workbook
wb = load_workbook(r"E:\Yzh\4月 中烟\4.23 中烟\源数据\4.14-21 下午三点 二维码.xlsx")

# 打印工作簿中的 表
print(wb.sheetnames)
print(wb.get_sheet_names())

#根据名称获取活动工作表
sheet = wb.get_sheet_by_name("sheet")

# 打印cell值
print(sheet["B5"])
print(sheet['B5'].value)

# 切片取cell 值
for i in sheet["B5:B7"]:
    print(i[0].value)

# 循环遍历所有cell 并打印值,按行遍历
for row in sheet:
    for cell in row:
        print(cell.value,end=",")
    print()

# 遍历指定行
for row in sheet.iter_rows(min_row=4,max_row=10,max_col=4):
    for cell in row:
        print(cell.value,end = ",")
    print()

# 按列遍历多有cell
for col in sheet.columns:
    for cell in col:
        print(cell.value,end=",")
    print()

# 遍历指定列
for col in sheet.iter_cols(min_col=3,max_col=5,max_row=10):
    for cell in col:
        print(cell.value,end=",")
    print()