Skip to content

Instantly share code, notes, and snippets.

@mkhorasani
Created April 16, 2021 16:04
  • Star 4 You must be signed in to star a gist
  • Fork 4 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save mkhorasani/8345812e69d5657bbfd6aea9ee0b7262 to your computer and use it in GitHub Desktop.
import openpyxl as xl
from openpyxl.chart import LineChart, Reference
import win32com.client
import PIL
from PIL import ImageGrab, Image
import os
import sys
from docx.shared import Cm
from docxtpl import DocxTemplate, InlineImage
from docx.shared import Cm, Inches, Mm, Emu
import random
import datetime
import matplotlib.pyplot as plt
######## Generate automated excel workbook ########
workbook = xl.load_workbook('Book1.xlsx')
sheet_1 = workbook['Sheet1']
for row in range(2, sheet_1.max_row + 1):
current = sheet_1.cell(row, 2)
voltage = sheet_1.cell(row, 3)
power = float(current.value) * float(voltage.value)
power_cell = sheet_1.cell(row, 1)
power_cell.value = power
values = Reference(sheet_1, min_row = 2, max_row = sheet_1.max_row, min_col = 1, max_col = 1)
chart = LineChart()
chart.y_axis.title = 'Power'
chart.x_axis.title = 'Index'
chart.add_data(values)
sheet_1.add_chart(chart, 'e2')
workbook.save('Book1.xlsx')
######## Extract chart image from Excel workbook ########
input_file = "C:/Users/.../Book1.xlsx"
output_image = "C:/Users/.../chart.png"
operation = win32com.client.Dispatch("Excel.Application")
operation.Visible = 0
operation.DisplayAlerts = 0
workbook_2 = operation.Workbooks.Open(input_file)
sheet_2 = operation.Sheets(1)
for x, chart in enumerate(sheet_2.Shapes):
chart.Copy()
image = ImageGrab.grabclipboard()
image.save(output_image, 'png')
pass
workbook_2.Close(True)
operation.Quit()
######## Generating automated word document ########
template = DocxTemplate('template.docx')
#Generate list of random values
table_contents = []
for i in range(2, sheet_1.max_row + 1):
table_contents.append({
'Index': i-1,
'Power': sheet_1.cell(i, 1).value,
'Current': sheet_1.cell(i, 2).value,
'Voltage': sheet_1.cell(i, 3).value
})
#Import saved figure
image = InlineImage(template,'chart.png',Cm(10))
#Declare template variables
context = {
'title': 'Automated Report',
'day': datetime.datetime.now().strftime('%d'),
'month': datetime.datetime.now().strftime('%b'),
'year': datetime.datetime.now().strftime('%Y'),
'table_contents': table_contents,
'image': image
}
#Render automated report
template.render(context)
template.save('Automated_report.docx')
@Ankit8989
Copy link

Please share template file

@mkhorasani
Copy link
Author

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment