python修改已存在的xlsx件--OpenPyXL

最新推荐文章于 2024-09-18 09:10:46 发布

JonyLei

最新推荐文章于 2024-09-18 09:10:46 发布

阅读量2.4w

点赞数 13

分类专栏： python 文章标签： python OpenPyXL excel

本文链接：https://blog.csdn.net/ChenLeihappy/article/details/80989552

版权

python 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

python提供了很多修改excel的模块，包括xlsxWriter、xlrd&xlwt、openPyXL、Microsoft Excel API、Pandas等，其中

xlsxWriter：只支持新建excel和向excel中写入数据，并不支持对已有excel的读取和修改，只能从零开始，功能很强大，支持大文件的写入，速度还比较多；适合的场景：需要创建xlsx文件，不需要读，且数据量大；

xlrd&xlwt&xlutils：对xls文件的读写和其他很多全面的功能，但是对xlsx的excel支持很差，经过这三个模块修改过的xlsx文件无法打开，显示已损坏（不知道有没有解决方法）；适用的场景：要读取xls和xlsx文件中的值，最后生成xls文件，需要的功能不是很复杂可以考虑使用该种方法；
参考文档：https://blog.csdn.net/u013045749/article/details/49910695

OpenPyXL：较好的支持对xlsx文件的修改，功能比较强大，适用于需要处理XLSX文件，需要修改XLSX文件中的值，最后生成xlsx。openpyxl（可读写excel表）专门处理Excel2007及以上版本产生的xlsx文件，xls和xlsx之间转换容易

注意：如果文字编码是“gb2312” 读取后就会显示乱码，请先转成Unicode。

官网上最推荐的是openpyxl：

综上，所以选择使用OpenPyX来做一个修改excel的小程序。

OpenPyXL的官网参考：
https://openpyxl.readthedocs.io/en/latest/usage.html
https://openpyxl.readthedocs.io/en/stable/

1、OpenPyXL模块的安装

pip install openpyxl

2、快速实现xlsx文件的单元格修改

# -*- coding: utf-8 -*-
from openpyxl import load_workbook
file_home = 'C:\\files\\openpyxs.xlsx'
wb = load_workbook(filename= file_home)
sheet_ranges = wb['Sheet1']
print(sheet_ranges['A1'].value)
ws = wb['Sheet1'] //根据Sheet1这个sheet名字来获取该sheet
ws["A1"] = 'LJK5679842'   //修改A1的值为LJK5679842
ws['D6'] = 'LKJI66666666666666'  //修改D6的值为，其中D6-F6是合并后的单元格，修改数据需修改最左上角单元格数据，来替代整个merge单元格
wb.save(file_depart)    //保存修改后的excel

3、OpenPyXL的学习

迭代所有的行：

#获取表格所有行和列，两者都是可迭代的
rows = ws.rows
columns = ws.columns
#迭代所有的行
for row in rows:
 line = [col.value for col in row]
 print line

#通过坐标读取值
print ws.cell('A1').value # A表示列,1表示行
print ws.cell(row=1, column=1).value

新建excel处理：

wb = Workbook()#创建工作簿 
ws = wb.active#激活工作表 
ws1 = wb.create_sheet("Mysheet")#创建mysheet表 
ws.title = "New Title"#表明改为New Title 
ws.sheet_properties.tabColor = "1072BA"#颜色 
ws['A4'] = 4#赋值 
d = ws.cell(row=4, column=2, value=10)#给第4行第2列赋值为10
cell_range = ws['A1':'C2']#选择单元格区域 
wb.save('test.xlsx')#保存

append函数在文末添加多行数据：

# 添加一行，在文末
row = [1 ,2, 3, 4, 5]
ws.append(row)
 
# 附加多行，从第一列开始附加
rows = [
  ['Number', 'data1', 'data2'],
  [2, 40, 30],
  [3, 40, 25],
  [4, 50, 30],
  [5, 30, 10],
  [6, 25, 5],
  [7, 50, 10],
]

合并和拆分单元格

# 合并单元格， 往左上角写入数据即可
sheet.merge_cells('B1:G1') # 合并一行中的几个单元格
sheet.merge_cells('A1:C3') # 合并一个矩形区域中的单元格

