openpyxl一键报告

# *
# *  @author Zhaoliang Ye 叶昭良(zl_ye@qny.chng.com.cn)
# *  @version V0.1
# *  @Title: readProducts.py
# *  @Description: 只读取每个文件F5数据
# *  @Time: 2022/2/10 10:37
# *
import os
import openpyxl

dir_path=os.path.dirname(os.path.realpath(__file__))
print(f'根路径: {dir_path}')
pro_path=os.path.join(dir_path,"products\\")
print(f'当前路径: {pro_path}')


values=[]

for file in os.listdir(pro_path):
    print(f'当前文件：{file}')
    if file.lower().endswith(".xlsx"):
        wb=openpyxl.load_workbook(os.path.join(pro_path,file))
        sheet=wb["Sheet1"]
        value=sheet["F5"].value
        values.append(value)

print(f'All the excel F5 value is {values}')

{{title}}
{{author}}
{{day}}/{{month}}/{{year}}

Index	Velocity	Cp	Ct
{%tr for item in table_contents %}
{{item.Index}}	{{item.Velocity}}	{{item.Cp}}	{{item.Ct}}
{%tr endfor %}


{{image}}
Figure1: graph of power

# *
# *  @author Zhaoliang Ye 叶昭良(zl_ye@qny.chng.com.cn)
# *  @version V0.1
# *  @Title: autoMateReport.py
# *  @Description: 自动生成报告
# *  @Time: 2022/2/10 10:58
# *

import os
import openpyxl
#:https://stackoverflow.com/questions/15025978/win32com-client-error/15051499
import win32com.client
import PIL
from PIL import ImageGrab,Image
from openpyxl.chart import LineChart, Reference
import time

dir_path=os.path.dirname(os.path.realpath(__file__))
print(f'根路径: {dir_path}')
file_path=os.path.join(dir_path,"input\\HZ93PowerCurves.xlsx")
file_out_path=os.path.join(dir_path,"output\\HZ93PowerCurvesout.xlsx")
print(f'当前路径: {file_path}')

wb=openpyxl.load_workbook(file_path)
## wb.create_sheet("newSheet")
#
print(f'wb.sheetnames={wb.sheetnames}')
sheet=wb["Sheet1"]
for row in range(2,sheet.max_row+1):
    velocity=sheet.cell(row,1)
    speed=sheet.cell(row,3)
    powerCp=sheet.cell(row,7)
    ## powerCp.value=float(velocity.value)*float(speed.value)
# https://www.osgeo.cn/openpyxl/charts/chart_layout.html
#https://blog.csdn.net/hunter_wyh/article/details/78498323
#values=Reference(sheet,min_row=2,max_row=sheet.max_row,min_col=7,max_col=7)
x=Reference(sheet,min_row=2,max_row=sheet.max_row,min_col=1)
values=Reference(sheet,min_row=1,max_row=sheet.max_row,min_col=7,max_col=8)
print(f'values={values}')
chart=LineChart()
chart.y_axis.title="Cp-Ct"
chart.x_axis.title="Velocity/(m/s)"
chart.add_data(values)
chart.set_categories(x)
chart.varyColors=True
chart.legend.position="r"
sheet.add_chart(chart,"e2")

wb.save(file_out_path)

###  拷贝excel表中图片！
## OSError: failed to open clipboard
#  pip install pillow --upgrade
xlApp = win32com.client.Dispatch('Excel.Application')
# for fname in filelist:
#     xlbook = XlsClass(xlApp, fname)
#     # do something with xlbook
#     xlbook.close()
output_image="output/chart.png"
# operation = win32com.client.Dispatch("Excel.Application")
# operation.DisplayAlert=0

# 老写法 可运行
workbook_2 =  xlApp.Workbooks.Open(file_out_path)
# 新写法
# workbook_2 =  XlsClass(xlApp,output_image)
sheet_2=workbook_2.Sheets(1)
for x,chart in enumerate(sheet_2.Shapes):
    chart.Copy()
    image=ImageGrab.grabclipboard()
    image.save(output_image,'png')
    # time.sleep(1)
    pass

print(f'type(workbook_2)={type(workbook_2)}')
workbook_2.Close()
#workbook_2.Close(True)
#xlApp.Quit()


## 导入word
##
from docx.shared import Cm,Inches,Mm,Emu
from docx import Document
from docxtpl import  DocxTemplate,InlineImage
import datetime


template =DocxTemplate("input/template.docx")
table_contents=[]
for i in range(2,sheet.max_row+1):
    table_contents.append(
        {
            "Index":i-1,
            "Velocity": sheet.cell(i,1).value,
            "Cp":sheet.cell(i,7).value,
            "Ct":sheet.cell(i,8).value,
        }
    )

## import saved figure
image=InlineImage(template,"output/chart.png",Cm(16))

## Declare template variable
context={
    "title":"Automate report of HZ93",
    "author":"yzl",
    "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("output/Automated_Report.docx")