Use Python to export Excel charts and methods for pictures everywhere

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 a  N 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 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 = = ch T.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 Pyxlchar T and 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格 Type Object Format.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_aveFormat Object Format_ave.set_border (1) #定义format_ave对象单元格边框加粗 (1 pixels) format format_ave.set_num_format (' 0.00 ') #定义format_ Ave Object Cell number category display format # The title, business name, and traffic data are written to the original cell in the form of row or column writes, 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) #计算 (average function) frequency #道周平均流量 chart.add_series ({' Categor                                ies ': ' =sheet1! $B $: $H ', #将 "Monday to Sunday" as a chart data label (x axis) ' Values ': ' =sheet1! $B $ ' +cur_row+ ': $H $ ' +cur_row, #频道一周所有数据作 #为数据区域 ' line ': {' color ': ' Black ', #线条颜色定义为black ' name ': ' =sheet1! $A $ ' +cur_row, # Reference business name as legend item}) for row in range (2, 7): Chart data series function call Chart_series (str (ROW)) #数据域以第2Chart.set_size ({' width ': 577, ' height ': 287}) #设置图表大小chart. Set_title ({' Name ': U ' Reptile Analysis '}) #设置图表 (above) headline Chart.set_y_axis ({' name ': ' Count '}) #设置y轴 (left) Small caption Worksheet.insert_chart (' A8 ', chart) #在A8单元格插入图表workbook. Close () #关闭Excel文档

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, 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 ',}) # C Onfigure 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 (one) # Insert the chart into the worksheet (with a offset). Worksheet.insert_chart (' D2 ', chart 1, {' X_offset ': +, ' Y_offset ': 10}) ######################################################################### Create a stacked chart sub-type. #chart2 = Workbook.add_chart ({' type ': ' Column ', ' subtype ': ' Stacked '}) # Configure the Firs T 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 ', ' Valu Es ': ' =sheet1! $C $: $C $7 ',}) # Add a chart title and some axis labels.chart2.set_title ({' name ': ' Stacked chart '}) chart2.s Et_x_axis ({' 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 an 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 ', ' categories ': ' =sheet1! $A $ $: $A $7 ', ' values ': ' =sheet1! $C $: $C $7 ',}) # ADD A Chart Title an D 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 (+) # Insert the chart into th e worksheet (with an 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.

