Python操作Excel工作簿的示例代码(\*.xlsx)

2023-07-28 06:04:04 319

前言

Excel作为流行的个人计算机数据处理软件，混迹于各个领域，在程序员这里也是常常被处理的对象，可以处理Excel格式文件的Python库还是挺多的，比如xlrd、xlwt、xlutils、openpyxl、xlwings等等，但是每个库处理Excel的方式不同，有些库在处理时还会有一些局限性。

接下来对比一下几个库的不同，然后主要记录一下xlwings这个库的使用，目前这是个人感觉使用起来比较方便的一个库了，其他的几个库在使用过程中总是有这样或那样的问题，不过在特定情况下使用也是挺不错的。

EXCEL文件

Excel被称为电子表格，其实际可以保存的格式分为很多种，但是“Excel工作簿(*.xlsx)”和“Excel97-2003工作簿(*.xls)”是其中比较常用的两种，可以认为.xls格式的表格是03版Excel之前常用的格式，而.xlsx是03版之后，一般指07版Excel之后常用的格式。

一般的Excel程序对于上述的两种格式都可以打开编辑，也可以相互转化存储，不过还是建议在没有特殊要求的情况下使用新版本的格式，一方面新的稳定版本可能会修复之前的一些BUG，同时也会带来进行一些优化。

我也是在写这篇总结之前才发现，一个空的.xlsx格式的文件大小有7KB，而一个空的.xls格式的文件大小有24KB，当我分别写入一个相同的汉字后，两个文件大小变成了10KB和30KB，差距还是不小的，还有一个问题就是在将.xlsx格式的文件另存为.xls格式时还会有兼容性提示，提醒用户有些设置可能会丢失，所以能选新版本还是尽量用新版本吧。

测试环境

因为很多应用程序是不断迭代的，相对应的Python库也是不断迭代的，这里尽可能的给出版本号，不同的版本可能会有不同的问题：

操作系统:Windows10随意版
Python:3.75
xlrd:1.2.0
xlwt:1.3.0
xlutils:2.0.0
openpyxl:3.0.3
xlwings:0.18.0

以上各个程序库使用之前自行安装就行，安装方法就不赘述了，不过可以提供一个可以快速安装镜像源，使用pipinstall-ihttps://pypi.doubanio.com/simple库名可以尽可能解决下载安装缓慢的问题。

Excel具体操作

关于使用Python具体操作Excel的方法可以分为三组，配合使用xlrd、xlwt、xlutils操作作为第一组，使用库openpyxl作为第二组，而xlwings作为第三组，这篇总结重点总结xlwings的使用，其他两组简单了解。

xlrd、xlwt、xlutils

这一组操作Excel的库名字很形象，一个读、一个写、一个小工具，凑到一起就可以对Excel肆意妄为了，下面做个小练习，打开一个Excel文件然后修改第一个单元格的值，再另存为一个新文件，代码如下:

importxlrd
importxlwt
importxlutils.copy

defsave_as_new_file(file_name,new_file_name):
#打开Excel文件
rb=xlrd.open_workbook(file_name)
#创建一个可写入的副本
wb=xlutils.copy.copy(rb)
#获得第一个sheet页签
ws=wb.get_sheet(0)
#第一个单元格写入测试值
ws.write(0,0,'testvalue')
#另存为一个新文件
wb.save(new_file_name)

上述代码无论是操作.xlsx文件还是操作.xls文件都不会报错，但是另存为的.xlsx格式的文件会打不开，同时你会发现正常存储的.xls文件打开后格式全都没了，怎么办，改个参数试试，将打开文件的代码修改如下：

rb=xlrd.open_workbook(file_name,formatting_info=True)

其中参数formatting_info=True就表示打开Excel时保留原有的格式，但是这是相对于.xls格式的文件，对于.xlsx格式的文件直接跑出异常raiseNotImplementedError("formatting_info=Truenotyetimplemented")，就因为处理不了.xlsx格式的文件，我暂时没有使用这几个库操作Excel。

还有一点，这几个库操作单元格时，行和列的索引是从0开始的。

openpyxl

首先说这个库主要用来操作.xlsx格式的文件，对于.xls格式的文件无法打开，会报openpyxldoesnotsupporttheold.xlsfileformat这样的错误，但是可以存储成这样的格式，再次打开时会有格式不匹配的警告，但是基础的数据还在，所以还是优先用来操作.xls格式的文件吧。

