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)

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)