Python操作Excel

  • 发布时间:2017年4月17日 11:21
  • 作者:杨仕航
  • 分类标签: Python
  • 阅读(12426)
  • 评论(0)

Python操作Excel,读写xls/xlsx文件已经有不少优秀的库。

例如xlrd, xlsxwriter,还有微软自己开发的pyvot。

假如,你用的是windows系统,而且安装了Office。最全面的操作方式(当然,也是最费资源的操作方式)是Python创建Excel对象,直接操作Excel。该方法可以完美移植Excel vba代码。也就是说,我们可以原生态的方式处理该类问题。

如果你只是简单读写单元格的值,建议不要用这种方式,因为相对耗资源。

如果你要处理单元格格式、条件格式、图表等等复杂的东西,推荐该方式,稳定且方便。


使用该方式有前提:需要安装win32com

win32com组件可以在https://sourceforge.net/projects/pywin32/files/pywin32下载,根据系统和Python版本选择对应的版本。下载安装完成之后,继续往下看内容。


1、打开Excel文件

单独把这块拿出来讲,因为要注意不少细节。

首先,Excel文件要使用绝对路径。若不确定是绝对路径还是相对路径,可以用如下方式:

#coding:utf-8
from win32com.client import Dispatch
import os

def open_xlsx(xlsx_path):
    #把相对路径转成绝对路径
    xlsx_fullname = os.path.abspath(xlsx_path)
    
    #创建Excel对象
    excel = Dispatch('excel.application')
    
    #打开Excel文件
    workbook = excel.workbooks.Open(xlsx_fullname)

不管三七二十一,参数是绝对路径也好,还是相对路径也好,直接将其转成绝对路径。

再利用Dispatch创建Excel对象,打开该Excel文件。


接着,还要注意Dispatch细节。假如你电脑已经打开了一个Excel进程。Dispatch会直接使用该进程。看上去没什么问题,甚至可以节省一些资源开支。但毕竟我们使用代码操作Excel文件,尽量不影响手动打开的文件或进程,避免操作干扰(包括程序本身也会被干扰)。这里建议使用另外一个类创建Excel对象:

#coding:utf-8
from win32com.client import DispatchEx
import os

def open_xlsx(xlsx_path):
    #把相对路径转成绝对路径
    xlsx_fullname = os.path.abspath(xlsx_path)
    
    #创建Excel对象
    excel = DispatchEx('excel.application')
    
    #打开Excel文件
    workbook = excel.workbooks.Open(xlsx_fullname)

DispatchEx类可以创建新的Excel进程,不与其他进程造成干扰。

另外,最好还要加上之前提到的win32com注意事项

#coding:utf-8
from win32com.client import DispatchEx
import os

def open_xlsx(xlsx_path):
    #把相对路径转成绝对路径
    xlsx_fullname = os.path.abspath(xlsx_path)
    
    #创建Excel对象
    pythoncom.CoInitialize()
    excel = DispatchEx('excel.application')
    
    #打开Excel文件
    workbook = excel.workbooks.Open(xlsx_fullname)
    
    pythoncom.CoUninitialize()

pythoncom那两句是保护DispatchEx创建的进程。


当然这个Dispatch还是有作用。例如你测试代码出错了,程序停止运行,而Excel进程还在。这次可以看看到底Excel中写入了什么,可以执行如下代码,把Excel进程的文件显示出来:

from win32com.client import Dispatch

excel = Dispatch('excel.application')
excel.visible = 1 #显示Excel

这个excel变量对应Excel的Application对象。相关属性可以在Excel vba查询。一般我们代码操作会将屏幕刷新的关闭,完整的打开Excel文件代码如下:

#coding:utf-8
from win32com.client import DispatchEx
import os

def open_xlsx(xlsx_path):
    #把相对路径转成绝对路径
    xlsx_fullname = os.path.abspath(xlsx_path)
    
    #创建Excel对象
    pythoncom.CoInitialize()
    excel = DispatchEx('excel.application')
    excel.visible = 0        #不显示Excel
    excel.DisplayAlerts = 0  #关闭系统警告
    excel.ScreenUpdating = 0 #关闭屏幕刷新
    
    #打开Excel文件
    workbook = excel.workbooks.Open(xlsx_fullname)
    
    pythoncom.CoUninitialize()