写一个新文件的常见用法：

fromopenpyxlimportWorkbook
fromopenpyxlimportload_workbook
fromopenpyxl.stylesimportFont,Fill,Alignment,PatternFill

defwrite_new_excel(file_name):
#创建一个excel文档
wb=Workbook()
#获得当前激活的sheet对象
ws=wb.active
#给A2单元格赋值
ws['A2']='ThisisA2cell'
#一行添加多列数据
ws.append([1,2,'hello'])
#添加新的sheet
ws=wb.create_sheet(title='NewInfo',index=0)
#设置单元格的值
ws['A1']='Thisisnewsheet'

#保存excel
wb.save(file_name)

读取和改写一个原有文件的常见用法：

defread_update_excel(file_name):
#加载Excel表
wb=load_workbook(file_name)
#打印sheet数量
print('sheetcount:',len(wb.sheetnames))
#打印所有sheet名字
print('sheetnamelist:',wb.sheetnames)
#获取第一个sheet对象
ws=wb[wb.sheetnames[0]]
#打印sheet表行数和列数
print('rowscount:',ws.max_row,'colscount:',ws.max_column)
#更新单元格A1的内容
ws['A1']='thisisA1'
#在第二行位置插入一行
ws.insert_rows(2)
#删除第五行
ws.delete_rows(5)
#获取单元格对象，对应B2单元格
cell=ws.cell(2,2)
#设置单元格内容
cell.value='thisisB2'
#修改字体格式为粗体
cell.font=Font(bold=True)
#修改单元格格式
cell.fill=PatternFill("solid",fgColor="F0CDCD")

#保存原文件或另存一个文件
wb.save(file_name)

使用这个库遇到的情况，存储带有样式的数据没有发现问题，但是当加入一个计算公式后，另存为一个文件时明显文件尺寸变小了，但是数据和公式没有发现有问题。

有资料说处理速度真的很慢，因为我处理的文件比较小，但是没有发现这方面的问题，还有一个问题就是说Excel中的宏全部丢失，这个测试的时候确实是丢了，只不过这个好像和文件格式有关，要想保存宏需要存储为.xlsm格式，但是openpyxl使用来操作.xlsx文件的，存储时会导致宏丢失，强行存储为.xlsm格式会导致最终的文件打不开。

还有一点，这个库操作单元格时，行和列的索引是从1开始的。

xlwings

这个库在操作的首先要创建一个App，通过这个创建出来的App对象来操作Excel，非常像把Excel的各种操作api封装到一起，然后通过这个App对象来调用，如果在创建App的时候不设置隐藏参数，是会正常打开Excel程序的。

使用xlwings的基本方式：

importxlwingsasxw

#设置Excel程序不可见
app=xw.App(visible=False,add_book=False)

#通过app操作Excel文件
#app.balabalabala.....
#app.balabalabala.....

#优雅的退出
app.quit()

创建一个新的Excel文件并写入数据：

defwrite_new_excel(app,file_name):
#创建新的Excel表
wb=app.books.add()
#获取当前活动的sheet
ws=wb.sheets.active
#初始化二维区域的值
arr_data=[[1,2,3],[4,5,6],[7,8,'end']]
#设置到新建的Excel中
ws.range('A1:B3').value=arr_data
#设置单独一个单元格的值
ws.range('A4').value='thisisA4'
#设置单独一个单元格的值
ws[3,1].value='thisisB4'
#保存Excel文件
wb.save(file_name)
wb.close()

需要注意的是通过行索引和列索引修改单元格时，起始索引是0。

读入已有Excel表格并修改

defread_update_excel(app,file_name):
#加载已有的表格
load_wb=app.books.open(file_name)
#获取Excel表中第一个sheet
load_ws=load_wb.sheets[0]
#打印sheet的名字
print(load_ws.name)
#根据sheet名字获取sheet对象
load_ws=load_wb.sheets[load_ws.name]
#获取当前活动的sheet
load_ws=load_wb.sheets.active

#获取存在数据的行数和列数
rows=load_ws.api.UsedRange.Rows.count
cols=load_ws.api.UsedRange.Columns.count
print('rowscount:',rows,'colscount:',cols)

#修改指定单元格数据（A1单元格）
load_ws[0,0].value='thisisA1'

#有空行或空列时获取准确的行列数量
print(load_ws.used_range.shape)