合并后只可以往左上角写入数据，也就是区间中:左边的坐标。
如果这些要合并的单元格都有数据，只会保留左上角的数据，其他则丢弃。换句话说若合并前不是在左上角写入数据，合并后单元格中不会有数据。
以下是拆分单元格的代码。拆分后，值回到A1位置。

插入时间格式数据：

import datetime
#python 类型数据会被自动转换
ws['A2'] = datetime.datetime.now()
#保存修改
wb.save("sample.xlsx")

获取一个单元格的数据：

c = ws['A4']
c = ws.cell('A4') 
d = ws.cell(row = 4, column = 2)  
cell_range = ws['A1':'C2']  //一次获取多个单元格的数据
ws['A1'] = 100  //写入值

中文编码的问题：

def gbk2utf(in_data , tag):  
    if 1 == tag:  
        return in_data.encode('gbk').decode('gbk')  
    elif 0 == tag:  
        return in_data.encode('gbk').decode('gbk').encode('utf8')

openpyxl会自动转换为不同的类型，有些表格中会有中文出现，就需要进行相应的转码。可以写一个函数专门处理转码，需要时调用。当原始的excel文件是gbk编码时，就需要tag=0的方式去处理，

因为读入后是gbk的编码，需要先encode为gbk再decode为unicode，再encode为utf8,就可以显示了。

4、openpyxl的使用

excel文件的三个对象：
workbook：工作簿，一个excel文件包含多个sheet。
sheet：工作表，一个workbook有多个，表名识别，如“sheet1”,“sheet2”等。

cell：单元格，存储数据对象

操作Excel的一般场景:
    打开或者创建一个Excel需要创建一个Workbook对象
    获取一个表则需要先创建一个Workbook对象，然后使用该对象的方法来得到一个Worksheet对象
    如果要获取表中的数据，那么得到Worksheet对象以后再从中获取代表单元格的Cell对象

open sheet：
通过名字：
ws = wb["sheet_name"]
等同于 ws2 = wb.get_sheet_by_name('sheet_name')
验证命令ws is ws2 is ws3 输出True
不知道名字用index
sheet_names = wb.get_sheet_names()
ws = wb.get_sheet_by_name(sheet_names[index])# index为0为第一张表
或者
ws =wb.active
等同于 ws = wb.get_active_sheet() #通过_active_sheet_index设定读取的表，默认0读第一个表

活动表表名wb.get_active_sheet().title

单元格操作：

c = ws['A4'] #read 等同于 c = ws.cell('A4') 
ws['A4'] = 4 #write 
#ws.cell有两种方式，行号列号从1开始
d = ws.cell(row = 4, column = 2) #行列读写
d = ws.cell('A4') 
写入cell值
    ws.cell(row = 4, column = 2).value = 'test'
    ws.cell(row = 4, column = 2, value = 'test')

特别注意：

get_sheet_names：获取所有表格的名称(新版已经不建议使用，通过Workbook的sheetnames属性即可获取)
例：

sheetnm = wb.worksheets
sheet = workbook.worksheets[0]

get_sheet_by_name：通过表格名称获取Worksheet对象(新版也不建议使用，通过workbook[‘sheet名‘]获取)；
例：ws = wb['Sheet1']

读取的文件路径为中文时的处理方法：

a = "C:\\test\\test\\同址变更.xlsx"
file_name = unicode(a, "utf8")
或者是：file_name = a.decode("utf-8").encode("gbk")  #进行转码

另外，如果被读取的excel中有图片的话，就会报如下错误：

解决方法：

去掉excel中的图片，或者是将openpyxl的版本回到5.0以下。。。。暂时没找着好办法

refer to：
https://stackoverflow.com/questions/48905957/cant-load-workbook-with-openpyxl-during-handling-of-the-above-exception-anoth

一些好的文档：
https://blog.csdn.net/cyjs1988/article/details/75041915?locationNum=8&fps=1
python 学习小组http://www.thinksaas.cn/group/show/368/page/4
官网：
https://pypi.python.org/pypi/openpyxl
http://openpyxl.readthedocs.io/en/default/
good：
http://blog.csdn.net/suofiya2008/article/details/6284208
http://blog.csdn.net/zzukun/article/details/49946147
http://www.thinksaas.cn/topics/0/501/501962.html