from openpyxl import Workbook
from openpyxl import load_workbook
# from openpyxl.writer.excel import ExcelWriter

'''
拷贝 sheet
'''
# 读取 output.xlsx
wb = load_workbook(u"output.xlsx")
# 获取当前的 sheet
source = wb.active
# 拷贝 sheet
target = wb.copy_worksheet(source)
# 给 sheet 重命名
target.title = 'copy'
# 保存
wb.save(u"output.xlsx")


'''
修改值
'''
wb = load_workbook(u"output.xlsx")
# 获取所有 sheet 名称
sheetnames =wb.sheetnames
# 获取第一个 sheet
sheet = wb[sheetnames[0]]
# A 列求和，并赋值到 D1
sheet["D1"] = "=SUM(A:A)"
# 打印 A2 的值
print(sheet['A2'].value)
# 打印位于第6行，第3列的值
print(sheet.cell(row=6,column=3).value)
# 赋值给 A1
sheet['A1'] = '47'
# 保存
wb.save("update.xlsx")


'''
创建 excel
'''
# 创建一个 excel
wb = Workbook()
# 获取当前的 sheet
sheet = wb.active
# 赋值 A1 为 4
sheet['A1'] = 4
# 保存
wb.save("create.xlsx")


'''
插入空行
'''
# 读取 output.xlsx
wb = load_workbook('output.xlsx')
sheet = wb.copy_worksheet(wb.active)
sheet.title = 'copy'
# 在第二行(idx)上方插入两个(amount)空行
sheet.insert_rows(idx=2, amount=2)
# 保存
wb.save('insert.xlsx')