openpyxl基本操作

参考资料：OpenPyXL的使用教程（一）

openpyxl 基本操作

from openpyxl import load_workbook, Workbook

# ========
# 新建工作簿+sheet
wb = Workbook()  #guess_types=True

#
filepath = r'e:/aa.xlsx'
wb = load_workbook(filepath)
wb.save(filepath)  #如果存在会覆盖，as_template=True, 将文件保存为模板，默认False

# sheet名列表
#wb.get_sheet_names()  #已弃用
wb.sheetnames
[sheet.title for sheet in wb]

# ========
# 新建sheet
ws = wb.create_sheet(0) #第一个位置
ws = wb.create_sheet(title='1', index=1)
ws.title = 'Sheet1'

# 访问指定sheet
ws = wb.active  #调用wb._active_sheet_index
#ws = wb.get_sheet_by_name(sheet_name)  #已弃用
ws = wb['Sheet1']

ws.rows
ws.columns
#ws.sheet_properties.tabColor = "1072BA"  #改变sheet标签栏字体颜色

# 增删改查
ws['A4'] = 4
ws.cell('A4')
c = ws.cell(row = 4, column = 2)
c.value = 4
cell_range = ws['A1':'C2']
#[cell.value for row in ws.iter_rows('A1:C2') for cell in row]

# ========
dataframe.to_excel(filepath, sheet_name=sheet_name, index=None)
writer = pd.ExcelWriter(filepath, engine='openpyxl')
dataframe.to_excel(excel_writer=writer, sheet_name=sheet_name, index=None)
writer.save()
writer.close()

# ========
def excelAddSheet(dataframe, filepath, sheet_name):
    import os
    from openpyxl import load_workbook, Workbook
    
    #FileNotFoundError
    if os.path.exists(filepath):
        #当表名已存在时，后面还可以添加参数，进行追加
        wb = load_workbook(filepath)  #keep_vba=True
        #sheet = book.active
        with pd.ExcelWriter(filepath, engine='openpyxl') as writer:
            writer.book = wb
            #stratrow=1, startcol=1
            dataframe.to_excel(excel_writer=writer, sheet_name=sheet_name, index=None)
        #writer.save()
        #writer.close()
    else:
        dataframe.to_excel(filepath, sheet_name=sheet_name, index=None)

　　oracle2Excel

import cx_Oracle
import openpyxl

# 用户名
username = 'tj_20160217'
# 密码
password = 'tj_20160217'
# IP
ip = '10.0.250.19'
# 端口
port = '1521'
# 数据库实例名
servername = 'starbass'
# 获取连接
xlsxPath = 'E:\\sysconfigen.xlsx'
sheetName = 'sysconfigen'
wb = openpyxl.Workbook()
ws = wb.active
ws.title = sheetName
with cx_Oracle.connect(username + '/' + password + '@' + ip + ':' + port + '/' + servername) as db:
    cur = db.cursor()
    result = cur.execute('select * from sysconfigen')
    row = 1
    column = 1
    # 获取表头
    for header in cur.description:
        ws.cell(row, column).value = header[0]
        column += 1
    row += 1
    for one_result in result.__iter__():
        column = 1
        for at in one_result:
            if at:
                ws.cell(row, column).value = at
            else:
                # 空值处理
                ws.cell(row, column).value = 'null'
            column += 1
        row += 1
wb.save(xlsxPath)

　　

参考资料：https://github.com/a18792721831/StudyPython/tree/master/helloExcel