python导出excel charts图表的例子

在Excel vba将chart另存为图片篇中讲了如何通过vba宏将excel 中的chart图表导出为的图片，本篇讲下如何使用纯python代码将excel 中的图表导出为图片。这里需要使用的模块有win32com、pythoncom模块。

网上经查询有人已经写好的模块pyxlchart，具体代码如下：

from win32com.client import Dispatch
import os
import pythoncom
class Pyxlchart(object):
    """
    This class exports charts in an Excel Spreadsheet to the FileSystem
    win32com libraries are required.
    """
    def __init__(self):
        pythoncom.CoInitialize()
        self.WorkbookDirectory = ''
        self.WorkbookFilename = ''
        self.GetAllWorkbooks = False
        self.SheetName = ''
        self.ChartName = ''
        self.GetAllWorkbookCharts = False
        self.GetAllWorksheetCharts = False
        self.ExportPath = ''
        self.ImageFilename = ''
        self.ReplaceWhiteSpaceChar = '_'
        self.ImageType = 'jpg'
    def __del__(self):
        pass
    def start_export(self):
        if self.WorkbookDirectory == '':
            return "WorkbookDirectory not set"
        else:
            self._export()
    def _export(self):
        """
        Exports Charts as determined by the settings in class variabels.
        """
        excel = Dispatch("excel.application")
        excel.Visible = False
        wb = excel.Workbooks.Open(os.path.join(self.WorkbookDirectory ,self.WorkbookFilename))
        self._get_Charts_In_Worksheet(wb,self.SheetName,self.ChartName)
        wb.Close(False)
        excel.Quit()
    def _get_Charts_In_Worksheet(self,wb,worksheet = "", chartname = ""):
        if worksheet != "" and chartname != "":
            sht = self._change_sheet(wb,worksheet)
            cht = sht.ChartObjects(chartname)
            self._save_chart(cht)
            return
        if worksheet == "":
            for sht in wb.Worksheets:
                for cht in sht.ChartObjects():
                    if chartname == "":
                        self._save_chart(cht)
                    else:
                        if chartname == cht.Name:
                            self._save_chart(cht)
        else:
            sht = wb.Worksheets(worksheet)
            for cht in sht.ChartObjects():
                if chartname == "":
                    self._save_chart(cht)
                else:
                    if chartname == cht.Name:
                        self._save_chart(cht)
    def _change_sheet(self,wb,worksheet):
        try:
            return wb.Worksheets(worksheet)
        except:
            raise NameError('Unable to Select Sheet: ' + worksheet + ' in Workbook: ' + wb.Name)
    def _save_chart(self,chartObject):
        imagename = self._get_filename(chartObject.Name)
        savepath = os.path.join(self.ExportPath,imagename)
        print savepath
        chartObject.Chart.Export(savepath,self.ImageType)
    def _get_filename(self,chartname):
        """
        Replaces white space in self.WorkbookFileName with the value given in self.ReplaceWhiteSpaceChar
        If self.ReplaceWhiteSpaceChar is an empty string then self.WorkBookFileName is left as is
        """
        if self.ImageFilename == '':
            self.ImageFilename == chartname
        if self.ReplaceWhiteSpaceChar != '':
            chartname.replace(' ',self.ReplaceWhiteSpaceChar)
        if self.ImageFilename != "":
            return self.ImageFilename + "_" + chartname + "." + self.ImageType
        else:
            return chartname + '.' + self.ImageType
if __name__ == "__main__":
    xl = Pyxlchart()
    xl.WorkbookDirectory = "\\\\maawtns01\\discipline\\procurement\\MATERIEL\\Raw Material\\Data Management\\Hawk"
    xl.WorkbookFilename = "Hawk Workability KPI.xlsm"
    xl.SheetName = ""
    xl.ImageFilename = "MyChart1"
    xl.ExportPath = "d:\\pycharts"
    xl.ChartName = ""
    xl.start_export()
    print "This file does not currently allow direct access"
    print "Please import PyXLChart and run start_export()"
   
这里还使用Excel vba将chart另存为图片篇中创建的chart_column.xlsx表，使用上面的模块的方法如下：

from pyxlchart import Pyxlchart
xl = Pyxlchart()
xl.WorkbookDirectory = "D:\\"
xl.WorkbookFilename = "chart_column.xlsx"
xl.SheetName = ""
#xl.ImageFilename = "MyChart1"
xl.ExportPath = "d:\\"
xl.ChartName = ""
xl.start_export()

由于有该表里有多张图表，所以上面未指定xl.ImageFilename ，使用示例如下：