openpyxl基本操作

openpyxl 基本操作

# 创建xml
from openpyxl import Workbook

# 创建工作簿
wb = Workbook()

# 获取sheet页（默认1个）
ws = wb.active
# 取第二个sheet页(需要新建)
# sheet名字，位置
ws1 = wb.create_sheet(title='1', index=1)

# 设置sheet名字
ws.title = 'hello'

# 获取sheet的另一种方式
ws1 = wb['1']

# 获取sheet的方式
# ws1 = wb.get_sheet_by_name('1')

# print(wb.get_sheet_names())
print(wb.sheetnames)

ws['A1'] = 'hello A1'
ws['A2'] = 'hello A2'
ws['A3'] = 'hello A3'

print(ws.cell(row=1, column=1))
print(ws.cell(1, 2))
print(ws.cell(2, 2))
print(ws.cell(3, 4))
for row in ws.iter_rows(1, 3):
    for cell in row:
        cell.value = 'hello cell'

print(ws['A1':'C3'])

# print(ws.rows)
# print(ws.columns)
# 保存文件,如果存在会覆盖
wb.save('E:\hello.xlsx')

# 读取
from openpyxl import load_workbook

wb = load_workbook('E:\hello.xlsx')
ws = wb['hello']
for row in ws.rows:
    for cell in row:
        print(cell.value)

　　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