python的openpyxl的使用笔记

闲与仙人扫落花 發表於 2019-9-26 14:26:00

<h3>openpyxl模块介绍</h3>
openpyxl模块是一个读写Excel 2010文档的Python库，如果要处理更早格式的Excel文档，需要用到额外的库，openpyxl是一个比较综合的工具，能够同时读取和修改Excel文档。其他很多的与Excel相关的项目基本只支持读或者写Excel一种功能。
<h3>安装openpyxl模块</h3>
openpyxl是一个开源项目，这里使用如下命令安装openpyxl模块
<pre class="line-numberslanguage-undefined"><code class="language-undefined">pip3 install openpyxl
</code></pre>
<h1>一.作成Excel文件</h1>
<h2>1.作成空的excel</h2>
<pre class="line-numberslanguage-python"><code class="language-python">import openpyxl

if __name__ == '__main__':

#作成一个新的空的Excel
wb = openpyxl.Workbook()

# 保存
wb.save('example.xlsx')
</code></pre>
<h2>2.变更默认sheet页的名称</h2>
<pre class="line-numberslanguage-python"><code class="language-python">import openpyxl

if __name__ == '__main__':

wb = openpyxl.Workbook
#当前打开的sheet页 wb.active
ws = wb.active

# 更改默认名称Sheet`
ws.title = "WorkSheetTitle"

# 保存
wb.save('example.xlsx')
</code></pre>
<h2>3.作成含多个sheet页的Excel</h2>
<pre class="line-numberslanguage-python"><code class="language-python">import openpyxl

if __name__ == '__main__':

wb = openpyxl.Workbook()
#当前打开的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)

# 保存
wb.save('example.xlsx')
</code></pre>
<h2>4.sheet页WS的tab颜色变更</h2>
<pre class="line-numberslanguage-python"><code class="language-python">import openpyxl

if __name__ == '__main__':

wb = openpyxl.Workbook()
ws = wb.active

# 更改默认名称Sheet`
ws.title = "WorkSheetTitle"

# WS的tab颜色设定
ws.sheet_properties.tabColor = "1072BA"

# 保存
wb.save('example.xlsx')
</code></pre>
<h2>二.单元格赋值</h2>
<h3>1.指定坐标</h3>
<pre class="line-numberslanguage-import"><code class="importlanguage-import">
if __name__ == '__main__':

wb = openpyxl.Workbook()
ws = wb.active

# 更改默认名称Sheet`
ws.title = "WorkSheetTitle"

# 给单元格赋值
ws["A1"] = "HOGE"
ws["B1"] = "FUGA"

# 保存
wb.save('example.xlsx')
</code></pre>
<h3>2.指定行列</h3>
<pre class="line-numberslanguage-import"><code class="importlanguage-import">
if __name__ == '__main__':

wb = openpyxl.Workbook()
ws = wb.active

# 更改默认名称Sheet`
ws.title = "WorkSheetTitle"

# 指定行列给单元格赋值
ws.cell(row=4, column=2, value=10)

# 保存
wb.save('example.xlsx')
</code></pre>
<h3>3.指定范围</h3>
<pre class="line-numberslanguage-csharp"><code class="language-csharp">import openpyxl

if __name__ == '__main__':

wb = openpyxl.Workbook()
ws = wb.active

# 更改默认名称Sheet`
ws.title = "worksheettitle"

# 指定行列给单元格赋值
v = 0
for i in range(1,10):
 for n in range(1,10):
 ws.cell(row=i, column=n, value=v)
 v += 1

# 保存
wb.save('example.xlsx')
</code></pre>
<h3>4.一次输出行</h3>
<pre class="line-numberslanguage-python"><code class="language-python">import openpyxl

# column名
column_title = ["FirstName", "LastName"]

if __name__ == '__main__':
"""
CELL放入值
"""
wb = openpyxl.Workbook()
ws = wb.active

# 更改默认名称Sheet`
ws.title = "worksheettitle"

# column名和値顺序放入单元格中
rows = [
 column_title,
 ["Tarou", "Tanaka"],
 ["Tarou", "Suzuki"],
 ["Tarou", "Uchiayama"],
]
for row in rows:
 ws.append(row)

# 保存
wb.save('example.xlsx')
</code></pre>
<div class="image-package">
<div class="image-container">
<div class="image-container-fill"> </div>
<div class="image-view" data-width="508" data-height="268"><img src="https://upload-images.jianshu.io/upload_images/14607294-8e5045b58d5d0283.png?imageMogr2/auto-orient/strip|imageView2/2/w/508/format/webp"></div>
</div>
<div class="image-caption">一次输出行运行结果</div>
</div>
<h3>5.单元格内换行</h3>
<pre class="line-numberslanguage-python"><code class="language-python">import openpyxl

if __name__ == '__main__':
"""
单元格内换行
"""
wb = openpyxl.Workbook()
ws = wb.active

# 更改默认名称Sheet`
ws.title = "WorkSheetTitle"

# 单元格内换行
ws['A1'] = "A\nB\nC"
ws['A1'].alignment = openpyxl.styles.Alignment(wrapText=True)

