How to Use Python to export Excel Charts and images everywhere

This article mainly introduces how to use Python to export Excel Charts and picture everywhere. It is very convenient for Python-related modules to operate office in Windows, for more information about how to use python-only code to export charts in excel as images, see this document. Here, we need to use the win32com and pythoncom modules.

The Python chart has been written on the Internet. The Code is as follows:

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()"

Here, we also use Excel vbato create a chartsung table named chart_column.xlsx in the image document. The method of using the above module is as follows:

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()

Because there are multiple charts in this table, xl. ImageFilename is not specified above. The following is an example:

Save chart as image in Excel vba
Using the xlswriter module in python, you can easily create images in excel. However, you want to export the generated chart as an image and import the image in email. After online query, you cannot find a way To export an image generated in excel as an image using the python code. However, by using a work und, you can use the vba macro in excel to easily export the image.

1. Export a single image

Python:

# Coding: utf-8import xlsxwriterimport randomdef get_num (): return random. randrange (0,201, 2) workbook = xlsxwriter.workbook('analyse_spider.xlsx') # create an Excel file worksheet = workbook. add_worksheet () # create a worksheet object chart = workbook. add_chart ({'type': 'column'}) # create a chart object # define the table header list title = [u'business name', u'monday ', u 'tuesday ', u 'wedday', u 'thursday', u 'Friday', u 'sunday ', u'average traffic'] buname = [u'o & M path', U' requires IT ', u'baidu. com ', U' 361way. com ', u'91 It.org '] # define channel name # define 5 channels traffic data list for seven days a week data = [] for I in range (5): tmp = [] for j in range (7): tmp. append (get_num () data. append (tmp) format = workbook. add_format () # define format object format. set_border (1) # defines the format format_title = workbook. add_format () # define format_title format object format_title.set_border (1) # define format_title object cell border bold (1 pixel) format format_title.set_bg_color ('# cccccccccc ') # define the background color of the format_title object cell to # '# cccccc 'Format_title.set_align ('center') # defines the format_title object cell center-aligned format format_title.set_bold () # defines the format_title object cell content bold format format_ave = workbook. add_format () # defines the format_ave format object format_ave.set_border (1) # defines the format_ave.set_num_format ('0. 00') # define the format_ave object cell digital category display format # Write the title, service name, and traffic data into the initial cell by row or column respectively, and reference the worksheet object of different formats. write_row ('a1', title, format_title) worksheet. write_column ('a2 ', bun Ame, 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) # define the function def chart_series (cur_row): worksheet. write_formula ('I' + cur_row, \ '= AVERAGE (B' + cur_row + ': H' + cur_row +') ', format_ave) # calculation (AVERAGE function) frequency # weekly average traffic chart. add_series ({'category ': '= Sheet1! $ B $1: $ H $ 1', # Use "Monday to Sunday" as the chart data label (X axis) 'values': '= Sheet1! $ B $ '+ cur_row +': $ H $ '+ cur_row, # All data in the channel for one week # For the data region 'line': {'color': 'black '}, # The line color is defined as black (black) 'name': '= Sheet1! $ A $ '+ cur_row, # reference the business name as A legend}) for row in range (2, 7): # data fields in 2nd ~ Chart_series (str (row) chart is called for six rows of chart data series functions. set_size ({'width': 577, 'height': 287}) # Set the chart size chart. set_title ({'name': u'crawler analytics '}) # Set the title chart (top. set_y_axis ({'name': 'Count'}) # Set the Y axis (left side) Title worksheet. insert_chart ('a8 ', chart) # insert a chart workbook into the A8 cell. close () # close the Excel document

Since there is only one image here, it is easy to generate images through vba code. Method: Open the excel chart and press the alt + F11 shortcut key to open the macro editing interface. Open the prompt window of the VB Editor: "View"-"immediate window", or press the shortcut key "Ctrl + G" and enter the following code:

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

After you press Enter, the chart generated above is generated on drive C.

Ii. Export multiple charts

The python code is as follows:

# Coding: utf-8import xlsxwriterworkbook = xlsxwriter.Workbook('chart_column.xlsx ') worksheet = workbook. add_worksheet () bold = workbook. add_format ({'bold ': 1}) # 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]) ######################################## ##### create a chart, the type is columnchart1 = workbook. add_chart ({'type': 'column'}) # configure series, which is related to the previous wordsheet. Chart1.add _ series ({'name': '= Sheet1! $ B $ 1', 'category': '= 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], 'category': ['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 analytics'}) chart1.set _ x_axis ({'name': 'test number '}) chart1.set _ y_axis ({'name': 'sample length (mm) '}) # S Et 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', 'category': '= Sheet1! $ A $2: $ A $ 7', 'values': '= Sheet1! $ B $2: $ B $ 7',}) # Configure second series. chart2.add _ series ({'name': '= Sheet1! $ C $1 ', 'category':' = 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', 'category': '= Sheet1! $ A $2: $ A $ 7', 'values': '= Sheet1! $ B $2: $ B $ 7',}) # Configure second series. chart3.add _ series ({'name': '= Sheet1! $ C $1 ', 'category':' = 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 ()

Three types of graphs are created on the same data source. As there are three graphs, the method for exporting a graph above is definitely not good. Open a macro here and create the following macro content:

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

This example is no longer available here. You can run it on your own.