使用Python匯出Excel圖表以及到處為圖片的方法

本篇講下如何使用純python代碼將excel 中的圖表匯出為圖片。這裡需要使用的模組有win32com、pythoncom模組。

網上經查詢有人已經寫好的模組pyxlchart，具體代碼如下：

from win32com.client import Dispatchimport osimport pythoncomclass 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.ImageTypeif __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 Pyxlchartxl = Pyxlchart()xl.WorkbookDirectory = "D:\\"xl.WorkbookFilename = "chart_column.xlsx"xl.SheetName = ""#xl.ImageFilename = "MyChart1"xl.ExportPath = "d:\\"xl.ChartName = ""xl.start_export()

由於有該表裡有多張圖表，所以上面未指定xl.ImageFilename ，使用樣本如下：

Excel vba將chart另存新檔圖片
python下使用xlswriter模組，可以輕鬆在excel 中建立圖片，不過想實現將產生的chart圖表匯出為圖片，在email 中匯入圖片的目標 。經網上查詢未找到通過python代碼將excel 中已經產生的圖片匯出為圖片的方法，不過通過變通方法，使用excel 內的vba 宏卻可以輕鬆將圖片匯出。

1、匯出單張圖片

python 建立chart圖片代碼：

#coding: utf-8import xlsxwriterimport randomdef get_num():  return random.randrange(0, 201, 2)workbook = xlsxwriter.Workbook('analyse_spider.xlsx')  #建立一個Excel檔案worksheet = workbook.add_worksheet()  #建立一個工作表對象chart = workbook.add_chart({'type': 'column'})  #建立一個圖表對象#定義資料表頭列表title = [u'業務名稱',u'星期一',u'星期二',u'星期三',u'星期四',u'星期五',u'星期六',u'星期日',u'平均流量']buname= [u'營運之路',u'就要IT',u'baidu.com',u'361way.com',u'91it.org']  #定義頻道名稱#定義5頻道一周7天流量資料列表data = []for i in range(5):  tmp = []  for j in range(7):    tmp.append(get_num())  data.append(tmp)format=workbook.add_format()  #定義format格式對象format.set_border(1)  #定義format對象儲存格邊框加粗(1像素)的格式format_title=workbook.add_format()  #定義format_title格式對象format_title.set_border(1)  #定義format_title對象儲存格邊框加粗(1像素)的格式format_title.set_bg_color('#cccccc')  #定義format_title對象儲存格背景顏色為                    #'#cccccc'的格式format_title.set_align('center')  #定義format_title對象儲存格置中對齊的格式format_title.set_bold()  #定義format_title對象儲存格內容加粗的格式format_ave=workbook.add_format()  #定義format_ave格式對象format_ave.set_border(1)  #定義format_ave對象儲存格邊框加粗(1像素)的格式format_ave.set_num_format('0.00')  #定義format_ave對象儲存格數字類別顯示格式#下面分別以行或列寫入方式將標題、業務名稱、流量資料寫入起初儲存格，同時引用不同格式對象worksheet.write_row('A1',title,format_title)worksheet.write_column('A2', buname,format)worksheet.write_row('B2', data[0],format)worksheet.write_row('B3', data[1],format)worksheet.write_row('B4', data[2],format)worksheet.write_row('B5', data[3],format)worksheet.write_row('B6', data[4],format)#定義圖表資料數列函數def chart_series(cur_row):  worksheet.write_formula('I'+cur_row, \   '=AVERAGE(B'+cur_row+':H'+cur_row+')',format_ave)  #計算（AVERAGE函數）頻                             #道周平均流量  chart.add_series({    'categories': '=Sheet1!$B$1:$H$1',  #將“星期一至星期日”作為圖表資料標籤(X軸)    'values':   '=Sheet1!$B$'+cur_row+':$H$'+cur_row,  #頻道一周所有資料作                                #為資料區域    'line':    {'color': 'black'},  #線條顏色定義為black(黑色)    'name': '=Sheet1!$A$'+cur_row,  #引用業務名稱為圖例項  })for row in range(2, 7):  #資料域以第2～6行進行圖表資料數列函數調用  chart_series(str(row))chart.set_size({'width': 577, 'height': 287})  #設定圖表大小chart.set_title ({'name': u'爬蟲分析'})  #設定圖表（上方）大標題chart.set_y_axis({'name': 'count'})  #設定y軸（左側）小標題worksheet.insert_chart('A8', chart)  #在A8儲存格插入圖表workbook.close()  #關閉Excel文檔

