首页 > > 网络编程 > 其它 >

Python导出Excel图表

2018-12-19 01:46:08来源：博客园阅读 ()

Python自动化办公的过程，部分涉及到导出Excel图表；本篇主要讲下使用python代码将excel中的图表导出为图片的开发过程；

Python 版本:

C:\Users>python
Python 3.6.0 (v3.6.0:41df79263a11, Dec 23 2016, 08:06:12) [MSC v.1900 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>>

Office版本：

数据准备

在导出图表前，先准备仿真数据并绘制图表，这里模仿运维工作的业务指标数据测试：

图表已经在Excel中绘制：

python导出Excel图表类

前期准备就绪，网上已有类似的导出Excel图表类，但是在后面的使用中发现问题，即关键函数已在下面代码中标红：

 1 import win32com,os
 2 from win32com.client import Dispatch
 3 import pythoncom
 4 '''
 5 启用win32模块导出excel的图表，图表需要打开加载缓存才能导出
 6 '''
 7 class Pyxlchart(object):
 8     """
 9     This class exports charts in an Excel Spreadsheet to the FileSystem
10     win32com libraries are required.
11     """
12     def __init__(self):
13         '''
14         初始化图表
15         '''
16         pythoncom.CoInitialize()    #多线程使用win32com调用com组件的时初始化
17         self.WorkbookDirectory = ''  #excel文件所在目录
18         self.WorkbookFilename = ''  #文件名称
19         self.GetAllWorkbooks = False  #获取所有book
20         self.SheetName = ''     #sheet名称
21         self.ChartName = ''     #导出单张图表时，指定图表名称
22         self.GetAllWorkbookCharts = False
23         self.GetAllWorksheetCharts = True
24         self.ExportPath = ''    #导出的文件路径
25         self.ImageFilename = '' #导出的图片名称
26         self.ReplaceWhiteSpaceChar = '_'
27         self.ImageType = 'jpg'  #定义导出的图片类型
28     def __del__(self):
29         pass
30     def start_export(self,_visible=False):
31         if self.WorkbookDirectory == '':
32             return "WorkbookDirectory not set"
33         else:
34             self._export(_visible)
35     def _export(self,_visible=False):
36         excel = Dispatch("excel.application")
37         #启用独立的进程调用excel，Dispatch会强行关闭正在打开的excel
38         #可以使用 DispatchEx为单独调用线程，不影响已经打开的excel
39         excel = Dispatch("excel.application")
40         excel.Visible = False
41         wb = excel.Workbooks.Open(os.path.join(self.WorkbookDirectory ,self.WorkbookFilename))
42         self._get_Charts_In_Worksheet(wb,self.SheetName,self.ChartName)
43         wb.Close(False)
44         excel.Quit()
45 
46     def _get_Charts_In_Worksheet(self,wb,worksheet = "", chartname = ""):
47         if worksheet != "" and chartname != "":
48             sht = self._change_sheet(wb,worksheet)
49             cht = sht.ChartObjects(chartname)
50 
51             self._save_chart(cht)
52             return
53         if worksheet == "":  #导出表格中所有图表
54             for sht in wb.Worksheets:
55                 for cht in sht.ChartObjects():
56                     if chartname == "":
57                         self._save_chart(cht)
58                     else:
59                         if chartname == cht.Name:
60                             self._save_chart(cht)
61         else:   #导出指定sheet中的图标
62             sht = wb.Worksheets(worksheet)
63             for cht in sht.ChartObjects():
64                 if chartname == "":
65                     self._save_chart(cht)
66                 else:
67                     if chartname == cht.Name:
68                         self._save_chart(cht)
69     def _change_sheet(self,wb,worksheet):
70         try:
71             return wb.Worksheets(worksheet)
72         except:
73             raise NameError('Unable to Select Sheet: ' + worksheet + ' in Workbook: ' + wb.Name)
74     def _save_chart(self,chartObject):
75         '''
76         保存图标到指定路径
77         :param chartObject: 图表名称
78         :return:
79         '''
80         imagename = self._get_filename(chartObject.Name)
81         savepath = os.path.join(self.ExportPath,imagename)
82         #print(savepath)
83 
84         chartObject.Chart.Export(savepath,self.ImageType)
85 
86     def _get_filename(self,chartname):
87         """
88         获取导出图表的文件名称
89         Replaces white space in self.WorkbookFileName with the value given in self.ReplaceWhiteSpaceChar
90         If self.ReplaceWhiteSpaceChar is an empty string then self.WorkBookFileName is left as is
91         """
92         if self.ReplaceWhiteSpaceChar != '':
93             chartname.replace(' ',self.ReplaceWhiteSpaceChar)
94         if self.ImageFilename == '': #未指定导出的图片名称，则与图表名称一致
95             return chartname + "." + self.ImageType
96         else: #指定了导出图片的命名格式
97             return self.ImageFilename + "_" + chartname + "." + self.ImageType

调用代码：

docPath = r'E:\temp'
if __name__=='__main__':
    Ect = Pyxlchart()
    Ect.WorkbookDirectory = docPath
    Ect.WorkbookFilename = 'Test.xlsx'
    Ect.SheetName = "图表"   #图表所在的sheet名称
    Ect.ExportPath = r'E:\temp\Export_Img'  #图片的导出路径
    Ect.start_export()
　　 print("All Chart is Export!")

执行成功，接下来到上面设置的导出路径查看导出的图片：

 E:\temp\Export_Img 的目录
2018-12-18  11:20                 0 Chart 1.jpg
2018-12-18  11:20                 0 Chart 2.jpg
2018-12-18  11:20            39,583 Chart 3.jpg
2018-12-18  11:20            38,950 Chart 4.jpg
               4 个文件         78,533 字节
E:\temp\Export_Img>

　　从文件查看中看到，图表文件已经成功导出；

图表导出的问题

　　但是，图表的导出并未能完全成功，从以上文件信息中看到导出的图片存在0字节的文件；点击查看图片可发现提示为空文件

　　具体原因分析:

　　经过本人多次的测试和探索发现：有效的图片为Excel的图表区域显示页面，通俗一点的说，即打开excel的图表所在sheet，当前屏幕显示了哪些图表，导出的图片就正常；在我个人认为可能是Office或Python对Excel的某种缓存功能，实际的缓存范围大概在当前显示页面的150%左右，超出区域的图表在未加载的情况下，导出成了0字节错误文件；

　　即使发现了这个BUG，网上搜索也未能找到有效的类似"关闭加载缓存"的技术贴，那么还得根据导出图表的基础逻辑解决；

　　继续测试，在Excel的图表中缩放显示全部图片测试，按照测试数据图表范围，缩放25%可显示全部图表（>_>或者把所有图表拖动到一个页面显示）:

 E:\temp\Export_Img 的目录
2018-12-18  11:20    <DIR>          .
2018-12-18  11:20    <DIR>          ..
2018-12-18  12:11             5,347 Chart 1.jpg
2018-12-18  12:11             5,595 Chart 2.jpg
2018-12-18  12:11             5,764 Chart 3.jpg
2018-12-18  12:11             5,888 Chart 4.jpg
               4 个文件         22,594 字节

　　如上述文件查看所示，当图表所在的sheet页面显示了所有图表时，所有图表的图片都成功的导出；

　　但是，缩放导出的图片是根据Excel的图标实际显示大小来导出的，所以缩放模式下，导出的图片大小、清晰度都不能正常使用；

解决方案

　　综上所述，已知Python根据Excel的图标实际显示来导出，那么，可以让Python的导出代码执行前加载所有正常图表，在之前的python导出Excel图表的类中，使用异步方式调用excel.application，即文档以后台方式导出图表；

　　如果需要完成Excel的所有图表加载，即必须手动或代码干预导出过程，在类中已经有代码可以设置文档可见；

excel.Visible = True    #设置导出Excel是否可见，当值为True时，可见打开的Excel

　　修改原代码:

def _export(self):
        excel = Dispatch("excel.application")
        # 启用独立的进程调用excel，Dispatch会强行关闭正在打开的excel
        # 可以使用 DispatchEx为单独调用线程，不影响已经打开的excel
        excel.Visible = True
        wb = excel.Workbooks.Open(os.path.join(self.WorkbookDirectory, self.WorkbookFilename))  
        #如需干预Excel图表导出，需要在文档打开到开始导出之间，加载完所有图表
        self._get_Charts_In_Worksheet(wb, self.SheetName, self.ChartName)
        wb.Close(False)
        excel.Quit()

　　解决方式显而易见，过程不多做描述；个人是使用win32api、win32con模块模拟键盘操作加载所有图表，因无法确认图表sheet所在的位置，需提前将图表所在的sheet设置在Excel文档的最后；或者可根据实际情况，由代码完成所有sheet的加载操作（比如多按几下pagadown翻页，屏幕识别判定内容范围等....）

　　完整代码如下：

  1 import os,time,sys
  2 import win32api
  3 import win32con
  4 from win32com.client import Dispatch
  5 import pythoncom
  6 '''
  7 启用win32模块导出excel的图表，图表需要打开加载缓存才能导出
  8 '''
  9 class Pyxlchart(object):
 10     """
 11     This class exports charts in an Excel Spreadsheet to the FileSystem
 12     win32com libraries are required.
 13     """
 14     def __init__(self):
 15         '''
 16         初始化图表
 17         '''
 18         pythoncom.CoInitialize()
 19         self.WorkbookDirectory = ''  #excel文件所在目录
 20         self.WorkbookFilename = ''  #文件名称
 21         self.GetAllWorkbooks = False  #获取所有book
 22         self.SheetName = ''     #sheet名称
 23         self.ChartName = ''     #导出单张图表时，指定图表名称
 24         self.GetAllWorkbookCharts = False
 25         self.GetAllWorksheetCharts = True
 26         self.ExportPath = ''    #导出的文件路径
 27         self.ImageFilename = '' #导出的图片名称
 28         self.ReplaceWhiteSpaceChar = '_'
 29         self.ImageType = 'jpg'
 30     def __del__(self):
 31         pass
 32     def start_export(self,_visible=True):
 33         if self.WorkbookDirectory == '':
 34             return "WorkbookDirectory not set"
 35         else:
 36             self._export(_visible)
 37     def _export(self,_visible):
 38         """
 39         Exports Charts as determined by the settings in class variabels.
 40         """
 41         excel = Dispatch("excel.application")
 42         #启用独立的进程调用excel，Dispatch容易冲突【会强行关闭正在打开的excel】
 43         #使用 DispatchEx为单独调用线程，不影响已经打开的excel
 44 
 45         excel.Visible = _visible
 46         wb = excel.Workbooks.Open(os.path.join(self.WorkbookDirectory ,self.WorkbookFilename))
 47 
 48         time.sleep(5)  # 等5秒等待进程打开加载文档
 49         # 使用打开excel的方式，则模拟键盘事件触发加载所有图表
 50         if excel.Visible == 1 or excel.Visible == True:
 51             win32api.keybd_event(17, 0, 0, 0)  # 键盘按下  ctrl键
 52             time.sleep(1)
 53             for k in range(4):
 54                 win32api.keybd_event(34, 0, 0, 0)  # ctrl+pageDown的组合会跳转sheet，20次跳转可以到最后的图表
 55             win32api.keybd_event(36, 0, 0, 0)  # 键盘按下  home键，和上个按键形成组合键，回到第一行开头
 56             win32api.keybd_event(17, 0, win32con.KEYEVENTF_KEYUP, 0)
 57             win32api.keybd_event(36, 0, win32con.KEYEVENTF_KEYUP, 0)
 58 
 59             # 当表格过大时，只能保存到页面显示的图标，故此需要先循环翻页将所有图片加载
 60             for i in range(15):  # 翻页加载所有图表
 61                 win32api.keybd_event(34, 0, 0, 0)  # 每次读取之后翻页
 62                 win32api.keybd_event(34, 0, win32con.KEYEVENTF_KEYUP, 0)
 63                 time.sleep(0.5)
 64 
 65         #图片加载完成，好了，导出图片继续进行
 66         self._get_Charts_In_Worksheet(wb,self.SheetName,self.ChartName)
 67         wb.Close(True)
 68         excel.Quit()
 69 
 70 
 71     def _get_Charts_In_Worksheet(self,wb,worksheet = "", chartname = ""):
 72         if worksheet != "" and chartname != "":
 73             sht = self._change_sheet(wb,worksheet)
 74             cht = sht.ChartObjects(chartname)
 75 
 76             self._save_chart(cht)
 77             return
 78         if worksheet == "":  #导出表格中所有图表
 79             for sht in wb.Worksheets:
 80                 for cht in sht.ChartObjects():
 81                     if chartname == "":
 82                         self._save_chart(cht)
 83                     else:
 84                         if chartname == cht.Name:
 85                             self._save_chart(cht)
 86         else:   #导出指定sheet中的图标
 87             sht = wb.Worksheets(worksheet)
 88             for cht in sht.ChartObjects():
 89                 if chartname == "":
 90                     self._save_chart(cht)
 91                 else:
 92                     if chartname == cht.Name:
 93                         self._save_chart(cht)
 94     def _change_sheet(self,wb,worksheet):
 95         try:
 96             return wb.Worksheets(worksheet)
 97         except:
 98             raise NameError('Unable to Select Sheet: ' + worksheet + ' in Workbook: ' + wb.Name)
 99     def _save_chart(self,chartObject):
100         '''
101         保存图标到指定路径
102         :param chartObject: 图表名称
103         :return:
104         '''
105         imagename = self._get_filename(chartObject.Name)
106         savepath = os.path.join(self.ExportPath,imagename)
107         #print(savepath)
108 
109         chartObject.Chart.Export(savepath,self.ImageType)
110 
111     def _get_filename(self,chartname):
112         """
113         获取导出图表的文件名称
114         Replaces white space in self.WorkbookFileName with the value given in self.ReplaceWhiteSpaceChar
115         If self.ReplaceWhiteSpaceChar is an empty string then self.WorkBookFileName is left as is
116         """
117         if self.ReplaceWhiteSpaceChar != '':
118             chartname.replace(' ',self.ReplaceWhiteSpaceChar)
119         if self.ImageFilename == '': #未指定导出的图片名称，则与图表名称一致
120             return chartname + "." + self.ImageType
121         else: #指定了导出图片的命名格式
122             return self.ImageFilename + "_" + chartname + "." + self.ImageType
123 
124 
125 docPath = r'E:\temp'
126 if __name__=='__main__':
127     Ect = Pyxlchart()
128     Ect.WorkbookDirectory = docPath
129     Ect.WorkbookFilename = 'Test.xlsx'
130     Ect.SheetName = "图表"   #图表所在的sheet名称
131     Ect.ExportPath = r'E:\temp\Export_Img'  #图片的导出路径
132     Ect.start_export()

View Code

　　执行，再次查看执行结果；

 E:\temp\Export_Img 的目录
2018-12-18  11:20    <DIR>          .
2018-12-18  11:20    <DIR>          ..
2018-12-18  12:39            40,649 Chart 1.jpg
2018-12-18  12:39            41,048 Chart 2.jpg
2018-12-18  12:39            45,048 Chart 3.jpg
2018-12-18  12:39            44,672 Chart 4.jpg
               4 个文件        171,417 字节