#从A1单元格开始扩展到非空行空列，最后的行数和列数
print((load_ws.range('A1').expand().last_cell.row,
load_ws.range('A1').expand().last_cell.column))

#从A1单元格开始扩展到非空行空列，最后的行数和列数
print((load_ws.range('A1').expand().last_cell.row,
load_ws.range('A1').expand().last_cell.column))

#从A1单元格开始扩展到非空行空列，最后形状
print(load_ws.range(1,1).expand().shape)

#从A1单元格开始扩展到非空行空列，最后的行数和列数
print((load_ws.range('A1').expand('table').rows.count,
load_ws.range('A1').expand('table').columns.count))

#保存修改后的Excel
load_wb.save(file_name)
load_wb.close()

Excel增加删除行和列

definsert_delete_rowscols(app,file_name):
#加载已有的表格
load_wb=app.books.open(file_name)
#获取当前活动的sheet
load_ws=load_wb.sheets.active

#从第2行开始插入4行，也就是说2-5行变成新插入的空行
load_ws.api.rows('2:5').insert
#删除第6行和第7行
load_ws.api.rows('6:7').delete
#插入一个单元格，实际测试效果是B列从B2开始向下移动，B2为新添加的单元格
load_ws.range('B2').api.insert
#插入新的一列
load_ws.api.columns('B').insert
#删除一列
load_ws.api.columns('C').delete

#保存修改后的Excel
load_wb.save(file_name)
load_wb.close()

单元格宽高查询设置与合并

defcell_operation(app,file_name):
#加载已有的表格
load_wb=app.books.open(FILE_PATH_ROOT+file_name)
#获取当前活动的sheet
load_ws=load_wb.sheets.active

#合并单元格
load_ws.range('A2:A3').api.merge

#获取单元格
cell=xw.Range('B2')
#打印单元格所在的行和列
print("rowis:",cell.row,"colis:",cell.column)

#打印当前格子的高度和宽度
print("cell.width:",cell.width,"cell.height:",cell.height)

#设置当前格子的高度和宽度
cell.row_height=32
cell.column_width=64

#指定单元格的高度和宽度自适应
cell.columns.autofit()
cell.rows.autofit()

#再次打印当前格子的高度和宽度
print("cell.width:",cell.width,"cell.height:",cell.height)

#保存修改后的Excel
load_wb.save(file_name)
load_wb.close()

几个库支持情况对比

虽然前面写了这么多方法，但是遇到一个实际的问题时还是会犹豫，到底用哪种方式呢？下面做一个简单的对比，只是根据我做的实验来简单对比，如果有不准确甚至是错误的地方，欢迎大家指出来，我会尽快改正的。

情景/库	xlrd、xlwt、xlutils	openpyxl	xlwings
读取.xls	可以带有样式读取	不支持	可以读取
保存.xls	可以带有样式保存	可以保存，但是提示文件扩展名不匹配，可以看到原始数据	可以保存，但是提示文件扩展名不匹配，可以看到原始数据
读取.xlsx	可以读取，但没有样式	可以带有样式读取	可以带有样式读取
保存.xlsx	保存后打不开	可以带有样式保存	可以带有样式保存
读取.xlsm	可以读取，但没有样式和宏	可以读取，但没有宏	可以读取包含宏的表格
保存.xlsm	保存后打不开，存成.xls格式宏丢失	保存后打不开，存成.xls想格式宏丢失	存储后宏还在
增删行和列	没有直接方法	支持	支持
另存后大小	.xls文件没有变化	.xlsx文件会变小	.xls、.xlsx文件没有变化
使用建议	只操作.xls文件可以考虑	只操作.xlsx文件可以考虑，不能带有宏	一个比较好的选择，使用时感觉速度稍微有点慢

总结

Excel表格程序经过版本的更替发生了很大的变化，出现了相同内容时.xls比.xlsx格式的文件大很多的情况
基于上一点考虑，如果能使用的新版的表格，那么就放弃旧的格式的吧
还有一个神奇的情况，一个带有少量数据的.xlsx格式的表格要比一个空表格还要小，这是什么情况，暂时没弄明白怎么回事，求知道的大神告知一二

到此这篇关于Python操作Excel工作簿的示例代码(\*.xlsx)的文章就介绍到这了,更多相关Python操作Excel工作簿内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票！

Python操作Excel工作簿的示例代码(\*.xlsx)

热门推荐

随机推荐