2、关闭Excel文件,退出Excel进程

你是不是因为测试代码打开了不少Excel进程了。可以先手动打开任务管理器,结束Excel.exe。

关闭Excel文件很简单,就是涉及到保不保存文件。

workbook对象有Close方法,第1个参数默认为False,关闭不保存。该方法使用可以参考Excel vba。

Excel退出进程,使用Application对象的Quit方法即可。

#coding:utf-8
from win32com.client import DispatchEx
import os

def open_xlsx(xlsx_path):
    #把相对路径转成绝对路径
    xlsx_fullname = os.path.abspath(xlsx_path)
    
    #创建Excel对象
    pythoncom.CoInitialize()
    excel = DispatchEx('excel.application')
    excel.visible = 0        #不显示Excel
    excel.DisplayAlerts = 0  #关闭系统警告
    excel.ScreenUpdating = 0 #关闭屏幕刷新
    
    #打开Excel文件
    workbook = excel.workbooks.Open(xlsx_fullname)
    
    #其他操作代码
    #...
    
    #关闭Excel文件,不保存(若保存,使用True即可)
    workbook.Close(False)
    
    #退出Excel
    excel.Quit()    
    pythoncom.CoUninitialize()

这里不得不提DisplayAlerts功臣,若没有关闭系统警告。只要你文件修改过,或者有多个workbook工作簿,直接Quit就会弹窗提示。一旦弹窗提示,万事皆休。

这里最好的做法是用Save方法或SaveAs方法保存你想保存的文件,再遍历所有工作簿逐一关闭。

#save_xlsx_path是保存的路径
workbook.SaveAs(os.path.abspath(save_xlsx_path))

#遍历退出
if excel:
    for wrk in excel.workbooks:
        wrk.Close(False)
    excel.Quit()
pythoncom.CoUninitialize()

遍历退出这段代码,建议写在出错处理的finally部分。


3、读写数据

当然,我们操作Excel。不是只是打开和关闭,读写数据才是本体。

若你有Excel vba基础,该操作很简单。单元格是Range对象,单元格的内容可以通过Range的Value属性获取。

例如,获取Excel第1个工作表,一共有多少行。并将A列的数据读取出来。

#假如已经打开了一个workbook
#workbook = ...

#激活第1个工作表
worksheet = workbook.worksheets[0]
worksheet.Activate()

#获取当前工作表总行数
row_max = excel.Range("A56636").End(-4162).Row

#直接A列获取数据(该方法可以获取得到数据列表)
values = excel.Range("A1:A%s" % row_max).Value

#也可以遍历
for row in range(1, row_max+1):
    print(worksheet.Cells(row, 1).Value)

若直接获取多个单元格的数据,结果是二维列表。可以使用flatten方法将二维列表转成一维的:

from compiler.ast import flatten
flatten(worksheet.Range('A1:A5').value)


同样,写入数据可以支持直接用list向多个单元格写入数据。

不过要注意行列方向。例如:

worksheet.Range('A7:A10').Value = [[1,],[2,],[3,],[4,]]
worksheet.Range('A7:D7').Value = [1,2,3,4]

可得到如下结果:

20170417/20170417111540536.png

也可以写多行多列:

worksheet.Range('A7:D10').Value = [
        [11, 12, 13, 14],
        [21, 22, 23, 24],
        [31, 32, 33, 34],
        [41, 42, 43, 44]
    ]

得到如下结果:

20170417/20170417111747482.png

我也记不全怎么对应,所以我写成如上代码的形式。单元格位置和我代码写的一一对应。

当然,也可以单个单元格写入:

worksheet.Range('A7').Value = '测试'


好了,使用win32com方式操作Excel差不多这些内容。该注意的东西也讲了。

其他较为复杂的操作方式,可以先在Excel录制宏得到vba代码,再移植到Python中。创建图表、数据透视表什么都不在话下。

上一篇:Redis在CentOS和Windows安装过程

下一篇:VSTO保存配置的方法和建议

评论列表

智慧如你,不想发表一下意见吗?

新的评论

清空