Getting graphs from Excel to Word

One of the problems I wanted to tackle when I started using python-docx was replacing tools like Oracle BI Publisher. I have years experience with this and it is not that nice to use but its support for graphs and overall it does do the job. I have been using it to create monthly management reports to show progress against KPIs etc. So one thing that I needed to tackle was getting nicely formatted graphs in there. Step in XLwings. My basic process: 

1. Create the required graphs in Excel. 
2. Create SQL / XLwings scripts to update the graphs 
3. Export the charts as pictures. 
4. Insert the pictures into Word document. 

It is not the remit of this post to cover the first 2 steps in detail but rather focus on step 3 and step 4. 

The Document is here:

The code:

	# -*- coding: utf-8 -*-
	"""
	Created on Thu Dec 28 10:49:01 2017
	@author: garym
	"""
	## Required imports
	import xlwings as xw
	import glob
	import os
	import datetime
	from docx import Document
	from docx.enum.text import WD_ALIGN_PARAGRAPH
	## set up folder locations
	input_dir = 'C:\\Users\\garym\\Documents\\New folder (2)\\'
	output_dir = r'C:\Users\garym\Documents\PyWinAutoBlog\Output'
	## set up extracting graphs
	Excel = True
	if Excel:
	## get latest file
	list_of_files = glob.glob(input_dir + 'Running*.xlsx')
	latest_file = max(list_of_files, key=os.path.getctime)
	#xw.App(visible=True)
	wb = xw.Book(latest_file)
	## loop through graphs and save as a picture
	i = 0
	for chart in wb.sheets['Dashboard'].api.ChartObjects():
	chart.Chart.Export(output_dir+'\\chart' + str(i) + ".png")
	i = i+1
	## close excel
	wb.close()
	### Open new document
	document = Document()
	## add picture and then centre it
	document.add_picture(output_dir+r'\chart1.png')
	last_paragraph = document.paragraphs[-1]
	last_paragraph.alignment = WD_ALIGN_PARAGRAPH.CENTER
	### Add more pictures
	document.add_picture(output_dir+r'\chart0.png')
	document.add_picture(output_dir+r'\chart2.png')
	document.add_picture(output_dir+r'\chart3.png')
	## set up filename
	filenamedocx = output_dir+r'\Running_report'+datetime.datetime.today().strftime('%d%m%y')+'.docx'
	## save document
	document.save(filenamedocx)

view raw exportgraphs.py hosted with ❤ by GitHub