# 保存
wb.save('example.xlsx')
</code></pre>
<div class="image-package">
<div class="image-container">
<div class="image-container-fill"> </div>
<div class="image-view" data-width="472" data-height="252"><img src="https://upload-images.jianshu.io/upload_images/14607294-f660ed5950c416df.png?imageMogr2/auto-orient/strip|imageView2/2/w/472/format/webp"></div>
</div>
<div class="image-caption">单元格内换行运行结果图</div>
</div>
<h2>三.设置单元格的style</h2>
<h4>1.style文稿</h4>
https://openpyxl.readthedocs.io/en/default/styles.html#cell-styles-and-named-styles
<h2>2.设置字体font</h2>
<pre class="line-numberslanguage-python"><code class="language-python">import openpyxl

if __name__ == '__main__':
"""
设置字体font
"""
wb = openpyxl.Workbook()
ws = wb.active

# 更改默认名称Sheet`
ws.title = "worksheettitle"

# 设置font
font = openpyxl.styles.Font(
 name = "宋体",
 size = 15,
)
a1 = ws["A1"]
a1.font = font
a1.value = "TEST"

# 保存
wb.save('example.xlsx')
</code></pre>
<h2>3.单元格边框border</h2>
<pre class="line-numberslanguage-python"><code class="language-python">import openpyxl
from openpyxl.styles import Border, Side

if __name__ == '__main__':
"""
设置单元格style
"""
wb = openpyxl.Workbook()
ws = wb.active

# 更改默认名称Sheet`
ws.title = "worksheettitle"

# 设置单元格border的style
border = Border(
 left=Side(
 border_style="thin",
 color="FF0000"
 ),
 right=Side(
 border_style="thin",
 color="FF0000"
 ),
 top=Side(
 border_style="thin",
 color="FF0000"
 ),
 bottom=Side(
 border_style="thin",
 color="FF0000"

 )
)
b2 = ws["B2"]
b2.border = border
b2.value = "TEST"

# 保存
wb.save('example.xlsx')
</code></pre>
<div class="image-package">
<div class="image-container">
<div class="image-container-fill"> </div>
<div class="image-view" data-width="520" data-height="240"><img src="https://upload-images.jianshu.io/upload_images/14607294-a8015c3c2605f58a.png?imageMogr2/auto-orient/strip|imageView2/2/w/520/format/webp"></div>
</div>
<div class="image-caption">单元格边框style 运行结果图</div>
</div>
<h2>4.合并单元格</h2>
<pre class="line-numberslanguage-python"><code class="language-python">import openpyxl

if __name__ == '__main__':

wb = openpyxl.Workbook()
ws = wb.active

ws.title = "worksheettitle"

# 合并单元格
ws.merge_cells("A1:E1")
ws["A1"] = "HOGE"

# 保存
wb.save('example.xlsx')
</code></pre>
<div class="image-package">
<div class="image-container">
<div class="image-container-fill"> </div>
<div class="image-view" data-width="528" data-height="178"><img src="https://upload-images.jianshu.io/upload_images/14607294-5ca9391d72146b6a.png?imageMogr2/auto-orient/strip|imageView2/2/w/528/format/webp"></div>
</div>
<div class="image-caption">合并单元格运行效果图</div>
</div>
<h2>5.单元格填充颜色</h2>
<pre class="line-numberslanguage-python"><code class="language-python">import openpyxl
from openpyxl.styles import PatternFill

if __name__ == '__main__':

wb = openpyxl.Workbook()
ws = wb.active

ws.title = "worksheettitle"

# 单元格填充颜色
fill = PatternFill(fill_type='solid',
 fgColor='FFFF0000')
b2 = ws["B2"]
b2.fill = fill
b2.value = "TEST"

# 保存
wb.save('example.xlsx')
</code></pre>
<div class="image-package">
<div class="image-container">
<div class="image-container-fill"> </div>
<div class="image-view" data-width="478" data-height="170"><img src="https://upload-images.jianshu.io/upload_images/14607294-afd5b5cc26d0aa96.png?imageMogr2/auto-orient/strip|imageView2/2/w/478/format/webp"></div>
</div>
<div class="image-caption">单元格填充颜色效果图</div>
</div>
<h2>四.hyperlink超链接</h2>
<pre class="line-numberslanguage-python"><code class="language-python">import openpyxl

if __name__ == '__main__':

wb = openpyxl.Workbook()
ws = wb.active

ws.title = "worksheettitle"

# 作成第二个sheet页名称胃example
ws2 = wb.create_sheet("example")

# 设置超链接到“example”sheet页鼠标定格在A5单元格
ws["A1"] = "Link"
ws["A1"].hyperlink = "example.xlsx#example!A5"

# 保存
wb.save('example.xlsx')
</code></pre>
<h2>五.读Excel文档</h2>
<pre class="line-numberslanguage-python"><code class="language-python">from openpyxl import Workbook, load_workbook

wb = load_workbook('./example.xlsx')
ws = wb.active

for row in ws:
for cell in row:
 print(cell)
</code></pre>
<div> </div>
<div class="_1kCBjS">
<div class="_18vaTa">
<div class="_3BUZPB">
<div class="_2Bo4Th"> </div>
7人点赞</div>
<div class="_3BUZPB"> </div>
</div>
<div class="_18vaTa">日记本
 
<div class="_3BUZPB ant-dropdown-trigger"> </div>
</div>
</div> 
来源：https://www.cnblogs.com/valorchang/p/11590652.html

頁: [1]

琼殿技术论坛's Archiver

python的openpyxl的使用笔记