Python 使用Excel自动化

from openpyxl import Workbook
from openpyxl import load_workbook
import random

# 创建excel表格
filename = "1.xlsx"
wb = Workbook()
# 默认创建一个名字为sheet 的sheet
ws = wb.active
print("默认创建的sheet名:", ws.title)
# 修改sheet的名称
ws.title = 'Sheet1'
# 修改下面tab的颜色，颜色表示是RGB形式
ws.sheet_properties.tabColor = 'FF0000'
# 创建新的sheet，第一个参数sheet名，
# 第二个参数 可以控制其插入位置
ws1 = wb.create_sheet('MySheet1')  # 位置默认最后
ws2 = wb.create_sheet('MySheet2', 0)  # 表示第一张表之前插入
ws3 = wb.create_sheet('MySheet3', -1)  # 表示倒数第一张表前

# 遍历所有的sheet
for sheetname in wb.sheetnames:
    print("遍历sheet:", sheetname)

# 选中的sheet
ws3 = wb["MySheet3"]
print("选中sheet name：", ws3.title)
# 修改active sheet 每次打开excel时候，默认的表格
wb.active = wb["MySheet3"]
ws3 = wb.active
print("当前active sheet:", ws3.title)

# cell单元格层面
# 单元格赋值 这里的ws3是得是一张sheet而不是 多张sheet的集合 sheetnames
ws3["A1"] = 1  # EXCEL里面的第一列第一列A1
# 获取单元格
cell = ws3["A1"]
print("A1的值", cell.value)

# 获取一块区域的所有单元格
cell_range = ws3['A1':'C5']  # 获取从A1到C5所有的单元格
for row in cell_range:  # 第一层是第一行
    for cell in row:  # 在第一行里获取第一个单元格
        print("行{0}列{1}值: {2}".format(cell.row, cell.column, cell.value))
        cell.value = random.randint(1, 100)

# 遍历行 先遍历行 参数 从第一行到第二行，从第二列到第三列
for row in ws3.iter_rows(min_row=1, min_col=2, max_row=2, max_col=3):
    for cell in row:
        print("行{0}列{1}值: {2}".format(cell.row, cell.column, cell.value))

# 遍历列  先遍历列
for col in ws3.iter_cols(min_row=1, min_col=2, max_row=2, max_col=3):
    for col in row:
        print("行{0}列{1}值: {2}".format(cell.row, cell.column, cell.value))

# 只想遍历值
for row in ws3.values:
    print("值遍历：", row)
#
for col in ws3.iter_cols(min_row=1, min_col=2, max_row=2, max_col=3, values_only=True):
    for col in row:
        print("行{0}列{1}值: {2}".format(cell.row, cell.column, cell.value))
# 保存
wb.save(filename)

# 加载已经存在的excel
wb = load_workbook(filename)
ws = wb.active
for row in ws.values:
    print("已经存在的excel的值", row)

wb = load_workbook(filename)
ws = wb.create_sheet("Exercise")
cell_range = ws['A1':'D6']
for row in cell_range:
    for cell in row:
        cell.value = random.randint(1, 100)

maxValue = 0
minValue = 101
sum = 0
for row in ws.iter_rows(min_row=1, max_row=6, min_col=1, max_col=4, values_only=True):
    for val in row:
        if val > maxValue:
            maxValue = val
        if val < minValue:
            minValue = val
        sum += val

ws['D7'] = maxValue
ws['D8'] = minValue
ws['D9'] = sum

wb.save(filename)

案例练习

湖人队的常规赛和季前赛的数据

原来nba这页sheet的数据

更新为19-20赛季的数据

步骤：
1.删除所有季前赛的数据行
2.在第一行插入19-20常规赛数据
[“19-20”,“常规赛”,113.4,48,88.3,34.9,31.6,72.9,24.3,45.7,25.4,8.6,6.6,20.7]
3.把所有的数据往下移一行，第一行的内容是
[“赛季”,"",“得分”,“命中%”,“出手”,“三分%”,“三分出手”,“罚球%”,“罚球出手”,“篮板”,“助攻”,“抢断”,“盖帽”,“犯规”]
4.合并A1和B1

删除数据

deletRows = []  # 用于存储删除数据
# 删除时注意，从后往前删除，避免影响行数变化
for cell in ws['B']:  # 遍历B列 得到 要删除行的索引号
    if cell.value == '季前赛':
        deletRows.append(cell.row)  # 通过cell.row取出行号，添加到deleteRows列表中
# 从倒数第一行开始，到-1结束（不包括-1是到0结束，即前闭后开）， step:每次遍历 的时候减去1
for index in range(len(deletRows) - 1, -1, -1):  # 遍历deleteRow列表中每一个元素（倒序）
    ws.delete_rows(deletRows[index])  # 跟去deleRows中的存储的cell.row删除行

添加数据

ws = wb['nba']  # 创建nba  的 sheet
datas = ["19-20", "常规赛", 113.4, 48, 88.3, 34.9, 31.6, 72.9, 24.3, 45.7, 25.4, 8.6, 6.6, 20.7]
titles = ["赛季", "", "得分", "命中%", "出手", "三分%", "三分出手", "罚球%", "罚球出手", "篮板", "助攻", "抢断", "盖帽", "犯规"]

ws.indert_rows(1)  # 在第一行插入
index = 0
for row in ws.iter_rows(min_row=1, max_row=1, min_col=1, max_col=1):  # 遍历第一行
    for cell in row:  # 行中每一个单元格
        cell.value = datas[index]  # 单元值插入data列表的值，按照索引号来
        index += 1
ws.move_range("A1:N4", rows=1)

添加title

# ws.indert_rows(1)  # 在第一行插入
index = 0
for row in ws.iter_rows(min_row=1, max_row=1, min_col=1, max_col=14):  # 遍历第一行
    for cell in row:  # 行中每一个单元格
        cell.value = datas[index]  # 单元值插入data列表的值，按照索引号来
        index += 1
ws.move_range("A1:N4", rows=1)

合并单元格

# 合并单元格
ws.merge_cells("A1:B1")
wb.save(filename)

总结

(2)获取整行和整列的值(数组)

table.row_values(i)

table.col_values(i)

时间格式处理
data_value = xlrd.xldate_as_tuple(sheet.cell_value(1,2) ,file.datemode) # xldate_as_tuple()方法得到日期数据年月日时分秒的值并返回为元组
datatime2 = date(*data_value[:3]).strftime(’%Y/%m/%d’) # 截取元组中的前三位，即年月日的值传给data，并进行时间格式化

(1990, 1, 10, 0, 0, 0)
1990/01/10

详细参考链接：
https://www.cnblogs.com/tdp0108/p/14239763.html