这篇文章主要介绍了python使用openpyxl操作excel的方法步骤，详细的介绍了openpyxl的常用属性以及操作excel的方法，感兴趣的可以了解一下
一 前言
放大招了，学完这篇openpyxl第三方库，读者将会懂得如何灵活的读取excel数据,如何创建excel工作表；更新工作表，删除工作表；是不是感觉很强大，留下赞赞吧！！
二 openpyxl常用属性函数
三 读取excel
读取整体流程如下
- 加载工作本
- 获取标签页
- 获取指定区域的单元格
- 获取单个单元格对象
- 通过单元格对象获取值
3.1 准备工作
创建一个名为zszxz.xlsx excel文件；其中标签sheet名为zszxz; 列A B C 如下；
<span>id num name </span>1 100<span> zszxz </span>2 101<span> smile </span>3 102<span> kitty </span>4 103<span> wolf </span>5 104<span> cloud </span>6 105 water
www#gaodaima.com来源gaodai#ma#com搞*!代#%^码$网搞代码
3.2 安装
支持操作excel和图像
<span>pip install openpyxl pip install pillow</span>
如果有读者这里不会的话，可以加入小编的Python交流扣扣群在群一起讨论和下载教程，群内还有最新的Python学习视频教程供免费下载。
3.3 获取所有标签页名称
<span>#</span><span> -*- coding: utf-8 -*-</span> <span>import</span><span> openpyxl path </span>= r<span>"</span><span>C:mydatageneratorpyzszxz.xlsx</span><span>"</span> <span>#</span><span> 加载工作本</span> workbook =<span> openpyxl.load_workbook(path) </span><span>#</span><span> 获得所有标签页</span> sheet_names =<span> workbook.get_sheet_names() </span><span>print</span>(sheet_names)
输出
[“zszxz”]
正确的获取方式
<span>#</span><span> -*- coding: utf-8 -*-</span> <span>import</span><span> openpyxl path </span>= r<span>"</span><span>C:mydatageneratorpyzszxz.xlsx</span><span>"</span> <span>#</span><span> 加载工作本</span> workbook =<span> openpyxl.load_workbook(path) </span><span>#</span><span> 获得所有标签页名称</span> <span>print</span>(workbook.sheetnames)
3.4 获取指定标签页对象
不建议通过方法获得标签页对象，建议通过属性方式获取；
<span>#</span><span> -*- coding: utf-8 -*-</span> <span>import</span><span> openpyxl path </span>= r<span>"</span><span>C:mydatageneratorpyzszxz.xlsx</span><span>"</span> <span>#</span><span> 加载工作本</span> workbook =<span> openpyxl.load_workbook(path) </span><span>#</span><span> 获得指定的sheet</span> sheet = workbook.get_sheet_by_name(<span>"</span><span>zszxz</span><span>"</span><span>) </span><span>print</span>(sheet)
输出
<Worksheet “zszxz”>
正确的获取方式
<span>#</span><span> -*- coding: utf-8 -*-</span> <span>import</span><span> openpyxl path </span>= r<span>"</span><span>C:mydatageneratorpyzszxz.xlsx</span><span>"</span> <span>#</span><span> 加载工作本</span> workbook =<span> openpyxl.load_workbook(path) </span><span>#</span><span> 获取sheet对象</span> sheet = workbook[<span>"</span><span>zszxz</span><span>"</span><span>] </span><span>print</span>(sheet)
输出
<Worksheet “zszxz”>
当然读者也可以通过循环工作本方式获取sheet，不赘述；
3.5 复制sheet
在已有的sheet上可以进行复制一个副本；
<span>#</span><span> -*- coding: utf-8 -*-</span> <span>import</span><span> openpyxl path </span>= r<span>"</span><span>C:mydatageneratorpyzszxz.xlsx</span><span>"</span> <span>#</span><span> 加载工作本</span> workbook =<span> openpyxl.load_workbook(path) </span><span>#</span><span> 获取sheet对象</span> sheet = workbook[<span>"</span><span>zszxz</span><span>"</span><span>] </span><span>#</span><span> 复制sheet</span> cp_sheet =<span> workbook.copy_worksheet(sheet) </span><span>print</span>(cp_sheet)
输出
<Worksheet “zszxz Copy”>
3.5 获取指定一个单元格对象
指定获取A1单元格对象
<span>#</span><span> -*- coding: utf-8 -*-</span> <span>import</span><span> openpyxl path </span>= r<span>"</span><span>C:mydatageneratorpyzszxz.xlsx</span><span>"</span> <span>#</span><span> 加载工作本</span> workbook =<span> openpyxl.load_workbook(path) </span><span>#</span><span> 获取sheet对象</span> sheet = workbook[<span>"</span><span>zszxz</span><span>"</span><span>] </span><span>#</span><span> 获取指定单元格</span> cell = sheet[<span>"</span><span>A1</span><span>"</span><span>] </span><span>print</span>(cell)
输出
<Cell “zszxz”.A1>
方式二
<span>#</span><span> -*- coding: utf-8 -*-</span> <span>import</span><span> openpyxl path </span>= r<span>"</span><span>C:mydatageneratorpyzszxz.xlsx</span><span>"</span> <span>#</span><span> 加载工作本</span> workbook =<span> openpyxl.load_workbook(path) </span><span>#</span><span> 获取sheet对象</span> sheet = workbook[<span>"</span><span>zszxz</span><span>"</span><span>] </span><span>#</span><span> 获取指定单元格</span> cell = sheet.cell(row=1, column=1<span>) </span><span>print</span>(cell)
输出
<Cell “zszxz”.A1>
3.6 访问多个单元格
访问单元格获取单元格对象；
<span>#</span><span> -*- coding: utf-8 -*-</span>
<span>import</span><span> openpyxl
path </span>= r<span>"</span><span>C:mydatageneratorpyzszxz.xlsx</span><span>"</span>
<span>#</span><span> 加载工作本</span>
workbook =<span> openpyxl.load_workbook(path)
</span><span>#</span><span> 获取sheet对象</span>
sheet = workbook[<span>"</span><span>zszxz</span><span>"</span><span>]
cells_range </span>= sheet[<span>"</span><span>A1</span><span>"</span>:<span>"</span><span>C1</span><span>"</span><span>]
</span><span>for</span> cells <span>in</span><span> cells_range:
</span><span>for</span> cell <span>in</span><span> cells:
</span><span>print</span>(cell)
输出
<Cell “zszxz”.A1>
<Cell “zszxz”.B1>
<Cell “zszxz”.C1>
方式二
<span>#</span><span> -*- coding: utf-8 -*-</span> <span>import</span><span> openpyxl path </span>= r<span>"</span><span>C:mydatageneratorpyzszxz.xlsx</span><span>"</span> <span>#</span><span> 加载工作本</span> workbook =<span> openpyxl.load_workbook(path) </span><span>#</span><span> 获取sheet对象</span> sheet = workbook[<span>"</span><span>zszxz</span><span>"</span><span>] </span><span>#</span><span> 获取指定范围的行</span> row = sheet.iter_rows(min_row=1, max_col=3, max_row=1<span>) </span><span>for</span> cell <span>in</span><span> row: </span><span>print</span>(cell)
输出
(<Cell “zszxz”.A1>, <Cell “zszxz”.B1>, <Cell “zszxz”.C1>)
sheet.iter_cols(min_row, max_col, max_row)通用的道理，不赘述
3.7 获取全部行
<span>#</span><span> -*- coding: utf-8 -*-</span> <span>import</span><span> openpyxl path </span>= r<span>"</span><span>C:mydatageneratorpyzszxz.xlsx</span><span>"</span> <span>#</span><span> 加载工作本</span> workbook =<span> openpyxl.load_workbook(path) </span><span>#</span><span> 获取sheet对象</span> sheet = workbook[<span>"</span><span>zszxz</span><span>"</span><span>] </span><span>#</span><span> 获取所有行</span> <span>for</span> row <span>in</span><span> sheet.rows: </span><span>print</span>(row)
输出
(<Cell “zszxz”.A1>, <Cell “zszxz”.B1>, <Cell “zszxz”.C1>)
(<Cell “zszxz”.A2>, <Cell “zszxz”.B2>, <Cell “zszxz”.C2>)
(<Cell “zszxz”.A3>, <Cell “zszxz”.B3>, <Cell “zszxz”.C3>)
(<Cell “zszxz”.A4>, <Cell “zszxz”.B4>, <Cell “zszxz”.C4>)
(<Cell “zszxz”.A5>, <Cell “zszxz”.B5>, <Cell “zszxz”.C5>)
(<Cell “zszxz”.A6>, <Cell “zszxz”.B6>, <Cell “zszxz”.C6>)
(<Cell “zszxz”.A7>, <Cell “zszxz”.B7>, <Cell “zszxz”.C7>)
同理 sheet.columns获取所有列不再赘述
3.8 获取值
cell.value获取属性值
<span>#</span><span> -*- coding: utf-8 -*-</span>
<span>import</span><span> openpyxl
path </span>= r<span>"</span><span>C:mydatageneratorpyzszxz.xlsx</span><span>"</span>
<span>#</span><span> 加载工作本</span>
workbook =<span> openpyxl.load_workbook(path)
</span><span>#</span><span> 获取sheet对象</span>
sheet = workbook[<span>"</span><span>zszxz</span><span>"</span><span>]
cells_range </span>= sheet[<span>"</span><span>A1</span><span>"</span>:<span>"</span><span>C1</span><span>"</span><span>]
</span><span>for</span> cells <span>in</span><span> cells_range:
</span><span>for</span> cell <span>in</span><span> cells:
</span><span>#</span><span> 获取属性值</span>
<span>print</span>(cell.value)
输出
id
num
name
四 写入 excel
4.1 写入文本
<span>#</span><span> -*- coding: utf-8 -*-</span> <span>import</span><span> openpyxl path </span>= r<span>"</span><span>C:mydatageneratorpyzszxz_write.xlsx</span><span>"</span> <span>#</span><span> 创建一个工作本</span> workbook =<span> openpyxl.Workbook() </span><span>#</span><span> 创建一个 sheet</span> sheet = workbook.create_sheet(<span>"</span><span>zszxz</span><span>"</span><span>) </span><span>#</span><span> 写入文本形式</span> sheet[<span>"</span><span>A1</span><span>"</span>] = <span>"</span><span>zszxz666</span><span>"</span> <span>#</span><span> 读取</span> <span>print</span>(sheet[<span>"</span><span>A1</span><span>"</span><span>].value) </span><span>#</span><span> 保存</span> workbook.save(path)
输出
zszxz666
4.2 写入数字
<span>#</span><span> -*- coding: utf-8 -*-</span> <span>import</span><span> openpyxl </span><span>import</span><span> datetime path </span>= r<span>"</span><span>C:mydatageneratorpyzszxz_write.xlsx</span><span>"</span> <span>#</span><span> 创建一个工作本</span> workbook =<span> openpyxl.Workbook() </span><span>#</span><span> 创建一个 sheet</span> sheet = workbook.create_sheet(<span>"</span><span>zszxz</span><span>"</span><span>) </span><span>#</span><span> 写入值数字形式</span> sheet[<span>"</span><span>A2</span><span>"</span>] = datetime.datetime(2020, 10, 15<span>) </span><span>print</span>(sheet[<span>"</span><span>A2</span><span>"</span><span>].value) </span><span>#</span><span> 保存</span> workbook.save(path)
输出
2020-10-15 00:00:00
4.3 写入公式
<span>#</span><span> -*- coding: utf-8 -*-</span> <span>import</span><span> openpyxl path </span>= r<span>"</span><span>C:mydatageneratorpyzszxz_write.xlsx</span><span>"</span> <span>#</span><span> 创建一个工作本</span> workbook =<span> openpyxl.Workbook() </span><span>#</span><span> 创建一个 sheet</span> sheet = workbook.create_sheet(<span>"</span><span>zszxz</span><span>"</span><span>) </span><span>#</span><span> 写入公式</span> sheet[<span>"</span><span>A3</span><span>"</span>] = <span>"</span><span>=SUM(1, 1)</span><span>"</span> <span>print</span>(sheet[<span>"</span><span>A3</span><span>"</span><span>].value) </span><span>#</span><span> 保存</span> workbook.save(path)
4.5 取消合并单元格
<span>#</span><span> -*- coding: utf-8 -*-</span> <span>import</span><span> openpyxl path </span>= r<span>"</span><span>C:mydatageneratorpyzszxz_write.xlsx</span><span>"</span> <span>#</span><span> 创建一个工作本</span> workbook =<span> openpyxl.Workbook() </span><span>#</span><span> 创建一个 sheet</span> sheet = workbook.create_sheet(<span>"</span><span>zszxz</span><span>"</span><span>) </span><span>#</span><span> 取消合并单元格</span> sheet.unmerge_cells(<span>"</span><span>A2:D2</span><span>"</span><span>) </span><span>#</span><span> 保存</span> workbook.save(path)
4.5 插入图片
<span>#</span><span> -*- coding: utf-8 -*-</span> <span>import</span><span> openpyxl </span><span>from</span> openpyxl.drawing.image <span>import</span><span> Image path </span>= r<span>"</span><span>C:mydatageneratorpyzszxz_write.xlsx</span><span>"</span> <span>#</span><span> 创建一个工作本</span> workbook =<span> openpyxl.Workbook() </span><span>#</span><span> 创建一个 sheet</span> sheet = workbook.create_sheet(<span>"</span><span>zszxz</span><span>"</span><span>) </span><span>#</span><span> 设置图像</span> img = Image(r<span>"</span><span>C:mydatageneratorpymain.jpg</span><span>"</span><span>) </span><span>#</span><span> 设置图像单元格说明</span> sheet[<span>"</span><span>A1</span><span>"</span>] = <span>"</span><span>you are my angel</span><span>"</span> <span>#</span><span> 插入图片</span> sheet.add_image(img, <span>"</span><span>A1</span><span>"</span><span>) </span><span>#</span><span> 保存</span> workbook.save(path)
结果如下：
4.6 隐藏轮廓
<span>#</span><span> -*- coding: utf-8 -*-</span> <span>import</span><span> openpyxl path </span>= r<span>"</span><span>C:mydatageneratorpyzszxz_write.xlsx</span><span>"</span><span> wb </span>=<span> openpyxl.Workbook() ws </span>=<span> wb.create_sheet() </span><span>#</span><span> 隐藏行 A-B</span> ws.column_dimensions.group(<span>"</span><span>A</span><span>"</span>, <span>"</span><span>B</span><span>"</span>, hidden=<span>True) </span><span>#</span><span> 隐藏 列 1 -5 </span> ws.row_dimensions.group(1, 5, hidden=<span>True) wb.save(path)</span>
4.7 设置行高列高
<span>#</span><span> -*- coding: utf-8 -*-</span> <span>import</span><span> openpyxl path </span>= r<span>"</span><span>C:mydatageneratorpyzszxz_write.xlsx</span><span>"</span> <span>#</span><span> 创建一个工作本</span> workbook =<span> openpyxl.Workbook() </span><span>#</span><span> 创建一个 sheet</span> sheet = workbook.create_sheet(<span>"</span><span>zszxz</span><span>"</span><span>) </span><span>#</span><span> 写入文本形式</span> sheet[<span>"</span><span>A1</span><span>"</span>] = <span>"</span><span>zszxz666</span><span>"</span> <span>#</span><span> 设置行高</span> sheet.row_dimensions[1].height = 50 <span>#</span><span> 设置列高</span> sheet.column_dimensions[<span>"</span><span>A</span><span>"</span>].width = 30<span> workbook.save(path) </span>
五 删除sheet
<span>#</span><span> -*- coding: utf-8 -*-</span> <span>import</span><span> openpyxl path </span>= r<span>"</span><span>C:mydatageneratorpyzszxz_write.xlsx</span><span>"</span> <span>#</span><span> 加载工作本</span> workbook =<span> openpyxl.load_workbook(path) </span><span>#</span><span> 获得sheet</span> sheet = workbook[<span>"</span><span>Sheet</span><span>"</span><span>] </span><span>#</span><span> 移除sheet</span> <span>workbook.remove(sheet) </span><span>#</span><span> 保存</span> workbook.save(path)
六 更新工作表
<span>#</span><span> -*- coding: utf-8 -*-</span> <span>import</span><span> openpyxl path </span>= r<span>"</span><span>C:mydatageneratorpyzszxz_write.xlsx</span><span>"</span> <span>#</span><span> 加载工作本</span> workbook =<span> openpyxl.load_workbook(path) </span><span>#</span><span> 获得sheet</span> sheet = workbook[<span>"</span><span>zszxz</span><span>"</span><span>] </span><span>#</span><span> 获得值</span> val = sheet[<span>"</span><span>A1</span><span>"</span><span>].value </span><span>print</span><span>(val) </span><span>#</span><span> 重新赋值</span> new_val = sheet[<span>"</span><span>A1</span><span>"</span>].value = <span>"</span><span>zszxz</span><span>"</span> <span>print</span><span>(new_val) </span><span>#</span><span> 保存</span> workbook.save(path)
输出
zszxz666
zszxz
七 参考文档
更多内容参考官方文档；openpyxl，到此这篇关于python使用openpyxl操作excel的方法步骤的文章就介绍到这了。
实操远比理论来的有用，这里给读者推荐一套《2020最新企业级项目实战案例》视频教程，这套实战案例对于小白和刚入门的小鸟还是比较友好的，有许多打基础的案例，能够帮你快速入门Python。需要的读者可以点击“领取”下载。
本文的文字及图片来源于网络加上自己的想法,仅供学习、交流使用,不具有任何商业用途,版权归原作者所有,如有问题请及时联系我们以作处理。
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：十分钟教会你使用Python操作excel，内附步骤和代码！python其实很简单
转载请注明原文链接：十分钟教会你使用Python操作excel，内附步骤和代码！python其实很简单