由於這裡只有一張圖片，通過vba 代碼很容易產生圖片 。方法為，開啟該excel 圖表，通過alt + F11 快速鍵開啟宏編輯介面；開啟VB編輯器的立即視窗：”視圖“-”立即視窗“，或者使用快速鍵"Ctrl + G" ，接著輸入如下代碼

activesheet.ChartObjects(1).Chart.Export "C:\chart.png"

按 " Enter " 鍵後，會在C盤產生上面的產生的chart圖表。

二、匯出多張圖表

python代碼如下：

#coding: utf-8import xlsxwriterworkbook = xlsxwriter.Workbook('chart_column.xlsx')worksheet = workbook.add_worksheet()bold = workbook.add_format({'bold': 1})# 這是個資料table的列headings = ['Number', 'Batch 1', 'Batch 2']data = [  [2, 3, 4, 5, 6, 7],  [10, 40, 50, 20, 10, 50],  [30, 60, 70, 50, 40, 30],]worksheet.write_row('A1', headings, bold)worksheet.write_column('A2', data[0])worksheet.write_column('B2', data[1])worksheet.write_column('C2', data[2])#############################################建立一個圖表，類型是columnchart1 = workbook.add_chart({'type': 'column'})# 配置series,這個和前面wordsheet是有關係的。chart1.add_series({  'name':    '=Sheet1!$B$1',  'categories': '=Sheet1!$A$2:$A$7',  'values':   '=Sheet1!$B$2:$B$7',})# Configure a second series. Note use of alternative syntax to define ranges.chart1.add_series({  'name':    ['Sheet1', 0, 2],  'categories': ['Sheet1', 1, 0, 6, 0],  'values':   ['Sheet1', 1, 2, 6, 2],})# Add a chart title and some axis labels.chart1.set_title ({'name': 'Results of sample analysis'})chart1.set_x_axis({'name': 'Test number'})chart1.set_y_axis({'name': 'Sample length (mm)'})# Set an Excel chart style.chart1.set_style(11)# Insert the chart into the worksheet (with an offset).worksheet.insert_chart('D2', chart1, {'x_offset': 25, 'y_offset': 10})######################################################################### Create a stacked chart sub-type.#chart2 = workbook.add_chart({'type': 'column', 'subtype': 'stacked'})# Configure the first series.chart2.add_series({  'name':    '=Sheet1!$B$1',  'categories': '=Sheet1!$A$2:$A$7',  'values':   '=Sheet1!$B$2:$B$7',})# Configure second series.chart2.add_series({  'name':    '=Sheet1!$C$1',  'categories': '=Sheet1!$A$2:$A$7',  'values':   '=Sheet1!$C$2:$C$7',})# Add a chart title and some axis labels.chart2.set_title ({'name': 'Stacked Chart'})chart2.set_x_axis({'name': 'Test number'})chart2.set_y_axis({'name': 'Sample length (mm)'})# Set an Excel chart style.chart2.set_style(12)# Insert the chart into the worksheet (with an offset).worksheet.insert_chart('D18', chart2, {'x_offset': 25, 'y_offset': 10})######################################################################### Create a percentage stacked chart sub-type.#chart3 = workbook.add_chart({'type': 'column', 'subtype': 'percent_stacked'})# Configure the first series.chart3.add_series({  'name':    '=Sheet1!$B$1',  'categories': '=Sheet1!$A$2:$A$7',  'values':   '=Sheet1!$B$2:$B$7',})# Configure second series.chart3.add_series({  'name':    '=Sheet1!$C$1',  'categories': '=Sheet1!$A$2:$A$7',  'values':   '=Sheet1!$C$2:$C$7',})# Add a chart title and some axis labels.chart3.set_title ({'name': 'Percent Stacked Chart'})chart3.set_x_axis({'name': 'Test number'})chart3.set_y_axis({'name': 'Sample length (mm)'})# Set an Excel chart style.chart3.set_style(13)# Insert the chart into the worksheet (with an offset).worksheet.insert_chart('D34', chart3, {'x_offset': 25, 'y_offset': 10})workbook.close()

同一資料來源上面建立了三種類型的圖 ，由於有三張圖，上面的匯出一張圖的方法肯定是不行了，這裡開啟宏，建立如下宏內容：

Sub exportimg()Dim XlsChart As ChartObjectFor Each XlsChart In Worksheets("Sheet1").ChartObjects  XlsChart.Chart.Export Filename:="C:\" & XlsChart.Name & ".jpg", FilterName:="JPG"NextEnd Sub

該樣本這裡就不再，具體可以自行運行。

