openpyxl基本操作
2024-08-31 01:33:10
参考资料: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
最新文章
- RandHelper
- [翻译]Telnet简单介绍及在windows 7中开启Telnet客户端
- Mysql中mysqldump命令使用详解
- POJ 3597 Polygon Division (DP)
- TEZ安装试用
- Python 与 meta programming
- 深入浅出 React Native:使用 JavaScript 构建原生应用
- Java:异常的处理
- 标签跳转break和continue
- [置顶] iOS开发规范
- 【Android】添加菜单和监听菜单方法详解
- CodeForces 385C	Bear and Prime Numbers 素数打表
- C# - 重写虚方法
- 虚拟机装的XP,无法上网,因为没有安装网卡驱动,怎么解决
- 华为软件开发云对比Jenkins-JavaWeb项目持续部署方式
- Lucene全文检索学习笔记
- Java 实现32位MD5加密
- python 编程
- maven插件调试方法
- 【转】git-stash用法小结