openpyxl

一、创建一个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()

 四、访问单元格

# 访问单元格
cell = sheet['A1']
cell = sheet.cell(row=1,column=1)
# 访问单元格的值
cell_val = sheet['A1'].value
cell_val = sheet.cell(row=1,column = 1).value
# 单元格赋值
sheet['A1'].value = "单元格赋值"
sheet.cell(row=1,column=1).value = "单元格赋值"
# 多个单元格赋值，可以借助for循环
# 赋值后需要保存，否则操作无效

# 访问多个单元格
cell_range = sheet["A1":"C3"]
colA = sheet['A']
col_range = sheet['A:C']
row1 = sheet[1]
row_range = sheet[1:5]
# 上述cell_range等对象都是tuple类型，需要循环取值
for row/col in cell_range:
    for cell in row/col:
        print(cell.value)
# 或者 可以指定行列范围
for row/col in sheet.iter_rows(min_row = 1, max_row = 3, min_col = 2, max_col = 5):
    for cell in row/col:
        print(cell)

五、cell的其它属性

# 设置超链接
sheet.cell(2,2).hyperlink = "www.baidu.com"

# cell 的其它属性
sheet.cell(row = 1,column = 2).column_letter
sheet.cell(row = 1,column = 2).coordinate
sheet.cell(row = 1,column = 2).col_idx
sheet.cell(row = 1,column = 2).encoding
sheet.cell(row = 1,column = 2).offset
sheet.cell(row = 1,column = 2).is_date
sheet.cell(row = 1,column = 2).data_type
sheet.cell(row = 1,column = 2).has_style
sheet.cell(row = 1,column = 2).style
sheet.cell(row = 1,column = 2).style_id
sheet.cell(row = 1,column = 2).font
sheet.cell(row = 1,column = 2).alignment
sheet.cell(row = 1,column = 2).border
sheet.cell(row = 1,column = 2).fill
sheet.cell(row = 1,column = 2).number_format
sheet.cell(row = 1,column = 2).hyperlink