Example of saving chart as an 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-8
Import xlsxwriter
Import random
Def 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'thurs', u'friday', u'satur ', u'sunday', u'average traffic']
Buname = [u 'O & M path', U' requires IT ', u'baidu. com ', U' 361way. com ', U' 91it. org '] # define the channel name
# Define the 5-Channel traffic data list for 7 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 the format object
Format. set_border (1) # defines the format of the format object cell border bold (1 pixel)
Format_title = workbook. add_format () # defines the format_title format object
Format_title.set_border (1) # defines the format_title object cell border bold (1 pixel) format
Format_title.set_bg_color ('# cccccccc') # defines the background color of the format_title object cell
# '# Cccccc' format
Format_title.set_align ('center') # defines the format of the center and alignment of the format_title object cell.
Format_title.set_bold () # defines the bold format of the content of the format_title object cell
Format_ave = workbook. add_format () # defines the format_ave format object
Format_ave.set_border (1) # defines the format_ave object cell border bold (1 pixel) format
Format_ave.set_num_format ('0. 00') # defines the display format of the number category of the format_ave object cell.
# Write the title, service name, and traffic data into the initial cell by row or column respectively, and reference objects of different formats
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)
# Define chart data series functions
Def chart_series (cur_row ):
Worksheet. write_formula ('I' + cur_row ,\
'= AVERAGE (B' + cur_row + ': H' + cur_row +') ', format_ave) # Calculate (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 of the channel for one week
# For the data region
'Line': {'color': 'black'}, # The line color is defined as black)
'Name': '= Sheet1! $ A $ '+ cur_row, # reference the business name as A legend
})
For row in range (2, 7): # the data fields are 2nd ~ Call functions of the chart data series in six rows
Chart_series (str (row ))
Chart. set_size ({'width': 577, 'height': 287}) # set the chart size
Chart. set_title ({'name': u'crawler analytics '}) # set the title of the chart (above)
Chart. set_y_axis ({'name': 'count'}) # set the title of the y axis (left)
Worksheet. insert_chart ('a8 ', chart) # insert a chart into the A8 cell
Workbook. 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-8
Import xlsxwriter
Workbook = xlsxwriter.workbook('chart_column.xlsx ')
Worksheet = workbook. add_worksheet ()
Bold = workbook. add_format ({'bold ': 1 })
# This is a data table column.
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 in the column type
Chart1 = 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 analysts '})
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 ',
'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 ChartObject
For Each XlsChart In Worksheets ("Sheet1"). ChartObjects
XlsChart. Chart. Export Filename: = "C: \" & XlsChart. Name & ". jpg", FilterName: = "JPG"
Next
End Sub

In this example, no screenshots are taken here. You can run the screenshot on your own.