python openpyxl自动化操作excel(xlsx)

代码：

#-*- coding: utf-8 -*-
import os,openpyxl
from openpyxl.styles import Border, Side,PatternFill

if __name__ == '__main__':
    #bug如果标题行里面有\r,会被转换成了_x000D_
    #有一个安全过滤的库，叫什么来着？    
    #作成一个新的空的Excel
    wb = openpyxl.Workbook()
    # 保存
    wb.save('example.xlsx')
    #打开
    wb = openpyxl.load_workbook('example.xlsx')
    #当前打开的sheet页 wb.active
    ws = wb.active
    # 更改默认名称Sheet`  
    ws.title = "WorkSheetTitle"
        # 定义第二个sheet页
    ws2 = wb.create_sheet("NewWorkSheet2")

    # 定义第三个sheet页
    # `0` 的设定 会将该sheet页 置于wb最前面
    ws3 = wb.create_sheet("NewWorkSheet3", 0)
    ws.title = "WorkSheetTitle"

    ws = wb._sheets[0]
    ws.title = "AA"
    # WS的tab颜色设定
    ws.sheet_properties.tabColor = "FF0000"
    
    # 给单元格赋值
    ws["A1"] = "HOGE"
    ws["B1"] = "FUGA"
    
    # 指定行列给单元格赋值
    ws.cell(row=4, column=2, value=10)
    # 指定行列给单元格赋值(9X9的单元格)
    v = 0
    for i in range(1,10):
        for n in range(1,10):
            ws.cell(row=i, column=n, value=v)
            v += 1
    # column名和値顺序(追加)放入单元格中
    column_title = ["FirstName", "LastName"]
    rows = [
        column_title,
        ["Tarou", "Tanaka"],
        ["Tarou", "Suzuki"],
        ["Tarou", "Uchiayama"],
    ]
    for row in rows:
        ws.append(row)
    # 单元格内换行
    ws['A1'] = "A\nB\nC"
    ws['A1'].alignment = openpyxl.styles.Alignment(wrapText=True)
    # 设置font
    font = openpyxl.styles.Font(
        name = "宋体",
        size = 15,
    )
    a1 = ws["A1"]
    a1.font = font
    a1.value = "TEST"
    # 设置单元格border的style
    border = Border(
        left=Side(
            border_style="thin",
            color="FF0000"
        ),
        right=Side(
            border_style="thin",
            color="FF0000"
        ),
        top=Side(
            border_style="thick",
            color="FF0000"
        ),
        bottom=Side(
            border_style="thin",
            color="FF0000"

        )
    )
    b2 = ws["B2"]
    b2.border = border
    b2.value = "TEST"
    # 合并单元格
    ws.merge_cells("A1:E1")
    ws["A1"] = "HOGE"
    
    # 单元格填充颜色
    fill = PatternFill(fill_type='solid',
                       fgColor='FFFF0000')
    b2 = ws["B2"]
    b2.fill = fill
    b2.value = "TEST"
    
    # 设置超链接 到“example”sheet页 鼠标定格在A5单元格
    ws["A1"] = "Link"
    ws["A1"].hyperlink = "example.xlsx#example!A5"
    
    #读Excel文档
    for row in ws:
        for cell in row:
            print(cell)

    
    # 保存
    wb.save('example.xlsx')
    os.system('example.xlsx')

拆分单元格

import openpyxl

workbook = openpyxl.load_workbook(path)  #加载已经存在的excel
# workbook = openpyxl.Workbook(path)
name_list = workbook.sheetnames
# worksheet = workbook.get_sheet_by_name(name_list[0])  #最新版本已经不能使用这种方法
worksheet = workbook[name_list[0]]


m_list = worksheet.merged_cells  #合并单元格的位置信息，可迭代对象（单个是一个'openpyxl.worksheet.cell_range.CellRange'对象），print后就是excel坐标信息
cr = []
for m_area in m_list:
    # 合并单元格的起始行坐标、终止行坐标。。。。，
    r1, r2, c1, c2 = m_area.min_row, m_area.max_row, m_area.min_col, m_area.max_col
    # 纵向合并单元格的位置信息提取出
    if r2 - r1 > 0:
        cr.append((r1, r2, c1, c2))
        print('符合条件%s' % str(m_area))

# 这里注意需要把合并单元格的信息提取出再拆分
for r in cr:
    worksheet.unmerge_cells(start_row=r[0], end_row=r[1],
                            start_column=r[2], end_column=r[3])

workbook.save(path)

python（openpyxl）复制excel数据到另一个excel

#-*- coding: utf-8 -*-
from openpyxl import Workbook
from openpyxl import load_workbook
import openpyxl

file1 = r"F:\统计表.xlsx"
#新建excel
wb2=openpyxl.Workbook()
wb2.save('test.xlsx')
print('新建成功')

#读取数据
wb1=openpyxl.load_workbook(file1)
wb2=openpyxl.load_workbook('test.xlsx')
sheet1=wb1._sheets[0]#获取sheet页
sheet2=wb2._sheets[0]

max_row=sheet1.max_row#最大行数
max_column=sheet1.max_column#最大列数

for m in range(1,max_row+1):
    for n in range(97,97+max_column):#chr(97)='a'
        n=chr(n)#ASCII字符
        i='%s%d'%(n,m)#单元格编号
        cell1=sheet1[i].value#获取data单元格数据
        sheet2[i].value=cell1#赋值到test单元格

wb2.save('test.xlsx')#保存数据
wb1.close()#关闭excel
wb2.close()

 

参考：https://www.cnblogs.com/valorchang/p/11590652.html

https://blog.csdn.net/chaodaibing/article/details/108343756

https://www.cnblogs.com/liuda9495/p/9039732.html

https://www.cnblogs.com/kearney908/p/7083958.html