Use Python to export an Excel chart and how to export it as a picture

This article mainly describes the use of Python to export Excel chart and export as a picture method, Python related modules in Windows Operation Office is very convenient, the need for friends can refer to the following

This article tells you how to use pure Python code to export a chart from Excel as a picture. The modules to be used here are win32com, pythoncom modules.

Online through the query has been written by the module Pyxlchart, the specific 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 is 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 Cha  RTS 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! = "": sh t = 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 a empty string then self. Workbookfilename is left as was "" "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 a nd run Start_export () "

Here you also use Excel VBA to save the chart as the Chart_column.xlsx table created in the picture chapter, using the above module 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 ()

XL is not specified above because there is more than one chart in the table. Imagefilename, use the following example:

Excel VBA saves the chart as a picture
Using the Xlswriter module under Python, you can easily create a picture in Excel, but you want to export the resulting chart chart to a picture and import the image's target in an email. The online query did not find a way to export a picture of a generated image in Excel through Python code, but using a VBA macro within Excel makes it easy to export the picture.

1. Export a single picture

Python creates a chart image code:

#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 '}) #创建一个图表对象 # define data Header list title = [u ' Business name ', U ' Monday ', U ' Tuesday ', U ' Wednesday ', U ' Thursday ', U ' Friday ', U ' Saturday ', U ' Sunday ', U ' average flow ']buname= [u ' Ops road ', U ' need it ', U ' baidu.com ', U ' 361way.com ', U ' 91it.org '] #定义频道名称 # define 5 channels 7 days a week traffic data list Range (5): TMP = [] for j in Range (7): Tmp.append (Get_num ()) data.append (TMP) Format=workbook.add_format () #定义format格式对象fo Rmat.set_border (1) #定义format对象单元格边框加粗 (1 pixels) format Format_title=workbook.add_format () #定义format_title格式对象format_ Title.set_border (1) #定义format_title对象单元格边框加粗 (1 pixels) format format_title.set_bg_color (' #cccccc ') #定义format_  The Title object cell background color is # ' #cccccc ' Format 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 pixels) format format_ave.set_num_format (' 0.00 ') #定义format_ave对象单元格数字类别显示格式 # The headings, business names, respectively, are written in rows or columns , traffic data is written to the original cell, referencing different format objects 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) # Calculation (average function) frequency #道周平均流量 chart.add_series ({' Categories ': ' =sheet1! $B $: $H $ ', #将 ' Monday to Sunday ' as a chart data label (x-axis) ' Valu Es ': ' =sheet1! $B $ ' +cur_row+ ': $H $ ' +cur_row, #频道一周所有数据作 #为数据区域 ' line ': {' color ': ' Black '}, #线条颜色定义为black color) ' name ': ' =sheet1! $A $ ' +cur_row, #引用业务名称为图例项}) for row in range (2, 7): Chart data series function call Chart_series (str (row)) Cha Rt.set_size ({' width ': 577, ' height ': 287}) #设置图表大小chart. Set_title ({' Name ': U ' crawlBug Analysis '}) #设置图表 (above) headline Chart.set_y_axis ({' name ': ' Count '}) #设置y轴 (left) small title Worksheet.insert_chart (' A8 ', chart) # Insert Chart Workbook.close () #关闭Excel文档 in cell A8

Since there is only one picture, it is easy to generate pictures through VBA code. To open the Excel chart, open the macro editing interface with ALT + F11 shortcut, open the VB Editor's Immediate window: "View"-"Immediate Window", or use the shortcut key "Ctrl + G" and enter the following code

ActiveSheet. ChartObjects (1). Chart.export "C:\chart.png"

When you press the "Enter" key, the generated chart chart above is generated on the C drive.

Two, 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}) #  This is a column of data table headings = [' Number ', ' Batch 1 ', ' Batch 2 ']data = [[2, 3, 4, 5, 6, 7], [10, 40, 50, 20, 10, 50], [30, 60, 70, 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]) ############################################ #创建一个图表, The type is Columnchart1 = Workbook.add_chart ({' type ': ' column '}) # Config series, which is related to the previous wordsheet. Chart1.add_series ({' name ': ' =sheet1! $B $ ', ' categories ': ' =sheet1! $A $: $A $7 ', ' values ': ' =sheet1! $B $: $B $7 ',}) # Configure a second series.  Note use of the 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 ': ' Result s 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 (one) # Insert the chart into the worksheet (with a offset). Worksheet.insert_chart (' D2 ', Chart1, {' X _offset ': +, ' Y_offset ': ten}) ######################################################################### Create a Stacked Chart sub-type. #chart2 = Workbook.add_chart ({' type ': ' Column ', ' subtype ': ' Stacked '}) # Configure the first series . chart2.add_series ({' name ': ' =sheet1! $B $ ', ' categories ': ' =sheet1! $A $: $A $7 ', ' values ': ' =sheet1! $B $: $B $7 ',}) # Configure Second series.chart2.add_series ({' name ': ' =sheet1! $C ', ' categories ': ' =sheet1! $A $: $A $7 ', ' values ': ' = sheet1! $C $: $C $7 ',}) # Add a chart title and some axis labels.chart2.set_title ({' name ': ' Stacked chart '}) chart2.set_x_axi S ({' name ': ' Test number '}) Chart2.set_y_axis ({' name ': ' Sample length (mm) '}) # set an Excel chart Style.chart2.set_style ( #) # Insert the chart into the worksheet (with a offset). WorksheEt.insert_chart (' D18 ', Chart2, {' X_offset ': +, ' 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 ', ' categories ': ' = sheet1! $A $: $A $7 ', ' values ': ' =sheet1! $B $: $B $7 ',}) # Configure second series.chart3.add_series ({' name ': ' =sheet1! $C $ 1 ', ' categories ': ' =sheet1! $A $: $A $7 ', ' values ': ' =sheet1! $C $: $C $7 ',}) # ADD A Chart title and some axis labels.chart3.se T_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 (+) # Insert the chart into the worksheet (with a offset) . Worksheet.insert_chart (' D34 ', chart3, {' X_offset ': +, ' Y_offset ': Ten}) Workbook.close ()

The same data source created three types of graphs, because there are three pictures, the above method of exporting a graph is definitely not, here to open the macro, 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

The example is no longer here and can be run on its own.